1、DQL语句
单表的条件查询:
-- 满足条件的字段将被显示查询
SELECT 字段名 FROM 表名 WHERE 条件;
准备数据
-- 创建表
CREATE TABLE student (
id int,
name varchar(20),
age int,
sex varchar(5),
address varchar(100),
math int,
english int
);
-- 插入记录
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
1.1 条件
-- 格式
select ... from 表名 where 条件;
比较运算符 | < <= = > >= != <> | 小于,大于,小于(大于)等于,不等于 |
---|---|---|
BETWEEN…AND… | 显示在某区间的值(包头包尾) | |
IN(set) | 显示在in列表中的值 | |
LIKE"%张%" | 模糊查询,like语句中,%表示0个或多个任意的字符,_代表一个字符 | |
IS NULL | 判断是否为空 | |
逻辑运算符 | AND | 多个条件同时成立(且) |
OR | 多个条件任一成立(或) | |
NOT | 不成立(非) |
a)比较运算符
-- 格式
> < >= <= = != <>
# 关系运算符
-- 查询math分数大于80分的学生
SELECT * FROM student WHERE math >80;
-- 查询english分数小于或等于80分的学生
SELECT * FROM student WHERE english <= 80;
-- 查询age等于20岁的学生
SELECT * FROM student WHERE age = 20;
-- 查询age不等于20岁的学生
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
b)逻辑运算符
-- 格式
&& -> and -- 条件同时满足(且)
|| -> or -- 条件满足一个(或)
! -> not -- 条件不满足(非)
in关键字
-- 格式
字段 in (值1,值2...) -- 使用or进行拼接,满足条件就显示
# 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student WHERE age > 35 AND sex = '男';
-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student WHERE age > 35 OR sex = '男';
-- 查询id是1或3或5的学生 7 9 11 13 ....
SELECT * FROM student WHERE id = 1 OR id =3 OR id = 5;
-- in关键字
-- 再次查询id是1或3或5的学生
SELECT * FROM student WHERE id IN(1,3,5);
-- 查询id不是1或3或5的学生
SELECT * FROM student WHERE id NOT IN(1,3,5);
c)范围查询
-- 格式
字段 between 较小的值 and 较大的值 -- 包头包尾
-- 查询english成绩大于等于77,且小于等于87的学生
SELECT * FROM student WHERE english >= 77 AND english <=87;
-- 推荐
SELECT * FROM student WHERE english BETWEEN 77 AND 87;
d)模糊查询
-- 格式
字段 like '通配符字符串';
% -- 任意字符 零个或多个
_ -- 一个字符
# like模糊匹配
-- 查询姓马的学生
SELECT * FROM student WHERE `name` LIKE '马%';
-- 查询姓名中包含'德'字的学生
SELECT * FROM student WHERE `name` LIKE '%德%';
-- 查询姓马,且姓名有三个字的学生
SELECT * FROM student WHERE NAME LIKE '马__';
1.2 排序
对查询结果进行排序:多字段排序,后者是在前者基础之上,再进行排序
-- 格式
select ... from 表名 order by 排序字段 [asc | desc],排序字段 [asc | desc];
asc -- 升序 默认值
desc -- 降序
# 排序
-- 查询所有数据,使用年龄降序排序
SELECT * FROM student ORDER BY age DESC;
-- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序
SELECT * FROM student ORDER BY age DESC,ma C;
-- 拓展知识点 数据库字符集 utf8 没有按照 拼音排序 必须使用 GBK
-- 使用java代码 convert() 转换函数
SELECT * FROM student ORDER BY NAME ;
SELECT * FROM student ORDER BY CONVERT(NAME USING gbk) ;
1.3 聚合函数
对一列数据进行计算,返回一个结果;忽略NULL
-- 格式
count(字段名) -- 统计
sum(字段名) -- 求和,如果指定列类型不是数值类型,那么计算结果为0
avg(字段名) -- 平均值,如果指定列类型不是数值类型,那么计算结果为0
max(字段名) -- 最大值,如果指定列是字符串类型,那么使用字符串排序运算
min(字段名) -- 最小值,如果指定列是字符串类型,那么使用字符串排序运算
# 聚合函数
-- 查询学生总数(不包含null值)
SELECT COUNT(id) FROM student;
SELECT COUNT(english) FROM student;
-- count(*) count(number) 包含NULL
SELECT COUNT(*) FROM student;
SELECT COUNT(7) FROM student;
-- 查询年龄大于40的总数
SELECT * FROM student WHERE age > 40;
SELECT COUNT(*) FROM student WHERE age > 40;
-- 查询数学成绩总分
SELECT SUM(math) FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student;
1.4 分组
对查询结果进行分组,相同的内容分为一组;通常与聚合函数一起使用
-- 格式
select 分组字段 from 表名 group by 分组字段 having 条件;
总结
where在分组前条件过滤,不能使用聚合函数
having在分组后条件过滤,可以使用聚合函数
# 分组
-- 按性别分组
SELECT COUNT(*) FROM student WHERE sex = '男';
SELECT COUNT(*) FROM student WHERE sex = '女';
SELECT sex FROM student GROUP BY sex;
-- 查询男女各多少人
SELECT sex,COUNT(*) FROM student GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex,COUNT(*) FROM student WHERE age >25 AND COUNT(*)>2 GROUP BY sex;-- 错误,where不可以使用聚合函数进行判断
SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex HAVING COUNT(*)>2;
1.5 分页
准备数据
# 分页
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
-- 格式
select ... from 表名 limit [ 起始索引(0),]向后查询的个数;
-- 查询学生表中数据,从第三条开始显示,显示6条
SELECT * FROM student LIMIT 2,6;
-- 查询学生表中数据,显示前6条
SELECT * FROM student LIMIT 0,6;
SELECT * FROM student LIMIT 6;
-- 模拟百度分页,一页显示5条
-- 第一页
SELECT * FROM student LIMIT 0,5;
-- 第二页
SELECT * FROM student LIMIT 5,5;
-- 第三页
SELECT * FROM student LIMIT 10,5;
-- 额外知识点
-- 第N页 (n-1)*size,size
1.6 格式
select ... from 表名 [where 子句] [group by 子句] [having 子句] [order by 子句] [limit 子句]
2、数据库备份与还原
数据库迁移:备份、还原
2.1 dos窗口
-- 备份 转存
mysqldump -u用户名 -p密码 数据库名 > 导出文件路径
-- 还原 登录mysql
source 导入文件路径;
2.2 图形化工具
3、数据库约束
3.1 概述
对数据的进一步限制,来保证数据的正确性、有效性和完整性
约束的分类
primary key -- 主键约束 类似于 身份证号
unique -- 唯一
not null -- 非空
default -- 默认值
foreign key --外键
3.2 主键
用来唯一标识一条记录,一般使用id作为主键
添加主键约束
-- 创建表
create table 表名(
字段名 字段类型 primary key,
...
);
-- 已有表
alter table 表名 add primary key(字段名);
主键要求唯一和非空
一张表只能有一个主键;(主键可以包含多个字段,一般最多设置2个)
-- 主键
-- 错误Duplicate entry '14' for key 'PRIMARY'
ALTER TABLE student ADD PRIMARY KEY(id);
-- 创建表【掌握】
CREATE TABLE stu1(
id INT PRIMARY KEY,
NAME VARCHAR(32)
);
-- 插入记录
INSERT INTO stu1 VALUES(1,'tom');
-- 错误Duplicate entry '1' for key 'PRIMARY'
INSERT INTO stu1 VALUES(1,'tom');
-- 错误 Column 'id' cannot be null
INSERT INTO stu1 VALUES(NULL,'jerry');
-- 让name 也作为主键 唯一和非空
-- 错误 Multiple primary key defined
ALTER TABLE student ADD PRIMARY KEY (NAME);
-- 联合主键(id,name)
CREATE TABLE stu2(
id INT ,
NAME VARCHAR(32),
PRIMARY KEY (id,NAME)
);
-- 插入记录
INSERT INTO stu2 VALUES(1,'tom');
INSERT INTO stu2 VALUES(1,'jerry');
-- 错误 Duplicate entry '1-jerry' for key 'PRIMARY',只有id和name完全相同才会报错
INSERT INTO stu2 VALUES(1,'jerry');
主键自增
自增器起始值为1
-- 创建表
create table 表名(
字段名 字段类型 primary key auto_increment,
....
);
-- 已有表
alter table 表名 addprimary key(字段名) auto_increment;
-- 主键自增
CREATE TABLE stu3 (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
);
-- 插入记录
INSERT INTO stu3 VALUES(1,'tom');
INSERT INTO stu3 VALUES(NULL,'jack');-- 自增为2
ALTER TABLE stu3 AUTO_INCREMENT=10000;-- 设置自增初始值
-- 拓展知识点 id 类型为varchar 要求唯一的 生成全球唯一 36位随机字符串
SELECT UUID();
-- truncate 和 delete 区别
DELETE FROM stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
TRUNCATE TABLE stu3;
INSERT INTO stu3 VALUES(NULL,'jack');
turncate 和 delete 区别
delete,记录删除,DML语句
truncate,表摧毁,DDL语句
删除主键
-- 格式
alter table 表名 drop primary key;
-- 删除主键
ALTER TABLE stu3 DROP PRIMARY KEY;
-- 需要先移出自增器
ALTER TABLE stu3 MODIFY id INT ;
3.3 唯一
NULL为特殊的值,可以重复出现
-- 创建表
create table 表名(
字段名 字段类型 unique,
.....
);
-- 已有表
alter table 表名 add unique(字段名);
-- 唯一
CREATE TABLE stu4(
id INT,
NAME VARCHAR(32) UNIQUE
);
-- 插入记录
INSERT INTO stu4 VALUES(1,'tom');
-- 错误 Duplicate entry 'tom' for key 'name'
INSERT INTO stu4 VALUES(2,'tom');
INSERT INTO stu4 VALUES(3,NULL);
INSERT INTO stu4 VALUES(4,NULL);
3.4 非空
-- 创建表
create table 表名(
字段名 字段类型 not null,
...
);
-- 已有表
alter table 表名 modify 字段名 字段类型 not null;
-- 非空
CREATE TABLE stu5(
id INT,
NAME VARCHAR(32) NOT NULL
);
-- 插入记录
INSERT INTO stu5 VALUES(1,'tom');
-- 错误 Column 'name' cannot be null
INSERT INTO stu5 VALUES(2,NULL);
INSERT INTO stu5 VALUES(1,'tom');
3.5 默认值
数据库所有字段的默认值为NULL
-- 创建表
create table 表名(
字段名 字段类型 DEFAULT '默认值';
...
);
-- 已有表
alter table 表名
-- 默认值
CREATE TABLE stu6(
id INT,
NAME VARCHAR(32),
sex VARCHAR(6) DEFAULT '男'
);
-- 插入记录
INSERT INTO stu6 VALUES(1,'rose','女');
INSERT INTO stu6(id,NAME) VALUES(2,'tom');
INSERT INTO stu6 VALUES(3,'jerry',NULL);-- 更改为null
4、表关系
4.1 概述
在现实生活中,我们实体与实体之间是存在关系的,那么我们在设计数据库表的时候也应该体现出这种关系;
一对多
例如:班级和学生、部门和员工
多对多
例如:学生和课程、老师和学生
一对一
例如:公司和注册地、居民和身份证号
4.2 一对多
例如:班级和学生
-- 表关系
CREATE DATABASE db_day02_1;
USE db_day02_1;
-- 一对多
-- 班级表 主表
CREATE TABLE class(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
);
-- 插入记录
INSERT INTO class VALUES(1,'黑马76期');
INSERT INTO class VALUES(2,'黑马77期');
-- 学生表 从表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
class_id INT -- 外键
);
-- 插入记录
INSERT INTO student VALUES(1,'贺哥',1);
INSERT INTO student VALUES(2,'凡哥',1);
INSERT INTO student VALUES(3,'峰哥',2);
-- 给从表添加外键约束
-- 错误 Cannot add or update a child(从表) row
ALTER TABLE student ADD CONSTRAINT class_id_fk FOREIGN KEY(class_id) REFERENCES class(id);
-- 删除外键约束
ALTER TABLE student DROP FOREIGN KEY class_id_fk;
4.3 多对多
例如:学生和课程
-- 多对多
-- 课程表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32)
);
-- 插入记录
INSERT INTO course VALUES(1,'java');
INSERT INTO course VALUES(2,'ui');
INSERT INTO course VALUES(3,'python');
-- 中间表 从表
CREATE TABLE sc(
s_id INT,
c_id INT,
-- 联合主键
PRIMARY KEY(s_id,c_id),
-- 学生外键约束
FOREIGN KEY(s_id) REFERENCES student(id),
-- 课程的外键约束
FOREIGN KEY (c_id) REFERENCES course(id)
);
-- 插入记录
INSERT INTO sc VALUES(1,1);
INSERT INTO sc VALUES(1,2);
INSERT INTO sc VALUES(2,1);
INSERT INTO sc VALUES(2,3);
INSERT INTO sc VALUES(3,1);
INSERT INTO sc VALUES(3,2);
INSERT INTO sc VALUES(3,3);
4.4 一对一
企业开发中,一把情况下可以把一对多设计成一张表
例如:公司和注册地
4.5 外键约束
多张表有关系的记录进一步限制,保证记录的正确性、有效性和完整性
添加外键约束
-- 创建表 在从表中设置
create table 表名(
字段名 字段类型 ,
....
[constraint] [约束名] foreign key(外键字段名) references 主表(主键字段)
);
-- 已有表
alter table 表名 add [constraint] [约束名] foreign key(外键字段名) references 主表(主键字段);
特点
- 主表不能删除从表已引用的数据
- 从表不能添加主表未拥有的数据
- 先添加主表数据再添加从表数据
- 先删除从表数据再删除主表数据
删除外键约束
-- 格式
alter table 表名 drop foreign key 约束名;
复习
-
能够使用SQL语句进行排序
select * from 表名 order by 排序字段 [asc(升序) desc(降序)],排序字段 [asc(升序) desc(降序)]
-
能够使用聚合函数
select count(*) from 表名 where 条件; select sum(字段名) from 表名; select max(字段名) from 表名; select min(字段名) from 表名; select avg(字段名) from 表名;
-
能够使用SQL语句进行分组查询
select 分组字段名 from 表名 where 条件 order by 分组字段 having 条件;
-
能够完成数据的备份和恢复
-
能够使用SQL语句添加主键、外键、唯一、非空约束
create table 表名( 字段名 字段类型 primary key auto_increment,-- 主键 字段名 字段类型 unique,-- 唯一 字段名 字段类型 not null-- 非空 ); select * from 表名 add primary key(字段名); select * from 表名 add unique(字段名); select * from 表名 modify 字段名 字段类型 not null; select * from 表名 foreign key(作为外键的字段名) references 主表(主键字段);
-
能够说出多表之间的关系及其建表原则
一对多,多对多,一对一
一对多:主表与从表,主表的主键作为从表的外键
多对多:需要创建一个中间表,主表对中间表:一对多,从表对中间表:一对多,从而使得主表与从表的关系为多对多