1. 基本操作
1.1 創(chuàng)建表：create table ...
# 創(chuàng)建用戶表t_user
mysql> create table t_user(
-> id bigint(20) primary key,
-> name varchar(20),
-> mobile varchar(20) comment '手機(jī)號(hào)',
-> username varchar(20) unique not null,
-> passwd varchar(100) not null,
-> create_time datetime comment '創(chuàng)建時(shí)間',
-> last_login datetime comment '上次登錄時(shí)間'
-> );
# 查看用戶表（查看簡(jiǎn)要信息可以用 desc TABLE）
mysql> show full columns from t_user;
+-------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+--------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+--------------------+
| id | bigint(20) | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| mobile | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 手機(jī)號(hào) |
| username | varchar(20) | utf8_general_ci | NO | UNI | NULL | | select,insert,update,references | |
| passwd | varchar(100) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
| create_time | datetime | NULL | YES | | NULL | | select,insert,update,references | 創(chuàng)建時(shí)間 |
| last_login | datetime | NULL | YES | | NULL | | select,insert,update,references | 上次登錄時(shí)間 |
+-------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+--------------------+
# 創(chuàng)建角色表
mysql> create table t_role (
-> id int primary key,
-> role varchar(20) nuique
-> );
# 查看角色表
mysql> show full columns from t_role;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | |
| role | varchar(20) | utf8_general_ci | YES | UNI | NULL | | select,insert,update,references | |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
1.2 修改：alter ...
# 增加字段
mysql> alter table t_user add column role_id int after id;
mysql> alter table t_role add column remark varchar(50) after `role`;
# 調(diào)整字段
mysql> alter table t_user change column name name varchar(20) comment '姓名' after passwd;
# 刪除字段
mysql> alter table t_role drop column remark;
1.3 插入：insert into ...
# 向t_user插入單條
mysql> insert into t_user (id,name,mobile,username,passwd,create_time) values (10001,'用戶1','13900000001','user1','123456',now());
# 向t_user插入多條
mysql> insert into t_user (id,username,passwd) values (10002,'user2','123456'),(10003,'user3','123456');
# 向t_role插入多條
mysql> insert into t_role (id,role) values (1,'管理員'),(2,'VIP'),(3,'普通用戶');
1.4 更新：update ... where
# column = value
mysql> update t_user set last_login=now() where id=10001;
# and / like / is null
mysql> update t_user set role_id = 2 where username like '%user%' and role_id is null;
1.5 查詢：select ... where
select.png
# 無where
mysql> select id,username,name from t_user;
+-------+----------+---------+
| id | username | name |
+-------+----------+---------+
| 10001 | user1 | 用戶1 |
| 10002 | user2 | NULL |
| 10003 | user3 | NULL |
+-------+----------+---------+
# where name is not null
mysql> select * from t_user where name is not null;
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+
| id | role_id | mobile | username | passwd | name | create_time | last_login |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+
| 10001 | 1 | 13900000001 | user1 | 123456 | 用戶1 | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+
# where role_id in (2,3)
mysql> select id,username from t_user where role_id in (2,3);
+-------+----------+
| id | username |
+-------+----------+
| 10002 | user2 |
| 10003 | user3 |
| 10004 | user4 |
+-------+----------+
# distinct
mysql> select distinct role_id from t_user;
+---------+
| role_id |
+---------+
| 1 |
| 2 |
+---------+
1.6 刪除：delete ...
mysql> delete from t_user where id = 10003;
Query OK, 1 row affected (0.01 sec)
1.7 排序：order by ... asc|desc
order by 后不可以加desc反向排序，asc或不加修飾則為正向排序。
mysql> mysql> select id,username,name from t_user order by id desc;
+-------+----------+---------+
| id | username | name |
+-------+----------+---------+
| 10004 | user4 | NULL |
| 10003 | user3 | NULL |
| 10002 | user2 | NULL |
| 10001 | user1 | 用戶1 |
+-------+----------+---------+
4 rows in set (0.00 sec)
1.8 limit
# limit n
mysql> select id,username from t_user limit 2;
+-------+----------+
| id | username |
+-------+----------+
| 10001 | user1 |
| 10002 | user2 |
+-------+----------+
# limit m,n
mysql> select id,username from t_user limit 1,2;
+-------+----------+
| id | username |
+-------+----------+
| 10002 | user2 |
| 10003 | user3 |
+-------+----------+
2 rows in set (0.00 sec)
1.9 分組查詢 group by ... having
這里需要注意下having與where的區(qū)別：
having可以用在group by之后對(duì)分組查詢的結(jié)果進(jìn)行篩選;
where可以用在group by之前腐泻，對(duì)分組前的數(shù)據(jù)進(jìn)行篩選;
where ... group by ... having ...這樣的形式是允許的，它做了2次篩選吃衅。
# 查詢各角色的用戶數(shù)量
mysql> select count(1) as role_users,role_id from t_user group by role_id;
+------------+---------+
| role_users | role_id |
+------------+---------+
| 1 | 1 |
| 3 | 2 |
+------------+---------+
# group by ... having
mysql> select count(1) as role_users,role_id from t_user group by role_id having role_users > 1;
+------------+---------+
| role_users | role_id |
+------------+---------+
| 3 | 2 |
+------------+---------+
1 row in set (0.00 sec)
1.10 外鍵約束 FOREIGN KEY
說實(shí)話這玩意兒N年前用得比較多，尤其是喜歡在數(shù)據(jù)庫里面做編程的老程序員腾誉，用上外鍵可以保證數(shù)據(jù)準(zhǔn)確性和一致性徘层。但近幾年開發(fā)中傾向于使用邏輯上的外鍵約束，即不指定FOREIGN KEY但通過程序和代碼來保證數(shù)據(jù)一致性利职。為什么趣效？因?yàn)樽饔貌淮螅姨貏e難用猪贪，特別是維護(hù)的時(shí)候跷敬，純粹給自己找麻煩。
這里就介紹下概念吧热押，實(shí)在不會(huì)還可以通過圖形界面工具來處理不是嗎西傀？（比如：workbench、navicat等等）
首先需要提醒的是：存在外鍵約束關(guān)系的兩表必須都是InnoDB引擎桶癣，MyISAM不支持外鍵拥褂。另外，外鍵列的數(shù)據(jù)類型與外鍵表主鍵的數(shù)據(jù)類型需要一致牙寞。
# 創(chuàng)建表時(shí)添加約束
mysql> create table t_user(
-> id bigint(20) primary key,
-> ...
-> role_id int,
-> constraint `fk_role_id` foreign key (`role_id`) references `t_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-> );
# 注意下最后的 ON DELETE饺鹃、ON UPDATE，這是外鍵約束參照
# 外鍵約束參照（如果沒有指定ON DELETE或者ON UPDATE间雀，默認(rèn)的動(dòng)作為RESTRICT）
CASCADE：從外鍵表更新或刪除數(shù)據(jù)自動(dòng)更新或刪除當(dāng)前表的外鍵約束列悔详；
SET NULL：從外鍵表更新或刪除數(shù)據(jù)自動(dòng)將當(dāng)前表的外鍵約束列設(shè)為NULL（需要保證此列沒有指定NOT NULL）；
RESTRICT：拒絕外鍵表更新或刪除雷蹂；
NO ACTION：在MySQL中同RESTRICT。
# 查看外鍵約束
show create table 表名杯道；
# 創(chuàng)建表以后添加外鍵約束
ALTER TABLE `表名` ADD CONSTRAINT `外鍵約束名` FOREIGN KEY ( `外鍵列` ) REFERENCES `外鍵表` ( `主鍵` ) ON DELETE CASCADE ON UPDATE CASCADE匪煌；
# 刪除外鍵
ALTER TABLE `表名` DROP FOREIGN KEY `外鍵約束名`责蝠；
1.11 其它操作
基本操作上述內(nèi)容基本夠用了，當(dāng)然MySQL支持的命令遠(yuǎn)不止這些萎庭，不過有些不常用霜医，僅給出命令格式不演示了。
# 修改列屬性
alter table `表名` modify `列名` varchar(20) default '無' not null;
# 修改列名及屬性）
alter table `表名` change column `列名` `新列名` 屬性（可省略驳规，表示不修改屬性）;
# 修改表名
alter table 表名 rename to 新表名;
# 查看所有約束
select * from information_schema.`TABLE_CONSTRAINTS` where TABLE_SCHEMA = '數(shù)據(jù)庫名' and TABLE_NAME = '表名';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2. 子查詢
# 查詢用戶及其角色
mysql> select id,username,name,(select role from t_role where id = t_user.role_id) as role from t_user;
+-------+----------+---------+-----------+
| id | username | name | role |
+-------+----------+---------+-----------+
| 10001 | user1 | 用戶1 | 管理員 |
| 10002 | user2 | NULL | VIP |
| 10003 | user3 | NULL | VIP |
| 10004 | user4 | NULL | VIP |
| 10005 | user5 | NULL | NULL |
+-------+----------+---------+-----------+
# 查詢所有角色肴敛，并統(tǒng)計(jì)各角色用戶數(shù)量
mysql> select id,role,(select count(1) from t_user where role_id = t_role.id) as count from t_role order by id;
+----+--------------+-------+
| id | role | count |
+----+--------------+-------+
| 1 | 管理員 | 1 |
| 2 | VIP | 3 |
| 3 | 普通用戶 | 0 |
+----+--------------+-------+
# update語句中也可以使用子查詢
# 將t_user表中不存在角色的用戶的角色設(shè)為3（普通用戶）
mysql> update t_user set role_id = (select id from t_role order by id desc limit 1) where role_id is null;
# insert語句也支持子查詢，它的格式如下
insert into 表名 (column1,column2...) select ...
3. 連接查詢 join
說明：各種join查詢后還可以加上where語句再篩選吗购，以滿足我們的實(shí)際需要医男。
# 為了效果明顯，先在t_user中插入一條role_id為null的記錄
mysql> insert into t_user (id,username,passwd) values (10005,'user5','123456');
# 同時(shí)將上面子查詢的修改還原捻勉，方便接下來的演示
update t_user set role_id = null where id = 10005;
以下關(guān)于join的描述中A表示左表镀梭，B表示右表。
3.1 inner join：A與B的交集
join前不加任何修飾則默認(rèn)為inner join
# inner join查詢具有角色的用戶及用戶角色
mysql> select u.id,u.username,r.role from t_user as u inner join t_role as r on u.role_id = r.id;
+-------+----------+-----------+
| id | username | role |
+-------+----------+-----------+
| 10001 | user1 | 管理員 |
| 10002 | user2 | VIP |
| 10003 | user3 | VIP |
| 10004 | user4 | VIP |
+-------+----------+-----------+
3.2 left [outer] join：A表所有+B與A重合部分
# 查詢所有用戶及其角色信息（無法關(guān)聯(lián)的角色將為null）
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id;
+-------+----------+-----------+
| id | username | role |
+-------+----------+-----------+
| 10001 | user1 | 管理員 |
| 10002 | user2 | VIP |
| 10003 | user3 | VIP |
| 10004 | user4 | VIP |
| 10005 | user5 | NULL |
+-------+----------+-----------+
# where role.id is null 查詢角色不為空的用戶及其角色信息
# 可以取代not in...提高sql效率踱启，因?yàn)閕n不會(huì)使用索引
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id where r.id is not null;
+-------+----------+-----------+
| id | username | role |
+-------+----------+-----------+
| 10001 | user1 | 管理員 |
| 10002 | user2 | VIP |
| 10003 | user3 | VIP |
| 10004 | user4 | VIP |
+-------+----------+-----------+
3.3 right [outer] join：B表所有+A與B重合部分
# 查詢所有角色及各角色對(duì)應(yīng)的用戶
mysql> select r.id,r.role,u.username from t_user as u right join t_role as r on u.role_id = r.id;
+----+--------------+----------+
| id | role | username |
+----+--------------+----------+
| 1 | 管理員 | user1 |
| 2 | VIP | user2 |
| 2 | VIP | user3 |
| 2 | VIP | user4 |
| 3 | 普通用戶 | NULL |
+----+--------------+----------+
# where user.id is null 查詢無用戶的角色
mysql> select r.id,r.role,u.username from t_user as u right join t_role as r on u.role_id = r.id where u.id is null;
+----+--------------+----------+
| id | role | username |
+----+--------------+----------+
| 3 | 普通用戶 | NULL |
+----+--------------+----------+
1 row in set (0.00 sec)
3.4 full join：A與B的并集 | 在A或B中存在的記錄
MySQL中原生并不支持full join报账，但可以利用union實(shí)現(xiàn)full join效果。
# 查詢所有用戶及角色信息
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id
-> union
-> select u.id,u.username,r.role from t_user as u right join t_role as r on u.role_id = r.id
-> ;
+-------+----------+--------------+
| id | username | role |
+-------+----------+--------------+
| 10001 | user1 | 管理員 |
| 10002 | user2 | VIP |
| 10003 | user3 | VIP |
| 10004 | user4 | VIP |
| 10005 | user5 | NULL |
| NULL | NULL | 普通用戶 |
+-------+----------+--------------+
# 查詢無角色的用戶及無用戶的角色
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id where r.id is null
-> union
-> select u.id,u.username,r.role from t_user as u right join t_role as r on u.role_id = r.id where u.id is null
-> ;
+-------+----------+--------------+
| id | username | role |
+-------+----------+--------------+
| 10005 | user5 | NULL |
| NULL | NULL | 普通用戶 |
+-------+----------+--------------+
3.5 cross join：交叉查詢（數(shù)學(xué)上成為笛卡爾積）
這種方式用得相對(duì)較少埠偿，但特殊情況下也是特殊作用的透罢，取決于業(yè)務(wù)需要和數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)。
mysql> select * from t_user cross join t_role;
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+----+--------------+
| id | role_id | mobile | username | passwd | name | create_time | last_login | id | role |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+----+--------------+
| 10001 | 1 | 13900000001 | user1 | 123456 | 用戶1 | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 | 2 | VIP |
| 10001 | 1 | 13900000001 | user1 | 123456 | 用戶1 | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 | 3 | 普通用戶 |
| 10001 | 1 | 13900000001 | user1 | 123456 | 用戶1 | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 | 1 | 管理員 |
| 10002 | 2 | NULL | user2 | 123456 | NULL | NULL | NULL | 2 | VIP |
| 10002 | 2 | NULL | user2 | 123456 | NULL | NULL | NULL | 3 | 普通用戶 |
| 10002 | 2 | NULL | user2 | 123456 | NULL | NULL | NULL | 1 | 管理員 |
| 10003 | 2 | NULL | user3 | 123456 | NULL | NULL | NULL | 2 | VIP |
| 10003 | 2 | NULL | user3 | 123456 | NULL | NULL | NULL | 3 | 普通用戶 |
| 10003 | 2 | NULL | user3 | 123456 | NULL | NULL | NULL | 1 | 管理員 |
| 10004 | 2 | NULL | user4 | 123456 | NULL | NULL | NULL | 2 | VIP |
| 10004 | 2 | NULL | user4 | 123456 | NULL | NULL | NULL | 3 | 普通用戶 |
| 10004 | 2 | NULL | user4 | 123456 | NULL | NULL | NULL | 1 | 管理員 |
| 10005 | NULL | NULL | user5 | 123456 | NULL | NULL | NULL | 2 | VIP |
| 10005 | NULL | NULL | user5 | 123456 | NULL | NULL | NULL | 3 | 普通用戶 |
| 10005 | NULL | NULL | user5 | 123456 | NULL | NULL | NULL | 1 | 管理員 |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+----+--------------+
15 rows in set (0.00 sec)
3.6 使用join更新表
先思考一個(gè)問題冠蒋，如何按照篩選條件（篩選條件需要使用連接查詢）更新表中部分?jǐn)?shù)據(jù)呢羽圃？
例如，清空存在角色浊服、且無姓名的用戶的上次登錄時(shí)間统屈。
正常的思路如下：
# 先用inner join查出存在角色且無姓名的用戶，再用in根據(jù)id更新t_user表中的last_login字段
mysql> update t_user set last_login = null where id in
-> (
-> select u.id from t_user as u inner join t_role as r on u.role_id = r.id where u.name is null
-> );
ERROR 1093 (HY000): You can't specify target table 't_user' for update in FROM clause
# 很遺憾牙躺，MySQL不支持這種方式愁憔！天哪，頭疼孽拷！
# 那么還有辦法嗎吨掌？當(dāng)然可以，既然提示說不能將更新的表放在from從句中脓恕，那我們可以把連接查詢結(jié)果再與需要更新的表進(jìn)行join
mysql> update t_user a join
-> (select u.id,u.username,r.role from t_user u join t_role r on u.role_id = r.id where u.name is null) b
-> on a.id = b.id
-> set a.last_login = null;
Query OK, 1 row affected (0.01 sec)
Rows matched: 3 Changed: 1 Warnings: 0
# 太好了膜宋，這種方式是可行的！
可見join不僅可以用在select語句中炼幔，還可以用在update及其它語句中秋茫。
3.7 使用join優(yōu)化子查詢
子查詢會(huì)將查詢到的每一條結(jié)果再依次查詢匹配，數(shù)據(jù)量較大時(shí)乃秀，花費(fèi)的時(shí)間時(shí)挺恐怖的肛著。
# 子查詢：查詢用戶及其角色
select id,`name`,username,(select role from t_role where id = t_user.role_id) as role from t_user;
# left join：
select u.id,u.`name`,u.username,r.role from t_user as u left join t_role as r on r.id = u.role_id;
多次執(zhí)行時(shí)間分別為0.001sec和0.000xsec圆兵，這里數(shù)據(jù)量太小效果不明顯，數(shù)據(jù)量越大區(qū)別越大枢贿。
3.8 使用join優(yōu)化聚合
現(xiàn)在有以下業(yè)務(wù)場(chǎng)景：t_pay_day表中記錄了用戶每天付款總和殉农，現(xiàn)在要統(tǒng)計(jì)各個(gè)用戶付款最多的日期。
# 創(chuàng)建表t_pay_day
mysql> create table t_pay_day(
-> pay_date date not null,
-> user_id bigint(20) not null,
-> pay_amount bigint,
-> primary key (`pay_date`,`user_id`)
-> );
# 準(zhǔn)備測(cè)試數(shù)據(jù)
mysql> select * from t_pay_day;
+------------+---------+------------+
| pay_date | user_id | pay_amount |
+------------+---------+------------+
| 2017-01-01 | 10002 | 101 |
| 2017-01-02 | 10002 | 102 |
| 2017-01-03 | 10002 | 103 |
| 2017-01-04 | 10002 | 104 |
| 2017-01-05 | 10002 | 105 |
| 2017-01-06 | 10003 | 201 |
| 2017-01-07 | 10003 | 202 |
| 2017-01-08 | 10003 | 203 |
| 2017-01-09 | 10003 | 204 |
| 2017-01-10 | 10003 | 205 |
| 2017-01-11 | 10004 | 301 |
| 2017-01-12 | 10004 | 302 |
| 2017-01-13 | 10004 | 303 |
| 2017-01-14 | 10004 | 304 |
| 2017-01-15 | 10004 | 305 |
| 2017-01-16 | 10005 | 401 |
| 2017-01-17 | 10005 | 402 |
| 2017-01-18 | 10005 | 403 |
| 2017-01-19 | 10005 | 404 |
| 2017-01-20 | 10005 | 405 |
+------------+---------+------------+
20 rows in set (0.00 sec)
- 子查詢中使用max()
# 子查詢中使用max()
mysql> select u.id,u.username,u.name,p.pay_date,p.pay_amount from t_user as u
-> left join t_pay_day as p on u.id = p.user_id
-> where pay_amount = (select max(pay_amount) from t_pay_day where user_id = u.id);
+-------+----------+------+------------+------------+
| id | username | name | pay_date | pay_amount |
+-------+----------+------+------------+------------+
| 10002 | user2 | NULL | 2017-01-05 | 105 |
| 10003 | user3 | NULL | 2017-01-10 | 205 |
| 10004 | user4 | NULL | 2017-01-15 | 305 |
| 10005 | user5 | NULL | 2017-01-20 | 405 |
+-------+----------+------+------------+------------+
4 rows in set (0.00 sec)
- 使用join優(yōu)化聚合子查詢
# 使用join優(yōu)化聚合子查詢
mysql> select u.id,u.username,u.name,p.pay_date,p.pay_amount from t_user as u
-> left join t_pay_day as p on p.user_id = u.id
-> left join t_pay_day as pp on pp.user_id = p.user_id
-> group by u.id,p.pay_date,p.pay_amount
-> having p.pay_amount = max(pp.pay_amount);
+-------+----------+------+------------+------------+
| id | username | name | pay_date | pay_amount |
+-------+----------+------+------------+------------+
| 10002 | user2 | NULL | 2017-01-05 | 105 |
| 10003 | user3 | NULL | 2017-01-10 | 205 |
| 10004 | user4 | NULL | 2017-01-15 | 305 |
| 10005 | user5 | NULL | 2017-01-20 | 405 |
+-------+----------+------+------------+------------+
4 rows in set (0.00 sec)
3.9 使用join實(shí)現(xiàn)分組選擇
現(xiàn)有以下業(yè)務(wù)場(chǎng)景：從用戶支付記錄中查詢每個(gè)人支付金額最大的2天局荚。
1）多次查詢
# 按照用戶遍歷查詢
mysql> select u.id,u.username,u.name,p.pay_date,p.pay_amount from t_user as u
-> right join t_pay_day as p
-> on p.user_id = u.id
-> where u.id = 10002
-> order by p.pay_amount
-> limit 2;
+-------+----------+------+------------+------------+
| id | username | name | pay_date | pay_amount |
+-------+----------+------+------------+------------+
| 10002 | user2 | NULL | 2017-01-05 | 105 |
| 10002 | user2 | NULL | 2017-01-04 | 104 |
+-------+----------+------+------------+------------+
2 rows in set (0.00 sec)
...有多少個(gè)用戶就需要查詢多少次...
這種方式缺點(diǎn)很多：
需要首先獲取用戶id超凳；
多次執(zhí)行，不符合批量處理的原則耀态，分組（用戶）非常多的時(shí)候很恐怖轮傍；
增加應(yīng)用程序與數(shù)據(jù)庫連接交互次數(shù)；
增加了網(wǎng)絡(luò)IO茫陆。
2）聚合查詢+join
# 思路：某用戶的支付記錄中大于等于此記錄中金額的記錄數(shù)為2即表示當(dāng)前金額從大到小排序?yàn)?
# 1.利用group by + count()查詢每條支付記錄對(duì)應(yīng)的該用戶的所有支付記錄中金額>=當(dāng)前記錄的數(shù)量金麸，記為pay_count
# 2.使用join將1的結(jié)果與t_user連接，篩選出滿足條件（pay_count<=2）的記錄
mysql> select u.id,u.username,a.pay_date,a.pay_amount from
-> (select p.user_id,p.pay_date,pay_amount,
-> (
-> select count(1) from t_pay_day where user_id = p.user_id and p.pay_amount<=pay_amount
-> ) as pay_count
-> from t_pay_day as p
-> group by p.user_id,p.pay_date,pay_amount
-> ) as a
-> join t_user as u on u.id = a.user_id
-> where pay_count <=2;
+-------+----------+------------+------------+
| id | username | pay_date | pay_amount |
+-------+----------+------------+------------+
| 10002 | user2 | 2017-01-05 | 105 |
| 10002 | user2 | 2017-01-04 | 104 |
| 10003 | user3 | 2017-01-10 | 205 |
| 10003 | user3 | 2017-01-09 | 204 |
| 10004 | user4 | 2017-01-15 | 305 |
| 10004 | user4 | 2017-01-14 | 304 |
| 10005 | user5 | 2017-01-20 | 405 |
| 10005 | user5 | 2017-01-19 | 404 |
+-------+----------+------------+------------+
8 rows in set (0.00 sec)
4. 運(yùn)算符與函數(shù)
字符函數(shù)
# length(): 計(jì)算字符串長(zhǎng)度（中文：3簿盅；數(shù)字挥下、字母等：1）
mysql> select length('中文');
+------------------+
| length('中文') |
+------------------+
| 6 |
+------------------+
# char_length()：計(jì)算字符串長(zhǎng)度（中文也是1個(gè)字符）
mysql> select char_length('中文');
+-----------------------+
| char_length('中文') |
+-----------------------+
| 2 |
+-----------------------+
# concat()：連接字符串
mysql> select concat('a','b','c');
+---------------------+
| concat('a','b','c') |
+---------------------+
| abc |
+---------------------+
# concat_ws()：使用分隔符連接字符串
mysql> select concat_ws('-','a','b','c','d');
+--------------------------------+
| concat_ws('-','a','b','c','d') |
+--------------------------------+
| a-b-c-d |
+--------------------------------+
# format()：數(shù)字格式化
mysql> select format(1234.5678,3);
+---------------------+
| format(1234.5678,3) |
+---------------------+
| 1,234.568 |
+---------------------+
# lower()：轉(zhuǎn)小寫字母
mysql> select lower("ABC");
+--------------+
| lower("ABC") |
+--------------+
| abc |
+--------------+
# upper()：轉(zhuǎn)大些字母
mysql> select upper("abc");
+--------------+
| upper("abc") |
+--------------+
| ABC |
+--------------+
# left()：取左側(cè)字符
mysql> select left('abcde',2);
+-----------------+
| left('abcde',2) |
+-----------------+
| ab |
+-----------------+
# right()：取右側(cè)字符
mysql> select right('abcde',2);
+------------------+
| right('abcde',2) |
+------------------+
| de |
+------------------+
# length()：字符串長(zhǎng)度
# ltrim()：刪除左側(cè)空格
# rtrim()：刪除右側(cè)空格
# trim()：刪除兩側(cè)空格
mysql> select concat('***',trim(' abc '),'***');
+-------------------------------------+
| concat('***',trim(' abc '),'***') |
+-------------------------------------+
| ***abc*** |
+-------------------------------------+
# substring()：截取字符串
mysql> select substring('abcdefg',2);
+------------------------+
| substring('abcdefg',2) |
+------------------------+
| bcdefg |
+------------------------+
mysql> select substring('abcdefg',2,3);
+--------------------------+
| substring('abcdefg',2,3) |
+--------------------------+
| bcd |
+--------------------------+
# substring_index(str,delim,count)：按分隔符截取字符串
mysql> select substring_index('192.168.1.110','.',1);
+----------------------------------------+
| substring_index('192.168.1.110','.',1) |
+----------------------------------------+
| 192 |
+----------------------------------------+
mysql> select substring_index('192.168.1.110','.',2);
+----------------------------------------+
| substring_index('192.168.1.110','.',2) |
+----------------------------------------+
| 192.168 |
+----------------------------------------+
mysql> select substring_index('192.168.1.110','.',-1);
+-----------------------------------------+
| substring_index('192.168.1.110','.',-1) |
+-----------------------------------------+
| 110 |
+-----------------------------------------+
mysql> select substring_index('192.168.1.110','.',-2);
+-----------------------------------------+
| substring_index('192.168.1.110','.',-2) |
+-----------------------------------------+
| 1.110 |
+-----------------------------------------+
# replace()：字符替換
mysql> select replace('a-b-c','-','**');
+---------------------------+
| replace('a-b-c','-','**') |
+---------------------------+
| a**b**c |
+---------------------------+
數(shù)值運(yùn)算
# ceil()：進(jìn)一取整
+-----------+
| ceil(1.2) |
+-----------+
| 2 |
+-----------+
# floor()：舍一取整
mysql> select floor(1.8);
+------------+
| floor(1.8) |
+------------+
| 1 |
+------------+
# round(浮點(diǎn)數(shù),小數(shù)位)：四舍五入
mysql> select round(1234.5678,2);
+--------------------+
| round(1234.5678,2) |
+--------------------+
| 1234.57 |
+--------------------+
# power()：冪運(yùn)算
mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
| 8 |
+------------+
# DIV：整數(shù)除法（區(qū)別于／）
mysql> select 5 div 3;
+---------+
| 5 div 3 |
+---------+
| 1 |
+---------+
mysql> select 5/3;
+--------+
| 5/3 |
+--------+
| 1.6667 |
+--------+
# truncate()：數(shù)字截?cái)?mysql> select truncate(1234.5678,2);
+-----------------------+
| truncate(1234.5678,2) |
+-----------------------+
| 1234.56 |
+-----------------------+
mysql> select truncate(1234.5678,-1);
+------------------------+
| truncate(1234.5678,-1) |
+------------------------+
| 1230 |
+------------------------+
日期時(shí)間
# now()：當(dāng)前日期和時(shí)間（yyyy-mm-dd HH:MM:ss）
# curdate()：當(dāng)前日期（yyyy-mm-dd）
# curtime()：當(dāng)前時(shí)間（HH:MM:ss）
# date_add()：日期變化（year、month桨醋、day棚瘟、week...）
mysql> select date_add('2016-07-08',interval -365 day);
+------------------------------------------+
| date_add('2016-07-08',interval -365 day) |
+------------------------------------------+
| 2015-07-09 |
+------------------------------------------+
# datediff()：日期差值
mysql> select datediff('2015-07-09','2016-07-08');
+-------------------------------------+
| datediff('2015-07-09','2016-07-08') |
+-------------------------------------+
| -365 |
+-------------------------------------+
# date_format()：日期格式化
mysql> select date_format('2017-01-02','%Y%m%d');
+------------------------------------+
| date_format('2017-01-02','%Y%m%d') |
+------------------------------------+
| 20170102 |
+------------------------------------+
元數(shù)據(jù)
# connection_id()：當(dāng)前連接ID
# database()：當(dāng)前數(shù)據(jù)庫
# last_insert_id()：最后插入記錄ID
# user()：當(dāng)前用戶
# version()：版本信息
mysql> select concat_ws(':',connection_id(),database(),last_insert_id(),user(),version());
+-----------------------------------------------------------------------------+
| concat_ws(':',connection_id(),database(),last_insert_id(),user(),version()) |
+-----------------------------------------------------------------------------+
| 10:test:0:root@localhost:5.7.17 |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
聚合函數(shù)
# avg()：平均值
# count()：計(jì)數(shù)
# max()：最大值
# min()：最小值
# sum()：求和
其它函數(shù)
# least()：計(jì)算列表中最小值
mysql> select least(123,456);
+----------------+
| least(123,456) |
+----------------+
| 123 |
+----------------+
# greatest()：計(jì)算列表中最大值
mysql> select greatest('AA','BB','CC');
+--------------------------+
| greatest('AA','BB','CC') |
+--------------------------+
| CC |
+--------------------------+
5. 自定義函數(shù)
這個(gè)用得也不多，特殊需要基本都在應(yīng)用程序處理了喜最，偷個(gè)懶有空再來補(bǔ)充吧...
6. 存儲(chǔ)過程
procedure某些情況下有神奇的效果偎蘸，它可以處理業(yè)務(wù)邏輯，比如寫一個(gè)登錄的存儲(chǔ)過程完成以下動(dòng)作：
1.檢查用戶名是否存在瞬内；
2.若用戶名存在迷雪，檢查密碼是否正確（有的網(wǎng)站統(tǒng)一提示用戶名或密碼錯(cuò)誤，估計(jì)是為了節(jié)省一次網(wǎng)絡(luò)IO虫蝶，強(qiáng)烈鄙視這種投機(jī)取巧的方法Ｕ逻帧）
3.記錄登錄信息；
4.此過程中可能還會(huì)增加訪問限制能真，比如一小時(shí)內(nèi)密碼錯(cuò)誤幾次會(huì)鎖定等等赁严。
這些動(dòng)作都放在應(yīng)用程序中的話會(huì)增加與數(shù)據(jù)庫的交互次數(shù)。
但是存儲(chǔ)過程用多了或者經(jīng)常做變動(dòng)的話粉铐，維護(hù)起來很抓狂疼约，所以還是盡量不用。
7. 索引
索引包括普通索引（index）蝙泼、主鍵索引（primary key）程剥、唯一索引（unique）、全文索引（fulltext）;
索引可以為單個(gè)列設(shè)置汤踏，還可以為多個(gè)列設(shè)置（多列索引）织鲸。
- 查看索引
show index|keys from 表名;
- 刪除索引
# 這種方式能夠刪除所有類型的索引
alter table 表名 drop index 索引名;
- 添加索引
# 添加普通索引
alter table 表名 add index 索引名(`索引列`);
# 添加主鍵索引（主鍵約束）
alter table 表名 add primary key(`主鍵列`);
# 添加唯一索引（唯一約束）
alter table 表名 add unique(`列名`);
# 添加全文索引
alter table 表名 add fulltext(`列名`);
# 添加多列索引
alter table 表名 add index 索引名(`列名1`,`列名2`...);
