第十章数据库

10.1 数据库

常见函数

database(),use(),concat(),now(),passward()

10.1.1数据库的引入

自己处理一些数据备份,容错的措施
很多功能如果只是通过操作文件来改变数据是非常繁琐的
- 程序员需要做很多事情
多于多个进程或多台机器操作同一份数据时
- 程序员自己解决并发和安全问题比较麻烦

c/s架构的操作数据文件的一个管理工具
- 1.帮助我们解决并发问题
- 2.帮我们用更简单更快捷的方式完成数据的增删改查
- 3.给我们提供一些容错,高可用机制
- 4.权限的认证

10.1.2数据库管理系统(DBMS)

专门用来管理数据,帮助用户更简洁的操作数据的软件
文件夹 --------数据库(database简写db)
数据库管理员---------DBA(database administrator)
数据库(管理系统)
- 关系型数据库
  - mysql 开源
    - 小公司,互联网公司
  - oracle 收费,比较严谨,安全性比较高
    - 用于国企,事业单位银行,金融行业
  - sql server
- 非关系数据库
  - redis
  - mongodb

10.2初识mysql

10.2.1 mysql的安装

10.2.2 mysql的cs架构

mysqld install 安装数据库服务
net start mysql 启动数据库的server端
net stop mysql 停止server端
客户端可以是python代码,也可以是一个程序
- mysql.exe 是一个客户端
- mysql -u用户名 -p密码

10.2.3mysql的用户和权限

在安装一个数据库后,有一个最高权限的用户root
mysql -h 连接ip地址,如果不写默认连接本地
我们的mysql客户端不仅可以连接本地的数据库,也可以连接网络上某个数据库的server端
mysql>select user()
- 查看当前用户是谁
mysql>set password = password('密码')
- 设置密码
mysql>create user 's21'@'192.168.12.%' identified by '123';
- 创建用户
mysql>show databases;
- 查看文件夹
mysql>creat database day37;
- 创建文件夹
mysql>grant all on day37. * to 's21'@'192.168.12.%'; #赋予day37中所有表的所有权限
- 授权并创建用户
- flush privileges 让权限立即生效
- mysql>grant all on day37. * to 'alex'@'%' identified by '123';
mysql>show grants for 's21'@'192.168.12.%';
- 查看当前用户有哪些权限

10.2.4库,表.数据

创建库,创建表 DDL(数据库定义语言)
存数据,删数据,修改语言,查看 DML(数据库操纵语言)
grant revoke DCL(控制权限)

10.2.3.1库操作

mysql>creat database 数据库名;
- 创建库
mysql>show databases;
- 查看当前有多少数据库
mysql>select database();
- 查看当前使用的数据库是什么
mysql>use 数据库名;
- 切换到这个数据库(文件夹)下
mysql>drop datatbase
- 删库(不要轻易使用,除非逼不得已)

10.2.3.2表操作

mysql>show tables;
- 查看当前文件夹中有多少张表
mysql>create table student(id int,name char(4));
- 创建表
mysql>drop table student;
- 删除表格
mysql>desc 表名
- 查看表字段的基础信息
mysql>show create table 表名
- 能够看到和这张表相关的所有信息
mysql>describe 表名

10.2.3.3操作表中的数据

mysql>insert into student values(1,'alex')
- 数据的增加
mysql>select * from student;
- 数据的查看
mysql>update 表 set 字段名 = 值
- 修改数据
- 例 : mysql>update student set name = 'yuan';
  - 会修改全部
- 例 : mysql>update student set name = 'yuan' where id = 2 ;
  - 会修改要修改内容
mysql>delete from 表名字
- 删除数据

  #总结:
  #SQL:结构化查询语言(Structured Query Language)
     #DDL((Data Definition Language) 数据库定义语言
     #是用于描述数据库中要存储的现实世界实体的语言。
        #创建用户
           # mysql>create user '用户名'@'%'   表示网络可以通讯的所有ip地址都可以使用这个用户名
           # mysql>create user '用户名'@'192.168.12.%'   表示192.168.12.0网段的用户可以使用这个用户名
           # mysql>create user '用户名'@'192.168.12.87'  表示只有一个ip地址可以使用这个用户名
     #创建库
        # mysql>creat database day38;
        #创建表
        # mysql>creat table 表名(字段名 数据类型(长度)，字段名 数据类型(长度)，)
      
    #DML(Data Manipulation Language) 数据库操纵语言
    #是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令，因此有很多开发人员都把加上SQL的SELECT语句的四大指令以“CRUD”来称呼。
      # mysql>insert into student values(1,'alex')  增
      # mysql>delete from 表名字                     删
      # mysql>update 表 set 字段名 = 值              改
      # mysql>select/show                           查
          #select
            # mysql>select user(); 查看当前用户
            # mysql>select database();  查看当前所在的数据库
          #show
            # mysql>show databases;   查看当前的数据库有哪些
            # mysql>show tables； 查看当前的库中有哪些表
          # mysql>desc 表名;  查看表结构
          # mysql>use 库名;   切换到这个库下
          
     #DCL(Data Control Language) 数据库控制语言
      #grant select on 库名.* to '用户名'@'ip地址/段' identified by '密码'

10.3mysql中的存储引擎

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。

#查看当前的默认存储引擎:
mysql> show variables like "%engine%";
#查询当前数据库支持的存储引擎
mysql> show engines \G;
#索引  -----------数据库的目录

#查看当前的默认编码
mysql>show variabls like '%chara%'

10.4表结构

10.4.1表的存储方式

存储方式一: MyISAM 5.5及以下默认存储方式
- 存储文件个数:表结构表中的数据,索引,
- 适合做读,插入数据比较频繁的,对修改和删除涉及较少,索引和数据分开存储
- 支持表级锁
- 不支持行级锁不支持事务不支持外键
存储方式二: innoDB 5.6及以上默认存储方式
- 存储文件个数 : 表结构,表中的数据
- 适合并发较高,对事物一致性要求较高,行队更适应频繁的删除和修改操作,索引和数据存在一起
- 支持行级锁和表级锁
- 支持事务
- 支持外键
存储方式三: MEMORY内存
- 存储文件个数:表结构
- 优势 : 增删改查都很快(用于热点新闻)
- 数据存在内存中,表结构存在硬盘上,查询速度快
- 劣势 : 重启数据消失,容量有限

10.4.2创建表

表介绍

表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段

建表语句

mysql>create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件],

);
例一: mysql>create table t1 (id int,name char(4));
查看表结构 :
- mysql>desc 表名 #查看表字段的基础信息
- mysql>show create table 表名; #能够看到和这张表相关的所有信息
- mysql>describe 表名;
- mysql>show create table 表名 \G; #能显示较为整洁(加分号会报错)
- mysql>show create table 表名 \G #能显示较为整洁
  
  使用分号或者\G
  
  证明使用memory存储退出数据库后登录会清空.

10.4.3MySQL中的数据类型

10.4.3.1数值类型

整数

#总结
1.int默认是有符号的
2.它能表示的数字范围不被宽度约束
3.他只能约束数字的显示宽度

#mysql>create table t5 (id1 int unsigned,id2 int);

小数

# create table t6 (f1 float(5,2),d1 double(5,2));   会四舍五入
# create table t7 (f1 float,d1 double);
# create table t8 (d1 decimal,d2 decimal(25,20));

非常准确,能表示小数点后30位,后面不指定位数时默认保留整数

10.4.3.2日期和时间

# 类型
    # year 年            历史事件
    # date 年月日  
    # time 时分秒        运动会
    # datetime(timestamp) 年月日时分秒    交易时间,上班打卡时间

# create table t9(
# y year,d date,
# dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
# ts timestamp);

写入当前时间

10.4.3.3字符串类型

char 定长的单位
- 存储时 :alex -----> alex ,
varchar 变长的单位
- 存储时:alex ------> alex4

哪一种存储方式好?

vachar :节省空间,存储效率相对低
char :浪费空间,存储效率相对高,长度变化小的

#手机号码,身份证号,用户名,密码   char  *****
#评论,微博,说说,微信状态        varchar

create table t11 (name1 char(5),name2 varchar(5));

10.4.3.4ENUM和SET类型

#ENUM  枚举

create table t12(
name char(12),
gender ENUM('male','female'),
hobby set('抽烟','喝酒','烫头','洗脚')    #自动去重
);

10.4.4约束

unsigned 设置某一个数字无符号

not null 某一个字段不能为空

defalut 给某个字段设置默认值

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

只向id1字段添加值，会发现id2字段会使用默认值填充

单独向id2中添加数值时,id1会默认添加0

向id1，id2中分别填充数据，id2的填充数据会覆盖默认值

#not null不生效：
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

#直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

#配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
设置严格模式：
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

unique 设置某一个字段不能重复

法一:
法二:

注意上面姓名设置的为unique,所以不能再创建

联合唯一

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

auto_increment 设置某一个int类型的字段,自动增加

前提 :自增字段必须是数字且必须唯一,自带非空属性

primary key 设置某一字段不能为空且不能重复

主键 :约束这个字段 非空(not null)且唯一(unique),的字段,你指定的第一个非空且唯一自动定义诶主键
一张表只能设置一个主键且最好设置一个主键

联合主键(不常用)

create table t7(
    id int primary key,     # 你指定的第一个非空且唯一的字段会被定义成主键
    name char(12) not null unique
)

foreign key 外键,涉及到两张表

员工表

id age gender salary hire_date postname postid post_comment post_phone

外键 foreign key 涉及到两张表

#员工表
create table staff(
id  int primary key auto_increment,
age int,
gender  enum('male','female'),
salary  float(8,2),
hire_date date,
post_id int,
#foreign key(post_id) references post(pid)   外键关联的那张表的字段必须为unique
)       

#部门表
create table post(
    pid  int  primary key,
    postname  char(10) not null unique,
    comment   varchar(255),
    phone_num  char(11)
)

reference
级联删除和级联更新
- cascade方式 :在父表上update/delete记录时，同步update/delete掉子表的匹配记录
- set null方式 :在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null
- set null方式 :在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null
- Set default方式 :父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别
```
create table staff2(
id  int primary key auto_increment,
age int,
gender  enum('male','female'),
salary  float(8,2),
hire_date date,
post_id int,
#foreign key(post_id) references post(pid) on update cascade on delete set null
);
```

10.4.5修改表结构

修改表名
- alter table 表名 rename 新表名;
增加表名
- alter table 表名 add 字段名数据类型 [完整性约束条件];
删除字段
- alter table 表名 drop 字段名;
修改字段
- alter table 表名 modify 字段名数据类型 [完整性的约束条件];
  
  修改已经存在的字段的类型,宽度和约束
- alter table 表名 change CHANGE 旧字段名新字段名旧数据类型 [完整性约束条件…];
  
  修改已经存在的字段的类型,宽度,约束及字段名字
修改字段排列顺序/在增加的时候指定字段位置
- alter table 表名 modify age int not null after id
- alter table 表名 modify age int not null first

10.4.6 表与表之间关系

两张表中数据的关系
- 多对一 foreign key 永远是在多的那张表中设置外键
  - 多个学生都是一个班级的
  - 学生表关联班级表
  - 学生是多 ,班级是一
- 一对一 foreign key +unique 后出现的的那张表中的数据作为外键(并且约束这个外键唯一)
  - 客户关系表 :手机号码,招生老师,上次联系时间,备注信息
  - 学生表 :姓名,入学日期,缴费日期,结业时间
- 多对多产生第三张表,把两个关联关系的字段作为第三张表的外键
  - 书
  - 作者

10.4.7数据的操作

增加 insert

方式一: insert into 表名 values (值...);

所有在这个表中的字段都需要按照顺序被填写在这里
方式二: insert into 表名 (字段名1,字段名2.....) values (值......);

所有在字段位置填写了名字的字段和后面的值必须是一一对应
方式三: insert into 表名 (字段名1,字段名2.....) values (值...),(值...),(值...)

所有在字段位置填写了名字的字段和后面的值必须是一一对应

注 :value单数一次性写入一行数据

values复数一次性写入多行数据

# 第一个角度
    # 写入一行内容还是写入多行
    # insert into 表名 values (值....)
    # insert into 表名 values (值....)，(值....)，(值....)
    
# 第二个角度
    # 是把这一行所有的内容都写入
    # insert into 表名 values (值....)
    # 指定字段写入
    # insert into 表名(字段1，字段2) values (值1，值2)

删除 delete

delete from 表 where 条件;

修改 update

update 表 set 字段=新的值 where 条件；

查询 select *****

单表查询

company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int

#准备操作
#创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);


#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| emp_name     | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入记录
#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk



准备表和记录

select 语句

select * from 表;
select 字段,字段... from 表;
select distinct 字段,字段 from 表; #按照查出来的字段去重
select 字段*5 from 表 ; # 按照查出来的字段去重
select 字段 as 新名字,字段 as 新名字 from 表 # 按照查出来的字段去重
select 字段新名字 from 表 # 按照查出来的字段去重

#简单查询
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       employee;

CONCAT() 函数用于连接字符串

CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
 CONCAT_WS() 第一个参数为分隔符(根据':'进行拼接)
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee; 
   
   
结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       employee;

where语句

比较运算符 >,<,=,>=,<=,!=,<>
逻辑运算 条件的拼接
- 与 and
  
  select * from employee where sex ='female' and age = 18;
- 或 or
  
  select * from employee where salary=1000 or salary=2000;
- 非 not
范围筛选
- 多选一 字段名 in (值1,值2,值3)
  
  select * from employee where salary in (2000,3000,4000);
- 在一个模糊的范围里
  - 在一个数值区间 1w-2w之间 between
    
    select emp_name from employ where salary between(1000,2000);
  - 字符串的模糊查询 like
    - 通配符 % 匹配任意长度的任意内容
      
      select * from employee where emp_name like "程%";
    - 通配符 _ 匹配一个长度的任意内容
      
      select * from employee where emp_name like "程_ _";
  - 正则匹配 regexp 更加细粒度的匹配的时候
    - select * from 表 where 字段 regexp 正则表达式;
      
      select * from employee where eme_name regexp '^[a-z]{5}';
身份运算符null

要用 is unll / is not null 来判断

查看岗位描述不为空的员工信息

select * from employee where post_comment is null;

group by 分组

select * from 表 group by post

会把在group by 后面的这个字段,也就是post字段的每一个不同得项都保留下来,并且把值是这一项的所有行归为一组(只会显示这个组中的第一项)

聚合

将很多行的同一个字段进行一些统计,最终得到一个结果

count (字段) 统计这个字段有多少项

select count(*) from employee;
sum(字段) 统计这个字段对应的数字的和

select sum(salary) from employee;
avg(字段) 统计这个字段对应值的平均值

select avg(salary) from employee
min(字段)
max(字段)

分组聚合

求各部门的人数

select count(*) from employee group by post;

书写错误的修改

求公司里男生和女生人数

select sex,count(id) from employee group by sex;

# 求各部门的平均薪资
	select post,avg(salary) from employee group by post;
# 求各部门的平均年龄
	select post,avg(age) from employee group by post;
# 求各部门年龄最小的
	select post,min(age) from employee group by post;
# 求各部门年龄最大的
	select post,max(age) from employee group by post;
# 求各部门薪资总和
    select post,sum(age) from employee group by post;
# 求各部门薪资最高的
	select name,max(salary) from employee group by post;
# 求各部门薪资最低的
	select name,min(salary) from employee group by post;
#求整个公司薪资最高的
	select name,max(salary) from employee ;
# 求最晚入职的
	select max(hire_date) from employee;
# 求最早入职的
	select min(hire_date) from employee;
# 求各部门最晚入职的
	select post,max(hire_date) from employee group by post;
# 求各部门最早入职的
	select post,min(hire_date) from employee group by post;
#求男生和女生最早入职的
	select sex,min(hire_date) from employee group by sex;

注 :

1.求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到,但是要得到对应的人,就必须通过多表查询

2.总是根据会重复的项来进行分组

3.分组总是会和聚合函数一起用(最大,最小,平均值)

having 条件

过滤组

#1.执行顺序 :总是先执行where,再执行group分组,所以相关先分组,之后再根据分组做某些条件筛选的时候where都用不上
#2.只能用having来实现,having中可用聚合函数
#3.having后面的条件要么是select 字段,要么是分组字段

#部门人数大于3的部门
	select post from employee group by post having count(*) > 3;
#平均薪资大于10000的部门
	select post from employee group by post having avg(salary) > 10000;
#   
    select * from employee having age>18

order by 排序

#根据薪资排序
select * from employee order by salary desc;

order by 某一个字段 asc; 默认是升序asc 从小到大
order by 某一个字段 desc; 指定降序排列desc 从大到小
order by 第一个字段 asc,第二个字段 desc; 指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列

limit

#取前n个  limit n   ==  limit 0,n
    考试成绩的前三名
    入职时间最晚的前三个
#分页    limit m,n   从m+1开始取n个
员工展示的网页
    18个员工
    每一页展示5个员工
#limit n offset m == limit m,n  从m+1开始取n个

顺序

10.4.8pymysql模块

#增
import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password="123",database='day40')
cur = conn.cursor()   # 数据库操作符 游标
cur.execute('insert into employee(emp_name,sex,age,hire_date) values ("郭丰","male",40,20190808)')
conn.commit()
conn.close()

#删
import pymysql

conn = pymysql.connect(host='127.0.0.1',user='root',passward='123',database='day40')
cur = conn.cursor()
cur.execute('delete from employee where id =18')
conn.commit()
conn.close()

import pymysql

conn = pymysql.connect(host='127.0.0.1',user='root',passward='123',database='day40')
cur = conn.cursor()  #元组型
#cur = conn.cursor(pymysql.cursors.DictCursor)   #加完之后为字典型
cur.execute('select * from employee where id >10')
#ret = cur.fetchone()
#print(ret)

#ret = cur.fetchmany(5)  #可添加条数
#print(ret)

ret = cur.fetchall()
print(ret)

conn.commit()
conn.close()

#db.rollback()       # 如果发生错误则回滚

sql注入风险

10.4.9 多表查询

连表查询

把两张表连在一起查

#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+

两张表是如何连在一起的?
内连接 inner join

两张表条件不匹配的项不会出现在结果中

select * from emp inner join department on emp.dep_id = department.id;

外连接

左外链接 left join

永远显示全量的左表中的数据

select * from emp left join department on emp.dep_id = department.id;
右外链接 right join

永远显示全量的右表中的数据

全外连接

select * from emp left join department on emp.dep_id = department.id

union

select * from emp left join department on emp.dep_id = department.id;

# 连接的语法
# select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;
    # 常用
    # 内链接
    # 左外链接

# 找技术部门的所有人的姓名
    # select * from emp inner join department on emp.dep_id = department.id;
# +----+-----------+--------+------+--------+------+--------------+
# | id | name      | sex    | age  | dep_id | id   | name         |
# +----+-----------+--------+------+--------+------+--------------+
# |  1 | egon      | male   |   18 |    200 |  200 | 技术         |
# |  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
# |  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
# |  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
# |  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
# +----+-----------+--------+------+--------+------+--------------+

select * from emp inner join department on emp.dep_id = department.id where department.name = '技术'
select emp.name from emp inner join department d on emp.dep_id = d.id where d.name = '技术'

# 找出年龄大于25岁的员工以及员工所在的部门名称
select emp.name,d.name from emp inner join department as d on emp.dep_id = d.id where age>25;

# 根据age的升序顺序来连表查询emp和department
select * from emp inner join department as d on emp.dep_id = d.id order by age;

# 优先使用连表查询，因为连表查询的效率高


# 练习
# 查询平均年龄在25岁以上的部门名
# 部门名 department表
select name from department where id in (
select dep_id from emp group by dep_id having avg(age)>25
);
# 员工表
select dep_id,avg(age) from emp group by dep_id;
select dep_id from emp group by dep_id having avg(age)>25;

# 查看不足1人的部门名(子查询得到的是有人的部门id)
# 查emp表中有哪些部门id
select dep_id from emp group by dep_id;
# 再看department表中
select * from department where id not in (???)
select * from department where id not in (select dep_id from emp group by dep_id);

# 查询大于所有人平均年龄的员工名与年龄
select * from emp where age>(select avg(age) from emp);

# 查询大于部门内平均年龄的员工名、年龄
select dep_id,avg(age) from emp group by dep_id;
select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d
on emp.dep_id = d.dep_id where emp.age > d.avg_age;

子查询

10.5 索引原理

10.5.1 基本概念

#什么是索引?
一个在存储表阶段就有的一个存储结构,能够在查询时加速

#索引的重要性
读写比例 10:1 ,读(查询)的速度至关重要

block 磁盘预读原理

读文件时的操作 :for line in f
linux操作系统中一次性会读取4096个字节(一个block快)

读硬盘的io操作的时间非常的长,比cpu执行指令的时间长很多,所以尽量的减少IO次数才是读写数据的主要解决的问题

聚集索引和辅助索引

innodb 聚集索引和辅助索引共存

聚集索引 ------主键

数据存储在树结构的叶子结点
辅助索引除了主键外的所有索引

数据不直接存储在树中

mysam 只有辅助索引无聚集索引

辅助索引

数据不直接存储在树中

10.5.2数据的存储方式

新的数据结构----------树

平衡树 balance tree(b树)

在b树的基础上进行了改良(b+树)

1.分直接点和根节点都不在存储实际数据
- 而是将所有的实际数据都存在于叶子节点中,导致分支和根节点能存储更多的索引信息,降低了树的高度
2.在叶子节点之间加入双向的链式结构
- 方便在查询中的范围条件

#注意:
#mysql当中的所有b+树索引的高度都基本控制在3层
   1.io操作的次数非常稳定
   2.有利于通过范围查询

#什么会影响索引效率     ------树的高度
  1.对哪一列创建索引,尽量选择短的列做索引
  2.对区分度高的列建索引,重复率超过了10%就不适合创建索引

10.5.3创建索引

#创建索引
create index 索引名字 on 表(字段);
#删除索引
drop index 索引名 on 表名字;

索引的种类

primary key 主键聚集索引 约束的作用 :非空 + 唯一

联合主键
unique 自带索引 ,辅助索引 ,约束的作用 :唯一

联合唯一
index 辅助索引 ,无约束作用

联合索引

10.6使用索引

没给id创建索引前,查找效率低

给id创建索引后,查找效率高

以email 作为条件时

不加索引,速度慢

查询的字段不是索引字段,也慢

查找第2999999条数据

查找第1条数据
加了索引,速度快

id 作为条件时

如果不加索引,速度慢
加了索引,速度快.

10.6.1索引不生效的原因

# 单列索引
    # 选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符
# 使用or来连接多个条件
    # 在满上上述条件的基础上
    # 对or相关的所有列分别创建索引

原因一:索引列不能再条件中参与计算
原因二: 要查询的数值范围大(范围越小,速度越快io操作少)
- !=,>,<,>=,<=
- between and
- like
  - 结果的范围大,索引不生效
  - 如果 abc% 索引生效, %abc索引就不生效
原因三:一列内容的区分度不高(使用name列测试)
原因四 :对两列内容进行条件查询

先去掉id列索引
- and and条件两端的内容,优先选一个有索引的,并且树形结构更好的来进行查询
  
  select * from s1 where id = 1000000 and email = 'eva1000000@oldboy';
  
  只有两个条件都成立才能完成where条件,先生成范围小的,缩小后面条件的压力
- or or条件的,不会进行优化,只是根据条件从错到有依次
  
  select * from s1 where id = 1000000 or email = 'eva1000000@oldboy';
  
  条件中带有or的要想命中索引列,这些条件中所有的列都是索引列
原因五: 联合索引
- 在联合索引中如果使用了or条件,索引不生效
- 最左前缀原则 :在联合索引中,条件必须含有在创建索引时的第一个索引列
- 在整个条件中,从开始出现模糊匹配的那一刻,索引就失效了
为避免其他索引干扰,删掉其他索引

创建联合索引 create index ind_mix on s1(id,email);

#合并索引
  #对两个字段分别创建索引,由于sql

#执行计划 :explain
  #如果想在执行sql之前就知道sql语句的执行情况,可以使用执行计划
  #语法  explain select id from s1 where id = 1000000;
  #情况一:
    30000000条数据,假设查一条数据要20秒,不想用这么长时间
    使用explain sql --->并不会真正执行sql而是会给你列出一个执行计划
 #情况二:
	有20条数据,但将来可能有2000000条数据
    使用explain sql

10.6.2合并索引和覆盖索引

覆盖索引

如果我们在使用索引作为条件查询,查询完毕后,不需要回表查,覆盖索引
```
explain select id from s1 where id = 1000000;
 explain select count(id) from s1 where id > 1000000;
```
合并索引

对两个字段分别创建索引,由于sql的条件让两个索引同时生效,这两个索引就是合并索引

10.7数据备份和事务

10.7.1数据备份

退出mysql后执行

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql   #备份所有表
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql   #备份指定表

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

重命名

10.7.2数据恢复

#方法一：
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

#方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   #关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql

10.2.3事务

# begin;  # 开启事务
# select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
# update emp set salary=10000 where id = 1; # 完成更新
# commit; # 提交事务

10.8sql注入

# create table userinfo(
# id int primary key auto_increment,
# name char(12) unique not null,
# password char(18) not null
# )
#
# insert into userinfo(name,password) values('alex','alex3714')

# 输入用户
# 输入密码
#
# 用户名和密码到数据库里查询数据
# 如果能查到数据 说明用户名和密码正确
# 如果查不到，说明用户名和密码不对
# username = input('user >>>')
# password = input('passwd >>>')
# sql = "select * from userinfo where name = '%s' and password = '%s'"%(username,password)
# print(sql)


# -- 注释掉--之后的sql语句
# select * from userinfo where name = 'alex' ;-- and password = '792164987034';
# select * from userinfo where name = 219879 or 1=1 ;-- and password = 792164987034;
# select * from userinfo where name = '219879' or 1=1 ;-- and password = '792164987034';

import pymysql

conn = pymysql.connect(host = '127.0.0.1',user = 'root',
                       password = '123',database='day41')
cur = conn.cursor()
username = input('user >>>')
password = input('passwd >>>')
sql = "select * from userinfo where name = %s and password = %s"
cur.execute(sql,(username,password))
print(cur.fetchone())
cur.close()
conn.close()

10.9慢日志

posted @ 2019-08-16 10:07 Primrose 阅读(279) 评论(0) 编辑收藏举报

刷新页面返回顶部

Primrose

第十章 数据库

第十章 数据库

10.1 数据库

10.1.1数据库的引入

10.1.2数据库管理系统(DBMS)

10.2初识mysql

10.2.1 mysql的安装

10.2.2 mysql的cs架构

10.2.3mysql的用户和权限

10.2.4库,表.数据

10.2.3.1库操作

10.2.3.2表操作

10.2.3.3操作表中的数据

10.3mysql中的存储引擎

10.4表结构

10.4.1表的存储方式

10.4.2创建表

表介绍

建表语句

10.4.3MySQL中的数据类型

10.4.3.1数值类型

10.4.3.2日期和时间

10.4.3.3字符串类型

10.4.3.4ENUM和SET类型

10.4.4约束

10.4.5修改表结构

10.4.6 表与表之间关系

10.4.7数据的操作

where语句

group by 分组

聚合

分组聚合

having 条件

order by 排序

limit

10.4.8pymysql模块

10.4.9 多表查询

连表查询

子查询

10.5 索引原理

10.5.1 基本概念

block 磁盘预读原理

聚集索引和辅助索引

10.5.2数据的存储方式

平衡树 balance tree(b树)

在b树的基础上进行了改良(b+树)

10.5.3创建索引

10.6使用索引

10.6.1索引不生效的原因

10.6.2合并索引和覆盖索引

10.7数据备份和事务

10.7.1数据备份

10.7.2数据恢复

10.2.3事务

10.8sql注入

10.9慢日志

公告

第十章数据库

第十章数据库