DML语句
DML是指对数据库中表记录的操作，主要包括数据的增删改查以及更新，下面依次介绍
首先创建一张表:：
表名：emp
字段：ename varchar(20),hiredate date ,sal decimal(10,2), deptno int(3)
mysql> create table emp(
-> ename varchar(20),
-> hiredate date,
-> sal decimal(10,2),
-> deptno int(3));
Query OK, 0 rows affected (0.02 sec)
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
1、insert插入记录
语法：insert into 表名(可选字段传值) values(与前面字段对应填值)
不指定字段名时顺序一一对应全要传值。
列如：
insert into emp values('kingfan','2018-10-23','2000',1);
mysql> insert into emp values('kingfan','2018-10-23','2000',1);
Query OK, 1 row affected (0.01 sec)
#使用查询语句查看添加记录
mysql> select * from emp;
+---------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+---------+------------+---------+--------+
| kingfan | 2018-10-23 | 2000.00 | 1 |
+---------+------------+---------+--------+
1 row in set (0.00 sec)
mysql还支持多条语句同时插入：
mysql> insert into emp values('rnf','2000-1-1','3000','2'),('edg','2000-1-1','4000',1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+---------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+---------+------------+---------+--------+
| kingfan | 2018-10-23 | 2000.00 | 1 |
| rnf | 2000-01-01 | 3000.00 | 2 |
| edg | 2000-01-01 | 4000.00 | 1 |
+---------+------------+---------+--------+
3 rows in set (0.00 sec)
注意每条记录之间要逗号隔开
更新记录update
对于表中的记录的值可以通过update命令来修改
语法1: update 表名 set 字段名=修改值 where 字段名=值；where是筛选条根据条件把找到的记录然后将set后面的字段名设定成指定值。
语法2:update 表名 set 字段名=修改值 where 字段名 like 值；
语法1：将enmae=kingfan的记录的ename改成Kingfan
mysql> select * from emp;
+---------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+---------+------------+---------+--------+
| kingfan | 2018-10-23 | 2000.00 | 1 |
| rnf | 2000-01-01 | 3000.00 | 2 |
| edg | 2000-01-01 | 4000.00 | 1 |
+---------+------------+---------+--------+
3 rows in set (0.00 sec)
mysql> update emp set ename='KingFan' where ename='kingfan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+---------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+---------+------------+---------+--------+
| KingFan | 2018-10-23 | 2000.00 | 1 |
| rnf | 2000-01-01 | 3000.00 | 2 |
| edg | 2000-01-01 | 4000.00 | 1 |
+---------+------------+---------+--------+
3 rows in set (0.00 sec)
语法2：
mysql> update emp set ename='RNG' where ename like 'rnf';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+---------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+---------+------------+---------+--------+
| KingFan | 2018-10-23 | 2000.00 | 1 |
| RNG | 2000-01-01 | 3000.00 | 2 |
| edg | 2000-01-01 | 4000.00 | 1 |
+---------+------------+---------+--------+
3 rows in set (0.00 sec)
删除记录
语法：delete from 表名 where 条件；
注意：不加where条件是删除表中所有记录
delete from emp where ename='KingFan';
mysql> select * from emp;
+---------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+---------+------------+---------+--------+
| KingFan | 2018-10-23 | 2000.00 | 1 |
| RNG | 2000-01-01 | 3000.00 | 2 |
| edg | 2000-01-01 | 4000.00 | 1 |
+---------+------------+---------+--------+
3 rows in set (0.00 sec)
mysql> delete from emp where ename='KingFan';
Query OK, 1 row affected (0.01 sec)
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| RNG | 2000-01-01 | 3000.00 | 2 |
| edg | 2000-01-01 | 4000.00 | 1 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
查询记录select
查询所有记录
语法select * from 表名
select * from emp
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| rng | 1993-01-01 | 2000.00 | 1 |
| edg | 1993-01-01 | 3000.00 | 2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
mysql>
按字段名和条件查询
mysql> select ename from emp where deptno=1;
+-------+
| ename |
+-------+
| rng |
+-------+
1 row in set (0.00 sec)
mysql>