整理MySQL《葵花宝典》

MySql数据库的使用

MySQL 数据是C/S架构
必须通过客户端(mysql) 才能使用到服务器(mysqld)

D:
cd /d D:\MyWork\MySQL\mysql-5.7.28-winx64\bin
mysql -h127.0.0.1 -P3306 -uroot -p"123456"

/d 后面的路径是你真实的安装路径.

查看服务器中有哪些个数据库
show databases;

创建新的数据库
create database company;

切换当前工作数据库
use company;

查看当前数据库中有哪些表
show tables;

导入.sql文件中的数据
source d:/company.sql;

查看表中的数据
select * from employees;
select * from departments;

实际操作 : 创建数据库company, 并切换数据库, 导入company.sql
实际操作 : 创建数据库world, 并切换数据库, 导入world.sql

丢弃数据库(删除数据库)
drop database 数据库名;

服务器
数据库1(目录)
表1(文件)
记录1
记录2
记录3
…
表2
表3
…
数据库2(目录)
…

1—基本 SELECT 语句

SELECT 标识选择哪些列。
FROM 标识从哪个表中选择。

举例：

查看表中的数据
select * from employees;
select * from departments;

select
name,
code,
continent,
indepyear
from
country

注 意

• SQL 语言大小写不敏感。
• SQL 可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各子句一般要分行写。
• 使用缩进提高语句的可读性。

列的别名

给列起别名, 使用关键字 as, 也可以省略as, 但是必须要有空格
别名可以使用""包围, 目的是让它原样显示或别名中包含特殊符号时.
select
continent 大洲,
surfacearea as area,
name,
code
from
country;

SELECT last_name “Name”, salary*12 "Annual Salary"
FROM employees;

查询结果表,是一张虚表.
select – 切割列
continent,
surfacearea,
name,
code
from
country; – 基表, 数据从这个表中取.

显示表结构describe

查看表结构(有哪些列, 列的数据类型)
describe customer;

查看表结构
desc country;

过滤 WHERE

使用WHERE 子句，将不满足条件的行过滤掉

WHERE 子句紧随 FROM 子句。

示例：

查询人口数大于1亿的国家的 国家代码, 名称, 所属大洲及人口.

select
code,
name,
continent,
population
from
country
where
population > 100000000;

比较运算

操作符	含义
=	等于（不是==）
>	大于
>=	大于 等于
<	小于
<=	小于 等于
<> !=	不等于
:=	赋值

​

BETWEEN

使用 BETWEEN 运算来显示在一个区间内的值

工资在范围[2500~3500]

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

IN

使用 IN运算显示列表中的值

经理id在()范围内的查询出来

SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);

LIKE

使用 LIKE 运算选择类似的值

选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。

查询名字以S开头的

SELECT
first_name
FROM
employees
WHERE
first_name LIKE ‘S%’;

---

select
code,
name
from
country
where
name like ‘china’; – 字符串里面没有通配符时, Like和 = 一样

– 常用的模糊查询
like '%张三%'

查询城市表中的哪些城市的名称包含ing.
select

---

from
city
where
name like ‘%ing%’;

– 查询第3个字母是o的员工

SELECT last_name
FROM employees
WHERE last_name LIKE ‘__o%’;

NULL

使用 IS (NOT) NULL 判断空值

NULL值参与任何的比较运算, 结果一定是false, NULL只支持 Is 操作.
– 查询尚未独立的国家

select
name,
code,
continent,
indepyear
from
country
where
indepyear = null;

逻辑运算

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

ORDER BY子句

使用 ORDER BY 子句排序

​ ASC（ascend）: 升序(默认方式)
​ DESC（descend）: 降序

ORDER BY 子句在SELECT语句的结尾

按工资大小进行排序, 是升序

SELECT last_name, job_id, department_id, salary
FROM employees
ORDER BY salary asc;

多个列时, 按顺序排, 前面 先排, 在排好的基础上进一步细排

SELECT
last_name, department_id, salary
FROM
employees
ORDER BY
department_id ASC,
salary DESC;

多表查询

笛卡尔集

笛卡尔集会在下面条件下产生:

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件

Mysql 连接

使用连接在多个表中查询数据

• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀

内连接inner join 和 on

内联接时, on和where是可以互换的. inner关键字也可以省略
虽然on和where可以互换, 但是绝对不要这样做.

select
ci.name,
co.name
from
city2 ci
join
country2 co
on
ci.countrycode = co.code
where
ci.population > 1000000;

查询所有国家名称及首都名称和官方语言

select
co.name country,
ci.name capital,
cl.language offical
from
country co
join
city ci
on
co.capital = ci.id – 联接条件
join
countrylanguage cl
on
co.code = cl.countrycode – 联接条件
where
cl.IsOfficial = ‘T’;

外连接

外联接 : 保证某张表的数据完整, 如果是联接条件为真的记录直接连接,但是联接条件为假的也保留,但是联接的是一个空行.

内联接没有左右之分, 所以只要有左右,就一定是外联, 所以outer关键字省略.
外联接时, on和where绝对不能互换.而且on不可以省略.

练习 : 找出所有国家及首都名称, 不要丢失国家数据.

select
co.name country,
ci.name capital
from
country co
left join
city ci
on
co.capital = ci.id
order by
capital desc;

练习 : 找出哪些国家没有官方语言

select
co.name country,
cl.language,
cl.IsOfficial
from
country co
left join
countrylanguage cl
on
co.code = cl.countrycode
and
cl.IsOfficial = 'T’
where
cl.IsOfficial is null
order by
country desc;

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

组函数类型

也称聚集函数, 统一处理运算. 统计运算. 特点就是一个组就一个结果值

AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
SUM() 求和

找出欧洲和亚洲国家的最小平均寿命和最大平均寿命.

****select
min(lifeexpectancy),
max(lifeexpectancy)
from
country
where
continent in (‘asia’, ‘europe’);

GROUP BY 子句语法

可以使用GROUP BY子句将表中的数据分成若干组

明确：WHERE一定放在FROM后面

group by 后面多个列时, 会以多个列的组合值为分组依据!!

select
continent, – 群体还是个体?
region,
max(population)
from
country
where
population > 5000000
group by
continent,
region;

如果有group by, 必须把分组依据的列放在select中.

select
continent, – 代表群体的概念.
avg(population)
from
country
group by
continent;

过滤分组： HAVING 子句

最后的统计结果再过滤行, 必须使用having

select
continent, – 群体还是个体?
region,
max(population) maxPop
from
country
where
population > 5000000
group by
continent,
region
having
maxPop > 100000000 – 能否 ?
order by
maxPop;

非法使用组函数

不能在 WHERE 子句中使用组函数。

可以在 HAVING 子句中使用组函数。

标准SQL中涉及的所有关键字

1) from : 确定主基表
2) join : 确定次基表 再进行联接(内联, 外联[考虑左右])
3) on : 联接条件
4) 如果还有多表 继续 join 和 on
5) where : 对以上的基表进行行过滤
6) group by : 分组, 特别注意分别依据什么列. 一旦分了组, 分组依据的列, 第一时间放在select后.
7) select : 进一步处理需要哪些列
8) having : 最终分组后的虚表的行过滤.
9) order by : 最终的虚表的排序.

2—子查询

子查询语法

• 子查询 (内查询) 在主查询之前一次执行完成。
• 子查询的结果被主查询(外查询)使用 。

一般情况下子查询返回的结果是一列一行.

查看人均寿命最低的国家.

select
name,
continent,
lifeexpectancy
from
country
where
lifeexpectancy = (select min(lifeexpectancy) from country);

找出各大洲中人口数大于本大洲平均人口的国家.
select
co1.name,
co1.continent,
co1.population,
co2.avgPop
from
country co1
join
(select
continent,
avg(population) avgPop
from
country
group by
continent) co2
on
co1.continent = co2.continent
where
co1.population > co2.avgPop
order by
co1.continent;

3—创建和管理表

创建数据库

创建数据库：
drop database if exists 数据库名;
create database if not exists 数据库名 charset 字符集;

– 修改数据库
alter database 数据库名 charset 新字符集; – 字符集的改变不会影响已有表.

创建一个保存员工信息的数据库
create database employees;

show databases;查看当前所有数据库

use employees;“使用”一个数据库，使其作为当前数据库

CREATE TABLE 语句

必须具备:
CREATE TABLE权限
存储空间

必须指定:
表名
列名, 数据类型, 尺寸

创建数据库
create database if not exists school charset gbk;

修改数据库, 只能修改字符集
alter database school charset utf8;

– 丢弃数据库 – 谨慎操作 : 数据库中所有东西全部丢弃, 并且不可逆.
drop database if exists school;

– 创建表 :
create table if not exists 表名(
列1 数据类型1(长度1) 其他选项1,
列2 数据类型2(长度2) 其他选项2,
列3 数据类型3(长度3) 其他选项3,
列4 数据类型4(长度4) 其他选项4,
…,
表级约束1,
表级约束…
) engine 引擎名 charset 字符集;

create table if not exists teacher(
id int auto_increment, – auto_increment是自增, 在插入数据时可以忽略它, 但是它会自动生成.
name varchar(20) not null, – 非空
age int,
phone varchar(15),
primary key(id) – 主键 : 它对应的数据是非空且唯一.
);

数据类型

int : 4字节整数
bigint : 8字节整数
char(长度) : 定长字符串 char(10), 实际数据’abc’ 底层总是10个字符
varchar(长度) : 变长字符串 varchar(10), 实际数据’abc’, 底层就是3个字符
double : 双精度浮点数
decimal : 定点数(精准, 但是废空间)
date : 日期
time : 时间
datetime : 日期时间
longtext : 长文本(4G)

使用子查询创建表

可以实现表的复制, 不能完全复制表结构.
create table if not exists country2
select * from world.country;

完全复制表结构, 遗憾的是没有数据
create table if not exists country3
like world.country;

ALTER TABLE 语句

使用 ALTER TABLE 语句可以实现：
向已有的表中添加列
修改现有表中的列
丢弃现有表中的列
重命名现有表中的列

创建表

1. 全新建表
   create table if not exists 表名(
   列1 数据类型1(长度) 其他选项(包括列级约束(unique, not null, default)),
   列2 数据类型2(长度) 其他选项,
   …,
   表级约束1,
   表级约束2
   ) engine 引擎名 charset 字符集;

2. 子查询建表
   create table if not exists 表名(
   列1 数据类型 其他选项1,
   列2 数据类型 其他选项2,
   列3 数据类型 其他选项3,
   )
   select
   列11, – 取出的值必须被新表列1的数据类型兼容
   列12,
   列13
   from
   基表;

create table if not exists 表2 select * from 表1;

3. 复制表结构
   create table if not exists 表2 like 表1;

修改表

主句都一样, alter table 表名
– 添加新列
alter table teacher
add column gender enum(‘男’, ‘女’) default ‘男’;

alter table teacher
add column address varchar(50) after name;

alter table teacher
add column email varchar(100) first;

–在电话后面添加新列address
alter table student
add column address varchar(50) after phone;
–在最前面添加一个新列 height
alter table student
add column height int first;

– 修改列数据类型等
alter table 表名
modify 列名 新数据类型(新长度) 新其他选项

alter table teacher
modify address varchar(100) default “北京昌平”;

– 修改列名等
alter table 表名
change 老列名 新列名 新数据类型(新长度) 新选项;

alter table teacher
change phone mobile char(20);

– 丢弃列 会导致被丢弃的列对应的所有数据丢失.
alter table 表名
drop column 列名;

alter table teacher
drop column email;

– 修改表名
alter table 表名
rename to 新表名;

alter table teacher
rename to teachers;

4—数据处理之增删改

数据操纵语言

DML 语句 : 数据操纵语言 : 事务构成者. 可以后悔
select
insert
update
delete

数据定义语言

DDL 语句 : 数据定义语言 和数据库对象操作相关, DDL是事务终结者. 无法后悔
create
alter
drop
truncate

数据控制语句

DCL 语句 : 数据控制语句 : 事务终结者
commit
rollback
grant

事务

– 事务 : 一组逻辑操作单元, 使得数据从一种状态切换到另一种状态.
– 操作的相关表必须是使用的InnoDB存储引擎.

A 原子性 : 事务中的一组操作要么全部成功, 要么全部失败.
C 一致性 : 事务前后数据一致.
I 隔离性 : 不同事务之间的操作是隔离, 互不影响.
D 持久性 : 事务一旦结束,所有操作持久生效.

– 事务过程

1. 启动事务 : set autocommit = false – 默认情况下自动提交是开启 设置只对当前会话有效.
   set @@autocommit=off
   begin, start transaction;

2. 事务组成 : 由DML组成

3. 事务结束 :
   DDL 事务是提交的.
   DCL :
   commit 提交 : 所有DML操作真的生效,写硬盘
   rollback 回滚 : 回滚到事务前, 所有DML语句撤销.
   客户端正常结束 : 事务提交
   客户异常结束 : 事务回滚

4. 还原设置 : set autocommit = true; set @@autocommit=on;

– 启动事务, 删除city2表中的数据再查看. 在另一个客户端中也查看表中数据
– 如果回滚事务? 如果提交事务.

预编译

把一个SQL提前交给服务器, 让它进行编译, 生成的执行体就可以方便的多次执行.
好处 : 提升效率, 执行方便.

prepare 预编译执行体名 from ‘SQL’;

prepare p1 from ’
select * from teachers
';

– 执行预编译
execute p1;

– 丢弃预编译
drop prepare p1;

– ?,和? 相当于方法的形参.
– ?只能代替某个值, 不可以代替表名,列名…
prepare p2 from ’
insert into teachers(
name,
age
) values (
?,
?
)
';

set @name=‘婷姐’, @a = 25;

– 必须通过用户变量来完成参数值的传递
execute p2 using @name,@a;

– 插入classes表中记录的sql预编译, 然后再创建用户变量, 并执行预编译.

prepare p3 from ’
insert into classes(
name,
classtype,
room,
begindate,
master
)values(
?,
?,
?,
?,
?
)
';

set @v1=‘JavaEE0522’, @v2=‘JavaEE’, @v3=‘101’, @v4=‘2020-05-22’,@v5=1;
execute p3 using @v1,@v2,@v3,@v4,@v5;

– 如果在被编译的sql中有’, 必须对其进行转义’'
prepare p4 from ’
insert into classes(
name,
classtype,
room,
begindate,
master
)values(
?,
?,
‘‘202’’,
now(),
?
)
';

– 丢弃唯一键
alter table classes drop key room;

prepare p5 from ’
delete from classes where id = ?
';

prepare p6 from ’
create table country3 select * from world.country
';

5—约束和分页

约 束

– 约束 : 主键约束只允许有一个
– 表级约束更好用, 可以做成联合的.

• not null 非空
• unique 唯一, 可以空, 一张表中可以有多个唯一约束
• default 缺省值
• primary key 主键(对于一张表来说最重要), 一张表只允许有一个主键
• foreign key 外键 保证子表中的数据的引用合法性. 在插入子表数据时必然会扫描父表一张表中可以有多个外键约束

create table if not exists test(
– id int primary key, – 列级主键
id int,
name varchar(20),
age int not null, – 必须是列级约束
gender char(1),
primary key(id, name), – 联合主键
unique(gender, age) – 联合唯一键
);

– 给班主任加上外键约束

drop table if exists classes;
create table if not exists classes (
id int auto_increment,
name varchar(20),
classtype enum(‘JavaEE’, ‘大数据’, ‘H5’) default ‘JavaEE’,
room char(3) unique, – 唯一, 不允许重复,但是可以为null
begindate date,
master int, – 引用老师
primary key(id),
– foreign key(本表的要引用别的表的列的数据的列) references 父表(父表的主键);
foreign key(master) references teachers(id)
);

– 当父表中的记录被子表引用时, 不可以直接删除父表中被引用的记录.

– 重新调整一下外键
– 查看外键名称, 先查看建表语句
– 丢弃外键约束时, 必须提供外键名.
alter table classes
drop foreign key classes_ibfk_1;
– 重新添加外键约束
alter table classes
– add constraint myfk foreign key(master) references teachers(id) on delete do nothing;

– 不可以删除父表中被引用的列.

add constraint myfk foreign key(master) references teachers(id) on delete cascade;

– 当删除父表中被引用的记录时, 子表被波及也删除, 级联删除

alter table classes
add constraint myfk foreign key(master) references teachers(id) on delete set null;

– 当删除父表中被引用的记录时,子表相应的记录中的引用值设置为null

MySQL中使用limit实现分页

– 最终记录只显示10条
select * from city2 limit 10;
– limit 略过的记录数, 最终显示的记录数
select * from city2 limit 0, 10;
select * from city2 limit 10, 10; – 第2页
select * from city2 limit 20, 10;
select * from city2 limit 70, 10; – 第8页

SELECT * FROM table LIMIT (PageNo - 1)*PageSize, PageSize;