MySQL基本使用之总结篇

MySQL
是一个关系型数据库。开源、使用标准SQL数据语言格式。
一、数据库的创建、查看、选择、删除
1、使用命令创建数据库：
create database 数据库名 default character set 字符编码;
create database tttt default character set utf-8;
2、查看数据库
show databases;
3、查看数据库编码
selectschema_name,default_character_set_name frominformation_schema.sch ematawhereschema_name =‘test’;
4、删除数据库
drop database 数据库名称;
drop database tttt;
5、选择数据库
use 数据库名;
use tttt;

二、数据类型
1、数值类型（只写常用）：int(m)： 4个字节。范围（-2147483648~2147483648）
*m是长度，不表示存储长度 eg:int(3)，若实际值为2，则查询结果为002
2、浮点类型：float(m,d) :m总个数d小数位,4字节，8位精度
double(m,d)：8字节，16位精度
3、字符串类型：char(n):固定长度，最多255字符
varchar(n):可变长度，最多65535字符
4、char 和 varchar
1)char(n) 若存入字符数小于 n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此。
2)char 类型的字符串检索速度要比 varchar 类型的快，因为char是固定长度
5、日期类型：date:日期
time:时间
datetime:日期+时间
6、二进制数据
BLOB：以二进制方式存储，不区分大小写。只能整体读出，不用指定字符集
TEXT：以文本方式存储，区分大小写，可以指定字符集。

三、创建表、删除、修改
1、创建表命令
create table 表名（列名1 类型 约束，列名2 类型 约束……）；
2、查看表
show tables;
3、删除表
drop table 表名;
4、修改表
4.1修改表名
alter table 旧表名 rename 新表名;
4.2 修改列名
alter table 表名 change column 旧表名 新表名 类型;
4.3 修改列类型
alter table 表名 modify 列名 新类型;
4.4 添加列
alter table 表名 add column 新列名 类型;
4.5 删除列
alter table 表名 drop column 列名;
总结：列的增删改都用到了column

四、约束
1、查看表的约束信息
show keys from 表名;
2、添加主键约束
alter table 表名 add primary key(列名);
3、删除主键约束
alter table 表名 drop primary key;
（若删除主键时，主键有自动增长能力，需要先去掉自动增长）
alter table 表名 modify 列名 类型;(和修改列类型一样的语句)
4、添加非空约束
alter table 表名 modify 列名 类型 not null;
5、删除非空约束
alter table 表名 modify 列名 类型 null;
6、添加唯一约束
alter table 表名 add constraint 约束名 unique(列名);
7、删除唯一约束
alter table 表名 drop key 约束名;
8、添加外键约束
alter table 表名 add constraint 约束名 foregin key(列名) references 参照表名(列名);
9、删除外键约束（需删除索引，索引名和约束名相同）
alter table 表名 drop foregin key 约束名；//删外键
alter table 表名 drop index 索引名；//删索引
总结：4个约束，主键、唯一、外键都使用add。唯一和外键需要起名字，因为删除时会用到。非空添加和删除都使用modify。Mysql不支持check检查性约束。

五、mysql的DML操作（增、删、改）
1、添加数据
insert into 表名 values(值 1，值 2，值 3…);
或insert into 表名(列名 1，列名 2，列名 3…) values(值 1，值 2，值 3…);
注：*若主键是自动增长，需要使用default null 0来占位。，如果是默认值，只能用default
*一个表只能有一个自动增长、必须是整数类型、只能添加到主键或唯一约束上，删除约束时，需要先删自动增长再删除约束，不然违反了自动增长的规则会报错。
2、设置默认值(default)
2.1创建表时设置默认值：在类型后面+default 默认值
2.2修改表时添加默认值：(可用添加列名语句来记忆)
alter table 表名 add column 列名 列类型 default 默认值;
3、更新数据
update 表名 set 列名=值，列名=值 where 条件;
*set、where后面不能用子查询，update后面可以
4、删除数据
delete from 表名 where 条件;
5、清空表
truncate table 表名;
6、delete与 truncate区别
•truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）；
•truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
• truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而 不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。

六、mysql事务会自动提交
关闭事务的自动提交：start transaction

七、mysql中包含空值的算术表达式计算结果为空。

八、mysql的连字符，不支持||（Oracle支持），支持concat()
eg:查询雇员表中的所有数据，将所有数据连接到一起，每列值中通过#分割。
select concat(employees_id,’#’,last_name,’#’,email,"#",salary,"#",commission_pct) from employees;

九、mysql常见的单行函数：
1、大小写控制函数
• lower(str)：将大写转小写
• upper(str)：将大写转小写
2、字符处理
• concat(str1,str2…)：将多个字符串连接起来
• substr(str,pos,len)：从str的pos位开始截取len长度
• length(str)：str的长度
• instr(str,substr)：获取substr在str的位置
• trim(str)：去首尾两端空格
• ltrim(str)：去左侧开头空格
• rtrim(str)：去右侧开头空格
• replace(str1.str2,str3)：将字符串str中所有符合str2的替换位str3
3、数字函数
• round(arg1,arg2)：四舍五入指定小数的值。
• round(arg1)：四舍五入保留整数
• trunc(arg1,arg2): 截断指定小数的值，不做四舍五入处理
• mod(arg1,arg2):取余
4、日期函数
• sysdate()/now() 返回当前系统时间，格式为 YYYY-MM-DD hh-mm-ss
• cuedate() 返回系统当前日期，不返回时间
• curtime() 返回当前系统中的时间，不返回日期
• dayofmonth(date) 计算日期 d 是本月的第几天
• dayofweek(date) 日期d 今天是星期几，1 星期日，2 星期一，以此类推
• dayofyear(date) 返回指定年份的天数
• dayofname(date) 返回 date 日期是星期几(英文)
• last_day(date) 返回 date 日期当月的最后一天
5、转换函数
• date_format(date,format) 将日期转换成字符串（类似 oracle 中的 to_char()）
• str_to_date(str,format) 将字符串转换成日期（类似 oracle 中的 to_date()）
format格式：%a缩写星期名 %b缩写月名 %M月名 %m月（数值）
6、通用函数
• ifnull(expr1,expr2) 判断expr1是否为null，如果为null，则用expr2来代替null（类似 oracle 的 NVL()函数）
• nullif(expr1,expr2) 判断 expr1 和 expr2 是否相等，如果相等则返回 null，如果不相等则返回 expr1
• if(expr1,expr2,expr3) 判断 expr1 是否为真（是否不为 null），如果为真，则使用 expr2 替代 expr1；如果为假，则使用 expr3 替代 expr1（类似 oracle 的 NVL2()函数）
• coalesce(value,…) 判断 value 的值是否为 null，如果不为 null，则返回 value；如 果为 null，则判断下一个 value 是否为 null……直至出现不为 null 的 value 并返回或者返回最 后一个为 null 的 value
• CASE WHEN THEN ELSE END 条件函数

十、外连接
1、左外连接 left outer join … on
2、右外连接 right outer join … on
3、全外连接union、union all
使用：（left outer join … on）union（right outer join … on）；

十一、group by组函数，使用having来过滤

十二、子查询可放在 where、having、from子句中
1、 多行子查询关键字：in、any、all

十三、正则表达式（性能高于like、可对整数、字符检索，使用regexp关键字，默认忽略大小写，不忽略需使用binary关键字）
1、查询以 x 开头的数据(忽略大小写)
select 列名 from 表名 where 列名 regexp’^x’;
2、查询以 x 开头的数据(不忽略大小写)
select 列名 from 表名 where 列名 regexp binary’^X’;
1、查询以 x 结尾的数据(忽略大小写)
select 列名 from 表名 where 列名 regexp’x${}^{\prime };2、查询以x结尾的数据(不忽略大小写)select列名from表名where列名regexpbinary’X$’;
3、英文的点“.”，它匹配任何一个字符，包括回车、换行等
4、* ？ 匹配0个或多个 +匹配一个或多个
5、| 或的意思（regexp’abc|bcd’;-匹配包含 abc 或 bcd）
6、“[a-z]”：字符范围 “¹”：以什么字符开头的 “[^…]”：匹配不包含在[]的字符
7、“{n}”：固定次数。 regexp’s{2}’; 匹配以 s 连续出现 2 次的所有数据
regexp’o.{2}’;包含两个o（不用连续）
8、“{n,m}”：范围次数。regexp’ '^s{2,5}’ 匹配以 s 开头且重复 2 到 5 次的所有数据

十四、索引
查询索引
show index from 表名;
删除索引
drop index 索引名 on 表名;
总结：索引中直接创建和删除会使用on，还有外连接使用on
1、普通索引
1.1直接创建索引
create index 索引名 on 表名（列名）;
1.2修改表创建索引
alter table 表名add index 索引名（列名）;
1.3创建表时创建索引
在最后加上index 索引名(列名);
2、唯一索引
2.1直接创建唯一索引
create unique index 索引名 on 表名(列名);
2.2修改表创建唯一索引
alter table 表名 add unique index 索引名(列名);
2.3创建表时创建索引
在最后加上 unique 索引名(列名);
3、主键索引
3.1修改表时创建索引
alter table 表名 add primary key(列名);
4、组合索引
4.1指使用多个字段创建的索引，用(最左前缀原则)。如：name，address，salary 创建组合索引。只能使用如下组合：
name/address/salary
name/address
name/
如果使用 addrees/salary 或者是 salary 则索引不会生效。
4.2修改表时创建组合索引
alter table 表名 add index 索引名(列名1，列名2……);
5、全文索引(类似搜索引擎)
5.1修改表时创建索引
alter table 表名 add fulltext 索引名(列名);
5.2使用全文索引
select 列名 from 表名 where match(全文索引列名)against(‘搜索内容’);
5.3更换全文解析器（指定 ngram 解析器）
alter table 表名 add fulltext 索引名(列名) with parser ngram;
十五、用户管理（分为root用户（超级管理员）、普通用户）
1、创建用户
create user 用户名 identified by ‘密码’;
2、查看用户
select user,host from mysql.user;
3、分配权限
grant 权限 on 数据库.表 to 用户名@登陆主机 indentified by “密码”
登陆主机： % 匹配所有主机、localhost、127.0.0.1
4、刷新权限（调整权限后都需要刷新）
flush privleges;
5、删除用户
drop user ‘用户名’@’localhost’

十六、分页查询：limit子句
MySQL 分页中开始位置为 0，分页子句在查询语句的最后侧。
格式：
select 投影列 from 表名 where 条件 order by limit 开始位置,查询数量;

十七：执行计划(通过explain关键字模拟优化器执行SQL语句)
1、MySQL 整个查询执行过程
• 客户端向 MySQL 服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进 入下一阶段
• 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
• MySQL 根据执行计划，调用存储引擎的 API 来执行查询 • 将结果返回给客户端，同时缓存查询结果
2、启动执行计划
explain +查询语句

十八、存储引擎
1、查看数据库引擎
show engines;
2、修改表级存储引擎
alter table 表名 engine=InnoDB;
3、数据库引擎
MyISAM
InnoDB
innodb 与 myisam 区别
1.InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事 务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组 成一个事务；
2.InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3.InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引 效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此， 主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据 文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度 很快；
5.Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高； (在 MySQL5.7 版本中已经支持全文索引)

---

1. .... ↩︎