第二部分MySQL数据库
数据库初识：
数据
数据：描述事物的计算机所识别的符号记录
数据库
常见的数据库应用场景：
什么是DBMS?
常用数据库排名
MySQL简介：
MySQL最受欢迎的的可视化工具-------Navicat
Navicat的基本使用
连接数据库
新建数据库
MySQL基本命令
SQL分类
命令行操作数据库
登陆数据库
查看当前所有数据库
show databases;
创建数据库
create database Test;
切换数据库
use test_01;
查看数据表
show tables;
创建数据表
数据表创建规范
查看数据表的表结构
desc userinfo;
数据类型
数值类型
字符串类型
日期和时间类型
空值类型
数据字段属性
UNSIGNED、ZEROFILL、AUTO_INCREMENT
NULL、NOT NULL、DEFAULT
表数据的注释
实战小练1
Result:
实战小练2
Result
CREATE table student( StudentNo int(4) not null PRIMARY KEY COMMENT "学号", LoginPwd varchar(20) not null COMMENT "登录密码", StudentName VARCHAR(20) not null COMMENT "学员姓名", Sex int(4) not null COMMENT "性别", GradeID INT(11) not NULL COMMENT "年级编号", Phone VARCHAR(50) NULL COMMENT "联系电话", Address VARCHAR(50) null DEFAULT "地址不详" COMMENT "地址", BornDate datetime not null COMMENT "出生日期", Email varchar(50) null COMMENT "邮箱", IdentityCard VARCHAR(18) NOT NULL COMMENT "身份证号" );
修改数据表
实战演练：
修改数据表名
添加数据表字段
修改数据表字段
删除数据表
备份数据库
数据库数据管理
添加数据-INSERT
实战小练
表间复制
WHERE条件子句
修改数据-UPDATE
实战小练
删除数据-DELETE
实战小练
清空表数据-TRUNCATE
DQL-数据查询
查询数据-select
AS子句
查询场景
实战小练
数据表
Result：
DISTINCT关键字-去重
where条件语句
NULL空值条件查询
BETWEEN AND范围查询
使用IN进行范围查询
LIKE模糊查询
连表查询
原数据
业务需求：
查询名字中有江的出版社，都出了哪些书?
普通等值连接写法
完整版
简写版
连接查询
内连接查询-inner join
上面的需求：查询名字中有江的出版社，都出了哪些书?用inner join 实现
外连接查询
实战小练
原数据：
左连接表查询
右连接表查询
ORDER BY排序
LIMIT分页
实战小练
MySQL的聚合函数(统计函数)
实战小练
GROUP BY分组
实战小练
原数据
Result
对于书籍出版社平均id值低于10的不显示？
注意：
分组后，对聚合函数的结果进行二次过滤时，要加在 having关键字后面，不能放在where语句中
MySQL子查询
实战小练：
原数据
业务需求：
查询曲江出版社出了哪些书？
Result
MySQL案例实战
案例实战1
原数据
学生表：student(编号sid，姓名sname，年龄age，性别sex)
课程表：course(课程编号cid，课程名称cname，教师编号tid)
教师表：teacher(教师编号tid，姓名tname)
成绩表：sc(学生编号sid，课程编号cid，成绩score)
业务需求
1. 查询平均成绩大于60分的同学的学号和平均成绩
2. 查询所有同学的学号、姓名、选课数、总成绩
3. 查询姓“李”的老师的个数
4. 查询学过“李纯”老师课的同学的学号、姓名
5. 查询没学过“李雷”老师课的同学的学号、姓名
6. 查询“001”课程比“002”课程成绩高的所有学生的学号、姓名
7. 查询学过“001”并且也学过编号“003”课程的同学的学号、姓名
8. 查询各科成绩最高分和最低分：以如下形式显示：课程ID，课程名，最高分，最低分
9. 查询没有学全所有课的同学的学号、姓名
10. 查询学过“李雷”老师所教的所有课的同学的学号、姓名
11. 查询至少学过学号为“1001”同学所学的一门课的其他同学学号和姓名
12. 按各科平均成绩从低到高和及格率的百分数从高到低顺序排序
13. 删除学习“李纯”老师课的SC表记录
Result:
查询平均成绩大于60分的同学的学号和平均成绩?
查询所有同学的学号、姓名、选课数、总成绩
查询姓“李”的老师的个数
查询学过“李纯”老师课的同学的学号、姓名
第一种方法：
第二种方法：
select sc.sid '学号',sname '姓名' from sc inner join student st on sc.sid=st.sid inner join course on sc.cid=course.cid inner join teacher on course.tid=teacher.tid where tname="李纯";
查询没学过“李雷”老师课的同学的学号
方法1
方法2
查询“001”课程比“002”课程成绩高的所有学生的学号、姓名
查询学过“001”并且也学过编号“003”课程的同学的学号、姓名
查询各科成绩最高分和最低分：以如下形式显示：课程ID，课程名，最高分，最低分
select sc.cid,cname,max(score),min(score) from course inner join sc on course.cid=sc.cid GROUP BY sc.cid;
查询没有学全所有课的同学的学号、姓名？
select sc.sid,sname,count(*) "选课数" from student inner join sc on student.sid=sc.sid GROUP BY sc.sid HAVING 选课数 <(select count(*) from course);
查询学过“李雷”老师所教的所有课的同学的学号、姓名
查询至少学过学号为“1001”同学所学的一门课的其他同学学号和姓名
方法1
方法2
按各科平均成绩从低到高和及格率的百分数从高到低顺序排序
删除学习“李纯”老师课的SC表记录
查询不同课程成绩相同的学生的学号、课程号、学生成绩
自联表的应用
select DISTINCT a.sid,a.cid,a.score from sc a inner join sc b on a.score=b.score where a.cid<>b.cid and a.score=b.score ORDER BY a.score asc;
统计各科成绩的各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
case when分支判断的应用
select sc.cid "课程ID", cname "课程名称", sum(case when score BETWEEN 85 and 100 then 1 else 0 end) "[100-85]", sum(case when score BETWEEN 70 and 85 then 1 else 0 end) "[70-85]", sum(case when score BETWEEN 60 and 70 then 1 else 0 end) "[60-70]", sum(case when score BETWEEN 0 and 60 then 1 else 0 end) "[<60]" from sc inner join course on sc.cid=course.cid GROUP BY sc.cid;
查询所有学生的选课情况，显示学生姓名和选修的课程名？
提示：组内拼接函数 group_concat(要拼接的字段名)
select sname,GROUP_CONCAT(cname) from student s inner join sc on s.sid=sc.sid inner join course c on sc.cid=c.cid GROUP BY sc.sid;
把sc表中胡悦悦老师教的课的成绩都改为此课程的平均成绩
UPDATE sc inner join (select sc.cid sc_cid,avg(score) sc_avg from sc inner join course on sc.cid=course.cid INNER JOIN teacher
on course.tid=teacher.tid where tname="胡悦悦") a on a.sc_cid=sc.cid set score=a.sc_avg;
注意：
对同一张表查看的时候更新会引起数据不一致，将查询结果放在派生表中就可避免。
MySQL视图
数据库视图的含义
视图的作用
创建视图的语法
实战小练：
原数据：
需求：为讲师创建只能查看课程id为4的课程成绩和学生姓名？
查看视图
修改视图
更新视图
删除视图
MySQL的事务
事务的含义
事务的ACID原则
MySQL事务的实现方法
MySQL事务处理步骤
事务小练
MySQL触发器
触发器的定义
触发器使用场景
触发器语法
触发器实战
业务需求：
监控student表，student表新增一个学生stu_count表中的count字段就加1？
Result：
===========================================================触发器============================================= # 创建stu_count表 create table stu_count( count INT(4) null DEFAULT 0, modif_data datetime ); # 向stu_count表中插入初始数据 insert into stu_count VALUES(1,now()); # 创建触发器 create TRIGGER count_stu after insert on student for each row begin update stu_count set count=count+1,modif_data=now(); end; 测试触发器 insert into student values(1010,"鲁班",19,"男");
删除触发器 drop trigger count_stu;
删除触发器
触发器必知
MySQL日期函数
返回当前的日期和时间
select now();
返回当前的日期
select curdate();
添加时间隔离
date_add(date,INTERVAL expr unit)
返回5年后的今天
select DATE_ADD(curdate(),INTERVAL 5 year);
返回5年前的今天
select DATE_ADD(curdate(),INTERVAL -5 year);
返回5周后的今天
select DATE_ADD(curdate(),INTERVAL 5 week);
获取当月的最后一天
select LAST_DAY(DATE_ADD(curdate(),INTERVAL -1 month)) ;
获取当月第一天
select DATE_ADD(curdate(), interval -day(curdate())+1 day);
select DATE_ADD(LAST_DAY(DATE_ADD(curdate(),INTERVAL -1 month)),INTERVAL 1 day);
日期格式化函数
date_format(date,format)
select DATE_FORMAT(curdate(),'%Y-%m-%D');
日期格式化参数：
Y完整年 y取后两位年
M英语月份 m数字型月份
D英语日 d数字日
H24进制时 h12进制时
i分钟 s秒
W英语的星期 w中文的星期
MySQL案例二
原数据：
获取每个性别的人数
select Gender,count(*) from cdsgustable where gender in("M","F")GROUP BY Gender;
查询2012年的数据
select * from cdsgustable where year(version)=2012; select * from cdsgustable where version like "2012%";
查询2012年12月的数据
select * from cdsgustable where version like "2012-12%"; select * from cdsgustable where year(version)=2012 and month(version)=12; select * from cdsgustable where DATE_FORMAT(version,"%Y-%m")="2012-12";
查询姓王的人数
select count(*) from cdsgustable where cname like "王%";
查询姓李的，并且CTFTP不是OTH的人数
select count(*) from cdsgustable where cname like "李%" and ctftp<>"OTH";
查询现在年龄大于30岁的人数
select count(*) from cdsgustable where year(curdate())-year(birthday)>30;
根据出生年月日，转换为星期，现实出生人最多的那个星期几的英语单词
select date_format(birthday,"%W") week_name,count(*) week_num from cdsgustable GROUP BY date_format(birthday,"%W") ORDER BY week_num desc limit 1;
获取每年的男女性别所占的百分比，并保留两位小数（结果如下所示）
提示：保留几位小数用函数round(值,保留小数位数)
select year(birthday), CONCAT(round(sum(case when gender="F" then 1 else 0 end)/count(*)*100,2),"%") "女", CONCAT(round(sum(case when gender="M" then 1 else 0 end)/count(*)*100,2),"%") "男",count(*) from cdsgustable where gender in("F","M") GROUP BY year(birthday);
MySQL存储过程
变量
系统变量
用户变量
局部变量
存储过程
存储过程基本语法
创建无参的存储过程 （每调用一次，就往某张表添加4条数据）
create table td(id int(4),name varchar(32));
创建存储过程
CREATE procedure p1() BEGIN insert into td(id) VALUES(1),(2),(3),(4); end;
使用存储过程
call p1();
select * from td;
创建有参存储过程
create procedure 存储过程名(参数列表) 输入 输出 输入输出 in out inout begin <SQL 语句> end;
创建带inout模式参数的存储过程
传入两个值，最后两个值都翻倍返回 CREATE procedure p6(inout a int,inout b int) BEGIN set a=a*2; set b=b*2; end; ---调用 set @m=10,@n=20; CALL p6(@m,@n); SELECT @m,@n;
存储过程实战
原数据：
业务需求
案例1 ：根据女生名，查询对应的男生信息；
参数列表形式 ([in | out |inout] 参数名称 参数类型 create procedure p2(in girlname char(32)) BEGIN SELECT b.* from boy b INNER JOIN girl g on b.id=g.boyfriend_id WHERE g.name=girlname; end; ---调用函数 call p2('张三');
案例2：创建存储过程，实现用户是否登录成功
--------创建存储过程用户是否登录成功 create PROCEDURE p3(in username varchar(32),in pwd varchar(32)) BEGIN DECLARE result VARCHAR(32) DEFAULT ''; SELECT count(*) into result from admin a WHERE a.username=username and a.password=pwd; SELECT if(result=1,'成功','失败') '是否登录成功！'; end; -- 先删除再运行 方法二 通过图形化界面 drop PROCEDURE p3; ------调用 call p3('a006661','123456');
案例3：根据女生名，返回对应的男生名
create PROCEDURE p4(in girlname VARCHAR(32),out boyname varchar(32)) BEGIN SELECT b.name INTO boyname from boy b INNER JOIN girl g on b.id=g.boyfriend_id where g.name=girlname; end; ----- 调用 call p4('王莹',@boyname); select @boyname;
案例4：根据女生名，返回对应的男生名和男生魅力值
create PROCEDURE p5(in girlname VARCHAR(32),out boyname varchar(32),out v int) BEGIN SELECT b.name,b.value INTO boyname,v from boy b INNER JOIN girl g on b.id=g.boyfriend_id where g.name=girlname; end; --------------------- call p5('李锦花',@boyname,@v); SELECT @boyname,@v;
案例5：创建存储过程实现传入用户名和密码，插入到admin表中
create PROCEDURE pp1(in uname varchar(32),in pwd VARCHAR(32)) begin insert into admin(username,password) VALUES(uname,pwd); end call pp1('鲁班','123456');
案例6：创建存储过程实现传入女生编号，返回女生名称和女生电话
create PROCEDURE pp4(in gid INT(11),out gname VARCHAR(32),out gphone VARCHAR(32) ) begin select g.name,g.phone into gname,gphone from girl g where g.id=gid; end call pp4(1,@gname,@gphone); select @gname,@gphone;
MySQL创建和使用索引
索引定义
数据库索引的作用和分类
创建索引
MySQL的索引区别
管理索引
索引准则
MySQL字符串函数
MySQL数字函数
CEIL向上取整函数
FLOOR向下取整函数
round随机数函数
MySQL的窗口函数
窗口函数实战
原数据
需求分析
统计每个用户最大的连续登录数(使用窗口函数实现)
第一步：通过用户id分组，在组内日期排序
SELECT uid,loadtime, row_number()over(PARTITION BY uid ORDER BY loadtime) as rn from user;
第二步：用登入日期的天数与行号做差，得到的Drn相同即表示是连续登录
SELECT uid,loadtime, row_number()over(PARTITION BY uid ORDER BY loadtime) as rn, (Day(loadtime)-row_number()over(PARTITION BY uid ORDER BY loadtime)) as Drn from user;
第三步 根据uid,Drn 分组，得到的count() 就为连续登陆的天数
SELECT uid,Drn,count(*) from (SELECT uid,loadtime, row_number()over(PARTITION BY uid ORDER BY loadtime) as rn, (Day(loadtime)-row_number()over(PARTITION BY uid ORDER BY loadtime)) as Drn from user) as bb GROUP BY uid,Drn;
第四步 取count 最大值
select uid,max(最大天数) from (SELECT uid,Drn,count(*) '最大天数' from (SELECT uid,loadtime, row_number()over(PARTITION BY uid ORDER BY loadtime) as rn, (Day(loadtime)-row_number()over(PARTITION BY uid ORDER BY loadtime)) as Drn from user) as bb GROUP BY uid,Drn) as aa GROUP BY uid;
浙公网安备 33010602011771号