day36总结

day04 数据库

昨日回顾

单表的操作:

		分组：
			group by：
				select count(id) from xxx group by gender;
			having：
				select count(id) from xxx group by gender having count(id) > 23;
			group by > having

		排序：
			order by 要排序的字段名(id)
			升序: asc 例子：order by id asc;
			降序: desc 例子： order by id desc;

			order by age asc, id desc;
			先根据年龄升序，然后如果年纪一样的时候，再根据id进行降序
		分页：
			limit offset,  size
			offset: 行数据的索引
			size： 取多少行数据

			select * from 表名 limit 0, 10

		先后顺序：
			where > group by > having > order by > limit

多表关系

	一对多：

		create table department(
			id int,
			name varchar(32) not null default ''
		)charset utf8;

		create table userinfo (
			id int,
			name varchar(32) not null default '',
			depart_id int,
			constraint 外键名 foreign key (depart_id) references department(id)
		)charset utf8;

	多对多：
		create table boy(
			id int,
			bname varchar(32) not null default ''
		)

		create table girl(
			id int,
			gname varchar(32) not null default ''
		)

		create table boy2girl(
			id int,
			bid int ,
			gid int ,
			constraint 外键名 foreign key (bid) references boy(id),
			constraint 外键名 foreign key (gid) references girl(id),
		)

	一对一：

		create table userinfo (
			id int,  #### 主键id
			name varchar(32),
			salary decimal

		)

		userinfo:

		id   name
		1    zekai
		2    xxxx
		3    hhhh
		create table pri(
			id int,
			salary decimal(23,4)
			uid int,
			unique(uid)
			constraint 外键
		)

		pri

		id  salary    	uid  (外键 + unique)
		1    1234.5		 1
		2 	2345.56      2
		3   897.56       3
		4   5678.56      4

多表查询：

3. 

	left join

		select * from department left join userinfo on department.id = userinfo.depart_id where group by order by limit ;
		right join

		inner join

		join

今天内容：

作业讲解

python操作mysql

		安装: pip install pymysql
		sql注入问题

		输入用户名:zekai ' or 1=1 #
		输入密码:dsadsa
		select * from user where name='zekai ' or 1=1 #' and password='dsadsa'
		[{'id': 1, 'name': 'zhangsan', 'password': ''}, {'id': 2, 'name': 'zekai', 'password': '123'}, {'id': 3, 'name': 'kkk', 'password': ''}]

产生的原因：
因为过于相信用户输入的内容， 根本没有做任何的检验

	解决的方法：
		sql = "select * from user where name=%s and password=%s"

		cursor.execute(sql, (user, pwd))

	连接：
		### 连接数据库的参数
		conn = pymysql.connect(host='localhost',user='root',password='123qwe',database='test',charset='utf8')
		# cursor = conn.cursor() ### 默认返回的值是元祖类型
		cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) ### 返回的值是字典类型 (*********)


	查：
		fetchall() ： 取出所有的数据 返回的是列表套字典
		fetchone() ： 取出一条数据 返回的是字典
		fetchmany(size) ： 取出size条数据 返回的是列表套字典

	增：
		sql = "insert into user (name, password) values (%s,  %s)"

		# cursor.execute(sql, ('xxx', 'qwe'))  ### 新增一条数据

		data = [
			('zekai1', 'qwe'),
			('zekai2', 'qwe1'),
			('zekai3', 'qwe2'),
			('zekai4', 'qwe3'),
		]
		cursor.executemany(sql, data)  ### 新增多条数据

		#### 加如下代码
		conn.commit()
			print(cursor.lastrowid)   ### 获取最后一行的ID值

	修：
		sql = "update user set name=%s where id=%s"

		cursor.execute(sql, ('dgsahdsa', 2))

		conn.commit()

		cursor.close()
		conn.close()

	删除：
		sql = "delete from user where id=%s"

		cursor.execute(sql, ('dgsahdsa', 2))

		conn.commit()

		cursor.close()
		conn.close()

索引

为啥使用索引以及索引的作用：

使用索引就是为了提高查询效率的

类比：
字典中的目录

索引的本质：
一个特殊的文件

索引的底层原理：

B+树

索引的种类：(**************************)

主键索引： 加速查找 + 不能重复 + 不能为空 primary key
唯一索引： 加速查找 + 不能重复 unique(name)
联合唯一索引：unique(name, email)
例子：
zekai 123@qq.com
zekai 123@qq.cmm

普通索引： 加速查找 index (name)
联合索引： index (name, email)

	索引的创建：

		主键索引：

			新增主键索引：
				create table xxx(
					id int auto_increment ,
					primary key(id)
				)

				alter table xxx change id id int auto_increment primary key;

				alter table t1 add primary key (id);

			删除主键索引：
				mysql> alter table t1 drop primary key;


		唯一索引：

			新增：
				1.
				create table t2(
					id int auto_increment primary key,
					name varchar(32) not null default '',
					unique u_name (name)
				)charset utf8

				2.
				CREATE  UNIQUE   INDEX  索引名 ON 表名 (字段名) ;
					create  unique index ix_name on t2(name);

				3.
				alter table t2 add unique index ix_name (name)

			删除：
				alter table t2 drop index u_name;

		普通索引：

			新增：
				1.
				create table t3(
					id int auto_increment primary key,
					name varchar(32) not null default '',
					index u_name (name)
				)charset utf8

				2.
				CREATE  INDEX  索引名 ON 表名 (字段名) ;
					create   index ix_name on t3(name);

				3.
				alter table t3 add  index ix_name (name)

			删除：
				alter table t3 drop index u_name;



		索引的优缺点：

			通过观察 *.ibd文件可知：

				1.索引加快了查询速度
				2.但加了索引之后，会占用大量的磁盘空间
		索引加的越多越好？

			不是

		不会命中索引的情况：

			a. 不能在SQl语句中，进行四则运算， 会降低SQL的查询效率

			b. 使用函数
				select * from tb1 where reverse(email) = 'zekai';
			c. 类型不一致
				如果列是字符串类型，传入条件是必须用引号引起来，不然...
				select * from tb1 where email = 999;

			#排序条件为索引，则select字段必须也是索引字段，否则无法命中
			d. order by
				select name from s1 order by email desc;
				当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢

				select email from s1 order by email desc;
				特别的：如果对主键排序，则还是速度很快：
					select * from tb1 order by nid desc;

			e. count(1)或count(列)代替count(*)在mysql中没有差别了

			f. 组合索引最左前缀

				什么时候会创建联合索引？

					根据公司的业务场景， 在最常用的几列上添加索引

					select * from user where name='zekai' and email='zekai@qq.com';

					如果遇到上述业务情况， 错误的做法：
						index ix_name (name),
						index ix_email(email)

					正确的做法：
						index ix_name_email(name, email)

如果组合索引为：ix_name_email (name,email) ************

				where name='zekai' and email='xxxx'       -- 命中索引

				where name='zekai'   -- 命中索引
				where email='zekai@qq.com'                -- 未命中索引

				例子：

					index (a,b,c,d)

					where a=2 and b=3 and c=4 and d=5   --->命中索引

					where a=2 and c=3 and d=4   ----> 未命中
						g:
				explain

				mysql> explain select * from user where name='zekai' and email='zekai@qq.com'\G
				*************************** 1. row ***************************
						   id: 1
				  select_type: SIMPLE
						table: user
				   partitions: NULL
						 type: ref       索引指向 all
				possible_keys: ix_name_email     可能用到的索引
						  key: ix_name_email     确实用到的索引
					  key_len: 214            索引长度
						  ref: const,const
						 rows: 1            扫描的长度
					 filtered: 100.00
						Extra: Using index   使用到了索引

索引覆盖：

			select id from user where id=2000;

慢查询日志：
查看慢SQL的相关变量

			mysql> show variables like '%slow%'
				-> ;
			+---------------------------+-----------------------------------------------+
			| Variable_name             | Value                                         |
			+---------------------------+-----------------------------------------------+
			| log_slow_admin_statements | OFF                                           |
			| log_slow_slave_statements | OFF                                           |
			| slow_launch_time          | 2                                             |
			| slow_query_log            | OFF   ### 默认关闭慢SQl查询日志， on                                          |
			| slow_query_log_file       | D:\mysql-5.7.28\data\DESKTOP-910UNQE-slow.log | ## 慢SQL记录的位置
			+---------------------------+-----------------------------------------------+
			5 rows in set, 1 warning (0.08 sec)

			mysql> show variables like '%long%';
			+----------------------------------------------------------+-----------+
			| Variable_name                                            | Value     |
			+----------------------------------------------------------+-----------+
			| long_query_time                                          | 10.000000 |

		配置慢SQL的变量：
			set global 变量名 = 值

			set global slow_query_log = on;

			set global slow_query_log_file="D:/mysql-5.7.28/data/myslow.log";

			set global long_query_time=1;