数据库

第十章 数据库

10.1初识数据库

10.1.1 数据库基础知识

数据库在程序中扮演的角色:存储数据

  • 用户名密码,商品价格,对数据的处理更便捷
  • web程序
名词 解释
数据data 描述事物的符号记录称为数据
数据库(db)database 存放数据的仓库
表table 即文件,用来存放多行内容/多条记录
数据库管理系统DBMS 组织,存储数据,高效获取和维护数据的系统软件
数据库管理员DBA 管理数据库的人
数据库服务器 一台计算机,运行数据库管理软件

数据库分类:

  • 关系型数据库:

    速度相对慢,数据之间关联紧密

    MySQL Oracle(安全性非常高) sqlserver sqlite

  • 非关系型数据库:

    存储数据快,数据之间关联较弱

    Redis MongoDB memcache(内存级别)

数据库优势:

1. 程序稳定性 ：这样任意一台服务所在的机器崩溃了都不会影响数据和另外的服务。
2. 数据一致性 ：所有的数据都存储在一起，所有的程序操作的数据都是统一的，就不会出现数据不一致的现象
3. 并发 ：数据库可以良好的支持并发，所有的程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不需要我们自己写socket
4. 效率 ：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多

SQL : 结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统

SQL语言主要用于存取数据,查询数据,更新数据和管理关系数据库系统.SQL语言分为3种类型:

  1. DDL语句 -- 数据库定义语言:数据库,表,视图,索引,存储过程.例如 create drop alter
  2. DML语句 -- 数据库操纵语言:插入数据insert,删除数据delete,更新数据update,查询数据select
  3. DCL语句 -- 数据库控制语言:例如控制用户的访问权限grant,revoke

10.1.2 数据库安装和卸载

  1. 安装

修改配置文件

  • 编码:UTF-8
  • 所有的配置项后面不能有特殊符号
  • 修改两个路径basedir datadir
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=C:Program Filesmysql-5.6.39-winx64 
# 设置mysql数据库的数据的存放目录
datadir=C:Program Filesmysql-5.6.39-winx64data 
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

以管理员身份打开CMD

  • 命令: mysqld install 安装mysql服务

启动mysql服务端

  • 命令: net start mysql

停止mysql

  • 命令: net stop mysql

启动mysql客户端,自动的连接本地的3306端口

  • 命令: mysql -u root 表示用root用户登录
  • 默认密码为空,set password = password('123') 设置密码
  • 密码登录: mysql -u root -p

创建账号:

​ 使用公司数据库,管理员会创建一个账号给你用 / 自己的数据库

​ 语法: create user '用户名'@'IP地址' identifield by '密码';

​ grant 权限种类(增删改查)

​ grant all -- 所有权限

​ grant select on 库名.* to '创建的用户'; -- 给一个已经存在的账号授权

​ grant select on 库名.* to '创建的用户' identifield by '密码'; -- 边创建边授权

​ select user() -- 查看当前账号

  1. 卸载

删除mysql数据库

  • net stop mysql 停止mysql服务
  • mysqld remove 删除mysql
  • 删除安装软件
  • 删除环境变量
  • 删除注册表/重启计算机

10.1.3 数据库的操作

# 操作文件夹(库)
创建库                create database 库名;
切换到创建的库的下面 	 use 库名;
查看所有库            show databases;
查看当前库            select database();
删除库          	   drop database 库名;

# 操作文件(表)
创建表            		 create table 表名(id int,name char(字符长度)...);
查看当前库中有所有的表    show tables;
删除表              	 drop table 表名;
查看表的结构         	   desc(describe) 表名;
查看建表语句             show create table 表名;
    
修改表名           	    alter table 表名 rename 新名字;

增加字段                alter table 表名 add 字段名 数据类型 完整性约束条件;
                       alter table 表名 add 字段名 数据类型 完整性约束条件 first;
    				  alter table 表名 add 字段名 数据类型 完整性约束条件 after 字段名;
        
修改表字段              alter table 表名 modify 字段名 数据类型 完整性约束条件;
修改表字段名称           alter table 表名 change 旧名字 新名字 完整性约束条件;
					  alter table 表名 change 旧名字 新名字 新数据类型 完整性约束条件;
删除字段                alter table 表名 drop 字段名

# 了解
删除主键                alter table 表名 drop primary key;
增加主键                alter table 表名 add primary key(id);
为表添加外键             alter table book add constraint fk_id foreign key(press_id) references press(id);
删除外键                alter table book drop foreign key fk_id;

# 操作文件中的内容
查看 命令: select * from 表名
增加 命令: insert into 表名 values(1,'Agoni'),(2,'xiaobai');
    	  insert into 表名(字段1,字段2) values(值1,值2);
    	  insert into 表名(字段1,字段2) select (字段1,字段2) from 库名.表2;
修改 命令: update user set name = 'xxx'; # 所有的名字都会被修改
	      update user set name = 'xxx' where id = 1; # 只修改id为1的名字
	      update user set name = 'xxx',id = 2 where id = 1; # 将id为1的名字修改为xxx,id改为2
	      update user set name = 'xxx',id = 2 where id = 1;
删除 命令: delete from 表名;  # 清空表
    	  delete from 表名 where 条件; # 删除表中的某个数据(常用)

10.2 存储引擎

存储引擎 -- 数据的存储方式(engines)

查看当前存储引擎 -- show engines

常用存储引擎:

10.2.1 innodb

​ 应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的

  • mysql 5.6以上的默认存储方式,存两个文件

  • row-level locking 行级锁

  • table-level locking 表级锁

  • foreign keys 外键

  • transaction 事物(要不同时成功,不要同时失败) 保证数据安全,数据的完整性而设置的概念

  • 树tree - 加速查询(树形结构(数据+树) + 表结构)

    1564581659983

10.2.2 myisam

​ 应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

  • mysql 5.5 以下默认存储方式.存3个文件

  • table_level locking 表级锁

  • 树tree - 加速查询(树形结构 + 数据 + 表结构)

    1564581677568

10.2.3 memory

​ 所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

  • 存1个文件
  • 基于hash
  • 所有的数据保存在内存中

10.3 数据类型

10.3.1 数值类型

整数类型

TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：存储年龄，等级，id，各种号码等

========================================
        tinyint[(m)][unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：-128 ～ 127
            无符号：0 ～ 255

            PS： MySQL中无布尔值，使用tinyint(1)构造。
========================================
        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：
            有符号：-2147483648 ～ 2147483647
            无符号：0 ～ 4294967295
========================================
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：-9223372036854775808 ～ 9223372036854775807
            无符号：0 ～ 18446744073709551615
浮点型

定点数类型: DEC等同于DECIMAL　　

浮点类型：FLOAT DOUBLE

作用：存储薪资、身高、体重、体质参数等

======================================
# FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]  小数点后5位,在精确位四舍五入,必须设置 M D

定义：单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号：-3.402823466E+38 to -1.175494351E-38,
        1.175494351E-38 to 3.402823466E+38
    
无符号： 1.175494351E-38 to 3.402823466E+38


精确度： **** 随着小数的增多，精度变得不准确 ****
======================================
# DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]  在精确位四舍五入

定义：双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号： -1.7976931348623157E+308 to -2.2250738585072014E-308
        2.2250738585072014E-308 to 1.7976931348623157E+308

无符号： 2.2250738585072014E-308 to 1.7976931348623157E+308
            
精确度：****随着小数的增多，精度比float要高，但也会变得不准确 ****

======================================
# decimal[(m[,d])] [unsigned] [zerofill]  默认 (10,0)

定义: 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

精确度： **** 随着小数的增多，精度始终准确 ****
        对于精确数值计算时需要用此类型
        decaimal能够存储精确值的原因在于其内部按照字符串存储。

10.3.2 日期类型

DATE TIME DATETIME TIMESTAMP YEAR

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

YEAR
    YYYY（ 1901/2155）

DATE
    YYYY-MM-DD（ 1000-01-01/9999-12-31）

TIME
    HH:MM:SS（'-838:59:59'/'838:59:59'）

DATETIME # 表示范围更大,还能拥有timestamp的特点
    YYYY-MM-DD HH:MM:SS（ 1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP  # 不能为空,默认值是当前时间,在修改的时候同时跟新时间
    YYYYMMDD HHMMSS（ 1970-01-01 00:00:00/2037 年某时）

10.3.3 字符串类型

# char类型：定长，简单粗暴，浪费空间，存取速度快
不写长度默认为1
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
    存储：
        存储char类型的值时，会往右填充空格来满足长度
        例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

# varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：
    存储：
        varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来
        
# char填充空格来满足固定长度，但是在查询时却会删除尾部的空格

10.3.4 枚举和集合类型

# enum 单选	
只能在给定的范围内选一个值, 如性别 sex 男male / 女female

# set 多选
在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3...)

10.4 完整性约束

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

主要分为：

  • unsigned 无符号的:只能是数字
  • not null 非空约束
  • default 默认值
  • unique 唯一约束
  • auto_increment 自动增加
  • primary key 主键约束
  • foreign key 外键约束

10.4.1 not null与default

# not null  非空
mysql> create table t1(id int); # id字段默认可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); # 可以插入空

mysql> create table t2(id int not null); # 设置字段id不为空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); # 不能插入空
ERROR 1364 (HY000): Field 'id' does not have a default value  # 报错

# default 默认值
设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值  # (设置严格模式后不能插入空)

mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;

10.4.2 unique

对于unique来说可以插入多个null值

  • 对于mysql来说,数据与数据之间相等就是重复
  • null不能用等号来判断
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
unique(name)
);

mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'  # 报错

# not null 和 unique的结合  
mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
# 如果一张表中没有设置primary key, 第一个设置 非空+唯一 的字段会被设置成主键,一张表中只能有一个主键.

# 设置联合唯一
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'

10.4.3 auto_increment

auto_increment 自增

  • 只能操作数字
  • 自带非空属性
  • 只能对unique字段进行设置
# 不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


# 也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


# 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

# 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
row in set (0.00 sec)

10.4.4 primary key

从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。

一个表中可以：

  • 单列做主键
# 在某一个字段后用primary key
create table department2(
id int primary key, # 主键:一般主键后面要跟上auto_increment自增
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)
  • 多列做主键（复合主键,联合主键）
==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)  # 联合主键
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

10.4.5 foreign key

外键约束 : 对应外表中的字段至少是unique的,推荐使用主键作为关联字段

create table class(
  id int primary ,
  cname char(12) not null unique,
  start_date date,
  period char(12),
  course char(12),
  teacher char(12)
);

create table student(
  id int primary key auto_increment,
  name char(12) not null,
  gender enum('male','female'),
  cid int,
  foreign key(cid) references class(id) on delete cascade on update cascade
);

# on delete cascade on update cascade 级连删除,级连更新

数据的插入操作 -- 把另一个表中的数据插入到当前表中
# insert into class select * from class;
# insert into class(id,name) select id,cname from class;

10.5 单表查询

10.5.1 单表查询的语法

select 字段1,字段2... from 表名;
					where 条件
					group by 字段  # 分组
					having 筛选
					order by 字段  # 排序
					limint 限制条数

10.5.2 关键字的执行优先级

重点中的重点：关键字的执行优先级
1.from      找到表
2.where     拿着where指定的约束条件，去文件/表中取出一条条记录
3.group by  将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.having    将分组的结果进行having过滤
5.select    执行select
6.distinct  去重
7.order by  将结果按条件排序
8.limit     限制结果的显示条数

10.5.3 简单查询

# 简单查询
select * form 表名;  # 查看表中所有信息
select 字段1,字段2 from 表名;  # 查看表中指定字段信息

# 可以调用函数
select now() / user() / database() / concat() / concat_ws()

# distinct去重复
select distinct 字段 from 表名; 

# 可以参与四则运算
select name,salary*12 from 表名; 
select name,salary*12 as 新字段名 from 表名;
select name,salary*12 新字段名 from 表名;

# 定义显示格式
CONCAT() # 函数用于连接字符串
select concat(字段,'字符串2',字段) from 表
select concat(emp_name,' : ',salary) as info from employee;
select concat(emp_name,' : ',salary) info from employee;

CONCAT_WS() # 第一个参数为分隔符
select concat_ws('分隔符',字符串,字段1,字段2) info from employee;
select concat_ws('|','信息',emp_name,salary) info from employee;

# 结合case语句
select
	(
        case
        when name = 'Agoni' then
        	name
        when name = '小白' then
        	concat(name,'_123')
        else
        	concat(name,'666')
        end
    ) as new_name
from
	表名;

10.5.4 where约束

where字句中可以使用：

  1. 比较运算符：> < >= <= <> !=
  2. between 10 and 20 值在10到20之间
  3. in(10,20,30) 值是10或20或30
  4. like 'egon%'
        pattern可以是%或_，
        %表示任意多字符
        _表示一个字符
  5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
  6. where 后面不能加内聚函数
# 单条件查询
select 字段 from 表名 where 条件;

# 多条件查询
select 字段1,字段2 from 表名 where 条件1 and 条件2;

# 关键字 between...and...
select 字段1,字段2 from 表名 where between 数值1 and 数值2;

# 关键字 in 集合查询
select 字段1,字段2 from 表名 where 字段 in (值1,值2,值3,...); # n选1

# 关键字 like 模糊查询
通配符 % 
select * from 表名 where 字段 like 'ab%'; # 该字段以ab开头的所有信息,%可以是任意长度字符
通配符 _
select * from 表名 where 字段 like '_x'; # 该字段以x结尾,且x前面只有一个字符的所有信息

# 关键字 is null(判断某个字段是够为null不能用等号,需要用is)
select 字段1,字段2 from 表名 where 字段 is not null;
select name,post_comment from 表名 where post_comment='' 
# 这里的 '' 是空字符串,不是null
 ps：
   执行
   update employee set post_comment='' where id=2;
   再用上条查看，就会有结果了

10.5.5 group by 分组

select * from 表名 group by 字段名  # 按照字段名分组(只显示每组中的第一条信息)
# 按照字段分组，那么select查询的字段只能是分组的字段，想要获取组内的其他相关信息，需要借助函数
- 根据某个重复率比较高的字段进行的,这个字段有多少中组就分成多少个组
- 可以去重
- 一旦分组就不能对具体某一条数据进行操作,都是考虑这组数据
- group_concat(字段) : 显示每个组的某个字段的所有信息,只用来做最终的显示,不能作为中间结果操作其他数据

10.5.6 聚合函数

  • 一般都要和分组情况一起用,如果没有和分组一起用,默认一整张表是一组
  • count 计数,每个组对应几条数据
  • max 最大值
  • min 最小值
  • avg 平均值
  • sum 求和

10.5.7 having 过滤条件

  • 对组进行筛选的条件
  • having问题 筛选条件要出现在前面

10.5.8 order by 排序

  • 默认是升序
  • order by 字段 asc -- 升序
  • order by 字段 desc -- 降序

10.5.9 limit 区间取值

  • 显示分页 -- limit m,n 从m+1 取 n个
  • 取前n名 -- limit n (m默认为0)
  • limit n offset m 同 limit m,n
  • limit 总是和order by 一起使用
  • 如果数据量非常大,取值效率非常低(例如 limit 10000,10 从10001开始取10条,前10000也要读取,耗费效率)

10.6 Python操作数据库

# 查询

import pymysql
conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
                 database='day39')
cur = conn.cursor()  # 游标,数据库操作符,游标会记录查询位置,下次查询接着下一个开始查询
sql = 'select * from employee where age = %s'
cur.execute(sql,(80,))
            
ret1 = cur.fetchone() # 默认查询一条
ret2 = cur.fetchmany(2) # 查询两条
ret3 = cur.fetchall() # 查询所有
print(ret1)
print(ret2)
print(ret3)
cur.close()
conn.close()

# insert 写入

import pymysql
conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
                 database='day39')
cur = conn.cursor()
sql = 'insert into employee(id,emp_name,sex,age,hire_date) values(%s,%s,%s,%s,%s)'
cur.executemany(sql,[(20,"大白","male",18,20190528),  # 如果插入多个数据要用executemany
                 (21,"老白","female",20,20190628)])
conn.commit()
cur.close()
conn.close()

# 注释
try:
   cursor.execute(sql) # 执行sql语句
   conn.commit()         # 提交到数据库执行
except:
   conn.rollback()       # 如果发生错误则回滚

10.7 多表查询

# 创建表
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> 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 |
+----+------------+--------+------+--------+

多表连接查询

# 重点:外连接语法
select * from 表1 inner/left/right join 表2 on 表1.字段 = 表2.字段;

10.7.1 交叉连接

交叉连接:不适用任何匹配条件,生成笛卡尔积(了解)

mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  1 | egon       | male   |   18 |    200 |  201 | 人力资源     |
|  1 | egon       | male   |   18 |    200 |  202 | 销售         |
|  1 | egon       | male   |   18 |    200 |  203 | 运营         |
|  2 | alex       | female |   48 |    201 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  2 | alex       | female |   48 |    201 |  202 | 销售         |
|  2 | alex       | female |   48 |    201 |  203 | 运营         |
|  3 | wupeiqi    | male   |   38 |    201 |  200 | 技术         |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  202 | 销售         |
|  3 | wupeiqi    | male   |   38 |    201 |  203 | 运营         |
|  4 | yuanhao    | female |   28 |    202 |  200 | 技术         |
|  4 | yuanhao    | female |   28 |    202 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  4 | yuanhao    | female |   28 |    202 |  203 | 运营         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力资源     |
|  5 | liwenzhou  | male   |   18 |    200 |  202 | 销售         |
|  5 | liwenzhou  | male   |   18 |    200 |  203 | 运营         |
|  6 | jingliyang | female |   18 |    204 |  200 | 技术         |
|  6 | jingliyang | female |   18 |    204 |  201 | 人力资源     |
|  6 | jingliyang | female |   18 |    204 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 |  203 | 运营         |
+----+------------+--------+------+--------+------+--------------+

# 根据给出的条件进行筛选
mysql> select * from employee,department as d where dep_id = d.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 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

10.7.2 内连接

select * from 表1 inner join 表2 on 连接条件

内连接:只显示两张表中相互匹配的项,其他不匹配的不显示

mysql> select * from employee inner join department on 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 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

10.7.3 左外连接

select * from 表1 left join 表2 on 连接条件

优先显示左表全部记录

mysql> select * from employee left join department on dep_id=department.id;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 | NULL | NULL         |
+----+------------+--------+------+--------+------+--------------+

10.7.4 右外连接

select * from 表1 right join 表2 on 连接条件

优先显示右表全部记录

mysql> select * from employee right join department on 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 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+

10.7.5 全外连接

显示左右两个表全部记录

mysql> select * from employee left join department on dep_id=department.id
    -> union
    -> select * from employee right join department on dep_id=department.id;
+------+------------+--------+------+--------+------+-------------+
| id   | name       | sex    | age  | dep_id | id   | name        |
+------+------------+--------+------+--------+------+-------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL        |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+-------------+

10.8 子查询

1. 子查询是将一个查询语句嵌套在另一个查询语句中。
2. 内层查询语句的查询结果，可以为外层查询语句提供查询条件。

10.8.1 带 in 关键字的子查询

如果查询出来的结果是一列中的多个值

==> select * from 表 where 字段 in (值1,值2,...)

# 查询存在年龄大于25岁员工的部门
1.在employee表查询年龄大于25岁的员工dep_id
select dep_id from employee where age>25; # 将此查询作为条件
2.在department表查询dep_id = id 所对应的的部门名字
select * from department where id in (select dep_id from employee where age>25);

10.8.2 带比较运算符的子查询

如果查询出来的结果是一个值

==> select * from 表 where 字段 运算符 值

# 查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name    | age  |
+---------+------+
| alex    | 48   |
| wupeiqi | 38   |
+---------+------+
2 rows in set (0.00 sec)

10.8.3 带 exists 关键字的子查询

exists关字键字表示存在。在使用exists关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False

# department表中存在dept_id=203，Ture,执行select语句
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| 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 |
+----+------------+--------+------+--------+

# department表中存在dept_id=205，False,不执行select语句
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

10.9 索引

什么是索引? -- 索引在mysql中也是一种键,在存储引擎用于快速找到记录的一种数据结构.

10.9.1 索引的数据结构

树 -- 树状图是一种数据结构,它是由那个有限节点构成一个具有层次关系的集合

1564989288855

B+树:

  • 索引字段要尽量小,IO次数取决于B+树的高度.
  • 索引的最左匹配特性.B+树是按照从左到右的顺序来建立搜索树的
  1. 数据值存储在叶子节点
  2. 在叶子节点之间加入了双向地址链接,更方便在叶子节点之间进行数据的读取

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

10.9.2 索引的种类

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

  1. innodb 中的索引

    InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。

    聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。

    辅助索引:除了主键之外所有的索引都是辅助索引

    回表:只查询一个索引并不能解决查询中的问题,还需要到具体的表中获取正确数据

  2. myisam 的索引

    全部都是辅助索引:辅助索引的叶子节点不包含行记录的全部数据

MySQL常用的索引

  1. primary key 的创建自带索引效果 非空 + 唯一 + 聚集索引
  2. unique 唯一约束的创建也自带索引效果 唯一 + 辅助索引
  3. index 普通的索引 辅助索引

创建索引: create index 索引名字 on 表名(字段名);

删除索引: drop index 索引名字 on 表名;

索引的优缺点:

  • 优点:查找速度快
  • 缺点:浪费空间,拖慢写的速度

创建索引发生的两个变化:

  • 创建索引之后的查询效率大幅度提高
  • 文件所占的硬盘资源也大幅度提高

10.9.3 正确使用索引

  1. 所查询的列不是创建了索引的列

  2. 在条件中不能带运算或者函数,必须是 " 字段=值 "

  3. 如果创建索引的列的内容重复率高,也不能有效的利用索引,不能提高查询效率 ( 重复率不超过10%的列比较适合做索引)

  4. 数据对应的范围太大,也不能有效的利用索引

    between...and... 运算符

  5. like 如果把%放在最前面也不能命中索引

  6. 多条件的情况

    and 只要有一个条件

    是索引列就可以命中索引

    or 只有索引的条件列都是索引列才能命中

  7. 联合索引

    在多个条件相连的情况下,使用联合索引的效率要高于使用单字段的索引

    where a=xx and b=xx;

    a和b分别创建了索引,正常情况下只能命中一个

    a,b都创建索引 -- 联合索引

    创建联合索引: create index 索引名 on 表(字段名1,字段名2)

    规则:

    • 创建索引的顺序 字段1,字段2, 条件中从哪一个字段出现了范围,后面的索引就失效.
    • 联合索引在使用的时候遵循 " 最左前缀原则 "
    • 联合索引中只有使用and能生效,使用or失效

了解

mysql神器 -- explain

  • 查看SQL语句的执行计划
  • 是否命中索引,命中索引的类型

覆盖索引 -- using index , 查询数据不需要回表就是索引

索引合并 -- 创建的时候是分开创建的,用的时候临时和在一起了,using union 表示索引合并

# 数据表库的导入导出

# 备份表 :homwork库中的所有表和数据
    mysqldump -uroot -p123 homework > D:s23day42a.sql
# 备份单表
    mysqldump -uroot -p123 homework course > D:s23day42a.sql
# 备份库 :
   	mysqldump -uroot -p123 --databases homework > D:s23day42db.sql
# 恢复数据:
    进入mysql 切换到要恢复数据的库下面
    sourse D:s23day42a.sql

# 开启事务,给数据加锁
    begin;
    select id from t1 where name = 'alex' for update;
    update t1 set id = 2 where name = 'alex';
    commit;

mysql可以开启慢日志,慢日志是通过配置文件开启

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Agoni-7/p/11305681.html