Mysql数据库insert、replace、update、delete、truncate、drop、select语句介绍_mysql update replace

目录

一.插入语句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.处理重复记录

5.where条件查找

6.select子查询

---

一.插入语句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: 0
 
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)

二.替换语句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: 0
 
mysql8.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: 0
 
mysql8.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)

5.where条件查找

（1）where的字符串和日期值需要用单引号括起来，日期值格式敏感

（2）where比较运算符

（3）is null查找空记录

mysql8.0 [SLB]>select * from t3 where permoney is null;
+----+------+----------+
| id | day  | permoney |
+----+------+----------+
|  4 |   26 |     NULL |
+----+------+----------+
1 rows in set (0.01 sec)

（4）between and查找范围内的记录

mysql8.0 [SLB]>select * from t3 where permoney between 400 and 500;
+----+------+----------+
| id | day  | permoney |
+----+------+----------+
|  3 |   25 |      450 |
+----+------+----------+
1 row in set (0.00 sec)

（5）in查找恰好匹配记录

mysql8.0 [SLB]>select * from t3 where permoney in(300);
+----+------+----------+
| id | day  | permoney |
+----+------+----------+
|  1 |   18 |      300 |
|  2 |   19 |      300 |
+----+------+----------+
2 rows in set (0.01 sec)

（6）like模糊匹配，“like”表示0或多个字符，“_”表示任意单个字符

mysql8.0 [SLB]>select * from t3 where day like "1%";
+----+------+----------+
| id | day  | permoney |
+----+------+----------+
|  1 |   18 |      300 |
|  2 |   19 |      300 |
+----+------+----------+
2 rows in set (0.00 sec)

（7）where逻辑运算符

（8）联合查询

对两个结果求并集，union去重，union all 不去重

mysql8.0 [SLB]>select * from t1 where name in("sulibao","lixinjin") union select * from t2 where id in(1,6);
+------+----------+
| id   | name     |
+------+----------+
|    1 | sulibao  |
|    2 | lixinjin |
|    6 | sulibao  |
+------+----------+
3 rows in set (0.00 sec)
 
mysql8.0 [SLB]>select * from t1 where name in("sulibao","lixinjin") union all select * from t2 where id in(1,6);
+------+----------+
| id   | name     |
+------+----------+
|    1 | sulibao  |
|    2 | lixinjin |
|    1 | sulibao  |
|    6 | sulibao  |
+------+----------+
4 rows in set (0.00 sec)

（9）排序显示

通过order by指定根据哪个字段进行排序，asc升序（默认），desc降序

mysql8.0 [SLB]>select * from t3 order by day desc;
+----+------+----------+
| id | day  | permoney |
+----+------+----------+
|  4 |   26 |     NULL |
|  3 |   25 |      450 |
|  2 |   19 |      300 |
|  1 |   18 |      300 |
|  0 | NULL |     NULL |
+----+------+----------+
5 rows in set (0.00 sec)
 
mysql8.0 [SLB]>select * from t3 order by day asc;
+----+------+----------+
| id | day  | permoney |
+----+------+----------+
|  0 | NULL |     NULL |
|  1 |   18 |      300 |
|  2 |   19 |      300 |
|  3 |   25 |      450 |
|  4 |   26 |     NULL |
+----+------+----------+
5 rows in set (0.00 sec)

（10）where regexp 正则表达式

mysql8.0 [SLB]>select * from t1 where name regexp '^su';
+------+---------+
| id   | name    |
+------+---------+
|    1 | sulibao |
+------+---------+
1 row in set (0.04 sec)
 
mysql8.0 [SLB]>select * from t1 where name regexp '^a';
+------+------+
| id   | name |
+------+------+
|    3 | aaa  |
+------+------+
1 row in set (0.00 sec)

6.select子查询

在匹配条件里再写select语句

mysql8.0 [SLB]>select * from t6;
+------+--------+------+
| age  | weight | id   |
+------+--------+------+
|   18 |   75.5 |    1 |
|   19 |   75.5 |    2 |
|   25 |     90 |    3 |
+------+--------+------+
3 rows in set (0.00 sec)
 
mysql8.0 [SLB]>
mysql8.0 [SLB]>
mysql8.0 [SLB]>select weight from t6 where age> avg(age);
ERROR 1111 (HY000): Invalid use of group function
mysql8.0 [SLB]>select weight from t6 where age>any(select avg(age) from t6);
+--------+
| weight |
+--------+
|     90 |
+--------+
1 row in set (0.01 sec)