Neekko33

数据库操作基础#

1 - 数据库的操作#

1.1 显示数据库#

show databases;

1.2 创建数据库#

create database [if not exists] `数据库名` [字符编码];

注意：

1. 如果创建的数据库已存在会报错，所以创建数据库时需要先判断是否存在；
2. 如果数据库名是关键字和特殊字符会报错，需要在特殊字符、关键字行加上反引号；

1.3 删除数据库#

drop database [if exists] 数据库名;

1.4 显示创建数据库的 SQL 语句#

show create database 数据库名;

1.5 修改数据库#

修改数据库的字符编码：

alter database 数据库名 charset=字符编码;

1.6 选择数据库#

use 数据库名;

2 - 表的操作#

2.1 显示所有表#

show tables;

2.2 创建表#

create table [if not exists] 表名(
	字段名 数据类型 [null|not null] [auto_increment] [primary key] [comment]
  字段名 数据类型 [default]
)engine=存储引擎

创建简单的表：

create table stu(
	id int,
  name varchar(30)
);

创建复杂的表：

# 如果带有中文
set names gbk;
# 创建复杂表
create table if not exists teacher(
		id int auto_increment primary key comment '主键',
  	name varchar(20) not null comment '姓名',
  	phone varchar(20) comment '电话号码',
  	address varchar(100) default '地址不详' comment '地址'
)engine=innodb;

提示： 使用数据库名.表名可以给其他数据库创建表

2.3 显示创建表的语句#

show create table 表名 [\G];

2.4 查看表结构#

desc[ribe] 表名;

2.5 删除表#

drop table [if exists] 表1,表2,...;

2.6 修改表#

alter table 表名

1. 添加字段

alter table 表名 add [column] 字段名 数据类型 [位置];

位置：

• first：在第一位添加
• after：在 xx 字段后添加

2. 删除字段

alter table 表名 drop [column] 字段名;

3. 修改字段（名字和类型）

alter table 表名 change 字段名 新字段名 数据类型...

4. 修改字段（不改名）

alter table 表名 modify 字段名 字段属性...

5. 修改引擎

alter table 表名 engine=引擎名称;

6. 修改表名

alter table 表名 rename to 新表名;

2.7 复制表#

create table 表名 select 字段 from 旧表

特点：不能复制父表的主键，只能复制父表的数据

create table 新表 like 旧表

特点：只能复制表结构，不能复制表数据

3 - 数据操作#

3.1 插入数据#

插入单条数据

insert into 表名 (字段1,字段2,...) values (值1,值2,...);

1. 可以插入部分字段，但是非空字段必须插入
2. 自动增长字段不用插入，数据库会自动插入增长的数字
3. 自动增长列的值插入 null 即可
4. 插入值的顺序和个数与表字段的顺序和个数一致，插入的字段可以省略
5. 可以通过 default 插入默认值

插入多条数据

insert into 表名 values (值1,值2,...),(值1,值2,...),...;

3.2 更新数据#

update 表名 set 字段=值 [where 条件]

3.3 删除数据#

delete from 表名 [where 条件]

3.4 清空表#

truncate table 表名

**delete from 表**和**truncate table 表**区别：
delete from 表：遍历表记录，一条一条的删除；
truncate table 表：将原表摧毁，再创建一个同结构的新表，就清空表而言，这种方法效率高；

3.5 查询语句（单表查询）#

语法：select [选项] 列名 [form 表名] [where 条件] [order by 排序] [group by 分组] [having 条件][limit 限制]

3.5.1 字段表达式#

可以通过as来取别名（as 可以省略）

3.5.2 from 语句#

from 后跟数据源，数据源可以放多个，返回笛卡尔积

3.5.3 dual 表#

dual 表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证 select 语句的完整又必须要一个表名，这时候就使用伪表。

3.5.4 where 子句#

MySQL 支持的运算符：>, <, >=, <=, =, !=, and, or, not
Where 后面跟的是条件语句，返回条件为真的记录

3.5.5 in|not in#

多个条件查询可以通过 in 语句 实现：

select * from stu where stuadress in ('北京','上海');

3.5.6 between...and|not between...and#

查找某个范围的记录：

select * from stu where stuage between 18 and 20;

3.5.7 is null|is not null#

判断是否为空：

select * from stu where ch is null;

3.5.8 聚合函数#

1. sum()：求和
2. avg()：求平均数
3. max()：求最大值
4. min()：求最小值
5. count()：记录值

3.5.9 通配符#

1. _ [下划线] 表示任意一个字符
2. % 表示任意字符

3.5.10 模糊查询（like）#

在学生表中查询姓张的学生：

select * from stu where stuname like '张_'

3.5.11 order by 排序#

• ASC：升序【默认】
• DESC：降序
• 多列排序：

# 年龄升序，成绩降序
select *,(ch+math) as '总分' from stu order by stuage asc,(ch+math) desc;

3.5.12 group by [分组查询]#

将查询的结果分组，分组查询目的在于统计数据

# 按照性别分组，显示每组的平均年龄
select avg(stuage) as '平均年龄',stusex from stu group by stusex;

注意：

1. 如果是分组查询，查询字段必须是分组字段和聚合函数；
2. 查询字段是普通字段，只取分组的第一个值；
3. 可以通过group_concat()函数将同一组的值连接起来显示
4. 分组以后结构默认按照升序排列，可以使用 desc 实现降序排列（直接加 desc）

多列分组：

# 按照性别和地区分组，显示每组的平均年龄
select stuaddress,stusex,avg(stuage) as '平均年龄'

3.5.13 having 条件#

where 和 having 的区别：
where 是对原始数据进行筛选，having 是对结果集进行筛选

3.5.14 limit#

语法：limit 起始位置，显示长度
注意：

1. 起始位置可以不写，默认从 0 开始
2. limit 可以在 update 和 delete 语句中使用

3.5.15 查询语句中的选项#

查询语句中的选项有两个：

1. all：显示所有数据【默认】
2. distinct：去除结果集中重复的数据

3.6 查询语句（多表查询）#

3.6.1 内连接【inner join】#

# 语法一：
select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段;
# 语法二：
select 列名 from 表1，表2 where 表1.公共字段=表2.公共字段;

注意：如果要显示公共字段，必须要指定表名

3.6.2 左外连接 【left join】#

以左边的表为标准，如果右边的表没有对应的记录，就以 null 填充结果

select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段;

3.6.3 右外连接 【right join】#

以右边的表为标准，如果左边的表没有对应的记录，就以 null 填充结果

select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段;

3.6.4 交叉连接 【cross join】#

1. 如果没有连接表达式返回的是笛卡尔积
2. 如果有连接表达式等价于内连接

3.6.5 自然连接 【natural】#

自动的判断连接条件，它是通过同名字段来判断
自然连接分为：

1. 自然内连接：natural join
2. 自然左外连接：natural left join
3. 自然右外连接：natural right join

自然连接结论：

1. 表连接通过同名的字段来连接
2. 如果没有同名的字段返回笛卡尔积
3. 会对结果进行整理，整理的规则如下
   • 连接字段保留一个
   • 连接字段放在最前面

3.6.6 using()#

1. 同来指定连接字段
2. using()也会对连接字段进行整理，整理规则与自然连接相同

3.7 子查询#

3.7.1 只有一个返回值#

语法：

select 语句 where 条件 (select ... from 表)

1. 外面的查询称为父查询，括号中的查询称为子查询
2. 子查询为父查询提供查询条件
3. =只适用于子查询返回一个值的情况

3.7.2 in|not in 子查询#

用于子查询返回多个值的情况

3.7.3 exists|not exists#

# 如果有学生笔试成绩大于80就显示所有学生
select * from stuinfo where exists(select * from stumarks where writtenexam>=80);

3.7.4 子查询分类#

1. 标量子查询：子查询返回的结果就一个
2. 列子查询：子查询返回的结果是一个列表
3. 行子查询：子查询返回的结果返回一行

• 例题：查询成绩最高的男生和女生

select stuname,stusex,ch from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);

4. 表子查询：子查询返回的结果当成一个表

• 注意：如果把子查询当成表来看待，必须取一个别名

3.8 union#

3.8.1 union 的使用#

作用：将多个 select 语句结果集纵向联合起来

select 语句 union [选项] select 语句 union [选项] select 语句

3.8.2 union 的选项#

1. all：显示所有数据
2. distinct：去除重复的数据【默认】

3.8.3 union 的注意事项#

1. union 两边的 select 语句的字段个数必须一致
2. union 两边的 select 语句的字段名可以不一致，最终按第一个 select 语句的字段名
3. union 两边的 select 语句中的数据类型可以不一致

4 - SQL 分类#

DDL（Data Definition Language）数据库定义语言 CREATE、ALTER、DROP
DML（Data Manipulation Language）数据操纵语言 SELECT、UPDATE、INSERT、DELETE
DCL（Data Control Language）数据库控制语言，是用来设置或更改数据库用户或角色权限的语句

5 - 数据表的文件介绍#

• 一个数据库对应一个文件夹
• 一个表对应一个或多个文件
  • myisam：一个表对应三个文件（表结构，表数据，表索引）
  • innodb：一个表对应一个表结构文件

6 - 字符集#

字符集：字符在保存和传输时对应的二进制编码合集。

set names 字符集编码

列属性与数据完整性#

1 - 数据类型#

1.1 数据类型 —— 值类型#

1.1.1 整形

注意：

1. 无符号整数（unsigned）没有负数，整数部分是有符号的两倍
2. 整型支持显示宽度（最小显示位数），比如int(5)，如果数值的位数小于 5 位，前面加上前导 0.（结合zerofill才起作用）

1.1.2 浮点型（保存近似值小数)

注意：

1. 浮点数声明：float(M,D) double(M,D)
   • M：总位数
   • D：小数位数
2. 浮点的精度可能会丢失

1.1.3 定点数
语法：decimal(M, D)
注意：

1. 定点数是变长的，大致每 9 个数字用 4 个字节来存储。定点数之所以能保存精确的小数，是因为整数和小数是分开存储的，占用的资源比浮点数要多。
2. 定点数和浮点数都支持显示宽度和无符号数。

1.2 数据类型 —— 字符型#

注意：

1. char(10)和varchar(10)的区别？

   相同点：它们最多只能保存 10 个字符；
   不同点：char 不回收多余的字符，varchar 会回收多余的字符；char 效率高，浪费空间，varchar 节省空间，效率比 char 低。

2. char 最大长度 255

3. varchar 理论长度 65535

1.3 数据类型 —— 枚举（enum）#

特点：

1. MySQL 的枚举类型是通过整数来管理的，第一个值是 1，第二个值是 2，以此类推；
2. 枚举类型可以直接插入数字

优点：

1. 运行速度快（数字比字符串运算快）
2. 限制数据，保证数据完整性
3. 节省空间

1.4 数据类型 —— 集合（set）#

1. 集合中插入顺序不一样，但是显示的顺序都是一样的
2. 不能插入集合中没有的数据
3. 集合的每个元素都被分配一个固定数字，从左往右按 20, 21,22... 排列

1.5 数据类型 —— 日期时间#

1.6 数据类型 ——boolean#

true 和 false 在数据库中对应 1 和 0

数据库设计#

1 - 数据库基本概念#

1. 关系：两个表的公共字段
2. 行：也称记录，也称实体
3. 列：也称字段，也称属性
4. 数据冗余：相同的数据存储在不同的地方
5. 数据完整性：正确性 + 准确性 = 数据完整性
   • 正确性：数据类型正确
   • 准确性：数据范围要准确

2 - 实体和实体之间的关系#

2.1 一对一#

1. 主表中的一条记录对应从表中的一条记录
2. 实现：主键和主键建立关系
3. 问题：一对一两个表完全可以用一个表实现，为什么要分成两个表？
   • 在字段数量很多的情况下，每次查询需要检索大量数据，这样效率低下。所以可以将所有字段分为 “常用字段” 和 “不常用字段”，这样对大部分查询者来说效率提高了。【表的垂直分割】

2.2 一对多#

1. 主表中的一条记录对应的从表中的多条记录
2. 实现：主键和非主键建立关系

2.3 多对多#

1. 主表中的一条记录对应从表中的多条记录，从表中的一条记录对应主表中的多条记录
2. 实现：建立第三张表来保存关系

3 - 数据库设计的步骤#

3.1 数据库设计具体步骤#

1. 收集信息：与该系统有关人员进行交流、座谈，充分理解数据库需要完成的任务
2. 标识对象（实体 - Entity）标识数据库要管理的关键对象或实体
3. 标识每个实体的属性（Attribute）
4. 标识对象之间的关系（Relationship）
5. 将模型转换成数据库
6. 规范化

3.2 绘制 E-R 图（实体关系图）#

E-R 图的语法

3.3 将 E-R 图转成表#

4 - 数据规范化#

实践证明，三范式是性价比最高的。

4.1 第一范式：确保每列原子性#

如果每列都是不可再分的最小数据单元（也称为最小的原子单元），那么满足第一范式。
注意：地址包含省、市、县、地区是否需要拆分？

• 如果仅仅起地址的作用，不需要统计，可以不拆分；如果有按地区统计的功能，那么需要拆分。
• 在实际项目中，建议拆分

4.2 第二范式：非键字段必须依赖于键字段#

如果一个关系满足第一范式，并且除了主键意外的其他列，都依赖于该主键，则满足第二范式。
注意：第二范式要求每个表只描述一件事情

4.3 第三范式：消除传递依赖#

如果一个关系满足第二范式，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式。
注意：传递依赖不能存在于非键字段中

视图#

1. 视图是一张虚拟表，他表示一张表的部分或多张表的综合的结构
2. 视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。

1 创建视图#

语法：

create [or replace] view 视图的名称 as select语句

注意：因为视图是一个表结构，所以创建视图后，会在数据库文件夹中创建一个视图名的 frm 文件

2 查看视图的结构#

desc 视图名;

3 查看创建视图的语法#

show create view 视图名\G;

4 显示所有视图#

show tables;

select table_name from information_schema.views;

show table status where comment='view'\G;

5 更改视图#

alter view 视图名 as select语句;

6 删除视图#

drop view [if exists] 视图1,视图2...;

7 视图的作用#

1. 筛选数据，防止未经许可访问敏感数据
2. 隐藏表结构
3. 降低 SQL 语句的复杂度

8 视图的算法#

场景：找出语文成绩最高的男生和女生

select * from (select * from stu order by ch desc)as t group by stusex;

我们可以将子查询封装到视图中：

create view vw_stu as select * from stu order by ch desc;

通过视图来查询：

select * from vm_stu group by stusex;

但是结果和上面直接查询的结果不一样，这是因为视图的算法造成的：

1. merge：合并算法，将视图的语句和外层的语句合并后在执行。
2. temptable：临时表算法，将视图生成一个临时表，再执行外层语句。
3. undefined：未定义，MySQL 到底用哪种算法由 MySQL 决定，一般视图会自动使用 merge 算法，因为效率更高。

** 解决：** 在创建视图的时候指定算法

create algorithm=temptable view 视图名 as select语句;

事务【transaction】#

1. 事务是一个不可分割的执行单元
2. 事务作为一个整体要么一起执行，要么一起回滚

1 事务操作#

# 开启事务
start transaction 或者 begin [work]
# 提交事务
commit
# 回滚事务
rollback

例子：银行转帐

start transaction
update bank set money=money-100 where cardid='1001'
update bank set money=money+100 where cardid='1002';
# 如果有报错：
rollback;
# 如果成功：
commit;

注意：

1. 事务在开启的时候产生，提交事务或回滚事务都结束；
2. 只有 innodb 和 BDB 才支持事务，myisam 不支持事务；

2 设置事务的回滚点#

语法：

# 设置回滚点
savepoint 回滚点名
# 回滚到回滚点
rollback to 回滚点

2.3 事务的特性（ACID）#

1. 原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行；
2. 一致性（Consistency）：事务完成时，数据必须处于一致的状态；
3. 隔离性（Isolation）：每个事务都是相互隔离的；
4. 永久性（Durability）：事务完成后，对数据的修改是永久性的；

索引#

优点：查询速度快
缺点：

1. 数据操作语句效率降低
2. 占用空间

1 索引的类型#

1. 普通索引
2. 唯一索引（唯一键）
3. 主键索引：只要创建主键就自动创建主键索引，不需要手动创建
4. 全文索引，搜索引擎使用，MySQL 不支持中文的全文索引，我们通过 sphinx 解决中文的全文索引

2 创建普通索引#

# 创建索引方法一
create index 索引名 on 表名(字段名);
# 创建索引方法二
alter table 表名 add index [索引的名称](列名);
# 创建表的时候就添加索引
create table emp(
	id int,
    name varchar(10),
    index ix_name(name) # 创建索引
);

3 创建唯一索引#

语法一：create unique index 索引名 on 表名(字段名);
语法二：alter table 表名 add unique [index] [索引的名称](列名);
语法三：创建表的时候添加唯一索引，和创建唯一键是一样的

4 删除索引#

drop index 索引名 on 表名;

5 创建索引的指导原则#

1. 该列用于频繁搜索；
2. 该列用于排序；
3. 公共字段要创建索引；
4. 如果表中的数据很少，不需要创建索引。MySQL 搜索索引的时间比逐条搜索数据的时间要长；
5. 如果一个字段上的数据只有一个不同的值，该字段不适合做索引，比如性别；

内置函数#

1 数字类#

# 生成随机数
select rand();
select * from stu order by rand() limit 2; # 随机抽取两个学生

# 取整（四舍五入，向上，向下）
select round(数字);
select ceil(数字);
select floor(数字);

# 截取数字
select truncate(数字，截取小数位数);

2 字符串类#

# 转成大写/小写
select ucase/lcase(字符串);

# 截取字符串
select left/right(字符串,位数);
select substring(字符串，开始位数【从1开始】，位数);

# 字符串拼接
select concat(字符串...);

# 字符串替换
coalesce(字段1，字段2); # 如果字段不为空就显示字段1，否则显示字段2
select stuname,coalesce(writtenexam.'缺考') from stuinfo natural left join stumarks; # 如果笔试成绩为空就显示‘缺考’

# 字符串长度
select length(字符串); # 字节长度
select char_length(字符串); # 字符个数

3 时间类#

# 获取当前的时间戳
select unix_timestamp();

# 时间戳转换为时间格式
select from_unixtime(unix_timestamp());

# 获取当前时间
select now();

# 日期相减
select datediff(now(),'2008-8-8') # 当前日期距离2008-08-08共多少天

# 转换格式
select cast(now() as time)
select convert(now(),time)

4 加密函数#

select md5(字符串);
select sha(字符串);

5 判断函数#

select stuname,if(ch>=60 && math>=60,'通过','不通过') '是否通过' from stu;

预处理#

预编译一次，可以多次执行。用来解决一条 SQL 语句频繁执行的问题

# 预处理语句
prepare 预处理名字 from 预处理语句;

# 执行预处理
execute 预处理名字 [using 变量];

# MySQL中声明变量
set @id=值

存储过程【procedure】#

1 存储过程的优点#

1. 存储过程可以减少网络流量
2. 允许模块化设计
3. 支持事务

2 创建存储过程#

create procedure 存储过程名(参数)
	begin
	sql...
	end;

3 调用存储过程#

call 存储过程名

4 删除存储过程#

drop procedure [if exists] 存储过程名

5 查看存储过程的信息#

show create procedure 存储过程名\G

6 显示所有的存储过程#

show procedure status\G

7 存储过程的参数#

存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout）
存储过程不能使用 return 返回值，要返回值只能通过 “输出参数” 来向外传递值。
例 1：输入参数

create procedure proc(in param varchar(10))
select * from stuinfo where stuno=param;

call proc('s25301');

例 2：查找同桌

create procedure proc(in name varchar(10))
begin
declare seat tinyint
select stuseat into seat from stuinfo where stuname=name
select * from stuinfo where stuseat=seat+1 or stuseat=seat-1
end;

总结：

1. 通过 declare 关键字声明局部变量；全局变量 @开头就可以了
2. 给变量赋值有两种方法
   方法一：set 变量名 = 值
   方法二：select 字段 into 变量 from 表 where 条件
3. 声明的变量不能与列名同名

例三：输出参数

create procedure proc(num int, out result int)
begin
set result=num*num
end;

call proc(10.@result);

select @result;

例四：输入输出参数

create procedure proc(inout num int)
begin
set num=num*num
end;

set @num=10

call proc(@num);

select @num;