1 知识地图
2 概念
3 安装与卸载
- 3.1 CentOS安装5.1版本
安装MySQL
启动MySQL
登录MySQL
修改密码
刷新权限
关闭防护墙
新建用户远程连接mysql数据库
允许任何ip地址(%表示允许任何ip地址)的电脑用admin帐户和密码(123456)来访问这个mysql server。
注意admin账户不一定要存在。
刷新权限
4 Navicat的使用
5 SQL
6 高级操作
7 练习

1 知识地图

2 概念

2.1 数据库的定义

数据库： 按照数据结构来组织、存储和管理数据的仓库

疑问： 我的数据明明可以存在Excel或其他的文件中，那还要数据库干嘛？

这就引出 数据存储 的几个发展阶段：

人工管理阶段
数据依靠人工整理，存储和管理。不方便保存，查询和管理
典型的案例如古代的 结绳记事 ，以及后来的 造纸术，印刷术，图书馆 等

文件系统阶段
将文件数据储存在硬盘上，通过文件管理系统，比人工方便很多。
但在查询数据内容时，依旧不太方便。
如果想查看文件里面的内容，需要一个个打开确认。

数据库管理阶段
网页显示的商品信息
电商网站.jpg
数据库中储存的商品信息
数据库商品信息.jpg
这个阶段，由于数据都储存在数据库中，能够快速查询，读取，管理。
所以受到广大用户（开发者）欢迎。
为什么要用数据库保存数据？

数据保存在数据库中会比保存在excel等文件中的查询速度更快
由于不用打开数据文件就可以进行数据读写，所以方便数据进行管理
可以同时被多人查看，方便共享

2.2 关系型数据库
RDBMS 全称 Relational Database Management System
中文意思为：关系型数据库管理系统
关系型数据库采用了关系模型（一对一，多对多，一对多… …）来组织数据

什么是关系型数据库？

比如上图中，有两个表格，一个角色表，一个技能表。这两个表格中有一列数据（姓名）是相同，我们使用这一列相同的数据来连接两个表格，使他们产生联系，一旦其中一个表格的数据更改了，另一个表格中的对应数据也会跟着更改，这种联系使他们的数据具有 一致性 ，这就是 关系型数据库 。

2.3 排名与分类

分类
「关系型」

Oracle：甲骨文开发，收费软件，一般用于大型项目。如：银行，电信等
MySQL：甲骨文公司收购Sun公司而来，开源免费数据库软件。在web项目中广泛使用
Microsoft SQL Server：微软开发的项目中使用

「非关系型」

Redis：高性能缓存数据库
SQLite：轻量级数据库，主要用于移动端

「关系型数据库的核心元素」

数据仓库：常常简称数据库，一个数据库系统可以有多个数据仓库
数据表：一个数据仓库可以有多张数据表
数据列：字段信息
数据行：一条数据记录

图示：红色代表数据库管理系统，绿色代表数据仓库，蓝色代表数据表，黄色代表字段，粉色代表数据行

排名
访问：https://db-engines.com/en/ranking，可以实时查看数据库排行榜

注意：上述排名不与数据库使用安装数量相等

2.4 SQL与MySQL

SQL

全称：Structured Query Language
结构化查询语言，用来操作关系型数据库的语言
SQl 语言不区分大小写，但是标准一般用大写

MySQL
MySQL 是由瑞典 MySQL AB 开发，后来被 Sun 公司收购。Sun 公司又被 Oracle 收购。
所以，MySQL 现在是 Oracle 旗下的产品。
目前，MySQL新版本中出现了收费版，免费版（社区版）依旧存在。收费异味着后续可能会走上闭源之路，但是，就算以后闭源也没关系，到时肯定会出现衍生版本，毕竟曾经开源过，对于付费无需担心。
MySQL的特点：

开源，免费
支持多平台
是学习数据库的首选
… …
3 安装与卸载
3.1 CentOS安装5.1版本
```powershell
安装MySQL
yum -y install mysql-server

启动MySQL

service mysqld start

登录MySQL

mysql -uroot -p (无密码)

修改密码

update user set password=password(‘新密码’) where user=’root’;

刷新权限

flush privileges;

关闭防护墙

service iptables stop

新建用户远程连接mysql数据库

允许任何ip地址(%表示允许任何ip地址)的电脑用admin帐户和密码(123456)来访问这个mysql server。

注意admin账户不一定要存在。

grant all on . to admin@’%’ identified by ‘123456’ with grant option;

刷新权限

flush privileges;


<a name="OpPL5"></a>
## 3.2 CentOS安装5.7版本
检查是否安装<br />检查
```shell
rpm -qa | grep mysql

强力卸载
如果已安装过，需要将原有MySQL卸载

rpm -e --nodeps 需要卸载的包名

检查文件夹
检查系统中是否存在MySQL对应的文件夹

如果有的话，需要将其删除

rm -rf /usr/lib64/mysql /usr/share/mysql

下载安装包

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

解压压缩包

移动目录&改名
移动
将解压出来的目录移动到 /usr/local 目录中

改名

mv mysql-5.7.24-linux-glibc2.12-x86_64/ mysql

创建数据目录
在mysql的目录中，创建一个data目录

mkdir /usr/local/mysql/data

修改权限

chmod -R 755 /usr/local/mysql

添加用户与组

group add mysql    # 添加用户组
useradd -r -g mysql mysql    # 添加用户
chown -R mysql:mysql /usr/local/mysql

编译安装

./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

如果报下图中的错误：

先检查是否安装了如下链接库文件

rpm -qa|grep libaio

没有安装的话需要安装，已安装仍报错，则安装下面的插件

yum -y install numactl

初始化
初始化成功后，记住最后的临时密码

编辑配置文件

vi /etc/my.cnf

在配置文件中加入如下内容：

[mysqld]
datadir=/usr/local/mysql/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=600
innodb_file_per_table=1
lower_case_table_names=1

测试启动

/usr/local/mysql/support-files/mysql.server start

添加软连接&启动

 ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql 
 ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
 service mysql restart

修改密码，开放远程连接
使用第一次初始化时的密码登录MySQL

修改密码

开放远程连接权限

4 Navicat的使用

4.1 连接MySQL

打开「navicat」
点击连接，选择「MySQL」
新建连接
- 连接名：任意
- 主机名或IP地址: 设置需要连接的电脑的ip地址
- 端口: MySQL默认3306，无需修改
- 用户名: 登录数据库的用户名
- 密码: 登录用户的密码
点击「连接测试」，连接成功表示可以正常登录
点击确定，完成

4.2 新建数据库

双击连接名，当前面的icon变为绿色，即表示连接成功
右键连接名，选择 新建数据库
数据库名称任填，字符集选择 utf8—UTF-8 Unicode ，排序规则选择 utf8_general_ci

4.3 数据表操作

新建表

连接某个数据库，右键表，选择 新建表
创建表时，至少要添加一个字段，填写字段名并选择好类型，然后点击保存按钮

修改表

删除表

4.4 数据增删改查

插入数据
打开表以后，在底部点击「 + 」，插入数据

删除数据

清空数据

查询数据

4.5 数据的类型与约束

为了更加准确的保存数据，保证数据正确有效，需要合理的使用数据类型和约束来限制数据的存储
常用的数据类型

整数：int，有符号范围（-2147483648~2147483647），无符号范围（0~4294967295）

整数无符号使用 unsigned 关键字来指定
小数：decimal，如decimal（5,2）表示共存5位小数，小数占2位，整数占3位
字符串：varchar，范围（0~65533），如varchar（3）表示最多存3个字符，中文与字母都是占一个
日期时间：datetime，范围（1000-01-01 00:00:00~9999-12-31 23:59:59），如：2020-01-01 12:12:12

约束

主键（primary key）：物理上存储的顺序，用来唯一标识每一行数据
非空（not null）：此字段不允许填写空值（不能不填）
唯一（unique）：此字段的值不允许重复
默认值（default）：如此字段不填时使用默认值，如填写时以填写为准
外键（foreign key）：维护两个表之间的关联关系（一般是另一张表的主键）
5 SQL
5.1 用户操作
5.1.1 创建用户
```sql — 格式 create user ‘用户名’@’ip地址’ identified by ‘密码’;

— 例 create user ‘wangwu’@’localhost’ identified by ‘123456’;

<a name="2SP7b"></a>
### 5.1.2 查看用户
```sql
select * from user;

5.1.3 查看用户权限

show grants for 'lisi'@'localhost';

-- 或者
select * from user;

5.1.4 用户赋权

格式

grant 权限1,权限2 on *.* to '用户名'@'ip';

-- 权限有：create、insert、delete、update、select、drop、all（所有权限）
-- 第一个*表示数据库名，第二个*表示表名（*是通配符，匹配所有数据库/表）

-- 案例
grant create,insert,delete,update,select,drop on *.* to 'lisi'@'localhost';

5.1.5 收回权限

revoke 权限1,权限2 on *.* from '用户名'@'ip地址';

-- 例
revoke create,insert,delete,update,select,drop on *.* from 'lisi'@'localhost';

5.1.6 修改用户名

rename user '旧用户名'@'ip地址' to '新用户名'@'ip地址';

-- 将lisi改名为lisi2
rename user 'lisi'@'localhost' to 'lisi2'@'localhost';

5.1.7 修改密码

set password for '用户名'@'ip地址'=password('新密码');

-- 例
set password for 'lisi2'@'localhost'=password('1234567');

5.1.8 删除用户

drop user '用户名'@'ip地址';

5.2 数据表操作

5.2.1 创建表

格式：

create table 表名(
  字段名 类型 约束
  字段名 类型 约束
  ...
 )

例1：创建学生表，姓名（长度为10），年龄（不能为负）

create table students(
  name varchar(10),
  age int unsigned       -- unsigned表示无符号，即不能为负
 )

例2：创建学生表，学生编号（主键），姓名（长度为10），年龄（不能为负），身高（保留2位小数）

create table students(
  id int unsigned primary key auto_increment, --设置id为主键，并自动编号，自动编号须与主键一起使用
  name varchar(10),
  age int unsigned,
  height decimal(5,2)   --总共5位，小数占2位，整数占3位
 )

5.2.2 删除表

直接删除（如果表不存在会报错）

drop table 表名;

先判断再删除（表不存在不会报错）

drop table if exists 表名;

5.3 数据增删改

5.3.1 插入数据

全量插入
格式一：插入全部，所有值与字段顺序对应

insert into 表 values(...);

例：在学生表中插入一个学生信息
表结构为

insert into students values(1, "zhangsan", 18, 179.00)

部分插入
格式二：插入部分数据

insert into 表(字段1，字段2...) values(值1，值2...)

例：插入一个学生信息，只插入姓名

insert into students(name) values("zhangsan")

批量插入
格式一：写多条插入语句，语句间用分号隔开

insert into students(name) values('安妮');
insert into students(name) values('金克斯');
insert into students values(0, '琴女', 19, 170);

格式二：写一条插入语句，设置多个数据，数据用逗号隔开

-- 格式一  
insert into students values(0,'kasha', 100, 190),(0, 'lala', 99, 176.4);

-- 格式二
insert into students(name) values('光女一号'),('光女二号'),('光女三号');

5.3.2 修改数据

格式

update 表 set 列1=值1,列2=值2... where 条件

例：修改id为5的学生数据，姓名改为狄仁杰，年龄改为20

update students set name="狄仁杰", age=20 where id=5;

5.3.3 删除数据

普通删除

delete from 表 where 条件

例：删除id为6的学生信息

delete from students where id=6;

注意：对于重要的数据，不能轻易执行删除操作，一旦删除，数据无法恢复。推荐进行 逻辑删除
逻辑删除
在表中添加 isdelete **字段，字段默认值为0。0表示未删除，1表示已删除

alter table students add column `isdelete` tinyint not null default '0' comment'用户是否删除
0未删除，1已删除'

tinyint 类型默认范围（-128-127），如果指定unsigned，则范围（0-255）
tinyint（2），括号中的2是指储存宽度，位数不够左边自动补0

决定删除某条数据时，将 isdelete 字段设置为1

update students set isdelete=1 where id=1;

以后在查询数据时，只查询 isdelete=0 （未删除）的数据

select * from students where isdelete=0;

5.4 基础查询

准备数据

 drop table if exists students;
 create table students (
        studentNo varchar(10) primary key,
        name varchar(10),
        sex varchar(1),
        hometown varchar(20),
        age tinyint(3), -- tinyint,小整数,有符号-128-127，无符号0-255,3表示显示宽度
        class varchar(10),
        card varchar(11)
 );

 insert into students values
 ('001', '王昭君', '女', '北京', 20, '1班', '11010119900'),
 ('002', '诸葛亮', '男', '上海', 18, '2班', '11010119900'),
 ('003', '张飞', '男', '南京', 24, '3班', '11010119900'),
 ('004', '白起', '男', '安徽', 22, '4班', '11010119900'),
 ('005', '大乔', '女', '天津', 19, '3班', '11010119900'),
 ('006', '孙尚香', '女', '河北', 18, '1班', '11010119900'),
 ('007', '百里玄策', '男', '山西', 20, '2班', '11010119900'),
 ('008', '小乔', '女', '河南', 15, '3班', NULL),
 ('009', '百里守约', '男', '湖南', 21, '1班', ''),
 ('010', '妲己', '女', '广东', 26, '2班', '11010119900'),
 ('011', '李白', '男', '北京', 30, '4班', '11010119900'),
 ('012', '孙膑', '男', '新疆', 26, '3班', '11010119900')

查询所有

select * from students;

查询指定字段

-- 单表时，直接字段名即可
select studentno, name from students;

-- 表名.字段名
select students.name from students;

-- 给表取表名
select s.name, s.hometown from students as s;

-- 给字段取别名
select name as 姓名, hometown as 籍贯 from students;

5.5 去重查询

在select后面查询字段前面使用 distinct 可以查询出消除重复行的结果

select distinct hometown as 籍贯 from students;

注意：如果使用distinct去重查询多列数据时，只有多列组合重复时才会去除

select distinct name,age from students;

5.6 条件查询

语法

使用where语句对表中的数据进行筛选，符合条件的数据会出现在结果集中

语法如下：

select 字段1，字段2... from 表名 where 条件;

例如：查询studentno为10的学生姓名

select name from students where studentno=010;

比较运算符

运算符	解释
>	大于
<	小于
>=	大于或等于
<=	小于或等于
=	等于
！=	不等于，两边是否不相等

例1：查询 陈萍萍 的年龄

select age from students where name='陈萍萍';

例2：查询 20岁 以下的学生

select * from students where age<20;

例3：查询籍贯不在北京的学生信息

select * from students where hometown!='北京';

练习

查询学号是007的学生的信息
查询除了1班以外的学生信息
查询年龄大于20的学生姓名以及性别 ```sql — 1 select * from students where studentno=007;

— 2 select * from students where class!=’1班’;

— 3 select name,sex from students where age>20;


逻辑运算符

| 运算符 | 解释 |
| --- | --- |
| and | 和，并且 |
| or | 或者 |
| not | 非（否定） |

**例1：**查询年龄小于20的女同学
```sql
select * from students where age<20 and sex='女';

例2：查询女学生或者1班的学生

select * from students where sex='女' or class='1班'

例3：查询非天津的学生

select * from students where not hometown='天津'

练习

查询河南或江苏的学生
查询1班的北京学生
查询非20岁的学生

答案

-- 1
select * from students where hometown='河南' or hometown='江苏'
-- 2
select * from students where class='1班' and hometown='北京'
-- 3
select * from students where not age=20

模糊查询

关键字：「 like 」
「 % 」表示一个或多个任意字符
「_ 」表示任意一个字符

例1：查询姓范的学生

select * from students where name like '范%'

例2：查询姓范并且名字只有一个字的学生

select * from students where name like '范_'

例3：查询名字以凡结尾的学生

select * from students where name like '%凡'

例4：查询名字中包含范的学生

select * from students where name like '%范%'

范围查询

「in」表示在一个非连续的范围内

例：查询家乡是北京或湖南或上海的学生

select * from students where hometown in ('北京','湖南','上海')

「between… and …」表示在一个连续的范围内

例：查询年龄为18到20岁的学生信息

select * from students where age between 18 and 20

注意：使用「between… and …」时，数字必须前小后大，否则查不到

练习

查询年龄在18或19或20的女学生
查询年龄在20到25以外的学生

答案

-- 1
select * from students where age in (18,19,20) and sex='女'
-- 2
select * from students where not age between 20 and 25

空判断

null与’’(空字符串)是不一样的
判断是否为空：「is null」

例1：查询没有填写身份证的学生

select * from students where card is null

例2：查询填写了身份证

select * from students where card is not null

5.7 查询排序

关键字：order by
升序：asc
降序：desc

注意：如果没有写asc/desc，则默认升序排序
例1：查询所有学生的信息，并按照年龄从小到大排序

select * from students order by age asc;  --asc可以省略不写

例2：查询所有学生的信息，按照年龄从小到大排序，如果年龄相同时再按照学号从大到小排序

select * from students order by age asc, studentno desc;

5.8 聚合函数

使用聚合函数可以快速得到「统计数据」，常用的聚合函数有如下五个
count
计算「总行数」
例：查询学生总数

select count(*) from students;

max
求此列「最大值」
例：查询女生中最大的年龄

select max(age) from students where sex='女';

min
求此列「最小值」
例：求3班的最小年龄

select min(age) from students where class='3班';

sum
求此列的「和」
例：查询北京学生的年龄总和

select sum(age) from students where hometown='北京';

avg
求此列的「平均值」
例：查询女生的平均年龄

select avg(age) from students where sex='女';

练习

查询所有学的最大年龄，最小年龄，平均年龄
3班一共有多少个学生
查询3班年龄小于20岁的学生有几个

答案

-- 1
select max(age) from students;
select min(age) from students;
select avg(age) from students;

-- 2
select count(*) from students where class='3班';

-- 3
select count(*) from students where class='3班' and age<20;

5.9 分组函数

按照字段分组，字段相同的数据会被放到一个组中
分组后，分组的依据列会显示在结果集中，其他列不显示
可以最分组后的数据进行统计，做 聚合运算
关键字：group by
数据筛选关键字：having

例1：查询各种性别的人数

select sex,count(*) from students group by sex;

例2：查询各种年龄的人数

select age,count(*) from students group by age;

例3：查询男生总人数

方案一：
select sex,count(*) from students group by sex having sex='男';
方案二：
select count(*) from students where sex='男';

注意：

where是对表的数据进行筛选，属于对原始数据进行筛选
having是对group by的结果进行筛选
两者是不同的

5.10 连接查询

「准备数据」
创建学生表，并插入数据

 drop table if exists students;
 create table students (
        studentNo varchar(10) primary key,
        name varchar(10),
        sex varchar(1),
        hometown varchar(20),
        age tinyint(3), -- tinyint,小整数,有符号-128-127，无符号0-255,3表示显示宽度
        class varchar(10),
        card varchar(11)
 );

 insert into students values
 ('001', '王昭君', '女', '北京', 20, '1班', '11010119900'),
 ('002', '诸葛亮', '男', '上海', 18, '2班', '11010119900'),
 ('003', '张飞', '男', '南京', 24, '3班', '11010119900'),
 ('004', '白起', '男', '安徽', 22, '4班', '11010119900'),
 ('005', '大乔', '女', '天津', 19, '3班', '11010119900'),
 ('006', '孙尚香', '女', '河北', 18, '1班', '11010119900'),
 ('007', '百里玄策', '男', '山西', 20, '2班', '11010119900'),
 ('008', '小乔', '女', '河南', 15, '3班', NULL),
 ('009', '百里守约', '男', '湖南', 21, '1班', ''),
 ('010', '妲己', '女', '广东', 26, '2班', '11010119900'),
 ('011', '李白', '男', '北京', 30, '4班', '11010119900'),
 ('012', '孙膑', '男', '新疆', 26, '3班', '11010119900')

创建成绩表并插入数据

drop table if exists scores;
create table scores (
    id int unsigned primary key auto_increment,
    coursesNo int(10) unsigned,
    studentNo varchar(10),
    socre tinyint(4)
);
insert into scores values
(1, 1, '001', 90),
(2, 1, '002', 75),
(3, 2, '002', 98),
(4, 3, '001', 86),
(5, 3, '003', 80),
(6, 4, '004', 79),
(7, 5, '005', 96),
(8, 6, '006', 80);

创建课程表并插入数据

drop table if exists courses;
create table courses (
    coursesNo int unsigned primary key auto_increment,
    name varchar(10)
);
insert into courses values
(1, '数据库'),
(2, '自动化测试'),
(3, 'Linux'),
(4, '测试理论'),
(5, 'web网络'),
(6, 'App测试')

数据表全部准备完毕，如下图
Snipaste_2020-03-04_16-30-32 (1).png

5.11 等值连接

语法：select * from 表1，表2 where 表1.列=表2.列
例：查询学生信息以及学生成绩

select * from students as stu,scores as sco 
where stu.studentNo=sco.studentNo

查询的信息太多了，精简一下，只要学生姓名和成绩

select stu.name,sco.socre from students as stu,scores as sco 
where stu.studentNo=sco.studentNo

注意：使用等值连接查询多表时，一定要先找出两张表中共同的字段，等值连接会把多个表生成一个 临时表，再使用 where进行过滤

5.12 内连接

把多张表用相同的列关联起来，过滤掉不同的数据，只保留相同的数据
图示

语法：select * from 表1 inner join 表2 on 表1.列=表2.列
例1：查询学生姓名以及学生成绩

select stu.name, sco.score from students as stu
inner join scores as sco
on stu.studentNo=sco.studentNo

5.13 左连接

以左表为主表，左表的数据全部显示，左表有右表没有的显示为null
Snipaste_2020-02-19_00-03-49 (1).png
语法：select * from 表1 left join 表2 on 表1.列=表2.列
例1：查询所有学生的成绩，包括没有成绩的学生

select stu.studentNo,stu.name,sco.score 
from students as stu
left join scores as sco
on stu.studentNo=sco.studentNo

注意：join前面的表为左表，后面的表为右表
例2： 查询所有学的课程以及成绩，包括没有成绩的学生

select stu.studentNo,stu.name,sco.score,cou.name as courseName from students as stu
left join scores as sco
on stu.studentNo=sco.studentNo
left join courses as cou
on sco.coursesNo=cou.coursesNo

5.14 右连接

以右表为主表，右表数据全部显示，右表有而左表没有的显示为null

语法：select * from 表1 right join 表2 on 表1.列=表2.列
准备新数据：在课程表中插入两门新课程

insert into courses values(7,'就业课'), (8, '安全性测试')

例1：查询所有课程的成绩，包括没有成绩的课程

select * from scores as sco
right join courses as cou
on sco.coursesNo=cou.coursesNo

例2：查询所有课程的成绩，包括没有成绩的课程，包括所有学生的信息

select * from scores as sco
right join courses as cou
on sco.coursesNo=cou.coursesNo
left join students as stu
on sco.studentNo=stu.studentNo

5.11 子查询

「定义」：在一个select语句中，嵌入另外一个select语句，这就是「子查询」
「主查询」：主要查询的对象，一般是第一个select
「主查询与子查询的关系」：
- 子查询是嵌入到主查询中的
- 子查询辅助查询，要么充当条件，要么充当数据源
- 子查询是可以独立存在的语句，是一条完整的select语句
「子查询分类」
- 标量子查询：子查询返回的结果是一个数据
- 列子查询：子查询返回的结果是一列
- 行子查询：子查询返回的结果是一行
- 表级子查询：子查询返回的结果是多行多列

标量子查询
例1：查询大于平均年龄的学生

-- 常规思路肯定是先查出平局年龄，然后再加条件过滤
select avg(age) from students; -- 查出平局年龄
select * from students where age>21.5833; -- 过滤
-- 上面的步骤因为使用了两条sql，如果数据表在执行完第一条sql后发生了数据变化
-- 那么到时查询的结果会错误

-- 而采用子查询写法则不会出现那样的错误，因为两条sql是组装在一起执行的
select * from students where age>(select avg(age) from students);

例2：查询王昭君的成绩

-- 先把王昭君的学号查出来
select studentNo from students where name='王昭君';
-- 再拿查到的学号去成绩表中查询成绩
select score from scores where studentNo=001;

-- 组合成子查询
select score from scores 
where studentNo=(select studentNo from students where name='王昭君');

列子查询
例：查询18岁学生的成绩，要求显示成绩

-- 先从学生表中查出18岁学生的学号
select studentNo from students where age=18   -- 注意这里会返回一列数据
-- 再拿着拿出的学号去成绩表中查询成绩，注意有多个学号要查
select score from scores where studentNo in (002, 006)
-- 组合成列子查询
select score from scores 
where studentNo in (select studentNo from students where age=18)

行子查询
例：查询年龄最大的男生信息

-- 查询男生中年龄最大的学生信息
select * from students where sex='男' order by age desc limit 1
-- select age from students order by age desc limit 1
select * from students where (sex,age) = 
    (select sex,age from students where sex='男' order by age desc limit 1)

「limit的用法」
limit用于返回前几条或中间某几条数据
limit接收两个参数：

第一个参数为偏移量，即起始值（注意与索引类似，从0开始，默认值0）
第二个为返回的的数目值

例：

-- 查询学生表的前5条数据
select * from students limit 5;

-- 查询学生表的第六行到第十行数据
select * from students limit 5,5;

注意：上面的解法只是为了演示行子查询，并不是最优解法
表子查询
例：查询数据库和自动化测试课程的成绩

-- 先查出数据库与自动化课程的编号
select * from courses where name in ('数据库', '自动化测试')
-- 再拿着查出的编号去成绩表查询成绩
select * from scores where studentNo in (1,2)
-- 组合成表子查询
select * from scores as sc
inner join (select * from courses where name in ('数据库', '自动化测试')) as c
on sc.coursesNo=c.coursesNo

子查询拓展

查询18到20岁的学生信息
查询小于18岁的学生的信息 ```sql — 1 select * from students where age in (select age from students where age between 18 and 20);

—2 select * from students where age in (select age from students where age<18)

<a name="KTMXa"></a>
## 5.12 日期处理
now()<br />获取当前时间
```sql
select now();

year()&month()&dayofmonth()
从一个日期中提取年、月、日

-- 查询2020年出生的人的信息
select * from stu where year(birthday)=2020;
-- 查询1月份生日的人的信息
select * from stu where month(birthday)=01;
-- 查询1号生日的人的信息
select * from stu where dayofmonth(birthday)=01;
-- 查询指定日期生日的人的信息
select * from stu where birthday="2001-01-02"

5.13 查询演练

「准备数据」

create table goods(
    id int unsigned primary key auto_increment,  -- 编号，主键，无符号，自动增长
    name varchar(150),
    cate varchar(40),
    brand_name varchar(40),
    price decimal(10,3) default 0,  -- 小数，总共10位，小数占3位，正数占7位
    is_show bit default 1,        -- 位变量，一般用来储存状态，值位0/1，类似true/false
    is_saleoff bit default 0
);
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
insert into goods values(0,'x240 超极本','超级本','联想','4999',default,default); 
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

「图示」
Snipaste_2020-03-05_14-27-34 (1)32.jpg
「演练」

-- 1. 查询电脑的平局价格，并保留两位小数
select round(avg(price), 2) from goods;

-- 2.查询所有大于平均价格的商品信息，并按降序排序
select * from goods 
where price>(select avg(price) from goods) 
order by price desc;

-- 3.查询类型为'超极本'的商品价格
select price from goods where cate='超级本';

-- 4.查询价格大于或等于'超级本'的商品信息，并按价格降序排序
select * from goods 
where price >=any (select price from goods where cate='超级本') 
order by price desc;

6 高级操作

6.1 命令行的使用

命令行登录

windows + r ，输入 cmd，打开命令行
输入 mysql -uroot -p ，再输入密码即可（或者直接在 -p 后接上密码）

数据仓库

查看所有数据仓库：show databases;
使用某个数据仓库：use 数据库名;
查看当前使用的数据库：select database();
创建数据库：create database 数据库名 charset=’utf8’;
删除数据库：drop database 数据库名;

数据表

查看当前数据库中的所有数据表：show tables;
查看表结构：desc 表名;
查看表创建语句：show create table 表名;
6.2 备份&恢复
备份
以管理员身份打开命令行窗口，输入：mysqldump -uroot -p密码数据库名>备份文件存放路径
```
mysqldump -uroot -p tashi>D:\tashi_bak.sql
```
恢复
首先，创建一个新的数据库，数据库名建议加上「bak」等标志
```
create database tashi_bak charset='utf8';
```
在备份文件所在路径打开命令行，输入
```
mysql -uroot -p 新的数据库名字<数据库备份文件(或所在路径);
```
6.3 存储过程
定义
「存储过程」也叫「存储程序」，简单来说就是一条或多条SQL语句的集合
「图示如下」：

创建存储过程
「语法」：

delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//

注意：

「delimiter」用于设置分割符号，默认为分号；但是，分号同时是SQL语句的结束符号，所以这里设置为//，当然也可以使用其他字符，不是分号即可
由于设置了分割符号为 //，只有到 // 时，才代表存储过程的命令结束了

「示例」:

delimiter //
create procedure proc_test()
begin
select * from students;
end
//

创建完成后，在函数选项卡中可以看到

调用存储过程
「语法」：

call 存储过程(参数列表)

「示例」：

call proc_test();     -- 调用前面创建的存储过程

总结

存储过程其实就是「可重复执行」并操作数据库的SQL语句的集合
存储过程只需编译一次，然后会被缓存起来，下次直接使用
存储过程可以减少网络交互，减少网络访问流量

6.4 视图
定义
「视图」的本质就是对查询的封装
对于经常在多个地方「重复」使用的「复杂」的查询语句，可以通过定义视图，方便使用

创建视图
「语法」

create  view 视图名称 as  select语句;

「案例」
创建视图，查询学生对应的课程成绩信息

create view v_student_score as 
select stu.studentno, stu.name 姓名, co.name 课程, sc.socre
from students stu 
inner join scores sc on stu.studentno=sc.studentNo
inner join courses co on co.coursesNo=sc.coursesNo;

创建完成后，在Navicat中的「视图」选项卡中可以看到

查看视图

show tables;

注意：

查看视图与查看所有表使用同一命令
在使用视图时，建议将视图名称以「特定标志」开头，比如以V开头，方便区分

使用视图

select * from 视图名称;

-- 如：
select * from v_student_score

删除视图

drop view 视图名称;

6.5 事务

定义

「事务」是一个操作序列，这些操作要么「都执行」，要么「都不执行」
「事务」广泛应用于订单系统，银行系统等多种场景

以一个「转账」流程为例，小明给小方转账500元，那么分解成步骤的话应该是：

小明 - 500
小方 + 500

上面两个步骤必须都完成，才能表示转账操作完成；中间任何一个步骤失败，都代表转账失败

事务常用命令
开启事务：数据的修改会暂时维护到本地缓存中，而不是物理表中

begin;

提交事务：将缓存中的数据变更维护到物理表中

commit;

回滚事务：放弃缓存中变更的数据

rollback;

案例
将学生表中的学生1的年龄减5岁，学生2的年龄加5岁

begin;
update students set age=age-5 where name="学生1";
update students set age=age+5 where name="学生2";
-- 此时，如果不输入commit进行提交的话，数据变更仅仅存在缓存中
commit; -- 提交

6.6 索引

定义
在数据库中建立「索引」，可以大大「提高数据查询的速度」。如果合理使用索引是一辆兰博基尼的话，那么没有使用索引就是一辆人力三轮车。

索引常用命令
查看索引

show index from 表名;

创建索引

-- 方法一：在创建表时就创建索引
create table create_index(
id int primary key,        -- 指定字段为主键则自动创建索引
name varchar(10) unique,   -- 指定字段为unique，唯一约束，也会自动创建索引
age int,                   -- 普通字段不会自动创建索引
key(age)                   -- 使用key为普通字段创建索引
);

-- 方法二：
create index 索引名称 on 表名(字段名); -- 注意：如果字段为字符串，则需指定长度，与创建时一致
-- 例：
create index name_index on create_index(name(10));
create index age_index on create_index(age);

删除索引

drop index 索引名称 on 表名;

案例
创建一张存有10万条数据的表，在有索引与无索引的情况依次进行查询，对比查询消耗时间
「操作步骤」：

新建一个「test_index」表，并导入10万条数据
打开命令行登录MySQL
开启运行时间监测
```
set profiling=1;
```

查找第10万条数据

select * from test_index where title="test100000";

查看运行时间
```
show profiles;
```

为test_index表创建索引

create index title_index on test_index(title(10));

再次查询第10万条数据

select * from test_index where title="test100000";

再次查看运行时间
```
show profiles;
```
「无索引与有索引查询时间对比图」

从图中可以看出，有索引比无索引的查询速度至少提高了100+倍

总结
虽然，使用「索引」后的查询速度优势很明显。但是，在创建了索引的表中进行数据变更时，会降低变更的速度，比如「insert、update、delete」等。因为，每一次修改都意味着要重新生成一次新的索引。
如果，在某些项目中，查询语句的使用频率远大于增删改语句，那么，可以在进行大量数据导入时先暂时删除索引，数据导入完成后再添加索引，其余情况可以优先设计使用索引，以提高查询速度。

6.7 命令行查询中文乱码

命令行中登录数据库
执行：set character_set_client=gb2312;
执行：set character_set_results=gb2312;
- 因为命令使用的时gbk的编码，而数据库使用的时utf8编码，从数据库服务端查询得到的数据在命令行这边会显示乱码，所以要在数据库服务端中修改这个编码格式。如果下次还是乱码，则需要再次手动修改。
- 可以使用 show variables like “character%”;去看查看编码格式

7 练习

准备数据

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `DEPTNO` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `DNAME` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `LOC` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', '会计', '北京');
INSERT INTO `dept` VALUES ('20', '调研', '上海');
INSERT INTO `dept` VALUES ('30', '销售', '深圳');
INSERT INTO `dept` VALUES ('40', '运营', '广州');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `EMPNO` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ENAME` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `JOB` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `HIREDATE` datetime DEFAULT NULL,
  `SAL` int(10) UNSIGNED DEFAULT NULL,
  `DEPTNO` int(11) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('7112', '白起', '经理', '1981-01-01 00:00:00', 3900, 20);
INSERT INTO `emp` VALUES ('7231', '张飞', '销售员', '1982-02-01 00:00:00', 1750, 30);
INSERT INTO `emp` VALUES ('7369', '王昭君', '职员', '1980-12-17 00:00:00', 800, 20);
INSERT INTO `emp` VALUES ('7499', '诸葛亮', '销售员', '1981-02-20 00:00:00', 2100, 30);
INSERT INTO `emp` VALUES ('7654', '大乔', '销售员', '1981-09-28 00:00:00', 1750, 30);
INSERT INTO `emp` VALUES ('7689', '孙尚香', '经理', '1981-05-01 00:00:00', 3350, 30);
INSERT INTO `emp` VALUES ('7782', '百里玄策', '经理', '1981-02-03 00:00:00', 2600, 30);
INSERT INTO `emp` VALUES ('7788', '小乔', '分析员', '1990-01-01 00:00:00', 2500, 30);
INSERT INTO `emp` VALUES ('7839', '百里守约', '总裁', '1999-01-01 00:00:00', 5000, 10);
INSERT INTO `emp` VALUES ('7844', '妲己', '销售员', '1981-09-09 00:00:00', 2000, 30);
INSERT INTO `emp` VALUES ('7876', '李白', '职员', '2001-01-02 00:00:00', 1100, 20);
INSERT INTO `emp` VALUES ('7900', '孙膑', '职员', '1878-01-01 00:00:00', 1450, 30);
INSERT INTO `emp` VALUES ('7902', '阿客', '分析员', '1878-02-03 00:00:00', NULL, 20);
INSERT INTO `emp` VALUES ('7934', '鲁班七号', '职员', '1981-01-23 00:00:00', 2000, 10);

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`  (
  `GRADE` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `LOSAL` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `HISAL` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

数据图示

习题
1.查询所有员工的部门号和部门名

--  等值连接写法
select ENAME, emp.DEPTNO, DNAME  from emp,dept 
where emp.DEPTNO=dept.DEPTNO ;

-- 内连接写法
select ENAME, emp.DEPTNO, DNAME from emp
inner join dept on emp.DEPTNO=dept.DEPTNO;

2.查询所有薪资水平大于鲁班七号的员工的信息

-- 子查询
select * from emp where 
sal>(select sal from emp where ename='鲁班七号');

3.查询有员工的部门信息

select * from dept where deptno in (
select distinct(DEPTNO) from emp);

4.查询从事「职员」工作的员工姓名和所在部门名称

select emp.ename, job,dept.dname from emp 
inner join dept on emp.deptno=dept.deptno
where job='职员'

5.查询每个部门的名称，编号以及最低薪资

select  d.deptno, d.dname, min(sal) from emp e 
inner join dept d  on e.deptno=d.deptno 
group by e.deptno

注意：5.7版本会报1055错误，到时候忽略这道题
6.查询「销售部」所有员工的姓名

select e.ename from emp e, dept d 
where e.deptno=d.deptno 
and d.dname='销售';

7.查询薪资大于平均薪资的员工信息

select * from emp 
where sal >(select avg(sal) from emp)

8.查询与「大乔」从事相同工作的员工信息，不包含大乔自己

select * from emp 
where job=(select job from emp where ename='大乔')
and ename!='大乔'

9.查询与销售部薪资水平相同的员工信息

select * from emp e, dept d 
where e.deptno=d.deptno 
and e.sal in (
        select sal from emp e, dept d 
        where e.deptno=d.deptno 
        nd d.dname='销售'
        );

10.查询薪资大于销售部所有人薪资水平的员工信息

select * from emp e, dept d 
where e.deptno=d.deptno 
and sal >all(
    select sal from emp e, dept d 
    where e.deptno=d.deptno 
  and d.dname='销售');

11.查询每个部门的编号，名称，位置以及员工总数

select d.deptno,dname,loc, count(*) as 员工总数 from emp e,dept d 
where e.deptno=d.deptno 
group by e.deptno

12.查询员工的姓名，部门名称，薪资以及薪资级别

select ename,dname, sal, grade from emp e,dept d, salgrade s
where e.deptno=d.deptno
and sal between s.losal and s.hisal

13.查询员工工作以及此工作的最低工资

select job, min(sal) from emp group by job

14.查询不同部门经理的最低工资

select dname,min(e.sal) from emp e,dept d 
where e.deptno=d.deptno and job='经理'
group by e.deptno

15.查询员工的姓名，工作，薪资以及该工作的最低工资

select e.ename, e.job, e.sal, r.msal 
from emp e, (select job, min(sal) msal from emp group by job ) r 
where e.job=r.job

16.查询所有经理中的最低薪资

select * from emp 
where job='经理' 
order by sal desc limit 1

17.查询每个部门中的经理的最低薪资

select r.ename, r.job, r.deptno, r.sal 
from (select * from emp order by sal) r 
where r.job='经理' group by r.deptno

「group by」在分组时，如果改组有多条数据，会取出一条，而这取出的一条是按照主键的顺序来取得所以，在某个部门中有多个经理时，需要先把所有人的薪资升序排好，这样分组时就会取出最低的那个

18.查询员工的姓名、年薪，并且按照年薪降序排序(不包含奖金)

select ename, sal*12 as ysal 
from emp 
order by ysal desc

19.查询员工的姓名、年薪，并且按照年薪降序排序(包含奖金)

select ename, sal*12+ifnull(comm, 0) as ysal 
from emp order by ysal desc

「ifnull」函数用来判断某个数据是否为空，如果为空则返回指定数据，不为空则返回原数据如果不用ifnull坐判断的话，那么当员工奖金为null时，null+年薪会变成null

20.查询员工薪资处于第4级别的员工信息

select * from emp, salgrade
where sal between losal and hisal 
and grade=4

21.查询薪资级别为2的员工姓名，部门所在地，以及该薪资级别的最低和最高薪资

select ename, loc, losal, hisal  from emp, salgrade, dept 
where sal between losal and hisal 
and emp.deptno=dept.deptno 
and grade=2

22.查询所有薪资级别比王昭君大的员工信息并且按照薪资级别降序排序

select * from emp,salgrade s
where sal between losal and hisal 
and grade>(
  select grade  from emp,salgrade
    where sal between losal and hisal 
    and ename='王昭君'
) order by grade desc

23.将白起的薪资加1000（强调字段值可以进行运算）

update emp set sal=sal+1000 where name="白起";

24.查询12月入职的员工信息

select * from emp where month(hiredate)=12;

25.查询81年入职的员工信息

select * from emp where year(hiredate)=1981;

26.查询17号入职的员工的信息

select * from emp where dayofmonth(hiredate)=17;

27.查询今天入职的员工信息

select * from emp where to_days(hiredate)=to_days(now());

28.将销售部所有的员工薪资加500

update emp set sal=sal+500 where deptno=(select deptno from dept where dname="销售");

02 | MySQL从入门到精通

1 知识地图

2 概念

2.1 数据库的定义

2.2 关系型数据库

2.3 排名与分类

2.4 SQL与MySQL

3 安装与卸载

3.1 CentOS安装5.1版本

安装MySQL

启动MySQL

登录MySQL

修改密码

刷新权限

关闭防护墙

新建用户远程连接mysql数据库

允许任何ip地址(%表示允许任何ip地址)的电脑用admin帐户和密码(123456)来访问这个mysql server。

注意admin账户不一定要存在。

刷新权限

4 Navicat的使用

4.1 连接MySQL

4.2 新建数据库

4.3 数据表操作

4.4 数据增删改查

4.5 数据的类型与约束

5 SQL

5.1 用户操作

5.1.1 创建用户

5.1.3 查看用户权限

5.1.4 用户赋权

5.1.5 收回权限

5.1.6 修改用户名

5.1.7 修改密码

5.1.8 删除用户

5.2 数据表操作

5.2.1 创建表

5.2.2 删除表

5.3 数据增删改

5.3.1 插入数据

5.3.2 修改数据

5.3.3 删除数据

5.4 基础查询

5.5 去重查询

5.6 条件查询

5.7 查询排序

5.8 聚合函数

5.9 分组函数

5.10 连接查询

5.11 等值连接

5.12 内连接

5.13 左连接

5.14 右连接

5.11 子查询

5.13 查询演练

6 高级操作

6.1 命令行的使用

6.2 备份&恢复

6.3 存储过程

6.4 视图

6.5 事务

6.6 索引

6.7 命令行查询中文乱码

7 练习