今日学习内容
DQL语句之分组查询:group by
-- select 字段列表 from 表名 group by 分组字段名称;
-- 注意事项: 1)查询的字段列表中可以使用 分组字段
-- 2)group by之后不能使用聚合函数
-- 需求:按照性别分组,查询出他们的数学成绩的平均分
SELECT
sex '性别', -- 查询的分组字段
AVG(math) '数学平均分' -- 查询每一个组的数学平均分
FROM
student3
GROUP BY
sex ;
-- 带条件分组查询的语法: where 条件 必须放在group by 之前,否则语法错误!
-- select 字段列表包含分组字段,聚合函数.. from 表名 where 条件 group by 分组字段;
-- 需求:按照性别分组,查询出他们的数学成绩的平均分,数学成绩大于70分的参与分组
SELECT
sex '性别',
AVG(math) '数学平局分'
FROM
student3
WHERE
math > 70 -- 先满足条件,然后才能分组;
GROUP BY
sex ;
-- 筛选查询 having
-- where条件,group by,having 必须先有条件,分组,然后才筛选!
-- 注意:筛选的后面可以使用聚合函数,group by的后面是不能使用聚合函数的
-- 需求:按照性别分组,查询数学平局分,条件:数学成绩大于70的人参与分组, 筛选出人数大于2的这一组
SELECT
sex '性别',
COUNT(id) '总人数',
AVG(math) '数学平均分'
FROM
student3
WHERE
math > 70
GROUP BY
sex
HAVING -- 后面可以使用聚合函数
COUNT(id) > 2 ;
-- 优化为
SELECT
sex '性别',
COUNT(id) 人数,
AVG(math) '数学平均分'
FROM
student3
WHERE
math > 70
GROUP BY
sex
HAVING -- 后面可以使用聚合函数
人数 > 2 ;
-- 分页查询 limit
-- select 字段列表 from 表名 limit 起始行数,每页显示的条数;
-- 起始行数:从0开始算的,
-- 起始行数 = (当前页码-1)*每页显示的条数
-- 前提条件:每页显示2条,
-- 第一页数据
SELECT * FROM student3 LIMIT 0,2;
-- 第二页数据
SELECT * FROM student3 LIMIT 2,2 ;
-- 第三页数据
SELECT * FROM student3 LIMIT 4,2 ;
-- 第四页数据
SELECT * FROM student3 LIMIT 6,2 ;
-- 第五页数据
SELECT * FROM student3 LIMIT 8,2;
-- 第六页数据
SELECT * FROM student3 LIMIT 10,2;
-- 第七页数据
SELECT * FROM student3 LIMIT 12,2;
-- 插入4条数据
INSERT INTO student3 VALUES
(10,'张佳宁',32,'女','咸阳市',97,100),
(11,'文章',22,'男','渭南市',90,88),
(12,'盲僧',20,'男','宝鸡市',87,96),
(13,'ez',18,'男','艾欧尼亚',67,77);
-- 数据库的备份和还原
-- 方式1:图形界面化工具
/*
备份:选中库名,右键----->BACKUP/Export 备份导出---->
选中Sql脚本,存储到本地某个磁盘上----->StructureAndData:表的结构和数据都需要备份
*/
/*
还原:先在SqlYog将myee_2203删除了,新建库右键---->Import 导入---->选中执行execute sql 脚本
*/
-- 数据库的备份和还原命令行的方式2
/*
备份:
以管理员身份进入dos,不需要登录mysql
保存到指定路径上
mysqldump -uroot -p你的密码 库名 > 磁盘上的路径xx\xx\xx.sql
还原: 进入dos控制台,登录mysql
先将库删除,新建一个库
使用这个库 use库名;
加载指定路径的sql脚本
source 备份的sql脚本路径(D:\EE_2203\day9\code\mysql_01.sql)
*/
CREATE DATABASE myee_2203;
SHOW TABLES ;
-- 查询所有的学生
SELECT * FROM student3;
-- 数据库的约束
-- 概念:限制用户操作数据库的一种行为(非法行为)
-- 直接插入null值或者某个非业务字段id一直在重复
-- 创建一张表stu表
CREATE TABLE stu(
id INT,
NAME VARCHAR(20),
gender VARCHAR(3) DEFAULT '男' -- 加入默认约束
);
INSERT INTO stu VALUES(1,'高圆圆','女'),(2,'文章','男') ;
INSERT INTO stu(id,NAME) VALUES(3,'王宝强') ;
-- 上面gender字段没有插入数据,默认就是null,空值(没有意义)
-- 1)默认约束 default :如果某个字段没有插入数据,那么默认约束起作用了
DROP TABLE stu ;
-- 通过sql语句将默认约束去掉
ALTER TABLE stu MODIFY gender VARCHAR(3) ;
INSERT INTO stu(id,NAME) VALUES(4,'赵又廷') ;
-- 通过sql语句给gender字段加上默认约束
ALTER TABLE stu MODIFY gender VARCHAR(3) DEFAULT '男' ;
DELETE FROM stu WHERE id = 4;
-- 2)非空约束 not null,当前这个字段值不能为null
CREATE TABLE stu(
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
address VARCHAR(50) NOT NULL-- 地址 ,加入非空约束
) ;
INSERT INTO stu VALUES(1,'高圆圆',42,'西安市'),(2,'赵又廷',45,'北京市') ;
-- 地址插入一个null值
INSERT INTO stu VALUES(3,'张佳宁',32,NULL) ; -- 这种直接插入null也属于非法行为
-- 当address字段设置not null约束,直接限定用户不能插入null值
-- Column 'address' cannot be null
-- insert into stu(id,name,age) values(3,'张佳宁',32) ; 'address' doesn't have a default value:地址没有默认值
-- 通过sql语句将非空约束去掉--修改表的类型
ALTER TABLE stu MODIFY address VARCHAR(50) ;
DELETE FROM stu WHERE id = 3;
-- 通过sql语句将非空约束加上
ALTER TABLE stu MODIFY address VARCHAR(50) NOT NULL ;
DROP TABLE stu ;
-- 3)唯一约束unique (属于一种index :索引)
CREATE TABLE stu(
id INT , -- 编号
NAME VARCHAR(20), -- 姓名
phone_number VARCHAR(11) UNIQUE -- 电话号码 必须设置唯一的
) ;
INSERT INTO stu VALUES(1,'高圆圆','13388886666'),(2,'张三','13255559999') ;
INSERT INTO stu VALUES(3,'张佳宁','13388886666') ;-- 重复值 Duplicate entry '13388886666' for key 'phone_number'
-- 通过sql语句将唯一约束删除
-- alter table stu modify phone_number varchar(11) ; -- 修改表的字段类型
-- 删除唯一约束的语法:atler table 表名 drop index 索引的字段名称(就当前类的字段名称一致的) ;
ALTER TABLE stu DROP INDEX phone_number ;
-- 删除id为3的数据
DELETE FROM stu WHERE id = 3 ;
-- 通过sql语句添加唯一约束,此时语法就和修改表的字段类型的语法一致了
ALTER TABLE stu MODIFY phone_number VARCHAR(11) UNIQUE ;
DROP TABLE stu ;
-- 4)主键约束 primary key(非空且唯一的) 一般和auto_increment(自增长约束)使用多一些
-- 设置在id非业务字段上
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT , -- 编号 加入主键约束,加入自增长
NAME VARCHAR(20) -- 姓名
);
DROP TABLE stu ;
INSERT INTO stu VALUES(1,'高圆圆'),(2,'文章') ;
INSERT INTO stu VALUES(1,'赵又廷') ;-- Duplicate entry '1' for key 'PRIMARY' id是一个主键,值不能重复
INSERT INTO stu VALUES(NULL,'王宝强') ;-- Column 'id' cannot be null
-- 通过sql将主键约束删除
-- alter table 表名 drop PRIMARY KEY ;
ALTER TABLE stu DROP PRIMARY KEY;
DELETE FROM stu WHERE NAME = '赵又廷' ;
-- 通过sql语句将主键约束加入(唯一起作用)
ALTER TABLE stu MODIFY id INT PRIMARY KEY ;
-- 加入自增长约束之后,不需要给id赋值了,自己自增
INSERT INTO stu(NAME) VALUES('高圆圆'),('文章') ;
-- 自增长约束可以自己插入新的id,然后在之前id上继续自增
INSERT INTO stu VALUES(17,'赵又廷') ;
INSERT INTO stu(NAME) VALUES('张三') ;
-- mysql有一个函数:获取最后一次自增长主键的id值
SELECT LAST_INSERT_ID() ;
SELECT * FROM stu ;
-- 5)外键约束 foreign key
SHOW TABLES ;
DROP TABLE employee ;
-- 描述员工信息,创建一个表,员工编号,员工姓名,员工性别,员工所在部门
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
emp_name VARCHAR(10) , -- 员工姓名
emp_gender VARCHAR(5), -- 员工性别
emp_dept_name VARCHAR(10) -- 员工所在的部门名称
) ;
-- 插入员工表数据
INSERT INTO emp(emp_name,emp_gender,emp_dept_name)
VALUES('文章','男','开发部'),
('高圆圆','女','测试部'),
('王宝强','男','运维部'),
('姚笛','女','测试部'),
('赵又廷','男','开发部') ,
('马保国','男','运维部') ;
DROP TABLE emp;
-- 上面存在的问题:员工表中包含员工信息以及部门信息,这种情况不能在数据库中出现
-- 查询出来的数据部门信息存在冗余,重复性大,优化改进:一张表始终描述一件事情
-- 将员工表拆分出来:将部门信息放在部门表中
-- 创建一张部门表 dept表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长约束 部门编号
dept_name VARCHAR(20) -- 部门名称
) ;
-- 插入三个部门
INSERT INTO dept(dept_name) VALUES('开发部'),('测试部'),('运维部') ;
-- 创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
emp_name VARCHAR(10) , -- 员工姓名
emp_gender VARCHAR(5), -- 员工性别
dept_id INT -- 员工的部门id
) ;
INSERT INTO emp(emp_name,emp_gender,dept_id)
VALUES('文章','男',1),
('高圆圆','女',2),
('王宝强','男',3),
('姚笛','女',2),
('赵又廷','男',1) ,
('马保国','男',3) ;
-- 插入了一条员工数据
INSERT INTO emp(emp_name,emp_gender,dept_id) VALUES('张三丰','男',4) ;
-- 问题2:员工表中插入一条不存在的部门,两种表没有建立关系
-- 解决:外键约束 foreign key ,员工表dept_id 和部门表主键id进行关联
-- 外键所在的表----从表 (员工表)
-- 部门表: 主表
-- 员工表:从表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
emp_name VARCHAR(10) , -- 员工姓名
emp_gender VARCHAR(5), -- 员工性别
dept_id INT, -- 员工的部门id
CONSTRAINT -- 声明
dept_emp_fk -- 外键名称 命名规则:主表名称_从表名称_fk
FOREIGN KEY (dept_id) -- 作用在这个dept_id字段上
REFERENCES dept(id) -- 关联主表的主键id
) ;
DROP TABLE emp ;
-- 删除3号部门
-- 加入外键约束:修改和删除是很麻烦,需要先修改或者删除从表数据,然后修改/删除主表数据
-- 将id为3的和id为的3清除
DELETE FROM emp WHERE id= 3 OR id = 6 ;
DELETE FROM dept WHERE id = 3;
SELECT * FROM dept ;
SELECT * FROM emp ;
5.13作业练习
一.SQL练习-- 在sqlyog中书写sql语句,保存为5_13.sql文件
1)创建一个名称为mydb1的数据库
CREATE DATABASE mydb1;
2)查询当前所有数据库
SHOW DATABASES;
3)查询当mydb2数据库的字符集
SHOW CREATE DATABASE mydb1;
4)删除前面mydb1的数据库
DROP DATABASE mydb1;
5)创建库名为 my2203的库
CREATE DATABASE my2203;
6)创建一个部门表 dept
字段 类型
id int 部门编号
deptName varchar 部门名称
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(20)
);
7)一个员工表employee表
字段 类型
id int 员工编号
name varchar 员工名称
gender varchar 性别
birthday date 出生日期
salary double 工资
dept_id int 部门id
CREATE TABLE empolyee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender VARCHAR(10),
birthday DATE,
salary DOUBLE,
dept_id INT,
CONSTRAINT
dept_empolyee_fk
FOREIGN KEY (dept_id)
REFERENCES dept(id) );
在创建employee中dept_id上设置为外键,和部门表dept的id关联
8)给部门表中插入4部门,"it销售部","it开发部","it测试部",'it产品部'
INSERT INTO dept (deptName) VALUES ('it销售部'),('it开发部'),('it测试部'),('it产品部');
9)插入10个员工,分别设置在这四个部门中,数据自定义
INSERT INTO empolyee (NAME,gender , birthday ,salary,dept_id) VALUES ('张三','男','1978-05-03','15000',4);
('宋丹丹','女','1971-05-03','15000',3),
('李四','男','1968-05-03','15000',2),
('王婆','女','1966-05-03','15000',1),
('西门庆','男','1989-05-03','15000',4),
('潘金莲','女','1955-05-03','15000',3),
('赵本山','男','1921-05-03','15000',2),
('武松','男','1900-05-03','15000',1),
('李逵','男','2000-05-03','15000',3),
('宋江','男','1988-05-03','15000',3);
10)将所有员工工资修改为5000
UPDATE empolyee SET salary = '5000';
11)将姓名为 "李四" 的员工工资修改为4000 (员工中需要自己加入李四员工信息)
UPDATE empolyee SET salary='4000' WHERE NAME = '李四';
12)将"王五"的工资在原有的基础上增加1000(员工中需要自己加入王五员工信息)
UPDATE empolyee SET salary=(salary+1000) WHERE NAME = '王五';
13)删除表中'王五'的记录
DELETE FROM empolyee WHERE NAME = '王五';
二.DQL语句查询
创建exam表按照如图中字段创建
CREATE TABLE exam (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10) ,
math INT ,
chinese INT,
englishi INT
);
INSERT INTO exam (NAME,math,chinese,englishi) VALUES ('小毛',88,78,70),('小邓',90,91,92),('小江',65,66,68),('小胡',88,77,66);
考试表信息如图(QQ群)
1)查询表中所有的学生,需要给表以及字段起别名
SELECT id '学号',NAME '姓名',math'数学成绩',chinese'语文成绩',englishi'英语成绩' FROM exam ;
2)查询表中所有学生的姓名和对应的英语成绩
select name,englishi from exam;
3)统计每个学生的总分
select name ,(math + chinese + englishi )'总分'from exam;
4)查询姓名为小江的学生成绩
SELECT * FROM exam WHERE NAME = '小江';
5)查询英语成绩大于90分的同学
SELECT * FROM exam WHERE englishi>90;
6)查询总分大于200分的所有同学
SELECT NAME ,(math + chinese + englishi )'总分'FROM exam WHERE (math + chinese + englishi )>200;
7)查询英语分数在80-90的同学
SELECT * FROM exam WHERE englishi<90 && englishi>80;
SELECT * FROM exam WHERE englishi BETWEEN 80 AND 90;
8)查询数学分数为89,90,91的同学
SELECT * FROM exam WHERE math = 89 ||math=90 ||math = 91;
9)查询数学>80,语文>80的同学
SELECT * FROM exam WHERE math >80 && chinese>80;
10)对总分排序按从高到底的顺序输出
SELECT NAME ,(math + chinese + englishi )'总分'FROM exam ORDER BY (math + chinese + englishi ) DESC ;
11)对学生成绩按照英语降序,英语相同学员按照数学降序
SELECT * FROM exam ORDER BY englishi DESC, math ASC;
12)统计一个班级共有多少学生
SELECT COUNT(id) FROM exam;
13)查询数学成绩最高分的同学
SELECT NAME ,MAX(math) FROM exam;
14)统计数学成绩大于90分的学生有多少个
SELECT COUNT(id) FROM exam WHERE math >90 ;
15)统计总分大于220的人数有多少个
SELECT COUNT(id) FROM exam WHERE (math + chinese + englishi )>200 ;
16)统计一个班级数学总成绩
SELECT SUM(math)'数学总成绩' FROM exam;
17)统计一个班级语文,英语,数学的各科的总成绩
SELECT SUM(math)'数学总成绩' ,SUM(chinese)'语文总成绩',SUM(englishi)'英语总成绩'FROM exam;
18)统计一个班级语文,英语,数学的成绩总和
SELECT SUM(math)'数学总成绩' ,SUM(chinese)'语文总成绩',SUM(englishi)'英语总成绩',(SUM(math)+SUM(englishi)+SUM(chinese))'总总分'FROM exam;
19)统计一个班级语文成绩平均分
SELECT AVG(chinese) FROM exam;
20)给考试表插入5条数据后,使用分页查询,每页显示3条数据,查询第三页的数据
SELECT * FROM exam LIMIT 6,3;
三.设计三张表
部门表dept,部门id为主键并且自增长,dname为部门名称,Ioc为部门所在地
添加四个部门
职务表job,id 为主键自增长,jname职务名称,description职务描述
添加添加4个职务
员工表,id主键自增长,ename 员工姓名, job_id 职务的id(关联职务表的主键id),joindate入职日期(date类型)
,salary (double类型)工资, bonus 奖金 (double类型),dept_id(部门编号,关联部门表的id)
将上面三个表设计出来,员工表中两个外键,分别是job_id和dept_id,然后插入相关表数据;
分别完成查询所有的部门表,所有的员工表,所有的职务表数据,
SELECT * FROM emp;
SELECT * FROM dept1;
SELECT * FROM jop;
完成之后,测试一下,如果给员工表插入不存在的部门id以及职务id能否添加成功,看结果是否外键进行了关联!
CREATE TABLE dept1(
id INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(10),
loc VARCHAR(10)
);
CREATE TABLE jop(
id INT PRIMARY KEY AUTO_INCREMENT,
jname VARCHAR(10) ,
description VARCHAR(10)
);
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(10),
jop_id INT, CONSTRAINT FOREIGN KEY (jop_id) REFERENCES jop(id),
joindate DATE ,
salary DOUBLE,
bonus DOUBLE,
dept_id INT ,CONSTRAINT FOREIGN KEY (dept_id) REFERENCES dept(id)
);
学习感悟
新的知识点单词未理解通透。可照葫芦画瓢运用。需要多练习。