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)
一,插入字段内容:
- 插入一条记录:
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)
- 可以省略字段名,但是需要按照顺序插入字段内容:
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)
- 插入多条内容:
mysql> insert into fen values('li','78','87'),('wang','64','35');用逗号分隔就可以
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> 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)
二,修改字段内容:
- 根据字段内容定位字段,再修改字段内容:
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: 0
mysql> 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)
- 多表更新字段:
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: 0
mysql> 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)
三,删除字段内容:
- 删除字段内容,整行:
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)