数据库mysql查询简谈

mysql数据库

一、高级

1.1 子查询

• 子查询嵌入到其他查询语句中查询语句，子查询只能出现在from，where、having中
• 子查询不要用select *,exists除外

select title from forum where uid in 
(select id from php_user where name='XX' );

select * from blog_article where cid in 
(select cid from blog_category where name='XX');

select * from (select uid,username,gender from blog_user 
where gender='男') as user;

1.2 多表查询

• 多表连接必须要有连接条件，否则结果没有意义
• 多表连接有两种写法：隐式(标准sql)和显式内连接
• 隐式(标准sql)连接 ： 连接条件写到where字句中

select title,content,name,publish_time
from user u,forum f #给表起个别名，已便书写
where u.id = f.uid; #在where写链接条件
 
select title,content,name,publish_time
from user u,forum f
where u.id = f.uid and name='XX';
select a.username,b.name ,c.title from bbs_user a,bbs_category b,bbs_forum c
where b.cid = c.cid and c.uid = a.uid;

• 显示内连接（inner join）

select a.uid,username,title,content
from bbs_user a inner join bbs_forum b on a.uid =b.uid #关联条件
where a.uid < 5; #过滤条件
 
select username,name,title
from bbs_user a inner join bbs_forum c on c.uid =a.uid
inner join bbs_category b on c.cid = b.cid;
select title,remark,username from blog_remark r join blog_article a
on r.aid = a.aid join blog_user u on r.uid = u.uid;

select后的字段如果在多个表中都有，引用的时候必须加上表名.字段名

• 表的自身连接

select * from areainfo a,areainfo b where a.pid=b.code and 
a.name='XX';

1.3 外连接
两张表关联查询时，根据以那种表为主可以分为左外连接和右外连接

• 左外连接
  以左表为主，如果右边的表中没有匹配的记录，则添加一个万能记录（各个字段都为null)与之连接

select username,r.* from blog_user u left join blog_remark r on
u.uid = r.uid

• 右外连接（right join）
  以右表为主，如果左边的表⾥没有匹配记录，则增加⼀个万能记录与之连接

1.4 集合操作
可以使用union将两个查询结果合并，mysql只支持并，不支持差和交

• 两个结果集中字段数一样，对应字段类型兼容
• 自动去除重复记录,不去除重复记录可以用 union all
• order by 放到最后

select * from student where class = '95031'
union all
select * from student where ssex='XX';

1.5 内部函数

• 字符串函数

函数	功能
char_length(str)	获取字符串的字符个数
length(str)	获取字符串的字节数
concat(s1, s2,… , sn)	连接s1, s2, …, sn 为一个字符串
lower(str)	将字符串str中所有的字符转换为小写
upper(str)	将字符串str中所有的字符转换为大写
left(str, x)	返回字符串str最左边的x个字符
right(str, y)	返回字符串str最右边的y个字符
lpad(str, n,pad)	将字符串pad对str最左边进行填充， 直到长度为n个字符长度
rpad(str, n,pad)	将字符串pad对str最右边进行填充， 直到长度为n个字符长度
ltrim(str)	去掉str中最左边的空格
rtrim(str)	去掉str中最右边的空格
trim(str)	去掉字符串str两边的空格
repeat(str, x)	返回str中重复出现x次的结果
replace(str, a,b)	将字符串str中的a更换为b
insert(str, x, y,instr)	将字符串str从第x位置开始， y个字符长度的子字符串替换为字符串instr
strcmp（s1,s2）	比较字符串s1, s2
substring(str, x,y)	返回字符串str x位置开始y个字符长度的字符串

• 日期函数

函数	功能
curdate()	得到当前日期
curtime()	得到当前时间
now()	得到当前日期和时间
year(date)	得到date的年份
month(date)	得到date的月份
day(date)	得到date的天
hour(time)	得到time的小时
minute(time)	得到time 的分钟
second(time)	得到time的秒
week(date)	得到date是一年中的第几周
date_format(date,fmt)	按格式化串fmt返回date的日期字符串

select DATE_FORMAT(now(),'%Y- %m-%d %H:%i:%s');

• 数学函数

函数	功能
abs(x)	求x的绝对值
ceil(x)	向上取整
floor(x)	向下取整
round(x,d)	四舍五入，d为保留小数的位数
pow(x,y)	x的y次幂
rand()	0~1之间的随机小数
mod(x,y)	等同于x % y,求x对y的模

• 其它函数

函数	功能
convert(expr,type)/cast(expr as type)	将表达式expr转换为type类型，type可以是：char(n)、date、datetime、integer、decimal
md5(str)	计算str的哈希值，返回一个 32位十六进制数字的二进制字符串
sha1(str)/sha(str)	计算str的哈希值，返回一个 40位十六进制数字的二进制字符串

二、数据控制

2.1 事务

• 事务把一组操作看做一个整体，要不都操作成功，要不都操作失败 。(ACID)
• 表的数据库引擎必须是innodb，innodb支持事物，myisam不支持事务
• 修改表引擎：alter table 表名 engine = innodb

select @@autocommit (1为自动提交 0为手动提交)
关闭自动提交
set autocommit = 0
start transaction /begin
一组操作
commit/rollback
commit 提交 会把数据写到硬盘
rollback 回滚 撤销操作

2.2 授权管理(了解)

• 创建用户

create user '用户名'@'服务器地址' identified by '密码'

• 删除用户

drop user '用户名'@'服务器地址'

• 修改密码

修改当前登录用户
set password = password('123456');
一般管理员可以修改任意用户密码
set password for 'db'@'localhost' = password('2333');

• 刷新

flush privileges

• 授权

 grant 权限 on 数据库.表 to '用户名'@'服务器地址'
 grant all on *.* to 'dd'@'localhost'
 *.* 所有数据库的所有表
 all 代表所有权限 
 权限包括：select、update、delete、alter、insert

• 回收

 revoke select on test.stars from 'db'@'localhost';

三、索引

索引是关系型数据库中用来提升查询性能最为重要的手段。关系型数据库中的索引就像一本书的目录，我们可以想象一下，如果要从这本书中找出某个知识点，但是这本书没有目录，这将是多么可怕的事情（我们估计得一篇一篇的翻下去，才能确定这个知识点到底在什么位置）。创建索引虽然会带来存储空间上的开销，就像一本书的目录会占用一部分的篇幅一样，但是在牺牲空间后换来的查询时间的减少也是非常显著的。

MySQL中，所有数据类型的列都可以被索引，常用的存储引擎InnoDB和MyISAM
能支持每个表创建16个索引。InnoDB和MyISAM使用的索引其底层算法是B-tree（B树），B-tree是一种自平衡的树，类似于平衡二叉排序树，能够保持数据
有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的操作都在对数时间内完成。

3.1 索引的优点

• 可以大大加快数据的检索速度
• 唯一索引可以保证数据的唯一性
• 可以降低分组、排序的时间
• 可以使用查询优化器提升系统性能

3.2 索引的缺点

• 建立索引会建立对应索引文件，占用大量空间
• 建立索引会降低增、删、改的效率

3.3 不建立索引

• 频繁更新的字段不要建立索引
• 没出现在where、having，不要建立索引
• 数据量少的表没有必要建立索引
• 唯一性比较差的字段不要建立索引

3.4 索引分类
普通索引

create index 索引名 on 表名(字段 asc/desc) 默认asc升序

唯一索引
在唯一索引所在列不能有重复值，增加和修改会受影响。

create unique index 索引名 on 表名(字段 asc/desc) 默认asc升序

主键索引
创建表，主键索引会自动添加，要求在主键上不能有重复值，不能有空值

复合索引（联合索引） 索引了多个列

• 使用联合索引，必须包含左前缀。 （a,b,c)
  • a
  • a,b
  • a,b,c

全文索引（了解）
一般会用全文索引服务器(sphinx)，不会直接创建全文索引

create FULLTEXT index 索引名 on 表名(字段 asc/desc)

删除索引

drop index 索引名 on 表

查看索引

show index from 表 \G
#查看sql性能
explain select sno,sname from student where class='1904'\G;

在上面的SQL执行计划中，有一项值得我们关注：

type：MySQL在表中找到满足条件的行的方式，也称为访问类型，包括：ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const/system、NULL。在所有的访问类型中，很显然ALL是性能最差的，它代表了全表扫描是指要扫描表中的每一行才能找到匹配的行。
possible_keys：MySQL可以选择的索引，但是有可能不会使用。
key：MySQL真正使用的索引。
rows：执行查询需要扫描的行数，这是一个预估值。

其它创建索引的方式

alter table 表 add index(字段1,字段2,...)
alter table 表 add primary key(字段1,字段2,...)
alter table 表 add unique(字段1,字段2,...)
alter table 表 add fulltext(字段1,字段2,...)

3.5 不使用索引的情况

• 查询时的联合索引没有左前缀，不使用索引
• or条件里，如果一方字段没有索引，则不使用索引
• 类型不对应的不会使用索引
• like ‘%tom’ ,如果左边是通配符，不会使用索引
• 使用!=、<>、not in操作，不使用索引

四、外键

如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表
B称为从表

• 数据表引擎必须是innodb
• 主表和从表相关的外键字段类型必须兼容

创建外键
ALTER TABLE 从表名
ADD CONSTRAINT 外键名称 FOREIGN KEY (从表的外键列) REFERENCES 主表名 (主
键列)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION

1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所持。

2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注
   意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

3. NO ACTION: InnoDB拒绝删除或者更新父表。

4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

删除外键
ALTER TABLE 从表 DROP FOREIGN KEY 外键名

五、视图

视图是关系型数据库中将一组查询指令构成的结果集组合成可查询的数据表的对
象。简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是一种实体结
构，而视图是一种虚拟结构，你也可以将视图理解为保存在数据库中被赋予名字的SQL语句。
使用视图可以获得以下好处：

1. 可以将实体数据表隐藏起来，让外部程序无法得知实际的数据结构，让访问者
   可以使用表的组成部分而不是整个表，降低数据库被攻击的风险。

2. 在大多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外
   部程序无法直接透过视图修改数据。

3. 重用SQL语句，将高度复杂的查询包装在视图表中，直接访问该视图即可取出
   需要的数据；也可以将视图视为数据表进行连接查询。

4. 视图可以返回与实体数据表不同格式的数据，

   • 创建视图
     create view 视图名(字段列表) as select子句
   • 删除视图
     drop view 视图名

既然视图是一张虚拟的表，那么视图的中的数据可以更新吗？视图的可更新性要视具体情况而定，以下类型的视图是不能更新的：

1. 使用了聚合函数（SUM、MIN、MAX、AVG、COUNT等）、DISTINCT、
   GROUP BY、HAVING、UNION或者UNION ALL的视图。
2. SELECT中包含了子查询的视图。
3. FROM子句中包含了一个不能更新的视图的视图。
4. WHERE子句的子查询引用了FROM子句中的表的视图。