首页 > 代码库 > Mysql DCL (Date Control Language)

Mysql DCL (Date Control Language)

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: 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)
    

二,修改字段内容:

  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: 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)
    	
  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: 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)
    
    	

三,删除字段内容:

  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)
    	

Mysql DCL (Date Control Language)