Mysql数据库 深度知识点

停止命令：net stop mysql
启动命令：net start mysql
 

mysql登录命令

mysql -h ip -P 端口 -u 用户名 -p
 
mysql --version 或者mysql -V用于在未登录情况下，查看本机mysql版本
select version();：登录情况下，查看链接的库版本
 
显示所有数据库：show databases;
进入指定的库：use 库名;
显示当前库中所有的表：show tables;
查看其他库中所有的表：show tables from 库名;
查看表的创建语句：show create table 表名;
查看表结构：desc 表名;
查看当前所在库：select database();
 

mysql语法规范

  1. 不区分大小写，但建议关键字大写，表名、列名小写
  2. 每条命令最好用英文分号结尾
  3. 每条命令根据需要，可以进行缩进或换行
  4. 注释
    • 单行注释：#注释文字
    • 单行注释：-- 注释文字 ，注意， 这里需要加空格
    • 多行注释：/ **注释文字 **/

SQL的语言分类

  • DQL（Data Query Language）：数据查询语言
    select 相关语句
  • DML（Data Manipulate Language）：数据操作语言
    insert 、update、delete 语句
  • DDL（Data Define Languge）：数据定义语言
    create、drop、alter 语句
  • TCL（Transaction Control Language）：事务控制语言
    set autocommit=0、start transaction、savepoint、commit、rollback
 
主要包括以下五大类
整数类型：bit、bool、tinyint、smallint、mediumint、int、bigint
浮点数类型：float、double、decimal
字符串类型：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：暂不介绍，用的比较少。
 

类型(n)说明

在开发中，我们会碰到有些定义整型的写法是int(11)，这种写法个人感觉在开发过程中没有什么用途，不过还是来说一下，int(N)我们只需要记住两点：
  • 无论N等于多少，int永远占4个字节
  • N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效
 
decimal采用的是四舍五入 
float和double采用的是四舍六入五成双
什么是四舍六入五成双？
就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉
char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息

数据类型选择的一些建议

  • 选小不选大：一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。
  • 简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
 
  • 尽量避免NULL：尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂。
  • 浮点类型的建议统一选择decimal
  • 记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引
 

权限生效时间

用户及权限信息放在库名为mysql的库中，mysql启动时，这些内容被读进内存并且从此时生效，所以如果通过直接操作这些表来修改用户及权限信息的，需要重启mysql或者执行flush privileges;才可以生效。
用户登录之后，mysql会和当前用户之间创建一个连接，此时用户相关的权限信息都保存在这个连接中，存放在内存中，此时如果有其他地方修改了当前用户的权限，这些变更的权限会在下一次登录时才会生效。

创建用户

语法：
create user 用户名[@主机名] [identified by '密码'];
说明：
  1. 主机名默认值为%，表示这个用户可以从任何主机连接mysql服务器
  2. 密码可以省略，表示无密码登录
通过修改mysql.user表修改密码
use mysql;
update user set authentication_string = password('321') where user = 'test1' and host = '%';
flush privileges;
 
给用户授权：
grant privileges ON database.table TO 'username'[@'host'] [with grant option]
 
grant命令说明：
  • priveleges (权限列表)，可以是all，表示所有权限，也可以是select、update等权限，多个权限之间用逗号分开。
  • ON 用来指定权限针对哪些库和表，格式为数据库.表名 ，点号前面用来指定数据库名，点号后面用来指定表名，*.* 表示所有数据库所有表。
  • TO 表示将权限赋予某个用户, 格式为username@host，@前面为用户名，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。
  • WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
    备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。

查看用户有哪些权限

show grants for '用户名'[@'主机']
show grants -- 查看当前用户的权限
撤销用户的权限：revoke privileges ON database.table FROM '用户名'[@'主机'];
删除用户：
1.drop user '用户名'[@‘主机’]
2.delete from user where user='用户名' and host='主机';
 
 

授权原则说明

  • 只授予能满足需要的最小权限，防止用户干坏事，比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限
  • 创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段
  • 初始化数据库的时候删除没有密码的用户，安装完数据库的时候会自动创建一些用户，这些用户默认没有密码
  • 为每个用户设置满足密码复杂度的密码
  • 定期清理不需要的用户，回收权限或者删除用户
 

总结

  1. 通过命令的方式操作用户和权限不需要刷新，下次登录自动生效
  2. 通过操作mysql库中表的方式修改、用户信息，需要调用flush privileges;刷新一下，下次登录自动生效
  3. mysql识别用户身份的方式是：用户名+主机
  4. 本文中讲到的一些指令中带主机的，主机都可以省略，默认值为%，表示所有机器
  5. mysql中用户和权限的信息在库名为mysql的库中
 
 

修改列

alter table 表名 modify column 列名 新类型 [约束];
或者
alter table 表名 change column 列名 新列名 新类型 [约束];
2种方式区别：modify不能修改列名，change可以修改列名
 
delete单表删除
delete [别名] from 表名 [[as] 别名] [where条件];
注意：
如果无别名的时候，表名就是别名
如果有别名，delete后面必须写别名
如果没有别名，delete后面的别名可以省略不写。
示例
-- 删除test1表所有记录delete from test1;
-- 删除test1表所有记录delete test1 from test1;
-- 有别名的方式，删除test1表所有记录delete t1 from test1 t1;
-- 有别名的方式删除满足条件的记录delete t1 from test1 t1 where t1.a>100;
多表删除
可以同时删除多个表中的记录，语法如下：
delete [别名1,别名2] from 表1 [[as] 别名1],表2 [[as] 别名2] [where条件];
说明：
别名可以省略不写，但是需要在delete后面跟上表名，多个表名之间用逗号隔开。
示例1
delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;
删除test1表中的记录，条件是这些记录的字段a在test.c2中存在的记录
 

drop，truncate，delete区别

  • drop (删除表)：删除内容和定义，释放空间，简单来说就是把整个表去掉，以后要新增数据是不可能的，除非新增一个表。
    drop语句将删除表的结构被依赖的约束（constrain），触发器（trigger）索引（index），依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
    如果要删除表定义及其数据，请使用 drop table 语句。
  • truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)，与drop不同的是，只是清空表数据而已。
    注意：truncate不能删除具体行数据，要删就要把整个表清空了。
  • delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。
    truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）
    truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。
    对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。
    delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
    truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
    如果有自增列，truncate方式删除之后，自增列的值会被初始化，delete方式要分情况（如果数据库被重启了，自增列值也会被初始化，数据库未被重启，则不变）
  • 如果要删除表定义及其数据，请使用 drop table 语句
  • 安全性：小心使用 drop 和 truncate，尤其没有备份的时候，否则哭都来不及
  • 删除速度，一般来说: drop> truncate > delete
drop
truncate
delete
 
条件删除
不支持
不支持
支持
删除表结构
支持
不支持
不支持
事务的方式删除
不支持
不支持
支持
触发触发器
否
否
是
别名中有特殊符号的，比如空格，此时别名必须用引号引起来。
 
不等于符号的使用：
<> 这个是最早的用法。
!=是后来才加上的。
两者意义相同，在可移植性上前者优于后者
故而sql语句中尽量使用<>来做不等判断
 
 

 

like（模糊查询）

select 列名 from 表名 where 列 like pattern;
pattern中可以包含通配符，有以下通配符：
%：表示匹配任意一个或多个字符
_：表示匹配任意一个字符。

 

查询运算符、like、between and、in、not in对NULL值查询不起效
 

<=>（安全等于）

<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低，用得较少
 

总结

  • like中的%可以匹配一个到多个任意的字符，_可以匹配任意一个字符
  • 空值查询需要使用IS NULL或者IS NOT NULL，其他查询运算符对NULL值无效
  • 建议创建表的时候，尽量设置表的字段不能为空，给字段设置一个默认值
  • <=>（安全等于）玩玩可以，建议少使用
语法：
select 列 from 表 limit [offset,] count;
说明：
offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。
count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。
limit中offset和count的值不能用表达式。
 

limit中不能使用表达式，limit后面的2个数字不能为负数

 

字段存在相同的值，当排序过程中存在相同的值时，没有其他排序规则时，mysql懵逼了，不知道怎么排序了； 
建议：分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序
where 多字段同时限制：
SELECT
user_id 用户id,
price 最大金额,
the_year 年份
FROM
t_order t1
WHERE
(t1.user_id , t1.price)
IN
(SELECT
t.user_id, MAX(t.price)
FROM
t_order t
GROUP BY t.user_id);
现在我们来讨论java输出的顺序为何和sql不一致？
上面java代码中两个表的连接查询使用了嵌套循环，外循环每执行一次，内循环的表都会全部遍历一次，如果放到mysql中，就相当于内标全部扫描了一次（一次全表io读取操作），主表（外循环）如果有n条数据，那么从表就需要全表扫描n次，表的数据是存储在磁盘中，每次全表扫描都需要做io操作，io操作是最耗时间的，如果mysql按照上面的java方式实现，那效率肯定很低。
那mysql是如何优化的呢？
msql内部使用了一个内存缓存空间，就叫他join_buffer吧，先把外循环的数据放到join_buffer中，然后对从表进行遍历，从表中取一条数据和join_buffer的数据进行比较，然后从表中再取第2条和join_buffer数据进行比较，直到从表遍历完成，使用这方方式来减少从表的io扫描次数，当join_buffer足够大的时候，大到可以存放主表所有数据，那么从表只需要全表扫描一次（即只需要一次全表io读取操作）。
mysql中这种方式叫做Block Nested Loop。
 
 
字段值为NULL的时候，not in查询有大坑，这个要注意
建议创建表的时候，列不允许为空
 

delimiter关键字  sql语句结束符

delimiter用来设置结束符，当mysql执行脚本的时候，遇到结束符的时候，会把结束符前面的所有语句作为一个整体运行，存储过程中的脚本有多个sql，但是需要作为一个整体运行，所以此处用到了delimiter
 
/*在执行过程中出任何异常设置hasSqlError为TRUE*/
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;
根据hasSqlError判断是否有异常，做回滚和提交操作
 
 
mysql使用binlog和relaylog记录数据库的变化
binlog有三种格式：ROW STATEMENT MIXED ，ROW记录national数据被修改了，修改成了什么样子；STATEMENT 记录修改的sql到binlog；MIXED就是以上两种模式的混合。
binlog是一个二进制文件，解析binlog有两种方式：
1.在命令行使用,show binlog events in 'binlog-filename'；方式查看
2.下载分析binlog，然后通过mysqlbinlog工具进行解析
 
 
扇区：磁盘存储的最小单位，扇区一般大小为512Byte
磁盘块：文件系统与磁盘交互的最小单位（计算机系统读写磁盘的最小单位），一个磁盘块由连续几个扇区组成，块一般大小为4KB
 
二分法查找数据的优点：定位数据非常快，前提是：目标数组是有序的。
 

总结一下使用索引的一些建议

  1. 在区分度高的字段上面建立索引可以有效的使用索引，区分度太低，无法有效的利用索引，可能需要扫描所有数据页，此时和不使用索引差不多
  2. 联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
  3. 查询记录的时候，少使用*，尽量去利用索引覆盖，可以减少回表操作，提升效率
  4. 有些查询可以采用联合索引，进而使用到索引下推（IPC），也可以减少回表操作，提升效率
  5. 禁止对索引字段使用函数、运算符操作，会使索引失效
  6. 字符串字段和数字比较的时候会使索引无效
  7. 模糊查询'%值%'会使索引无效，变为全表扫描，但是'值%'这种可以有效利用索引
  8. 排序中尽量使用到索引字段，这样可以减少排序，提升查询效率
posted @ 2019-10-21 11:40  兔老霸夏  阅读(511)  评论(0)  编辑  收藏  举报