MySQL手记

算是对我入门数据库学习的第一门数据库语言的一个交代了

一、数据库基本命令

  • SQL语句以英文 ; 为结束符
  • SQL语句不区分大小写

1.1 基本操作命令

命令
quit / exit / ctrl+d 退出数据库
show databases; 查看所有数据
select database(); 查看当前使用的数据库
create database 数据库名 charset=utf8; 创建数据库
use 数据库名； 使用数据库
drop database 数据库名； 删除数据库-慎重使用

1.2 数据表基本操作命令

创建数据表：

1
2
3
4
5
6
7
8
9
10
create table 表名(
字段名称  数据类型  可选的约束条件
field1 datatype constraint1,
field2 datatype,
field3 datatype,
…………………………
fieldN datatype,
--主键说明可以放在字段中独立说明，也可以放在最后统一说明。
primary key(one or more fields)
);
1
2
3
4
5
6
7
8
create table students(
	id int unsigned primary key auto_increment, --unsigned auto_increment
	name varchar(20) default '',
	age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男', '女', '中性','保密'),
    cls_id int unsigned default 0
);

Ps： --unsigned无符号、auto_increment表示自动增长

1.3 查看修改表结构命令

查看修改表结构命令
show tables; 查看当前数据库中的所有表
desc 表名; 查看表结构
show create table 表名; 查看表的创建语句–详细过程

在开发过程中不需要频繁的操作表结构，能够在需要的时候根据这些语法写出符合要求的SQL语句就可以啦

数据表结构修改命令
alter table 表名 列名 类型; 添加字段
alter table change 原名 新名 类型及约束; 重命名字段
alter table 表名 modify 列名 类型和约束; 修改字段
alter table 表名 drop 列名; 删除字段
drop table 表名; 删除表

1.4 表数据操作命令

  • 添加数据
1
2
3
4
insert into 表名 values(...);						全列插入:值得顺序与表结构得字段一一对应.
insert into 表名(列1,列2...) values(值1,值2,...);   部分列插入:指的顺序与给出得列顺序对应.
insert into 表名 values(...),(...)......;			 一次性插入多条数据.
insert into 表名(列1,列2...) values(值1,值2,...),(值1,值2,...)...;   部分列多行插入数据.
  • 修改查询数据
1
2
3
select * from 表名;							 查询所有列
select 列1,列2,列3... from 表名;				   查询指定列数据
update 表名 set 列1=值1,列2=值2,... where 条件.	 修改数据
  • 删除数据
1
delete from 表名 where 条件;					删除数据

二、where 比较 运算 查询

作用：使用where子句对表中的数据进行筛选

1
2
select * from 表名 where 条件;
--select * from students where id=1;

2.1 比较、逻辑运算符

1
>  =  <  >=  <=   不等于!= 或 <>
1
and  or  not
1
2
3
4
5
6
7
8
--查询编号大于等于3的女学生:
select * from students where id >= 3 and gender=0;
--查询姓名不是“BlindArbiter”的学生:
select * from students where name != 'BlindArbiter';
--查询没被删除的学生:
select * from students where is_delete=0;
--查询年龄不在10岁到15岁之间的学生:
select * from students where not (age >= 10 and age <= 15);

2.2 模糊查询

关键字：like

  • % 表示任意多个字符
  • _ 表示一个任意字符
1
2
3
4
5
6
--查询students表中姓为张的人:
select * from students where name like "张%";
--查询姓张且姓名一共两个字的学生:**
select * from students where name like '张_';
--查询姓张或叫三的学生:
select * from students where name like '张%' or name like '%三';

2.3 范围查询

  • 连续范围：in
  • 非连续范围：between……and…… 
1
2
--查询编号为3至8的学生：
select * from students where id between 3 and 8;

注意：between A and B 在匹配数据的时候匹配的范围空间是 [A,B]

2.4 空判断

  • null 与 ‘ ’ 是不同的
  • is not null 顺序不要错误
1
2
3
4
--判断为空: is null
select * from students where height is null;
--判断为非空: is not null
select * from students where height is not null;

三、order排序查询

以百度搜索为例，用户输入关键字搜索，作为网站往往就需要将用户最需要的网页和数据发送给用户，所以百度就会将用户搜索的关键字和数据库中已经存在的数据进行关联性分析，将关联度高的网页准备好发送给用户浏览，但是如果出现很多关联性几乎一致的网页该怎么办呢？

所以这个时候就需要使用到排序，先将关联程度进行排序，然后将关联程度一样的数据根据比如用户点击量等属性进行排序。

  • 排序查询语法
1
select * from 表名 order by 列1 asc|desc (,列2asc|desc,..)
  • 将行数据按列1排序，若列1值相同，则按列2排，以此类推
  • asc 升序,从小到大（默认）
  • desc 降序,从大到小
1
2
3
4
--查询未删除男生信息，按学号降序:
select * from students where gender=1 and is_delete=0 order by id desc;
--显示所有的学生信息，先按照年龄从大→小排序，当年龄相同时 按照身高从高→矮排序:
select * from students  order by age desc,height desc;

四、聚合函数

命令 作用
count(字段) 计算总行数
max(字段) 求此字段得最大值
min(字段) 求此字段得最小值
sum(字段) 求此字段之和
avg(字段) 求此字段平均值
  • 作用：把当前所在表当做一个组进行统计
  • 特点:
    • 每个组函数接收一个参数（字段名或者表达式）
    • 统计结果中默认忽略字段为NULL的记录
    • 不允许出现嵌套 比如sum(max(xx))

例1：求总行数

1
2
3
4
--返回非NULL数据的总行数.
select count(height) from students; 
--返回总行数，包含null值记录;
select count(*) from students;

例2：求最大值

1
2
--查询女生的编号最大值
select max(id) from students where gender = 2;

例3：求最小值

1
2
--查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

例4：求和

1
2
3
4
--查询男生的总身高
select sum(height) from students where gender = 1;
--平均身高
select sum(height) / count(*) from students where gender = 1;

例5：求平均值

1
2
3
4
--求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
--求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;

五、group分组查询

所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

group by 分组 将查询结果按照一个或多个字段分组，字段值相同为一组

  • 1. group by的使用

1
2
3
4
-- 单字段分组：根据gender字段来分组
select gender from students group by gender;
-- 多字段分组：根据name和gender字段进行分组
select name, gender from students group by name, gender;

  • 2. group by + group_concat()的使用

group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割

1
2
-- 根据gender字段进行分组，查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;

  • 3. group by + 聚合函数的使用

1
2
3
4
-- 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;

  • 4. group by + having的使用

having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by

1
2
-- 根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;

  • 5. group by + with rollup的使用

with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

1
2
3
4
-- 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;

六、limit限制查询

提问：select * from 京东某个数据表，会产生什么效果？

回答：由于数据过多有可能就直接死机了

  • limit限制查询

1
可以使用limit限制取出记录的数量，但limit要写在sql语句的最后

语法：limit 起始记录，记录数

说明：

  • 起始记录是指从第几条记录开始取，第一条记录得下标是0
  • 记录数是指从起始记录开始向后依次取得记录数

limit (n-1)*m, m
n:页数 m: 记录数

例1：查询前3行男生信息:

1
2
3
select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;

查询学生表，获取第n页数据的SQL语句:

1
select * from students limit (n-1)*m,m

七、连接查询

当查询结果的数据来源于多张表时，需要将多张表连接成一个大的数据集进行汇总显示

  • 连接查询

mysql支持三种类型的连接查询，分别为：

  • 内连接查询
  • 外连接查询
  • 自连接查询

7.1 连接查询-内连接

  • 内连接语法

1
select 字段 from 表1   inner join   表2 on 表1.字段1 = 表2.字段2

例1：使用内连接查询学生表与班级表:

1
select * from students as s inner join classes as c on s.cls_id = c.id;

注意:

1
2
1 内连接：根据连接条件取出两个表 “交集”
2 on 是连接条件 where是连接后筛选条件

7.2 连接查询-外连接

  • 语法

1
2
左连接：主表 left join 从表 on 连接条件;
右连接：从表 right join 主表 on 连接条件;

注意 :

  • 能够使用连接的前提是 , 多表之间有字段上的关联
  • 左连接和右连接区别在于主表在SQL语句中的位置, 因此实际左连接就可以满足常见需求

7.2.1 左连接

例1：使用左连接查询学生表与班级表:

1
select * from students as s left join classes as c on s.cls_id = c.id;

7.1.2 右连接

例1：使用右连接查询学生表与班级表:

1
select * from students as s right join classes as c on s.cls_id = c.id;

7.3 连接查询-自连接

就是自己和自己连接的表

  • 创建areas表:
1
2
3
4
5
create table areas(
    id varchar(30) not null primary key, 
    title varchar(30), 
    pid varchar(30)
);
  • 执行sql文件给areas表导入数据:
1
2
--source 表示执行的sql文件
source areas.sql;
  • 自连接查询的用法:
1
2
--自连接查询必须对表起别名 city as c, provice as p
select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '广东省';

八、子查询

就是把一个查询的结果当做另一查询的条件

  • 标量子查询：子查询返回的结果是一个数据(一行一列)
  • 列子查询：返回的结果是一列(一列多行)
  • 行子查询：返回的结果是一行(一行多列)

主查询和子查询的关系：

  1. 子查询是嵌入到主查询中
  2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
  3. 子查询是可以独立存在的语句,是一条完整的 select 语句

例1. 查询大于平均年龄的学生:

1
select * from students where age > (select avg(age) from students);

例2. 查询学生在班的所有班级名字:

1
select name from classes where id in (select cls_id from students where cls_id is not null);

例3. 查找年龄最大,身高最高的学生:

1
select * from students where (age, height) = (select max(age), max(height) from students);

九、外键

  • 对于已经存在的字段添加外键约束
1
2
-- 为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);
  • 在创建数据表时设置外键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建学校表
create table school(
    id int not null primary key auto_increment, 
    name varchar(10)
);

-- 创建老师表
create table teacher(
    id int not null primary key auto_increment, 
    name varchar(10), 
    s_id int not null, 
    foreign key(s_id) references school(id)
);
  • 外键的作用

外键有一个很重要的作用：防止无效信息的插入
注意：外键本身也是一种约束 和 not null 等约束一样

  • 在创建数据表的时候设置外键约束

创建数据表的同时直接创建外键

1
2
3
4
5
6
7
8
9
10
11
create table goods(
	id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    in_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) referebces goods_cates(id),
	foreign key(brand_id) referebces goods_brands(id)
);
  • 删除外键约束
1
2
3
4
5
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称 
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称; 
注意:使用到外键约束会极大的降低表更新的效率, 所以在追求读写效率优先的场景下一般很少使用外键

十、一些课堂练习——SQL Service篇

写在这里是因为一开始我的SQL Service打不开了，所以就干脆先照着题目写着代码先，找天再另外分开这部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
1．查询新媒体学院学生的学号和姓名。
SELECT sno,sname
FROM studinfo
WHERE sdept = '新媒体学院'
 
2．查询选修了课程的学生学号。 
SELECT DISTINCT sno
FROM sc
ps:DISTINCT有去重功能

3．查询新闻学院或计算机学院姓张的学生信息。
SELECT *
FROM studinfo
WHERE sdept IN ('新闻学院','计算机学院') AND sname like '张%'

4．查询其他学院中比“电子信息学院”某一学生年龄小的学生信息。
SELECT *
FROM studinfo
WHERE sbirthday > ANY(SELECT sbirthday
					  FROM studinfo
					  WHERE sdept = '电子信息学院');

5．查询选修人数超过10人的课程号。 
SELECT cno
FROM sc
GROUP BY cno
HAVING COUNT(sno)>10

6．查询每门课的最高分、最低分和平均分。
SELECT MAX(score) AS MAX_Score, MIN(score) AS Min_Score, AVG(score) AS AVG_Score
FROM sc
GROUP BY cno

7．查询选修课平均成绩大于等于85分的学生的学号和其选修课程的最高分。 
SELECT studinfo.sno, MAX(sc.score) AS Max_score
FROM studinfo FULL OUTER JOIN sc
ON studinfo.sno = sc.sno
GROUP BY studinfo.sno
HAVING AVG(sc.score)>=85

8．查询选了两门以上的课程的学生姓名。 
SELECT studinfo.sname
FROM studinfo JOIN sc
ON studinfo.sno = sc.sno
GROUP BY studinfo.sname
HAVING COUNT(sc.cno)>=2

9．查询选修“数据库技术与应用”课程且成绩在80分及以上的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。 
SELECT studinfo.sno, sc.score
FROM studinfo JOIN sc
ON studinfo.sno = sc.sno
WHERE sc.cno == '03' AND sc.score > 80
ORDER BY sc.score DESC, studinfo.sno ASC;

10．查询学生的学号、姓名、选修的课程名及成绩。 
SELECT studinfo.sno,sname,course.cname,sc.score
FROM studinfo, course, sc
WHERE studinfo.sno = sc.sno AND course.cno = sc.cno

11．查询每一门课的间接先修课信息(即先修课的先修课)。 
SELECT *
FROM course
WHERE cpno is NOT NULL

12. 查询“高等数学”课成绩高于张三“高等数学”课成绩的学生学号和成绩。 
SELECT studinfo.sno,sc.score
FROM sc 
JOIN studinfo ON studinfo.sno = sc.sno
JOIN course ON course.cno = sc.cno
WHERE course.cname = '高等数学' 
	AND sc.score > (SELECT score
    FROM sc 
	JOIN studinfo ON studinfo.sno = sc.sno
	JOIN course ON course.cno = sc.cno
    WHERE studinfo.sname = '张三' AND course.cname = '高等数学')

13. 查询每个学生的姓名和选课数量
SELECT studinfo.sname, COUNT(sc.cno) AS courseNUM
FROM studinfo JOIN sc
ON studinfo.sno = sc.sno
GROUP BY studinfo.sname

14. 查询学生姓名,所选课程名称和成绩
SELECT studinfo.sname,course.cname,sc.score
FROM sc
JOIN studinfo ON studinfo.sno = sc.sno
JOIN course ON course.cno = sc.cno

SELECT TOP 2 student.sid,sanme,sc.score
FROM sc
JOIN student ON sc.sid = student.sid
JOIN course ON sc.cid = course.cid
WHERE course.
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明作者BlindArbiter。

博客近期分类标签归档
友链ModifierDrunkbabyGhDemi其他友链
更多GitHub

Copyright © 2022 - 2023 BlindArbiter

Powered by Hexo | Theme - Stellar