一、连接查询
1.接连查询:
同时查询多个表中的数据
SELECT * FROM 表名1,表名2... WHERE 连接条件;
连接查询如果不加连接条件，结果是一个笛卡尔积：（a,b,c）(1,2,3) --> (a1,a2,a3,b1,b2,b3,c1,c2,c3)
查询每个老师对应的学院
SELECT teaname, collname FROM tb_college, tb_teacher WHERE tb_teacher.collid=tb_college.collid;
查询学生姓名对应的学科名的分数
SELECT stuname, couname, mark FROM tb_student, tb_course, tb_score
WHERE tb_score.stuid=tb_student.stuid and tb_score.couid=tb_course.couid;
查询平均分高于80分的学生名字和对应的平均分分数
注意:如果连接查询的时候既有连接条件又有筛选条件，我们要把筛选条件写在连接条件后边
SELECT stuname, avg_mark FROM
tb_student,
(SELECT stuid, AVG(mark) as avg_mark FROM tb_score GROUP BY(stuid)) as temp_t
WHERE tb_student.stuid = temp_t.stuid and avg_mark>80;
2.内连接
SELECT * FROM 表1 inner join 表2 on 表2的连接条件 inner join 表3 on 表3的连接条件 ...;
注意: 中间表写在最前面(存在关联其他表外键的表)
查询学生姓名对应的学科名的分数
SELECT stuname, couname, mark FROM tb_score
INNER JOIN tb_studnt ON tb_student.stuid=tb_score.stuid
INNER JOIN tb_course ON tb_course.couid=tb_score.couid
WHERE mark < 60;
3.外连接
外连接分为左外连接、右外连接和全连接， 但是在MySQL中支持左外连接和右外连接
表1(左表) left/right/inner join 表2(右表)
左外连接：将左表中对应字段的所有数据取出，然后再对应的右表中字段的值，如果右表对应的值不存在结果就为null
右外连接：将右表中对应字段的所有数据取出，然后再对应的左表中字段的值，如果左表对应的值不存在结果就为null
查所有学生名对应的成绩
SELECT stuname, mark FROM tb_score INNER JOIN tb_student ON tb_student.stuid=tb_score.stuid;
SELECT stuname, mark FROM tb_score RIGHT JOIN tb_student ON tb_student.stuid=tb_score.stuid;
二、字段索引
字段的索引就相当于于目录，作用是为了能够快速的对这个字段进行查找
添加索引的好处是可以大大的提高查询效率； 缺点是：1.会消耗额外的存储空间， 2.会让添加和删除的效率降低
建议: 1.索引不能滥用 2.如果项目中针对某个字段的查询很频繁，建议加个对应的索引
EXPLAIN： 获取执行计划
EXPLAIN SELECT * FROM tb_student WHERE stuid=110;
EXPLAIN SELECT * FROM tb_student WHERE stuname='张三';
添加索引: create index 索引名称 on 表名 (字段名); - 给指定表中的指定字段添加索引
CREATE INDEX index_stuname on tb_student (stuname); -- 给名字加索引
CREATE INDEX index_stuname1 on tb_student (stuname(1)); -- 按姓加索引
删除索引
ALTER TABLE tb_student DROP INDEX index_stuname;
三、DCL
1.创建用户
create user 用户名@登录地址
登录地址: （限制用户能够登录MySQL的主机地址）， ip地址(指定地址), localhost(数据库本机), %(任何位置)
CREATE USER 'zhangshan'@'%' IDENTIFIED BY 'yuting123456';
删除用户: drop user 用户名;
DROP USER 'zhangshan';
2.授权
grant 权限类型 on 数据库.对象 to 用户名;
GRANT SELECT on school.tb_student TO 'zhangshan';
GRANT UPDATE on school.tb_student TO 'zhangshan';
GRANT all PRIVILEGES ON school.* TO 'zhangshan'; -- 添加所有权限
GRANT all PRIVILEGES ON school.* TO 'zhangshan' WITH GRANT OPTION; -- 添加所有权限，并且能够将自己的权限再授权给其他用户
3.召回授权
REVOKE 权限类型 on 数据库.对象 from 用户名;
REVOKE DELETE on school.* FROM 'zhangshan';
REVOKE all PRIVILEGES on school.* FROM 'zhangshan';
REVOKE all PRIVILEGES on school.* FROM 'zhangshan';
REVOKE SELECT on school.tb_student FROM 'zhangshan';
REVOKE UPDATE on school.tb_student FROM 'zhangshan';
4.事务
完成一个任务需要执行多条sql，但是要求这多个操作中只要有一个操作失败，这个任务就失败，数据全部还原；所有的操作都成功，整个任务才成功的时候就使用事务
开启事务环境
BEGIN;
UPDATE tb_student set stuname='444' WHERE stuname='222';
UPDATE tb_student set birth2='1990-1-1' WHERE stuname='222';
提交事务(只有begin到commit之间的所有的sql都执行成功，才会执行commit; 否则执行rollback)
COMMIT;
事务回滚(放弃beigin到commit之间执行成功的所有sql语句的结果)
ROLLBACK;
四、在python中使用pyMySQL
第一步：按照第三方库pymysql
pip install -i https://pypi.doubanio.com/simple pymysql
第二步：创建连接
conn = pymysql.connect(host='localhost', # 本机用localhost，远程用IP地址
port=3306, # 端口号
user='root', # 用户名
password='123456', # 密码
database='db_myschool', # 数据库
charset='utf8', # 支持中文
autocommit=True) # 每次执行sql语句自动提交
第三步：
# 获取游标对象 - 提供数据库的上下文
cursor = conn.cursor()
第三步：
# 查询所有数据
cursor.execute('select * from tb_student')
for row in cursor.fetchall():
print(row)
print("共%d条数据。" % cursor.rowcount)
第四步：
# 关闭连接
conn.close()
网友评论