目录
MYSQL使用总结
MYSQL的基本概念
DB:数据库.是指长期储存在计算机内的,有组织的,可共享的数据集合.
DBS:数据库系统.由数据库,硬件,软件和人员组成,管理的对象是数据.
DBMS:数据库管理系统.是一种操纵和管理数据库的大型软件,用于建立,使用和维护数据库.DBMS通常分三类:关系数据库系统RDBS,面向对象的数据库系统OODBS,对象关系数据库系统ORDBS.
DBA:数据库管理员.
MYSQL是C/S架构.
MYSQL服务的启动与停止
启动服务
net start 服务名
停止服务
net stop 服务名MYSQL服务的登录和退出
登录
mysql [-h主机名 -p端口号] -u用户名 -p密码
[]中的内容表示可选,本机登录可不写
退出
exit
ctrl+cMYSQL常见命令
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(
列名 列类型,
列名 列类型，
...
);
6.查看表结构
describe 表名;
desc 表名;
7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端,即DOS命令
mysql --version
mysql --VSQL的分类
DDL针对数据结构与约束.
DML针对数据.
DCL针对权限.
TCL针对事务控制.
DML数据操纵语言
插入
方式一:
insert into 表名(字段名...) values(值,值,...);
字段名可以省略,默认所有列.
支持一次插入多行,且支持子查询(insert into 表名 查询语句;).
方式二:
insert into 表名 set 字段=值,字段=值,...;修改
修改单表:
update 表名 set 字段=值,字段=值 [where 筛选条件];
修改多表:
update 表名1 别名1
left|right|inner join 表名2 别名2
on 连接条件
set 字段=值,字段=值
[where 筛选条件];删除
方式一:使用delete
删除单表:
delete from 表名 [where 筛选条件] [limit 条目数/起始索引,条目数];
级联删除:
delete 别名1,别名2 from 表1 别名1
inner|left|right join 表2 别名2
on 连接条件
[where 过滤条件];
方式二:使用truncate
truncate table 表名;
两种删除方式的区别
- 自增:truncate删除后,插入新记录,标识列(自增)从1开始.delete删除后,插入新纪录,标识列(自增)从断点开始.
- 回滚,日志,触发器:truncate不可以回滚,不记录日志,不会触发该表的删除触发器.delete可以回滚,记录日志,可以触发该表的删除触发器.
- 筛选条件:delete可以添加筛选条件.truncate不可以添加筛选条件.
- 效率:truncate效率较高.delete相对效率较低
- 返回值:truncate没有返回值.delete可以返回受影响的行数.
DQL数据查询语言
DQL属于DML中的一员.
基础查询
select 查询列表 from 表名;
查询列表可以是字段,常量,表达式,函数.字符+数值,先转换再运算,转换不了变为0再运算.
null+值,结果为null.
条件查询
select 查询列表
from 表名
where 筛选条件;筛选条件的分类:
- 简单条件运算符:<,<=,=,<=>,>,>=,!=,<>.
- 逻辑运算符:&&,and,||,or,!,not.
- 模糊查询:like,搭配通配符使用(%任意多个字符,_任意单个字符)
- 范围查询:between and,in.
- 判断null值:is null/is not null
| 普通类型值 | null值 | 可读性 | |
|---|---|---|---|
| is null | 不支持 | 支持 | 好 |
| <=> | 支持 | 支持 | 差 |
排序查询
select 查询列表
from 表名
where 筛选条件
order by 排序列表 [asc]|desc;分组查询
select 分组函数,分组后的字段
from 表
[where 筛选条件]
group by 分组的字段
[having 分组后的筛选]
[order by 排序列表];| 使用关键字 | 筛选的表 | 位置 | |
|---|---|---|---|
| 分组前筛选 | where | 原始表 | group by的前面 |
| 分组后筛选 | having | 分组后的结果 | group by的后面 |
连接查询
SQL92语法支持的连接方式
- 内连接
- 等值连接
- 非等值连接
- 自连接
SQL99语法支持的连接方式
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全外连接(MYSQL不支持)
- 交叉连接(笛卡尔乘积)
SQL92等值连接:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段];
一般为表起别名
多表的顺序可以调换
n表连接至少需要n-1个连接条件
等值连接的结果是多表的交集部分SQL92非等值连接:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段];SQL92自连接:
select 查询列表
from 表1 别名1,表1 别名2
where 等值的连接条件
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]SQL99内连接
select 查询列表
from 表1 别名
[inner] join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit子句;
表的顺序可以调换
内连接的结果=多表的交集
n表连接至少需要n-1个连接条件SQL99外连接
select 查询列表
from 表1 别名
left|right|full[outer] join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit子句;
查询的结果=主表中的所有行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
left join 左边的是主表
right join 右边的是主表
full join 两边都是主表
一般用于查询除了交集部分的剩余的不匹配的行SQL99交叉连接
select 查询列表
from 表1 别名
cross join 表2 别名;
执行效率慢,不推荐,平时也不用,可用无条件的内连接代替,执行效率快七种JOIN连接
select 查询列表
from 表A 别名A
inner join 表B 别名B
on 别名A.key=别名B.key;
select 查询列表
from 表A 别名A
left join 表B 别名B
on 别名A.key=别名B.key;
select 查询列表
from 表A 别名A
right join 表B 别名B
on 别名A.key=别名B.key;
select 查询列表
from 表A 别名A
left join 表B 别名B
on 别名A.key=别名B.key
where 别名B.key is null;
select 查询列表
from 表A 别名A
right join 表B 别名B
on 别名A.key=别名B.key
where 别名A.key is null;
select 查询列表 from 表A 别名A left join 表B 别名B on 别名A.key=别名B.key
union
select 查询列表 from 表A 别名A right join 表B 别名B on 别名A.key=别名B.key;
select 查询列表 from 表A 别名A left join 表B 别名B on 别名A.key=别名B.key
where 别名B.key is null
union
select 查询列表 from 表A 别名A right join 表B 别名B on 别名A.key=别名B.key
where 别名A.key is null;子查询
子查询的分类:
- 标量子查询/单行子查询:结果集为一行一列.
- 列子查询/多行子查询:结果集为多行一列.
- 行子查询:结果集为一行多列.
- 表子查询:结果集为多行多列.
各位置支持的子查询类型:
- select后面
- 标量子查询.
- from后面
- 表子查询
- where或having后面
- 标量子查询
- 列子查询
- 行子查询
- exists后面
- 标量子查询
- 列子查询
- 行子查询
- 表子查询
分页查询
select 查询列表
from 表
limit 起始角标/页码,条目数量;
公式:
select 查询列表
from 表
limit (page-1)*size,size;
总页数公式:
总页数=(总记录数+条目数-1)/条目数;联合查询
查询语句1
union [all]
查询语句2
union [all]
...;
查询的列需要一致.
列的类型,顺序一致.
union默认去重,union all不去重.查询顺序
书写顺序与大概执行顺序
select 查询列表 7
from 表1 别名 1
连接类型 join 表2 别名 2
on 连接条件 3
where 筛选条件 4
group by 分组列表 5
having 分组后筛选 6
order by 排序列表 8
limit 起始角标,条目数; 9select语句在SQL解析器中的顺序
FROM 左表
ON 连接条件
连接类型 JOIN 右表
WHERE where过滤条件
GROUP BY 分组列表
HAVING having过滤条件
SELECT
DISTINCT 查询列表
ORDER BY 排序列表
LIMIT 分页数
找到左表,选取左表中第一个符合连接条件的行,去右表中找到匹配行,返回左表找第二个行,依次类推(嵌套-循环连接算法).
DDL数据定义语言
库的定义
create database [if not exists] 库名 [character set 字符集名]; 创建库
alter database 库名 character set 字符集名; 修改库
drop database [if exists] 库名; 删除库表的定义
create table [if not exists] 表名(
字段名 字段类型 [约束],
字段名 字段类型 [约束],
...
字段名 字段类型 [约束]
); 创建表
添加列
alter table 表名 add column 列名 类型 [first|after 字段名];
修改列的类型或约束
alter table 表名 modify column 列名 新类型 [新约束];
修改列名
alter table 表名 change column 旧列名 新列名 类型;
删除列
alter table 表名 drop column 列名;
修改表名
alter table 表名 rename [to] 新表名;
删除表
drop table [if exists] 表名;
复制表的结构
create table 表名 like 旧表;
复制表的结构+数据
create table 表名
select 查询列表 from 旧表 [where 筛选条件];DCL数据控制语言
系统管理员,数据库创建者,数据库拥有者,数据库安全管理员关心,程序员不关心.
TCL事务控制语言
事务的特性(ACID)
- 原子性
- 一致性
- 隔离性
- 持久性
隐式提交
隐式提交:隐式地结束当前会话中活动的任何事务,就好像在执行语句之前已经执行了COMMIT一样.
大多数的DDL语言都会导致在执行完语句之前进行隐式提交.即使DDL语句本身执行失败,但是之前的语句依旧会被提交.
显式提交
开启显式提交
set autocommit=0;
start transaction;
insert update delete也会开启事务.
设置回滚点
savepoint 回滚点名;
结束事务
commit 提交
rollback 回滚
rollback to 回滚点名 回滚到指定回滚点并发问题
脏读:事务可以读取未提交的数据.
不可重复读:两次执行同样的查询,可能会得到不一样的结果.
幻读:当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行.
隔离级别
- read uncommitted:读未提交
- read committed:读已提交/不可重复读
- repeatable read:可重复读
- serializable:串行化
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| 读未提交 | 存在 | 存在 | 存在 |
| 读已提交 | 解决 | 存在 | 存在 |
| 可重复读 | 解决 | 解决 | 存在 |
| 串行化 | 解决 | 解决 | 解决 |
数据类型
数值型
如果超如范围,会报out or range警告并插入临界值.
都可以设置无符号或有符号,默认有符号,通过unsigned设置无符号.
- 整型
| tinyint | smallint | mediumint | int/integer | bigint |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 8 |
长度可以不指定,默认会有长度.长度只是用来显示的,存储占用空间大小不变.显示宽度如果不够,左边用0填充,但需要搭配zerofill使用,并且默认变成无符号数.
- 浮点型
- 定点数decimal(M,D) M表示整数部分+小数部分,默认为10 D表示小数部分,默认为0
- 浮点数float(M,D) 4字节/double(M,D) 8字节
字符型
- char定长字符char(M),M可以省略.
- varchar变长字符varchar(M),M不可以省略.
- binary定长二进制串
- varbinary变长二进制串
- enum枚举
- set集合
- text文本字符
- blob二进制大对象
日期型
- year年
- date日期
- time时间
- datetime日期时间8
- timestamp时间戳4
常见的约束
非空约束
- NOT NULL 字段不可为空
唯一约束
- UNIQUE 字段值必须唯一,不可重复
默认约束
- DEFAULT 设置默认值
检查约束
- CHECK MYSQL不支持
主键约束
- PRIMARY KEY 主键=唯一+非空 实体完整性约束
外键约束
- FOREIGN KEY 外键 引用别的表的字段 参照完整性约束 数据的操作约束
设置外键约束后,插入数据要先插入主表(主键被引用的表),删除数据要先删除从表(拥有外键的表).
级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;| 支持类型 | 是否能起约束名 | |
|---|---|---|
| 列级约束 | 除了外键 | 不可以 |
| 表级约束 | 除了非空和默认 | 可以,但对主键无效 |
列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求.
添加非空约束
alter table 表名 modify column 字段名 字段类型 not null;
删除非空约束
alter table 表名 modify column 字段名 字段类型 ;
添加默认约束
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认约束
alter table 表名 modify column 字段名 字段类型 ;
添加主键约束
alter table 表名 add [constraint 约束名] primary key(字段名);
删除主键约束
alter table 表名 drop primary key;
添加唯一约束
alter table 表名 add [constraint 约束名] unique(字段名);
删除唯一约束
alter table 表名 drop index 索引名;
添加外键约束
alter table 表名 add [constraint 约束名] foreign key(字段名) references 主表（被引用列）;
删除外键约束
alter table 表名 drop foreign key 约束名;自增长列
create table 表名(
字段名 字段类型 约束 auto_increment
);
修改表时设置自动增长列
alter table 表 modify column 字段名 约束 auto_increment;
删除自增长列
alter table 表 modify column 字段名 约束;
自动增长从1开始,默认步长为1.
更改起始值,手动插入值.
更改步长,更改系统变量.
set auto_increment_increment=值;
一个表至多有一个自增长列.
自增长列必须为数值型.
自增长列必须为一个key.视图
视图:调用时产生结果集的存储查询.视图充当虚拟表.也就是说数据在执行时动态产生.
创建视图
create view 视图名
as
查询语句;
修改视图
create or replace view 视图名
as
查询语句;
alter view 视图名
as
查询语句;
删除视图
drop view 视图1,视图2,...;
查看视图
desc 视图名;
show create view 视图名;视图一般用于查询,而不是更新,所以包含以下特点的视图都不允许更新:
- 分组函数,group by,distinct,having,union.
- join
- 常量视图.
- where后的子查询用到了from中的表.
- 用到了不可更新的视图.
| 关键字 | 是否占用空间 | 使用 | |
|---|---|---|---|
| 视图 | view | 占用少,只保存SQL逻辑,不保存数据 | 一般用于查询 |
| 表 | table | 占用多,保存实际的数据 | 增删改查 |
视图的两种实现
链接地址:视图的两种实现.
变量
系统变量
系统变量变量分为全局系统变量(需要有super权限)和会话系统变量.
查看系统变量
show [global|session] variable like '';
查看指定的系统变量的值
select @@[global|session].变量名; 无指定则默认为session
为系统变量赋值
set [global|session] 变量名=值;
set @@global.变量名=值;
set 变量名=值;系统变量跨重新启动无效,如要永久有效则需修改配置文件.
自定义变量
用户变量
用户变量作用域:当前连接/会话.
用户变量位置:begin end里面,也可以放在begin end外.
声明并赋值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
更新用户变量值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
select xx into @变量名 from 表;
使用用户变量
select @变量名;局部变量
局部变量作用域:仅仅在begin end中有效.
局部变量位置:只能放在begin end中的第一句.
声明局部变量
declare 变量名 类型 [default 值];
赋值或更新
set 变量名=值;
set 变量名:=值;
select @变量名:=值;
select xx into 变量名 from 表;
使用局部变量
select 变量名;存储过程
存储过程可以封装复杂业务逻辑,减少网络流量.配合call使用.类似于子程序.
存储过程按需编译,编译后放入缓存,每个客户端连接维护自己的缓存,单个连接多次使用存储过程,则使用编译版本.否则和执行查询没什么区别.
声明分隔符
delimiter 分隔符
创建存储过程
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体;
end 分隔符
还原分隔符
delimiter ;
参数模式:in out inout 默认为in,可省略
存储过程体的每一句都需要用分号结尾
调用存储过程
call 存储过程名(实参列表);
查看存储过程
show create procedure 存储过程名;
删除存储过程
drop procedure 存储过程名;
存储过程创建后,存储过程体和存储过程名称就不可以被修改了,只能更改存储过程的特征/信息函数
内置函数
调用方法:select 函数名(实参列表);单行函数
- 字符函数:
- concat连接.
- substr截取子串
- upper变大写
- lower变小写
- replace替换
- length字节长度
- trim前后去空白
- lpad左填充
- rpad右填充
- instr获取子串第一次出现的位置.
- 数学函数:
- ceil向上取整
- round四舍五入
- mod取模
- floor向下取整
- truncate截断
- rand获取0-1之间的随机小数
- 日期函数:
- now返回当前日期+时间
- curdate返回当前日期.
- curtime返回当前时间
- year返回年
- month返回月
- monthname以英文形式返回月
- day返回日
- hour小时
- minute分
- second秒
- date_format将日期转换为字符.
- str_to_date将字符转换为日期.
- datediff返回两个日期相差的天数.
- 其他函数:
- ifnull(字段名,0) 字段为null则用0替代,不为null则用原来的值.
- isnull(表达式/字段) 判断是否为null,1或0.
- version当前数据库服务器的版本.
- database当前打开的数据库.
- user当前用户.
- password('字符')返回该字符的密码形式.
- md5('字符')返回该字符的MD5加密形式.
- 流程控制函数:
if(条件表达式,表达式1,表达式2) 如果条件成立,返回表达式1,否则返回表达式2分组函数/聚合函数/统计函数/组函数
- max最大,忽略null,可搭配distinct实现去重统计
- min最小,忽略null,可搭配distinct实现去重统计
- sum求和,处理数值,忽略null,可搭配distinct实现去重统计
- avg求平均值,处理数值,忽略null,可搭配distinct实现去重统计
- count计数,忽略null,可搭配distinct实现去重统计.count(字段)统计字段非空值的个数,count(*)统计结果集的个数.
和分组函数一同查询的字段,要求是group by后出现的字段.
where字句中不能使用分组函数.
自定义函数
函数可以封装功能逻辑,可以单独调用.类似于函数.
声明分隔符
delimiter 分隔符
创建函数
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体;
return xx;
end 分隔符
还原分隔符
delimiter ;
调用函数
select 函数名(实参列表);
查看函数
show create function 函数名;
删除函数
drop function 函数名;
函数创建后,函数体和函数名称就不可以被修改了,只能更改函数的特征/信息存储过程和函数的区别
- 单独调用:存储过程不可以单独调用,需要使用call,函数可以单独调用,可以写在SQL中.
- 封装内容:存储过程通常封装业务逻辑,函数通常封装功能逻辑.
- 参数模式:存储过程有参数模式,函数没有参数模式.
- 返回值:存储过程可以返回多个返回值,函数有且只有一个返回值.
- 互相调用:存储过程可以调用函数,函数不可以调用存储过程.
- 递归调用:存储过程中可以调用存储过程,但不支持递归,函数可以调用函数,且支持递归.
流程控制结构
顺序结构
略.哈哈
分支结构
IF结构
类似于Java中的IF结构.
IF函数
if(条件表达式,表达式1,表达式2) 如果条件成立,返回表达式1,否则返回表达式2
可以作为表达式放在任何位置IF结构
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;
只能放在begin end中CASE结构
可以放在任何位置.
如果放在begin end 外面,作为表达式结合着其他语句使用.
如果放在begin end 里面,一般作为独立的语句使用.
类似于Java中的IF ELSE结构.
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end [case];类似于Java中的SWITCH结构
case 变量/表达式/字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end [case];循环结构
只能放在begin end中.
WHILE循环
先判断后执行.
[名称:]while 循环条件 do
循环体
end while [名称];REPEAT循环
先执行,后判断.
[名称:]repeat
循环体
until 结束条件
end repeat [名称];LOOP循环
死循环.
[名称:]loop
循环体
end loop [名称];循环控制语句
- LEAVE
leave类似于Java语言中的break语句,跳出循环.
- ITERATE
iterate类似于Java语言中的continue语句,跳过本次循环,继续执行下一次.
索引
创建索引
create [unique] index 索引名 on 表名(列名(长度));
alter 表名 add [unique] index 索引名 on(列名(长度));删除索引
drop index 索引名 on 表名;查看索引
show index from 表名;explain执行计划
基本语法
explain SQL语句;
8031
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
