MySQL大全（一）

一、SQL分类

DDL Data Definition Language 数据定义语言
用于完成对数据库对象（数据库、数据表、试图、索引等）的创建、删除、修改

DML Data Manipulation Language 数据操作/操纵语言
用于完成对数据表中数据的添加、删除、修改操作
添加：将数据存储到数据表
删除：将数据从数据表中删除
修改：对数据表中的数据进行修改

DQL Data Query Language 数据查询语言
用于将数据表总的数据查询出来

DCL Data Control Language 数据控制语言
用于完成

二、基本语法

SQL指令不区分大小写
每条SQL表达式结束之后都以 ；结束
SQL关键字之间以空格进行分隔
SQL之间可以不限制换行（可以有空格的地方就可以换行）

三、DDL

3.1 DDL-数据库操作

查询数据库
显示当前mysql中的数据库列表

show database;
1

显示指定名称的数据库创建的SQL指令

show create database <dbName>;
1

创建数据库
创建数据库

create database <dbName>;
1

创建数据库，当指定名称的数据库不存在时执行创建

create database if not exists <dbName>;
1

在创建数据库的同事指定数据库的字符集（字符集：数据存储在数据库中采用的编码格式 utf8、gbk）

create database <dbName> character set utf8;
1

修改数据库 修改数据库字符集

alter database <dbName> character set utf8;
1

删除数据库 删除数据库时会删除当前数据库中所有的数据表以及数据表中的数据
删除数据库

drop database <dbName>;
1

如果数据库存在则删除数据库

drop database if exists <dbName>;
1

使用/切换数据库

use <dbName>;
1

3.2 DDL-数据表操作

创建数据表
数据表实际就是一个二维的表格，一个表格是由多列组成，表格中的每一列称之为表格的一个字段

create table students {
	stu_num char(8) not null unique,
	stu_num varchar(20) not null,
	stu_gender char(2) not null,
	stu_age int not null,
	stu_tel char(11) not null unique,
	stu_qq varchar(11) unique
};
1
2
3
4
5
6
7
8

查询数据表

show tables;
1

查询表结构

desc <tableName>;
1

删除数据表
删除数据表

drop table students;
1

当数据表存在是删除数据表

drop table if exists students;
1

修改数据表
修改名

alter table <tableName> rename to <newTableName>;
1

修改数据表字符集。数据表也是有字符集的，默认字符集和数据库一致。

alter table <tableName> character set utf8;
1

添加列（字段）

alter table <tableName> add <columnName> varchar(200);
1

修改列（字段）的列表和类型

alter table <tableName> change <oldColumnName> <newColumnName> <type>;
1

只修改列（字段）类型

alter table <tableName> modify <columnName> <newType>;
1

删除列（字段）

alter table <tableName> drop <columnName>;
1

3.3 MySQL数据类型

数据类型指的是数据表中的列中支持存放的数据的类型
数值类型
在mysql中有多种数据类型可以存放数值，不同的数据存放的数值范围或者形式是不同的

字符串类型
存储字符串序列的类型

日期类型
在MySQL数据库中，我们可以使用字符串来存储时间，但是如果我们需要基于时间字段进行查询操作（查询在某个时间段内的数据）就不便于查询实现

3.4 字段约束

约束介绍
在创建数据表的时候，指定的对数据表的列的数据限制性的要求（对标的列中的数据进行限制）
为什么要给表中的列添加约束呢？
保证数据的有效性
保证数据的完整性
保证数据的正确性
字段常见的约束有哪些呢？
非空约束（not null）：限制此列的值必须提供，不能为null
唯一约束（unique）：在表中的多条数据中，此列的值不能重复
主键约束（primary key）：非空+唯一，能够唯一标识数据表中的一条数据
外键约束（foreign key）：建立不同表之间的关联关系

非空约束
限制数据表中此列的值必须提供
创建表：设置图书表的book_name not null

create table books(
	book_isdn char(4),
	book_name varchar(18) not null,
	book_author varchar(8)
);
1
2
3
4
5

唯一约束
在表中的多条数据，此列的值不能重复
创建表：设置图书表的book_isdn为unique

create table books(
	book_isdn char(4) unique,
	book_name varchar(18) not null,
	book_author varchar(8)
);
1
2
3
4
5

主键约束
主键就是数据表中记录的唯一标识，在一张表中只能有一个主键（主键可以是一个列，也可以是多个列的组合）
当一个字段声明为主键之后，添加数据时，此字段下的数据不能为null且此字段数据不能重复
创建表是添加主键约束

create table books(
	book_isdn char(4) primary key,
	book_name varchar(18) not null,
	book_author varchar(8)
);
//或者
create table books(
	book_isdn char(4),
	book_name varchar(18) not null,
	book_author varchar(8),
	primary key(book_isdn)
);
1
2
3
4
5
6
7
8
9
10
11
12

删除数据表主键约束

alter table books drop primary key;
1

创建表之后添加主键约束

//创建表时没有添加主键约束
create table books(
	book_isdn char(4),
	book_name varchar(18) not null,
	book_author varchar(8)
);
//创建表之后添加主键约束
alter table books modify book isdn char(4) primary key;
1
2
3
4
5
6
7
8

主键自动增长
在我们创建一张数据表时，如果数据表中有列可以作为主键（例如：学生表中的学号，图书表中的isdn）我们可以直接将这个列设置为主键。
当有些数据表中没有合适的列作为主键时，我们可以额外定义一个与记录本身无关的列（ID）作为主键，此列数据无具体的含义，主要用于标识一条记录，在mysql中我们可以将此列定义为int，同时是指为自动增长，当我们向数据表中新增一条记录时，无需提供ID列的值，它会自动生成。
定义主键自动增长
定义int类型字段自动增长：auto_increment

create table types(
	type_id int primary key auto_increment,
	type_name varchar(10) not null,
	type_remark varchar(128)
);
1
2
3
4
5

注意：自动增长从1开始，每添加一条记录，自动的增长的列会自动加1，当我们把某条记录删除之后再添加数据，自动增长的数据也不会重置生成（自动增长只保证唯一性，不保证连续性）

联合主键
联合主键是将数据表中的多列组合在一起设置为表的主键
定义联合主键

create table grades(
	stu_num char(9),
	course_id int,
	score int,
	primary key(stu_num, course_id)
);
1
2
3
4
5
6

注意：在实际企业项目数据库的设计中，联合主键使用频率并不高。当一张数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键。

四、DML数据操纵语言

用于完成对数据表的插入、删除、修改操作

create table students(
	stu_num char(8) primary key,
	stu_name varchar(8) not null,
	stu_gender char(2) not null,
	stu_age int not null,
	stu_tel char(11) not null unique,
	stu_qq varchar(11) unique
);
1
2
3
4
5
6
7
8

4.1插入数据

语法

insert into <tableName>(columnName,columnName,...) values(value1,value2,...);
1

示例

//向数据表中指定的列添加数据（不允许为空的列必须提供数据）
insert into stus(stu_num, stu_name,stu_gender,stu_age,stu_tel) values('20210101','张三','男',21,'13030303300');

//数据表名后的字段名列表顺序可以不与表中一致，但是values中值得顺序必须与表名后字段名顺序对应
insert into stus(stu_num,stu_name,stu_age,stu_tel,stu_gender) values('20210103','王五',20,'13030303302','女');

//当要想表中的所有列添加数据时，数据表名后面的字段列表可以省略，但是values中的值的顺序要与数据表定义的字段保持一致
insert into stus values(20210105','孙琦','男',21,'13030303304','666666');

//不过在项目开发中，即使要向所有的列添加数据，也建议将列名的列表显式写出来，增强SQL的稳定性和可读性
insert into stus(stu_num, stu_name,stu_gender,stu_age,stu_tel,stu_qq) values(20210105','孙琦','男',21,'13030303304','666666');
1
2
3
4
5
6
7
8
9
10
11

4.2删除数据

从数据表中删除满足特定条件（所有）的记录
语法

delete from <tableName> [where conditions];
1

示例

//删除学号为20210102的学生信息
delete from stus where stu_name='20210102';

//删除年龄大于20岁的学生信息（如果满足where子句的记录有多条，则删除多条记录）
delete from stus where stu_age>20;

//如果删除语句中没有where子句，则表示删除当前数据表中的所有记录（敏感操作）
delete from stus;
1
2
3
4
5
6
7
8

4.3修改数据

对数据表中已经添加的记录进行修改
语法

update <tableName> set columnName=value [where conditions]
1

示例

//将学号为20210105的学生姓名修改为“孙七” （只修改一列）
update stus set stu_name="孙七" where stu_num='20210105';

//将学号为20210103的学生性别修改为男，同时将QQ修改为777777（修改多列）
update stus set stu_gender='男', stu_qq='777777' where stu_num='20210103';

//根据主键修改其他所有列
update stus set stu_name='韩梅梅', stu_gender='女',stu_age=18,stu_tel='13121213311',stu_qq='999999', where stu_num='20210102';

//如果update语句没有where子句，则表示修改当前表中所有行（记录）
update stus set stu_name='Tom";
1
2
3
4
5
6
7
8
9
10
11

五、DQL数据查询语言

从数据表中提取满足特定条件的记录
单表查询
多表联合查询

5.1查询基础语法

// select 关键字后指定要显示到的记录的哪些列
select colnumName1 [,colnumName2, colnumName3...] from <tableName>;

// 如果要显示查询到的记录的所有列，则可以使用*代替字段名列表（在项目开发中不建议使用*）
select * from <tableName>;
1
2
3
4
5

5.2 where 子句

在删除、修改和查询的语句后都可以添加where子句（条件），用于帅选满足特定条件的数据的删除、修改和查询操作。

delete from <tableName> where conditions;
update <tableName> set ... where conditions;
select ... from <tableName> where conditions;
1
2
3

条件关系运算符

//   = 等于
select * from stus where stu_num = '20210101';

// !=  <> 不等于
select * from stus where stu_num != '20210101';
select * from stus where stu_num <> '20210101';

// > 大于
select * from stus where stu_age>18;

// < 小于
select * from stus where stu_age<20;

// >= 大于等于
select * from stus where stu_age>=20;

// <= 小于等于
select * from stus where stu_age<=20;

// between and 区间查询    between v1 and v2   [v1, v2] 闭区间
select * from stus where stu_age between 18 and 20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

条件逻辑运算符号
在where子句中，可以将多个条件通过逻辑运算符（and or not）进行谅解，通过多个条件来筛选要操作的数据。

// and 并且  筛选多个条件同时满足的记录
select * from stus where stu_gender='女' and stu_age<21;

// or 或者 筛选多个条件中至少满足一个条件的记录
select * from stus where stu_gender='女' or stu_age<21;

// not 取反
select * from stus where stu_age not between 18 and 20;

1
2
3
4
5
6
7
8
9

5.3 LIKE子句

在where子句的条件中，我们可以使用like关键字来实现模糊查询
语法

select * from <tableName> where columnName like 'reg';
1

在like关键字后的reg表达式中
% 表示任意多个字符（包含0个） 【%o% 包含字符o】
_ 表示任意一个字符 【_o% 第二个字母为o】

示例

//查询学生姓名中包含字母o的学生信息
select * from stus where stu_name like '%o%';

//查询学生姓名中第一个字为“张”的学生信息
select * from stus where stu_name like '张%';

//查询学生姓名中最后一个字母为o的学生信息
select * from stus where stu_name like '%o';

//查询学生姓名中第二个字母o的学生信息
select * from stus where stu_name like '_o%';
1
2
3
4
5
6
7
8
9
10
11

5.4对查询结果的处理

设置查询的列
声明显示查询结果的指定列

select colnumName1, colnumName2, ... from <tableName> where conditions;
1

计算列
对从数据表中查询的记录的列进行一定的运算之后显示出来

// 出生年份 = 当前年份 - 年龄
select stu_name, 2021-stu_age from stus;
1
2

字段别名
我们可以为查询结果的列名 去定义一个语义性更强的别名

select stu_name, 2021-stu_age as stu_birth_year from stus;
1

select stu_name as 姓名, 2021-stu_age as 出生年份 from stus;
1

distinct 消除重复行
从查询的结果中将重复的记录消除 distinct

select stu_age from stus;
1

select distinct stu_age from stus;
1

5.5 order by 排序

将查询到的满足条件的及记录按照指定的列的值升序/降序排列
语法

select * from <tableName> where conditions order by conlumn asc|desc;
1

order by columnName 表示将查询结果按照指定的列排序
asc 按照指定的列升序（升序）
desc 按照指定的列降序

示例

//单字段排序
select * from stus where stu_age>15 order by stu_gender desc;
1
2

//多字段排序：先满足第一个排序规则，当第一个排序的列的值相同时再按照第二个列的规则排序
select * from stus where stu_age>15 order by stu_gender desc, stu_age desc;
1
2

5.6 聚合函数
SQL中提供了一些可以对查询的记录的列进行计算的函数——聚合函数
count() 统计函数，统计满足条件的指定字段值的个数（记录数）

// 统计学生表中的学生总数
select count(stu_num) from stus;
1
2

// 统计学生表中性别为男性的学生总数
select count(stu_num) from stus where stu_gender='男';
1
2

max() 计算最大值，查询满足条件的记录中指定列的最大值

select max(stu_age) from stus;
1

select max(stu_age) from stus where stu_gender='女';
1

min() 计算最大值，查询满足条件的记录中指定列的最大值

select min(stu_age) from stus;
1

select min(stu_age) from stus where stu_gender='女';
1

sum() 计算和，查询满足条件的记录中，指定列值的总和

// 计算所有学生年龄的总和
select sum(stu_age) from stus;
1
2

// 计算所有学生年龄的总和
select sum(stu_age) from stus where stu_gender='男';
1
2

sum() 求平均值，查询满足条件的记录中，指定列的平均值

select avg(stu_age) from stus;
1

select avg(stu_age) from stus where stu_gender='男';
1

5.7 日期函数和字符串函数

日期函数
当我们向日期类型的类添加数据时，可以通过字符串类型赋值（字符串的格式必须为yyyy-MM–DD hh:mm:ss），如果我们想要获取当前系统时间时间添加到日期类型的列，可以使用now() 或者 sysdate()
示例

desc stus;
1

// 通过字符串类型，给日期类型的列赋值
insert into stus(stu_num, stu_name, stu_gender, stu_age, stu_tel, stu_qq, stu_enterence) values ('20200108', '张小山', '女', 20, '13434343344', '12311', '2021-09-01 00:00:00');

// 通过now() 获取当前时间
insert into stus(stu_num, stu_name, stu_gender, stu_age, stu_tel, stu_qq, stu_enterence) values ('20200108', '张小山', '女', 20, '13434343344', '12311', now());

//  通过sysdate() 获取当前时间
insert into stus(stu_num, stu_name, stu_gender, stu_age, stu_tel, stu_qq, stu_enterence) values ('20200108', '张小山', '女', 20, '13434343344', '12311', sysdate());

// 通过now 和sysdate获取当前系统时间
select now();
1
2
3
4
5
6
7
8
9
10
11

select sysdate();
1