一、数据表操作
1.1、插入
1.1.1、迅速插入百万条测试数据
①、新建表
create table test( id INT NOT NULL AUTO_INCREMENT, value SMALLINT(1) NOT NULL, PRIMARY KEY ( id ) );
②、先用PHP代码生成数据，再导入
<?php
$t=mktime();
set_time_limit(1000);
$myFile="e:/insert.sql";
$fhandler=fopen($myFile,wb);
if($fhandler){
$sql="265";
$i=0;
while($i<10000000)
{
$i++;
fwrite($fhandler,$sql."\r\n");
}
echo"写入成功,耗时：",mktime()-$t;
}③、然后再导入
LOAD DATA local INFILE e:/insert.sql INTO TABLE test(`value`);
④、查询数量
select count(*) from test;
1.2、更新
1.3、删除
1.3.1、清空表数据后如何让自增ID仍从1开始
truncate test;
1.4、查询
1.4.1、一个查询案例
①、数据文件
②、求所有电脑产品的平均价格，并且保留两位小数
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
查询所有价格大于平均价格的商品，并且按价格降序排序
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5845.10 ORDER BY goods_price DESC;
使用子查询来实现
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods) ORDER BY goods_price DESC;
③、查询类型为“超记本”的商品价格
SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';
查询价格大于或等于"超级本"价格的商品，并且按价格降序排列
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;
= ANY 或 = SOME 等价于 IN
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;
④、创建“商品分类”表
CREATE TABLE IF NOT EXISTS tdb_goods_cates( cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(40) );
查询 tdb_goods 表的所有记录，并且按"类别"分组
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
将分组结果写入到tdb_goods_cates数据表
INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
⑤、通过 tdb_goods_cates 数据表来更新 tdb_goods 表
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id ;
⑥、通过CREATE...SELECT来创建数据表并且同时写入记录
SELECT brand_name FROM tdb_goods GROUP BY brand_name; CREATE TABLE tdb_goods_brands ( brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, brand_name VARCHAR(40) NOT NULL ) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
⑦、复制编号为 19,20 的两条记录
INSERT ... SELECT实现复制
INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);
⑧、查找重复记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;
⑨、删除重复记录
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
1.4.2、查询某字段中以逗号分隔的字符串的方法
①、创建表
CREATE TABLE test( id int(6) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id), pname VARCHAR(20) NOT NULL, pnum VARCHAR(50) NOT NULL );
②、插入带有逗号分隔的测试数据
INSERT INTO test(pname,pnum) VALUES(产品1,1,2,4); INSERT INTO test(pname,pnum) VALUES(产品2,2,4,7); INSERT INTO test(pname,pnum) VALUES(产品3,3,4); INSERT INTO test(pname,pnum) VALUES(产品4,1,7,8,9); INSERT INTO test(pname,pnum) VALUES(产品5,33,4);
③、查找 pnum 字段中包含 3 或者 9 的记录
方法一：使用 find_in_set
SELECT * FROM test WHERE find_in_set(3,pnum) OR find_in_set(9,pnum);
方法二：使用正则
SELECT * FROM test WHERE pnum REGEXP (3|9);
这样会产生多条记录，33也被查找出来了。换一种方式：
SELECT * FROM test WHERE CONCAT(,,pnum,,) REGEXP[^0-9]+[3|9][^0-9]+";
CONCAT 用于将多个字符串连接成一个字符串，返回结果为连接参数产生的字符串。
1.4.3、concat、group_concat
本文中使用的例子均在下面的数据库表user下执行：
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `date` varchar(64) NOT NULL, `singin` tinyint(1) NOT NULL, `sex` tinyint(1) NOT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` VALUES ('1', '小明', '2018-07-05 00:00:00', '1', '1', '0');
INSERT INTO `user` VALUES ('2', '小王', '2018-07-05 01:21:35', '3', '0', '0');
INSERT INTO `user` VALUES ('3', '小丽', '2018-07-03 12:30:59', '2', '1', '0');
INSERT INTO `user` VALUES ('4', '小王', '2018-07-04 15:26:14', '4', '0', '0');
INSERT INTO `user` VALUES ('5', '小明', '2018-06-11 15:26:40', '4', '1', '0');
INSERT INTO `user` VALUES ('6', '小明', '2018-06-01 15:26:54', '2', '0', '0');
INSERT INTO `user` VALUES ('7', '', '2018-06-31 11:34:34', '0', '0', null);
INSERT INTO `user` VALUES ('8', 'maryleo', '2018-06-31 12:43:59', '0', '1', '60');
INSERT INTO `user` VALUES ('9', 'nancysun', '2018-06-31 12:43:59', '0', '1', '60');①、concat()函数
A、功能：将多个字符串连接成一个字符串。
B、语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
C、举例：
例1:
select concat (id, name, score) as info from user;
中间有一行为null是因为user表中有一行的score值为null。
例2：在例1的结果中三个字段id，name，score的组合没有分隔符，我们可以加一个逗号作为分隔符：
select concat(id,',',name,',',score) as info from user;
这样看上去似乎顺眼了许多～～
但是输入sql语句麻烦了许多，三个字段需要输入两次逗号，如果10个字段，要输入九次逗号...麻烦死了啦，有没有什么简便方法呢？——于是可以指定参数之间的分隔符的concat_ws()来了！！！
②、concat_ws()函数
A、功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）
B、语法：concat_ws(separator, str1, str2, ...)
说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。
C、举例：
例3:我们使用concat_ws()将 分隔符指定为逗号，达到与例2相同的效果：
select concat_ws(',',id,name,score) as info from user;例4：把分隔符指定为null，结果全部变成了null：
select concat_ws(NULL,id,name,score) as info from user;
③、group_concat()函数
前言：在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。
select name,min(id) from user group by name;
该例查询了name相同的的人中最小的id。如果我们要查询name相同的人的所有的id呢？
当然我们可以这样查询：
select name,id from user order by name;
但是这样同一个名字出现多次，看上去非常不直观。有没有更直观的方法，既让每个名字都只出现一次，又能够显示所有的名字相同的人的id呢？——使用group_concat()
A、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
B、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
C、举例：
例7：使用group_concat()和group by显示相同名字的人的id号：
select name,group_concat(id) from user group by name;
例8：将上面的id号从大到小排序，且用'_'作为分隔符：
select name,group_concat(id order by id desc separator '_') as ids from user group by name;
例9：上面的查询中显示了以name分组的每组中所有的id。接下来我们要查询以name分组的所有组的id和score：
select name,group_concat(concat_ws('-',id,score) order by id) as value from user group by name;1.4.4、gruop by 报错
①、问题
在mysql 工具 搜索或者插入数据时报下面错误：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column "database_tl.emp.id" which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
②、原因
A、看一下group by的语法：
select 选取分组中的列+聚合函数 from 表名称 group by 分组的列
从语法格式来看，是先有分组，再确定检索的列，检索的列只能在参加分组的列中选。
我当前Mysql版本5.7.17，
再看一下 ONLY_FULL_GROUP_BY 的意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。
B、查看mysql版本命令
select version();
查看sql_model参数命令：
SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.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
第一项默认开启ONLY_FULL_GROUP_BY，
③、解决方法
A、只选择出现在 group by 后面的列，或者给列增加聚合函数；（不推荐）
B、命令行输入：
set @@GLOBAL.sql_mode=; set sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
默认关掉ONLY_FULL_GROUP_BY！
这个时候 在用工具select 一下
SELECT @@sql_mode; SELECT @@GLOBAL.sql_mode;
发现已经不存在ONLY_FULL_GROUP_BY ，感觉已经OK。但是如果你重启Mysql服务的话，发现ONLY_FULL_GROUP_BY还是会存在的
C、彻底解决
想要彻底解决这个问题 就得去改my.ini 配置（如果你们mysql 没有这个文件，就把my-default.ini 改成my.ini，我这个版本就是没有my.ini配置问题）
在文件最后添加
[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
重启 mysql
/etc/init.d/mysql restart
1.4.5、in 查询优化
select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839'); 67 rows in set (12.00 sec)
只有67行数据返回，却花了12秒，而系统中可能同时会有很多这样的查询，系统肯定扛不住。用desc看一下(注：explain也可)
desc select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839'); +----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+ | 1 | PRIMARY | abc_number_prop | ALL | NULL | NULL | NULL | NULL | 2679838 | Using where | | 2 | DEPENDENT SUBQUERY | abc_number_phone | eq_ref | phone,number_id | phone | 70 | const,func | 1 | Using where; Using index | +----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+ 2 rows in set (0.00 sec)
从上面的信息可以看出，在执行此查询时会扫描两百多万行，难道是没有创建索引吗，看一下
show index from abc_number_phone; +------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | abc_number_phone | 0 | PRIMARY | 1 | number_phone_id | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 0 | phone | 1 | phone | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 0 | phone | 2 | number_id | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | number_id | 1 | number_id | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | created_by | 1 | created_by | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | modified_by | 1 | modified_by | A | 36879 | NULL | NULL | YES | BTREE | | | +------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.06 sec) show index from abc_number_prop; +-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | abc_number_prop | 0 | PRIMARY | 1 | number_prop_id | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | number_id | 1 | number_id | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | created_by | 1 | created_by | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | modified_by | 1 | modified_by | A | 311268 | NULL | NULL | YES | BTREE | | | +-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.15 sec)
从上面的输出可以看出，这两张表在number_id字段上创建了索引的。
看看子查询本身有没有问题。
desc select number_id from abc_number_phone where phone = '82306839'; +----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+ | 1 | SIMPLE | abc_number_phone | ref | phone | phone | 66 | const | 6 | Using where; Using index | +----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec
没有问题，只需要扫描几行数据，索引起作用了。查询出来看看
select number_id from abc_number_phone where phone = '82306839'; +-----------+ | number_id | +-----------+ | 8585 | | 10720 | | 148644 | | 151307 | | 170691 | | 221897 | +-----------+ 6 rows in set (0.00 sec)
直接把子查询得到的数据放到上面的查询中
select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897); 67 rows in set (0.03 sec)
速度也快，看来MySQL在处理子查询的时候是不够好。我在MySQL 5.1.42 和 MySQL 5.5.19 都进行了尝试，都有这个问题。
根据网上这些资料的建议，改用join来试试。
修改前：select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839'); 修改后：select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839'; select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839'; 67 rows in set (0.00 sec)
效果不错，查询所用时间几乎为0。看一下MySQL是怎么执行这个查询的
desc select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839'; +----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+ | 1 | SIMPLE | b | ref | phone,number_id | phone | 66 | const | 6 | Using where; Using index | | 1 | SIMPLE | a | ref | number_id | number_id | 4 | eap.b.number_id | 3 | | +----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+ 2 rows in set (0.00 sec)
小结：当子查询速度慢时，可用JOIN来改写一下该查询来进行优化。
1.4.6、union 排序问题
①、实现功能，活动日期，当天的排在最前，其次是 大于当天的额，最后 是以前的活动 分开，startteim >= 当天的, union starttime< 当天的 问题，分开查，好使，放在一起，大于当天的乱掉
②、原来是：
(select * from v9_activity where starttime>now() and enable=1 order by starttime ASC) UNION ALL (select * from v9_activity where starttime < now() and enable=1 order by starttime DESC)
③、以上排序乱掉。修改为以下：
(SELECT * from (select * from v9_activity where starttime>now() and enable=1 order by starttime ASC) as activity1) UNION ALL (select * from (select * from v9_activity where starttime < now() and enable=1 order by starttime DESC) as activity2)
1.4.7、MYSQL查询~ 存在一个表而不在另一个表中的数据
select A.ID from A left join B on A.ID=B.ID where B.ID is null
1.4.8、查询在线人数，并能处理异常掉线的 SQL
...WHERE ________< now()
WHERE 用户上次更新时间 + 正常更新间隔< now()
1.4.9、有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列
create table table1(a int,b int,c int); insert into table1 values(22,24,23); select (case when a>b then a when a>c then a when b>c then b else c end) as max from table1;
1.4.10、mysql 查询当天、本周、本月、上一个月的数据
①、内置时间函数
current_date()/curdate() 当天日期，2018-07-09 current_time()/curtime() 当前时间，11:50:59
sysdate() 执行到当前函数时的时间，2018-07-09 11:50:59 current_timestamp()/now() 执行SQL语句时的时间，2018-07-09 11:50:59 SELECT now(),current_timestamp(),sysdate(),sleep(2),now(),current_timestamp(),sysdate() 结果会发现最后一个SYSDATE()显示的时间会较其他时间大两秒。
unix_timestamp() 日期转时间戳 from_unixtime() 时间戳转日期
to_days() 函数返回一个天数! 从年份0开始的天数 有一张表！order表 有一个字段 create_time 类型 datetime 如果要查询当前表中昨天的数据那么 select * from order where to_days(now())-to_days(create_time)<1 from_days()给出一个天数 N，返回一个 DATE 值
date_sub(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。 select date_sub(curdate(),interval 7 day)
date() 函数返回日期或日期/时间表达式的日期部分。 year(date)返回日期的年份，范围为1000到9999，或者对于“零”日期返回0。 quarter(date)返回日期的一年中的季度，范围为1到4。 month(date)返回日期的月份，1月至12月的范围为1至12，对于包含月份为零的日期（如“0000-00-00”或“2008-00-00”），返回0。 week(date[,mode])此函数返回日期的周号。 week（）的双参数使您能够指定星期是从星期天还是星期一开始，以及返回值是在0到53还是从1到53的范围内。如果省略mode参数，则值 使用了default_week_format系统变量。 yearweek 是获取年份和周数的一个函数
cast函数语法规则是：cast(字段名 as 转换的类型 )，其中类型可以为： char[(N)] 字符型 、date 日期型、datetime 日期和时间型、decimal float型、signed int、time 时间型
period_diff() 返回两个时段之间的月份差值
②、查询
今天：select * from 表名 where to_days(时间字段名) = to_days(now()) 昨天：select * from 表名 where to_days(now()) - to_days(时间字段名) <= 1 近7天：select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名) 近30天：select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名) 本月：select * from 表名 where date_format(时间字段名,'%Y%m') = date_format(curdate(),'%Y%m') 上一月：select * from 表名 where period_diff(date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m')) = 1 查询本季度数据：select * from 表名 where quarter(时间字段名) = quarter(now()) 查询上季度数据：select * from 表名 where quarter(时间字段名) = quarter(date_sub(now(),interval 1 quarter)) 查询本年数据：select * from 表名 where year(时间字段名) = year(now()) 查询上年数据：select * from 表名 where year(时间字段名) = year(date_sub(now(),interval 1 year)) 查询当前这周的数据：select * from 表名 where yearweek(date_format(时间字段名,'%Y-%m-%d')) = yearweek(now()) 查询上周的数据：select * from 表名 where yearweek(date_format(时间字段名,'%Y-%m-%d')) = yearweek(now())-1
1.4.11、mysql in、find_in_set、like
①、in查询相当于多个or条件的叠加
select * from user where user_id in (1,2,3); 等效于 select * from user where user_id = 1 or user_id = 2 or user_id = 3; not in与in相反，如下 select * from user where user_id not in (1,2,3); 等效于 select * from user where user_id != 1 and user_id != 2 and user_id != 3;
②、FIND_IN_SET(str,strlist)：str 要查询的字符串，strlist 字段名 参数以”,”分隔 如 (1,2,6,8)。如果str不在strlist 或strlist 为空字符串，则返回值为 0 。
select find_in_set('b','a,b,c'); //返回2FIND_IN_SET函数用来比较是不是包含
③、like是广泛的模糊匹配，字符串中没有分隔符
select * from user where name like '%a%'
1.4.12、MySQL实现排名并查询指定用户排名功能
①、表结构
CREATE TABLE `testsort` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NULL DEFAULT 0 COMMENT '用户id', `score` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '分数', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '测试排序' ROW_FORMAT = Compact; INSERT INTO `testsort` VALUES (1, 12, 80.00); INSERT INTO `testsort` VALUES (2, 13, 78.00); INSERT INTO `testsort` VALUES (3, 8, 99.00);
②、思路
A、可以先排序，再对结果进行编号；也可以先查询结果，再排序编号。
B、说明：
@rownum := @rownum + 1 中 := 是赋值的作用，这句话的意思是先执行@rownum + 1，然后把值赋给@rownum；
(SELECT @rownum := 0) r 这句话的意思是设置rownum字段的初始值为0，即编号从1开始。
C、实现排名：
方法一：
SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t;
方法二：
SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, testsort AS t ORDER BY t.score DESC;
③、查看指定用户排名
方法一：
SELECT b.* FROM ( SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t ) AS b WHERE b.uid = 13;
方法二：
SELECT b.* from ( SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, testsort AS t ORDER BY t.score DESC ) as b where b.uid = 13;
结果：
④、实现从指定用户uid为8,12 中获取uid为8的排名
SELECT b.* FROM ( SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort WHERE uid IN (8,12) ORDER BY score DESC) AS t ) AS b WHERE b.uid = 8;
结果：
1.4.13、内连接、左连接、右连接
①、内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
②、左连接
select a.*,b.* from a left join b on a.id=b.parent_id
③、右连接
select a.*,b.* from a right join b on a.id=b.parent_id
二、数据库操作
①、创建
CREATE DATABASE t1; CREATE DATABASE IF NOT EXISTS t1; CREATE DATABASE t2 CHARACTER SET gbk;
②、更新
ALTER DATABASE t2 CHARACTER SET utf8;
③、查看
SHOW CREATE DATABASE t2; SHOW DATABASES;
④、删除
DROP DATABASE t1;
三、数据类型
①、定义
数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。
②、整形
③、数据类型之浮点型
④、数据类型之日期时间型
⑤、数据类型之字符型
⑥、mysql 中 char、varchar、text 的区别
A、char的总结：
char 最大长度是 255 字符，注意是字符数和字符集没关系。可以有默认值，尾部有空格会被截断。
B、varchar的总结：
varchar 的最大长度 65535 是指能存储的字节数，其实最多只能存储 65532 个字节，还有 3 个字节用于存储长度。 注意是字节数这个和字符集有关系。一个汉字字符用 utf8 占用 3 字节，用 gbk 占用 2 字节。可以有默认值，尾部有空格不会截断。
C、text的总结：
text 和 varchar 基本相同。text 会忽略指定的大小这和 varchar 有所不同，text 不能有默认值。尾部有空格不会被截断。 text 使用额外的 2 个字节来存储数据的大小，varchar 根据存储数据的大小选择用几个字节来存储。 text 的 65535 字节全部用来存储数据，varchar 则会占用 1－3 个字节去存储数据大小。
四、创建数据库
①、创建
USE test; SELECT DATABASE(); SHOW TABLES; SHOW TABLES FROM mysq1;
CREATE TABLE tb1( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL UNIQUE KEY DEFAULT '', age TINYINT UNSIGNED, salary FLOAT(8,2) UNSIGNED NULL )ENGINE=InnoDB;
SHOW COLUMNS FROM tb1; //查看数据表结构
②、约束
唯一约束：UNIQUE KEY，唯一约束可以保证记录的唯一性、唯一约束的字段可以为空值（NULL）、每张数据表可以存在多个唯一约束
默认约束：DEFAULT
主键约束：PRIMARY KEY
非空约束：NOT NULL
外键约束：FOREIGN KEY，1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。2.数据表的存储引擎只能为InnoDB。3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。4.外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。
CREATE TABLE provinces( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL ); CREATE ABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR（10）NOT NULL, FOREIGN KEY（pid）REFERENCES provinces (id) ON DELETE CASCADE ); SHOW INDEXES FROM provinces \G;
1.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
2.SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
3.RESTRICT：拒绝对父表的删除或更新操作。
4.NOACTION：标准SQL的关键字，在MySQL中与RESTRICT相同
③、utf8_general_ci/cs、utf8_unicode_ci、utf8_bin
utf8_general_cs：区分大小写（cs：case sensitiveci 大小写敏感） utf8_general_ci：不区分大小写，校对快、准确度差（ci：case insenstive 大小写不敏感） utf8_unicode_ci：不区分大小写，校对慢、准确度高 utf8_bin：区分大小写，可存储二进制内容
五、表级约束与列级约束
①、定义
对一个数据列建立的约束，称为列级约束.对多个数据列建立的约束，称为表级约束
列级约束既可以在列定义时声明，也可以在列定义后声明
表级约束只能在列定义后声明
②、进一步解释
NOT NULL、DEFAULT只有列级约束
PRIMARY KEY 、UNIQUE KEY、UNIQUE KEY有表级和列级约束
CHECK检测约束不起作用，mysql手册里写的很清楚：“所有的存储引擎均对CHECK子句进行分析，但是忽略CHECK子句。”
六、修改数据表
①、添加/删除列
ALTER TABLE users1 ADD aQe TINYINT UNSIGNED NOT NULL DEFAULT 10 AFTER username:
ALTER TABLE users1 DROP age; ALTER TABLE users1 DROP hobby,DROP password; 删除一列的同时在新增一列也是可以的！只需要用都逗号间隔
②、添加约束
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id); //添加主键约束 ALTER TABLE users2 ADD UNIQUE KEY (username); //添加唯一约束 ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id); //添加外键约束 ALTER TABLE users2 ALTER age SET DEFAULT 15; //添加默认约束
③、删除约束
ALTER TABLE users2 DROP PRIMARY KEY; //删除主键约束 ALTER TABLE users2 DROP INDEX username; //删除唯一约束 ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1; //删除外键约束 ALTER TABLE users2 ALTER age DROP DEFAULT; //删除默认约束
④、修改列定义和更名数据表
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; //修改列的定义 ALTER TABLE users2 RENAME users3; //修改列名称 RENAME TABLE users3 TO users2; //数据表更名