Mysql DCL (Date Control Language)

数据库：mysql> select * from fen;+-------+-------+---------+| name  | china | english |+-------+-------+---------+| ming  |    69 |      99 || hong  |    48 |      33 || qiang |    77 |      33 |+-------+-------+---------+3 rows in set (0.00 sec)mysql> select * from class;+-------+--------+------+| name  | number | sex  |+-------+--------+------+| ming  |      1 |    1 || hong  |      2 |    0 || qiang |      3 |    1 |+-------+--------+------+3 rows in set (0.00 sec)

一，插入字段内容：

1. 插入一条记录：

   mysql> insert into class (name,number,sex)values('li','4','0'); 命令解析：insert into （关键字：插入）class（表名）name，number，sex（字段名）values（关键字：值）li，4,0（字段内容）Query OK, 1 row affected (0.01 sec)mysql> select * from class;+-------+--------+------+| name  | number | sex  |+-------+--------+------+| ming  |      1 |    1 || hong  |      2 |    0 || qiang |      3 |    1 || li    |      4 |    0 |+-------+--------+------+4 rows in set (0.00 sec)

2. 可以省略字段名，但是需要按照顺序插入字段内容：

   mysql> insert into class values('wang',5,1);（省略了字段名）Query OK, 1 row affected (0.01 sec)mysql> select * from class;                 +-------+--------+------+| name  | number | sex  |+-------+--------+------+| ming  |      1 |    1 || hong  |      2 |    0 || qiang |      3 |    1 || li    |      4 |    0 || wang  |      5 |    1 |+-------+--------+------+5 rows in set (0.00 sec)

3. 插入多条内容：

   mysql> insert into fen values('li','78','87'),('wang','64','35');用逗号分隔就可以Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from fen;+-------+-------+---------+| name  | china | english |+-------+-------+---------+| ming  |    69 |      99 || hong  |    48 |      33 || qiang |    77 |      33 || li    |    78 |      87 || wang  |    64 |      35 |+-------+-------+---------+5 rows in set (0.00 sec)

二，修改字段内容：

1. 根据字段内容定位字段，再修改字段内容：

   mysql> update fen set china=89 where name='li';命令解析：update（关键字：更新）fen（表名）set（关键字：设置）china=89（将要更改的内容）where（关键字：在哪）name='li'（定位条件）Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from fen;                      +-------+-------+---------+| name  | china | english |+-------+-------+---------+| ming  |    69 |      99 || hong  |    48 |      33 || qiang |    77 |      33 || li    |    89 |      87 || wang  |    64 |      35 |+-------+-------+---------+5 rows in set (0.00 sec)

2. 多表更新字段：

   mysql> update class a ,fen b set a.number=8,b.china=59 where a.name='li'and  b.name='li';命令解析：class a（给class表一个别名a）a.number（class表的number字段）a.name='li'（位置条件：class表的name字段等于li）and（关键字：把两个条件链接起来）Query OK, 2 rows affected (0.01 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from class;+-------+--------+------+| name  | number | sex  |+-------+--------+------+| ming  |      1 |    1 || hong  |      2 |    0 || qiang |      3 |    1 || li    |      8 |    0 || wang  |      5 |    1 |+-------+--------+------+5 rows in set (0.00 sec)mysql> select * from fen;+-------+-------+---------+| name  | china | english |+-------+-------+---------+| ming  |    69 |      99 || hong  |    48 |      33 || qiang |    77 |      33 || li    |    59 |      87 || wang  |    64 |      35 |+-------+-------+---------+5 rows in set (0.00 sec)

三，删除字段内容：

1. 删除字段内容，整行：

   mysql> delete from class where name='li';命令解析：delete（关键字：删除）from（关键字：指定哪个表）where（定义表位置）Query OK, 1 row affected (0.01 sec)mysql> select * from class;              +-------+--------+------+| name  | number | sex  |+-------+--------+------+| ming  |      1 |    1 || hong  |      2 |    0 || qiang |      3 |    1 || wang  |      5 |    1 |+-------+--------+------+4 rows in set (0.01 sec)