-------------------------------------------- MySQL课堂笔记------------------------------------------D:\> --DOS(系统)命令行提示符D:\>D:\>D:\>mysql -h localhost -u root -proot --连接mysql数据库Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> --MySQL的命令行提示。mysql>mysql> quit --退出ByeD:\>mysql -h localhost -u root -pEnter password: ****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql>D:\>mysql -u root -pEnter password: ****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 17Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases; --查看当前用户下允许看到数据库+--------------------+| Database |+--------------------+| information_schema || mysql || test |+--------------------+3 rows in set (0.00 sec)mysql> create database dd; --创建一个数据库ddQuery OK, 1 row affected (0.00 sec)mysql> create database dd; --创建一个数据库dd(因为dd数据库已存在,故报错)ERROR 1007 (HY000): Can''t create database 'dd'; database existsmysql> show databases; --查看当前用户下允许看到数据库+--------------------+| Database |+--------------------+| information_schema || mysql || dd || test |+--------------------+4 rows in set (0.00 sec)mysql> create database if not exists dd; --若dd数据库不存在则创建数据库ddQuery OK, 0 rows affected, 1 warning (0.00 sec)mysql> drop database dd; --删除一个dd数据库Query OK, 0 rows affected (0.03 sec)mysql> drop database if exists dd; --若dd数据库存在则尝试删除。Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create database lamp74; --创建一个数据库lamp74Query OK, 1 row affected (0.00 sec)mysql> use lamp74; --进入(选择)数据库lamp74Database changedmysql> select database(); --查看当前在哪个数据库下+------------+| database() |+------------+| lamp74 |+------------+1 row in set (0.00 sec)mysql> show tables; --查看当前库下的所有表格。Empty set (0.00 sec)mysql>--建表格式:create table 表名(字段名 类型 [约束],字段名 类型 [约束],...);mysql> create table demo(name varchar(8) not null,age int,addtime datetime);Query OK, 0 rows affected (0.06 sec)--同上创建一个demo2表,格式一样mysql> create table demo2( -> name varchar(8) not null, --name字段,字串类型8个长度,非空 -> age int, -- age字段 int整型 -> addtime datetime -- addtime添加时间字段 datetime日期类型 -> );Query OK, 0 rows affected (0.05 sec)mysql>mysql> show tables; --查看当前数据库下都有什么表格+------------------+| Tables_in_lamp74 |+------------------+| demo || demo2 |+------------------+2 rows in set (0.00 sec)mysql> desc demo; --查看demo表的表结构+---------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+------------+------+-----+---------+-------+| name | varchar(8) | NO | | NULL | || age | int(11) | YES | | NULL | || addtime | datetime | YES | | NULL | |+---------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> show create table demo; --以表格方式查看demo的建表语句+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+| demo | CREATE TABLE `demo` ( `name` varchar(8) NOT NULL, `age` int(11) default NULL, `addtime` datetime default NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> desc demo; --查看demo表结构+---------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+------------+------+-----+---------+-------+| name | varchar(8) | NO | | NULL | || age | int(11) | YES | | NULL | || addtime | datetime | YES | | NULL | |+---------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> show create table demo\G --以文本的输出格式查看demo的建表语句*************************** 1. row *************************** Table: demoCreate Table: CREATE TABLE `demo` ( `name` varchar(8) NOT NULL, `age` int(11) default NULL, `addtime` datetime default NULL) ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql>mysql> drop table demo2; --删除一个表格demo2Query OK, 0 rows affected (0.00 sec)mysql> drop table if exists demo2; --若demo2表存在则删除表格demo2Query OK, 0 rows affected (0.00 sec)mysql> show tables;+------------------+| Tables_in_lamp74 |+------------------+| demo |+------------------+1 row in set (0.00 sec)mysql>--添加数据的格式:insert into 表名(字段列表) values(值列表..);--如下三条是添加数据格式mysql> insert into demo(name,age,addtime) values('zhangsan',20,'2013-11-11 16:30:20');Query OK, 1 row affected (0.02 sec)mysql> insert into demo(name,age,addtime) -> values('lisi',22,'2013-11-11 15:20:30');Query OK, 1 row affected (0.00 sec)mysql> insert into demo values('wangwu',24,'2013-12-12 10:10:11');Query OK, 1 row affected (0.00 sec)--查看数据格式:select 字段名(*表示所有字段) from 表名 [条件];mysql> select * from demo; --查看demo表中的所有数据+----------+------+---------------------+| name | age | addtime |+----------+------+---------------------+| zhangsan | 20 | 2013-11-11 16:30:20 || lisi | 22 | 2013-11-11 15:20:30 || wangwu | 24 | 2013-12-12 10:10:11 |+----------+------+---------------------+3 rows in set (0.00 sec)mysql> select age,name from demo; --只查看指定的两列数据信息+------+----------+| age | name |+------+----------+| 20 | zhangsan || 22 | lisi || 24 | wangwu |+------+----------+3 rows in set (0.00 sec)mysql>-- 数据的修改:update 表名 set 字段名=修改的值,字段名=修改的值,... where 条件;--将demo表中zhangsan的年龄age改成23岁。mysql> update demo set age=23 where name='zhangsan';Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from demo;+----------+------+---------------------+| name | age | addtime |+----------+------+---------------------+| zhangsan | 23 | 2013-11-11 16:30:20 || lisi | 22 | 2013-11-11 15:20:30 || wangwu | 24 | 2013-12-12 10:10:11 |+----------+------+---------------------+3 rows in set (0.00 sec)-- 将demo表中wangwu的age改成25,addtime改成'2012-12-24 10:10:20'mysql> update demo set age=25,addtime='2012-12-24 10:10:20' where name='wangwu';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from demo;+----------+------+---------------------+| name | age | addtime |+----------+------+---------------------+| zhangsan | 23 | 2013-11-11 16:30:20 || lisi | 22 | 2013-11-11 15:20:30 || wangwu | 25 | 2012-12-24 10:10:20 |+----------+------+---------------------+3 rows in set (0.00 sec)mysql>--删除语句格式:delete from 表名 where 条件式;mysql> delete from demo where name='lisi'; --将lisi数据删除Query OK, 1 row affected (0.00 sec)mysql> select * from demo;+----------+------+---------------------+| name | age | addtime |+----------+------+---------------------+| zhangsan | 23 | 2013-11-11 16:30:20 || wangwu | 25 | 2012-12-24 10:10:20 |+----------+------+---------------------+2 rows in set (0.00 sec)mysql>------------------------------------------------------------ 数据库的备份和恢复---------------------------------------------------------D:\>D:\>mysqldump -u root -p lamp74 >lamp74_20131111.sql --备份lamp74数据库Enter password: ****D:\>mysqldump -u root -p lamp74 demo>lamp74_demo.sql --备份lamp74库下的demo表。Enter password: ****--数据恢复,将数据导入lamp74库中D:\>mysql -u root -p lamp74grant all on *.* to zhangsan@'%' identified by '123';Query OK, 0 rows affected (0.17 sec)--刷新生效,否则就要重启MySQL服务才可以。mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> grant all on lamp74.* to qq@'%' identified by '123';Query OK, 0 rows affected (0.03 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quitByeD:\>mysql -h 192.168.200.250 -u qq -pEnter password: ***Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 41Server version: 5.0.51b-community-nt-log MySQL Community Edition (Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || lamp74 || test |+--------------------+3 rows in set (0.00 sec)mysql>