第一天: mysql数据库入门
1.数据库定义:
数据库(简称DB),顾名思义,就是存储数据的仓库,可以对数据进行持久化保存.(数据保存
在内存里面是瞬时状态,开关机就没有了)
系统:通过一个可视化的界面通过java逻辑获取到数据库的数据进行展示;比如将学生信息展示在界面上,需要经过: 数据库 --> JavaJDBC技术(Java Database Connectivity Java数据库连接技术 )
将数据库的数据获取到 --> 展现在前端页面上. 或者说将某个学生信息存储到数据库里面的经过:在
前端表单输入 -->java获取到前端数据的数据 -->存储到数据库中.
DBA: databaseadmin数据库管理员;
2.数据库分类:
数据库分为两大类:
1.关系型数据库:mysql, oracle, sqlserver\db2等等
2.非关系型数据库: Redis mogoDB
mysql特点:
免费,轻量级(安装包很小);适合于数量不是很大的系统,比如网站, app等.
oracle数据库特点:
目前和mysql都属于oracle公司,oracle数据库是oracle公司最核心的产品,安装包大(1个多G),功能强大;
使用免费,也有付费版本. 多用于政府,国企等大型企业,存储的数据量比较大.
SqlServer数据库:
Microsoft微软的, 免费, 因为SQLServer不能跨平台,只能在windows系统上运行; 微软有自己支持的开发语言c#, 但是不可跨平台慢慢就没落了. 说白点就是绑定销售windows系统.
mysql安装包:
1.安装服务器端, 注意: root, 编码格式模式是拉丁文要改成utf8, 端口号3306
2.安装好服务端,安装客户端; SQL yog客户端;
如果你没有客户端, 也可以通过dos命令操作mysql数据库.
1.启动mysql服务
net start mysql
2.关闭服务
net stop mysql
3.登录mysql
mysql -u root -p sa123
4.显示数据库
show database;
5.使用该数据库那张表
user mysql;
6.查看mysql的表结构
desc mysql;
7.退出mysql
exit;
注意问题:
1.客户端登录报2003,很有可能是mysql服务端没有启动,先启动服务端才能够使用mysql数据库
3.SQL分类:
操作数据库都是用的SQL命令,SQL(Structured Query Language)叫做结构化查询语句
其实每个数据库都有自己的SQL语句,但是都遵照SQL-92标准.
SQL分类:
1.DML: (Data Manipulation Language)数据操作语言:对数据增删改
2.DDL: (Data Definition Language)数据定义语言: 创建和删除数据库或者表
3.DCL: (Data Control Languege)数据控制语言: 用于控制数据库组件的存取许可,存取权限许可,存取权限,事务提交回滚等操作
4.DQL: (Data Query Language)数据查询语言:对数据的查询
如图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X3UOlzNZ-1634887477641)(C:\Users\Achuan\AppData\Roaming\Typora\typora-user-images\image-20211019150415498.png)]
4.SQL运算符
算数运算符: + - * / %
赋值运算符: =
逻辑运算符: and or not
比较运算符: = > < !=(不等于,非sql-92标准) <>(不等于)
数据库分为系统数据库和自定义数据库,系统数据库是自带的,用于记录用户对mysql数据库的欣慰
操作等记录.
自定义的数据库是用于存储表以及表里面的数据用的.
5.数据库类型
数据类型是给不同的数据开辟不同大小空间,好处是节约空间.
1.数值类型
2.字符串类型
3.char类型和varchar的区别:
char(10)是定义一个固定长度字符串,比如将zhang存入会将剩余的空间以空字符代替,比如
“zhang”;char一般用于固定的长度,比如MD5加密处理生成的都是长度固定的字符串.
varchar(10)是定义一个可变长度字符串,比如一开始定义一个了10个长度,但是存入’zhang’只有
5个字符,它将压缩剩余的空间变成5个字符串的长度,反之会扩容.
6.建数据库:
DROP DATABASE mydb;
CREATE DATABASE mydb;
7.建表:
具体语法参考菜鸟教程https://www.runoob/mysql/mysql-create-tables.html
#2.创建表
USE mydb;
CREATE TABLE IF NOT EXISTS student(
studentNo INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
studentName VARCHAR(10) NOT NULL COMMENT'姓名',
sex CHAR(2) DEFAULT'男' NOT NULL COMMENT'性别',
gradeId INT(5) NOT NULL COMMENT '年级ID',
address VARCHAR(50),
borndate DATETIME COMMENT '出生日期',
identityCard INT(10) NOT NULL UNIQUE KEY COMMENT '身份证号'
)CHARSET=UTF-8;
说明:
1.not exists 可选项
2.PRIMARY KEY 是标识主键,主键的作用是包装这条数据的完整性(唯一性),标识了主键的列不能为null,
值不能重复,一个表只能有一个主键,多个列可以组成复合主键
3.AUTO_INCREMENT让键的值自增(该列一定是int类型)
4.所有的长度单位都是以字节为单位.
5.DEFAULT '男'设置默认值,如果该列不插值,值默认就是男
6.UNIQUE KEY 唯一键,标识的列不能重复
8.insert插入语句
INSERT INTO student
(studentName,sex,gradeId,address,borndate,identityCard)
VALUES('giao','男',1,'北京','1999-1-1',10011);
INSERT INTO result VALUES(501,2,'2020-1-3',90),(501,1,'2020-2-
2',56),501,1,'2020-2-2',56);
注意:
1.插入的列的顺序, 个数, 类型一定要值一致
2.如果你要插入指定的列就写需要插入的列和值就可以;如果插入该表所有的列,列可以不写!
9.可以设置自增起始值
#可以设置自增起始值和递增跨度
#设置单表的起始值
ALTER TABLE student AUTO_INCREMENT=500;
#设置自增列的跨度,每次+5
SET @@auto_increment = 5;
#也可以
ALTER TABLE 'mydb','student'
AUTO_INCREMENT=2,MIN_ROWS=1;
10.插入默认值
#插入默认值
INSERT INTO student
(studentName,sex,gradeId,address,borndate,identityCard)
VALUES('giao',DEFAULT,1,'北京','1999-1-1',10013);
INSERT INTO student
(studentName,gradeId,address,borndate,identityCard)
VALUES('giao2',1,'北京','1999-1-1',10014);
11.delete删除表中数据
DELETE FROM student;
#where是筛选的查询条件 and是条件1和条件2都要同时满足
DELETE FROM student WHERE studentName='giao3' AND gradeId=1;
12.select查询表中所有的数据
#查询所有列
SELECT * FROM student;
#查询指定列
SELECT studentName,address FROM student;
#查询giao6的学号,地址
SELECT studentName,address FROM student WHERE studentName='giao6';
13.root管理员创建用户
第二天:查询
表关系:
1.设计数据库的三大范式
我们为什么去设计上图这四张表?为什么不把这四张表的数据全部放到一张表里呢?比如全部放到一张表里面,如果该学生没有成绩势必造成成绩的字段空着,如果该年级没有科目势必科目的字段空着,这样造成严重的冗余.所以我们设计数据库要遵循三大范式这个设计规范来作为参考来设计我们的数据表.
三大范式:
第一大范式(1NF):保障列的原子性,列不可再细分.
第二大范式(2NF):在第一范式的基础上,表中每个字段直接依赖于主键,通俗点讲就是每个表都只描述一个事物(和我们java类一样,每个类都做自己的事情).学生表只存学生信息科目表存科目信息.
第三范式:在第一,二范式基础上,非主键的字段不直接依赖.通俗点讲,每个类要与主键之间存在直接关系.
不存在传递依赖.
2.alter语句
#修改表名
ALTER TABLE student2 RENAME TO student;
#添加字段
ALTER TABLE student ADD love VARCHAR(10) NOT NULL;
#修改字段
ALTER TABLE student CHANGE love love2 INT(4);
#删除字段
ALTER TABLE student DROP love;
3.添加主键约束
#添加主键约束
ALTER TABLE grade
ADD CONSTRAINT pk_grade PRIMARY KEY(gradeId);
#查看表的定义
DESC grade;
4.添加外键约束
#如果约束学生表的gradeID必须是grade表中的gradeID主键里面的值.
#就必须去设置主外键约束
ALTER TABLE student
ADD FOREGING KEY(gradeId) REFERENCES grade(gradeId);
#因为设置了主外键的关系,所以导致错误的gradeId=4插入不进去报错
INSERT INTO student(studentName,sex,gradeId,address,borndate,identityCard)
VALUES('张飞','女',4,'南昌','1999-9-9',10017);
#如果设置了主外键关联,一定要先删除从表的数据,再去删主表
DELETE FROM student;
DELETE FROM grade;
#所以主外键关联既然这么麻烦,建议在项目中就不要使用,建议逻辑关系
5.其他约束:
#非空约束
ALTER TABLE Persons
MODIFY Age INT NULL;
#唯一约束
ALTER TABLE Persons
ADD UNIQUE(p_Id)
#默认约束
ALTER TABLE student
MODIFY sex DEFAULT '男'
SELECT * FROM result
#检查约束
ALTER TABLE result
ADD CHECK(studentresult>=0 AND studentresult<=100)
6.update修改语句
#修改语句
UPDATE student SET studentName='李四'
WHERE studentName='giao3';
UPDATE student SET studentName='王五',sex='女'
WHERE studentNo=511;
#修改李四的身份证号码+5
UPDATE student SET identityCard=identityCard+5 WHERE studentName='李四'
7.like, is null, between, in 列举模糊查询
#查询student表中所有的列的数据
SELECT * FROM student
#按姓名查询
SELECT * FROM student WHERE studentName='夏一桐';
#多条件查询指定的值
SELECT studentNo,studentName,sex,gradeId,address
FROM student WHERE studentName='夏一桐' AND gradeId=1
#模糊查询 找姜XXX
SELECT * FROM student WHERE studentName LIKE '姜%'
#找姜X
SELECT * FROM student WHERE studentName LIKE '姜_'
#找XXX海淀区XXX
SELECT * FROM student WHERE address LIKE '%海淀区%'
#找到email的值以a-e开头
SELECT * FROM student WHERE email REGEXP '^[a-e]'#起别名
#电话135或138开头
SELECT * FROM student
WHERE phone LIKE '135%' OR phone LIKE '138%'
#起别名
SELECT studentNo AS '学号',studentName AS '姓名',sex '性别',gradeId,address AS
dizhi FROM student WHERE studentName='夏一桐' AND gradeId=1
#不等于
SELECT studentNo,studentName,sex,gradeId,address
FROM student WHERE gradeId != 1
#不等于(92SQL标准)
SELECT studentNo,studentName,sex,gradeId,address
FROM student WHERE gradeId <> 1
#查询空null是没有数据的
SELECT * FROM result WHERE studentresult IS NULL
#查询不为空的
SELECT * FROM student WHERE email=''
SELECT * FROM result
#查询在60-80之间的(包含)的分数,只能从小到大
SELECT * FROM result WHERE studentresult BETWEEN 60 AND 80
#等同于:
SELECT * FROM result WHERE studentresult>=60 AND studentresult<=80
#####################IN 列举 ######################
#主要查询成绩是60,70,80的成绩信息
SELECT * FROM result WHERE studentresult IN (60,70,80)
SELECT r.studentNo,s.studentName,r.studentresult FROM result r
LEFT JOIN student s ON r.studentNo = s.studentNo
WHERE r.studentresult IN(60,70,80)
8.聚合函数
SELECT COUNT(studentNo) from student
#注意如果列里面有null是不作为统计(所有聚合函数都是如此)
#所以用*号统计多少行
SELECT COUNT(*) FROM student
#计算总和
SELECT SUM(studentresult) FROM result WHERE subjectId=1
#计算平均分(null的值不会统计)
SELECT AVG(studentresult) FROM result WHERE subjectId=1
#计算总分/总人数
SELECT SUM(studentresult)/COUNT(*) FROM result WHERE subjectId=1
SELECT MAX(studentresult)'最高分',MIN(studentresult)'最低分',
AVG(studentresult)'平均分',SUM(studentresult)'总分' FROM result WHERE subjectId=1
9.order by排序
#ASC 升序
SELECT * FROM result WHERE subjectId=1
ORDER BY studentresult ASC
#多条件排序,先后顺序
SELECT * FROM result
WHERE studentresult >= 60
ORDER BY subjectId ASC,studentresult DESC
#DESC 降序
SELECT * FROM result
WHERE subjectId=1
ORDER BY studentresult DESC
#查询科目1的成绩前三名
SELECT * FROM result
WHERE subjectId=1
ORDER BY studentresult DESC
LIMIT 0,3
10.limit偏移量
#LIMIT可以用于mysql分页
SELECT * FROM result
WHERE subjectId=1
ORDER BY studentresult DESC
LIMIT 0,5
SELECT * FROM result
WHERE subjectId=1
ORDER BY studentresult DESC
LIMIT 5,5
11.group by 分组
#查询每个年级的人数
#注意分组查询的列一定是分组的字段和聚合函数
SELECT gradeId,COUNT(*) FROM student
GROUP BY gradeId
#查询每个年级男女各自的人数
#多余条件分组有先后顺序
SELECT gradeId,sex,COUNT(*) FROM student
GROUP BY gradeId,sex
#查询年级人数最高的两个年级的信息
#先年级分组查询再排序再偏移量取前2
SELECT gradeId,COUNT(*) FROM student
GROUP BY gradeId
ORDER BY COUNT(*) DESC
LIMIT 0,2
#查询每个学生的平均分,得到前三名
SELECT studentNo,AVG(studentresult) FROM result
GROUP BY studentNo
ORDER BY AVG(studentresult) DESC
LIMIT 0.3
#查询每个科目及格的人数
SELECT subjectId,COUNT(*) FROM result
WHERE studentresult >= 60
GROUP BY subjectId
12.having分组后筛选
#查询每个科目及格的人数大于10人的科目
#having和where都是筛选条件,没有分组用where,分组后用having
SELECT subjectId,COUNT(*) FROM result
WHERE studentresult >= 60
GROUP BY subjectId
HAVING COUNT(*)>10
#查询及格的人数大于10人的科目数量高的科目
SELECT subjectId,COUNT(*) FROM result
WHERE studentresult >= 60
GROUP BY subjectId
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC
LIMIT 0,1
13.distinct去重复
SELECT COUNT(*) FROM result
#DISTINCT 字段可以去除指定字段的重复数据
SELECT DISTINCT studentNo FROM result
#如果distinct多个列,会将过滤掉studentNo,subjectId两个字段重复的值
SELECT DISTINCT studentNo,subjectId FROM result
#注意:DISTINCT必须放到最前面
SELECT studentNo, DISTINCT subjectId FROM result
#最常用的是用于返回不重复字段的记录,比如有多少人考试了
SELECT COUNT(DISTINCT studentNo) FROM result
14.行转列[面试题]
DROP TABLE IF EXISTS studentscore;
CREATE TABLE studentscore(
'name' VARCHAR(10),
'subjectName' VARCHAR(10),
'score' INT(4)
);
INSERT INTO studentscore VALUES
('张三','英语',90),
('张三','数学',80),
('张三','语文',45),
('李四','英语',12),
('李四','数学',23),
('李四','语文',54);
实现步骤:
步骤1:
#步骤1:先根据多条件判断,统计行
SELECT 'name',
CASE subjectName WHEN '英语' THEN score ELSE 0 END '英语',
CASE subjectName WHEN '数学' THEN score ELSE 0 END '数学';
CASE subjectName WHEN '语文' THEN score ELSE 0 END '语文'
FROM studentscore
步骤2:
#步骤2根据姓名分组,然后聚合函数统计
SELECT 'name',
MAX(CASE subjectName WHEN '英语' THEN score ELSE 0 END)'英语',
MAX(CASE subjectName WHEN '数学' THEN score ELSE 0 END)'数学',
SUM(CASE subjectName WHEN '语文' THEN score ELSE 0 END)'语文'
FROM studentscore
GROUP BY NAME
第三天:多表联合查询
联合查询就是通过表之间的关联逻辑,将两表或者更多的表联合来查询显示.
联合查询分为内连接,外连接,全连接(基本不用)
1.内连接
内连接分为等值连接和inner join两种方式,两种方式作用一样只是写法不一样而已.
#########################联合查询###############################
#需要查看到s1年级的学生学号,姓名,年级ID,年级名称,地址
SELECT studentNo,studentName,gradeId,gradeName,address FROM student,grade
WHERE student.gradeId=grade.gradeId
#内连接 方式1:等值连接(交叉连接)[两个表的gradeId的值相等就匹配出来,不相等就不匹配]
SELECT s.gradeId=g.gradeId
AND g.gradeId='S1'
SELECT * FROM result
SELECT * FROM subject
#查询走进Java编程世界这门课程的成绩
SELECT r.studentNo,j.subjectName,r.studentresult,r.examdate FROM result r,
subject j
WHERE r.subjectId=j.subjectId
AND j.subjectName='走进Java编程世界'
#查询走进Java编程世界这门课程的成绩
SELECT r.studentNo,s.studentName,j.subjectName,r.studentresult,r.examdate
FROM result r,subject j,student s
WHERE r.subjectId=j.subjectId
AND r.studentNo=s.studentNo
AND j.subjectName='走进JAve编程世界'
#内连接 方式2:inner join
#注意: inner join和上面等值连接作用都是一样,只是写法不一样
SELECT r.studentNo,j.subjectName,r.studentresult,r.examdate FROM result r,
subject j
WHERE r.subjectId=j.subjectId
AND j.subjectName='走进Java编程世界'
#inner join 写法
SELECT r.studentNo,j.subjectName,r.studentresult,r.examdate
FROM result r,subject j,student s
WHERE r.subjectId=j.subjectId
AND r.studentNo=s.studentNo
AND j.subjectName='走进Java编程世界'
SELECT r.studentNo,s.studentName,j.subjectName,r.studentresult,r.examdate
FROM result r,subject j,student s
WHERE r.subjectId=j.subjectId
AND r.studentNo=s.studentNo
AND j.subjectName='走进Java编程世界'
SELECT r.studentNo,s.studentName,j.subjectName,r.studentresult,r.examdate
FROM result r
INNER JOIN subject j ON r.subjectId=j.subjectId
INNER JOIN student s ON r.studentNo=s.studentNo
WHERE j.subjectName='走进Java编程世界'
SELECT * FROM result
SELECT * FROM subject
#查询平均分大于60的学生学号,姓名,平均分
SELECT s.studentNo,s.studentName,AVG(studentresult)
FROM result r
INNER JOIN student s ON r.studentNo=s.studentNo
GROUP BY studentNo
HAVING AVG(studentresult)>60
2.外连接
外连接也分为左外连接和右外连接.
########################外连接#####################
SELECT * FROM result
#查询所有学生的姓名,年龄,地址
#left左连接,以left关键字左边的为主键查询left左边这张表所有的数据
#然后再去关联left右边的表,关联上显示数据,关联不上就显示null
#因此查询到了所有的学生但是雷应飞这个学生没有匹配的年级
SELECT s.studentName,s.gradeId,s.gradeName,s.address FROM student s
LEFT OUTER JOIN grade g ON s.gradeId=g.gradeId
SELECT s.studentName,s.gradeId,g.gradeName,s.address FROM student s
LEFT JOIN grade g ON s.gradeId=g.gradeId
#查询每个学生的成绩信息
#如果我们使用内连接,student表中studentNo和result的studentNo去匹配,如果匹配上显示,匹配不上不显示
SELECT s.studentName,r.studentNo,r.studentresult FROM student s
LEFT JOIN result r ON s.studentNo=r.studentNo
SELECT s.studentName,r.studentNo,r.studentresult FROM student s
RIGHT JOIN result r ON s.studentNo=r.studentNo
3.union关键字((了解即可)
union关键字是将多个表的结果集组合成一个新的结果集合
SELECT studentNo,subjectId,studentresult FROM result WHERE subjectId=1
UNION
SELECT studentNo,subjectId,studentresult FROM result WHERE subjectId=2
UNION
SELECT subjectName,classHour,gradeId FROM subject WHERE gradeId=1
4.避免笛卡尔积
没有两边关联的条件筛选,获取到的笛卡尔积结果集,这是必须要避免的!
SELECT * FROM result,subject
5.系统函数
1.日期函数
2.字符串函数
3.数学函数
第四天:子查询
子查询就是在SQL里面嵌套查询语句,子查询查到的结果作为外层父查询的一个条件.
1.子查询操作
#子查询
SELECT * FROM student WHERE studentName='白燕'
#查询出生年龄比白燕小的学生信息
SELECT * FROM student
WHERE borndate>#白燕的出生日期
#子查询:子查询查询到的结果作为外层查询的条件
SELECT * FROM student
WHERE borndate>(SELECT borndate FROM student WHERE studentName='白燕')
#查询白燕前面的和后面各自一个学生
#列举
SELECT * FROM result WHERE studentresult IN(60,70,80);
#当里面有多个值匹配的时候,用in列举
SELECT * FROM student
WHERE studentNo IN(
(SELECT CONCAT(SUBSTR(studentNo,1,7),(SUBSTR(studentNo,7,8)-1))
FROM student WHERE studentName='白燕'
),
(SELECT CONCAT(SUBSTR(studentNo,1,7),(SUBSTR(studentNo,7,8)+1))
FROM student WHERE studentName='白燕'
))
#用子查询走进java编程世界的成绩信息
SELECT * FROM result r WHERE
r.subjectId=(SELECT subjectId FROM subject j WHERE j.subjectName='走进Java编程世界')
#查询学生的学号,科目名称,成绩信息
#会报错:原因是子查询的表不能作用域父查询,所以子查询只能查询result表
#如果非要查询两张表,请看下面案例
SELECT r.studentNo,j.subjectName,r.studentresult FROM result r WHERE
r.subjectId=(SELECT subjectId FROM subject j WHERE j.subjectName='走进Java编程世界')
#子查询可以在任意位置写:但是下面我们用子查询的方式不如两表联查
SELECT r.studentNo,(SELECT subjectName FROM subject sj WHERE
sj.'subjectId'=1),r.studentresult FROM result r WHERE
r.subjectId=(SELECT subjectId FROM subject j WHERE j.subjectName='走进java编程世界')
SELECT r.studentNo,j.subjectName,r.studentresult FROM result r
INNER JOIN subject j ON r.subjectId=j.subjectId
WHERE j.subjectName='走进Java编程世界';
#查询参加最近一次'走进Java编程世界'这门课程考试的学生的最高分和最低分
SELECT * FROM result r
WHERE r.'subjectId'=(走进java编程世界的subjectId)
AND r.'examdate'=(查询到走进java编程世界的这门课程max(examdate)考试日期)
SELECT MAX(studentresult),MIN(studentresult) FROM result r
WHERE r.'subjectId'=(SELECT subjectId FROM subject j WHERE j.'subjectName'=
'走进java编程世界')
AND r.'examdate'=(
SELECT MAX(examdate) FROM result r
INNER JOIN subject j ON r.'subjectId'=j.'subjectId' WHERE j.'subjectName'=
'走进java编程世界'
)
#查询走进java编程世界这门课程的考试成绩为60的学生名单
#步骤1:查询到走进java编程世界这门课程考试成绩60的学号
#步骤2:再根据学号查询学生名单
SELECT * FROM student WHERE studentNo
IN(
SELECT r.studentNo FROM result r
INNER JOIN subject j ON r.'subjectId'=j.'subjectId'
WHERE j.subjectName='走进java编程世界'
AND r.'studentresult'>60
);
#查询参加走进java编程世界这门课程最近一次考试的学生名单
SELECT * FROM student WHERE studentNo IN(
SELECT r.'studentNo' FROM result r
WHERE r.'subjectId'=(SELECT subjectId FROM subject j WHERE j.'subjectName'=
'走进java编程世界')
AND r.'examdate'=(
SELECT MAX(examdate) FROM result r
INNER JOIN subject j ON r.'subjectId'=j.'subjectId' WHERE j.''subjectName'=
'走进java编程世界'
)
)
#查询没有参加走进java编程世界这门课程最近一次考试的学生名单
SELECT * FROM student WHERE studentNo NOT IN(
SELECT r.'studentNo' FROM result r
WHERE r.'subjectId' = (SELECT subjectId FROM subject j WHERE j.'subjectName'=
'走进java编程世界')
AND r.'examdate'=(
SELECT MAX(examdate) FROM result r
INNER JOIN subject j ON r.'subjectId'=j.'subjectId' WHERE j.'subjectName'=
'走进java编程世界'
)
)
2.子查询关键字
a.in
#查询走进java编程世界这门课程的考试成绩为60的学生名单
#步骤1:查询到走进java编程世界这门课程考试成绩60的学号
#步骤2:再根据学号查询学生名单
SELECT * FROM student WHERE studentNo
IN(
SELECT r.studentNo FROM result r
INNER JOIN subject j ON r.subjectId=j.subjectId
WHERE j.subjectName='走进java编程世界'
AND r.studentresult > 60
);
#查询参加走进java编程世界这门课程最近一次考试的学生名单
SELECT * FROM student WHERE studentNo IN(
SELECT r.studentNo FROM result r
WHERE r.subjectId=(SELECT subjectId FROM subject j WHERE j.subjectName=
'走进java编程世界')
AND r.examdate=(
SELECT MAX(examdate) FROM result r
INNER JOIN subject j ON r.subjectId=j.subjectId WHERE j.subjectName=
'走进java编程世界'
)
)
#查询没有参加走进java编程世界这门课程最近一次考试的学生名单
SELECT * FROM student WHERE studentNo NOT IN(
SELECT r.studentNo FROM result r
WHERE r.subjectId=(SELECT subjectId FROM subject j WHERE j.subjectName=
'走进java编程世界')
)
b.exists
#查询走进java编程世界最近一次考试成绩
#如果有60分以上的成绩,就显示分数排行榜在前5名的学号,分数
#exists存在
SELECT * FROM result r WHERE exists(
SELECT studentresult FROM result r
WHERE subjectId=(SELECT subjectId FROM subject j WHERE j.subjectName='走进java编程世界')
AND examdate=(
SELECT MAX(examdate) FROM result r
INNER JOIN subject j ON r.subjectId=j.subjectId WHERE j.subjectName='走进java编程世界'
)
AND studentresult>60
)
AND subjectId=(SELECT subjectId FROM subject j WHERE j.subjectName='走进java编程世界')
AND examdate=(
SELECT MAX(examdate) FROM result r
INNER JOIN subject j ON r.subjectId=j.subjectId WHERE j.subjectName='走进java编程世界'
)
ORDER BY studentresult DESC
LIMIT 0,5
#查询所有科目的学生信息成绩,如果有就将不及格的+10分;
#查询看有没有不及格的学生成绩
UPDATE result set studentresult = studentresult+10 WHERE studentNo=111
#不能先select出同一表中的某些值,再update这个表(在同一语句中)
#解决办法是,将select得到的结果,再通过中间表select一遍,这样就规避了错误
#这个问题只出现于mysql,mssql和oracle不会出现此问题
UPDATE result SET studentresult=studentresult+10
WHERE exists(
SELECT * FROM (SELECT studentNo FROM result r
WHERE studentresult<60)t1
)
AND studentresult<60
c.any
SELECT * FROM result
WHERE studentresult >ANT(
SELECT studentresult FROM result WHERE studentNo='S1101004')
#查询到此学号S1101004这个学生的任意一门课程成绩高的学号,姓名,成绩
SELECT s.studentNo,s.studentName,r.studentresult FROM student s,(
SELECT * FROM result
WHERE studentresult > ANY(
SELECT studentresult FROM result WHERE studentNo='S1101004')
)r WHERE s.studentNo=r.studentNo
#等同于
SELECT * FROM result
WHERE studentresult >(SELECT MIN(studentresult) FROM result WHERE
studentNo='S1101004')
d.all
#all
#查询到比学号S1101004这个学生的所有课程成绩高的学号,姓名,成绩
SELECT s.studentNo,s.studentName,r.studentresult FROM student s,(
SELECT * FROM result
WHERE studentresult > ALL(
SELECT studentresult FROM result WHERE studentNo='S1101004')
r WHERE s.studentNo = r.studentNo
#等同于
SELECT * FROM result
WHERE studentresult > (SELECT MAX(studentresult) FROM result WHERE
studentNo='S1101004')
第五天:事务
1.事务定义
事务:(Transaction)
事务将多个操作绑定成一个组合,这个组合里面的所有操作要不全部执行,要不都不执行.
举个例子,将1 2 3 4 5个事情绑定在一起组成一个事务,如果1 2 3 4 5件事情都执行成功了就可以
提交事务,如果其中某一件事情执行的过程出现了异常执行不成功,那么该事务中的这5件事情都不能执行成功
需要回滚到该事务之前的状态去
2.事务的四大特征ACID原则
原子性:(Atomicity)事务是最小单位,不可再分
一致性:(Consistency)事务要求所有的DML(增删改)语句操作的时候,必须保证同时成功或者同时失败
隔离性:(Isolation)事务A和事务B之间具有隔离性
持久性:(Durability)当事务提交或者回滚的时候代表事务结束,事物结束之前增删改的语句都是暂时存储在内存里,当事务结束后就会将数据持久化的保存在数据库中
3.事务的操作过程
1.开启事务(start Transaction或者begin)
2.提交事务(commit)
3.回滚事务(rollback)
4.保留点(savepoint)
5.事务结束(不管是事务提交还是事务回滚都标志着事务回滚)
注意:
mysql是默认提交事务的,只要是增删改查语句都会默认自动提交;
我们可以设置是否自动提交
#0不自动提交 1自动提交
set autocommit=0|1 or on|off;
4.事务的上机操作
USE mydb;
DROP TABLE bank;
CREATE TABLE Bank(
id INT(4) PRIMARY KEY,
customerName CHAR(10)COMMENT '用户名',
currentMoney DECIMAL(10,2)COMMENT'当前余额'
);
SELECT * FROM bank;
#truncate也是删除数据,和delete区别在于delete是可以恢复的,trunkcat删除的数据不能恢复
#一般删除数据,建议使用delete
TRUNCATE bank;
INSERT INTO bank VALUES(1.'张三',1000);
INSERT INTO bank VALUES(2,'李四',1);
SELECT * FROM bank;
#mysql是自动提交的,设置不自动提交
SET autocommit=0;
#张三给李四转账
#------------案例1-------------
START TRANSACTION;--开启事务
#begin;--这样也可以
UPDATE bank SET currentMoney=currentMoney-500 WHERE customerName='张三';
UPDATE bank SET currentMoney=currentMoney+500 WHERE customerName='李四';
#rollback;--事务回滚
COMMIT; -- 事务提交
#------------案例2--------------
BEGIN; -- 也可以开启事务
UPDATE bank SET currentMoney=currentMoney-500 WHERE customerName='张三';
SAVEPOINT s1;
UPDATE bank SET currentMoney=currentMoney+500 WHERE customerName='李四';
ROLLBANK TO s1; -- 回滚到你标记的保存点
#------------案例3---------------
DROP TABLE bank;
CREATE TABLE bank(
id INT(4) PRIMARY KEY,
customerName CHAR(10) COMMENT '当前余额'
-- check(currentMoney>0)
);
###################通过mysql编程来控制提交或者回滚##################
DELETE $$
BEGIN
DECLARE t_error INT(4) DEFAULT 0;
DECLARE COUNTINUE HANDLER FOR SQLEXCPTION SET t_error=1;
START TRANSACTION;
UPDATE bank SET currentMoney=currentMoney-1100 WHERE customerName='张三';
UPDATE bank SET currentMoney=currentMoney+500 WHERE customerName='李四';
IF t_error=1 THEN
ROLLBANK;
ELSE
COMMIT;
END IF;
#后期我们是通过java代码层来控制多个业务绑定一个事务;
#当java中try语句块中操作业务的时候发送了异常,就可以将事务回滚
5.事务的隔离性
事务的隔离性是ACID原则之一,指的是事务A和事务B之间具有一定的隔离性,互相不干涉
事务隔离有四个级别:
1.读未提交: read uncommitted
2.读已提交: read committed
3.可重复读: repeatable read
4.串行化: serializable
a.读未提交
事务A和事务B,事务A未提交的数据,事务B可以读取到
--这样读取到的数据叫做"脏数据"
--这种隔离级别最低,这种级别一般在理论上存在,数据库隔离级别一般要高于该级别
b.读已提交
事务A和事务B,事务A提交后的数据,事务B才能读取到
-- 保证数据更安全,这种隔离级别高于读未提交
-- 换句话讲,对方的事务提交之后的数据,我当前事务才可以读取到:可以避免"脏数据"
c.可重复读
这个级别高于已读提交,在读已提交的基础上,可以重复读取数据
-- mysql默认就是可重复读级别
d.串行化
事务A和事务B,事务A在操作数据库时,事务B只能排队等候
-- 事务A操作完数据后事务B才可以去操作数据,优点数据安全但是并不并发,效率比较低,用户体验差,不建议使用
当然我们可以去设置事务的隔离级别:
方式1:
可以在D:\mysql-5.6.37-winx64\my.ini文件中配置
配置选项:
-- READ UNCOMMITTED
-- READ COMMITTED
-- REPEATABLE READ
-- SERIALIZABLE
#配置读已提交
transaction-isolation=READ_COMMITTED
方式2:dos命令登录mysql服务器配置
mysql -u root -psa123
mysql -> set transaction isolation level READ_COMMITTED;
6.mysql数据库引擎分类
mysql数据库有很多中数据库引擎,不同的引擎对mysql有不同的技术支持.mysql默认InnoDB引擎.
引擎分InnoDB,MyISAM,MERGE,CSV等十几种引擎,常见的三种引擎是InnoDB,MyISAM.MEMORY引擎.
mysql想要有事务支持,就应该使用InnoDB引擎
| 特性 | InnoDB | MyISAM | MEMORY |
|---|---|---|---|
| 对事务支持 | 支持 | 不支持 | 不支持 |
| 对外键的支持 | 支持 | 不支持 | 不支持 |
| 存储限制 | 最多64TB | 依情况而定 | 依情况而定 |
| 空间使用 | 高 | 低 | 低 |
| 内存使用 | 高 | 低 | 低 |
| 插入数据的速度 | 低 | 高 | 高 |
查看引擎:
SHOW ENGINS;#显示InnoDB引擎是default
修改引擎:
去my.ini文件中加入:
default-storage-engine=InnoDB
需要重启服务才生效
第六天:视图, 索引, 备份
1.视图
a.视图的定义
视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据,并不是物理表存在的;作为一个select语句查询到的保存在数据字典中虚拟表.
通过视图可以展现基本的部分数据,比如公司老板有权限看到员工表所有的信息,但是呢老板给不同层级的人分配不同的权限看到这张表的数据或者字段不一样,比如财务可以看到员工的编号,姓名,工资:部门领导能看到该部门下所有员工的姓名,业绩,工资;员工自己只能看到自己的员工编号,姓名,部门,电话,地址,考核业绩,工资.因此就可以让这些不要直接操作物理表,而是通过创建不同权限的视图分配给不同用户,那么该用户不能直接对物理表进行操作只有对该权限的视图进行操作.
b.视图的好处
1.安全:给不同用户分配的查询视图权限不同因此对有些不需要看到的数据进行了隐藏
2.简单:使用视图的用户不需要关系后面表的结构,关联条件和筛选条件等,用户只要基于这个视图的结果去做增删改查操作即可
通俗点讲:使用视图的大部分情况是为了保障数据的安全性,提高查询效率.
c.操作视图
######################步骤1####################
#创建视图
DROP VIEW studentresult_view;
CREATE VIEW studentresult_view
AS
SELECT studentNo,gradeId,studentName,address,email FROM student
WHERE gradeId=1
####################步骤2######################
#通过dba用户取消gousheng的myschool所有权限
#给gousheng用户分配视图授权,雨荨对视图进行查询和修改里面的数据
GRANT SELECT,UPDATE ON studentresult_view TO gousheng;
####################步骤3######################
#用gousheng用户取消gousheng的myschool所有权
#给gousheng用户分配视图授权,允许对视图进行查询和修改里面的数据
studentresult_view
#一定要注意:如果是单表查询创建的视图是可以增删改,多表联查只能对视图查询
SELECT * FROM studentresult_view;
#对单表视图的修改,最终映射到物理表
UPDATE studentresult_view SET address='南昌市' WHERE studentName='金蝶';
#作业：创建视图，显示学号、学生姓名、科目名称、成绩、考试日期；
#分配查询权限给gousheng用户
注意:一定是在单表查询出来的结果集做的视图才可以增删改操作(DML操作);
2.索引
1.索引的定义
索引是一种特殊的文件,他们包含着对数据表里所有记录的引用指针.更通俗点讲,数据库索引好比一本书的目录,能加快数据库的查询速度.
索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引不一样.聚簇索引能提高多行数据检索的速度,非聚簇索引对于单行的检索很快.
注意:要注意的是不是索引的查询的列都需要索引来提高查询速度,建议太多的索引会影响更新和插入的速度,因为**
它需要同样更新每个索引文件.对于一个经常需要更新和插入的表格,就没必要为一个很少使用where去查询的字段建立索引了,对于数据量比较小的表,也没必要建立宁外的索引.
2.索引分类
1.普通索引
2.唯一索引
3.全文索引
4.单列索引
5.多列索引
6.空间索引
7.主键索引
8.组合索引
SELECT * FROM student WHERE studentName='金蝶'
#当数据量小的时候,通过条件查询,执行速度还是很快的
#但是随着业务数据量不断的增加,数据达到百万行千万级的时候
#再去执行上行查询语句就能明显感受到速度很慢,这时候我们就可以通过索引来优化
#我们的查询速度:因为我目前是根据姓名来查询,索引来给studentName这个字段添加索引
CREATE INDEX index_studentName ON student(studentName);
#当我studentName创建了一个普通索引,当用studentName去查询的时候,mysql底层会使用索引去查询
#提高了我们的查询速度
SELECT * FROM student WHERE studentName='金蝶'
#如果这样查没有使用到索引,因为它没创建索引
SELECT * FROM student WHERE address='南昌市'
#虽然我们没有创建studentNo的索引,但是因为他是主键,创建主键的时候会默认给你创建主键的索引
SELECT * FROM student WHERE studentNo='S1101001';
#这样是不会使用到索引的,创建了索引的列一定要放在最前面才可以
SELECT * FROM WHERE address='南昌市' AND studentName='金蝶'
3.创建索引:
#最常用
CREATE INDEX indexName ON table_name(column_name);
方式2:在创建完表后使用alter创建索引
ALTER table tableName ADD INDEX indexName(columnName)
4.删除索引
#indexName可选项,加上就是删除该表指定的索引,不加就是删除该表所有的索引
DROP INDEX [indexName] ON mytable;
5.EXPLAIN关键字
我们可以通过EXPLAIN关键字来分析查询语句该用哪种索引
EXPLAIN SELECT * FROM student WHERE studentName='张三'
3.备份
后期大家可以去了解分布式数据库:
方式1:
1.导出:sql yog客户端 工具 -> 备份数据库,转存到SQL -> 选数据和数据结构 ->导出
mystudentdb_yang_1102_day01.sql
2.导出:执行SQL脚本即可
方式2:在客户端的情况下使用DOS命令
cmd命令:
#导表和数据
mysqldump -u root -psa123 myschooldb student >d:\myschooldb_student1102.sql
#导数据库
mysqldump -u root -psa123 myschooldb >d:\myschooldb.sql
#导入数据库脚本(前提有myschool数据库)
mysql -u root -p myschool new < d:\myschooldb.sql;
更多推荐
2021-10-22 mysql数据库
发布评论