MySQL 简易笔记

MySQL

SQL语句的分类

1.DQL:数据查询语言（所有带有select的语句）
2.DML:数据操作语言（insert，delete，update）操作表中的数据
3.DDL:数据定义语言(create, drop, alter) 操作表的结构
4.TCL:事物控制语言(事务提交 commit 事务回滚 rollback)
5.DCL:数据控制语言(授权 grant 撤销授权 revoke)

常用命令(大小写不敏感)

连接到Mysql服务的指令：mysql -h 主机名 -P 端口 -u 用户名 -p密码(没有空格)
若没有-h，默认是本机，若没有-P，默认是3306
1.登录:mysql -uroot -p
2.退出:exit
3.查看mysql中有哪些数据库:show databases;
4.使用数据库 use (dataname)
5.创建数据库 create database (dataname) [character set (编码格式) collate(校对规则)]
若没有指定编码格式，校对规则，则默认与数据库的编码格式和校对规则相同。
6.查看当前库有什么表: show tables;
7.查看创建特定表时用到的MySQL语句：show create database (dataname);
8.查看表列相关信息：show columns from (dataname) = describe (dataname)
9.删除数据库：drop database (dataname);
10.备份数据库：mysqldump -u root -p -B 数据库0 数据库1 … 数据库n > 备份文件存放路径 (在dos下执行) (备份实际上就是创建该库所要用的SQL语句) (-B表明要备份的是一个数据库)
11.备份表：mysqldump -u root -p 数据库 表1 表2 … > 备份文件存放路径 (在dos下执行)
11.恢复数据库：source 备份文件路径
12.创建表：create table [tablename] (field1 datatype, field2 datatype…) character set… collate… engine…
13.修改表
添加列 alter table tablename add column datatype [default expr]
修改列 alter table tablename modify column datatype [default expr]
删除列 alter table tablename drop column
修改表名 rename table 表名 to 新表名
修改表字符集 alter table tablename character set 字符集
修改列名 alter table tablename change prename newname datatype [default expr]
14.删除表：drop table tablename

insert操作

insert into tablename (要插入数据的字段) values(),(),()…
若是给所有字段添加数据，可以不写前面的字段名称
默认值的使用：当不给某个字段值时，如果有默认值就会添加默认值，否则报错。

update操作

update tablename set xxx = xx, aaa = aa,… [where key = X] 若没有where语句，则修改该列所有数据

delete操作

delete from tablename [where xxx = xx] 若没有where，整张表都会被删

select操作

select [distinct] * / {column1, column2…} from tablename distinct可起到去重作用
select可以配合表达式操作 select expression… from tablename
eg:select (a + b + c) from tablename (a,b,c均为列)
取别名 select column/expression as xxx …. from tablename

**列与列之间用“，”分隔，若以空格分隔，则默认后者是前者的别名。

在where子句中常用的运算符

比较运算符:

• > < <= >= = < > !=
• between…and…
• in(set)
• like… / not like… 模糊查询
  like操作符（模糊）： %表示0到多个字符 _:表示单个字符
• is null
  逻辑运算符:
  and, or, not

order by排序查找结果

select column1, column2, … from tablename order by coloumn asc/desc [, column2 asc/desc …]
默认升序，且该子句应位于select语句的结尾

group by, having

group by用于对查询的结果分组统计 先分组然后在组内统计。
having子句用于限制分组显示结果 类似于where的效果。

加密和系统函数

• user() 查询当前用户
• database() 查询数据库名称
• md5(str) 加密

流程控制函数

if(expr1, expr2, expr3) 若expr1为真，则执行expr2否则执行expr3，在if语句中，null才被判定为假
ifnull(expr1, expr2) 若expr1不为null，则返回expr1，否则执行expr2
select case when expr1 then expr2 when expr3 then expr4 … else exprN 若expr1为真，则执行expr2，若expr3成立，则执行expr4 … 否则执行exprN（只会返回一个值）

表的理解

数据库中最基本的单元是表
数据库中是以表格的形式表示数据的

行(row):数据/记录
列(col):字段(属性：字段名，数据类型，约束等)

mysql表查询 – 增强

基本语法：select … limit start, rows; 表示从start+1行开始取，取出rows行
语法顺序：group by… having… order by… limit…

多表查询

默认情况下对两个表进行查询时，采用笛卡尔积：
从第一张表中取出一行与第二张表中的每一行结合，返回记录数=表1行数*表2行数
因此需要使用where子句对提取出来的数据进行筛选

自连接

特点：把同一张表当作两张表使用 但在使用中要给两张表取不同的名字
应用场景：需要的数据在同一张表中但不在同一行中
select x from [databasename] as a, [databasename] as b where…

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套语句

• 单行子查询
  单行子查询是指只返回一行数据的子查询语句

• 多行子查询
  多行子查询指返回多行数据的子查询 使用关键字in

格式：select * from emp where salarys = (select salarys from emp where name = ‘pcx’)

子查询的结果可以作为一张临时表使用

all和any

all表示达成全部条件，any表示达成某一条件。

表复制:

insert into table1 select (数据列名) from table2 (复制数据)
create table [tablename] like [table2] (拷贝表结构)

合并查询

关键字:union
使用场景：

• 在单个查询中从不同的表返回类似结构的数据
• 对单个表执行多个查询，要求返回单张表
  union all 不去重 union 去重

外部链接

使用场景：在将一个表中的行与另一个表中的行相关联，需要包含没有关联行的那部分行时
关键字：left outer join / right outer join on
语法: select xxx from [table1] left/right outer join [table2] on 条件

约束

主键

主键不能重复并且不能为null
一张表最多只能有一个主键，但可以是复合主键
主键指定方式：

• 在字段名后指定
• 在表定义的最后指定

unique

表示该列中的值都是唯一的

外键

将从表的某字段与主表的某字段绑定
语法：表定义的最后 foreign key(从表字段) references 主表名(主表字段)
主表中的字段必须是主键或有unique约束

细节：
1.类型为innodb的表才支持外键
2.外键字段的类型要和关联字段的类型一致
3.外键字段的值必须要在关联字段中出现过或者为null
4.一旦建立主外键关系，数据就不能随意删除
（只有当从表中关联的外键全部被删除时，关联字段才允许被删除）

自增长

设置为自增长的列在每一次添加一条记录时自动增量
语法 column datatype […] auto_increment
自增长默认从1开始，也可使用语句 alter table tablename auto_increment = x;来修改起始值

• 只有一列数据可以使用自增长
• 被设为自增长的列必须能被索引。

索引

语法：
create [unique] index 索引名 on 表名(对应字段)
alter table 表名 add index 索引名(对应字段)
添加主键索引: alter table 表名 add primary key(列名)
删除索引:drop index 索引名 on 表名
删除主键索引:alter table 表名 drop primary key

原理：
程序会根据索引字段建立一棵二叉搜索树（也有可能用的别的数据结构）
若没有索引则会进行全表扫描
劣势：

• 内存空间消耗
• 对增删改语句的效率造成影响（数据结构必须做出对应调整）

索引的类型：

• 主键索引，主键自动为主索引
• 唯一索引 unique
• 普通索引 index
• 全文索引 fulltext Mysql自带的全文索引使用不多

事务

事务处理：管理必须成批执行的MySQL操作
术语：
事务：一组SQL语句
回退：撤销指定的SQL语句
提交：将未存储的SQL语句结果写入数据库表
保留点：事务处理中设置的临时占位符，可以对它发布回退

语法

start transaction 开始事务
savepoint 保留点名 设置保留点
rollback [to 保留点名] 回滚（默认回滚到初始状态）
commit 提交修改

注意：每一次回退都会将路径上的保留点全部删除

隔离级别

隔离：多个连接开启各自事务操作数据库中数据时，数据库所做的保证各个连接获取数据的准确性的操作

脏读：当前连接在事务进行中时可以读取到其他连接事务中还未提交的操作
不可重复读：当前连接在事务进行中时可以读取到其他连接的修改操作
幻读：当前连接在事务可以读取到其他连接的增添或删除操作
在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

隔离级别：

• 读未提交(read uncommitted) 可能发生脏读、不可重复读、幻读
• 读已提交(read committed) 可能发生不可重复读、幻读
• 可重复读(repeatable read) 可能发生幻读（但在Mysql中被解决） 不上锁
• 可串行化(serializable) 安全 上锁

锁：当程序检测到有连接在对数据库进行操作时，会终止其他连接对数据库进行操作

查看当前隔离级别：select @@transaction_isolation
查看系统当前隔离级别：select @@global.transaction_isolation
设置当前会话隔离级别：set session transaction isolation level [目标隔离级别]
设置系统隔离级别：set global transaction isolation level [目标隔离级别]

acid特性：

• 原子性：事务中的操作要么都发生，要么都不发生
• 一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态
• 隔离性：数据库为每一个用户开启的事务不能被其他事务的操作数据干扰，多个并发事务之间要相互隔离
• 持久性：一个事务一旦提交，他对数据库中的数据的改变就是永久性的，

存储引擎

MyISAM

不支持事务，也不支持外键，但访问速度快，对事务完整性没有要求（适用于只需要CRUD的情况）

InnoDB

提供了具有提交、回滚和崩溃恢复能力的事务安全，但处理效率稍差一些，且要占据更多的磁盘空间来保留数据和索引

MEMORY

使用存在内存中的内容来创建表。默认使用Hash索引，因此有极高的访问速度。

视图

视图是根据基表创建的一张虚拟表
创建视图只会创建一个表结构，并将基表中的数据映射到视图中（可以理解为视图中存储着基表数据的引用）
不管对视图还是基表进行修改，都会对另一方造成影响

视图的使用

• 创建：create view 视图名 as select column1, … from 基表
• 修改：alter view 视图名 as select column1, … from 基表
• 删除：drop view 视图名1，视图名2

用户管理

创建用户：create user ‘用户名‘@’ip地址’ identified by ‘密码’
修改密码：alter user ‘用户名‘@’ip地址’ identified by ‘密码’
删除用户：drop user ‘用户名‘@’ip地址’

用户权限管理

赋予权限： grant 权限列表 on 库.对象 to ‘用户名‘@’ip地址’ [identified by ‘密码’]
回收权限：revoke 权限列表 on 库.对象 from ‘用户名‘@’ip地址’ [identified by ‘密码’]

注意：

• 在创建用户时如果不指定Host，则为%，表示可以从任意IP登录该用户
• 指定host时，192.168.0.%表示192.168.0.*
• 在删除用户时，若host不是%，则需要明确指定IP

常用数据类型

decimal (M,D) M表示该数总位数, D表示小数点后位数 【默认情况下M是10，D是0】

数学相关函数

ABS(num) 绝对值
BIN(decimal_number) 十进制转二进制
ceiling(num) 向上取整
conv(num, from_base, to_base) 进制转换
floor(num) 向下取整
format(num, decimal_places) 保留小数位数
hex (decimalnumber) 转十六进制
least (num1, num2, …) 最小值
mod(num, denominator) 求余
rand([seed]) 随机数 范围为 0 <= v <= 10

字符串类型

char 固定长度字符串，最大255字符
varchar 可变长度字符串 最大65532字节(有3个字节被用于记录大小)
字符串使用细节：
char(4),varchar(4) 此时的4表示字符数，不区分字母与汉字，其占用空间大小视编码格式决定
char(4)是定长
varchar(4)是变长，其占用空间可伸缩，而varchar本身需要1-3个字节来存放长度大小
若数据定长，推荐使用char，re：char的查询速度更快。
若varchar不够用，则可使用mediumtext或longtext

字符串相关函数

** 字符串起始下标为1**

• charset(str) 返回字符字符集
• concat(str1, …) 连接字符串
• instr(string, substring) 返回substring在string中出现的位置，若没有则返回0
• ucase(string) 转换成大写
• lcase(string) 转换成小写
• left(string, length) 从string中的左边起取length个字符
• length(string) string长度
• replace(str, search_str, replace_str) 在str中用replace_str替换search_str
• strcmp(string1, string2) 逐字符比较两子串大小
• substring(str, position [, length]) 从str的position位置开始，取length个字符
• ltrim(string) rtrim(string) trim 去除前端/后端/前后端空格。

日期类型

date 年月日
datetime 年月日时分秒
timestamp 时间戳 （在insert和update时自动更新）配置代码 timestamp xxx not null default current_timestamp update current_timestamp

时间日期函数

current_date() 当前日期
current_time() 当前时间
current_timestamp() 当前时间数
date(datetime) 返回datetime日期部分
date_add(date, interval d_value d_type) 在date上加上日期或时间
date_sub(date. interval d_value d_type) 在date上减去日期或时间
datediff(date1, date2) 两个日期差（结果是天） 日期需要年月日时分秒
timediff(date1, date2) 两个时间差（多少小时多少分钟多少秒）
now() 当前时间
unix_timestamp()返回1970-1-1 00:00:00至今的秒数
last_day(date)返回传入日期所在月份的最后一天

*日期类型可以用大于小于号比较，但要将日期用单引号括起来