未更新完个人笔记
MySQL
MySQL基础命令
数据类型
数字类型
| 类型 | 大小 | 无符号范围（UNSIGNED） | 描述 |
|---|---|---|---|
| TINYINT | 1 | TINYINT UNSIGNED（0，255） | 小整数值 |
| SMALINT | 2 | （0，65535） | 大整数值 |
| MEDIUMINT | 3 | （0，16777215） | 大整数值 |
| INT | 4 | （0，429494967295） | 大整数值 |
| BIGINT | 8 | （0，2^64-1） | 极大整数值 |
| FLOAT | 4 | 0和（1.75494351，255E-38,1.7976931348623157) | 单精度浮点数值 |
| DOUBLE | 8 | 0和(2.2250738585072014 E-308,1.7976931348623157 ) | 双精度浮点数值 |
| DECIMAL | 依赖精度和D的标准度 | 小数值（精确点数） |
字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255bytes | 定长字符串 |
| VARCHAR | 0-65535bytes | 变长字符串 |
| TINYBLOB | 0-255bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255bytes | 短文本字符串 |
| BLOB | 0-6535bytes | 二进制形式长文本数据 |
| TEXT | 0-6535bytes | 长文本数据 |
| MEDIUMBLOB | 0-1677215bytes | 二进制中等长度文本数据 |
| MEDIUMTEXT | 0-1677215bytes | 中等长度文本数据 |
| LONGBLOB | 0-294967295bytes | 二进制极大文本数据 |
| LONGTEXT | 0-294967295bytes | 极大文本数据 |
日期类型
| 类型 | 大小 | 格式 | 描述 |
|---|---|---|---|
| DATE | 3 | YYYY-MM-DD | 日期值 |
| TIME | 3 | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | YYYY | 年份值 |
| DATETIME | 8 | YYYY-MM-DD HH:MM:SS | 混合日期时间值 |
| TIMESTAMP | 4 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
MySQL服务启动和关闭
启动
net start mysql80
关闭
net stop mysql80
cmd命令里打开MySQL里面清空屏幕
system cls
DDL语句
数据库操作
-
查询所有数据库
SHOW DATABASES; -
查询当前数据库
SELECT DATABASE(); -
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则]; -
删除数据库
DROP DATABASE [IF EXISTS]数据库名; -
使用数据库
USE 数据库名;
表查询
-
查询当前数据库所有表
SHOW TABLES; -
查询表结构
DESC 表名; -
查询指定表的建表语句
SHOW CREATE TABLE 表名;
表的创建
CREATE TABLE 表名（
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
……
字段3 字段3类型[COMMENT 字段3注释],
）[COMMENT 表注释];
表结构的修改
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT '注释'][约束]; -
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型; -
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT '注释'][约束]; -
删除字段
ALTER TABLE 表名 DROP 字段名; -
修改表名
ALTER TABLE 表名 RENAME TO 新表名; -
删除表
DROP TABLE [IF EXISTS] 表名; -
删除指定表，并创建该表（相当于删除表的所有数据，只剩表结构）
TRUNCATE TABLE 表名;
DML语句
DML语句数据操作语言，用来对数据库中表的数据记录进行增删改操作。
添加数据（INSERT）
-
给指定字段添加数据
INSERT INTO 表名（字段1，字段2，……） VALUES（值1，值2，……）; -
给全部字段添加数据
INSERT INTO 表名 VALUES（值1，值2，……）; -
批量添加数据
INSERT INTO 表名（字段1，字段2，……） VALUES（值1，值2，……）,（值1，值2，……）;INSERT INTO 表名 VALUES（值1，值2，……）,（值1，值2，……）,（值1，值2，……）;注意：
- 插入数据时，指定字段顺序要与值是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入数据大小，应该在字段的规定范围内。
修改数据（UPDATE）
UPDATE 表名 SET 字段名1=值1，字段名2=值2，……[WHERE 条件];
注意：修改语句的条件可以有，也可以没有，如果没有就是修改整张表的所有数据。
删除数据（DELETE）
DELETE FROM 表名 [WHERE 条件];
注意：如果不加条件就是删除整张表中的所有数据。
DQL语句
DQL语句是数据查询语言，数据查询语言，用来查询数据库表中的记录。
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
基础查询
-
查询多个字段
SELECT 字段1，字段2…… FROM 表名;SELECT * FROM 表名; -
设置别名
SELECT 字段1[AS 别名1]，字段2[AS 别名2]…… FROM 表名; -
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名
条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件：
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或!= | 不等于 |
| BETWEEN……AMD…… | 在某个范围内（含最小值，最大值） |
| IN(……) | 在in之后的列表中的值，多选一 |
| LIKE 占位符 | 模糊匹配 ，_ 匹配单个字符，%匹配任意个字符 |
| IS NULL | 是NULL |
| 逻辑与算符 | 功能 |
|---|---|
| AND 或 && | 并且，多个条件同时成立 |
| OR 或 || | 或者，多个条件任意一个成立 |
| NOT 或 ！ | 非，不是 |
聚合函数
将一列数据作为一个整体，进行纵向计算。
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
语法：
所有的聚合函数null值不参与运算。
SELECT 聚合函数(字段)FROM 表名
分组查询
语法：
SELECT 字段名 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
WHERE与HAVING的区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组，having是分组之后对结果进行过滤。
- 判断条件：where不能对聚合函数进行判断，而having可以。
排序查询
语法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;
排序方式：如果第一个排序方式相同，就按照第二个方式。
-
ASC ：升序，默认的排序
-
DESC ：降序
分页查询
语法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;
注意：
- 起始索引从0开始，起始索引 = （查询页码-1）* 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL是limit
- 如果查询的是第一页数据，起始索引可以省略。
执行顺序：
FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT
DCL
用来管理数据库用户，控制数据库的访问权限。
管理用户
-
查询用户
USE MYSQL;SELECT * FROM USER; -
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; -
修改用户
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'; -
删除用户
DROP USER '用户名'@'主机名';
注意：主机名可以使用通配符 %
权限控制
| 权限 | 说明 |
|---|---|
| all，all privileges | 所有权限 |
| select | 查询数据 |
| insert | 插入数据 |
| update | 修改数据 |
| delete | 删除数据 |
| alter | 修改表 |
| drop | 删除数据库、表、视图 |
| create | 创建数据库、表 |
-
查询权限
show grants for '用户名'@‘主机名'; -
授予权限
grants 权限列表 on 数据库.表名 to '用户名'@‘主机名'; -
撤销权限
revoke 权限列表 on 数据库.表名 for '用户名'@‘主机名';
注意：
- 多个权限之间用逗号分隔。
- 授权是，数据库名和表名可以使用*进行通配，代表所有。
函数
字符串函数
| 函数 | 功能 |
|---|---|
| concat(s1,s2,……) | 字符串拼接将s1,s2,……拼接成一个字符串 |
| lower(str) | 将字符串全部转为小写 |
| upper(str) | 将字符串全部转为大写 |
| lpad(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| prad(str,n,pad) | 右填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| trim(str) | 去掉字符串头部和尾部的空格 |
| substring(str,start,len) | 返回从字符串str从start位置起的len个长度字符串 |
数值函数
| 函数 | 功能 |
|---|---|
| ceil | 向上取整 |
| floor(x) | 向下取整 |
| mod(x,y) | 返回x/y的模 |
| rand() | 返回0-1的随机数 |
| round(x,y) | 求参数x的四舍五入，y是要保留几位小数 |
日期函数
| 函数 | 功能 |
|---|---|
| curdate | 返回当前日期 |
| curtime | 返回当前时间 |
| now | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| month(date) | 获得指定date的月份 |
| day(date) | 获得指定date的日期 |
| date_add(date,interval expr type) | 返回一个日期，时间值加上一个时间间隔expr的时间值，tyep可以是day或者month、year |
| datediff(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
| 函数 | 功能 |
|---|---|
| if(value,t,f) | 如果value为true，则返回t，否则返回f |
| ifnull(value1,value2) | 如果value不为空，返回value1，否则返回value2 |
| case when [val1] then [res1] ……else [default] end | 如果value为true，返回res1，……否则返回default |
| case [expr] where [val1] then[res1] ……else [default] end | 如果expr的值等于val1，返回res1，……否则返回default |
约束
概念：约束时作用与表中字段上的规则，用于限制存储在表中的数据。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | not null |
| 唯一约束 | 保证该字段的所有数都是唯一、不重复 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | default |
| 检查约束 | 保证字段值满足某一个条件 | check |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | foreign key |
primary key auto_increment 如果该字段是数字，会自动增长。
添加约束
一、 创建表的时候进行添加
create table 表名(
字段1 字段类型 约束类型
)
二、已经创建好表后进行追加
-
添加主键约束
alter table 表名 modify column 字段 字段类型 primary key -
添加非空约束
alter table 表名 modify column 字段 字段类型 not null -
添加默认约束
alter table 表名 modify column 字段 字段类型 default 默认值 -
添加唯一约束
alter table 表名 modify column 字段 字段类型 unique
删除约束
-
删除唯一约束
alter table 表名 drop index 字段 -
删除主键约束
alter table 表名 drop primary key -
删除非空约束
alter table 表名 modify column 字段名 字段类型 null -
删除默认约束
alter table 表名 modify column 字段 字段类型
外键约束
添加外键语法：
- 建表时添加
create table 表名(
字段名 字段类型 …… [constraint] 外键名称 foreign key (外键字段) references 主表 (主表列名)
)
-
已经建好表追加
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表 (主表列名) -
删除外键
alter table 表名 drop foreign key 外键名称
多表查询
内连接
内连接：相当于查询 A、B交集部分的数据。
1. 隐式内连接
select 字段列表 from 表1,表2 where 条件……
- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件……
外连接
-
左外连接：查询左表所有数据，以及两张表的交集部分数据。
select 字段列表 from 表1 left outer join 表2 on 连接条件…… -
右外连接：查询右表所有数据，以及两张表的交集部分数据。
select 字段列表 from 表1 right outer join 表2 on 连接条件……
自连接
自连接：当前表与自身的连接查询，自连接必须使用别名。
select 字段列表 from 表a as 表a别名 [内外连接都行] [inner] join 表a as 别名b on 条件
联合查询
对于union 查询，就是把多次查询的结果并起来，形成一个新的查询结果集。
select 字段列表 from 表a……;
union [all] //这个all如果省略不写两条如果有重复的就只会显示一条
select 字段列表 from 表b……;
-
对于联合查询的多张表的列数必须保存一致，字段类型也需要保持一致。
-
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。
子查询
1. 概念：SQL语句中嵌套的select语句，称为嵌套查询，又称子查询。
select * from t1 where column1=(select coulumn1 from t2);
子查询外部的语句可以是insert、update、delete、select的任何一个。
- 根据子查询结果不同，分为几种：
- 标量子查询（子查询结果为单个值）
- 列子查询（子查询结果为一列）
- 行字查询（子查询结果为一行）
- 表子查询（子查询结果为多行多列）
- 根据子查询出现的位置分为：where之后、from之后、select之后。
事务
事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于物理当中的原子（一种不可分割的最小单位）。
往通俗的讲就是，事务就是一个整体，里面的内容要么都执行成功，要么都不成功。不可能存在部分执行成功而部分执行不成功的情况。
就是说如果单元中某条sql语句一旦执行失败或者产生错误，那么整个单元将会回滚(返回最初状态)。所有受到影响的数据将返回到事务开始之前的状态，但是如果单元中的所有sql语句都执行成功的话，那么该事务也就被顺利执行。其中innodb支持事务(transaction)，而myisam，memory等不支持事务。
事务的四个特性(ACID)
-
原子性（Atomicity）：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况
-
一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举一个栗子，李二给王五转账50元，其事务就是让李二账户上减去50元，王五账户上加上50元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50元转账。而对于李二少了50元，王五还没加上50元这个中间状态是不可见的。
-
隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
-
持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。
操作
- 查看自动提交事务的状态
select @@autocommit- 禁止自动提交事务的功能
set autocommit = 0;- 开启事务
start transaction;- 提交事务
commit;- 回滚事务
rollback;
事务并发时出现的问题
- 脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
- 不可重复读 ：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1在读取同一个字段，值就不同了
- 幻读：对于两个事务T1,T2，T1在A表中读取了一个字段，然后T2又在A表中插入了一些新的数据时，T1再读取该表时，就会发现神不知鬼不觉的多出几行了…
事务的隔离级别
- read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。
- read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）
- repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）
- serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下（因为你不完成就都不可以弄，效率太低）
索引
#### 索引分类
-
普通索引（index）
允许在定义索引字段中插入重复着和空值
-
唯一索引（unique）
要求索引列的所有值都只能出现一次，即必须唯一。
-
主键索引（primary key）
在MySQL的主键上创建的索引就是主键索引，主键索引会自动创建，一个表只能有一个主键索引，同时主键索引也是唯一索引。
-
全文索引（full text）
MySQL可以通过建立全文索引，利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。比如实现全匹配模糊查询。
-
空间索引（spatial）
空间索引使用R树，R树是用于索引多维数据的专用数据结构
创建索引语法
- 创建表时创建索引
create table 表名(
字段名 数据类型,
……,
[索引类型]index[索引名](字段名)[asc|desc]
)
- 对存在的表创建索引
alter table 表名 add [索引类型]index[索引名](字段名)[asc|desc];
- 使用create index语句创建索引
create table [索引类型]index [索引名](字段名)[asc|desc]
查看索引
explain select * from 表名 where 条件表达式
删除索引
- 使用drop删除索引
drop index 索引名 on 表名
- 使用alter table删除索引
alter table 表名 drop index 索引名
视图
创建视图
create view 视图名
as
select 字段 #完整的查询语句
with check option #表示视图操作时的检查条件如果省略，则不进行检查
查看视图
-
describe 视图名 -
desc 视图名 -
show table status like 视图名 -
show create view 视图名
修改视图
- 使用create or replace view进行修改
create or replace view 视图名 as
select 语句
with check option
- 使用alter view语句进行修改
alter view 视图名
as
select 语句
with check option
查询视图
select * from 视图名
更新视图
insert into 视图名 value();
删除视图
drop view 视图名
存储过程
- 创建
dilimiter$$
create procedure 存储过程过程名称([参数列表])
begin
sql语句
end$$;
- 调用
call 名称([参数])
- 查看
show create procedure 存储过程名词;
- 删除
drop procedure 存储过程名词;
360
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
