MySQL开发心得笔记超详细及SQL语法考核
一、理解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下各个对象都不区别大小写
②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 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建立用户
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’
如果不给sql_mode参数加入PIPES_AS_CONCAT的话，|| 默认是or的意思，查询结果是一列显示是1。
三十五、使用mysql> help functions; 学习MySQL各类函数
三十六、常见功能函数
1、upper(……)、lower(……)大小写变换
2、user()查看登录用户、current_user()查看当前用户
3、database()查看使用的数据库