Stoker的数据库学习之基本语句（二）

数据库学习之基本语句（二）

DQL语句 对数据库中的数据进行查询（简单查询）

我们用下图的emp表来进行操作：

1 . 去除重复数据 DISTINCT
注意：需要查询的所有数据完全相同才能去除重复的
查询emp表中所有薪金：

SELECT  DISTINCT ename,sal FROM emp;

2 . 查看雇员的月薪与佣金之和
sal+comm 两列值相加并形成新的字段
值和null相加 只能返回null

SELECT ename,sal+comm FROM emp;
## 如果comm是null 就用参数2代替
SELECT ename,sal+IFNULL(comm,0) FROM emp;
## 给查询字段起别名 AS(可以省略）
SELECT ename,sal+IFNULL(comm,0) total FROM emp ;

3 . 排序 关键词 ORDER BY
默认是升序（asc） 降序desc
查询所有学生记录，按年龄升序排序

SELECT * FROM stu ORDER BY age;

查询所有学生记录，按年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序。

SELECT * FROM emp ORDER BY sal DESC,empno DESC;

4.聚合函数 sum avg max min count(总记录数）
查询emp表中记录数

SELECT COUNT(*) FROM emp;
## 可以去除null值 
SELECT COUNT(comm) comm_count FROM emp;

查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp WHERE sal>2500;

统计月薪与佣金之和大于2500元的人数

SELECT COUNT(*) FROM emp WHERE sal+IFNULL(comm,0)>2500;

注意查询时要保证要查询的字段记录数对等
查询有佣金的人数，有领导的人数：

SELECT COUNT(comm),COUNT(mgr) FROM emp ;

查询所有雇员月薪和、佣金和

SELECT SUM(sal) FROM emp;
SELECT SUM(comm) FROM emp;

查询所有雇员月薪和，以及所有雇员佣金和

SELECT SUM(sal),SUM(comm) FROM emp;

查询所有雇员月薪+佣金和

SELECT SUM(sal+IFNULL(comm,0)) total FROM emp;

统计所有员工平均工资：

SELECT AVG(comm) FROM emp;

查询最高工资和最低工资：

SELECT MAX(sal),MIN(sal) FROM emp;

5 .分组查询 GROUP BY
注意：按什么分组 前面的字段就是什么
保证查询结果数量对等

查询每个部门的部门编号和每个部门的工资和

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于1500的人数
WHERE条件 要加再group by 的前面

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno ;

查询工资总和大于9000的部门编号以及工资和

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000 ;

注意：where与having使用区别
1).where后不能使用聚合函数
2).分组后如果要添加筛选条件要使用 HAVING 关键词
3).HAVING后可以使用聚合函数
4).WHERE是在分组前筛选
5).HAVING是在分组后筛选

6.分页查询 关键词 LIMIT（MySQL方言）
LIMIT 从第几条开始显示的数据 ，一共显示多少数据
从第0条开始显示
起始页 = （pagesize-1）* count
LIMIT （pagesize-1）* count，count
LIMIT 要在所有条件之后写

SELECT * FROM emp LIMIT 0,3;
SELECT * FROM emp LIMIT 3,3;

7 .模糊查询 LIKE
使用占位符
% 多个字符
_ 一个下划线 表示一个字符

SELECT * FROM emp WHERE ename LIKE 'S%';

完整性

数据完整性（保证用户插入的数据是有效的）
引用完整性（多张表依赖的关系）
域完整性（一个单元格的完整性）
实体完整性（一条数据的完整性）

主键（唯一且不能为空）用于精准删除
唯一键（唯一但可以为空）
主键自增

设置主键方法一
关键词 PRIMARY KEY

CREATE TABLE stu1 (
sid INT PRIMARY KEY,
sname VARCHAR(50)
);

设置主键方法二

CREATE TABLE stu2(
sid INT ,
sname VARCHAR(50),
PRIMARY KEY(sid)
);

方法二可以创建联合主键， 两个字段 同时当主键

CREATE TABLE stu3(
classid INT,
sid INT ,
sname VARCHAR(50),
PRIMARY KEY(classid,sid)
);

注意：联合主键都相同才算重复

方法三 先创建表 再添加主键约束

CREATE TABLE stu4(
sid INT,
sname VARCHAR(50)

);
## 添加主键约束
ALTER TABLE stu4 ADD CONSTRAINT PRIMARY KEY(sid);
## 删除主键约束 注意：删除主键不用约束
ALTER TABLE stu4 DROP PRIMARY KEY ;

唯一键约束

CREATE TABLE stu5(
sid INT PRIMARY KEY,
sname VARCHAR(50) UNIQUE
);

主键自增

CREATE TABLE stu6(
sid INT PRIMARY KEY auto_increment,
sname VARCHAR(50) UNIQUE
);
INSERT INTO stu6(
sname
)VALUES(
'王一'
);
INSERT INTO stu6(
sname
)VALUES(
'王二'
);
INSERT INTO stu6(
sid,sname
)VALUES(
NULL,'王三'
);

主键只能自增 删除了也不会倒退
插入时可以给null 让系统自增

域完整性：
not null
default（默认值）
字段类型

CREATE TABLE stu7(
sid int PRIMARY KEY auto_increment,
sname VARCHAR(50) NOT NULL,
sgender VARCHAR(50) DEFAULT '男'
);
INSERT INTO stu7(sname)
VALUES('haha1');

注意：
有默认值约束时 直接插入null 可以
要使用默认值 可以不差入该字段（跟主键不同）

引用完整性（参照物约束）
表和表之间的关系
一对一（较少） 一对多 多对多（常用）

一对多关系：
学生表（主） 和 分数表（从） 可以通过外键来建立两张表之间的关系
建立外键方法一
明确外键在从表中

CREATE TABLE student(
sid INT PRIMARY KEY,
sname VARCHAR(50)
);
## 外键别名规范 fk_主表名_从表名_字段名
## CONSTRAINT 外键别名 FOREIGN KEY 从表字段（从表中的关联字段） REFERENCES 主表名（主表中的关联字段）
CREATE TABLE score(
sid INT,
score INT,
CONSTRAINT fk_student_score_sid
FOREIGN KEY (sid) REFERENCES student (sid)
);

建立外键方式二

CREATE TABLE student1(
sid INT PRIMARY KEY,
sname VARCHAR(50)
);
CREATE TABLE score1(
sid INT,
score INT

);
## 添加外键约束
ALTER TABLE score1 ADD CONSTRAINT fk_student1_score1_sid
FOREIGN KEY (sid) REFERENCES student1 (sid);
## 删除外键(一个表中可能有多个外键)
ALTER TABLE score1 DROP FOREIGN KEY fk_student1_score1_sid;

多对多关系 ：
老师表和学生表
中间表来表示多对多关系
外键在中间表 两个外键

CREATE TABLE teacher(
tid INT PRIMARY KEY,
tname VARCHAR(50)
);
CREATE TABLE studentX(
sid INT PRIMARY KEY,
sname VARCHAR(50)
);
## 中间表
CREATE TABLE teacher_studentX(
tid INT,
sid INT
);
ALTER TABLE teacher_studentX ADD CONSTRAINT fk_teacher_studentX_tid
FOREIGN KEY (tid) REFERENCES teacher(tid);
ALTER TABLE teacher_studentX ADD CONSTRAINT fk_teacher_studentX_sid
FOREIGN KEY (sid) REFERENCES studentX(sid);