数据库入门

知识点

△用数据库的原因

1文件操作的复杂度
2同步
3并发处理
4安全

△数据库系统（DBS）
数据库(DB) + 数据库管理系统 (DBS)+ 数据库应用程序 + 数据库管理员 (BDA)+ 最终用户

△数据库管理系统-DBM
网络应用服务端
我们要使用服务端的数据 - 需要有一个客户端
客户端可以自己写 : 未来写代码的时候
也可以用别人写好的 : 第三方的工具 数据库管理软件的公司出版的官方客户端
数据库管理系统本质上也是管理一堆文件
只不过人家的管理方式比我们更高效 更安全

△数据库管理员-DBA

搭建数据库服务环境
用户的创建 权限的管理
性能\语句的优化
数据库的二次开发 : 让数据库具有公司的特质

△软件

mysql : 小公司
甲骨文 oracle : 事业单位 金融企业
微软 sql server
sqllite

△数据库的分类

关系型数据库 mysql oracle sqlserver sqllite
非关系型数据库 redis mongodb memcache hbase

关系型数据库

优点:

1、易于维护：都是使用表结构，格式一致
2、使用方便：SQL语言通用，可用于复杂查询
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询
缺点:

1、读写性能比较差，尤其是海量数据的高效率读写
2、固定的表结构，灵活度稍欠
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈

非关系型数据库

优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等
2、速度快，成本低：nosql数据库部署简单，基本都是开源软件

缺点：
1、不提供sql支持，学习和使用成本较高
2、无事务处理
3、数据结构相对复杂，复杂查询方面稍欠

△ SQL是关系数据库管理系统的标准语言。

△名词

DB 数据库 - 文件夹
table 表 - 文件
data 一条数据-每一行数据

△三种方法操作数据库

终端，python，第三方工具

△二.mysql编码问题

编码问题
1.临时解决问题在客户端执行set XXXX = utf8;
2.永久解决问题fimy. ini添加set xXxx = utf8;
3.实时解决问题create table表名() charset=utf8;

△服务端-安装启动关闭

mysqld install    #安装
net start mysql   #启动  以管理员身份在cmd中输入
net stop mysql    #关闭

creat user 'eva'@'%'identified by'123';
#创建用户eva 在所有网段允许 设置密码为123

△

1,先跟数据库进行连接(用账号和密码,默认3306端口)

2,创建数据库:

​ create database 数据库名:
​ 删除数据库（慎用！）：
​ DROP DATABASE 数据库名称；
​ 显示所有数据库：
​ SHOW DATABASES;
​ 切换数据库：
​ USE 数据库名称 ;

3.创建表:

4.查看表结构

础数据类型

数字类型

整数 :

tinyint(m) 1个字节 -128~127

smalint(m) 1个字节 -32768~32767

mediumint 3个字节 -8388608~8388608

int(m) 4个字节 -2147483648~2147483647

bigint(m) 8个字节 -9223372036854775808~9223372036854775807

m: 显示宽度,和数据类型的取值范围是无关的

(int unsigned) : unsigned 标上表示无符号

小数 :

MySQL中使用浮点数和定点数来表示小数。它们都可以用(m,n)来表示，其中m称为精度，表示总共的位数；n称为标度，是表示小数的位数（会四舍五入）

float(m,d) 单精度浮点型,4字节 -3.402823466E+38~-1.175494351E-38

double(m,d) 双精度浮点型,8字节 -1.7976931348623157E+308~-2.225738585072014E-308

decimal(m,d) 压缩严格的定点数,m+2个字节 最大取值范围与double一致

时间类型

year 年 酒的产期

time 时:分:秒 计时软件

date 年-月-日 入职日期 出账日期 还款日期

datetime 年-月-日 时:分:秒 1000-01-01 00:00:00~9999-12-31 23:59:59 日志,消费记录,上下班打卡

timestamp 年-月-日 时:分:秒 不能为空，自动写当前时间，级联更新，范围小

now() 函数表示当前时间

默认当前时间并级联更新

让datetime 自动写当前时间，级联更新
create table 表(
列 datetime NOT NULL         #非空
DEFAULT CURRENT_TIMESTAMP    #自动写当前时间
ON UPDATE CURRENT_TIMESTAMP  #级联更新当前时间
)

字符串类型

char(255) ：定长的 节省时间 浪费空间 手机号码、身份证号

varchar(65535) ： 变长的 节省空间 浪费时间 评论

枚举和集合

enum 单选

set 多选，自动去重

例如建表时定义字段
爱好 set（“游泳”，“篮球”，“下棋”，“音乐”，“旅游”）
insert into t_1 values("游泳，篮球，音乐")
添加时会自动去重和去掉不存在的

举例-数字 字符串

create table t1(i1 tinyint,i2 int); #默认创建的数据类型都是有符号的  i1-表头
create table t2(i1 tinyint unsigned,i2 int unsigned); #给字段添加一个unsigned表示无符号
create table t3(f1 float,f2 double);

mysql> create table t1(
    id int,name varchar(20),
    sex enum('man','wuman'),
    age int,
    hire_date date,
    post varchar(20),
    post_comment varchar(20), #一般在用char varchar时要限制字符
    salary double(7,2),
    office int,
    depart_id int); 32

举例-小数

create table t3(f1 float,f2 double);
create table t4(f1 float(7,2));  小数点后2位,小数点前5位

举例-时间

create table t7(dt datetime NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ TIMESTAMP,y year);  
  NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ TIMESTAMP   #让datetime的时间在无数据填充的情况下会自动更新到此次修该的时间

举例-枚举和集

create table t8(username cha(12),gender enum('male','female'));
create table t8(username cha(12),hobby set('喝酒','抽烟','烫头','洗脚'));
填充的时候也必须时字符串格式,添加时会自动去重和去掉不存在的

数据操作之前

在维护和操作时，一定要先备份一份，之后再去操作数据库

会遭遇各的数据丢失的情况:
硬件故障
软件故障
自然灾害
黑客攻击
误操作 (占比最大)

备份

1，在MySQL的bin目录中打开命令窗口（bin目录中才有复制的命令）
2，输入：mysqldump –uroot –p test101 > C :\t1.sql （如果有警告可以忽略）

恢复

连接MySQL，创建数据库
在MySQL的bin目录中打开命令窗口
输入：mysql –uroot –p 数据库名 < d:\t1.sql

管理员账户设置密码

# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆

# mysql -uroot -p -h                mysql5.6默认是没有密码的  遇到password直接按回车键
# mysql> set password = password('root');   给当前数据库设置密码
# mysql> select user();  #查看当前用户
# 输入“ipconfig”即可查看到本机的ip信息。
# ；表示sql语句的结束
# \c放弃当前要执行的sql语句
# mysql> exit     # 也可以用\q quit退出

创建用户账号并授权

mysql> grant all on *.* to 'eva'@'%' identified by '123'

5.6及以下版本
mysql> grant all on *.* to 'eva'@'localhost' identified by '123'

△分两步:

创建用户

mysql> create user 'eva'@'192.168.10.%'   IDENTIFIED BY '123';# 指示网段
mysql> create user 'eva'@'192.168.10.5'   # 指示某机器可以连接
mysql> create user 'eva'@'%'                    #指示所有机器都可以连接  
mysql> show grants for 'eva'@'192.168.10.5';查看某个用户的权限 

给账号授权

mysql> grant all on *.* to 'eva'@'%';
#grant 权限类型 on 数据库名称 . *  (或表名)  to 'shang'@'%';

mysql> flush privileges;    # 刷新使授权立即生效

用户端-操作

操作库（文件夹）

数据备份: mysqldump –uroot –p test101 > C :\t1.sql   （如果有警告可以忽略）
数据恢复: mysql –uroot –p 数据库名 < d:\t1.sql
增：create database db1 charset utf8;  charset utf8可省略,创建数据库
查：show databases;  显示所有数据库
改：alter database db1 charset latin1;(改成欧洲的)
删除: drop database db1;  删除数据库（慎用！）
切换:：use db1; 切换数据库

操作表（文件）

先切换到文件夹下：use db1 
增：create table t1(id int,name char)CHARSET=utf8; 创建表
   
查： select database();            查看当前所在库
       show tables;                  查看所有的表
       desc 表;                       查看表结构 
   	   show creat table 表名；  查看建表语句和搜索引擎，显示的信息更全面
	   show engines;           查看MySQL重要引擎
改： ALTER TABLE t_2 ADD age INT; 增加列名 类型
       RENAME TABLE t_1 TO t_2;    更改表名称
   	   alter table t_1 modify name char(3);  修改列的数据类型
       alter table t_1 change name name1 char(2);  修改列名和数据类型
       ALTER TABLE t_1 DROP age;	删除列

删：drop table t1

操作表数据

增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
	 insert into t1(id,name) values (5,'wusir'), (6,'wusir');
	 insert into t2 select * from t1; 
	  先执行执行select,把查到的内容导入到t2
	 insert into t1(id,name) select id,name from t1;
   
查： select * from 表;     查询表的信息
       select emp_name,salary from employee;  指定列查询
       select emp_name,salary*12 from employee;   在列中使用四则运算
       select emp_name,salary*12 as annul_salary from employee; 重命名
       select emp_name,salary*12 annul_salary from employee; 重命名
       select distinct post from employee;  去重
       select distinct sex,post from employee;  双向去重

改： update 表 set 字段1=值1,字段2=值2 where 条件;
删：delete from t_1 where id=1;
		如果不加where：DELETE FROM t_person; 会删除所有表中的内容


通用建表语句:
CREATE TABLE sanguo(
id INT PRIMARY KEY AUTO_INCREMENT,  # 主键自增
NAME VARCHAR(32),
age INT,
arms VARCHAR(32),
country VARCHAR(32),
skill VARCHAR(32)
)engine = innodb CHARSET=utf8;

查

where

having要跟在group by之后，对分组查询的结果进行过滤（过滤分组）
where要出现在group by之前，执行表中所有数据来进行过滤（过滤行）
另外，having可以用聚合函数，并支持所有where子句操作符数

函数 concat() 拼接

# select concat('姓名 ：',emp_name),concat('年薪:',salary*12) from employee;
# select concat_ws('|','a','b','c')

case when语句

== if条件判断句

  3SELECT
       (
           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;

-- 代表注释

查询用的函数

count 统计值

select concat ('<名字:',name, '>'  ,   '<薪资:',salary,'>') from 表;

max 最大值

min 最小值

avg 平均值

sum 求和

ascii（str）

查看字符的ASCII码值,str是空时返回0

SELECT ASCII('a')

char(数字)

查看ASCII码值对应的字符

SELECT CHAR(97)

concat 拼接字符串

SELECT CONCAT(12,34,'ab')
SELECT uname,CONCAT(age,'岁') FROM t_user;

length（str）

字符串中包含的字符个数

SELECT LENGTH('abc')

left（str，len）

截取字符串

截取字符串左端的len个字符

SELECT LEFT('qwertyui',3)

right（str,len）截取字符串右端的len个字符

SELECT RIGHT('qwertyui',3)

substring(str,pos,len) 指定位置截取,截取字符串str的位置pos起的len个字符（从1开始）

 SELECT SUBSTRING('qwertyuio',2,3)

截取所有人物的姓
SELECT SUBSTRING(uname,1,1) FROM t_user;  
同时去除重复项
SELECT DISTINCT SUBSTRING(uname,1,1) FROM t_user;

ltrim（str）

返回删除了左空格的字符串

SELECT LTRIM('   abc    ')

rtrim（str）

返回删除了右空格的字符串

SELECT RTRIM('     abc     ')

trim（方向 remstr from str）：返回从某侧删除remstr后的字符串str

方向词包括both（两侧）、leading（左）、trailing（右）
SELECT TRIM(‘   abc    ’)   --删除两侧空格
SELECT TRIM(BOTH ‘x’ FROM ‘xxxabcxxx’) --删除两侧特定字符
SELECT TRIM(LEADING 'x' FROM 'xxxabcxxx')   删除左侧特定字
SELECT TRIM(TRAILING 'x' FROM 'xxxabcxxx')  删除右侧特定字

space（n）

返回由n个空格组成的字符串

replace（str,from_str,to_str)

替换字符串

SELECT REPLACE('123abc123','123','def')

lower(str) upper(str)

大小写转换

SELECT LOWER('aBcD')
SELECT UPPER('aBcD')

mod(m,n)

求m%n的余数

SELECT MOD(3,2)

abs(n)

求绝对值

SELECT ABS(-30)

floor（n)

表示向下取整

SELECT FLOOR(5.6)

ceiling（n)

表示向上取整

SELECT CEILING(5.6)

round(n)

表示将值 n 四舍五入为整数，无小数位

SELECT ROUND(5.6)

round(n,d)

表示将值 n 四舍五入为小数点后 D 位的数值，D为小数点后小数位数
若要保留 n 值小数点左边的 D 位，可将 D 设为负值

SELECT ROUND(345.6789,2)

pow(x,y)

求x的y次幂

SELECT POW(2,3)

PI()

获取圆周率

SELECT PI( )

rand()

获取一个0.0-1.0之间的随机数

SELECT RAND( )

获取当前日期

SELECT CURRENT_DATE()

获取当前时间

SELECT CURRENT_TIME()

获取当前日期和时间

SELECT NOW()

时间和日期格式化

date_format(data,format)
SELECT DATE_FORMAT('2018-8-8','%Y年%m月%d日')
SELECT DATE_FORMAT(CURRENT_DATE(),'%y年%m月%d日')
UPDATE t_user SET brithday=DATE_FORMAT(CURRENT_DATE(),'%y年%m月%d日')
										WHERE uname = '吕布'
format参数可用的值如下：
%Y  年份，返回4 位整数
%y  年份，返回2 位整数
%m  月，返回0-12的整数
%d  日期，返回0-31之间的整数
%H  小时 (00..23)
%h  小时 (01..12)
%i  分钟(00..59)
%s  秒 (00..59)

单表查询

公式

使用算数表达式查询某几列:年龄增加50

SELECT uname,country,age+50 FROM t1;

as

SELECT age+50 AS '年龄' FROM t_user;   #修改列名的显示（起别名）注意不要用关键字，as可以省略

distinct 查询时去重

SELECT DISTINCT country FROM t_user;
SELECT DISTINCT country，uname FROM t_user;  #联合去重
查询每个国家都有谁，注意：distinct后面的字段用逗号分隔，逗号两边不能有空格

between

a and b [a,b]

# select * from employee where salary between 10000 and 20000;

in

# select * from employee where salary in (17000,19000);

like 模糊查询

_ 通配符 表示一个字符长度的任意内容

select * from employee where emp_name like 'jin___'

% 通配符 表示任意字符长度的任意内容

select * from employee where emp_name like 'jin%'
select * from employee where emp_name like '%g'
select * from employee where emp_name like '%n%'

regexp 正则匹配

# select * from employee where emp_name regexp '^jin'

查看所有员工中名字是jin开头，n或者g结果的员工信息
select * from employee where emp_name regexp '^jin.*[gn]$';

IS NULL关键字

(判断某个字段是否为NULL不能用等号，需要用IS)

	 SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL;
	 SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL;

	 SELECT emp_name,post_comment FROM employee WHERE post_comment=''; 
	 										注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

比较运算

= > < >= <= != / <>

select * from employee where age>18;
select * from employee where salary<10000;
select * from employee where salary=20000;

逻辑运算

and or not

and
select * from employee where age>18 and post='teacher';

or
select * from employee where salary<10000 or salary>30000;

not
select * from employee where salary not in (10000,17000,18000);

分组聚合 group by

# 查询岗位名以及岗位包含的所有员工名字
# select post,group_concat(emp_name) from employee group by post;

# 查询各部门年龄在20岁以上的人的平均薪资
# select post,avg(salary) from employee where age>20 group by post;

# select * from 表 where 条件 group by 分组

having 对分组之后过滤 (group by 配合having)

having放在分组之后，因为作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组

查询平均薪资大于1w的部门
select avg(salary) from employee group by post having avg(salary) > 10000

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,emp_name,count(id) from employee group by post having count(id)<2

2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

order by 排序

    #  asc 升序
    #  select * from employee order by salary;
    #  select * from employee order by salary asc;
    #  desc 降序
    #  select * from employee order by salary desc;

    # select * from employee order by age,salary;
    # select * from employee order by age,salary desc;
    # select * from employee order by age desc,salary;

排列顺序从高到低 DESC

limit 分页

在查询时可以只检索前几条或者中间某几行数据（数据量很大时，几百万条）
SELECT * FROM t_user LIMIT 0,3;
limit 后面的第一个数字设置从哪里开始检索（偏移量，从0开始）
limit 后面的第二个数字是设置显示多少条

# select * from 表 order by 列 limit n; 取前n条
# select * from 表 order by 列 limit m,n; 从m+1开始,取n条
# select * from 表 order by 列 limit n offset m; 从m+1开始,取n条

多表查询

连表查询

内连接

所有不在条件匹配内的数据，都会被剔出连表

方式一 ：
select * from employee,department where dep_id = department.id;
方式二 ：
select * from employee inner join department on dep_id = department.id;

外连接

left join

左外连接 
select * from employee left join department on dep_id = department.id;

right join

右外连接 
select * from employee right join department on dep_id = department.id;

全外连接

别的数据库里是full join

select * from employee left join department on dep_id = department.id
union
select * from employee right join department on dep_id = department.id

子查询

select * from 表 where 字段 = (select ....);
子查询效率相对于连表查低, 因为 子查询要查两张表,连表查询查一张表

约束

primary key 主键 =非空+唯一

创建表时设置主键

create table t6(id int primary key, name char(12) not null unique);
create table t5(family char(12) ,name char(12),primary key(family,name));  
# 约束各自不能为空 且联合唯一 还占用了整张表的主键

创建表之后设置主键

CREATE TABLE t1(
   id INT NOT NULL,
   NAME CHAR(20)
);
ALTER TABLE t1 ADD PRIMARY KEY (id);

自动更新的主键

CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
-- id  你自己起的字段名字。
-- int  数据类型，整型。
-- primary key 定义这个字段为主键。
-- auto_increment 定义这个字段为自动增长，即如果INSERT时不赋值，则自动加1

auto_increment 自增

自增的必须是主键

create table t6(id int auto_increment, name char(12));   # 报错
create table t8(id int primary key auto_increment, name char(12)) ;
create table t9(id int unique auto_increment, name char(12)) auto_increment=100000; 自增=>非空  非空+唯一约束会被定义成主键 

delete from t7; 清空表数据但不能重置auto_increment
truncate table t7;  # 清空表并且重置auto_increment

所有的操作都无法改变auto_increment的自动计数。但是我们也没有必要去改变它。
1.至少要看到自增的效果
2.至少写3条数据 4，5，6
3.删掉第5条，再看结果
4.再insert一条数据
5.删掉第5条，再看结果
6.再insert一条数据
7.清空整张表
8.再insert一条数据，再看结果

修改auto_increment
alter table 表名 auto_increment = n; 修改表的auto_increment
alter table t7 auto_increment = 1000; 修改表的auto_increment

not null 非空

# create table t1(id int not null,name char(12));
    # 默认插入0
# create table t2(id int,name char(12)  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"
​```

unique唯一 (不能重复)

# create table t4(id int unique,name char(12));

# 联合唯一约束
# create table t5(family char(12),name char(12),unique(family,name));

# 约束各自不能为空 且联合唯一
# create table t5(family char(12) not null,name char(12) not null,unique(family,name)); 


# 唯一+非空 id name
# create table t6(id int not null unique, name char(12) not null unique);
# pri 是怎么产生的？ 第一个被设置了非空+唯一约束会被定义成主键 primary key
# 主键在整张表中只能有一个

default 默认值

create table t3(id int,name char(12),sex enum('male','female') default 'male');
非空约束 和 默认值
create table t3(id int not null,name char(12) not null,sex enum('male','female') not null default 'male');

外键

△外键：用来和其他表建立联系
外键具有保持数据完整性和一致性的机制，目前MySQL只在InnoDB引擎下支持
（ENGINE=INNODB）
外键是表中的一个列，其值必须在另一个表的主键或者唯一键中列出
作为主键的表称为 主表，作为外键的表称为 依赖表
外键会参照主表的主键或唯一键

△外键的作用有两点：
1.对子表(外键所在的表)的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败
2.对父表的作用：对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败

△以下情况创建外键会失败：
外键的引用类型不一样，如主键是int外键是char
找不到主表中引用的列
主键和外键的字符编码不一样

foreign key(class_id) references class3(cid) 

没有建立外键：
create table stu(id int,name char(12),class_id int);
create table class(cid int,cname char(12));
insert into stu values (1,'日魔',1),(2,'炮手',1)
insert into class values(1,'py27');
insert into class values(2,'py28');
select * from stu,class where class_id = cid;
delete from stu where id = 1；
delete from class where cid = 1;

stu2 class2
create table class2(cid int unique,cname char(12));
create table stu2(id int,name char(12),class_id int,foreign key(class_id) 
                  references class2(cid));
insert into class2 values(1,'py27');
insert into stu2 values (1,'日魔',1),(2,'炮手',1)
delete from class2 where cid = 1;
insert into class2 values(2,'py28');
update class2 set cid = 1 where cid = 2;  不能修改

关联关系

一对一

# create table guest(id int primary key,name char(12));
# create table student(id int primary key,sname char(12),gid int unique,
# foreign key(gid) referances guest(id));

一对多 foreign key

# create table class(id int primary key,cname char(12));
# create table student(id int primary key,sname char(16),cid int,
# foreign key(cid) references class(id));

多对多

# create table class(id int primary key,cname char(12));
# create table teacher(id int primary key,tname char(12));
# create table teach_cls(id int,cid int,tid int,
# foreign key(cid) references class(id)),
# foreign key(tid) references teacher(id))
# );

级联更新

on update cascade

stu3 class3 级联更新
create table class3(cid int primary key,cname char(12));
create table stu3(id int,name char(12),class_id int,foreign key(class_id) 
                  references class3(cid) on update cascade);
insert into class3 values(1,'py27');
insert into stu3 values (1,'日魔',1),(2,'炮手',1)
update class3 set cid = 2; 修改了class3中的cid，stu3中相关的数据也会跟着变化，
是on update cascade设置导致的

存储引擎

意义

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作
使用不同的存储引擎还可以获得特定的功能

存储方式、存储机制

表结构 存在一个文件中 : 硬盘上

表数据 存在另一个文件中、内存中

索引(目录) 为了方便查找设计的一个机制 ：

存储引擎的种类

innodb ：

存储方式为索引+数据 表结构 ,数据的持久化存储

提供了具有提交、回滚和崩溃恢复能力的事务安全，支持外键。但是比起Myisam存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎.

myisam ：

存储方式为索引 数据 表结构 数据的持久化存储 ,有表级锁

不支持事务、也不支持外键，但访问速度快，对事务没有要求

memory ：

存储方式为表结构,数据断电消失

查看表引擎

show create table books; 

查看MySQL重要引擎

show engines;

设置引擎

建表的时候可以指定引擎

create table innot(id int) engine = innodb;
create table myist(id int) engine = myisam;
create table memot(id int) engine = memory;

事务

一致性 n条语句的执行状态是一致的

begin；   # 开启事务
select id from innot where id =1 for update;
update innot set id = 2 where id = 1；
commit；  # 提交事务 解锁被锁住的数据，让他们能够被修改

mysql锁

行级锁 ：只对涉及到修改的行加锁，利于并发的修改，但是对于一次性大量修改效率低下
表级锁 ：一次性加一把锁就锁住了整张表，不利于并发的修改，但是加锁速度比行锁的效率要高
外键约束 ：被约束表中的数据不能随意的修改/删除 约束字段据要根据被约束表来使用数据

索引-目录

索引原理

△磁盘预读性原理
1个block块 4096个字节/9ms

△树
树 根节点 分支节点 叶子节点
平衡树 balance tree - B树

△聚集索引/聚簇索引 ： 叶子节点会存储整行数据 —— innodb的主键
△辅助索引/非聚集索引 ：除了主键之外的普通索引都是辅助索引，一个索引没办法查到整行数据，需要回聚集索引再查一次(回表)

△b+树 是为了更好的处理范围问题在b树的基础上有所优化
△mysql中innodb存储引擎的所有的索引树都是b+树

优点：

当数据库中存在很多条记录，例如几十万条，查询速度就成了一个问题
先在目录中查询，然后根据目录所示的页码找到查询内容，大大缩短查询时间.
不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行
表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件

缺点：

索引也需要占空间，如果我们有大量的索引，索引文件可能会占很多空间
3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
使用原则：

建议

1.对经常更新的表就避免对其进行索引，对经常用于查询的字段应该创建索引
2.数据量小的表最好不要使用索引
3.在不同值少的列上(字段上)不要建立索引，比如在"性别"字段上只有男，女两个不同值

建立一个索引

1，创建表时，主键会默认带有索引

2. 创建表时直接指定

CREATE TABLE mytable(  -- 创建表时直接指定
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX index_1 (username)  
); 

3.补索引

CREATE INDEX index_1 ON t_user(uname)
     创建一个索引    索引名  从  表名  在哪个字段

删除索引

drop index index_1 on t_user(uname);

视图

在创建一个视图时，只存放视图的定义，也就是动态检索数据的查询语句，并不存放视图对应的数据，在用户使用视图时才去求相对应的数据，所以视图称为‘虚表’

-- 查看每个国家的总人数，年龄总和，平均年龄，最高年龄，最低年龄
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user
					GROUP BY country;

为上面的语句创建视图：
CREATE VIEW v_user AS
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user 	
				GROUP BY country;    
SELECT * FROM v_user; --以后在使用上面的查询语句时只需要使用视图名

python连接数据库

import pymsql

conn=pymyssq.connet
(host='localhost,user='root',pqssword='123',db='数据库名',charset='utf8') 
                       #先修路-conn

cur = conn.cursor()    #备车-cur

sql = '语句'    #装货

cur.execute(sql)        #发车

emps = cur.fetchall()      #带回交易货物

for i in data:
	print(i)	#查货

创一个表的模板

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
);


#插入记录
#三个部门：教学，销售，运营
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)
;

作业:

数据库小考

书名	作者	出版社	价格	出版日期
倚天屠龙记	egon	北京工业地雷出版社	70	2019-7-1
九阳神功	alex	人民音乐不好听出版社	5	2018-7-4
九阴真经	yuan	北京工业地雷出版社	62	2017-7-12
九阴白骨爪	jinxin	人民音乐不好听出版社	40	2019–8-7
独孤九剑	alex	北京工业地雷出版社	12	2017-9-1
降龙十巴掌	egon	知识产权没有用出版社	20	2019-7-5
葵花宝典	yuan	知识产权没有用出版社	33	2019–8-2

0.建表book，并向表中插入数据
1.查询egon写的所有书和价格
2.找出最贵的图书的价格
3.求所有图书的均价
4.将所有图书按照出版日期排序
5.查询alex写的所有书的平均价格
6.查询人民音乐不好听出版社出版的所有图书
7.查询人民音乐出版社出版的alex写的所有图书和价格
8.找出出版图书均价最高的作者
9.找出最新出版的图书的作者和出版社
10.显示各出版社出版的所有图书
11.查找价格最高的图书，并将它的价格修改为50元
12.删除价格最低的那本书对应的数据
13.将所有alex写的书作业修改成alexsb
14.select year(publish_date) from book
自己研究上面sql语句中的year函数的功能，完成需求：
将所有2017年出版的图书从数据库中删除
15.有文件如下，请根据链接自学pymysql模块，使用python写代码将文件中的数据写入数据库
学python从开始到放弃|alex|人民大学出版社|50|2018-7-1
学mysql从开始到放弃|egon|机械工业出版社|60|2018-6-3
学html从开始到放弃|alex|机械工业出版社|20|2018-4-1
学css从开始到放弃|wusir|机械工业出版社|120|2018-5-2
学js从开始到放弃|wusir|机械工业出版社|100|2018-7-30

# select distinct * from 表 where 条件 group by 字段
# having 过滤 order by 字段 limit n offset m;

# 书名	作者	出版社	价格	出版日期
# create table books(bname char(20),
# author char(12),
# press char(20),
# price float(6,2),
# pub_date date);

# insert into books values
# ('倚天屠龙记','egon','北京工业地雷出版社',70,'2019-7-1'),
# ('九阳神功','alex','人民音乐不好听出版社',5,'2018-7-4'),
# ('九阴真经','yuan','北京工业地雷出版社',62,'2017-7-12'),
# ('九阴白骨爪','jinxin','人民音乐不好听出版社',40,'2019–8-7'),
# ('独孤九剑','alex','北京工业地雷出版社',12,'2017-9-1'),
# ('降龙十巴掌','egon','知识产权没有用出版社',20,'2019-7-5'),
# ('葵花宝典','yuan','知识产权没有用出版社',33,'2019–8-2');

# 查询egon写的所有书和价格
# select bname,price from books where author='egon';

# 找出最贵的图书的价格
# select max(price) from books;

# 求所有图书的均价
# select avg(price) from books;

# 将所有图书按照出版日期排序
# select * from books order by pub_date;

# 查询alex写的所有书的平均价格
# select avg(price) from books where author = 'alex'

# 查询人民音乐不好听出版社出版的所有图书
# select * from books where press = '人民音乐不好听出版社';

# 查询人民音乐出版社出版的alex写的所有图书和价格
# select bname,price from books where press = '人民音乐不好听出版社' and author = 'alex'

# 找出出版图书均价最高的作者
# select author from books group by author order by avg(price) desc limit 1;
# select author,avg(price) as avg_price from books group by author order by avg_price desc limit 1;

# 找出最新出版的图书的作者和出版社
# select author,press from books order by pub_date desc limit 1

# 显示各出版社出版的所有图书
# select press,group_concat(bname) from books group by press;

# 查找价格最高的图书,并将它的价格修改为50元
# select max(price) from books;    # 70
# update books set price=50 where price = 70;

# update books set price=50 order by price desc limit 1;
# update books set price=50 where price = (select * from (select max(price) from books)as t);

# 删除价格最低的那本书对应的数据
# select min(price) from books;  # 5
# delete from books where price = 5;

# delete from books order by price limit 1;
# delete from books where price = (select * from (select min(price) from books)as t);

# 将所有alex写的书作者修改成alexsb
# update books set author = 'alexsb' where author = 'alex';

# select year(publish_date) from book
# month(publish_date)
# day(publish_date)
# delete from books where year(publish_date) = 2017;