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)
多表联合查询
-
内连接
select 表1.字段名,表2.字段名,表n.字段名 where 条件 ;
-
外连接
-
左连接
select 表1.字段,表2.字段 from 表1 left join 表2 on 条件;
-
右连接
select 表1.字段,表2.字段 from 表1 right join 表2 on 条件;
-
多表查询
select 表1.字段1,表n.字段 from 表名1,表名2,表3 where 表1.字段=表2.字段 and 表2.字段=表3.字段;
-
子查询
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 条件] #根据条件删除指定内容