1.1数据概念
- 什么是数据库
数据就是存储数据的仓库，其本质就是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库的数进行增加，修改，删除及查询等
- 什么是数据管理系统
数据管理系统：指一种操作和管理数据的大型软件，用于建立，使用和维护数据库，对数据库进行统一管理和控制，一保证数据库的安全性和完整性。用户通过数据据管理系统访问数据库中表的数据
- 数据库与数据库管理系统的关系
1.2SQL语句分类
- 数据定义语言：简称DDL，用来定义数据库对象：数据库，表，列等。关键字：create ，alter ，drop等
- 数据操作语言：简称DML，用来对数库中表的记录进行更新，关键字：insert ，delete ，update等
- insert into 表名 (列1 ,列2) values (值1 ,值2)
- update 表名 set 列名1=值1 , 列名2=值2
- delete from 表名 where 列名= 值
- 数据控制语言:简称DCL,用来定义数据库的访问权限和安全级别及创建用户
- 数据查询语言：简称DQL，用来查询数据库中表的记录，关键字：select ， from ，where等
select xxx from xxx where xxx group by xxx having xxx order by xxx limit xxx
where使用的关键字：= ，！= ，<> ，< , > ,>= , <= ,in ,between xxx and xxx , is full , is not full ,and ,or ,not
通配符：_ 、% 例如：like'%张%' 、like'张_' ，like'张三%'
SQL通用语法
- SQL语句可以单行或多行,以分号结尾
-
可使用空格和缩进增强语句的可读性
-
MySQL数据的SQL语句不区分大小写,关键字建议使用大写
-
SELECT * FROM user;
- 同样使用/**/的方式完成注释
- MySQL中我们常用的的数据类型
| 类型名称 | 说明 |
| int(integer) | 整数类型 |
| double | 小数类型 |
| float | 小数类型 |
| decimal(m,d) | 指定整数位于小数位长度的小数类型 |
| date | 日期类型,格式yyyy-MM-dd,包含年月日,不包含时分秒 |
| datetime | 日期类型,格式yyyy-MM-dd HH:MM:SS,包含年月日时分秒 |
| timestamp | 日期类型,时间戳 |
| varchar | 文本类型 |
2.2DDL之数据库操作:database
创建数据库
格式： create database 数据库名; create database 数据库名 character set 字符集; 例如： #创建数据库 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8 CREATE DATABASE webdb_1; #创建数据库 并指定数据库中数据的编码 CREATE DATABASE webdb_2 CHARACTER SET utf8;
查看数据库
查看数据库MySQL服务器中的所有的数据库: show databases; 查看某个数据库的定义的信息: show create database 数据库名; 例如： show create database webdb_1;
删除数据库
drop database 数据库名称; 例如： drop database webdb_2
使用数据库
查看正在使用的数据库: select database(); 其他的数据库操作命令 切换数据库： use 数据库名; 例如： use webdb_1;
2.3DDL之表操作:table
创建表
格式： create table 表名( 字段名 类型(长度) [约束], 字段名 类型(长度) [约束], ... );
常用数据类型：
数值类型
int 整型 (0,4291967295) bigint 极大整型 (0，18 446 744 073 709 551 615) double 浮点型，例如：double(5,2)表示最多5为，其中必有2位小数，及最大值为999.99
字符串类型
char 固定长度字符串类型；char(10) 不常用 varchar(n) 可变长度字符串类型；varchar(10) 常用 text 字符串类型；大数据量类型。
其他类型
blob 字节类型；适合存：图片、音频、视频
日期和时间类型
date 日期类型，格式为：yyyy-MM-dd time 时间类型，格式为：hh:mm:ss timestamp 时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值 datetime 日期时间类型 yyyy-MM-dd hh:mm:ss
解析：int（11）代表什么意思？
11代表的并不是长度，而是字符显示宽度。在字段类型为 int 时，无论你显示宽度设置为多少，int 类型能存储的最大值和最小值永远都是固定的
结论：
1、如果一个字段设置了无符号和填充零属性，那么无论这个字段存储什么数值，数值的长度都会与设置的显示宽度一致，如上述例子中的字段 b，插入数值 1 显示为00000000001，左边补了 10 个零直至长度达到 11 位；
2、设置字段的显示宽度并不限制字段存储值的范围，比如字段 d 设置为 int(5)，但是仍然可以存储 1234567890 这个 10 位数字；
3、设置的字符宽度只对数值长度不满足宽度时有效，如 d 字段 int(5)，插入 1 时，长度不足 5，因此在左边补充 4 个零直到 5 位，但是插入 1234567890 时超过了 5 位，这时的显示宽度就起不了作用了。
约束：
primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。
- 例如：创建分类表
CREATE TABLE category ( cid INT primary key, #分类ID cname VARCHAR(100) #分类名称 )ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='xxxxxxx';
--ENGINE=InnoDB 存储引擎=InnoDB，记录是以行的形式存储的
--DEFAULT CHARSET=utf8 默认数据集=utf8
--ROW_FORMAT=COMPACT
row_format就代表了当前使用的行记录结构类型
InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据
--COMMENT='xxxxxxx'
查看表
查看数据库中的所有表： 格式：show tables; 查看表结构： 格式：desc 表名; 例如：desc category;
删除表
格式：drop table 表名; 例如：drop table category;
修改表结构格式
- alter table 表名 add 列名 类型(长度) [约束];
作用：修改表添加列.
例如：#1，为分类表添加一个新的字段为 分类描述 varchar(20)
ALTER TABLE category ADD `desc` VARCHAR(20);
- alter table 表名 modify 列名 类型(长度) 约束;
作用：修改表修改列的类型长度及约束.
例如：#2, 为分类表的描述字段进行修改，类型varchar(50) 添加约束 not null
ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;
- alter table 表名 change 旧列名 新列名 类型(长度) 约束; 作用：修改表修改列名.
例如：#3, 为分类表的分类名称字段进行更换 更换为 description varchar(30)
ALTER TABLE category CHANGE `desc` description VARCHAR(30);
- alter table 表名 drop 列名;
作用：修改表删除列.
例如：#4, 删除分类表中description这列
ALTER TABLE category DROP description;
- rename table 表名 to 新表名;
作用：修改表名
例如：#5, 为分类表category 改名成 category2
RENAME TABLE category TO category2;
- alter table 表名 character set 字符集(了解);
作用：修改表的字符集
例如：#6, 为分类表 category 的编码表进行修改，修改成 gbk
ALTER TABLE category CHARACTER SET gbk;
2.4 DML数据操作语言
插入表记录：insert
- 语法：
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values (值1,值2,值3..);
-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values (值1,值2,值3..);
- 注意：
- 值与字段必须对应，个数相同，类型相同
- 值的数据大小必须在字段的长度范围内
- 除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
- 如果要插入空值，可以不写字段，或者插入null。
- 例如：
INSERT INTO category(cid,cname) VALUES('c001','电器');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','书籍');
INSERT INTO category(cid) VALUES('c005');
INSERT INTO category(cname,cid) VALUES('耗材','c006');
更新表记录：update
用来修改指定条件的数据，将满足条件的记录指定列修改为指定值
- 语法：
- 更新所有记录的指定字段
update 表名 set 字段名=值,字段名=值,...;
- 更新符合条件记录的指定字段
update 表名 set 字段名=值,字段名=值,... where 条件;
- 注意：
- 列名的类型与修改的值要一致.
- 修改值得时候不能超过最大长度.
- 除了数值类型外，其它的字段类型的值必须使用引号引起
删除记录：delete
- 语法：
delete from 表名 [where 条件];
2.5 DOS操作数据乱码解决
我们在dos命令行操作中文时，会报错
insert into category(cid,cname) values(‘c010’,’中文’);
ERROR 1366 (HY000): Incorrect string value: 'xB7xFExD7xB0' for column 'cname' at row 1
错误原因:因为mysql的客户端设置编码是utf8,而系统的cmd窗口编码是gbk
- 查看MySQL内部设置的编码
show variables like 'character%';
查看所有mysql的编码
- 需要修改client、connection、results的编码一致（GBK编码）
解决方案1：在cmd命令窗口中输入命令，此操作当前窗口有效，为临时方案。
set names gbk;
解决方案2：安装目录下修改my.ini文件，重启服务所有地方生效。
3.SQL约束
3.1 主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
- 主键必须包含唯一的值。
- 主键列不能包含 NULL 值。
- 每个表都应该有一个主键，并且每个表只能有一个主键。
添加主键约束
- 方式一： 在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)
缺点:只能使用一个字段作为主键
CREATE TABLE persons (
id_p int PRIMARY KEY,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)
- 方式二：创建表时，在所有字段之后,使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键,可以是符合主键)
CREATE TABLE persons (
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255),
PRIMARY KEY (firstname,lastname)
)
- 方式三：创建表之后，通过修改表结构，声明指定字段为主键：
- 格式：ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
CREATE TABLE persons (
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255)
)
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)
或者
AlTER TABLE person ADD CINSTRAINT pk_id PRIMARY KEY (firstname,lastname)
删除主键约束
如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
ALTER TABLE persons DROP PRIMARY KEY
在实际创建表的时候,很少使用真实业务数据作为主键(业务主键:如学号,课程号);大部分时候是使用逻辑性字段(字段没有业务含义,值是什么都没有关系),将这种字段主键成为逻辑主键
3.2 自动增长列
我们通常希望在每次插入新记录时，数据库自动生成字段的值。
我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。
- 下列 SQL 语句把 "persons" 表中的 "p_id" 列定义为 auto_increment 主键
CREATE TABLE persons (
p_id int PRIMARY KEY AUTO_INCREMENT,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)
- 向persons添加数据时，可以不为p_id字段设置值，也可以设置成null，数据库将自动维护主键值：
INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates') INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates')
- 扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：
ALTER TABLE persons AUTO_INCREMENT=100
- 面试题
问：针对auto_increment ，删除表中所有记录使用 delete from 表名 或使用 truncate table 表名，二者有什么区别？
删除方式：
delete 一条一条删除，不清空auto_increment记录数。
truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。
3.3 非空约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
- 下面的 SQL 语句强制 "id_p" 列和 "lastname" 列不接受 NULL 值：
CREATE TABLE persons ( id_p int NOT NULL, lastname varchar(255) NOT NULL, firstname varchar(255), address varchar(255), city varchar(255) )
3.4 唯一约束
UNIQUE 约束唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。 请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束；而且本质的区别就是唯一值默认允许为空，而且是多个为空
添加唯一约束
与主键添加方式相同，共有3种
- 方式一：创建表时，字段之后直接跟unique/unique key
CREATE TABLE persons ( id_p int UNIQUE, lastname varchar(255) NOT NULL, firstname varchar(255), address varchar(255), city varchar(255) )
- 方式二：创建表时，在所有字段之后增加unique key(字段列表)
CREATE TABLE persons ( id_p int, lastname varchar(255) NOT NULL, firstname varchar(255), address varchar(255), city varchar(255), UNIQUE (id_p) )
- 方式三：创建表后，修改表结构，声明字段唯一：
ALTER TABLE persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)
删除唯一约束
- 如需撤销 UNIQUE 约束，请使用下面的 SQL：
ALTER TABLE persons DROP INDEX 名称
例如
ALTER TABLE persons DROP INDEX firstnanme;
- 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。
3.5 默认约束
default 约束 用于指定字段默认值。当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值。
- 添加默认约束，在创建表时候添加
CREATE TABLE student( id int pirmary key, name varchar(50) not null, sex varchar(10) default '男' ); -- 批量插入(MySQL中的方式，别的数据库软件不好使哦) INSERT INTO student1 values(1,'jerry','女'); INSERT INTO student1 values(2,'tom',default); -- sex插入的是默认值 INSERT INTO student1 values(3,'bruce',null); -- sex插入的是null值
- 删除默认约束
ALTER TABLE t_user MODIFY user_id INT(10);
4.SQL语句(DQL)
4.1 DQL准备工作和语法
DQL数据查询语言（重要）
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。
查询关键字：SELECT
语法：SELECT 列名 FROM 表名;
WHERE --> GROUP BY --> HAVING --> ORDER BY
语法：
SELECT selection_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果分组*/
LIMIT offset_start, row_count /*结果限定*/
- 学生表：stu
|
字段名称 |
字段类型 |
说明 |
|
sid |
char(6) |
学生学号 |
|
sname |
varchar(50) |
学生姓名 |
|
age |
int |
学生年龄 |
|
gender |
varchar(50) |
学生性别 |
|
CREATE TABLE stu ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) ); |
||
|
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male'); INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female'); INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male'); INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female'); INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male'); INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female'); INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male'); INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female'); INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male'); INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female'); INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL); |
||
- 雇员表：emp
|
字段名称 |
字段类型 |
说明 |
|
empno |
int |
员工编号 |
|
ename |
varchar(50) |
员工姓名 |
|
job |
varchar(50) |
员工工作 |
|
mgr |
int |
领导编号 |
|
hiredate |
date |
入职日期 |
|
sal |
decimal(7,2) |
月薪 |
|
comm |
decimal(7,2) |
奖金 |
|
deptno |
int |
部分编号 |
|
CREATE TABLE emp ( empno INT, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), comm decimal(7,2), deptno INT ); |
|
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO emp VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO emp VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO emp VALUES(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL,20); INSERT INTO emp VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400,30); INSERT INTO emp VALUES(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL,30); INSERT INTO emp VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL,10); INSERT INTO emp VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL,20); INSERT INTO emp VALUES(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL,10); INSERT INTO emp VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0,30); INSERT INTO emp VALUES(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); INSERT INTO emp VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO emp VALUES(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL,10); |
- 部分表：dept
|
字段名称 |
字段类型 |
说明 |
|
deptno |
int |
部分编码 |
|
dname |
varchar(50) |
部分名称 |
|
loc |
varchar(50) |
部分所在地点 |
SELECT * FROM stu WHERE sname LIKE '____i';
|
CREATE TABLE dept( deptno INT, dname varchar(14), loc varchar(13) ); |
|
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS'); INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO'); INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON'); |
1 基础查询
1.1 查询所有列
SELECT * FROM stu;
1.2 查询指定列
SELECT sid, sname, age FROM stu;
2 条件查询
2.1 条件查询介绍
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>= BETWEEN...AND； IN(set); NOT IN(set); 可以有多个值，用逗号隔开 IS NULL 、IS NOT NULL AND; OR; NOT;
2.2 查询性别为女，并且年龄50的记录
SELECT * FROM stu WHERE gender='female' AND age=50;
2.3 查询学号为S_1001，或者姓名为liSi的记录
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';
2.4 查询学号为S_1001，S_1002，S_1003的记录
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');
2.5 查询学号不是S_1001，S_1002，S_1003的记录
SELECT * FROM stu WHERE sid NOT IN ('S_1001','S_1002','S_1003');
2.6 查询年龄为null的记录
SELECT * FROM stu WHERE age IS NULL;
2.7 查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age>=20 AND age<=40; SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
2.8 查询性别非男的学生记录
SELECT * FROM stu WHERE gender!='male'; SELECT * FROM stu WHERE gender<>'male'; SELECT * FROM stu WHERE NOT gender='male';
2.9 查询姓名不为null的学生记录
SELECT * FROM stu WHERE sname IS NOT NULL; SELECT * FROM stu WHERE NOT sname IS NULL;
3 模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
通配符:
_：任意一个字符
%：任意0~n个字符
例如： '%张%' 小张、张、张三、小张三、小小张、小小张三丰
'张_' 张三、张四
'张%' 张、张三、张三丰、张三小丰
3.1 查询姓名由5个字母构成的学生记录
SELECT * FROM stu WHERE sname LIKE '_____';
模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母，5个“_”表示5个任意字母。
3.2 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '____i';
3.3 查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%'; 其中“%”匹配0~n个任何字母。
3.4 查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';
3.5 查询姓名中包含“a”字母的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';
4 字段控制查询
4.1 去除重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT
SELECT DISTINCT sal FROM emp; SELECT DISTINCT sal,mgr FROM emp;
4.2 查看雇员的月薪与佣金之和
因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。
SELECT *,sal+comm FROM emp;
comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：
SELECT *,sal+IFNULL(comm,0) FROM emp;
4.3 给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total，
SELECT *,sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时，是可以省略AS关键字的。
SELECT *,sal+IFNULL(comm,0) total FROM emp; SELECT sname 姓名,gender 性别 FROM stu; (AS均可以省略，空格就相当于AS)
5 排序
order by 列名 asc(默认升序,可以不用写) 、desc(降序)
5.1 查询所有学生记录，按年龄升序排序
SELECT * FROM stu ORDER BY sage ASC; SELECT * FROM stu ORDER BY sage;
5.2 查询所有学生记录，按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;
5.3 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno ASC;
6 聚合函数查询
sum avg max min count
聚合函数是用来做纵向运算的函数：
COUNT(): 统计指定列中不为NULL的记录行数；
MAX(): 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序(即字典顺序)运算；
MIN(): 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序(即字典顺序)运算；
SUM(): 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(): 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
6.1 COUNT()
当需要纵向统计时可以使用COUNT()。
查询emp表中记录数并改名
SELECT COUNT(*) AS cnt FROM emp; 特别注意：COUNT(*)计数所有列也即计数所有行
查询emp表中有佣金的人数并改名：
SELECT COUNT(comm) cnt FROM emp;
注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。
查询emp表中月薪大于2500的人数(既有月薪且满足某一条件的人数)
SELECT COUNT(*) FROM emp WHERE sal > 2500;
查询月薪与佣金之和大于2500元的人数
SELECT COUNT(*) AS cnt FROM emp WHERE (sal+IFNULL(comm,0)) > 2500;
查询有佣金的人数，和有领导的人数
SELECT COUNT(cmm),COUNT(mgr) FROM emp;
查询有佣金的人数且有领导的人数
SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL AND mgr IS NOT NULL;
6.2 SUM()和 AVG()
当需要纵向求和时使用sum()函数。
查询所有雇员月薪和
SELECT SUM(sal) FROM emp;
查询所有雇员月薪和，和所有雇员佣金和
SELECT SUM(sal), SUM(comm) FROM emp;
查询所有雇员（月薪+佣金）的和
SELECT SUM(sal+IFNULL(comm,0)) FROM emp; SELECT SUM(sal)+ SUM(comm) FROM emp;
统计所有员工平均工资
SELECT AVG(sal) FROM emp;
6.3 MAX() 和 MIN()
查询最高工资和最低工资
SELECT MAX(sal),MIN(sal) FROM emp;
7 分组查询
排序语法：select xxx from xxx where xxx order by xxx
当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部门来分组。
注：凡和聚合函数同时出现的列名，一定要写在group by 之后。
例如：查询stu中female与male的人数：
SELECT COUNT(*) FROM stu WHERE gender IS NOT NULL GROUP BY gender;
7.1 分组查询
查询 每个部门的部门编号和每个部门的 工资和：
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno; 凡和聚合函数同时出现的列名，一定要写在group by 之后。
查询 每个部门的部门编号以及每个部门的 人数：
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno; 凡和聚合函数同时出现的列名，一定要写在group by 之后。
查询 每个部门的部门编号以及每个部门工资大于1500的 人数：
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
7.2 HAVING子句
查询 工资总和大于9000的部门 编号以及工资和：
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
查询 工资大于1500，工资总和大于6000的部门 编号以及工资和，按工资总和降序排序
SELECT deptno,SUM(sal) FROM emp WHERE sal>1500 GROUP BY deptno HAVING SUM(sal)>6000 ORDER BY sum(sal) DESC;
注：having与where的区别:
1、having是对分组后的数据进行过滤。
where是对分组前的数据进行过滤。
2、having后面可以使用聚合函数(统计函数)。
where后面不可以使用聚合函数(即使用基本数据)。
3、having是对分组后数据的约束。
where是对分组前记录的条件，如果某行记录没有满足 where子句的条件，那么这行记录不会参加分组。
8 LIMIT 方言
LIMIT 用来限定查询结果的起始行，以及总行数。即后面要学的分页技术。
8.1 查询5行记录，起始行从0开始，即从第一行开始
SELECT * FROM emp LIMIT 0, 5;
注意：起始行从0开始，即第一行开始！5表示要查询5行。
8.2 查询10行记录，起始行从3开始，即从第4行开始
SELECT * FROM emp LIMIT 3, 10;
8.3 分页查询
如果一页记录为10条，希望查看第3页记录应该怎么查呢？
- 第一页记录起始行为0，一共查询10行；
- 第二页记录起始行为10，一共查询10行；
- 第三页记录起始行为20，一共查询10行；
分页的思路：
SELECT * FROM emp LIMIT 0, 3; 第一页：从第1行开始，读3行 SELECT * FROM emp LIMIT 3, 3; 第二页：从第4行开始，读3行 SELECT * FROM emp LIMIT 6, 3; 第三页：从第7行开始，读3行 SELECT * FROM emp LIMIT 9, 3; 第四页：从第10行开始，读3行
例如：
int currentPage = 1; // 当前页
int pageSize = 3; // 每页的条数
若当前页为1,则 LIMIT 0, 3; （1-1）*3=0
若当前页为2,则 LIMIT 3, 3; （2-1）*3=3
若当前页为3,则 LIMIT 6, 3; （3-1）*3=6
若当前页为4,则 LIMIT 9, 3; （4-1）*3=9
......
所以：若当前页为currentPage,则 LIMIT (currentPage-1)*pageSize, pageSize;
......
8.3 查询代码的书写顺序和执行顺序
查询语句书写顺序：select – from - where - group by - having - order by - limit
查询语句执行顺序：from - where - group by - having - select - order by - limit （MySQL中演示不了，Oracle中可以演示）
实际开发过程中,通常需要很多表才能完成,要有外键必须先有主键，外键关联/引用/参照主键，主键和外键的数据类型必须一致。
外键约束：foreign key
例：
-- 学生表(主表)
CREATE TABLE student(
stuid VARCHAR(10) primary key,
stuname VARCHAR(50) not null );
第一种添加外键约束的方式：在创建表格的时候同时添加外键约束。
CREATE TABLE score(
stuid VARCHAR(10), --外键列的数据类型一定要与主键列的数据类型一致
score INT,
courseid INT,
CONSTRAINT fk_stuid FOREIGN KEY(stuid) REFERENCES student(stuid)
);
CONSTRAINT(constraint:约束)
FOREIGN KEY(foreign key:外键)
REFERENCES(references:引用/参照/关联)
第二种添加外键约束的方式：在表格创建时没有添加外键约束，之后通过修改表格添加外键约束。
-- 分数表(次表/子表)
CREATE TABLE score(
stuid VARCHAR(10), --外键列的数据类型一定要与主键列的数据类型一致
score INT,
courseid INT
);
ALTER TABLE score ADD CONSTRAINT fk_student_score_stuid FOREIGN KEY(stuid) REFERENCES stu(stuid);
select * from student;
insert into student values('1001','张三丰');
insert into student values('1002','张无忌');
insert into student values('1003','王尼玛');
insert into student values('1004','王老五');
insert into student values('1002','张无忌');
select * from score;
insert into score values('1001','98',1);
insert into score values('1002','95',1);
insert into score values('1002','67',2);
insert into score values('1003','83',2);
insert into score values('1003','57',3);
SQL中 PK、UK、DF、CK、FK 的意思：
--主键约束 PK
在表外修改：alter table xxx add constraint PK_字段 primary key(字段);
在表中修改：constraint PK_字段 primary key(字段),
在表中修改：字段 字段类型 primary key,
--唯一约束 UK
在表外修改：alter table xxx add constraint UK_字段 unique key(字段);
在表中修改：constraint UK_字段 unique key(字段),
在表中修改：字段 字段类型 unique,
--默认约束 DF
在表外修改：alter table xxx add constraint DF_字段 default('默认值') for 字段;
在表中修改：constraint DF_字段 default('默认值') for 字段,
在表中修改：字段 字段类型 default('默认值'),
--检查约束 CK
在表外修改：alter table xxx add constraint CK_字段 check(约束。如：len(字段)>1);
在表中修改：constraint CK_字段 check(约束。如：len(字段)>1),
在表中修改：字段 字段类型 check(约束。如：len(字段)>1),
--外键约束 FK
在表外修改：alter table xxx add constraint FK_主表_子表_主表主键字段 foreignkey(子表外键字段) references 主表(主表主键字段);
在表中修改：constraint FK_主表_子表_主表主键字段 foreign key(子表外键字段) references 主表(主表主键字段),
在表中修改：字段 字段类型 foreign key(子表外键字段) references 主表(主表主键字段),
1、表与表之间的关系
一对一：
例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。
人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。
设计从表可以有两种方案：
方式1：在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；即：字段 字段类型 unique,
方式2：给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。
示例：用方式2
create table QQ(
qqid int primary key,
password varchar(50) ); create table QQDetail(
qqid int primary key,
name varchar(50),
address varchar(200) ); alter table QQDetail add constraint fk_QQ_QQDetail foreign key(qqid) references QQ(qqid);
或者
alter table QQ add constraint fk_QQ_QQDetail foreign key(qqid) references QQDetail(qqid);
注意：虽然是一对一，但是维护关系不一样，那么主从表关系也不一样。(也就是说仍然有主从表的关系)
一对多(多对一)：
最为常见的就是一对多！一对多和多对一，这是从哪个角度去看或者说以谁为参照物。
-- 学生表(主表)
CREATE TABLE student(
tuid VARCHAR(10) primary key,
stuname VARCHAR(50) not null
);
第一种添加外键约束的方式：在创建表格的时候同时添加外键约束。
-- 分数表(次表/子表)
CREATE TABLE score(
stuid VARCHAR(10), -- 外键列的数据类型一定要与主键列的数据类型一致
score INT,
courseid INT,
CONSTRAINT fk_stuid FOREIGN KEY(stuid) REFERENCES student(stuid) );
CONSTRAINT(constraint:约束)
FOREIGN KEY(foreign key:外键)
REFERENCES(references:引用/参照/关联)
第二种添加外键约束的方式：在表格创建时没有添加外键约束，之后通过修改表格添加外键约束。
-- 分数表(次表/子表)
CREATE TABLE score(
stuid VARCHAR(10), -- 外键列的数据类型一定要与主键列的数据类型一致
score INT,
courseid INT
); ALTER TABLE score ADD CONSTRAINT fk_student_score_stuid FOREIGN KEY(stuid) REFERENCES stu(stuid);
多对多：
例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。
例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。
create table teacher( tid int primary key, tname varchar(20) );
create table stu( sid int primary key, sname varchar(50) );
--中间表
create table tea_stu_rel( tid int, sid int ); alter table tea_stu_rel add constraint fk_teacher_rel foreign key(tid) references teacher(tid); alter table tea_stu_rel add constraint fk_stu_rel foreign key(sid) references stu(sid);
六、多表查询（重要）
多表查询有如下几种
- 1. 合并结果集查询(UNION、UNION ALL)
- 2. 连接查询
内连接查询 [INNER] JOIN ON
外连接查询 OUTER JOIN ON
左外连接查询 LEFT [OUTER] JOIN
右外连接查询 RIGHT [OUTER] JOIN
全外连接查询(MySQL不支持) FULL JOIN
自然连接查询 NATURAL JOIN
- 3. 子查询
- 4. 自连接查询
1、合并结果集查询(UNION、UNION ALL)
作用：合并结果集就是把两个select语句的查询结果合并到一起。
合并结果集有两种方式：
- UNION：去除重复记录，
例如：SELECT * FROM t1 UNION SELECT * FROM t2;
- UNION ALL：不去除重复记录，
例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2;
要求：被合并的两个结果：列数、列类型必须相同。
-- 联合查询
CREATE TABLE A( NAME VARCHAR(10), score INT ); CREATE TABLE B( NAME VARCHAR(10), score INT );
-- 批量插入
INSERT INTO A VALUES('a',10),('b',20),('c',30); INSERT INTO B VALUES('a',10),('b',20),('d',40);
--合并结果集查询
SELECT * FROM A UNION SELECT * FROM B; SELECT * FROM A UNION ALL SELECT * FROM B;
2、连接查询(非常重要)
连接查询就是求出多个表的乘积，
例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢？
当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。
也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。
重点:使用主外键关系作为条件来去除无用信息
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。
SELECT emp.ename, emp.sal, emp.com, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno;
还可以为表指定别名，然后在引用列时使用别名即可。
SELECT e.ename, e.sal, e.com, d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;
2.1 内连接查询 [INNER] JOIN ON
上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言的内连接查询。
SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno; -- INNER 可以省略。MySQL 默认连接方式为内连接。
SELECT s.stuid, s.stuname, s.score FROM student s, score c WHERE s.stuid = c.stuid AND score>70; -- 99查询法(老的查询方法)
SELECT s.stuid, s.stuname, s.score FROM student s INNER JOIN score c ON s.stuid = c.stuid WHERE score>70; -- 新的标准查询法
INNER可以省略。
特别注意：ON相当于WHERE，一般多用于主外键条件关联。
不是主外键条件也可以啊！说白了，ON就是筛选的条件。
内连接的特点：查询结果必须满足条件。
例如我们向emp表中插入一条记录。
其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。
2.2 外连接查询(左外连接查询、右外连接查询) [OUTER] JOIN ON
外连接的特点：查询出的结果存在不满足条件的可能。
左外连接查询：
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno; -- OUTER可以省略。以表emp为主。
左外连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
这么说你可能不太明白，我们还是用上面的例子来说明。
其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。
但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。
右外连接查询
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno; -- OUTER可以省略。以表dept为主。
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。
例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。
连接查询心得：
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。
通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。
这个条件大多数情况下都是使用主外键关系去除(一般使用内连接查询)。
两张表的连接查询一般会有一个主外键关系(没有关系的话，你查个鬼啊！)，三张表的连接查询就一般会有两个主外键关系，
所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。
如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
特别注意：我自己测试过，两张表的主外键关系可以alter修改表的主次表关系，使这两张表有了主外键关系；
两种表也可以不有主外键关系，只要他们对应的字段和字段类型相同就行。
多张表查询
法一：内连接查询(99查询法)：
SELECT * FROM student s, score c, course cc WHERE s.stuid = c.stuid AND c.courseid = cc.coureseid; SELECT s.stuid, s.stuname, c.score, cc.cname FROM student s, score c, course cc WHERE s.stuid = c.stuid AND c.courseid = cc.coureseid;
法二：新的内连接查询
SELECT * FROM student s JOIN score c ON s.stui = c.stuid JOIN cource cc ON c.courseid = cc.coureseid;
小结：
合并查询：union、unionall
连接查询：
内连接查询：[inner] join in
左外连接查询：left [outer] join in
右外连接查询：right [outer] join in
多个表查询：n表连接查询，至少有n-1个关系条件。
2.3、自然连接查询(NATURAL JOIN)
大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。
而自然连接无需你去给出主外键等式，它会自动找到这一等式：两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！
当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！
SELECT * FROM emp NATURAL JOIN dept; -- 没写条件，默认内连接查询。 SELECT * FROM emp NATURAL LEFT JOIN dept; -- 默认左外连接 SELECT * FROM emp NATURAL RIGHT JOIN dept; -- 默认右外连接
3、子查询(非常重要)
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
子查询出现的位置：
- where后，作为主句的条件来用。
- from后，作表。
当子查询出现在where后作为条件时，还可以使用如下关键字：
- any
- all
子查询结果集的形式：
- 单行单列（用于条件）
- 单行多列（用于条件）
- 多行单列（用于条件）
- 多行多列（用于表）
练习1：查询工资高于JONES的员工。
分析：
查询条件：工资>JONES工资，其中JONES工资需要一条子查询。
第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename='JONES';
第二步：查询高于甘宁工资的员工
SELECT * FROM emp WHERE sal>(第一步);
结果：
SELECT * FROM emp WHERE sal>( SELECT sal FROM emp WHERE ename='JONES');
练习2：查询与SCOTT在同一个部门的员工。
SELECT *FROMemp WHERE depton=( SELECT depton FROM emp WHERE ename ='SCOTT'); SELECT * FROM emp WHERE depton=(20); -- 两句等价
- 子查询结果集作为条件
- 子查询结果集形式为单行单列
练习3：查询工资高于30号部门所有人的员工信息。
分析：
法一：
SELECT * FROM emp WHERE sal>( SELECT MAX(sal) FROM emp WHERE deptno=30);
法二：查询条件：查询工资高于30号部门所有人的工资，其中查询30号部门所有人工资是子查询。高于所有需要使用all关键字。
第一步：查询30号部门所有人的工资(多行单列)
SELECT sal FROM emp WHERE deptno=30;
第二步：查询高于部门编号为30号的部门所有人的工资的员工信息
SELECT * FROM emp WHERE sal > ALL(第一步);
结果：
SELECT * FROM emp WHERE sal > ALL( SELECT sal FROM emp WHERE deptno=30)
- 子查询结果集作为条件
- 子查询结果集形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）
练习4：查询工作和工资与MARTIN（马丁）完全相同的员工信息。
分析：
查询条件：工作和工资与MARTIN完全相同，这是子查询。
第一步：查询出MARTIN的工作和工资(单行多列)
SELECT job,sal FROM emp WHERE ename='MARTIN';
第二步：查询出与MARTIN工作和工资相同的人
SELECT * FROM emp WHERE (job,sal) IN(第一步);
结果：
SELECT * FROM emp WHERE (job,sal) IN( SELECT job,sal FROM emp WHERE ename='MARTIN');
练习5：查询有2个以上直接下属的员工信息。(即如果mgr中的数据有出现两次以上一样的，说明该编号对应的人有两个以上的直接下属)
SELECT * FROM emp WHERE empno IN( SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);
- 子查询结果集作为条件
- 子查询结果集形式为单行多列
练习6：查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
分析：（多表查询，无需子查询）
查询列：员工名称、员工工资、部门名称、部门地址
查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）
条件：员工编号为7788
第一步：去除多表，只查一张表，这里去除部门表，只查员工表
SELECT ename, sal FROM emp e WHERE empno=7788;
第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno AND empno =7788; -- 不用子查询
-- 用子查询（很鸡肋，意义不大）
第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。
第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。
SELECT dname,loc,deptno FROM dept;
第四步：替换第二步中的dept
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname, loc, deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788; SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT * FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788;
- 子查询作为表
- 子查询形式为多行多列
小结：表dept是一张表的表名，表示一张表，
dept AS d(dept d),给表dept起了个新名字d，则d也表示一张表，
dept 等价于 select * from dept。
4、自连接查询
自己连接自己，起别名(即把自己看成两张表，为了区分，所以每个自己要起一个名字)
求7369员工的经理的编号和姓名
SELECT ename,empno FROM emp WHERE empno = ( SELECT mgr FROMemp WHERE empno =7369);
求7369员工编号和姓名以及该员工的经理编号和姓名
SELECT e1.empno, e1.ename, e2.empno, e2.ename FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno =7369;
练习：求各个部门薪水最高的员工所有信息
普通版本(会有问题)
SELECT * FROM emp WHERE sal IN( SELECTMAX(sal) FROM emp GROUP BY deptno);
改进版本
SELECT e1.* FROM emp e1, (SELECT MAX(sal) maxsal, deptno d FROM emp GROUP BY deptno) e2 --部门最高工资和部分号一起组成的表 WHERE e1.deptno = e2.d AND e1.sal = e2.maxsal;