Mysql数据库 深度知识点，

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语法规范

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 '密码'];

说明：

通过修改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段
• 初始化数据库的时候删除没有密码的用户，安装完数据库的时候会自动创建一些用户，这些用户默认没有密码
• 为每个用户设置满足密码复杂度的密码
• 定期清理不需要的用户，回收权限或者删除用户

 

总结

   

修改列

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   二分法查找数据的优点：定位数据非常快，前提是：目标数组是有序的。  

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