sql学习笔记（18）-----------数据库创建过程(四)
ame,pname,count(pname) from demo group by?
cname,pname;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| sh | dh | 3 |
| sh | rg | 1 |
+-------+-------+--------------+
4 rows inset (0.00 sec)
同样使用with rollup关键字后，统计出更多的信息，如下。注意：with rollup不可以和ordery by同时使用
?
ysql> selectcname,pname,count(pname) from demo group by cname,pname?
with rollup;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| bj | NULL | 5 |
| sh | dh | 3 |
| sh | rg | 1 |
| sh | NULL | 4 |
| NULL | NULL | 9 |
+-------+-------+--------------+
7 rows inset (0.00 sec)
使用外键需要注意的问题 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
?
创建外键的方式
?
mysql>create table temp(id int, name char(20), foreign key(id)?
references outTable(id) on delete cascade on update cascade)；
注意：Innodb类型的表支持外键，myisam类型的表，虽然创建外键可以成功，但是不起作用，主要原因是不支持外键。
?
优化SQL语句的一般步骤 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
?
通过show status命令了解各种SQL的执行频率
1
mysql> show [session|global]status;
其中：session（默认）表示当前连接，global表示自数据库启动至今
?
mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';
参数说明：
?
Com_XXX表示每个XXX语句执行的次数如：
Com_select 执行select操作的次数，一次查询只累计加1
Com_update 执行update操作的次数
Com_insert 执行insert操作的次数，对批量插入只算一次。
Com_delete 执行delete操作的次数
只针对于InnoDB存储引擎的：
?
InnoDB_rows_read 执行select操作的次数
InnoDB_rows_updated 执行update操作的次数
InnoDB_rows_inserted 执行insert操作的次数
其他：
?
connections 连接mysql的数量
Uptime 服务器已经工作的秒数
Slow_queries:慢查询的次数
定位执行效率较低的SQL语句
1
2
explain select* from table where id=1000;
desc select * fromtable where id=1000;
通过EXPLAIN分析较低效SQL的执行计划
?
mysql> explain select count(*) from stu where name like"a%"\G?
*************************** 1. row ***************************?
id: 1?
select_type: SIMPLE?
table: stu?
type: range?
possible_keys: name,ind_stu_name?
key: name?
key_len: 50?
ref: NULL?
rows: 8?
Extra: Using where; Using index?
1 row inset (0.00 sec)
每一列的简单解释
?
?
id: 1
select_type: SIMPLE 表示select的类型，常见的取值有SIMPLE（）简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SESECT）等
table: stu ?输出结果集的表
type: range 表示表的连接类型，性能有好到差：system（表仅一行）、const（只一行匹配）、eq_ref（对于前面的每一行使用主键和唯一）、ref（同eq_ref，但没有使用主键和唯一）、ref_or_null（同前面对null查询）、index_merge（索引合并优化）、unique_subquery（主键子查询）、index_subquery（非主键子查询）、range（表单中的范围查询）、index（都通过查询索引来得到数据）、all（通过全表扫描得到的数据）
possible_keys: name,ind_stu_name 表查询时可能使用的索引。
key: name ?表示实际使用的索引。
key_len: 50 索引字段的长度
ref: NULL
rows: 8 扫描行的数量
Extra: Using where; Using index 执行情况的说明和描述
?
索引问题 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
?
MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
?
mysql>create index ind_company2_name on company2(name(4));
--其中company表名ind_company2_name索引名
MySQL如何使用索引
1、使用索引
?
(1)对于创建的多列索引，只要查询的条件中用到最左边的列