MySQL指令
1. 查询数据库
show databases; (如果是多个数据库的话就用复数)
2. 创建数据库
create database 数据库名 charset utf8;
(数据库名由字母、数字、下划线组成，数字不能在最前面)
创建数据库增加charset utf8可以使用数据库插入中文,否则中文报错
3. 删除数据库
drop database 数据库名;
4. 选择数据库
use 数据库名;
创建表table (创建表前先选中具体某个数据库)
(1)查看所有表
show tables;
(2)创建表
create table 表名 (列名 类型, 列名 类型, 列名 类型,......);
注意:类型有int,varchar(),decimal(m,n) m指总共的数字个数,n指小数点后的个数
(3)查看指定表的结构
desc 表名;
(4)删除表
drop table 表名;
数据库命令进行注释
在SQL中可以使用“--空格+描述”来表示注释说明
表的增删改查（CRUD）指令详解
1. 增加 (Create)
1.1 单行全列插入:
insert into 表名 (id, name, age) values(1, ‘小红’，111); 注意点：插入字符串时记得用分号’ ’或者" "隔开 值要和列相匹配 (列的个数和类型)
1.2 多行指定列插入:
insert into 表名 (列名, 列名, ......) value (值, 值,......);
例:
insert into student (id, sn, name) values
(102, 20001, "曹孟德"),
(103, 20002, "孙仲谋");
注意:没有填充的值会被填充默认值null
2. 查询 (Retrieve)
注意理解select的条件执行顺序:
1. 遍历表中的每个记录
2. 把当前记录的值,带入条件,根据条件进行筛选.
3.如果这个记录条件成立,就要保留,进行列上的表达式的计算
4.如果有 order by 会在所有行都被获取到之后(表达式也计算完了) 再针对所有的结果进行排序
2.1 全列查询
select * from 表名; *表示通配符,可以代指所有列
不建议使用*进行全列查询,会意味着需要查询的数据量大,会影响数据库正常运行
2.2 指定列查询
select 列名,列名 from 表名;
2.3 查询字段为表达式
表达式: 加减乘除之类针对列的运算
select 列名, 列名+列名+列名 from 表名;
运行的结果不会影响数据库的数据,只是临时的运算结果
表达式查询,是 列 与 列 之间的运算,把每一行都带入到这样的运算中.
2.4 别名
查询的表达式、列、表名指定别名,返回的结果集中,以别名作为该列的名称(让表达式有更好理解的名称)
select 表达式 as 别名 from 表名; as可以省略,但是不建议
例: select name, chinese + math + english as total from exam_result;
2.5 去重
进行查询数据去重操作,只保留一个,使用关键字distinct
select distinct 列名 from 表名;
如果distinct 后面有多个列名,两个列名内容必须同时一样才能去重
2.6 排序
查询的时候把行进行排序 (排序是针对临时数据在客户端展开的,不影响在数据库的存储顺序)
明确排序规则
(1)针对哪个列作为比较规则
(2)排序的时候是升序还是降序
select 列名 from 表名 order by 列名 asc/desc;
order by 列名: 指定某个列进行排序
asc: 升序
desc: 降序 (如果省略,默认升序)
2.7 指定条件查询: WHERE
会指定具体的条件,按照条件针对数据进行筛选.
select 列名 from 表名 where 条件;
遍历这个表的每一行记录,把每一行的数据分别带入条件中,
如果条件成立,这个记录就会被放入结果集合中,
如果条件不成立,这个记录就pass
注意:如果查询的有null,是查不出来的,直接定为false.
2.7.1 基本查询:
----查询英语不及格的同学和英语成绩( < 60)----
select name, english from exam_result where english < 60;
----查询语文成绩好于英语成绩的同学----
select name,chinese,english from exam_result where chinese > english;
----查询总分在200分以下的同学----
select name, chinese + math +english 总分 from exam_result where chinese + math + english < 200; (表达式使用别名)
2.7.2 AND与OR
----查询语文成绩和英语成绩都大于80分的同学
select * from exam_result where chinese > 80 and english > 80;
----查询语文成绩大于80分啊,或者英语成绩大于80分的同学
select * from exam_result where chinese > 80 or english > 80;
2.7.3 范围查询
1. BETWEEN ...... AND ......
----查询语文成绩在[80,90]分的同学及语文成绩
select name, chinese from exam_result where chinese between 80 and 90;
----使用 AND 也可以实现
select name, chinese from exam_result where chinese >= 80 and chinese <= 90;
2. IN
----查询数学成绩是58或者98或者99分的同学及数学成绩
mysql> select name, math from exam_result where math in (58,59,98,99);
2.8 模糊查询 : LIKE
通配符,就是一些特殊的字符,能够表示特定的含义~
%: 代指任意个任意字符.
-: 代指一个任意字符.
select * from exam_result where name like "孙__";
select * from exam_result where name like "孙%";
孙% : 查询以 孙 开头的内容
%孙: 查询以 孙 结尾的内容
%孙%: 查询包含 孙 的
2.9 NULL的查询:
----查询语文成绩为空的同学
select * from exam_result where chinese is null;
----查询多个列成绩为null的同学
select * from exam_result where chinese <=> english;
2.10 分页查询: LIMIT
使用select * 这种方式查询,是比较危险的,需要保证一次查询,不要查出来的太多
limit 可以限制这次查询最多能查出来多少个结果(因为有的时候,查询出来的数据非常多,全部显示出来会影响效率,也会不方便用)
select * from exam_result limit 3; (只显示前3个)
也可以分页
select * from exam_result limit 3 offset 3; (offset 3 表示偏移量/下标)
带有limit的指令SQL执行速度会稍快,因为遍历表的集合时候,发现满了,就停止遍历了
2.11 查询时插入 :
查询可以搭配插入使用,把查询语句的查询结果,作为插入的数值
此处要求查询出来的结果集合,列数/类型 要和插入的这个表匹配
2.12 聚合查询:
表达式查询,是针对 列 和 列 之间进行运算的
聚合查询,相当于在 行 和 行 之间进行运算
COUNT :
也可以: select count(name) from exam_result; //返回结果的行数
1) 如果当前 列 里面有 null,两种方式计算的 count 结果就不同了
2) 指定具体列,是可以进行去重的
重复的只算一个,null也不算进去
SUM :
把这一列的若干行,给进行求和.(算术运算),只能针对数字类型进行使用
AVG :
求平均数
MAX :
求最大值
MIN :
求最小值
2.13 GROUP BY子句
group by 指定列名
就是针对查询结果,按照指定列名,进行分组. 把值相同的行,归为一组
针对每个分组都进行聚合查询的操作
查询每个分组的平均工资
拿到上述的平均工资按照升序排列
给聚合查询指定条件
1. 聚合之前的条件
2. 聚合之后的条件
3. 修改 (Update)
语法:
update 表名 set 列名 = 值 where 条件;
还可以使用 update, 可以一次修改多个列~
update 表名 set 列名 = 值, set 列名 = 值 where 条件;
----将总成绩倒数前3位的3位同学的数学成绩减上30分
select * from exam_result set math = math -30 order by chinese + math+english limit 3;
----将所有同学的语文成绩更新为原来的 1 / 2 倍
update exam_result set chinese = chinese / 2;
4. 删除 (Delete)
语法:
delete from 表名 where 条件 / order by / limit;
----删除整张表
不指定任何条件,就是删除整个表
delete * from exam_result;
drop table 是删除了表,也删除了表里的记录.
delete 只是删除表里的记录,表还在(空表)
数据库约束
有的时候,数据库中的数据,是有一定要求的,有数据认为是合法的数据,有些是非法数据.靠人工检查时非常不靠谱的(相对于计算机来说)
数据库.自动对数据的合法性进行校验检查的一系列机制~~目的就是为了保证数据库中能给个避免被插入/修改一些非法数据. 所以提供了以下的约束类型:
- NOT NULL: 指示某列不能存储null值.是必填项.
- UNIQUE: 保证某列的每行必须有唯一的值.
- DEFAULT: 规定没有给列赋值时的默认值
- PRIMARY KEY: NOT NULL 和 UNIQUE 的结合. 确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快找到表中的一个特定的记录.
- FOREIGN KEY: 保证一个表中的数据匹配另一个表中的值的参照完整性.
- CHECK: 保证列中的值符合指定的条件. 对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句.
1. NULL约束
2. UNIQUE: 唯一的约束
指定列为唯一的,不重复的
unique 约束,会让后续插入数据/修改数据的时候,都会先触发一次 查询 操作.
通过这个查询,来确定当前这个记录是否已经存在~~
数据库引入约束之后,执行的效率就会受到影响,就可能会降低很多
3. DEFAULT: 默认值约束
4. PRIMARY KEY: 主键约束
一张表里只有一个 primary key. 这个是最重要的约束,一行记录的身份标识
唯一且不为空
对于带有主键的表来说, 每次插入数据/修改数据,也会涉及到进行先查询的操作
对于整数类型的主键,常搭配自增长 auto_increment 来使用. 插入数据对应字段不给值时,使用最大值 + 1 ,如下图所示
5. FOREIGN KEY: 外键约束
foreign key (字段名) references 主表(列)
通过父表来约束子表,执行这个命令时,会触发查询操作
指定外键约束的时候,要求父表中被关联的这一列必须是主键或者 unique !
添加时没有class中对应的classId,就会报错,如下图所示:
删除父表操作时,确保父表没有被引用(可以删除在引用的子表)
表的设计
梳理清楚 需求场景中的实体,实体之间的关系
1. 一对一
一个学生只能由一个账号. 一个账号只能属于一个学生.
2. 一对多
一个学生只能属于一个班级, 一个班级可以有多个学生.
3. 多对多
一个学生可以选择多个课程,一个课程可以被多个同学选择.
联合查询 / 多表查询
笛卡尔积,就是得到了一个更大的表. 列数,就是原来的两个表的列数之和. 行数,就是原来两个表的行数之积
两个表:
select * from student, class;
笛卡尔积是全排列的过程,在尝试穷举出所有的可能性
生成的顺序不关键,并且可以通过order by 来排序
上述结果存在一部分无效/无意义的数据
为排除无效的数据,通过观察可以知道classId相同的就是有效的数据
select * from student, class where student.classId = class.classId;
student.classId = class.classId 这种专门用来筛选出有效数据的条件也称为"连接条件", 上述多表查询的操作,也可以称为"连接操作"
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
问题:查询"许仙"同学的成绩
许仙-->学生表 成绩-->分数表
所以需要针对学生表和分数表进行联合查询.
步骤:
1. 先确定要查询的信息,来自于哪些表
2. 针对这两个表进行笛卡尔积
指定连接条件,把无效数据去掉,保留有效数据.
所谓连接条件,应该是有某个列在两个表中同时存在
select * from student, score where student.id = score.student_id;
再根据其他要求,补充其他条件
select * from student, score where student.id = score.student_id and student.name = "许仙";
由于上述列出了所有的列,实际题目要求,只是列出 许仙 和 成绩 ,把其他不必要的列去掉
select student.name, score.score from student, score where student.id = score.student_id and student.name = "许仙";
JOIN ON
多表查询,还有另外一种写法: join on
1. 确定信息来自学生表和分数表
2. 把学生表和分数表进行 笛卡尔积
3. 指定连接条件
4.补充其他条件
5. 保留必要的列,去掉多余的列
上述两种写法的作用是一样的
查询所有同学的总成绩及同学的个人信息
步骤:
1. 信息出自于 学生表 分数表
2. 学生表和分数表 进行笛卡尔积
3. 指定连接条件(去掉无效结果)
4. 此处不需要其他条件,需要聚合操作~~按照学生名字进行分组
5. 针对 * 进行调整 ,调整成符合客观情况的列
查询所有同学的成绩,及同学的个人信息
步骤:
1. 信息来自三个表 学生表 课程表 分数表
2. 笛卡尔积
3. 指定连接条件
三个表,需要两个连接条件
三个表笛卡尔积,就相当于表1 和表2 先笛卡尔积(需要一个连接条件),得到的结果再和表3 笛卡尔积(又需要一个连接条件)
4.补充其他条件--->这里不需要补充了
5. 对列进行精简
也可以使用 join on 来写
select student.name,course.name, score.score from student join score on student.id = score.student_id join course on course.id = score.course_id;
内连接
外连接
只能使用join on的方式写. 可以给join 前头加上left / right 关键字,就得到"左外连接"和"右外连接"
左外连接
右外连接
如果针对上述数据进行调整,不再使数据能够一一对应,此时结果就不同了!!
王五的分数信息,在分数表中不存在,分数表中id为4的信息,在学生表中也不存在.
针对上述信息,进行内连接,得到的结果,就是只包含两个表中都存在的数据
对于左外连接,以左侧的表为基准(写作student join score ,此时 student 就是左侧表,score 就是右侧表)
会保证左侧表中的每个数据都一定会存在
左侧表数据在右侧表中不存在的部分(列), 会使用null来填充.
右外连接和左外连接,以右表为基准,使右表中每个数据都存在,对应在左表中不存在的部分,都以null进行填充
站在集合的角度看待上述的几种连接
内连接
左外连接
右外连接
全外连接
自连接
同一个表,自己和自己计算笛卡尔积.
自连接基本上很少会见到. 处理特定问题的特殊技巧.(奇淫巧技)
显示所有"计算机原理"成绩比"Java"成绩高的成绩信息
上述观察数据结果的过程中存在核心问题!!!
score表,表示不同的科目的分数的时候,是通过不同的行来表示的.
而咱们前面进行的各种条件查询,都是基于 列 和 列 之间进行的比较,无法直接对行和行进行比较!!!
这里就需要想办法,把行与行之间的关系,转成列之间的关系!!(自连接,就能完成这样的操作的!!)
继续添加条件,把其他暂时不关心的条件去掉
找计算机原理比Java大的成绩:
自连接能够把行之间的关系转换成列之间的关系
也要注意到,自连接的时候,为了转换成上述的列关系,产生的大部分中间结果都是没有必要的
子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
- 单行子查询: 返回一行记录的子查询
上面两个语句嵌套结合起来就是:
- 多行子查询: 返回多行记录的子查询 (通过in)
查询"语文"或"英语" 课程的成绩信息
1. 先求语文和英文的id
2. 通过id查出语文和英文的id
结合起来就是
合并查询
把多个查询的结果集合,合并到一起
union 允许你从不同的多个表分别查询,只要每个表的查询结果集合列的类型和个数匹配,都能合并.
or 只能针对一个表
索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。
特点:
- 加快查询的速度
- 索引自身是数据结构,也要占据存储空间
- 当我们需要进行新增,修改的时候,也需要针对索引进行更新。（额外的开销）
适用场景：
- 对于存储空间，要求不高（存储空间比较充裕）
- 应用场景中，查询较多，增加，修改，删除的操作不多的时候
1. 索引的使用
1.1 查看索引
show index from 表名;
mysql 中的primary key 和unique 和foreign key 都会自动生成索引。
1.2 创建索引
例如,开发中发现,经常会根据学生名字进行查询,就可以针对名字这一列创建索引:
创建索引,都是根据具体的列来创建的.针对某一列创建索引之后,后续查询的时候,也必须得针对这一条件查询,才能通过索引来提速
create index 索引名字 on 表名(列名)
1.3 删除索引
drop index 索引名 on 表名；
936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
