SQL学习_sql是一套标准

SQL学习

1. 数据库概述

1.1 SQL（Structured Query Language）概述
SQL和数据库打交道，完成与数据库的通信。SQL是一套标准，即每个数据库都兼容SQL，但是每个数据库都有自己的特性，当你使用这些特性时，SQL语句可能就不是标准了。
1.2 数据库（DataBase，简称DB）
数据库，通常是一个或一组文件，保存了一些符合特定规格的数据。数据库软件称为数据库管理系统（DBMS），全称为DataBase Management System,如Oracle，SQL Sever，MySQL，Sybase，informix,DB2,interbase,PostgreSql。

1.3 SQL、DBMS、DB之间的关系
DBMS 通过执行 SQL语句 操作 DB

2. SQL语句分类

2.1 DQL 数据查询语言（凡是带有SELECT关键字的都是查询语句）
关键字：

SELECT
1

2.2 DML 数据操作语言 （凡是对表中的数据进行增删改的都是DML）
关键字：

INSERT 增
DELETE 删
UPDATE 改
1
2
3

2.3 DDL 数据定义语言（主要操作的是表的结构，如表的字段）
关键字：

CREATE 新建
DROP 删除
ALTER 修改
1
2
3

2.4 TCL 事务控制语言
关键字：

COMMIT 事务提交
ROLLBACK 事务回滚
1
2

2.5 DCL 数据控制语言
关键字：

GRANT 授权
REVOKE 撤销权限
1
2

3. 语法概述

//所有条件中的符号或关键字：
关系：= , > , < , >= , <= , != , <> 
逻辑：or , and 
//and 优先级高于 or ,所以在同时使用 and 和 or 时为避免错误，用()区分优先级；
区间：id between 4 and 6 //即：闭区间[4,6]。使用时必须遵循左小右大
为空：is null 
不为空：is not null//数据库中null不可以用=衡量，所以“=null”是不行的。
包含：in(值1，值2，...) //相当于多个 or 
不包含：not in(值1，值2，...) 
模糊查询：like // _ 和 % 都是特殊符号。 _ 代表一个字符， % 任意个字符

例子：
select name from 表名 where name like '_a%';  条件为字段name中的值的第二个字母为'a'
select name from 表名 where name like '__a%';  条件为字段name中的值的第三个字母为'a'
select name from 表名 where name like '%a%';  条件为字段name中的值的带有字母'a'
select name from 表名 where name like '%a';  条件为字段name中的值的最后一个字母为'a'
select name from 表名 where name like 'a%';  条件为字段name中的值的第一个字母为'a'
select name from 表名 where name like '%\_%';  条件为字段name中的值的带有下划线 _ (\表示转义)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

语句执行顺序：
1.from
2.where
3.select
4.order by

··············DQL语句···········
SELECT 列名 FROM 表名； // * 表示通配符,如果表名称为 * ，则查询整个表
//* 一般不使用。因为在java代码中，* 最终还是要转换为关键字，这样效率会变低。
select 列名1 as 别名1，列名2 as 别名2，列名3 from 表名；//查询多个字段
/* 
as 关键字用来给查询后的列起别名，只是在显示时为别名，实际原列表的字段未改变。
as 也可以省略，“表名 别名”和“表名 as 别名”相同作用。
如果别名中带空格或者别名是中文，只需要用''或者""把别名括起来就可以了。
注意：在所有数据库中，字符串统一用''括起来，''是标准，""在oracle数据库里面用不了，但是在MySQL中可以使用。
*/
select 字段 from 表名 where 条件;//条件查询

select 字段*k from 表名；
select 字段+k from 表名；
select 字段-k from 表名；
select 字段/k from 表名；
/*字段可以参与算数运算,但是它会自动把字段名起别名，如
select 字段*k from 表名；结果中字段名变为“字段*k”。
*/


//------------排序------------
select 字段 from 表名 order by 字段 desc;//降序
select 字段 from 表名 order by 字段 asc;//升序（默认）
select 字段 from 表名 order by 字段1  desc,...字段n desc;//多字段排序，其中desc可以换为asc。
 //多字段排序的规则：从左到右，先按照左边第一个字段排：如果第一个字段中的值有相同，再按照第二个字段排序，以此类推。
 select 字段 from 表名 order by k;//k表示第k列，按照第k列查询。(不建议使用，因为列的顺序可能改变)
 
 //---------------统计-------------
select * from 表 group by 字段；//只分组:
 例子: select count(sex) as re,sex from star group by sex having re > 3;
select count(sex) from star group by sex;//分组统计

DESC 表名；//只看表结构，没有数据

···············DML语句·············
//----------增-----------
insert into 表名 values(值1，值2，...)；
insert into 表名(字段1，字段2，...) values(值1，值2...)；
insert into 表名(字段1，字段2，...) values(值1，值2...),(值1，值2,...),...；
//----------删------------
delete from 表名 where 条件；
//-----------改-----------
update 表名 set 字段1 = 值1，字段2 = 值2,... where 条件；


·················DDL语句············
(我们可以在Navicat里面通过“设计表”来完成DDL语句的操作)
//----------增-----------
create table 表名(字段1 类型，字段2 类型...);
//----------删------------
drop table 表名;
//-----------改-----------
alter table 表名 modify 字段 字段类型;//修改字段类型
alter table 表名 add 字段 字段类型;//添加新的字段
alter table 表名 add 字段 字段类型 after 字段;//添加字段并指定位置 
alter table 表名 drop 字段名;//删除表字段 
alter table 表名 change 原字段名字  新的字段名字 字段类型//修改指定的字段


··············DCL语句············
create user'xiaoming' @ 'localhost' identified by '666666';//创建用户
grant all on test.*to 'xiaoming' @ 'localhost';//授权用户
flush privileges;//刷新权限
revoke all on test.* from 'xiaoming' @ 'localhost';//取消授权
drop user'xiaoming' @ 'localhost';//删除用户


··············DTL语句：···············
set autocommit=0;//开启事务：
rollback;//操作回滚：
commit;//提交事务：
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

4 DQL语句

4.1 单行处理函数

特点：一个输入对应一个输出（即所处理的数据只能在一行中取得）

lower(str) //转换小写
select lower() from 表名；
upper(str) //转换大写

length(str) //取长度

SUBSTR(str FROM pos FOR len) //取子串从pos截取len个长度
SUBSTR(str FROM pos) //取子串从pos截取到结束
//str表示被截取的字段,pos表示开始的下标(MySQL中的下标从1开始),len表示截取的长度
concat(str，str) //字符串拼接

trim(str) //去空格

format() //设置千分位

round(X,D)//四舍五入(X为数据，D>=0,则D为保留的小数位数；D<0,则D为保留的整数位数，即：从个位开始数D位，使得它四舍五入)
/*例子：
select round('1236.567',0) from 表名;//结果为1236
select round('1236.567',2) from 表名;//结果为1236.56
select round('1236.567',-1) from 表名;//结果为1240；
select round('1236.567',-2) from 表名;//结果为1200
*/

rand() //生成随机数

ifnull(expr1,expr2) //如果expr1为null,则把他的值设置为exper2
/* 数据库中只要有null参与的数学运算，结果都为null */

case 字段 when 值1 then.. when 值2 then..else..end;//相当于C语言里面的switch结构,每个when..then相当于if语句
/*例子：
select ENAME,job,sal as oldsal,
(case job when 'CLERK' then sal*1.3 when 'SALESMAN' then sal*0.9 else sal end) as newsal from emp;
改语句的结果是：如果 job 是 'CLERK' ,则 sal*1.3 ；如果 job 是 'SALESMAN' ，则 sal*0.9 ；其他情况 sal 。
*/

str_to_date(str,format) //将字符串转换为日期
date_format(date,format) //格式化日期 date为日期字段
	其中format的各种格式
%W 星期名字(Sunday……Saturday)  
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）  
%Y 年, 数字, 4 位  
%y 年, 数字, 2 位  
%a 缩写的星期名字(Sun……Sat)  
%d 月份中的天数, 数字(00……31)  
%e 月份中的天数, 数字(0……31)  
%m 月, 数字(01……12)  
%c 月, 数字(1……12)  
%b 缩写的月份名字(Jan……Dec)  
%j 一年中的天数(001……366)  
%H 小时(00……23)  
%k 小时(0……23)  
%h 小时(01……12)  
%I 小时(01……12)  
%l 小时(1……12)  
%i 分钟, 数字(00……59)  
%r 时间,12 小时(hh:mm:ss [AP]M)  
%T 时间,24 小时(hh:mm:ss)  
%S 秒(00……59)  
%s 秒(00……59)  
%p AM或PM  
%w 一个星期中的天数(0=Sunday ……6=Saturday ）  
%U 星期(0……52), 这里星期天是星期的第一天  
%u 星期(0……52), 这里星期一是星期的第一天  
%% 一个文字“%”。  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

4.2 多行处理函数（分组函数）

特点：多个输入对应一个输出（即处理的数据可以跨多个行）

以下函数必须先分组后使用。如果不分组，那么整张表默认为一组
count() //计数
sum() //求和
avg() //平均值
max() //最大值
min() //最小值

**************************
注意：
1、分组函数在处理数据时自动忽略null,即null不参加数学运算
2、count(*) 与 count(字段) 的区别：
	count(*) 统计表中总行数，即如果有一行数据，则count++；
	count(字段) 统计该字段下不为 null 的元素总数
3、分组函数不能直接在 where 子句中使用
	原因：分组函数必须先分组后使用，而SQL语句执行顺序如下：
	1.from
	2.where
	3.group by
	4.having
	5.select
	6.order by
	即：如果在where子句中使用分组函数 ,就是在分组（group by）前就使用了分组函数 ,这样会报错.
4、所有的分组函数可以组合起来一起用
	例子：select max(字段),min(字段),count(字段),... from 表名；
*****************************
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.3 分组查询

在实际应用中，可能需先 分组 后对每一组数据操作

select ... from ... group by ... ;

select ... from ... group by ... having ... ;

****************************
注意：
1、在一条select语句中，如果有group by 语句，则 select 后面只能跟： 参加分组的字段 以及 分组函数 ，其他的一律不能跟!
2、group by 后面如果跟了多个字段(即联合分组)，则从左到右，依次作为标准来分组
3、having 子句可以对分组后的数据进一步过滤
	注:having 不可以单独使用，必须和 group by 联合使用;
	   having 不可以代替 where;
	   having 比 where 的特殊在于 having 后可以跟分组函数
4、优化策略：where 和 having 优先选择 where，在 where 无法完成的情况下再使用 having。
****************************
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4.4 连接查询

概念：多表联合起来查询，即为联合查询。

分类：

根据年代:
SQL92：1992年出现的语法。
SQL99：1999年出现的语法。

根据表连接方式：
内连接：
			等值连接
			非等值连接
			自连接
外连接：
			右外连接（右连接）
			左外连接（左连接）
全连接：
1
2
3
4
5
6
7
8
9
10
11
12
13

distinct //去重(只能出现在所有字段前面)  如果distinct 后面出现多个字段，则表示多个字段联合去重
1

笛卡尔积现象：两张表联合查询，且无任何限制条件，最终查询结果的条数是两张表条数的乘积。

select name,job from emp,student;
如果emp里面有5条数据，student里面有4条数据，则结果是4*5=20条数据
1
2

如何避免：加条件

select name,job from emp,student where emp.id=student.id;
注意：
1、实际两张表的匹配次数位减少，只是显示了符合条件的结果
2、提高效率的方法：起别名。如
	select e.name,s.job from emp e,student s where e.id=s.id;
	//SQL92语法
	//这样的结果字段名不会变为别名
	//select后面使用 e.name,s.job 可以减少表的连接次数，提高效率
1
2
3
4
5
6
7
8

4.4.1 内连接

特点：完全能匹配到的数据可以查询到，所查的表无主次关系

1、等值连接：表与表的连接条件为 等值关系

select ... from 表名1 inner join 表名2 on 表名1....= 表名2.... where ... ;
// inner 表示内连接，写了它代码可读性高。它可以省略
1
2

2、非等值连接：表与表的连接条件为 非等值关系

例如：
select ... from 表名1 inner join 表名2 on 表名1.... between 表名2.... and 表名2.... where ... ;

1
2
3

3、自连接：要找的信息在同一张表里面，这时我们需要把一张表看为两张表。（用起别名来实现）

例子：
select a.ENAME,b.ENAME MGR from emp a join emp b on a.EMPNO=b.MGR; 
//找出员工名及他的上级的名字
1
2
3

emp 表

查询结果：

4.4.2 外连接

1、右外连接(右连接)

select ... from ... right join ... on ... ;
//right 代表jion 关键字 右边 的表为主表，主要是为了将这张主表的数据查询初来，左边 的表的数据是 “捎带着” 关联查询出来。
即：如果右边表有一条数据在左边表里面找不到对应，那么结果中左边表那一条显示为null；
例子：
select e.ename,d.dname,d.DEPTNO from emp e right outer join dept d on e.DEPTNO=d.DEPTNO;
// outer 表示外连接，写了它代码可读性高。它可以省略
1
2
3
4
5
6

emp表

dept表

查询结果：

2、左外连接（左连接）

select ... from ... left join ... on ... ;

例子：
select e.ename,d.dname,d.DEPTNO from emp e left outer join dept d on e.DEPTNO=d.DEPTNO;
1
2
3
4

查询结果：

4.4.3 多连接

select ...
from 
	a
join 
	b
on
	a和b的连接条件1
join 
	c
on 
	a和c的连接条件2
join 
	...
;

//内连接与外连接可以混合使用
/*
解释：
a和b按照条件1连接 --> 
[a和b按照条件1连接的 结果] 和c按照条件2连接 --> 
[(a和b按照条件1连接的结果)和c按照条件2连接的 结果] 和...按照条件...连接 -->
...
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

4.5 子查询

概念：select 里面嵌套select，被嵌套的select 语句为子查询。

例子：
select 
	...(select )
from 
	...(select )
where
	...(select )
...	
1
2
3
4
5
6
7
8

4.5.1 where子句中的子查询

例子：
select ename,sal from emp where sal>(select avg(sal) from emp); 查询工资高于平均工资的员工的名字
1
2

4.5.2 from子句中的子查询

注意：from后面的的子查询，可以将子查询的查询结果当作一张临时表。

例子：找出每个岗位的平均工资的薪资等级，并按照降序排序，要求显示岗位以及对应的薪资等级
（
分析：可以先查询每个岗位的平均工资，显示出岗位及平均薪资：然后将它作为一张临时表，和salgrade表连接。
注意：临时表产生的字段含有关键字，一定要给它起别名，不然无法获取
）
select 
	k.job,s.GRADE 
from 
	(select job,avg(sal) avgsal from emp group by job) k //因为临时表字段中含有'avg'关键字，所以应该起别名
join 
	salgrade s 
on
	 k.avgsal between s.LOSAL and s.HISAL 
order by s.grade desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4.5.3 select 子句的子查询

使用规则：子查询中不可以返回多条数据，否则会报错

例子：
select a.ename,(select d.dname from dept d where a.DEPTNO=d.DEPTNO) as dname from emp a;//找出员工的部门名，要求显示员工名及其部门名

select e.ename,e.DEPTNO,(select d.dname from dept d) as dname from emp e;//这条语句会报错
1
2
3
4

4.6 union 合并查询结果集

union可以实现表的拼接。（它的效率要高于条件查询）
规则：要求合并的两个结果集的列数相同，列与列的数据类型相同

select ... from ...
union 
select ... from ... ;

例子：
select ename,job from emp where job='MANAGER'
union 
select ename,job from emp where job='SALESMAN';
的效率高于：
select ename,job from emp where job='MANAGER' or job='SALESMAN';
1
2
3
4
5
6
7
8
9
10

效率高的原因：
如果是表连接，那么每连接一次新表，则匹配的次数满足笛卡尔积；
用union实现表的拼接，可以减少表的匹配次数。

举例：
a有10条记录 ；b有10条记录；c有10条记录。现在实现：a 连接 b 连接 c 。
则：
表连接：
最终匹配次数：101010=1000；
用union表合并：
匹配次数：a 连接 b ：1010=100；
匹配次数：a 连接 c ： 1010=100；
最终匹配次数：100+100=200；

4.7 limit 分页

将查询的结果集的一部分取出来，通常使用在分页当中。

//注意：limit 在 order by 后面执行
limit startIndex,length //startIndex为起始下标，默认从0开始取，length是长度
limit length //length是长度(默认从0开始取)

分页公式（即显示每一页数据的SQL语句中的分页部分）：
pageSize为每页显示的数据的条数，pageNo第几页
startIndex = (pageSize-1)*pageNo;
length = pageSize;
1
2
3
4
5
6
7
8

5 DDL语句

数据类型：

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

5.1 表的创建

create table 表名(字段名1 数据类型,字段名2 数据类型,字段名3 数据类型,...);

表名：建议以t_或者tbl_开始，可读性强，见名知意
字段名：可读性强，见名知意
//表名、字段名都是标识符
1
2
3
4
5