[TOC]
## DDL语言
1、进入mysql的指令：
mysql -h 127.0.0.1 -P 3306 -u root -p敲回车 //注意：端口是大写的P，密码是小写的p
这时候输入密码
2、mysql> set password for root@localhost = password('新密码'); //修改密码。root@localhost是用户名和主机
mysqladmin -uroot -p'123456' password 'root' //新密码要和password中间空个格。这种是不进入mysql执行的
3、退出用exit或quit
### 操作库
1、show databases; //显示数据库
2、create database name; //创建数据库
3、use databasename; //选择要打开的数据库
4、drop database name //直接删除数据库，不提醒
5、mysqladmin drop databasename //删除数据库前，有提示。
6、flush privileges //刷新数据库
7、show engines\G; //数据库支持的所有引擎
### 操作表
1、创建表
CREATE TABLE 表名(
字段名 数据类型 其他关键词,
.....
);
2、show tables; //显示表
3、show tables like'%tablename%' //模糊查询表名
4、desc 表名; //查看表结构
rename命令格式：rename table 原表名 to 新表名; //修改表名
5、 show table status\G; //查看表的状态
5、show create table 表名; //查看表的引擎
5、【修改表结构：ALTER TABLE 表名】
ALTER TABLE 表名 MODIFY 字段名 VARCHAR(255); //修改列的数据类型
如果同时需要修改列名和数据类型，则可以先修改数据类型再来修改列名，即执行上述sql语句，俩遍。
增加列：
ALTER TABLE 表名 ADD 新字段名 数据类型 关键词 位置（放到哪个列后面）
mysql> alter table tp5_user add sex tinyint unsigned not null default 0 comment '0男1女' after age; //将新字段，放到age后面
ALTER TABLE 表名 DROP 字段名 //删除列
alter table 表名 engine=innodb; //修改表引擎
alter table 表名 add primary key(字段名); //添加主键
alter table 表名 drop 字段名; //删除索引
6、truncate table 表名 //这样不但将数据全部删除，而且重新定位自增的字段
7、DROP TABLE 表名 //删除表
8、复制表结构及数据到新表
CREATE TABLE 新表
SELECT * FROM 旧表
9、只复制表结构到新表
CREATE TABLE 新表
LIKE 旧表
10、复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表
SELECT * FROM 旧表
11、复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,…….)
SELECT 字段1,字段2,…… FROM 旧表
## DML和DQL语言
### sql语句的增删改查
INSERT INTO 表名 (字段1,字段2) VALUES (值1,值2); //【增】
DELETE FROM 表名 WHERE条件; //【删】
UPDATE 表名 SET 字段=新值 WHERE条件; //【改】
SELECT * FROM 表名; //【查】
点击查看常用函数
点击查看常用关键字
使用关键字
1、group by 字段名 [having 条件表达式][with rollup] //分组
例子1：与聚合函数一起使用
先以性别进行分组，在聚合统计男、女各有多少人
select count(*) as totel,sex from tp5_user group by sex;
例子2：与group_concat()函数一起使用
select gradeName,group_concat(stuName) from t_student group by gradeName;
例子3：与HAVING一起使用
select sex from tp5_user group by sex having count(*) > 100; //在group中执行搜索，大于100的。having
有多个条件，用and 或 or 相连
例子4：与with rollup 一起使用（最后加入一个总和行）
注意：使用with rollup 后，不能再使用 ORDER BY 语句对结果集进行排序
select gradeName,group_concat(stuName) from t_student group by gradeName with rollup;
2、去重
以name去除重复的行，注意：只能写一个字段，否则去重会失效（所以这个demo去重不成功）
SELECT distinct name,age from yii_user
3、连接查询
语法：SELECT 哪些字段 FROM 表1 用哪种连 表2 ON 连表条件
select * from score,student where score.id=student.id -- 相当于inner join
select * from score join student on (score.id=student.id) -- 默认的join是inner join
select * from score join student using(id) where student.id=2 -- using函数等同于on，参数传 关联字段(可以带表名也可以不带)
-- A表中有，B表中没有
select * from score as a left JOIN student as b on a.id=b.id where b.id is null
select * from score as a where a.id not in (SELECT id from student)
select * from score as a where not exists (select 1 from student as b where a.id=b.id) -- 用not EXISTS的话，要加关联字段
注意：
在使用left jion时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
3、全连和内连，一定要写on或where，否则会出现笛卡尔积（a表的总行数*b表的总条数）
如这种 select * from score INNER JOIN student -- 或不写inner join 或写 full join都会出现这种情况
内连（推荐）内连是查条件全相等的:
SELECT `a`.`id`,`a`.`title`,`a`.`content`,`c`.`cat_name` FROM `tp5_article` `a` INNER JOIN `tp5_cat` `c` ON `a`.`cat_id`=`c`.`id` ;
//5表相连，模糊查询默认是为空。from前只写你要输出的字段即可，不用写字段的关联条件
SELECT
a.CREATE_TIME,a.remark,a.nextFollowupTime,
b.id,b.name as resource_name,
c.name as input_name,
d.name as grade_name,
e.name as school_name
FROM
customer_folowup AS a
INNER JOIN customer AS b ON a.CUSTOMER_ID = b.id
INNER JOIN user as c on a.CREATE_USER_ID=c.USER_ID
INNER JOIN data_dict as d on b.pointial_student_grade_dict=d.id
INNER JOIN organization as e on b.BL_SCHOOL=e.id
where d.institution_id=2 and d.STATE=0 and c.name like '%%'
order by a.id asc
limit 100
补充：MySQL STRAIGHT_JOIN 与 NATURAL JOIN的使用
长话短说：straight_join实现强制多表的载入顺序，从左到右，如：
...A straight_join B on A.name = B.name
straight_join完全等同于inner join 只不过，join语法是根据“哪个表的结果集小，就以哪个表为驱动表”来决定谁先载入的，而straight_join 会强制选择其左边的表先载入。
往往我们在分析mysql处理性能时，如(Explain),如果发现mysql在载入顺序不合理的情况下，可以使用这个语句，但往往mysql能够自动的分析并处理好。
左连：左表里有多少条就打印出来多少条，匹配不上的，右表字段显示null
select a.*,b.* from score as a LEFT JOIN student as b on a.id=b.id
右连：
select score.*,student.* from score right join student on score.id=student.id
全连：完整外部联接返回左表和右表中的所有行（有的版本不支持，可以使用left join union right join）
交叉连接：俩表的行数相乘
SELECT a.*,b.* FROM score as a CROSS JOIN student as b on a.id=b.id -- 不论写不写cross join,如果不加on条件都是笛卡尔乘积
4、联合查询：俩条sql语句通过UNION ALL连接在一起。从上到下依次执行
语法：Union [union选项]
All: 保留所有(不管重复)；Distinct: 去重(整个重复): 默认的
场景：
从多个表中查询出相似结构的数据，并且返回一个结果集
从单个表中多次SELECT查询，将结果合并成一个结果集返回。
SELECT title,content FROM `tp5_article` UNION ALL SELECT * FROM `tp5_cat`;
注意：列的数量必须相等
where和Union的区别：这里Union All可以返回重复的数据，就是where子句完成不了的工作
order by 字段，必须是2个sql中都有的
union 去重；union all不去重
5、子查询：括号里sql的结果，是括号外sql的where条件。从里到外依次执行（连表比子查询效率高）
语法：.带in关键字查询：select 字段1，字段2 frome 表名 where 字段 [not]in(元素1，元素2)；
按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置
From子查询: 子查询跟在from之后
SELECT people.name,people.chinese,people.math,people.english FROM (SELECT name,chinese,math,english FROM tb_demo071) AS people
Where子查询: 子查询出现where条件中
SELECT * FROM mark where id in(2,5); //查id等于2或5的
SELECT id FROM `student` where id in(select stu_id from mark); //查学生表里有成绩的
Exists子查询:
子查询查询到记录（存在），则进行外层查询，否则，不执行外层查询。还有个not exists
-- EXISTS:如果存在身高大于2米的人，列出表中所有数据。否则 不输出。因为exists返回的是bool值
select * from student where EXISTS (select * from student where height >= '200')
按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)
标量子查询: 子查询得到的结果是一行一行的数据
列子查询: 子查询得到的结果是一列多行
-- 列子查询：找出语文和数学都及格人的名字（只输出名字，所以叫一列多行）
-- 换种思路，先找出小于60分的，在pass掉小于60分的人（只要这个人有一科低于60分，那他另一科的成绩也就挂了）就是语文和数学都及格的了
select name from score where name not in (select name from score where score<60) -- 前提是 语文和数学是一个字段
3.行子查询: 子查询得到的结果是多列一行
带比较运算符的子查询（子查询可以使用比较运算符）
-- 行子查询：找出年龄最大且身高最高的人
-- 换种思路：一个条件一个条件的取最大数
select * from student where age = (select max(age) from student) and height = (select max(height) from student)
select * from student where (age,height)=(select max(age),max(height) from student)
4.表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)
-- 表子查询：找出每个人最好的成绩
-- 思路：先以人分类，相同名字的凑成一组，在根据分数分组后倒序，这样张三的最高分就在上面了-》在拿名字分组，此时相同名字他取第一个
select * from (select * from score GROUP BY name,score desc) as a GROUP BY name
例子1：any 或 some
any 或 some关键字是同义词，表示满足其中任何一个条件即可，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件
例子4、带any关键字的子查询（any关键字表示满足其中任一条件）
select * from t_book where price>= any(select price from t_priceLevel);
例子5、带all关键字的子查询（all关键字表示满足所有条件）
select * from t_book where price>= all(select price from t_priceLevel);
6、limit 从第几个开始取，取多少条
7、带like的模糊查询：select 字段1，字段2... frome 表名 where 字段 [not] like '字符串'；
“%”代表任意字符； “_"代表单个字符；
select * frome t_student where stuName like '张三''； //完全等于张三的
select * frome t_student where stuName like '张三%''； //右匹配，找以张开头的
select * frome t_student where stuName like '%张三%''； //含有张三的任意字符
select * frome t_student where stuName like '张三_''
8、空值查询：select 字段1，字段2...frome 表名 where 字段 is[not] null; //null比较特殊，详情点击查看
9、带between and的范围查询：select 字段1，字段2 frome 表名 where 字段 [not]between 取值1 and 取值2；
select * frome t_student where age between 21 and 29;
select * frome t_student where age not between 21 and 29;
SELECT * FROM `yii_msg` where speak_time >= '' and speak_time <= '' //或用 >= <=
10、HAVING 可以单独使用
-- HAVING 要放在最下面，原因1：having是对查出来的结果在筛选，而where是查之前进行筛选；
-- 原因2：having支持通过字段别名（mini_contract就是别名），进行筛选，where只支持原来的字段名
HAVING 1 = 1
## DCL语言
### 授权
注意：和权限相关的，每次做完都要刷新权限 flush privileges;
1、创建用户
create user '用户名' @'指定ip访问' identified by '密码';
create user 'test' @'127.0.0.1' identified by '123456';
2、给新用户赋予权限
grant 权限（增删改查关键字） on 数据库名.* to '用户名' @'指定ip访问';
grant update,insert,delete,select on test.* to 'test' @'127.0.0.1';
3、撤销权限
revoke 权限 on 数据库名.* from '用户名' @'指定访问ip';
revoke select on test.* from 'test' @'127.0.0.1';
注意：撤销后刷新完权限后，要先quit退出在重新登录才生效
4、给所有的数据库用*.*，给所有的权限用ALL
案例：创建用户firstdb(密码firstdb)和数据库，并赋予权限于firstdb数据库
mysql> create database firstdb; //创建数据库
mysql> grant all on firstdb.* to firstdb identified by 'firstdb'; //会自动创建用户firstdb。mysql默认的是本地主机是localhost,对应的IP地址就是127.0.0.1，所以你用你的IP地址登录会出错，如果你想用你的IP地址登录就要先进行授权用grant命令。
mysql>grant all on *.* to firstdb@localhost identified by '123456'; //grant 与on 之间是各种权限，例如:insert,select,update等
on 之后是数据库名和表名,第一个*表示所有的数据库，第二个*表示所有的表
firstdb可以改成你的用户名，@后可以跟域名或IP地址，identified by 后面的是登录用的密码，可以省略，即缺省密码或者叫空密码。
5、查看权限
show grants for '用户名'@'指定访问ip'\G;
show grants for 'test'@'127.0.0.1'\G;
6、删除用户
drop user '用户名'@'指定访问ip'; //注意：要和表中的对应上
drop user 'test'@'127.0.0.1';
## DTL事务控制语句
start transaction; //开启事物
执行sql....
commit; //没问题，提交
rollback; //有问题，回滚
### 存储过程
1、定义存储过程
mysql> create procedure test(a int)
-> begin
-> select * from tp5_user where id=a;
-> end///
Query OK, 0 rows affected (0.01 sec)
2、调用存储过程（使用call关键字调用 存储过程的名字）
call test(1)///
3、删除存储过程
drop procedure test///
4、查看存储过程
show procedure status///
### 视图
总结：相当于是as后面得sql结果存到了test_view表中（show tables;就能看出来），然后使用视图的时候查test_view表
1、创建
create view 视图名(视图名中定义的字段别名) as SQL语句;
mysql> create view test_view(view_name,view_fee) as select name,fee from tp5_user where id=6;
2、使用
select 视图名中定义的字段别名 from 视图名;
select view_name from test_view;
3、查看
show create view 视图名\G;
show create view test_view\G;
4、删除
drop view 视图名;
drop view test_view;
### 触发器
总结：相当于php框架中的钩子
1、创建
mysql> create trigger 触发器名称 关键字1 关键字2 on 表名 for each row
-> begin
-> update total_num set num=num-1 where type=1;
-> end///
mysql> delimiter ///
mysql> create trigger delete_total_num after delete on article for each row
-> begin
-> update total_num set num=num-1 where type=1;
-> end///
2、查看
show create trigger 触发器名称\G;
show create trigger delete_total_num\G;
3、删除
drop trigger 触发器名称;
drop trigger delete_total_num;
其他
1、select version(),current_date; //显示当前mysql版本和当前日期
2、SELECT User, Host, Password FROM mysql.user; //在Mysql中显示所有用户（mysql库中的user表）
3、SELECT DISTINCT User FROM mysql.user; //显示去重后的用户（其实只是加了个关键字修饰sql语句）
注意：在Mysql中其实有一个内置且名为mysql的数据库，这个数据库中存储的是Mysql的一些数据，比如用户（user）、权限信息、存储过程等，所以呢，我们可以通过如下简单的查询语句来显示所有的用户呢
4、 delimiter 结束符 //修改sql语句的结束符
编码相关
乱码：只有win的cmd才会出现，是因为cmd的编码是gbk（改不了的）改mysql的....
1.查看数据库编码格式
1
mysql> show variables like 'character_set_database';
2.查看数据表的编码格式
1
mysql> show create table <表名>;
3.创建数据库时指定数据库的字符集
mysql>create database <数据库名> character set utf8;
4.创建数据表时指定数据表的编码格式
create table tb_books (
name varchar(45) not null,
price double not null,
bookCount int not null,
author varchar(45) not null ) default charset = utf8;
5.修改数据库的编码格式
mysql>alter database <数据库名> character set utf8;
6.修改数据表格编码格式
mysql>alter table <表名> character set utf8;
7.修改字段编码格式
mysql>alter table <表名> change <字段名> <字段名> <类型> character set utf8;
mysql>alter table user change username username varchar(20) character set utf8 not null;
导入导出数据库
注意：mysqldump和mysql指令是在未登录未进入的情况下使用的
1.导出整个数据库
mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的
文件名(数据库默认编码是latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql #>后面不加目录名，是导出到你当前所在的目录中
2.导出一个表
mysqldump -u 用户名 -p 密码 数据库名 表名> 导出的文件名
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
mysqldump -u wcnc -p -d -add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
-d 没有数据 -add-drop-table 在每个create语句之前增加一个drop table
4.1、先登录后-》导入数据库
mysql -u root -p //进入mysql数据库控制台
mysql>use 数据库 //选择数据库
mysql>source wcnc_db.sql //使用source命令，后面参数为脚本文件(如这里用到的.sql)
4.2、不登录-》使用mysql命令，导入数据库
mysql -u 用户名 -p 数据库名< 要导入的sql文件
mysql -u root -p test < /user.sql #然后敲回车，输入密码
自定义变量
-- 自定义变量，计算行号
SELECT
@row := @row + 1 as 行号, -- 迭代器+1
fenxiao.*
FROM fenxiao, (SELECT @row := 0) t -- 虚个表，将变量置位0
WHERE @row < 8 -- 输出行号<8的数据
自定义函数
-- 自定义函数 之 字符串替换（函数体中必须以分号结尾）
DELIMITER $$ -- 修改结束符
DROP FUNCTION IF EXISTS `test`.`getdate`$$ -- 如果test库有getdate函数，就先将该函数删除
CREATE FUNCTION `test`.`getdate`(gdate datetime) RETURNS varchar(255) -- gdate是形参，datetime是数据类型；返回字符串
BEGIN -- 函数体开始,相当于{
DECLARE x VARCHAR(255) DEFAULT ''; -- 定义变量，默认是空
SET x= date_format(gdate,'%Y年%m月%d日%h时%i分%s秒'); -- 给变量赋值
RETURN x; -- 返回 结果，x的类型要和 returns 时的一样
END $$ -- 函数体开始,相当于}
DELIMITER;
select getdate('2018-07-23 15:47:56'); -- 调用函数
-- 自定义函数 之 if判断（将字符串s保留前n位）
DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`cutString` $$
CREATE FUNCTION `sp_test`.`cutString`(s VARCHAR(255),n INT) RETURNS varchar(255)
BEGIN -- 函数体开始
IF(ISNULL(s)) THEN RETURN ''; -- 判断开始
ELSEIF CHAR_LENGTH(s)<n THEN RETURN s; -- 小于n位直接返回
ELSEIF CHAR_LENGTH(S)=n THEN RETURN '相等';
ELSE RETURN CONCAT(LEFT(s,n),'...'); -- 大于n位，先截取，在拼接上...
END IF; -- 判断结束
END $$ -- 函数体结束
DELIMITER ;
-- 自定义函数 之 循环
DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`morestar`$$
CREATE FUNCTION `test`.`morestar`(n INT) RETURNS text -- 返回长文本
BEGIN
DECLARE i INT DEFAULT 0; -- 定义变量，默认值是0
DECLARE s TEXT DEFAULT ''; -- 定义变量
myloop:LOOP -- 循环开始
SET i=i+1; -- 每次循环+1
SET s = CONCAT(s,'*'); -
IF i > n THEN LEAVE myloop; -- 如果i 大于 传进来的n 就结束循环
END IF;
END LOOP myloop; -- 循环结束
RETURN s;
END $$
DELIMITER ;
SELECT morestar(5); -- 调用
