阶段学习总结之05
概要
day21 约束条件（补充）
-
约束条件之主键
-
约束条件之自增
-
约束条件之外键
-
修改表的SQL语句
day22 单表查询
-
单表查询关键字
-
多表查询两种策略
day23 多表查询
-
补充知识
-
多表查询理论
-
可视化软件navicat
day24 多表查询练习与Python操作数据库
-
多表查询练习题
-
Python操作MySQL数据库
-
PyMySQL模块基本使用
day25 PyMySQL其它操作与数据库知识补充
-
pymysql其他操作
-
SQL注入
-
事务
-
用户管理
-
索引
-
辅助知识补充
详情
day21 约束条件（补充）
-
约束条件之主键
主键:primary key 1.从约束条件的层面看,主键约束相当于非空且唯一 2.主键是InnoDB存储引擎组织数据表的依据 (1) InnoDB规定一个表必须'有且只有'一个主键 (2) 如果未指定主键,则会自动采用隐藏字段作为主键 (3) 如果表中没有主键但存在'非空且唯一'的字段,则自上而下的第一个该类字段会自动变成该表的主键 (4) 主键具有索引的功能可以加快查询速度,如果是隐藏字段则无法加快查询速度,故不推荐使用此方式 (5)在SQL语句中,除了可以将单列设为主键以外,也可以将多列的组合设为主键
-
约束条件之自增
语法: id int primary key auto_increment 1.主键自增的表插入数据时无需对主键赋值 2.创建表时,一般将id类字段作为主键 特点: 1.delete操作删除数据时不会重置或回退当前自增主键的值 eg: delete from test_auto_increment where id=2; 2.truncate操作清空数据时可以同时清空当前自增主键的值 eg: truncate test_auto_increment;
-
约束条件之外键
如果只有一张表,字段数很多的时候,使用上会有不便 1.表结构不清晰 2.表数据重复 3.数据扩展性很差 如果要缓解上述问题,需要将表拆开,并添加一个表示表关系的字段——外键 表关系的种类 判断方法 1.先看表1 问:表1中的一条记录能否对应表2中的多条记录? 答:可以/不可以 2.再看表2 问:表2中的一条记录能否对应表1中的多条记录? 答:可以/不可以 3.由此可见 双方都可以,即为"多对多" 一方可以,另一方不可以,即为"一对多" 双方都不可以,即为"没有关系"或"一对一" 外键创建位置 一对多的表关系中,外键字段创建在多的那一方 多对多的表关系中,外键字段需创建在第三张关系表中 一对一的表关系中,外键可以创建在任意一张表,最好创建在查询频率高的表中和一对多的表关系的区别在于外键具有唯一约束
外键的特性
创建表时先写基本字段类型与约束条件，然后在考虑外键关系。
1.创建表时必须先创建被关联表(自身不存在外键字段的表),否则报错 2.插入记录时必须先插入被关联表的记录,再插入关联表的记录 3.被关联表中的记录无法自由删除和修改 4.要实现被关联表和关联表外键字段的自由删除和修改,则需使用级联更新/级联删除功能
-
修改表的相关SQL语句
1.修改表名 alter table 旧表名 rename 新表名; 2.增加字段 alter table 表名 add 字段名 数据类型[完整性约束],add 字段名 数据类型[完整性约束]; # 增加多个字段 alter table 表名 add 字段名 数据类型[完整性约束] first; #在首位增加字段(很少用) alter table 表名 add 字段名 数据类型[完整性约束] after 字段名; # 在某字段后增加字段 3.删除字段 alter table 表名 drop 字段名; 4.修改字段 alter table 表名 modify 字段名 数据类型 [完整性约束]; alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束] alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束] modify关键字只能修改字段数据类型和完整性约束条件,不能修改字段名 change关键字能修改字段名、字段数据类型和完整性约束条件
day22 单表查询
-
单表查询关键字
注意事项 1.书写SQL语句时,可以先写主体,即 select * from 表名,再根据实际需求添加关键字并修改具体查询字段。 2.使用命令操作时最好在命令结束之后检查一下执行情况 3.窗口由于表字段较多而展示错乱时可以使用\G分行展示 查询SQL语句书写自由度较高,同一结果可由多种写法达成。
关键字——筛选where
1.查询范围内数据 select * from emp where id>=3 and id<=6; 查询条件为一个数值范围时也可以用between关键字(结果相同) select * from emp where id between 3 and 6; 2.查询特定几个选项的数据 select * from emp where salary=20000 or salary=18000 or salary=17000; 查询条件为限制多个选项时也可以用in关键字(结果相同) select * from emp where salary in (20000,18000,17000); 3.模糊查询 占位符号: %:匹配任意个数的任意字符 _:匹配单个个数的任意字符 eg: select name,salary from emp where name like '%o%'; select name,salary from emp where name like '____'; 也可使用函数,两种写法结果相同 select name,salary from emp where char_length(name)=4;
关键字——分组group by
需求中有'每个'的,通常需要分组解决。 select post from emp group by post; 分组之后默认只可以直接获取到分组的依据 无法再获取内部单个个体数据 如果想要获取需要借助于一些方法 set global sql_mode="strict_trans_tables,only_full_group_by"; 聚合函数 用于分组之后的数据处理 1. max 最大值 2. min 最小值 3. avg 平均值 4. sum 求和 5. count 计数
关键字——过滤 having
过滤having与筛选where的功能很相似。 相同: 都有筛选记录的功能 不同: where 用于分组前;having用于分组后 eg: 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门 思路: 1.先统计各部门的工资 select post,avg(salary) from emp group by post; 2.对分组前数据进行筛选 select post,avg(salary) from emp where age>30 group by post; 3.对分组后数据进行过滤 select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
关键字——去重 distinct
完全相同的查询结果才能去重 select distinct gender from emp;
关键字——排序 order by
1.默认升序 select * from emp order by salary; 2.升序关键字 asc 默认可省略 select * from emp order by salary asc; 3.降序关键字 desc select * from emp order by salary desc;
关键字——分页 limit
使用方式 limit 参数; 参数表示条数 limit 参数1,参数2; 第一个参数表示起始位置,第二个参数表示条数 select * from emp limit 5; 显示5条 select * from emp limit 8,2; 跳过8条开始,显示2条 查询最高的记录详情简便办法: 可以按该列排序,再使用limit取第一条记录
关键字——正则表达式 regexp
eg:查询姓名以字母j开头，以n或者y结尾的记录 select * from emp where name regexp '^j.*(n|y)$';
day23 多表查询
-
补充知识
1.group_concat() 用于分组之后,获得除分组依据外其他字段,其本质类似于拼接,该方法还可以获取多个字段并拼接分隔符 2.concat() 用于分组之前拼接字段,也可以加分隔符 3.concat_ws() 用于分组之前,相同分隔符的情况,首先指定共同分隔符 4.as 起别名 (1)字段别名(as可省略,由于会使语法结构不明显,故不推荐) select id as '编号',name as '姓名',gender as '性别' from emp; (2)表别名(常用于多表查询) select * from emp as t1 where t1.id>5;
-
多表查询理论
1、查询SQL语句执行的结果类似于一张表 2、涉及多张表的语句会有相同字段名，需要在字段名前面加表名限制 多表查询有两种方法: 联表 1.内连接 inner join 只展示两张表的对应数据 2.左连接 left join 以关键字左边的表为基准只展示左表所有数据,不对应的字段显示null 3.右连接 right join 以关键字右边的表为基准只展示右表所有数据,不对应的字段显示null 4.全连接 union 等价于左连接与右连接的结合,不对应的数据显示null 子查询 本质:将一张表的查询结果加括号,再作为条件给别的SQL语句使用。等同于分步骤操作 """有些复杂查询需要两种方法相结合"""
-
可视化软件navicat
本质：SQL语句封装并执行，可提高效率。 Navicat软件需要收费 有些软件不具有的功能也可以修改生成的SQL语句完成 SQL语句可保存在.sql文件中
day24 多表查询练习与Python操作数据库
-
多表查询练习
1、查询所有的课程的名称以及对应的任课老师姓名
select c.cname as '课程名称',t.tname as '任课老师姓名' from course as c left join teacher as t on c.teacher_id=t.tid;
2、查询平均成绩大于八十分的同学的姓名和平均成绩
select st.sname as '学生姓名',sc.an as '平均成绩' from student as st inner join (select student_id,avg(num) as an from score group by student_id having avg(num)>80) as sc on st.sid=sc.student_id;
3、查询没有报李平老师课的学生姓名
select sname from student where sid not in ( select distinct student_id from score where course_id in ( select cid from course where teacher_id in( select tid from teacher where tname='李平老师' ) ) );
4、查询没有同时选修物理课程和体育课程的学生姓名
select sname from student where sid in( select student_id from score where course_id in( select cid from course where cname in ('物理','体育') ) group by student_id having count(course_id)=1 );
5、查询挂科超过两门(包括两门)的学生姓名和班级
select student.sname,class.caption from student inner join class on student.class_id=class.cid where student.sid in (select student_id from score where num<60 group by student_id having count(num)>=2);
-
Python操作MySQL数据库
通过Python代码可以操作MySQL数据库,需要借助第三方的模块。 第三方模块:非内置的模块 # 通过网络下载第三方模块 下载步骤: 1.将Python解释器下scripts目录配置到环境变量 2.下载模块 (1) pip3 install pymysql # 在cmd或者pycharm终端输入) (2) settings --> python interpreter --> +号再搜索 # pycharm界面操作 3.远程仓库 通过pip3下载模块时默认从国外的仓库下载模块文件 有时会很慢,可以尝试使用国内的仓库 (1)阿里云 http://mirrors.aliyun.com/pypi/simple/ (2)豆瓣 http://pypi.douban.com/simple/ (3)清华大学 https://pypi.tuna.tsinghua.edu.cn/simple/ (4)中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/ (5)华中科技大学http://pypi.hustunique.com/ # 切换库 1.cmd终端 pip3 install 模块名 -i 仓库地址 2.pycharm更改仓库地址 下载模块的界面点击 Manage Repositories 3.永久更改 修改Python解释器内置配置文件 # pip3命令报错 1.timeout 网络不稳定,需多次尝试或更换网络 2.warning pip3工具版本过低,需要更新,提示信息中有更新命令 3.无明显关键字,有红色提示信息 当前环境不支持,需先按要求准备环境
-
PyMySQL模块基本使用
1.引入模块 import pymysql 2.创建连接对象 conn = pymysql.connect( host='127.0.0.1', # 服务端地址 port=3306, # 端口,MySQL默认3306 user='root', # 用户名 password='123456', # 密码 database='db_dbcx', # 指定库 charset='utf8' # 字符编码 ) 3.生成游标对象,等待用户输入命令(该参数将结果封装成字典列表) cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 4.书写SQL语句 sql = 'select * from teacher' 5.执行SQL语句 cursor.execute(sql) 6.获取执行结果 res = cursor.fetchall() print(res)
day25 PyMySQL其它操作与数据库知识补充
-
pymysql其他操作
pymysql.connect()中 参数password支持简写passwd 参数database支持简写db cursor = conn.cursor() 1.游标对象默认可以执行查询操作 sql = 'select * from teacher' 2.游标对象执行涉及到操作数据的SQL语句不直接生效 sql = 'insert into teacher(tname) values("Leo")' 可以看到在数据库中数据并未增加 解决: 方法1:每次执行完操作数据后提交 conn.commit() 方法2:pymysql.connect中添加自动确认参数 autocommit=True 3.cursor.fetchall() #从结果中获取所有数据 4.cursor.fetchone() # 从结果中获取第一条数据 5.cursor.fetchmany(3) # 从结果中获取指定条数的数据 6.cursor.scroll(2,'relative') # 游标相对当前位置移动,第一个参数为正则向右,为负则向左 cursor.scroll(1,'absolute') # 游标相对于起始位置移动,第一个参数为正则向右,为负则向左 7.cursor.executemany(sql, [(记录1的参数),(记录2的参数)] # 一次插入多条记录
-
SQL注入
# 直接简单地拼接SQL语句可能会导致SQL注入问题 sql = "select * from userinfo where name=%s and password=%s" 1.只需要用户名正确即可通过验证 select * from userinfo where name='jasonNB' -- ajsdkjaskldjklasd' and password='' 2.无论用户名密码是什么均可通过验证 select * from userinfo where name='xxx' or 1=1 -- jkasdjaksldklsajd' and password='' SQL注入的本质:利用一些特殊符号或特殊语法形式拼接处违背常理的语句。 涉及到关键性的数据(用户名/密码等)不要手动拼接,使用能够过滤特殊符号的固定方法
-
事务
事物的四大特性:ACID 1.原子性---A 原子性是指事务包含的所有操作只能全部执行成功,只能全部执行失败并回滚，即成功指的是该事务涉及到的所有记录均更改成功,失败指的是该事务对数据库的数据无影响。 2.一致性---C 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。例如转账,转账前后双方的金额和必须是相同的。 3.独立性---I 独立性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰。例如转账,当一个用户需要转账给另一个用户的事务未结束时,第三方无法和这两个用户之间产生交易。 4.持久性---D 持久性是指一个事务被提交=以后，对数据库中的记录的改变就是永久性的，即使数据库系统遇到故障也不会丢失提交事务的操作。 事务操作步骤 1.先开启事务操作 start transaction; 2.进行修改操作 # 买支付100元 update user set balance=900 where name='jason'; # 中介拿走30元 update user set balance=1030 where name='kevin'; # 卖家拿到70元 update user set balance=1070 where name='tony'; 3.这时可以进行回滚操作 rollback; 4.如果确认数据没有问题,不需要回滚,则可以提交事务 commit;
-
用户管理
1.创建用户 create user 用户名 identified by '密码' 2.修改密码 set password for 用户名 = Password('新密码');# 指定用户 set password = Password('新密码');# 当前登录用户 3.重命名用户 rename user 旧用户名 to 新用户名; 4.删除用户 drop user 用户名; 5.查看用户访问权限 show grants for 用户名; 6.授予访问权限 grant select on db_dbcx.* to 用户名; 7.撤销权限 revoke select on db_dbcx.* from 用户名; 操作权限的单位 1.整个服务器:grant all/revoke all 2.整个数据库:on db.* 3.特定表:on db.t1
-
索引
1.索引的概念 类似于书的目录,一种能加快查询速度的数据结构。 2.索引也是键,能使存储引擎快速查询到记录 主键 primary key 可以加快查询,有其他的功能 唯一键 unique 可以加快查询,有其他的功能 索引键 index key 只能加快查询 外键 foreign key 与索引无关,不能提升查询速度 """ 主键 即聚集索引,叶子结点放的一条条完整的记录 唯一键和索引键 即辅助索引,叶子结点存放的是该字段对应的那条记录的主键值 """ 3.索引的使用 (1)在表中有大量数据的前提下,创建索引速度会很慢 (2)在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低 (3)并不是查询速度慢就将该字段制作成索引的,修改频率高的字段不适合变成索引
-
辅助知识补充（了解为主）
视图
1.视图概念 用select查询得到的结果可被当成一张表,并被保存下来以供下次使用,该表称为视图 2.视图使用 使用视图可以避免重复查询同样的数据 3.语法 create view teacher2course as select语句; 一般不推荐使用视图 1.视图记录不会随着表的更改而更改 2.创建过多视图会导致使用上的不便
触发器
1.触发器的概念 在满足对某张表数据的增、删、改的情况下，自动触发的功能。 2.触发器使用场景 对某一张表数据增删改行为执行前后需自动执行的额外操作,即另一段SQL语句。 3.语法 create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin sql语句 end 4.触发器命名习惯 一般命名为触发器简写_前或后_操作_表名 eg:tri_before_insert_test01 5.删除触发器 drop trigger 触发器名;
存储过程
1.存储过程的概念 SQL中的自定义函数 2.语法 # SQL语句需要由分号结尾,故定义存储过程需要先重定义结束符 delimiter $$ # 类似创建函数 create procedure p1() # 存储过程需要执行的操作,前后需分别加begin和end begin select * from cmd; end $$ # 存储过程定义结束,将结束符修改回分号 delimiter ; # 像调用函数一样调用存储过程 call p1()
1.可以在cmd终端使用help 函数名查看相关信息 2.移除指定字符(默认移除空格) Trim、Lrim、Rrim SELECT TRIM(' bar '); -> 'bar' SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx'
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar'
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'
3.大小写转换 Lower、Upper
4.获取左右指定个数字符 Left、Right
5.返回读音相似的值(英文环境效果明显)
eg: where Soundex(name)=Soundex('J.Lie')
6.日期格式 date_format(一般采用年-月-日)
日期可以按年月分组,查询年与月
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
7.其它时间函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
浙公网安备 33010602011771号