一.插入语句insert
1.插入单条数据
2.插入多条数据
二.替换语句replace
1.格式一
2.格式二，将其他表的字段复制到本表字段
3.格式三
三.修改语句update
四.删除语句delete、truncate、drop
1.delete from 表名 查找条件;
2.truncate table 表名;
3.drop table 表名;
五.查询语句select
1.基础语法
2.select配合算术表达式
3.配合as定义字段别名
4.处理重复记录
一.插入语句insert
注意：into可以省略，特殊字符需要使用'\'转义
1.插入单条数据
（1）insert into 表名 values (字段1的值，字段2的值);
mysql8.0 [SLB]>desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql8.0 [SLB]>insert t1 values (1,"sulibao");
Query OK, 1 row affected (0.01 sec)
mysql8.0 [SLB]>select * from t1;
+------+---------+
| id | name |
+------+---------+
| 1 | sulibao |
+------+---------+
1 row in set (0.00 sec)
（2）insert 表名 set 字段=值,字段值;
mysql8.0 [SLB]>insert t1 set id=2,name="lixinjin";
Query OK, 1 row affected (0.00 sec)mysql8.0 [SLB]>select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | sulibao |
| 2 | lixinjin |
+------+----------+
2 rows in set (0.00 sec)
2.插入多条数据
insert 表名(字段1,字段2) values (字段1的值，字段2的值)，(字段1的值，字段2的值)，(字段1的值，字段2的值);
mysql8.0 [SLB]>insert t1(id,name) values (3,"aaa"),(4,"bbb"),(5,"ccc");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql8.0 [SLB]>select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | sulibao |
| 2 | lixinjin |
| 3 | aaa |
| 4 | bbb |
| 5 | ccc |
+------+----------+
5 rows in set (0.00 sec)
二.替换语句replace
注意：into可以省略，表在没有设置主键时使用replace命令，只会新增一条命令，replace不生效。使用replace语句向表插入新记录，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），再插入新记录。
1.格式一
replace 表名 values(字段1的值,字段2的值) ;
#有主键的t2
mysql8.0 [SLB]>select * from t2;
+----+----------+
| id | name |
+----+----------+
| 1 | sulibao |
| 2 | lixinjin |
+----+----------+
2 rows in set (0.00 sec)mysql8.0 [SLB]>replace t2 values(2,"lioahang");
Query OK, 2 rows affected (0.00 sec)mysql8.0 [SLB]>select * from t2;
+----+----------+
| id | name |
+----+----------+
| 1 | sulibao |
| 2 | lioahang |
+----+----------+
2 rows in set (0.00 sec)#无主键的t1
mysql8.0 [SLB]>select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | sulibao |
| 2 | lixinjin |
| 3 | aaa |
| 4 | bbb |
| 5 | ccc |
+------+----------+
5 rows in set (0.00 sec)mysql8.0 [SLB]>replace t1 values(5,"666");
Query OK, 1 row affected (0.00 sec)mysql8.0 [SLB]>select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | sulibao |
| 2 | lixinjin |
| 3 | aaa |
| 4 | bbb |
| 5 | ccc |
| 5 | 666 |
+------+----------+
6 rows in set (0.00 sec)
2.格式二，将其他表的字段复制到本表字段
replace 表名(字段列表) select 字段列表 from 查找条件;
mysql8.0 [SLB]>select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | sulibao |
| 2 | lixinjin |
| 3 | aaa |
| 4 | bbb |
| 5 | ccc |
| 5 | 666 |
+------+----------+
6 rows in set (0.00 sec)mysql8.0 [SLB]>replace t2(id,name) select id,name from t1 where id=4;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql8.0 [SLB]>select * from t2;
+----+----------+
| id | name |
+----+----------+
| 1 | sulibao |
| 2 | lioahang |
| 4 | bbb |
+----+----------+
3 rows in set (0.00 sec)
3.格式三
replace 表名 set 字段=值,字段=值;
mysql8.0 [SLB]>replace t2 set id=4,name="lixinjin";
Query OK, 2 rows affected (0.00 sec)mysql8.0 [SLB]>select * from t2;
+----+----------+
| id | name |
+----+----------+
| 1 | sulibao |
| 2 | lioahang |
| 4 | lixinjin |
+----+----------+
3 rows in set (0.00 sec)三.修改语句update
修改符合查找条件的字段，可指定修改多个字段
update 表名 set 修改字段=值 查找条件;
mysql8.0 [SLB]>select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | sulibao |
| 2 | lixinjin |
| 3 | aaa |
| 4 | bbb |
| 5 | ccc |
| 5 | 666 |
+------+----------+
6 rows in set (0.00 sec)mysql8.0 [SLB]>update t1 set id=6 where name="666";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql8.0 [SLB]>select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | sulibao |
| 2 | lixinjin |
| 3 | aaa |
| 4 | bbb |
| 5 | ccc |
| 6 | 666 |
+------+----------+
6 rows in set (0.00 sec)
四.删除语句delete、truncate、drop
1.delete from 表名 查找条件;
删除数据，保留表结构，可以恢复，数据量大时就很
mysql8.0 [SLB]>select * from t2;
+----+-----------+
| id | name |
+----+-----------+
| 1 | sulibao |
| 2 | lioahang |
| 4 | lixinjin |
| 5 | lixinjin |
| 6 | sulibao |
| 8 | lixinjin1 |
+----+-----------+
6 rows in set (0.00 sec)mysql8.0 [SLB]>delete from t2 where name="lioahang";
Query OK, 1 row affected (0.01 sec)mysql8.0 [SLB]>select * from t2;
+----+-----------+
| id | name |
+----+-----------+
| 1 | sulibao |
| 4 | lixinjin |
| 5 | lixinjin |
| 6 | sulibao |
| 8 | lixinjin1 |
+----+-----------+
5 rows in set (0.00 sec)
2.truncate table 表名;
删除所有数据，保留表结构，不可以恢复，一次全部删除所有数据，速度相对delete较快
3.drop table 表名;
直接删除表数据和表结构，速度最快
五.查询语句select
1.基础语法
select */具体字段 from 表名;
mysql8.0 [SLB]>select id,name from t2;
+----+-----------+
| id | name |
+----+-----------+
| 1 | sulibao |
| 4 | lixinjin |
| 5 | lixinjin |
| 6 | sulibao |
| 8 | lixinjin1 |
+----+-----------+
5 rows in set (0.00 sec)
2.select配合算术表达式
（1）对数值型数据列、变量、常量可以使用算数操作符创建表达式（+、-、*、/）
注意：
"+"默认只有运算符功能，对于转换成功的值直接相加，转换不成功就将字符型数值视为0，有一方为null值结果就为null值（null值：空值是指不可用、未对其分配值，空值不等于零或空格，任意数据类型都支持空值）
（2）对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+、-）
（3）可以在列和常量之间、多列之间进行运算
（4）优先级： 乘法和除法的优先级高于加法和减法，同级运算的顺序是从左到右，可以使用括号强行改变优先级
mysql8.0 [SLB]>select * from t3;
+----+------+----------+
| id | day | permoney |
+----+------+----------+
| 1 | 18 | 300 |
| 2 | 19 | 300 |
| 3 | 25 | 450 |
+----+------+----------+
3 rows in set (0.00 sec)mysql8.0 [SLB]>select id,day,permoney*day from t3;
+----+------+--------------+
| id | day | permoney*day |
+----+------+--------------+
| 1 | 18 | 5400 |
| 2 | 19 | 5700 |
| 3 | 25 | 11250 |
+----+------+--------------+
3 rows in set (0.00 sec)
（5）安全等于运算符号<=>
用于比较数值大小，真1假0
mysql8.0 [SLB]>select 2<=>2;
+-------+
| 2<=>2 |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)mysql8.0 [SLB]>select 2<=>3;
+-------+
| 2<=>3 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
判断是否为空
mysql8.0 [SLB]>insert t3(id,day) values(4,26);
Query OK, 1 row affected (0.00 sec)mysql8.0 [SLB]>select * from t3;
+----+------+----------+
| id | day | permoney |
+----+------+----------+
| 1 | 18 | 300 |
| 2 | 19 | 300 |
| 3 | 25 | 450 |
| 4 | 26 | NULL |
+----+------+----------+
5 rows in set (0.00 sec)
mysql8.0 [SLB]>select id,day from t3 where permoney<=>null;
+----+------+
| id | day |
+----+------+
| 4 | 26 |
+----+------+
2 rows in set (0.00 sec)
3.配合as定义字段别名
mysql8.0 [SLB]>select id as "工号",day "工期",permoney "每日工资" from t3;
+--------+--------+--------------+
| 工号 | 工期 | 每日工资 |
+--------+--------+--------------+
| 0 | NULL | NULL |
| 1 | 18 | 300 |
| 2 | 19 | 300 |
| 3 | 25 | 450 |
| 4 | 26 | NULL |
+--------+--------+--------------+
5 rows in set (0.00 sec)
4.处理重复记录
（1）查看重复
mysql8.0 [SLB]>select * from t2;
+----+-----------+
| id | name |
+----+-----------+
| 1 | sulibao |
| 4 | lixinjin |
| 5 | lixinjin |
| 6 | sulibao |
| 8 | lixinjin1 |
+----+-----------+
5 rows in set (0.00 sec)mysql8.0 [SLB]>select name from t2;
+-----------+
| name |
+-----------+
| sulibao |
| lixinjin |
| lixinjin |
| sulibao |
| lixinjin1 |
+-----------+
5 rows in set (0.00 sec)
（2）distinct清除重复行，可以指定字段范围
mysql8.0 [SLB]>select distinct name from t2;
+-----------+
| name |
+-----------+
| sulibao |
| lixinjin |
| lixinjin1 |
+-----------+
3 rows in set (0.00 sec)
