SQL的增删改查 的使用

SQL

查询

select 字段列表 from 表名；
​
select id as 序号,name as 姓名 from 表名;#给字段取别名，最终显示的是别名
​
select * from 表名; #查询所有的字段

查询单个字段 不重复记录 distinct

select distinct 字段名 from 表名;

where 后边 可以接的条件

=   等于
>  大于
<  小于
>= 大于等于
<= 小于等于
!= 不等于 
and 并且 
or  或者 
​
SELECT f_id as 序号,f_name as 名称,f_price as 价格 FROM fruits where f_price>10 and f_name='banana';
​

模糊查询

select * from 表名 where 字段名 like 'n%';
select * from 表名 where 字段名 like '%n%';
select * from 表名 where 字段名 like '%n';

查询字段是否允许为空

`select 字段名 from where 字段名 is not null;
`select 字段名 from where 字段名 is null;

排序 order

• asc 为正序 ，默认

• desc 为降序

select * from fruits where f_name is not null order by f_price; #默认是正序  
select * from fruits where f_name is not null order by f_price asc; #跟上面效果一样 
select * from fruits where f_name is not null order by f_price desc;#降序排列 

多字段排序

select * from 表名 where 字段名 is not null order by 字段名 asc,字段名 desc;
如果第一个字段已经将结果排好 那么第二个字段 会被忽略 
s_id的值 多个数据行是也一样的  那么 继续按照第二个字段进行排序 

限制结果集 limit

select * from 表名 where 字段 is not null order by 字段名 asc,字段名 desc limit 10; #只显示符合结果的前十个

聚合函数

函数	说明
sum	求和
count	统计
max	最大值
min	最小值
avg	平均值

select 函数名(字段名) from 表名;

mysql> select max(f_price) from fruits;

mysql> select min(f_price) from fruits;

mysql> select sum(f_price) from fruits;

mysql> select avg(f_price) from fruits;

分组

select 字段名 from 表名 group by 字段;
select 字段,avg(字段) from 表名 group by 字段名;
按照字段字段进行分组 ,然后列出每个每个分组的平均值

group_concat

separator 指定连接符

mysql> select s_id,group_concat(f_price separator '-') as f_price from fruits group by s_id;
+------+------------------+
| s_id | f_price          |
+------+------------------+
|  101 | 10.20-5.20-3.20  |
|  102 | 5.30-11.20-10.30 |
|  103 | 2.20-9.20        |
|  104 | 5.50-6.40-7.60   |
|  105 | 2.60-11.60-8.20  |
|  106 | 15.60            |
|  107 | 3.60-3.60        |
+------+------------------+
7 rows in set (0.00 sec)


mysql> select s_id,group_concat(f_price order by f_id separator '-') as f_price from fruits group by s_i
id;
+------+------------------+
| s_id | f_price          |
+------+------------------+
|  101 | 5.20-10.20-3.20  |  #这三个值  按照 f_id 进行正向排序  
|  102 | 11.20-10.30-5.30 |
|  103 | 2.20-9.20        |
|  104 | 7.60-6.40-5.50   |
|  105 | 8.20-2.60-11.60  |
|  106 | 15.60            |
|  107 | 3.60-3.60        |
+------+------------------+
7 rows in set (0.00 sec)


mysql> select s_id,group_concat(distinct f_price order by f_id separator '-') as f_price from fruits grooup by s_id;
+------+------------------+
| s_id | f_price          |
+------+------------------+
|  101 | 5.20-10.20-3.20  |
|  102 | 11.20-10.30-5.30 |
|  103 | 2.20-9.20        |
|  104 | 7.60-6.40-5.50   |
|  105 | 8.20-2.60-11.60  |
|  106 | 15.60            |
|  107 | 3.60             | #相同的只留下了 一个  
+------+------------------+
7 rows in set (0.00 sec)

在分组的结果上进行统计

mysql> select s_id,count(f_price) as 数量 ,group_concat(f_price separator '-') as 价格列表 from fruitsgroup by s_id;
+------+--------+------------------+
| s_id | 数量   | 价格列表         |
+------+--------+------------------+
|  101 |      3 | 10.20-5.20-3.20  |
|  102 |      3 | 5.30-11.20-10.30 |
|  103 |      2 | 2.20-9.20        |
|  104 |      3 | 5.50-6.40-7.60   |
|  105 |      3 | 2.60-11.60-8.20  |
|  106 |      1 | 15.60            |
|  107 |      2 | 3.60-3.60        |
+------+--------+------------------+
7 rows in set (0.00 sec
Copy

分组的结果上进行统计 然后再过滤 having

mysql> select s_id,count(f_price) as 数量 ,group_concat(f_price separator '-') as 价格列表 from fruits
group by s_id having 数量=1;
+------+--------+--------------+
| s_id | 数量   | 价格列表     |
+------+--------+--------------+
|  106 |      1 | 15.60        |
+------+--------+--------------+
1 row in set (0.00 sec)

多表联合查询

1. 内连接

select 表1.字段名,表2.字段名,表n.字段名 where 条件 ;

1. 外连接

• 左连接

select 表1.字段,表2.字段 from 表1 left join 表2 on 条件;

• 右连接

select 表1.字段,表2.字段 from 表1 right join 表2 on 条件;

1. 多表查询

select 表1.字段1,表n.字段 from 表名1,表名2,表3 where 表1.字段=表2.字段 and 表2.字段=表3.字段;

1. 子查询

mysql> select * from users where uid in (1,2,3,5);
+-----+--------------+----------+
| uid | username     | password |
+-----+--------------+----------+
|   1 | 迪丽热巴     | 123456   |
|   2 | 古力娜扎     | 654321   |
|   3 | 热依扎       | 123123   |
|   5 | 珍塔玛莎     | 111111   |
+-----+--------------+----------+
4 rows in set (0.06 sec)
   
mysql> select * from users where uid in (select uid from order_goos);
+-----+--------------+----------+
| uid | username     | password |
+-----+--------------+----------+
|   1 | 迪丽热巴     | 123456   |
|   2 | 古力娜扎     | 654321   |
|   3 | 热依扎       | 123123   |
|   5 | 珍塔玛莎     | 111111   |
+-----+--------------+----------+
4 rows in set (0.00 sec)

union union all

两张没有关联的表 字段内容一样 可以放在一起显示 用union或者union all

union 是 union all 的去重结果

mysql> select uid from order_goos union select uid from users;
+-----+
| uid |
+-----+
|   2 |
|   1 |
|   3 |
|   5 |
|   8 |
|   4 |
+-----+
6 rows in set (0.00 sec)

mysql> select uid from order_goos union all select uid from users;
+-----+
| uid |
+-----+
|   2 |
|   1 |
|   3 |
|   5 |
|   2 |
|   1 |
|   8 |
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
12 rows in set (0.00 sec)

mysql> select uid,name from order_goos union all select uid,username from users;
+-----+--------------+
| uid | name         |
+-----+--------------+
|   2 | 苹果鼠标     |
|   1 | skii精华     |
|   3 | 乳霜         |
|   5 | 寇驰包包     |
|   2 | 阿玛尼       |
|   1 | iPhone12     |
|   8 | 平板电脑     |
|   1 | 迪丽热巴     |
|   2 | 古力娜扎     |
|   3 | 热依扎       |
|   4 | 蒙娜丽莎     |
|   5 | 珍塔玛莎     |
+-----+--------------+
12 rows in set (0.00 sec)

修改update

update 表名 set 字段=值 where 条件;

删除 delete

drop table 表名 #删除表结构和数据

delete from 表名 #删除整个表的内容

delete from 表名 [where 条件] #根据条件删除指定内容