mysql数据库查询、索引等必会操作讲解

一、子查询

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

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

二、多表查询

多表连接必须要有连接条件，否则结果没有意义 多表连接有两种写法：隐式(标准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=’王琨’;

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）

mysql> 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 c2.name

from category c1,category c2

where c1.pid = c2.cid;

//表的字段可以直接连接

select * from zzl_student where sno = monitor and class=’95031’;

“`

三、外连接

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

左外连接

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

select *
from user u left join forum f
on u.id =f.uid;

右外连接

以右表为主，如果左边的表里没有匹配记录，则增加一个万能记录与之连接

四、集合操作

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

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

order by 放到最后

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

五、数据控制

5.1 事务

事务把一组操作看做一个整体，要不都操作成功，要不都操作失败 。

表的数据库引擎必须是innodb，innodb支持事物，myisam不支持事务

修改表引擎：alter table 表名 engine = innodb

“`~

查询是否为自动提交

select @@autocommit (1为自动提交 0为手动提交)

关闭自动提交

set autocommit = 0

start transaction /begin

一组操作

commit/rollback

commit 提交 会把数据写到硬盘

rollback 回滚 撤销操作

“`~

5.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

回收

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

六、索引

索引就像图书的目录，可以加快查询速度

普通索引

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

唯一索引

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

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

主键索引

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

全文索引（了解）

一般会用全文索引服务器，不会直接创建全文索引

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

删除索引

查看索引

show index from 表

其它创建索引的方式

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,...)

七、外键

如果表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语句，可以不把视图当做基本表使用

不要在视图上进行增、删、改

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

删除视图
drop view 视图名

九、pymysql操作mysql数据库

安装pymysql

pip install pymysql

9.1 pymysql操作数据库的五行拳

连接数据库

使用Connect方法连接数据库

pymysql.Connections.Connection(host=None, user=None, password='', database=None, port=0,  charset='')
参数说明：
   host – 数据库服务器所在的主机。
   user – 登录用户名。
   password – 登录用户密码。
   database – 连接的数据库。
   port – 数据库开放的端口。（默认: 3306）
   charset – 连接字符集。
返回值：
  返回连接对象

例子：
link = pymysql.Connect(host='localhost', port=3306, user='root', password='123456', db='zzl', charset='utf8')

连接对象方法

2. 创建游标

cursor = link.cursor()

print(cursor.rowcount) #打印受影响行数

3. 执行sql语句

# 执行sql语句

sql = 'select * from user1'

# 执行完sql语句，返回受影响的行数

num = cursor.execute(sql)

获取结果集

result1 = cursor.fetchone()
print(result1)

关闭连接

cursor.close()
link.close()

注意：

写完代码后，需要将py文件添加可执行权限

sudo chmod +x conndb.py
./conndb.py

9.2 pymysql中事务处理

pymysql默认是没有开启自动提交事务，所以我们如果进行增、删、改，就必须手动提交或回滚事务。

sql = 'delete from user where id=%s' % user_id

# 如果要执行增删改语句的时候，下面的就是固定格式
try:
    cursor.execute(sql)
    # 如果全部执行成功，提交事务
    link.commit()
    print(cursor.lastrowid) #获取最后插入记录的自增id号
except Exception as e:
    print(e)
    link.rollback()
finally:
    cursor.close()
    link.close()