起风网一、理解MySQL基本概念
1、MySQL软件：MySQL实际上就是一软件，是一工具，是关系型数据库管理系统软件
2、MySQL数据库：就是按照数据结构来组织、存储和管理数据的仓库
3、MySQL数据库实例：
①MySQL是单进程多线程（而oracle是多进程），也就是说MySQL实例在系统上表现就是一个服务进程，即进程；
②MySQL实例是线程和内存组成，实例才是真正用于操作数据库文件的；
一般情况下一个实例操作一个或多个数据库；集群情况下多个实例操作一个或多个数据库。
二、MySQL数据库启动以及启动的判断
1、启动MySQL数据实例：
shell> service mysqld start #rpm包安装的mysql
如果是源码安装的话，推荐使用mysqld_safe命令的安全启动(可以看到启动信息)。
2、判断MySQL数据库是否启动：
shell> netstat -tulnp|grep 3306 #如果可以过滤出来(有输出)证明已启动
shell> mysqladmin -uroot -p123 ping #出现mysqld is alive证明是活跃的
三、如何使用官方文档和help
1、基本技能：DBA所有的操作必须来自于官方文档
2、mysql> help contents; #寻求help帮助的入口
四、官方文档概览
1、Tutorial：将MySQL常用的一些操作使用一个场景串联起来
只是关注里面的灰色部分就可以，按照里面的灰色操作部分顺一遍
2、server Administrator：MySQL管理需要的一些命令、工具、参数等
3、SQL Syntax
SQL语法，使用最多，特别是DDL语句一定要使用SQL语法进行参考
4、Server Option / Variable Reference：MySQL的参数和状态值，使用较多
5、Functions and Operators
MySQL常用函数和操作符，使用较多
6、Views and Stored Programs
视图、存储过程、函数、触发器、event语法参考
7、Optimization：优化
非常值得细致的看一遍，此篇文档不仅仅用来参考，更多的是用来学习优化知识，算是DBA进阶宝典
8、Partitioning
如果是要进行表分区，此文档是必须参考的资料，也是唯一参考的资料
9、Information Schema、Performance Schema
中级DBA常用的两个参考资料
10、Spatial Extensions
地理位置信息
11、Replication
MySQL使用复制功能，常用的参考资料
12、Semisynchronous Replication
半同步复制，个别场合会用到
五、如何使用官方文档
1、参考官方文档修改密码强度(降低密码强度)、修改密码
①改密码强度：
mysql> show variables like ‘validate_password%’;
mysql> SET GLOBAL validate_password_policy=0;
②修改密码：set、alter
2、参考官方文档查询当前数据库连接的数量(查询状态值Threads_connected)
mysql> show status like ‘%Threads_connected%’;
注意：查看状态值是show status
查看变量值是show variables
3、建立一个数据库指定字符集
mysql> create database test_db character set utf8;
4、给一个表增加一个列，要求这个列的数据类型是字符串、非空（alter）
ALTER TABLE tbl_name ADD COLUMN col_name varchar(20) not null;
5、用函数将两个字符串串联起来(concat：合并多个字符串)
CONCAT()：returns NULL if any argument is NULL.
CONCAT_WS(separator,str1,str2,…)
6、mysqladmin的使用：类同于ping数据库是否活跃、关闭数据库
shell> mysqladmin -uroot -p123 ping
mysqld is alive
shell> mysqladmin -uroot -p123 shutdown
7、如何启动数据库：mysqld_safe命令(切记挂后台&，否则占领当前会话无法退出)
shell> mysqld_safe –defaults-file=/etc/my.cnf &
官方文档对于具有一定基础知识的人来说，是一个最合适的工具，可以使DBA的操作变得没有障碍
六、登录MySQL查看当前会话的状态
mysql> status
七、描述MySQL在Linux平台下的大小写、同时演示大小写的区别
1、数据库名、表名、表别名严格区别大小写
2、列名、列别名忽略大小写
3、变量名严格区别大小写
4、MySQL在windows下各个对象都不区别大小写
mysql> show variables like 'lower%';+------------------------+-------+| Variable_name | Value |+------------------------+-------+| lower_case_file_system | OFF || lower_case_table_names | 0 |+------------------------+-------+
①lower_case_file_system是对实际的文件系统的反应，为只读变量，不能修改。Off表示MySQL所在的文件系统大小写敏感，也就是说进入MySQL所在的文件系统查看里面的内容，发现有mysql文件夹，此时新建一个名为MYSQL的文件夹是可以的，说明大小写敏感。
②lower_case_table_names表示表名或数据库存储是否区别大小写，为只读变量，可以在配置文件my.cnf里面修改：
0表示区分大小写，按照新建数据库的大小写形式存储显示；
1表示无论新建数据库大小写都以小写的形式存储显示。
八、MySQL的几种帮助
1、shell> mysql –help
2、mysql> help show
mysql> show create table tel_name
mysql> help set
九、MySQL的变量如何查看，如何修改
1、查看变量用select
局部变量select var_name;
用户变量select @var_name;
全局变量select @@var_name;
2、修改变量用set
SET variable_assignment [, variable_assignment] ...variable_assignment: user_var_name = expr #变量名字=一个值 |[GLOBAL | SESSION] system_var_name = expr |[@@global. | @@session. | @@]system_var_name = expr
①set global表示修改后对全部会话生效，为全局修改变量
②set session表示修改后对本次会话生效
③如果变量是只读变量可以通过修改MySQL的配置文件my.cnf来修改变量，在[mysqld]下添加一行数据：user_var_name=expr，然后重启数据库再登录即可。
十、MySQL的状态参数如何查看、如何参考阅读其内容
在官方文档的Server Option / Variable Reference部分，进行参考查看MySQL的参数变量以及状态值
1、cmd-line表示能否在mysql安全启动(mysqld_safe)中进行参数设置 –var_name=……
2、option file表示能否在mysql的参数文件中进行参数设置
3、system var表示是否是系统变量
4、status var表示是否是状态变量
5、var scope表示变量的范围：全局global、会话session
6、dynamic表示是否是动态参数，yes是动态，no是静态
十一、如何查看某个数据库里面有多少表、每一个表的列的信息
1、show tables; desc tbl_name;
2、mysql> select * from information_schema.TABLES
-> where TABLE_NAME=’tbl_name’\G;
①information_schema数据库：也称为数据字典，记录了各数据库的表、视图、索引、存储过程、函数等信息……
②information_schema.TABLES：记录了MySQL中每一个数据库中表所在的数据库、表的名字、表的行数等信息。
十二、如何查看一个表的建表语句、一个数据库的建库语句
1、show create table tbl_name;
2、show create database db_name;
十三、如何查看MySQL支持的数据类型以及数据类型如何使用
mysql> help contents;
mysql> help data types;
mysql> help ……
十四、列举show命令常用的语法
1、show status like …… 查看状态值
2、show variables like …… 查看变量参数值
3、show create …… 查看建表、库……的语句信息
4、show procedure status where db=’db_name’\G; #查看存储过程信息
5、show warnings\G; #查看警告信息
十五、help kill如何使用
mysql> help kill
KILL [CONNECTION | QUERY] processlist_id
注：Thread processlist identifiers can be determined from the ID column of the INFORMATION_SCHEMA.PROCESSLIST table。
mysql> select * from INFORMATION_SCHEMA.PROCESSLIST\G;
十六、描述MySQL用户名组成以及特点
1、MySQL用户身份识别认证：用户名user、密码password、登录mysqld主机host
shell> mysql -uroot -p123 -h172.16.11.99
-u：登录的用户名
-p：登录用户对应的密码
-h：MySQL服务器主机IP，默认是localhost的IP
2、MySQL的用户管理模块的特点：客户端请求连接，提供host、username、password，用户管理模块进行验证请求连接，通过mysql.user表进行校验信息。
十七、如何查看MySQL有多少用户以及对应的权限
1、mysql> select count(*) from mysql.user; #查看MySQL有多少用户
2、mysql> select * from mysql.user\G; #用户信息查询（权限）
十八、建立一个用户
1、本地登录
mysql> create user ‘u1’@’localhost’ identified by ‘123’;
2、任意都可以登录
mysql> create user ‘u2’@’%’ identified by ‘123’;
3、某一个网段可以登录
mysql> create user ‘u3’@’172.16%’ identified by ‘123’;
4、具体主机可以登录
mysql> create user ‘u4’@’172.16.12.24’ identified by ‘123’;
十九、使用help grant，给用户赋权
二十、建立一个db1数据库的只读用户
建用户然后授权
mysql> GRANT SELECT ON db1.* TO ‘olr_user’@’%’;
二十一、建立一个只能进行系统状态信息查询的管理用户
mysql> grant select on information_schema.* to ‘admin_user’@’%’;
二十二、建立一个db1的生产用户，只能进行dml、select，不能进行ddl
mysql> grant select,insert,update,delete on *.* to ‘pro_user’@’%’;
二十三、建立一个可以进行DDL的管理用户
mysql> grant create,drop,alter on *.* to ‘admin_user’@’%’;
二十四、建立一个工资表，只有指定的用户可以访问工资列，其他用户都不能访问工资列
实现步骤：
先在mysql.user里将所有用户检索出来，进行跑批处理(脚本或存储过程)revoke对该表列的权限；
然后grant创建用户，并对该表列赋访问权限。
二十五、查询上述用户以及所赋权限是否正确，同时进行验证
mysql> select * from mysql.user\G; #查看MySQL用户信息
进行用户登录验证
二十六、解释with grant option，并且演示其功能
mysql> grant all on *.* to ‘zhang’@’%’ identified by ‘123’ with grant option;
with grant option子句：通过在grant语句的最后使用该子句，就允许被授权的用户把得到的权限继续授给其他用户。也就是说，客户端用zhang用户登录MySQL，可以将zhang用户有的权限使用grant进行授权给其他用户。
二十七、查询某一个表上的权限、查看某一个列上的权限、查看某一个数据库上面的权限
1、 查询所有数据库的权限
mysql> select * from mysql.user;
2、 查询某个数据库的权限
mysql> select * from mysql.db;
3、 查询某个数据库中某个表的权限
mysql> select * from mysql.tables_priv;
4、 查询某个数据库某个表中某个列的权限
mysql> select * from mysql.columns_priv;
二十八、修改参数运行使用grant建立用户，修改参数禁止grant建立用户
mysql> show variables like 'sql_mode%';+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.37 sec)
sql_mode参数中的NO_AUTO_CREATE_USER值：不自动创建用户
mysql>set @@session.sql_mode=……; #设置sql_mode参数
二十九、修改mysql的用户密码，分别使用grant、alter、set修改
①mysql> grant all on *.* to ‘用户名’@’登录主机’ identified by ‘密码’;
②mysql> alter user ‘用户名’@’登录主机’ identified by ‘密码(自定义)’;
③mysql> SET PASSWORD FOR ‘用户名’@’登录主机’ = PASSWORD(‘密码’);
三十、破解密码步骤：
①到/etc/my.cnf 里将 validate_password=off 行注释 //关闭密码策略
②shell> mysqld_safe –skip-grant-tables & //重启数据库
③shell> mysql -uroot //无密码登录
④mysql> flush privileges; //刷新权限使密码生效
⑤修改密码，退出，重启数据库，进入
三十一、使用revoke进行权限的收回，将上面用户的授权分别收回，同时查看收回后的结果
①REVOKE INSERT ON *.* FROM ‘jeffrey’@’localhost’;
②REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] …
三十二、select最简单常用语法
1、全表查询
select * from tbl_name;
2、某些行查询
select * from tbl_name where ……;
3、某些列查询
select clm_name from tbl_name;
4、某些行的某些列查询
select clm_name from tbl_name where ……;
5、列别名
select clm_name as new_name from tbl_name;
6、列运算
select clm_name+123 from tbl_name;
三十三、concat函数的使用
1、concat函数：将多个字符串参数首尾相连后返回
2、concat_ws函数：将多个字符串参数以给定的分隔符，首尾相连后返回
3、group_concat：函数的值等于属于一个组的指定列的所有值，以逗号隔开，并且以字符串表示
三十四、演示打开和关闭管道符号“|”的连接功能
PIPES_AS_CONCAT：将“||”视为字符串的连接操作符而非或运算符
|| 管道连接符：
mysql> select 列名1 || 列名2 || 列名3 from 表名;
在mysql中，进行上式连接查询之后，会将查询结果集在一列中显示，列名是‘列名1 || 列名2 || 列名3’
mysql> select s_no || s_name || s_age -> from student;+-------------------------+| s_no || s_name || s_age |+-------------------------+| 1001张三23 || 1002李四19 |+-------------------------+
如果不显示结果，是因为sql_mode参数中没有PIPES_AS_CONCAT，只要给sql_mode参数加入PIPES_AS_CONCAT，就可以实现像CONCAT一样的功能；
如果不给sql_mode参数加入PIPES_AS_CONCAT的话，|| 默认是or的意思，查询结果是一列显示是1。
三十五、使用mysql> help functions; 学习MySQL各类函数
三十六、常见功能函数
1、upper(……)、lower(……)大小写变换
2、user()查看登录用户、current_user()查看当前用户
3、database()查看使用的数据库
三十七、使用help来学习下面的数据类型(建立对应类型的列、插入数据、显示数据)
1、整数：int
2、非负数：unsigned无符号即非负数—e.g：int unsigned
3、小数：dec
4、浮点数以及科学计数法：float、double
如果FLOAT数据在插入的时候，要使用NeM(科学计数法)的方式插入时：
比如
5e2 就是5*10的2次方
5e-2就是5*10 的-2次方
4e-1+5.1e2 就是510.4
5、字符串：varchar
6、布尔：bool、boolean—synonyms(同义词)：TINYINT(1)
7、位：bit
如何使用16进制常量：hex()
如何使用2进制常量：bin()
date类型以及STR_TO_DATE函数
time类型以及STR_TO_DATE函数
dateime数据类型以及标准写法、STR_TO_DATE函数
date和time显示方式以及date_format函数
三十八、时区
1、查看操作系统时区、数据库时区
查看操作系统时区：
shell> cat /etc/sysconfig/clock
ZONE=”Asia/Shanghai”
shell> ls /usr/share/zoneinfo
……
mysql> show variables like ‘system_time%’; #查看MySQL系统时区
mysql> show variables like ‘time_zone%’; #查看数据库时区
2、修改数据库时区为东八区，去掉数据库时区对os时区的依赖(查看官方文档)
加载系统时区：将Linux时区导入到数据库中
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p123 mysql
mysql> set @@global.time_zone=’Asia/Shanghai’;
修改数据库时区为东八区，同时在参数文件中进行修改，永久保存
3、时区在什么时候有用：
如果数据库里面没有timestamp这个数据类型，那么时区参数没有意义！
你如何确认你的数据库里面是否有timestamp类型的列？
mysql> select table_name,column_name,data_type -> from information_schema.columns -> where data_type='timestamp';
……
时区原理描述：insert过程和select过程的描述：相对应的0时区的转换
4、时区的正确实践(timestamp)
insert以前：你的values对应的时间到底是哪个时区，然后设置set @@session.time_zone为对应的时区
select获取以前：你想得到什么时区的时间，就设置set @@session.time_zone为对应的时区
三十九、字符集
1、查看服务器的字符集
mysql> show variables like ‘character_set_server’;
2、查看数据库字符集
mysql> show variables like ‘character_set_database’;
一般在数据库实现字符集即可，表和列都默认采用数据库的字符集
gbk
utf8
3、查看表的字符集、查看列的字符集
mysql> show create table tbl_name;
4、字符集原理描述、字符集正确实践
对于insert过程描述、对于select过程描述
①对于insert来说，character_set_client、character_set_connection相同，而且正确反映客户端使用的字符集
②对于select来说，character_set_results正确反映客户端字符集
③数据库字符集取决于我们要存储的字符类型
④字符集转换最多发生一次，这就要求character_set_client、character_set_connection相同
⑤所有的字符集转换都发生在数据库端
总述：
1)建立数据库的时候注意字符集(gbk、utf8)
2)连接数据库以后，无论是执行dml还是select，只要涉及到varchar、char列，就需要设置正确的字符集参数:
character_set_client、character_set_connection、character_set_results
5、客户端字符集如何来理解？
取决于客户端工具
shell> mysql -uroot -p123456 -hserver_host -P3306
mysql工具本身没有字符集，因此客户端字符集取决于工具所在的os的字符集（windows：gbk、linux：utf8）
sqlyog工具本身带字符集，此时客户端os字符集就没有意义
6、如何判断字符集出现了问题？
所有设置都正确，但是查询到的还是乱码，这就是出现问题了
四十、如何识别变量参数、状态参数status var
show variables……
show status……
识别判断都是查看官方文档System Var、Status Var
四十一、如何识别动态参数、静态参数
动态参数dynamic：Yes
静态参数dynamic：No
四十二、对于动态参数如何设置，如何判断动态参数是否可以在全局级别或者会话级别修改
1、set
2、修改参数文件/etc/my.cnf：弊端是需要重启才能生效（很少用）
判断：参考官方文档Option/Variable Summary，通过Var scope来进行判断动态参数的全局global、both
四十三、对于静态参数如何修改
静态参数，在整个实例声明周期内都不得进行更改，就好似是只读的；
一般静态参数都是在配置文件中修改/etc/my.cnf，当然静态参数能否写入配置文件还要看官方文档对该参数的Option File的描述Yes与否。
四十四、掌握@@、@的区别
1、@@var_name表示的系统变量
根据系统变量的作用域可分：全局变量、会话变量
2、@var_name表示的用户变量
①用户变量和数据库连接有关，连接后声明变量，连接断开后，自动消失；
②select一个没有赋值的用户变量，返回NULL，也就是没有值；
Mysql的变量类似于动态语言，变量的值随所要赋的值的类型而改变。
四十五、set @@session.和set @@global.的生效时间
对于一个新建立的连接，只有全局变量，会话变量还不存在，这个时候会从全局变量拷贝过来。
1、set @@session.：只对当前连接起作用
2、set @@global.：对全局变量的修改会影响到整个服务器
注意：set系统变量时，不带作用域修饰，默认是指会话作用域；
（特别注意，有些系统变量不带作用域修饰，无法设置，因此最好都带上作用域设置系统变量）。
四十六、动态参数最佳实践
1、尽量先进行会话级别的设置set @@session，确认生效而且效果不错以后，再进行全局设置，如果需要马上生效，杀掉所有的会话：
mysql> select concat(‘kill ‘,conn_id,’;’) from sys.session;
2、确认没有问题以后，修改参数文件，下次系统启动一直生效。
四十七、select书写技巧
1、确认需要访问数据来自于哪几张表
from来自某张表或者某几张表
join添加某张表
on表连接条件
记住一点：每关联一个表就需要加上对应的on条件(on条件就是主外键条件)
2、通过where条件来过滤数据
3、确认需求里面是否有分组聚合的含义
分组：group by
聚合：聚合函数
聚合条件过滤：having
4、是否需要排序
order by
四十八、MySQL内置函数(将列出的常见的一些函数熟悉过一遍)
1、内置函数的多少是一个数据库是否成熟的标志
2、学会使用help Functions学习和使用函数(重点!!!!!!!!!!!)
3、常用函数要过一遍
①日期时间相关的函数
CURDATE、DATEDIFF、DATE_FORMAT、DAYOFWEEK、LAST_DAY、EXTRACT、STR_TO_DATE
②比较操作符要求都过一遍，help Comparison operators;
③流程控制行数help Control flow functions;
④加密函数help Encryption Functions;
只需要看看decode、password两个函数即可
⑤信息获取函数help Information Functions;
通过这些函数可以知道一些信息，过一遍即可
⑥逻辑操作符help Logical operators;
！、and、or，这些常用的要过一遍
⑦杂项函数help Miscellaneous Functions;
简单浏览一下里面的函数，对于名字有个印象即可
⑧数值函数help Numeric Functions;
使用数据库来进行数学运算的情况不多，常用的加减乘除、TRUNCATE、ROUND
⑨字符串函数help String Functions;
CONCAT、CONCAT_WS、CAST、FORMAT、LIKE、REGEXP、STRCMP、TRIM、SUBSTRING、UPPER，其它函数名字过一遍
4、聚合分组函数的使用了解
①select后面得列或者出现在group by中，或者加上聚合函数
select c1,c2,sum(c3),count(c4)from t1group by c1,c2;
②help contents;
查看聚合函数help Functions and Modifiers for Use with GROUP BY;
AVG、MAX、MIN、SUM、COUNT、COUNT DISTINCT、GROUP_CONCAT、BIT_AND、BIT_OR、BIT_XOR
四十九、隐式类型转换，要避免隐式类型转换
1、最常用的几个数据类型：数字、字符串、日期时间
2、字符串里面可以存放数字和日期，但是在设计表的时候，要注意不要将日期和数字列设计成字符串列
3、对于字符串列的比较，一定要加上引号：
mysql> select * from t where name_phone=’1301110001′;
五十、limit使用很频繁，注意其使用方法
1、limit使用的场合
从结果集中选取最前面或最后面的几行
2、limit配合order by使用
3、MySQL5.7 doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
五十一、in、not in、exists、not exists、left join、distinct join互相转换
1、in和exists可以互相转换
select * from players a where a.teamno in (select teamno from team where teamname='骑士队');select * from players a where exists (select 1 from team b where a.teamno=b.teamno and b.teamname='骑士队');
2、not in和not exists可以互相转换
3、not in、not exists可以转换成left join
select * from 学生信息 a where a.stuno not in (select stuno from 选课信息表);select * from 学生信息 aleft join 选课信息 bon a.stuno=b.stunowhere b.成绩 is null;
4、in、exists可以转换成distinct join
select * from 学生信息 a where a.stuno in (select stuno from 选课信息表 b);select * from 学生信息 a where exists (select 1 from 选课信息 b where a.stuno=b.stuno);select distinct a.*from 学生信息join 选课信息 bon a.stuno=b.stuno;
五十二、连接的具体使用含义
1、理解为什么会出现表连接：查询的列来自于多个表
select 列from ..where 列group by 列having 列order by 列limit x
2、理解表连接的书写方式
join一个表、on一个条件
3、理解表连接的注意条件
①两个表要连接一定要存在主外键关系(有可能需要第三张表协助关联)
实际上存在外键约束
存在外键列，但是没有外键约束
②防止扇形陷阱(两个表需要关联，但是没有直接主外键，借助第三个表进行关联，但是存在扇形问题，此时不能借助第三个表进行关联)
示例：学院表、专业表、学生表
学院实体和专业实体之间是一对多的联系；
学院实体和学生实体之间也是一对多的联系；
而学生和专业之间没有联系；
如果学生和专业通过学院表进行关联，就会出现扇形问题。
4、外连接：左外连接、右外连接
外连接是为了防止出现某一个表的数据被遗漏
开发人员非常喜欢使用外连接.
五十三、子查询
1、子查询可能出现的位置
①select from之间可能会出现子查询
②from后面
③join后面可能会出现子查询
④where后面可能会出现子查询
⑤having后面可能会出现子查询
2、尽最大程度的不要使用子查询
3、相关子查询、无关子查询
相关子查询特别容易出现在select from之间、where后面
相关子查询不能独立执行，子查询执行次数取决于父查询返回的行数
无关子查询可以独立执行，子查询执行一次
五十四、子查询出现的场合
1、where中出现的子查询，一般可使用表连接进行改写
①select 列(涉及到A表，没有涉及到B表)
②where 条件(涉及到B表)
2、from后面的子查询
①对于取出来的数据再次进行复杂的处理
例如分组聚合、having条件、where条件等
②对一个结果集再次进行复杂的查询
意味着我们取数据的这个过程中，对数据进行处理的力度很复杂
3、select from之间的子查询
对于返回的每一行数据，select和from之间的子查询都要执行一次
select后面的列要进行复杂的处理，如果这个处理涉及到另外一个表，若这个表很可能没有出现在from和join里面，则进行子查询：
示例：将每一个同学的成绩列出来，同时计算他的成绩和本组平均成绩的差距
select 学生成绩,学生成绩-(select avg(成绩) from 选课表 a where a.组ID=b.组ID)from 选课表 b;
五十五、select执行的顺序
select ...from ...join ...on ...where ...group by ..having ...order by ...
1、先从表中取数据，访问innodb buffer pool
from …
join …
on …
where
2、分组、聚合，数据已经进入用户工作空间
group by …
having …
3、select ….：取列数据
4、order by：排序输出
五十六、集合操作
union：结果集去重
union all：结果集不去重
五十七、insert增
1、insert values一条数据
表的名字后面最好加上列的名字
2、insert values多条数据
3、insert into select
select可以非常复杂，语法完全就是select
五十八、update改
基本格式：update 一个表 set 列 where 列条件;
1、一定要带上where条件
2、update分为下面的几个步骤操作
①找到需要update的数据，此操作取决于where条件
where条件可以是一个复杂的where条件，比如是一个子查询
示例：将平均成绩75分以上的学生的级别设置为优等生
update 学生信息表 aset grade=‘优等生’where a.stuno in (select b.stuno from 成绩表 b group by b.stuno having avg(成绩)>=75);
②set后面的列，也可以很复杂，比如是一个相对子查询
UPDATE players_data pdSET number_mat = ( SELECT count(*) FROM matches m WHERE m.playerno = pd.playerno), sum_penalties = ( SELECT sum(amount) FROM penalties pen WHERE pen.playerno = pd.playerno);
3、update可以改写成一个select语句
把1和2改写成一个select语句，不要对一个update在生产里面直接进行优化
4、update可以使用order by，数据按照顺序进行更新
5、update可以使用limit，限制每次更新的行数
五十九、replace替代已有的行
使用场合insert+update，两个表数据合并到一起
六十、delete删
1、绝大多数情况下需要加上where条件
2、where条件可以很复杂，例如是一个子查询
3、理解delete和truncate的区别
truncate：清空全部数据、速度快、释放空间(不删表)
delete：全部或者部分删除数据、速度慢、不释放空间
六十一、临时表
1、只是针对当前会话有效，临时表和数据都存储在用户工作空间
2、临时表的使用很消耗资源
①create、insert、drop，因此在非常频繁的查询环境下，不宜使用临时表；
②临时表需要使用用户工作空间，临时表中存在的数据不易过多，否则容易出现磁盘临时表；
3、临时表的使用场合
需要暂存结果集数据，后面的操作需要访问这些暂存结果集，主要是为了可读性。
4、有一种误区一定要注意，一定不要将普通表作为临时表来使用
原因：普通表当做临时表来使用，下面的操作需要手工去做
①create、insert、truncate或者drop
②对于普通表的所有操作都会产生redo(事务)，非常消耗资源
六十二、关于约束
1、非空
2、default约束
3、主键约束
4、外键约束
5、SET、ENUM约束
约束注意点：
①尽量选择列都为非空
②对于bool、时间列经常会出现default约束
③每一个表尽最大程度要有主键
④唯一键可以有多个，唯一键可以有空值
⑤外键列一般会有，但是外键约束不建议使用，在应用层面保证主表和外表的一致性
⑥合理使用set和enum约束，提升数据的质量
⑦外键约束中on delete、update，尽量不要设置级联删除操作(很危险！！！)
六十三、表的DDL
1、极其严肃的一个动作
2、使用help书写DDL语句
3、ddl动作的后遗症和危险性
①影响I、D、U、S
②长时间锁表、产生海量IO
4、测试DDL的影响范围—优化对象
①锁表时间
②IO情况
③具体测试要求
示例：产生一个500万行的表(写一个存储过程实现)，对表进行增加列、删除列、修改列的名字、将列的长度变长、将列的长度变短
mysql> delimiter $$mysql> create procedure do_big(x int) -> begin -> declare v int; -> set v=x; -> create table test(test_num int auto_increment not null primary key); -> while v>0 do -> insert into test values(null); -> set v=v-1; -> end while; -> end $$mysql> delimiter ;mysql> call do_big(5000000);……mysql> select count(*) from test;+----------+| count(*) |+----------+| 5000000 |+----------+
看一下上面的这些操作，哪些操作时间长、哪些操作时间短，并对其进行初步的原理分析
mysql> insert into test values(123456789);mysql> delete from test where test_num=123;mysql> alter table test CHANGE COLUMN -> test_num -> test_id int(10) not null auto_increment;mysql> alter table test modify test_id int(100);mysql> alter table test modify test_id int(20);
总结：对于一个大表而言，将列的长度变长时间是最长的，其他的操作处理时间都还挺短。
六十四、视图的最佳实践
1、视图就是select的一个名字
2、不建议使用复杂视图
select语句里面不要带有distinct、group by、聚合函数、union等操作
3、不建议在视图中嵌套视图
4、视图的主要使用场合
统一访问接口(select)—主要的好处
规范访问
隐藏底层表结构、ddl不影响应用访问
5、视图在安全方面的意义
六十五、存储过程(脚本)
1、存储过程使用的场合
①重复性很高的复合操作(dml)
②统一访问接口(dml、事务)
③批量业务(跑批)
2、存储过程结构分析
①存储过程中嵌入了dml、select
②存储过程有参数，参数的不同会产生不同的事务
in、out、inout
③存储过程里面有结构化语句，即流程控制语句：
循环
条件判断
使得在执行dml、select的时候，变得方便
④存储过程可以定义变量
select取出来的结果可以存储到变量中
dml需要的输入值可以通过变量来实现
⑤存储过程里面可以有游标，游标的核心就是可以对一个结果集进行处理
1)定义游标(游标和一个select关联)
2)打开游标(将select的结果赋给游标，可以是N行列)
3)遍历游标(一行行数据获取，每一行数据赋给N个变量)
4)关闭游标
⑥存储过程有异常处理部分
1)异常处理是一个存储过程是否可以产品化、商业化很重要的一个标志
2)异常处理只关心SQL语句的异常
每一个存储过程都要对着三类SQLWARNING、NOT FOUND、SQLEXCEPTION进行处理；
存储过程异常处理通常只是进行错误的记录，或者空处理。
⑦存储过程书写过程
1)定义一个结构
存储过程基本结构
参数
异常处理
2)书写涉及到SQL语句
3)考虑使用变量、游标、条件判断、循环将SQL语句组合起来
4)经常使用begin end来将一组SQL语句或者语句组合起来，作为一个语句来出现
3、存储过程安全方面的意义：防止对底层表直接进行dml
六十六、自定义函数
1、自定义函数和存储过程的区别
①有一个返回值
CREATE FUNCTION SimpleCompare(n INT, m INT)RETURNS VARCHAR(20)……
②调用的时候必须放在=的右边
set @ax = SimpleCompare(1,2);
2、整理笔记，将函数定义和函数调用整理一个例子出来
六十七、触发器
1、尽量少使用触发器，不建议使用
2、触发器是一个begin end结构体
3、触发器和存储过程的唯一区别就是在于被执行方式上的区别
存储过程需要手工去执行
触发器被DML自动触发
4、触发器被触发的条件
①for each row(每一行都被触发一次，这就决定了频繁dml的表上面不要有触发器)
②增删改都可以定义触发器
③before、after可以定义触发的时机
5、触发器中经常使用new、old
insert里面可以有new
delete里面可以有old
update里面可以有new、old
6、使用触发器的场合
一般用来进行审计使用：产品价格表里面的价格这个列，只要是有人对这个表的这个列进行更新，就要保存修改前和修改后的值，将这个信息记录到一个单独的表中(审计表)
7、要求你将触发器的例子保存到笔记中
①insert触发器（new）
②delete触发器（old）
③update触发器（new、old）
④before、after
六十八、event
1、周期性执行
①linux里面的at、crontab
②MySQL里面的event
2、event的核心知识点
①执行一次
CREATE EVENT myeventON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTEDO begin UPDATE t1 SET mycol = mycol + 1; end
②周期性执行
CREATE EVENT myeventON SCHEDULE EVERY 1 DAY STARTS STR_TO_DATE(‘2017-05-01 20:00:00’,'yyyy-mm-dd hh24:mi:ss')DO begin UPDATE t1 SET mycol = mycol + 1; end
评论前必须登录！
立即登录 注册