1、count 使用
- 1.1 查询employee表中记录数:
SELECT COUNT(*) FROM employee;
- 1.2 查询员工表中有绩效的人数:
SELECT COUNT(performance) FROM employee;
- 1.3 查询员工表中月薪大于2500的人数:
SELECT COUNT(*) FROM employee WHERE salary > 2500;
- 1.4 统计月薪与绩效之和大于5000元的人数:
SELECT COUNT(*) FROM employee WHERE IFNULL(salary,0)+IFNULL(performance,0)>5000;
- 1.5 查询有绩效的人数，和有管理费的人数:
SELECT COUNT(performance),COUNT(manage) FROM employee;
2、SUM 和MAX和MIN 的使用
- 1、查询所有雇员月薪和
SELECT SUM(salary) FROM employee;
- 2、查询所有雇员月薪和,以及所有雇员绩效和
SELECT SUM(salary),SUM(performance) FROM employee;
- 3、 查询所有雇员月薪+绩效和
SELECT SUM(salary+IFNULL(performance,0)) FROM employee;
- 4、统计所有员工平均工资
SELECT AVG(salary) FROM employee;
- 5、 查询最高工资和最低工资
SELECT MAX(salary),MIN(salary) FROM employee;
3、分组查询
- 1、 将查询结果按照1个或者多个字段进行分组,字段值相同的为1组
SELECT gender FROM employee GROUP BY gender;
- 2、把相同的数据去掉
SELECT gender,GROUP_CONCAT(name) FROM employee GROUP BY gender;
4、分组查询-使用
- 1、在一组里面所有男性名字和女性名字
SELECT gender,GROUP_CONCAT('name') FROM employee GROUP BY gender;
- 2、 所有男性工资和女性工资
SELECT gender,GROUP_CONCAT(salary) FROM employee GROUP BY gender;
- 3、对某一个部门进行分组,查看每个部门薪资信息都罗列出来
SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;
5、GROUP BY + 聚合函数
- 1、查看部门平均薪资
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 2、查看每个部门的最高薪资
SELECT department,GROUP_CONCAT(salary),MAX(salary) FROM employee GROUP BY department;
- 3、查看每个部门的最低薪资
SELECT department,GROUP_CONCAT(salary),MIN(salary) FROM employee GROUP BY department;
- 4、查询每个部门的部门名称和每个部门的工资和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 5、查询每个部门的部门名称和每个部门的人数
SELECT department,GROUP_CONCAT(name),COUNT(*) FROM employee GROUP BY department;
- 6、查询每个部门的部门名称以及每个部门工资大于1500的人数
SELECT name,salary FROM employee WHERE salary>1500;
SELECT department,GROUP_CONCAT(salary),COUNT(*) FROM employee WHERE salary>1500 GROUP BY department;
6、GROUP BY + having
- 1、把所有部门进行分组
SELECT department FROM employee GROUP BY department;
- 2、每一组有哪些薪资,以及薪资总和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 3、完成
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;
7、HAVING和WHERE的区别
HAVING是在分组后对数据进行过滤;
WHERE是在分组前对数据进行过滤;
HAVING后面可以使用分组函数(统计函数);
WHERE后面不可以使用分组函数;
WHERE是对分组前记录的条件,如果某行记录没有满足where子语句的条件,那么这行记录不参与分组;而HAVING是对分组后的数据约束;
- 1、查询工资大于2000的,工资总和大于6000的部门名称以及工资和;
- 各部门工资
- 各部门工资总和
- 各部门工资总和大于6000
- 各部门工资总和大于6000降序排序
- 2.、查询工资大于2000
SELECT salary FROM employee WHERE salary>2000;
- 3、工资总和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee WHERE salary > 2000 GROUP BY department HAVING SUM(salary)>9000;
ORDER BY SUM(salary) DESC;
8、书写顺序，执行顺序
书写顺序SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
执行顺序FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT
分页
-
1、 LIMIT
image.png
SELECT * FROM employee
LIMIT (curPage-1)*pageSize,pageSize;
数据完整性
- 1、完整性分类：
实体完整性
域完整性
引用完整性
- 2、什么是实体完整性
表中的一行(一条记录)代表一个实体(entity)
- 3、实体完整性的作用
标识每一行数据不重复.行级约束
- 4、约束类型
主键约束(primary key)
唯一约束(unique)
自动增长(auto_increment)
1、 主键约束
- 1、特点:
每个表中要有一个主键
数据唯一,且不能为null
- 2、添加方式:
CREATE TABLE 表名(字段名1 数据类型 primary key,字段名2 数据类型);
- 3、举例:创建person表
CREATE TABLE person(ID BIGINT primary key,NAME VARCHAR(50))
CREATE TABLE 表名(字段1 数据类型,字段名2 数据类型,primary key(要设置主键的字段));
- 4、举例创建
CREATE TABLE person(
ID BIGINT,
name VARCHAR(50),
age INT,
primary key(ID)
);
2、联合主键：两个字段数据同时相同时,才违反联合主键约束
- 1、创建联合主键
CREATE TABLE 表名(字段1 数据类型,字段2 数据类型,primary key(主键1,主键2));
- 2、举例
CREATE TABLE student(
ID BIGINT,
snum BIGINT,
name VARCHAR(50),
primary key(ID,snum))
- 3、先创建表再添加主键
CREATE TABLE student(sid INT,name VARCHAR(50));
- 4、如果表中你之前已经添加了数据,数据不符合主键规则就会报错
ALTER TABLE student ADD CONSTRAINT primary key(sid);
3、唯一约束
- 1、特点
指定列的数据不能重复
可以为空值
- 2、格式:
CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)
4、自动增长列
- 1、特点
指定列的数据自动增长
即使数据删除,还是从删除的序号继续往下
- 2、格式
CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE);
- 3、举例
CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE);
5、域完整性
- 1、使用
限制此单元格的数据正确,不对照此列的其他单元格比较
域代表当前的单元格
- 2、数据类型
数值类型
日期类型
字符串类型
- 3、非空约束(NOT NULL)
CREATE TABLE stu(
id int primary key auto_increment,
name varchar(20) unique not null,
gender char(1) default '男'
)
- 4默认值约束(DEFAULT)
CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE NOT NULL,
字段3 数据类型 DEFAULT '默认值');
- 5、举例:
CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL,
gender CHAR(1) DEFAULT '男'
);
6、参照完整性
- 什么是参照完整性
(1)是指表与表之间的一种对应关系
(2)通常情况下可以通过设置两表之间的主键、外键关系,或者编写两表的触发器来实现
(3)有对应参照完整性的两张表格,再对他们进行数据插入,更新,删除的过程中,系统都会将被修改表格与另外一张对应表格进行对照,从而阻止一些不正确的数据的操作
-
2、数据库的主键和外键类型一定要一致
-
3、参照完整性添加
（1）通过一张表里面的某个字段,关联另外一张表
image.png
（2）对已有表添加外键关系
ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);
7、多表查询
- 1. 一对一
一夫一妻(开发中使用比较少)
- 2、一对多关系
一个人可以拥有多辆汽车,要求查询某个人拥有的所有车辆
创建Person表
- 人
CREATE TABLE person(
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(50),
p_age INT,
p_sex CHAR(1)
);
- 车
CREATE TABLE car(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50),
c_color VARCHAR(25),
p_id INT,
CONSTRAINT fk_Person FOREIGE KEY(c_id) REFERENCES person(p_id)
);
- 3、多对多关系
例如:学生选课,一个学生可以选修多门课程,每门课程可以供多个学生选择
再例如: 老师和学生的多对多关系(一个老师有多个学生,一个学生有多个老师)
image.png
1、创建老师表
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(50),
t_age INT,
t_genger CHAR(1) DEFAULT '男'
);
2、创建学生表
CREATE TABLE students(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50) NOT NULL,
s_age INT,
s_gender CHAR(1) DEFAULT '男'
);
3、中间表(创建学生与老师 关系表)
CREATE TABLE tea_stu_rel(
t_id INT,
s_id INT
);
4、添加外键
ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_tid
FOREIGE KEY(t_id)
REFERENCES teacher(t_id);
ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_sid
FOREIGE KEY(sid)
REFERENCES students(sid);
8、合并结果集UNION与UNION_ALL
-
1、UNION
合并时去除重复记录 -
2、 UNION ALL
合并时不去除重复记录 -
3、格式
SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
- 4、创建表
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 A VALUES('a',10),('b',20),('c',40);
- 4、 UNION
SELECT * FROM A
UNION
SELECT * FROM B;
- 5、UNION ALL
SELECT * FROM A
UNION ALL
SELECT * FROM B;
- 6、 注意事项
被合并的两个结果集:列数,列类型必须相同
网友评论