大学的时候学习了mysql数据库,当时感觉挺简单的。但是后面的开发过程中,一直用的spingdatajpa,sql语句用的很少。现在系统的总结一下:
1.查询
单表查询
1.全字段查询
select * from t1;
- 语法:select * from 表名;
2.单表个别字段查询
select id from t1;
- 语法:select 字段一,字段二 from 表名;
3.多表查询
select t1.id,t1.score,winton.name from t1,winton;
条件查询
1.单表条件查询
select * from t1 where socre>90;
- 语法:select 字段1,字段2 from 表名 where 条件;
2.多表条件查询
select t1.id,t1.score,winton.name from t1,winton where t1.id=winton.id;
语法:select 表一字段,表二字段 from 表一,表二 where 条件;
3.嵌套查询
select name from winton where id=(select id from t1 where score=90);
语法:select 字段一,字段二…… from 表名 where 条件(查询);
4.并查询
UNION 操作符合并两个或多个 SELECT 语句的结果。
(水之队,又加入了一个成员,也叫小珩,即一个小组内出现了成员名字相同的情况)
5.union用法
1.union目的:查询出members表中group_id = 1和group_id为null的成员:
select m.member_name from members m where m.group_id = 1
union
select m.member_name from members m where m.group_id is null;
- 结果:
分析:将上面查询的结果和下面的查询结果组合在了一起。会发现一个问题,“小珩”这条数据只展示了一遍,但是我们有两个小珩呀。所以union会对查到的结果进行去重处理。
2.union all
目的:解决union遇到的问题,将所有查到的结果查询出来:
select m.member_name from members m where m.group_id = 1
union all
select m.member_name from members m where m.group_id is null;
- 结果:
分析:可以看到“小珩”的数据展示了两次,即union all会将查到的结果直接全部展示出来。
- join联结的两个表,是通过匹配两个表中相同字段的相同的值,得到的结果集。可以认为是结果集的列拓宽了。join是使得查询的内容更详细了。
- union是取将两个结果集的并集,但两个结果集所拥有的列数是一样、列的顺序也一样、字段类型相同或相似(DBMS认为的相似),才能union并展示在一起。可以认为是结果集的行数多了。union是使得查询的结果更多了。
2.删除
delete from winton where id=4;
- 1语法:delete from 表名 where 条件;
更新
update t1 set score=69 where id=2;
- 1语法:update 表名 set 更改的字段名=值 where 条件;
3.常用函数
1.求和
select sum(score) from t1;
- 1注:sum(字段) 对字符串和时间无效
2.求平均值
select avg(score) from t1;
- 1注:avg(字段)对字符串和时间无效
3.计数
select count(*) from t1;
- 1注:count(字段名)不包含NULL;
4.求最大值
select max(name) from winton;
- 注:max(colunm)返回字母序最大的,返回数值最大的
5.求最小值
select min(name) from winton;
- 注:min(colunm)返回字母序最小值,返回数值最小值
4.常用的修饰符
select distinct name from winton;
2.limit查询结果数限制
下面是选自 "Websites" 表的数据:
mysql> SELECT * FROM Websites;
+----+---------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 5000 | USA |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
下面的 SQL 语句从 "Websites" 表中选取头两条记录:
实例 SELECT * FROM Websites LIMIT 2;
执行以上 SQL,数据如下所示:
3.order by 排序
select * from winton order by name;
- 注:默认是升序
desc 降序
slelect * from winton order by name desc;
- asc 升序
select * from winton order by name asc;
4.group by 分组
select name from winton group by name;
5.索引
创建普通索引
create index wintonIndex on winton (name);
- 1语法:create index 索引名称 on 表名 (字段一,字段二,……);
创建唯一索引
create unique index wintonIndex on winton (id);
- 1语法:create unique index 索引名 on 表名 (字段一,字段二,……);
ps:unique index 要求列中数据唯一,不能出现重复。
移除索引
drop index wintonIndex on winton;
- 1语法: drop index 索引名 on 表名;
6.left join和right join
left join:
返回包括左表中的所有的记录和右表连接字段相等的记录
select * from A left join B on A.id = B.id
因为left join是以左表为主表,所以只要左表有数据,不管右表有没有数据
(如果右表没有数据则为null),查询结果都会存在
right join:
返回包括右表中的所有的记录和左表连接字段相等的记录
select * from A right join B on A.id = B.id
inner join:
等值连接,只返回两个表中连接字段相等的值
left join练习
逻辑是组织下面有部门,部门下面有用户,组织和部门通过organization_id字段关联,
部门和用户通过department_id关联
组织表(t_organization)
部门表(t_department)
用户表(t_user)
(1)两张表的查询
查询所有组织信息以及下属中所有部门信息
-
select * from
-
t_organization AS o
-
left join
-
t_department AS d
-
on
-
o.organization_id = d.organization_id;
查询结果如下:
(2)三张表查询
查询所有组织信息以及下属所有部门信息以及下属所有用户信息:
-
select * from
-
t_organization AS o
-
left join
-
t_department AS d
-
ON
-
o.organization_id = d.organization_id
-
left join
-
t_user AS u
-
ON
-
d.department_id = u.department_id;
查询结果如下: