【数据库学习笔记】MySQL_02_查询,约束

时间:2021-12-02 08:42:04

MySQL_02

A.查询

1.语法

a.基本语句:

select:selection_list  要查询的列名称

from:table_list  要查询的表名称

where:condition  行条件

group bby:grouping_columns  对结果分组

having:condition  分组后的条件

order by:sorting_columns  对结果排序

limit:offrset_start,row_count  分页查询

b.条件查询语句:where 子句

=、!=、<>(不等于)、<、<=、>、>=

and: 并且

or: 或者

not: 非

between...and: 在什么范围之间

in(set1,set2...): 或者

is null: 为空

is not null: 不为空

c.在练习前,先插入两张表

-- 创建数据库mydb_01
CREATE DATABASE mydb_01;

-- 使用mydb_01数据库
USE mydb_01;

-- 建立stu学生表
CREATE TABLE stu (
sid VARCHAR(6), -- 学生编号
sname VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(20) -- 学生性别
);

-- 插入数据
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雇员表
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);

-- 查看表数据
SELECT * FROM stu;

SELECT * FROM emp;
stu表

【数据库学习笔记】MySQL_02_查询,约束

emp表
【数据库学习笔记】MySQL_02_查询,约束

2.模糊查询 like

通配符

a._:匹配单个任意字符

1)查询姓名是三个任意字符组成的

-- 查询姓名是任意 
SELECT * FROM stu WHERE sname LIKE '___'; -- 三个"_"
【数据库学习笔记】MySQL_02_查询,约束

2)查询第二个字符是h的

-- 查询第二个字符是h的
SELECT * FROM stu WHERE sname LIKE '_h%';

【数据库学习笔记】MySQL_02_查询,约束

b.%:匹配多个任意字符

1)查询名字中包含h的

-- 查询名字中包含h的
SELECT * FROM stu WHERE sname LIKE '%h%';
【数据库学习笔记】MySQL_02_查询,约束

2)要查询名字是c开头的

-- 要查询名字是c开头的
SELECT * FROM stu WHERE sname LIKE 'c%';
【数据库学习笔记】MySQL_02_查询,约束

3)查询名字是n结尾的

-- 查询名字是n结尾的
SELECT * FROM stu WHERE sname LIKE '%n';
【数据库学习笔记】MySQL_02_查询,约束

3.条件查询

a.查询emp中sal为3000的员工姓名

-- 查询emp中sal为3000的员工姓名
SELECT ename, sal FROM emp WHERE sal=3000;
【数据库学习笔记】MySQL_02_查询,约束

!=,not,>,<,>=,<=自行练习

b.并且

查询emp中2000<=sal<=3000的员工

-- 查询emp中2000<=sal<=3000的员工 and 或者 between...and...
SELECT ename, sal FROM emp WHERE sal>=2000 AND sal <= 3000;
SELECT ename, sal FROM emp WHERE sal BETWEEN 2000 AND 3000;
【数据库学习笔记】MySQL_02_查询,约束

c.或者

查询emp中sal为800或者3000的员工

-- 查询emp中sal为800或者3000的员工
SELECT ename, sal FROM emp WHERE sal=8000 OR sal=3000;
SELECT ename, sal FROM emp WHERE sal IN(800,3000);
【数据库学习笔记】MySQL_02_查询,约束

d.为空

查询emp中comm为空的员工

-- 查询emp中comm为空的员工
SELECT ename, comm FROM emp WHERE comm IS NULL;
【数据库学习笔记】MySQL_02_查询,约束

e.非空

查询emp中comm不为空的员工

-- 查询emp中comm不为空的员工
SELECT ename, comm FROM emp WHERE comm IS NOT NULL;
【数据库学习笔记】MySQL_02_查询,约束

4.字段控制

在上一篇中提到过,在这详细演示

a.修改字段的别名:as(可以省略,不建议)

1)给emp中sname,sal字段起别名

-- 给emp中sname,sal字段起别名
SELECT ename AS '姓名', sal AS '工资' FROM emp;
【数据库学习笔记】MySQL_02_查询,约束

2)给运算字段起别名

计算员工的收入(工资sal+奖金comm)

-- 计算员工的收入(工资sal+奖金comm)
SELECT ename AS '姓名', (sal + comm) AS '收入' FROM emp;
【数据库学习笔记】MySQL_02_查询,约束

null是因为部分员工奖金为null(没有),null参与运算,结果都为null,后面说解决方法

3)给表起别名

-- 给表起别名e
SELECT e.ename,e.sal FROM emp AS e;

b.字段运算

a.关于null参与运算

比如:工资是1000,奖金是null,两者相加,结果为null

解决方法:替换为一般会将null

ifnull(字段名称,如果是null的替换值):mysql的特有语言,其他数据库不适用

例如上面的,求员工的收入

-- 计算员工的收入(工资sal+奖金comm)
SELECT ename AS '姓名', sal AS '工资', comm AS '奖金', (sal + IFNULL(comm,0)) AS '收入' FROM emp;
【数据库学习笔记】MySQL_02_查询,约束

c.去重复记录:distinct

统计工资的等级,我只需要查询工资的金额,但不需要数量和姓名

-- 查询sal的工资等级(不要重复的)
SELECT DISTINCT sal AS '工资' FROM emp;
【数据库学习笔记】MySQL_02_查询,约束

5.排序order by

a.升序排序 asc默认值

工资从小到大排

-- 按sal从小到大排序
SELECT * FROM emp ORDER BY sal ASC;
【数据库学习笔记】MySQL_02_查询,约束

b.降序排序 desc

工资从大到小排

-- 按工资从大到小排序
SELECT * FROM emp ORDER BY sal DESC;
【数据库学习笔记】MySQL_02_查询,约束
如果出现多条工资一样的,那我们可以指定第二排序条件

-- 按工资从大到小排序,当工资一样,按照奖金降序排序
SELECT * FROM emp ORDER BY sal DESC, comm DESC;
【数据库学习笔记】MySQL_02_查询,约束

6.聚合函数

a.概述

聚合函数,是用来做纵向运算的函数

b.count()

统计指定列不为null的记录行数:统计个数,比如统计emp中有多少个员工

-- 统计emp中员工的个数
SELECT COUNT(ename) FROM emp;
-- 或者传个*
SELECT COUNT(*) FROM emp;
【数据库学习笔记】MySQL_02_查询,约束
注意:一般不传有null值的列,除非你要统计拿奖金的人数

c.max()

计算指定列的最大值,如果该列是字符串类型,那么使用字符串排序运算

查询工资的最大值

-- 查询工资的最大值
SELECT ename AS '姓名', MAX(sal) AS '最高工资' FROM emp;
【数据库学习笔记】MySQL_02_查询,约束

d.min()

计算指定列的最小值,如果该列是字符串类型,那么使用字符串排序运算

查询工资的最小值

-- 查询工资的最小值
SELECT ename AS '姓名', MIN(sal) AS '最低工资' FROM emp;
【数据库学习笔记】MySQL_02_查询,约束

e.sum()

计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0

计算总工资

-- 计算总工资
SELECT SUM(sal) AS '总工资' FROM emp;
【数据库学习笔记】MySQL_02_查询,约束

f.avg()

计算制定列的平均值,如果指定列类型不是数值类型,那么计算结果为0

【数据库学习笔记】MySQL_02_查询,约束

7.分组查询groub by

a.概述

一般配合聚合函数使用,查出的数据才有意义

b.查询的字段

1)分组字段本身

2)聚合函数

比如:部门编号分组,有5个部门,求各部门平均工资,那展示出来的数据应该有5条

select 部门编号, avg(工资) from 表名 group by 部门编号

(第一个部门编号为查询列,第二个部门编号为按部门编号分类)

-- 按部门查询
SELECT job AS '部门', AVG(sal) AS '部门平均工资' FROM emp GROUP BY job;
【数据库学习笔记】MySQL_02_查询,约束

c.需求

每个部门工资高于1500的人数

select 部门编号, count(*) from 表名 where  工资>=1500 group by 部门编号:

(where是对分组前的条件限定,也就是不满足条件的不参与分组)

-- 每个部门工资高于1500的人数
SELECT job AS '部门', COUNT(*) AS '工资不低于1500的人数' FROM emp WHERE sal >= 1500 GROUP BY job;
【数据库学习笔记】MySQL_02_查询,约束

d.where和having

1)where:在分组之前对条件进行限定,不满足条件,就不会参与分组

2)having:在分组之后,对结果集的筛选

e.需求

查询部门平均工资大于2000的部门

select 部门编号, avg(工资) from 表名 group by 部门编号 having avg(工资)>2000;

-- 查询部门平均工资大于2000的部门
SELECT job AS '平均工资>2000的部门', AVG(sal) AS '平均工资' FROM emp GROUP BY job HAVING AVG(sal)>2000;
【数据库学习笔记】MySQL_02_查询,约束

f.需求

查询员工工资大于1500,且平均工资大于2000的部门

(首先各个部门筛选出工资大于1500的,算出平均值,再筛选大于2000的部门)

SELECT job AS '平均工资大于2000', AVG(sal) AS '平均工资' FROM emp WHERE sal > 1500 GROUP BY job HAVING AVG(sal) > 2000;
【数据库学习笔记】MySQL_02_查询,约束

8.分页查询limit

a.概述

limit 0,5 第一个参数为开始的记录索引,第二个参数为每页的条数,索引从0开始

规律:参数1= (页码 - 1) * 参数2

比如:现在我要查看第20页,每页显示10条

limit (20-1)*10, 10----->limit 190,10

b.需求

分页显示emp,每页5条数据

-- 分页显示emp,每页5条数据
SELECT * FROM emp LIMIT 0, 5; -- 第一页
SELECT * FROM emp LIMIT 5, 5; -- 第二页
SELECT * FROM emp LIMIT 10, 5; -- 第三页

c.oracle中的分页语句:rownum

d.SQLServer中的分页语句:top

B.约束,多表关系

1.概述

a.作用:为了保证数据的有效性和完整性

b.mysql约束常用:

主键约束:primary key   被修饰过的字段唯一非空

唯一约束:unique(了解) 被修饰过的字段唯一,对null不起作用

非空约束:not null(了解) 被修饰过的字段非空

外键约束:foreign key(了解)

c.注意:一张表只能有一个主键,这个主键可以包含多个字段

d.一个字段同时被唯一和非空约束,功能就类似于一个主键,但不是主键

2.主键约束的添加方式:

a.方式1:

建表时,同时添加约束格式:字段名次 字段类型 primary key

-- 方式1
CREATE TABLE myTable_01(
id INT PRIMARY KEY, -- 将id设为主键
username VARCHAR(20)
);

-- 插入数据
INSERT INTO myTable_01 VALUES(1,'Tom'); -- 成功
INSERT INTO myTable_01 VALUES(1,'Tom'); -- 失败
INSERT INTO myTable_01 VALUES(NULL,'Jack'); -- 失败

b.方式2:

建表时,在约束去添加约束,所有字段声明完成之后,就是约束区域

-- 方式2
CREATE TABLE myTable_02(
id INT,
username VARCHAR(20),
PRIMARY KEY (id) -- 将id设置为主键
);

c.方式3:

建表之后,通过修改表结构添加约束

-- 方式3
CREATE TABLE myTable_01(
id INT,
username VARCHAR(20)
);

-- 添加约束
-- 将id和username设置为联合约束
-- 可以存在id 或者 username一个重复,但不能两个都重复
ALTER TABLE myTable_01 ADD PRIMARY KEY(id,username);

-- 插入数据
INSERT INTO myTable_01 VALUES(1,'Tom'); -- 成功
INSERT INTO myTable_01 VALUES(1,'Jack'); -- 成功
INSERT INTO myTable_01 VALUES(1,'Tom'); -- 失败

3.唯一约束添加方式

a.方式1:

建表的同时添加约束 格式: 字段名称 字段类型 unique

-- 唯一约束
CREATE TABLE myTable_01(
id INT UNIQUE,
username VARCHAR(20) UNIQUE
);

b.方式2和方式3与主键一致,不再介绍

4.非空约束添加方式

a.方式1:

建表的同时添加约束 格式: 字段名称 字段类型 not null

-- 非空约束
CREATE TABLE myTable_01(
id INT NOT NULL,
username VARCHAR(20) NOT NULL
);
b.方式2和方式3与主键一致,不再介绍

5.自增auto_incerment

要求:

a.被修饰的字段类型支持自增,一般int

b.被修饰的字段必须是一个key,一般是primary key

-- 自增
CREATE TABLE myTable_01(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
);

6.外键约束

a.概述

为了保证数据有效性和完整性,添加约束(外键约束)

在多表的一方添加外键约束

在开发中,关系中的一方称之为主表或者一表,关系中的多方称之为多表或者从表,

为了表示一对多的关系,一般会在多表的一方添加一个字段

字段名称自定义(建议:主表的名称_id)

字段类型一般和主表的主键的类型保持一致,我们称这个字段为外键

b.格式:

alter table 多表名称 add foreign key(多表字段名) references 一表名称(主键)

c.添加外键约束特点

1)主表中不能删除从表中已经引用的数据

银行卡销户前得把钱取出来

2)从表中不能添加主表中不存在的数据

没买火车票,不能乘坐火车

d.如果添加了外键后我想删除主表中的数据 怎么办 ?

1)方式1

alter table 多表名 add foreign key(外键名称) references 一表名(主键) on delete cascade

然后就可以删除表中数据

2)方式2

先把带有外键的多表的数据删除,再删除一表数据

7.表的常见关系

a.一对多:用户和订单,一个用户对应多个订单
b.多对多:订单和商品,一个订单对应多个商品,一个商品也出现在多个订单

c.一对一:夫妻

8.一对多

a.概述

在开发中,关系中的一方称之为主表或者一表,关系中的多方称之为多表或者从表,

为了表示一对多的关系,一般会在多表的一方添加一个字段

字段名称自定义(建议:主表的名称_id)

字段类型一般和主表的主键的类型保持一致,我们称这个字段为外键

b.案例

天猫购物客户与订单的关系

-- 创建用户表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长
username VARCHAR(20)
);

-- 创建订单表
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品编号
totalprice DOUBLE, -- 商品价格
user_id INT -- 准备添加的外键,下单用户id
);

-- 添加外键约束
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id);

自行插入数据,测试外键约束的特点

9.多对多

a.开发中处理多对多

引入一张中间表,存放两张表的主键

一般会讲这两个字段设置为联合主键

这样既可以将多对多的关系拆分,两个多对多

为了保证数据的有效性和完整性

需要在中间表上添加两个外键约束即可

b.案例

一个老师带多个学生,一个学生也被多个老师带

-- 创建教师表
CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT, -- 老师编号
tname VARCHAR(20), -- 老师姓名
age INT -- 老师年龄
);

-- 录入老师信息
INSERT INTO teacher(id,tname,age) VALUES(1,'张老师',30);
INSERT INTO teacher(id,tname,age) VALUES(2,'李老师',32);

-- 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 学生编号
sname VARCHAR(20), -- 学生姓名
sclass VARCHAR(20) -- 班级
);

-- 录入学生信息
INSERT INTO student(id,sname,sclass) VALUES(101,'张三','电子1101');
INSERT INTO student(id,sname,sclass) VALUES(102,'李四','电子1102');
INSERT INTO student(id,sname,sclass) VALUES(103,'王五','电子1103');
INSERT INTO student(id,sname,sclass) VALUES(104,'程六','电子1104');

-- 创建中间表
CREATE TABLE orders(
teacher_id INT, -- 老师编号
stu_id INT -- 学生编号
);

-- 录入中间表信息(两个表的主键)
INSERT INTO orders(teacher_id, stu_id) VALUES(1,101);
INSERT INTO orders(teacher_id, stu_id) VALUES(1,102);
INSERT INTO orders(teacher_id, stu_id) VALUES(1,103);
INSERT INTO orders(teacher_id, stu_id) VALUES(1,104);
INSERT INTO orders(teacher_id, stu_id) VALUES(2,101);
INSERT INTO orders(teacher_id, stu_id) VALUES(2,102);
INSERT INTO orders(teacher_id, stu_id) VALUES(2,103);
INSERT INTO orders(teacher_id, stu_id) VALUES(2,104);

-- 关联外键
ALTER TABLE orders ADD FOREIGN KEY(teacher_id) REFERENCES teacher(id); -- 关联老师
ALTER TABLE orders ADD FOREIGN KEY(stu_id) REFERENCES student(id); -- 关联学生

-- 查询表
SELECT * FROM teacher;
SELECT * FROM student;
SELECT * FROM orders;
teacher

【数据库学习笔记】MySQL_02_查询,约束

student

【数据库学习笔记】MySQL_02_查询,约束

orders
【数据库学习笔记】MySQL_02_查询,约束

10.多表查询(代码演示在后面)

a.内连接

1)格式1:显式的内连接

select a.*, b.* from a[inner] join on an的连接条件

2)格式2:隐式的内连接

select a.*,b.* from a,b where ab的连接条件

b.外连接

1)左外连接

select a.*,b.* from a  left [outer] join b on 连接条件;outer 可以不写

先展示join左边(a)的所有数据,根据条件关联查询join右边的表(b)

符合条件则展示出来,不符合以null值展示.

2)右外连接

select a.*,b.* from b right [outer] join a on 连接条件;

先展示join右边(a)的所有数据,根据条件关联查询join左边的表(b)

符合条件则展示出来,不符合以null值展示.

c子查询:一个查询依赖另一个查询.

d.笛卡尔积:了解

多张表无条件的联合擦查询,没意义

select 表1.*, 表2.* from 表1,表2;

11.多表查询练习

用户和订单的多表查询

a.建立两张表

-- 创建用户表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);

-- 录入用户信息
INSERT INTO USER VALUES(101,'张三');
INSERT INTO USER VALUES(102,'李四');
INSERT INTO USER VALUES(103,'王五');
INSERT INTO USER VALUES(104,'赵六');

-- 创建订单表
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT,
price DOUBLE, -- 订单价格
user_id INT -- 订单客户
);

-- 录入订单信息
INSERT INTO orders VALUES(1,2000,101);
INSERT INTO orders VALUES(2,1000,101);
INSERT INTO orders VALUES(3,1300,102);
INSERT INTO orders VALUES(4,2800,103);
INSERT INTO orders VALUES(5,1000,NULL);

-- 关联外键
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id)

-- 查询表
SELECT * FROM USER;
SELECT * FROM orders;

b.查看用户订单,没有订单的用户不显示

1)隐示内连接

-- 隐式内连接
SELECT user.*, orders.* FROM USER, orders WHERE user.id = orders.user_id;

【数据库学习笔记】MySQL_02_查询,约束
订单5没有,所以不显示

2)显式内连接

-- 显式内连接
SELECT user.*, orders.* FROM USER JOIN orders ON user.id = orders.user_id;
结果一样

c.查看所有用户的订单详情

左外连接,user在左

-- 左外连接
SELECT user.*, orders.* FROM USER LEFT JOIN orders ON user.id = orders.user_id;
【数据库学习笔记】MySQL_02_查询,约束

从左边看(用户看订单),赵六没有订单,为空

d.查看所有订单的用户详情

右外连接,orders 在右

-- 右外连接
SELECT user.*, orders.* FROM USER RIGHT JOIN orders ON user.id = orders.user_id;
【数据库学习笔记】MySQL_02_查询,约束
从右边看(订单看用户),订单5没有用户,所以用户名为null

e.查看用户为张三的订单详情

-- 先查询张三的id,再查询订单
SELECT id FROM USER WHERE username = '张三'; -- 101
SELECT * FROM orders WHERE user_id = 101;

-- 一步到位
SELECT * FROM orders WHERE user_id = (SELECT id FROM USER WHERE username = '张三');
【数据库学习笔记】MySQL_02_查询,约束

f.查询出订单的价格大于1000的所有用户信息

-- 先查询订单,再查询用户
SELECT user_id FROM orders WHERE price > 1000; -- 101,102,103
SELECT * FROM USER WHERE id IN(101,102,103);

-- 一步到位
SELECT * FROM USER WHERE id IN(SELECT user_id FROM orders WHERE price > 1000);
【数据库学习笔记】MySQL_02_查询,约束

g.查询订单价格大于1000的订单信息及相关用户的信息

1)方式1:内连接

-- 内连接
SELECT orders.*, user.* FROM orders, USER WHERE user.id = orders.user_id AND price > 1000;
【数据库学习笔记】MySQL_02_查询,约束

2)方式2:子查询

先查出订单,将查询结果作为一张临时表

其实用的就是内链接 查两张表

-- 注意给临时表起名 :表名 as 名称
SELECT user.*, temp.* FROM USER,(SELECT * FROM orders WHERE price > 1000 ) AS temp WHERE user.id = temp.user_id;
【数据库学习笔记】MySQL_02_查询,约束