系统参数系列
show table status where name = "film": 显示某个表的属性
show status : 显示mysql状态
show processlist : 显示mysql连接状态
show index from user : 显示user表索引
show profiles : 显示查询执行时间状况,使用前先设置参数 set profiling=1;
show profile for query 1: 查看第一条sql的具体执行情况
PAGER cat > /dev/null : 将输出转到垃圾黑洞
TRUNCATE TABLE: 清空table所有数据并重置自增主键的值
SELECT * INTO OUTFILE 'out.txt' FROM user : 将user表的数据选择出来以后导入到out.txt文件.
Explain: 解析SQL语句的执行计划
EXPLAIN SELECT * FROM USER\G;
\G 表示输出EXPLAIN树
AUTOCOMMIT变量
AUTOCOMMIT变量表示是否自动提交事务,MySQL中的每一条单独的查询都是一个单独的事务
默认是AUTOCOMMIT=1时,如下是等价的
UPDATE user SET age = 10 WHERE user_id = 1;
与
BEGIN;UPDATE user SET age = 10 WHERE user_id = 1;COMMIT;
而如果SET AUTOCOMMIT=0,则我们执行的任何一条SQL都相当于在一个未提交的事务中,我们需要手动执行COMMIT或者说ROLLBACK.
JOIN 系列
MySQL中 cross join, join, inner join或者不加join都是等效的，如下
SELECT * FROM USER,BOOK;
SELECT * FROM USER CROSS JOIN BOOK;
SELECT * FROM USER JOIN BOOK;
SELECT * FROM USER INNER JOIN BOOK;
另外，有JOIN里的ON和不加JOIN的WHERE等效如下：
SELECT * FROM USER JOIN BOOK ON USER.USERID = BOOK.USERID;
SELECT * FROM USER,BOOK WHERE USER.USERID = BOOK.USERID;
SELECT * FROM USER,BOOK WHERE USING(USERID);
LEFT JOIN, RIGHT JOIN
SELECT * FROM USER LEFT JOIN BOOK ON USER.USERID = BOOK.USERID;
等效于
SELECT * FROM BOOK RIGHT JOIN USER ON BOOK.USERID = USER.USERID;
这两句的意思是将左表的每条记录与右表中的每条记录相连，如果右表中存在的则显示出来，不存在的用NULL表示，如下图
CREATE 系列
create database student character set = utf8 collate =utf8_general_ci;create tablestudent (
stu_idint primary key not nullauto_increment comment "主键id",
stu_namevarchar(5) comment "名称",
stu_gender enum("男", "女") comment "性别",
stu_dob date comment "出生年月",
stu_natplacevarchar(5) comment "籍贯",
stu_depavarchar(5) comment "系",
stu_intro blob comment "简介",
stu_photo blob comment "照片",indexstu_inx using btree (stu_name, stu_depa)
) engine=myisam,character setutf8,
collate utf8_general_ci,
comment="学生信息表";create tablescore (
stu_idint not nullcomment "学生id",
cs_idint not nullcomment "课程id",
scorefloat not nullcomment "成绩",indexstu_course_inx using btree (stu_id, cs_id)
) engine=myisam,character setutf8,
collate utf8_general_ci,
comment="成绩表";create tablecourse (
cs_idint primary key not nullauto_increment comment "主键id",
cs_nochar(8) not null uniquecomment "课程号",
cs_creditfloat not nullcomment "学分",
cs_hourstinyint not nullcomment "学时",
cs_avg_gradefloatcomment "平均分",
cs_total_gradefloatcomment "总分",indexcs_no_inx using btree (cs_no)
) engine=myisam,character setutf8,
collate utf8_general_ci,
comment= "课程表";
建表是,key和index是同义词,都是索引的的意思
LIMIT的使用
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.//如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5; //检索前 5个记录行//换句话说，LIMIT n 等价于 LIMIT 0,n。
UNION: 合并两个结果集并过滤掉重复内容
UNION ALL: 合并两个结果集,结果集可能有重复
使用UNION要注意的是两个结果集的SELECT项必须是相同的
(select actor_id fromactor)union(select actor_id fromfilm_actor)
(select actor_id fromactor)union all(select actor_id from film_actor)
ALTER系列
修改表名: ALTER TABLE USER RENAME TO USERTEMP;
添加列: ALTER TALBE USER ADD COLUMN AGE INT;
删除列: ALTER TABLE USER DROP COLUMN AGE;
修改列: ALTER TABLE USER CHANGE AGE BIRTH INT;
修改列属性: ALTER TABLE USER MODIFY AGE TINYINT;
添加主键: ALTER TABLE USER ADD PRIMARY KEY(USERID);
添加唯一索引: ALTER TABLE USER ADD UNIQUE (USERID);
添加单列索引或联合索引(BTree索引): ALTER TABLE USER ADD INDEX index_name (column1, column2, column3);
添加全文索引：ALTER TABLE USER ADD FULLTEXT (column) ; #仅限于MyISAM
删除索引: ALTER TABLE USER DROP INDEX index_name;
添加外键: ALTER TABLE USER ADD CONSTRAINT (CONSTRAINT_NAME) FOREIGN KEY (BOOKID) REFERENCES BOOK(BOOKID) ON UPDATE/DELETE RESTRICT/CASCADE;
此处注意 ON UPDATE/DELETE RESTRICT/CASCADE;
如果是 update restrict,则如果user表存在有bookid = n时，book表的这个bookid = n的行，不能更新bookid
例如，user表存在 userid = 1, bookid = 2的行，就不能执行 update book set bookid = 3 where bookid = 2;
如果是 update cascade,那么user表如果存在bookid = n， 当book表的bookid = n 这行被修改为bookid = x时，user表里的bookid = n 也会变为bookid = x
例如，user表存在userid = 1, bookid = 2, 执行 update book set bookid = 3 where bookid = 2, 则user表里的 bookid = 2 都会变成 bookid = 3;
如果是 delete restrict,那么如果user表里存在userid = 1, bookid = 2的行，就不能删除 book 表里bookid = 2的行
如果是 delete cascade,那么如果 user 表里存在 userid = 1, bookid = 2的行, 此时如果删除book表里 bookid = 2 的行，则user表里所有bookid=2的行也会被删除
删除外键: ALTER TABLE USER DROP FOREIGN KEY BOOKID;
临时表
当前数据库连接有效，数据库连接结束时自动drop
当临时表名和已存在表名一样时，已存在表会被隐藏，直到临时表被drop
临时表不能被重复打开，也即是说一条SQL查询不能出现两次临时表
1.创建
(1)CREATE TEMPORARY TABLE test_temp (id INT NOT NULL, age int);
(2)CREATE TEMPORARY TABLE test_tem SELECT * FROM user;
2.使用
SELECT * FROM test_temp;
3.删除
DROP TABLE test_temp;
日期函数
1.获取时间: curtime() 输出：23:05:05
2.获取日期: curdate() 输出：2012-10-09
3.获取日期和时间: now(), sysdate() 输出 2012-10-09 23:05:05
4.日期比较(不包括时间)函数: datediff("2010-10-10 20:10:11", now()) = 0 表示当前日期(不包括时间)等于 "2010-10-10"
datediff的参数可以使datetime也可以是date，如果参数1小于参数2返回负数，相等返回0，否则返回整数，数值为天数的差值
5.时间比较，直接用大于小于号
6.返回日期的年份: SELECT YEAR('98-10-11'), 返回 1998
7.UNIX_TIMESTAMP('2010-10-10 20:10:00'): 将日期时间转换成时间戳
对于有联合索引(a, b)的表,如果执行以下查询
SELECT a FROM tb1 WHERE b BETWEEN 2 AND 3
即使b的范围很小,也需要进行全表扫描,因为没有用到前缀索引a,而Oracle则可以松散索引扫描达到高效查询
使用LIMIT来提高MIN()的效率
1. SELECT MIN(actor_id) FROM sakila.actor WHERE first_name ="PENELOPE";2. SELECT actor_id FROM sakila.actor WHERE first_name = "PENELOPE" LIMIT 1;
此处由于first_name没有索引,所以会对actor表进行全表扫描,第一句SQL取出所有记录.而由于actor_id是主键,它的索引是按顺序排列的,使用limit取到的第一条记录肯定就是MIN的值,所以它的效率高,但是这样失去了SQL可以表达出来的查询意义,这是效率和原则之间的权衡.
COUNT(expr): 统计所有expr不为null的行的行数,注意一下expr为假,并不代表为NULL,只有当expr确实返回NULL时,才是NULL
COUNT(col): 统计col列的非NULL结果数
COUNT(*): 统计结果集行数
*对于MyISAM来说,COUNT(*)且没有任何WHERE条件是非常快的,因为它可以直接从存储引擎获得一个表的行数
可以利用这点来优化COUNT查询,如下
SELECT COUNT(*) FROM world.city WHERE id >5;
这个语句需要扫描表中4000+行数据,而如果改成下面这种
SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) FROM world.city WHERE id <= 5;
这个语句仅仅扫描6条记录就可以得出结果,这是对于MyISAM而言,其他数据库引擎则不是这样
CASE ... WHEN ... 与 IF
统计不同颜色的商品数量
SELECT SUM(IF(color='blue', 1, 0)) AS blue, SUM(IF(color='red', 1, 0)) AS red FROMitems;SELECT SUM(CASE WHEN color='blue' THEN 1 ELSE 0 END) AS blue, SUM(CASE WHEN color='red' THEN 1 ELSE 0 END) AS red FROM items;
也可以用COUNT()实现
SELECT COUNT(color='blue' OR NULL) as blue, COUNT(color='red' OR NULL) as red FROM items
GROUP BY 效率优化
应该尽量使GOURP BY的COL拥有索引,这样可以降低临时表和filesort的使用
select actor.first_name, actor.last_name, count(*)fromfilm_actorinner joinactor USING(actor_id)group by actor.first_name, actor.last_name;
explain分析
1
SIMPLE
actor
ALL
PRIMARY
200
Using temporary; Using filesort
1
SIMPLE
film_actor
ref
PRIMARY
PRIMARY
2
sakila.actor.actor_id
13
Using index
改为
select actor.first_name, actor.last_name, count(*)fromfilm_actorinner joinactor USING(actor_id)group by actor.actor_id;
explain分析
1
SIMPLE
actor
index
PRIMARY,idx_actor_last_name
PRIMARY
2
200
1
SIMPLE
film_actor
ref
PRIMARY
PRIMARY
2
sakila.actor.actor_id
13
Using index
可见这两个查询的效率差异是很大的,前者使用了临时表和filesort做全表扫描,而后者只是做了一次索引覆盖扫描
此外,GROUP BY默认会按照GROUP BY的列排序,如果不需要这个排序,可以ORDER BY NULL,或者使用DESC和ASC指定排序顺序
GROUP_CONCAT 与 GROUP BY 的配合
我们知道GROUP_BY(COL)会将COL列相同的值得行合在一起,这样就会损失其他列的数据
如果要获取所有其他列的数据,则可以使用GROUP_CONCAT, 它可以将丢失的这些列值使用 ", " 拼接起来
例如
数据库数据
直接使用group by,会损失一些password
使用 group_concat 拼接 password
LIMIT的优化
考察下面的SQL语句,观察一下film表的index
film
0
PRIMARY
1
film_id
A
1000
BTREE
film
1
idx_title
1
title
A
1000
BTREE
film
1
idx_fk_language_id
1
language_id
A
2
BTREE
film
1
idx_fk_original_language_id
1
original_language_id
A
2
YES
BTREE
select film_id, description from film order by title limit 50, 5
EXPLAIN结果
1
SIMPLE
film
ALL
1000
Using filesort
可知idx_title为辅助索引,由于innodb的辅助索引只记下了主键索引而没有其他内容,所以当我们同时查询film_id和desciption的时候,就无法使用title索引进行排序了,所以改成如下写法,做一个"延迟关联"
select film_id, description fromfilminner join(select film_id from film order by title limit 50, 5)as tb using (film_id);
EXPLAIN
1
PRIMARY
ALL
55
1
PRIMARY
film
eq_ref
PRIMARY
PRIMARY
2
tb.film_id
1
2
DERIVED
film
index
idx_title
767
1000
Using index
这种写法先产生子查询衍生表,查询的时候使用了title索引做索引覆盖扫描,然后再用外表与衍生表做联接,此时使用了主键索引,这种效率可以提高很多,对比如下
44
0.00152075
select film_id, description from film order by title limit 50, 5
48
0.00040575
select film_id, description from film inner join ( select film_id from film order by title limit 50, 5 ) as tb using (film_id)
使用UNION与变量来做短路查询
select greatest(@found := -1, actor_id) as id, 'actor' from actor where actor_id = 1
UNION ALL
select actor_id, 'film_actor' from film_actor where actor_id = 1 and @found is null
UNION ALL
select 1, 'reset' fromDUAL where (@found := null) is not null
这个查询的效果是先查询actor表,当actor表查询得到记录时,@found变量会被赋值-1.这样第二个查询因为@found is null就不会被执行,最后再将@found重置为null
FOR UPDATE 的替代方案
如果使用SELECT ... FOR UPDATE会对记录加上行锁,在事务的后续操作过程中,可能会造成很多堵塞
一个替代方案就是不用FOR UPDATE,而改用一个标志位来表示记录正在被处理,例如
SET AUTOCOMMIT=1;COMMIT;UPDATEunsent_emailsSET status='claimed', owner=CONNECTION_ID()WHERE owner=0 AND status='unsent'LIMIT10;SET AUTOCOMMIT=0;SELECT id FROMunsent_emailsWHERE owner=CONNECTION_ID() AND status='claimed';
那么当前连接线程就可以在后续造作中没有顾虑的修改这些记录了,而不需要加锁,不会造成阻塞.
LENGTH(), CHAR_LEGNTH(): 这两个函数用来计算字符串的长度,其中LENGTH()统计的字节数,CHAR_LEGNTH()是字符数
例如 @name := '中国北京' @name以UTF8编码
LENGTH(@name) 是 12
CHAR_LENGTH(@name) 是 4
substring_index()的使用
substring_index(str, delimiter, count) 可以从左开始计算delimiter在str中出现的次数,当出现次数达到count次时,如果count为正数,返回delimiter左边的字符串,如果count为负数,则返回delimiter右边的字符串,例子:
331
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
