2306训练营MySQL

一、SQL

1、SQL（Structrued Query Language）

结构化查询语言，用于访问和处理数据库的标准的计算机语言

2、语法特点

（1）SQL对关键字的大小不敏感（windows）
（2）SQL语句可以多行或者多行书写，以分号结束
（3）SQL的注释

-- 单行注释，后面一定要加一个空格
SELECT * FROM emp; -- 查询emp所有的数据

#单行注释，#后面可加可不加空格

/*
多行注释
多行注释
*/

3、数据库系统

（1）关系型数据库（RDBMS）
①Oracle
②MySQL
③SQL server
④PostgreSQL
⑤SQLite

（2）非关系型数据库（NoSQL）
①Redis
②MongoDB
③Elasticsearch
④Cassandra
⑤Hbase

4、MySQL数据库基本操作-DDL

DDL（Data Definition Language）数据定义语言，包括：对数据库的常用操作、对表结构的常用操作、修改表结构

（1）对数据库的常用操作

功能	SQL
查看所有的数据库	show databases;
创建数据库	create database [if not exists] mydb1 [charset=utf8]
切换（选择要操作的）数据库	use mydb1;
删除数据库	drop database [if exists] mydb1;
修改数据库编码	alter database mydb1 character set utf8

（2）对表结构的常用操作-创建表

创建表格式：

create table [if not exists] 表名(
	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名3 类型[(宽度)] [约束条件] [comment '字段说明'],
) [表的一些设置];

注：
常见的数据类型有：①数值类型；②日期和时间类型；③字符串类型
①数值类型

类型	大小	用途
TINYINT	1byte	小整数值
INT或INTEGER	4byte	大整数值
DOUBLE	8byte	浮点数值

②字符串类型

类型	大小	用途
CHAR	0-255byte	定长字符串
VARCHAR	0-65535byte	变长字符串

③日期类型

类型	大小(byte)	格式	用途
DATE	3byte	YYYY-MM-DD	日期值
DATETIME	8byte	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4byte	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

（3）对表结构的常用操作-其他操作

功能	SQL
查看当前数据库的所有表名称	show tables;
查看指定某个表的创建语句	show create table 表名;
查看表结构	desc 表名;
删除表	drop table 表名;

（4）对表结构的常用操作-修改表结构格式
①修改表添加列

语法格式：

alter table 表名 add 列名 类型(长度) [约束];

②修改列名和类型

语法格式：

alter table 表名 change 旧列名 新列名 类型(长度) [约束];

③修改表删除列

语法格式：
alter table 表名 drop 列名;

④修改表名

语法格式：

rename table 表名 to 新表名;

5、MySQL数据库基本操作-DML

DML是指数据操作语言，Data Manipulation Language，用来对数据库表中的数据记录进行更新

关键字：insert（插入）、delete（删除）、update（更新）

（1）数据插入

语法格式：

insert into 表名(列名1,列名2,列名3...) values (值1,值2,值3...);	//向表中插入某些列

insert into 表名 values (值1,值2,值3...); 	//向表中插入所有列

（2）数据修改

语法格式：

update 表名 set 字段名1 = 值1,字段名2 = 值2...;

#根据条件修改
update 表名 set 字段名1 = 值1,字段名2 = 值2... where
条件;

（3）数据删除

drop 删除表
detele和truncate 删除表中数据

语法格式：
delete from 表名 [where 条件];

truncate table 表名
或者
truncate 表名

注：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解为是将整个表删除，然后再创建该表

6、MySQL约束

约束(constraint)：实际上就是表中数据的限制条件

（1）分类

①主键约束(primary key) PK
②自增长约束（auto increment）
③非空约束（not null）
④唯一性约束（unique）
⑤默认（default）
⑥零填充约束（zerofill）
⑦外键约束（foreign key） FK

（2）主键约束

①概念：

主键约束的关键字是：primary key；
主键约束相当于 唯一约束 + 非空约束的组合，主键约束的列不允许重复，也不允许出现空值；
每个表最多只允许一个主键；

②操作：

a.添加单列主键：有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键

方式1-语法：
create table 表名(
	...
	<字段名> <数据类型> primary key
	...
)

方式2-语法：
create table 表名(
	...
	[constraint <约束名>] primary key [字段名]
);

b.通过修改表结构添加主键

语法格式：
create table 表名(
	...
);

alter table <表名> add primary key (字段列表);

c.删除主键约束

语法格式：

alter table <表名> drop primary key;

（3）自增长约束

语法格式：

create table 表名(
	字段名1 字段类型 primary key auto_increment,
	字段名2 字段类型,
	...
);

自增长的值如果一开始没有给定的话，默认从1开始

注：
①默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1
②一个表中只能有一个字段使用auto_increment约束
③auto_increment约束的字段必须具备NOT NULL属性
④auto_increment约束的字段类型只能是整数类型（TINYINT、INT）
⑤auto_increment约束字段的最大值是该字段的数据类型约束，如果达到上限，auto_increment就会失效

b.指定自增字段初始值

语法格式：

#方式1，创建表时指定
create table 表名(
	字段名1 字段类型 primary key auto_increment,
	字段名2 字段类型,
) auto_increment = 自增初始值;

#方式2，创建表之后指定
create table 表名(
	字段名1 字段类型 primary key auto_increment,
	字段名2 字段类型,
);

alter table 表名 auto_increment = 自增初始值;

注：
truncate数据之后自动增长从默认起始值开始；
delete删除之后，自增长还是在最后值基础上加1而不是直接从1开始，永远都是从1开始，不会以之前设置的默认值开始；

（4）非空约束

not null，非空约束指字段的值不能为空，对于使用了非空约束字段如果用户在添加数据是没有指定值，数据库系统就会报错

①添加非空约束

语法格式：

方式1，创建表时指定
create table 表名(
	字段名1 类型 [not null],
	字段名2 类型 [not null]
	...
);

方式2，创建表之后指定
create table 表名(
	字段名1 类型,
	字段名2 类型,
	...
)

alter table 表名 modify 字段名 字段类型(长度) [not null]；

②删除非空约束

语法格式：
alter table 表名 modify 字段名 类型;

（5）唯一性约束

unique key：指所有记录中字段的值不能重复出现

①添加唯一性约束

语法格式：

方式1，创建表时指定
create table 表名(
	字段名 字段类型 [unique]
	...
)

方式2，创建表之后指定
alter table 表名 add constraint 约束名 unique (列);

②删除唯一性约束

语法格式：

alter table 表名 drop index 唯一约束名

注：如果不是使用第二种方法添加的唯一约束，那此时的唯一约束名就是列名

（6）默认约束

default：用来指定某列的默认值

①添加默认约束

语法格式：

方式1，创建表时指定
create table 表名(
	字段名 字段类型 default 默认值,
	...
)

方式2，创建表之后指定
create table 表名(
	字段名 字段类型,
	...
)

alter table 表名 modify 列名 类型 default 默认值;

②删除默认约束

语法格式：

alter table 表名 modify column 字段名 类型 default null;

（7）零填充约束

zero fill：
①插入数据时，当该字段的值长度小于定义的长度时，会在该值的前面补上相应的0
②zerofill默认为int(10)

①添加零填充约束

语法格式：

create table 表名(
	字段名1 类型 zerofill,
	...
);

②删除零填充约束

语法格式：

alter table 表名 modify id int;

7、MySQL基本操作-DQL

DQL：Data Query Language，数据查询语言，数据库管理系统中一个重要的功能就是查询，数据查询不应只是简单的返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示

语法格式：

select 
	[all|distinct]
	<目标列的表达式1> [别名1],
	<目标列的表达式2> [别名2]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

简化版语法

	select  *|列名 from 表 where 条件

（1）运算符

①算术运算符

算术运算符	说明
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取模，求余数

②比较运算符

比较运算符	说明
=	等于
< 和 <=	小于和小于等于
> 和 >=	大于和大于等于
<=>	安全的等于
<> 或 !=	不等于
IS NULL 或 ISNULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否介于两个值之间
IN	判断一个值不是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配，模糊查询

③逻辑运算符

逻辑运算符	说明
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 或者	逻辑或

（2）排序查询(order by)

语法格式：

select 
	字段名1,字段名2
from 表名
order by 字段名1 [asc|desc],字段名2[asc|desc]......

特点：
①asc代表升序，desc代表降序，如果不写默认升序
②order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
③order b子句，放在查询语句的最后面，LIMIT子句除外

（3）聚合查询

聚合函数会忽略空值

聚合函数	作用
count()	记录行数
sum()	求和
max()	求最大值
min()	求最小值
avg()	求平均值

（4）分组查询(group by)

语法格式：

select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;

如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段尽量不要出现

group by后面可以跟多个字段

分组之后的条件筛选-having
①分组之后对统计结果进行筛选的话必须要使用having，不能使用where
②where子句用来筛选FROM子句指定的操作所产生的行
③group by子句用来分组where 子句的输出
④having子句用来分组的结果中再次做筛选

语法格式：

select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;

（5）分页查询(limit)

语法格式：

-- 方式1，显示前n条数据
select 字段1,字段2... from 表名 limit n

-- 方式2，分页显示
select 字段1,字段2... from 表名 limit m,n
m：(当前页-1)*每页显示条数
n：表示查询多少条数据(每页显示条数)

（6）INSERT INTO SELECT语句

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT 语句

语法格式：

insert into 表名(字段名1,字段名2,...) select value1,value2,... from Table1 或者：
insert into 表名 select * from Table1

注：
①SQL执行顺序：from -> where -> group by -> count -> having -> select -> order by -> limit
②ifnull函数，比如ifnull(salary,0)，则当作0，不为null，则还是原来的值

8、MySQL多表操作

多表关系：一对一、一对多/多对一、多对多

（1）一对一关系

一个学生只有一张身份证，一张身份证只能对应一个学生；
在任意表中添加唯一外键，指向另一方主键，确保一对一关系；
一般一对一关系很少见，遇到一对一关系的表最好是合并表

（2）一对多/多对一关系

部门和员工
一个部门有多个员工，一个员工只能对应一个部门
实现原则：在数据多的一方建立外键，指向数据少的一方的主键