MYSQL初识：
数据库优势：
程序稳定性：程序崩溃不会影响数据和服务
数据一致性：所有数据存储在一起
并发：数据库本身支持并发
效率：使用数据库对数据进行的增删改查效率要高
数据库分类：
关系型数据库(表结构)：
特点相对慢，数据之间关联强
mysql，oracle，sqlserver，sqllite，accesse
非关系型数据库（key:value）：
特点相对快，数据与数据的关联小
redis，mongodb ，memcache(淘汰-内存级别，淘汰了)
区别：
- 关系型数据库存储效率相对低，数据与数据之间关联紧密
- 关系型数据库存储效率相对高，数据与数据之间的关系是key:value
Mysql语句分为三种：
DBMS：数据库管理系统软件
DDL 语句， 数据库定义语言：数据库，表，视图，索引，存储过程，例如create，drop，alter
DML 语句，数据库操纵语言：插入、删除、更新、查询数据，insert，delete，update，select
DCL 语句， 数据库控制语言：用户的访问权限，grant，revoke
Mysql 默认三个数据库：
mysql：保存mysql权限，配置参数，状态信息，主从配置
information_schema： 保存着mysql维护其他数据库信息，如库名，表和访问权限等
performance_schema：数据库存储引擎，监视数据库运行的资源消耗，资源等待等等
Mysql工作流程：
- 登录授权认证安全：
- 查询，解析，分析，优化，缓存
- 存储过程，触发器，视图
- 存储和提取数据
- 存储数据，表信息，用户操作日志
Mysql 安装：
Mysql安装：
路径不能有中文
路径不能有特殊字符
InnoDB创建数据库和表后生成的文件：
数据库：文件夹，表：文件
db.opt 字符集
.frm 表结构
.idb 每表一个表空间（表数据和表索引）
存储引擎：
数据的存储方式-->存储引擎
使用不同的存储引擎，数据是已不同方法存储的
查看存储引擎：show engines;
Innodb:
innodb引擎，支持事务ACID特性，支持数据的完整型，并发条件下保持数据一致性，还可以保证事务的完整提交回滚，保证数据准确性，5.6以上默认innodb
mysql5.6以上，默认的存储方式
transactions 事务：保证数据的安全，执行要么成功要么失败，使用的是支付宝支付
row-level locking 行级锁：
table-level locking 表级锁：
foreign keys：外键：
树tree = 加速查询（树形结构（数据+树）+ 表结构）
- 存储两个文件
并发 修改 删除操作的效率比较高
事务：
ACID（原子性，一致性，隔离性，持久性）事务必须支持这种特性：
原子性：事务操作同时完成，或者同时不完成
一致性：完成的数据结果要保持一致
隔离性：事务之间不能相互影响
持久性：当服务器宕机了，仍然要保证数据的完整
事务隔离级别：隔离越高性能越低，可以配置，事务通过数据集交互
事务日志：自我完成重做或撤销：
redo log 重做日志：写入到日志里面，服务器崩溃了起来重新操作
undo log 撤销日志：服务器崩溃了，还会重新执行
Myisam：
如果引用程序是以读操作或者插入操作为主，并且对事务的完整型，并发性要求不高，选择Myisam引擎
mysql5.5一下，默认的存储方式
持久化存储
table-level locking 表级锁
树tree= 加速查询（树形结构（数据+树）+ 表结构）
存储三个文件
Memory：
将所有的数据保存在内存中，可以提供快速的访问，缺陷是对表大小有限制，不支持事务，异常关闭数据丢失，现在不用Memory引擎，都用非关系型数据库代替
基于hash
账号创建授权：
删除匿名账号（安全）：
drop user ''@localhost;
drop user 'root'@'::1';
设置管理员账号：
1，使用管理员登录：mysql -uroot
2，修改管理员密码：set password = password("123");
3，登录管理员账号：mysql -uroot -p123
创建数据库账号：
- 查看创建的账号：select user(); select host,user from mysql.user;
- 创建账号：
- create user "haiyang"@"192.168.13.%" identified by "123";
- flush privileges;
修改账号密码：
- update user set password=password("1234") where user='haiyang' and host='192.168.13.%';
- flush privileges;
grant 账号赋权 (增删改查)：
grant all 所有权限
存在账号赋权：grant select on mysql.* to "hai"@"192.168.13.%"
无账号创建，并赋予读取权限：
grant select on mysql.* to "hai"@"192.168.13.%" identified by "123";
查看赋予的权限：
- show grants for 'haiyang'@'192.168.13.%';
创建指定的ip地址登录，只能指定这个ip地址才可以登录：
- mysql -uhaiyang -p123 -h192.168.13.144
账号赋予的权限可以进行回收........
查看引擎： show engines;
查看字符集：show session variables like '%character%';
数据-增删改查：
库操作：
创建库：create database ftp;
切换库：use 库名;
删除库：drop database 库名;
查看当前在哪个库下：select database();
表操作：
create 创建表：
- create table 表名(id int,name char(字符)) engine=myisam;
drop 删除表：
drop table 表名;
alter
修改表名：alter table 表名 rename 新表名;
增加新的字段：alter table employee add haha char(10);
修改表字段大小：alter table biao modify name char(20);
修改表字段名称：alter table biao change name name1 char(20)
id age name varchar(255) alter table 表名 rename 新表明 alter table 表名 add 新字段 类型(宽度) 约束; # add 新字段 类型(宽度) 约束 after id # add 新字段 类型(宽度) 约束 first # alter table 表名 change 旧字段 新字段 类型(宽度) 约束; # change name username char(12) not null # change name name char(12) not null # change name name varchar(255) after id; # alter table 表名 modify 存在的字段 新类型(新宽度) 新约束; # modify name char(12) unique; # modify name char(12) unique after id;
show
查看表： show tables;
查看表结构： desc 表名; = describe 表名;
查看表结构的详细信息：show create table userinfo;
数据操作：
插入数据：insert into 表名value(1,"haiyang"); 插入一条
插入两条：insert into 表名values(1,"haha"),(2,"dudu"); s 插入两条
自增插入：insert into 表名("字段") values("haha"),("dudu");
修改一个字段：update 表名set name="fafa" where id=2; （必须指定ID）
修改两个字段：update employee set age=20,salary=8000 where id=1;
查看表数据：select * from 表名;
清空表：delete from 表名;
删除表中ID为1的数据：delete from 表名 where id=1;
Pymysql-数据插入：
插入语句示例：
import pymysql db = pymysql.connect("127.0.0.1", "root", "123", "day32") #打开mysql数据库： cursor = db.cursor() #使用cursor方法获取操作游标： #插入语句 sql = 'insert into book(shuming,zuozhe,chubanshe,price,date) values(%s,%s,%s,%s,%s);' #格式化输出，这样可以避免格式错误 try: cursor.execute(sql,("学python从开始到放弃","alex","人民大学出版社",50,"2018-7-1")), cursor.execute(sql,("学mysql从开始到放弃","egon","机械工业出版社",60,"2018-6-3")) db.commit() except: db.rollback() # 插入语句事务不完成则回滚 db.close() # 关闭数据库连接 import pymysql conn = pymysql.Connection(host='127.0.0.1', user='root', password="123", database='kaoshi') cur = conn.cursor() with open(r'E:\Pycharm-代码\考试\数据库考试\数据库答案\data\user',mode='r',encoding='utf-8') as f: for line in f: new_line = line.strip().split(',') print(new_line) sql = 'insert into user(userid,user_name,password) values(%s,%s,%s)' cur.execute(sql, (new_line)) conn.commit() cur.close() conn.close()
创建表-数值类型：
数值类型：
设置严格模式： 不支持对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"
整数：
int： 占用四个字节，正负两亿* 这两个比较常用，都可以存负数
tinyint：占用1个字节 ，负128,正127 unsigned转为整数正负和在一起255！！！
整数示例：
# 创建表一个是默认宽度的int，一个是指定宽度的int(5) mysql> create table t1(id1 int,id2 int(5)); #插入多写数据不会报错 mysql> insert into t1 values(111111,111111); mysql> select * from t1; +--------+--------+ | id1 | id2 | +--------+--------+ | 1 | 1 | | 111111 | 111111 | +--------+--------+ # 修改id1字段 给字段添加一个unsigned表示无符号，就是没有负数，只有整数 mysql> alter table t1 modify id1 int unsigned; mysql> desc t1; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id1 | int(10) unsigned | YES | | NULL | | | id2 | int(5) | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ #只能插入十个数据，超过会报错 mysql> insert into t1 values (2147483648,2147483647); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +------------+------------+ | id1 | id2 | +------------+------------+ | 1 | 1 | | 111111 | 111111 | | 2147483648 | 2147483647 | +------------+------------+ #数据小时候使用，字节为： mysql> create table t2(id tinyint); #默认创建最大插入127最大数量 #添加unsigned最大可以插入为255，因为变成整数，负数转为整数为255 mysql> alter table t2 modify id tinyint unsigned;
小数：
float：float(255.30) 255整数多少位，5,小数点保留位
double：double(255.30) 精度更高，精确位四舍五入
decimal：decimal(60,30)
小数示例：
# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位 mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); #插入数据被截断，四舍五入 mysql> insert into t2 values (1.234,1.234,1.234); mysql> insert into t2 values (1.235,1.235,1.235); +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | | 1.24 | 1.24 | 1.24 | +------+------+------+ #建立新表，去掉参数约束： #float小数点默认五位，double无限制，decimal取整 insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555); mysql> select * from t3; +---------+--------------------+------+ | id1 | id2 | id3 | +---------+--------------------+------+ | 1.23556 | 1.2355555555555555 | 1 | +---------+--------------------+------+
时间:
日期时间类型：
内置函数now() user() database()
datatime : 年月日时间 日志/打卡时间/博客/评论/页面超时时间 *，表示的范围大
data: 年月日 员工生日/入职日期/离职时间/开班时间 *
time: 8.30 上班，上课时间，竞赛数据 *
year: 年份
timestamp：由于表示范围问题，用的少了,约束不能为空，默认值是当前时间
类型示例：
#创建三个类型：date，time，datatime mysql> create table t4 (d date,t time,dt datetime); #插入内置函数now()年月日时间，根据格式写入各自的类型中： mysql> insert into t4 values(now(),now(),now()); mysql> insert into t4 values(null,null,now()); mysql> select * from t4; #默认可以插入空 +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2019-07-30 | 17:45:34 | 2019-07-30 17:45:34 | | NULL | NULL | NULL | | NULL | NULL | 2019-07-30 17:48:53 | +------------+----------+---------------------+year:
mysql> create table t7(y year); mysql> insert into t7 values(2016); mysql> select * from t7; +------+ | y | +------+ | 2016 | +------+datatime：
#datatime 插入的几种方式 mysql> create table t8 (dt datetime); mysql> insert into t8 values ('2018-9-26 12:20:10'); mysql> insert into t8 values ('2018/9/26 12+20+10'); mysql> insert into t8 values ('20180926122010'); mysql> insert into t8 values (20180926122010); mysql> select * from t8; +---------------------+ | dt | +---------------------+ | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | +---------------------+
字符串:
char长度范围(0-255)，varchar长度范围(0-65535)
char：
- 定长存储，存储速度更快，读取速度也快，占用空间 *
- char(12)
- haiy ---> "haiy........"12的长度，剩下的字节会自动补全
varchar：
变长存储，存储速度慢，占用空间小 *
varchar(12)
haiy ---> "haiy........"5的长度
时间换空间 list，因为空间大需要一定时间计算
空间换时间 dict 占用空间，直接拿key取值，速度快
使用场景：
- 手机号码、身份证号码：char
- 用户名/密码：有一定范围性 char
- 评论：varchar
char varchar示例：按照字符算长度
#创建varchar char字段 mysql> create table t9 (v varchar(4),c char(4)); #插入数据，查看数据char类型会去掉空格 mysql> insert into t9 values ('da ','da '); mysql> select * from t9; +------+------+ | v | c | +------+------+ | da | da | +------+------+ #查询结果计算真实长度 mysql> select length(v),length(c) from t9; +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 4 | 2 | +-----------+-----------+ #超过字符数量进行截断： mysql> insert into t9 values ('huhaiyang','huhaiyang'); mysql> select * from t9; +------+------+ | v | c | +------+------+ | da | da | | huha | huha | +------+------+
enum（枚举类型）:
gender 单选题，只能从有限的条件中选择：
#enum，只能选择单个: mysql> create table t10 (name char(20),gender enum('haiyang','junli')); #只能选择一个插入: mysql> insert into t10 values("baobao","haiyang"); mysql> select * from t10; +--------+---------+ | name | gender | +--------+---------+ | baobao | haiyang | +--------+---------+ #多插入报错: mysql> insert into t10 values("baobao","haiyang","junli"); ERROR 1136 (21S01): Column count doesn't match value count at row 1
set 多选题，从有限的条件中选，去重：
#多选去重 mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车')); #查看多选内容： mysql> desc t11; +-------+------------------------------------------+ | Field | Type | +-------+------------------------------------------+ | name | char(20) | | hobby | set('抽烟','喝酒','烫头','翻车') | +-------+------------------------------------------+ #插入数据，从set集合中可以多选择，选择的如果重复可以去重，没有set中选项为空 mysql> insert into t11 values("junli","抽烟,喝酒");
表约束：
unsigned： 无符号的：数字
not null： 非空约束，指定某列不能为空；
default： 默认值，不是非空也可以设置默认值
unique： 唯一约束，id user phone mail，约束不能重复，约束角度：not null + uniqe = primary key
auto_increment： 自动 针对int，自带not null ，前提需要设置unique
primary key： 主键约束 ，相当于非空加唯一，一张表只有一个
foreign key： 外键约束 前提需要设置主键或unique
- on delete cascaed 级联删除：
- on update cascaed 级联更新：
not null非空：
非空约束，指定某列不能为空；
mysql> create table t12 (id int not null); #指定为空 mysql> desc t12; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ #插入空报错 mysql> insert into t12 values (null); ERROR 1048 (23000): Column 'id' cannot be null
null+default：
#创建表字段，插入数据为空时，默认666 mysql> create table t13(id1 int not null,id2 int not null default 666); mysql> desc t13; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | | NULL | | | id2 | int(11) | NO | | 666 | | +-------+---------+------+-----+---------+-------+ #插入数据，t12字段默认666 mysql> insert into t13 (id1) values (111); #如有id2有新的数据，覆盖默认数据666 mysql> insert into t13 (id1,id2) values (112,223); mysql> select * from t13; +-----+-----+ | id1 | id2 | +-----+-----+ | 111 | 666 | | 112 | 223 |
unique （唯一约束）：
约束不能重复
对与mysql来说，数据与数据之间相等就是重复，null 不能用=判断，对与nuique说，可以插入多个空值
#方法一： mysql> create table department1(id int unique,name varchar(20) unique,comment varchar(100)); #方法二： mysql> create table department2(id int,name varchar(20),comment varchar(100),unique(name)); #测试唯一约束： mysql> insert into department1 values(1,"IT","python"); mysql> insert into department1 values(1,"IT","python"); ERROR 1062 (23000): Duplicate entry '1' for key 'id'
not null 和unique唯一约束：
如果一张表中没有设置primary key主键，那么第一个设置非空+唯一的字段会被设置成主键
特点：一张表中只能有一个主键
不能为空，不能重复：
#约束ID不能为空，不能重复，PRI=primary mysql> create table t14(id int not null unique); mysql> desc t14; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+
unique 联合唯一：
多个字段一起设置唯一约束
#设置host，port联合唯一后，在插入和这两个字段相同的数据报错 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(4,"dudu","192.168.3.3",88); mysql> insert into service values(8,"dudu","192.168.3.3",88); ERROR 1062 (23000): Duplicate entry '192.168.3.3-88' for key 'host'
auto_increment 自增：
第一 只能操作数据
第二 自带非空属性
第三 只能对unique使用
第四：不受删除影响，内部会记录自增数量，使用truncate table 表名，自增清零
#设置自增必须设置联合唯一： mysql> create table t15(id int unique auto_increment,name varchar(5)); #插入数据查看： mysql> insert into t15(name) values("hai"); mysql> insert into t15(name) values("yang"); #数据自动增长 mysql> select * from t15; +----+------+ | id | name | +----+------+ | 1 | hai | | 2 | yang | +----+------+ #也可以自己添加数据： mysql> insert into t15 values(10,"yang"); #AUTO_INCREMENT=12 每次都会记录： mysql> show create table t15; | t15 | CREATE TABLE `t15` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(5) DEFAULT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
primary key：
主键
约束的角度上：not null + unqiue
一张表只能有一个主键
#创建一个主键 mysql> create table t16(id int primary key auto_increment,name char(14)); mysql> insert into t16(name) values("haiyang"); mysql> select * from t16; +----+---------+ | id | name | +----+---------+ | 1 | haiyang | | 2 | haiyang | +----+---------+联合主键：
mysql> create table t17(family_name char(4),name char(12),primary key(family_name,name));
foreign key 外键：
创建表的类型必须是innodb存储引擎：
外键关联，被关联表必须要设置主键或者至少是unique唯一约束：
#部门表，员工表相互关联 #先创建部门表被关联表必须要设置主键，在创建员工表关联 mysql> create table departments (dep_id int(4) primary key,dep_name varchar(11)); #在创建员工表进行关联： mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); foreign key 关联字段，references departments(dep_id))被关联字段（部门表主键）
外键操作：
#创建部门表被关联表 mysql> create table department(id int primary key,name varchar(10) not null); #创建员工表进行关联，联级更新：on update cascade 联级删除：on delete cascade create table employ(id int primary key,name varchar(10) not null,dpt_id int,foreign key(dpt_id) references department(id) on update cascade on delete cascade); #部门表-父表插入数据 mysql> insert into department values(1,"技术部"),(2,"销售部"),(3,"人力资源部"); #员工表-插入数据 insert into employ values (1,'yuan',1), (2,'nezha',2), (3,'egon',2), (4,'alex',2), (5,'wusir',3), (6,'李沁洋',3), (7,'皮卡丘',3), (8,'程咬金',3), (9,'程咬银',3); #删除附表中的数据，子表也会跟着删除 mysql> delete from department where id=2; mysql> select * from department; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 技术部 | | 3 | 人力资源部 | +----+-----------------+ mysql> select * from employ; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 3 | | 6 | 李沁洋 | 3 | | 7 | 皮卡丘 | 3 | | 8 | 程咬金 | 3 | | 9 | 程咬银 | 3 | +----+-----------+--------+ #更新父表department，子表employee中对应的记录跟着改： mysql> update department set id=2 where id =3; mysql> select * from employ; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 2 | | 6 | 李沁洋 | 2 | | 7 | 皮卡丘 | 2 | | 8 | 程咬金 | 2 | | 9 | 程咬银 | 2 | +----+-----------+--------+
表之间关系：
表之间关系三种模式：
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
一对一关系：
两张表：学生表和客户表：一个学生是一个客户
关联方式：foreign key+unique
#创建客户表 create table customer( id int primary key auto_increment, name varchar(20) not null, qq varchar(10) not null, phone char(16) not null); #创建学生表： create table student( id int primary key auto_increment, class_name varchar(20) not null, customer_id int unique, #该字段必须要唯一 foreign key(customer_id) references customer(id) #外键字段要保证unique on delete cascade on update cascade); #客户表写入数据： insert into customer(name,qq,phone) values ('海洋','31811231',13811341220), ('俊丽','31811231',13811341220), ('hhh','31811231',13811341220); mysql> select * from customer; +----+--------+----------+-------------+ | id | name | qq | phone | +----+--------+----------+-------------+ | 1 | 海洋 | 31811231 | 13811341220 | | 2 | 俊丽 | 31811231 | 13811341220 | | 3 | hhh | 31811231 | 13811341220 | +----+--------+----------+-------------+ #一对一增加学生，customer_id设置了唯一约束，不可重复： mysql> insert into student(class_name,customer_id) values("一班",1),("二班",2),("二班",3);
一对多：
建立三张表：出版社，作者信息，书
一对多或者一对多：一个出版社可以出版多本书 关联方式：foreign key
#创建出版社表：设置id为主键 mysql> create table press(id int primary key auto_increment,name varchar(20)); #创建书籍:将press_id关联到出版社表ID，联表删除和更新，如删除一个出版社，出版社对应的数据全部清空 create table book( id int primary key auto_increment, name varchar(20), press_id int not null, #一对多字段不约束设置为null这样可以插入多条数据 foreign key(press_id) references press(id) on delete cascade on update cascade); #创建三个出版社： mysql> insert into press(name) values('北京工业地雷出版社'),('人民音乐不好听出版社'),('知识产权没有用出版社'); mysql> select * from press; +----+--------------------------------+ | id | name | +----+--------------------------------+ | 1 | 北京工业地雷出版社 | | 2 | 人民音乐不好听出版社 | | 3 | 知识产权没有用出版社 | +----+--------------------------------+ #查看到出版社表每个ID对应一个出版社，这时我们可以根据ID进行插入数据，可以多个数据对应一个出版社ID mysql> insert into book(name,press_id) values('九阳神功',1),('九阴真经',2),('九阴白骨爪',2),('独孤九剑',3),('降龙十巴掌',2),('葵花宝典',3); mysql> select * from book; +----+-----------------+----------+ | id | name | press_id | +----+-----------------+----------+ | 1 | 九阳神功 | 1 | | 2 | 九阴真经 | 2 | | 3 | 九阴白骨爪 | 2 | | 4 | 独孤九剑 | 3 | | 5 | 降龙十巴掌 | 2 | | 6 | 葵花宝典 | 3 | +----+-----------------+----------+
多对多：
三张表：出版社，作者信息，书
关联方式：foreign key+一张新的表
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
#创建作者表： mysql> create table author(id int primary key auto_increment,name varchar(20)); #创建一张新表存放作者表和书表关联关系，如要查询数据，直接查新建的这个表： create table author2book( id int not null unique auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) ); #插入四个作者： mysql> insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq'); mysql> select * from author; #查看作者id +----+---------+ | id | name | +----+---------+ | 1 | egon | | 2 | alex | | 3 | yuanhao | | 4 | wpq | +----+---------+ mysql> select * from book; #查看书籍id +----+-----------------+----------+ | id | name | press_id | +----+-----------------+----------+ | 1 | 九阳神功 | 1 | | 2 | 九阴真经 | 2 | | 3 | 九阴白骨爪 | 2 | | 4 | 独孤九剑 | 3 | | 5 | 降龙十巴掌 | 2 | | 6 | 葵花宝典 | 3 | +----+-----------------+----------+ #根据两者关系，插入数据： mysql> insert into author2book(author_id,book_id) values(1,1),(1,2),(3,6),(4,1),(4,5);
单表查询：
简单查询：
#根据字段查看数据 mysql> select emp_name,salary from employee;distinct去重：联合去重
mysql> select distinct post from employee;通过四则运算查询：
mysql> select emp_name, salary*12 from employee; mysql> select emp_name, salary*12 as Annaul from employee; #计算的字段修改成Annual
定义显示格式：case when 筛选列，条件判断：
#concat 函数用于拼接字符串： mysql> select concat('姓名: ',emp_name,' 年薪: ', salary*12) as Annual_salary from employee; mysql> select concat("<姓名：",emp_name,">","<薪资：",salary,">") from employee; #concat_ws ":"第一个参数为分隔符： mysql> select concat_WS(': ',emp_name,salary*12) as Annual_salary from employee; #case when 从头到尾，条件判断，成功匹配结果concat拼接： 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条件约束：
范围查询：
<> >= = != between and or not in
#单条件查询 = ： mysql> select emp_name from employee where post="sale"; mysql> select emp_name,age from employee where post="teacher";#查看所有老师年龄 #多条件查询 = and： mysql> select emp_name from employee where post="sale" and salary > 1000; #查看所有老师年龄并且大于30的： mysql> select emp_name,age from employee where post="teacher" and age > 30; #between and 关键字查询 一万到两万范围条件查询： mysql> select emp_name from employee where salary between 10000 and 20000; mysql> select emp_name from employee where salary not between 10000 and 20000; #关键字is 判断某个字段是否为空，不能用等号，这种判断使用is mysql> select emp_name,post_comment from employee where post_comment is null; #关键字in：判断元祖里面数据是否在salary中 mysql> select emp_name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000; #这种方法比较麻烦 mysql> select emp_name,salary from employee where salary in(3000,3500,9000); mysql> select emp_name,salary from employee where salary not in(3000,3500,9000);
like 模糊查询：
like：
- 通配符% ：一个百分号代表任意长度的任意字符
- “a%” 开头
- "%ing" 结尾
- "%a%" 包含
- _
- "a_" 一个下划线代表一个任意字符
- 通配符% ：一个百分号代表任意长度的任意字符
regexp：正则匹配
“^a”
"\d+"
模糊查询示例：
#匹配以什么开头： mysql> select * from employee where emp_name like "eg%"; #匹配中间字符： mysql> select * from employee where emp_name like "%咬%"; #一个下划线代表一个字符： mysql> select * from employee where emp_name like "__金"; #匹配老师字段并且以jin开头 select emp_name,age from employee where post="teacher" and emp_name like"jin%";
分组：
group by
根据某个重复率比较高的字段进行的，这个字段有多少种可能就分成多少个组
- 根据性别分组:男的一组，女的一组
- 根据部门分区:销售一组，教学一组
- 去重，一旦分组就不能对具体某一条数据进行操作了
- group_concat：了解，只用来做最终的显示，不能作为中间结果操作其他数据
分组示例：
#将post分组，在使用count查看，组内有多少成员 mysql> select post,count(id) from employee group by post; mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+
聚合函数：
count：计数，每个组对应几条数据
max min：求最大最小值
avg： 求平均值
sum：就和值
聚合函数示例：
#查看表有多少列 mysql> select count(*) from employee; #查看某个字段=1有多少个 mysql> SELECT COUNT(*) FROM employee WHERE depart_id=1; #max 取表中最大工资，mix取小 mysql> select max(salary) from employee; #平均值 mysql> select avg(salary) from employee; #ID等于三的平均值 mysql> select avg(salary) from employee where depart_id=3;
分组+聚合：
#对男女分组，在查看男女分组的数量 mysql> select sex,count(id) from employee group by sex; #查看post分组成员平均工资 mysql> select post,avg(salary) from employee group by post; #查看男女分组平均工资 mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+
having 筛选：
where：筛选行
having：筛选组
执行优先级从高到低：where > group by > having >order by > limit 1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使 用聚合函数
过滤示例：
#查询大于十万的员工 mysql> select * from employee where salary > 100000; #分组数量大于三的： mysql> select post,count(id) from employee group by post having count(id) > 3; +-----------+-----------+ | post | count(id) | +-----------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | +-----------+-----------+ mysql> select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2; #查出各岗位平均工资大约1000切小于20000的岗位名和平均工资 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+
order by 排序：
按单列排序：
#默认正序排，从小到大： mysql> select * from employee order by salary; mysql> select * from employee order by salary ASC; #两条命令相同 +------------+ | salary | +------------+ | 1000.37 | | 2000.35 | | 2100.00 | | 3000.13 | #降序从大到小排： mysql> select * from employee order by salary desc; +------------+ | salary | +------------+ | 1000000.31 | | 30000.00 | | 20000.00 |
按多列排序：
#队列排序逗号分开： mysql> select * from employee order by age asc,hire_date desc; #查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
limit 限制查询的记录数：
示例：
#排序查询，结果只显示六个 mysql> select * from employee order by salary desc limit 6; #从五开始，第一条数据为第六条 mysql> select * from employee order by salary desc limit 5,6;
多表查询：
交叉查询（了解）：
先计算两张表的笛卡尔积，在根据用户给出的条件进行筛选
select * from department,employee where dep_id = department.id;
内外连接：
inner join 内连接：
内连接只显示两张表中相互匹配的项，其他不匹配的不显示：
#表的先后顺序，跟查询显示顺序相同 mysql> select * from employee e inner join department d on e.dep_id=d.id; mysql> select * from employee e inner join department d on e.dep_id = d.id order by e.age;
外连接：
left 左外连接：
不管左表中是否匹配上都会显示所有内容：
mysql> select * from employee e left join department d on e.dep_id = d.id; #右连接其实只要把表替换一下位置就可以 mysql> select * from department d left join employee e on d.id = e.dep_id;
right 右外连接：
不管右表中是否匹配上都会显示所有内容：
mysql> select * from department d right join employee e on d.id = e.dep_id;
全外连接：
union 上下联表
左右表中不匹配的都会显示：
mysql> select * from employee e left join department d on e.dep_id = d.id union select * from department d left join employee e on d.id = e.dep_id;
子查询：
子查询是将一个查询语句嵌套在另一个查询语句中（几乎哪里都可以放）
内层查询语句的查询结果，可以为外层查询语句提供查询条件
连表的效率比子查询的效率要高
语句中出现两个select语句就是子查询：
#查询大于所有人平均年龄的员工名与年龄，一个表查询两次 mysql> select name from employee where age > (select avg(age) from employee); #查询平均年龄在25岁以上的部门名 mysql> select name from department where id in(select dep_id from employee group by dep_id having avg(age) > 25); #查询技术部员工姓名： mysql> select name from employee where dep_id in(select id from department where name = "技术"); #查看不足1人的部门名(子查询得到的是有人的部门id) mysql> select name from department where id not in (select distinct dep_id from employee); #查询大于部门内平均年龄的员工名、年龄 一个表内查询内连接，后在条件判断 mysql> select * from employee e inner join (select dep_id,avg(age) avg_age from employee group by dep_id) t on e.dep_id = t.dep_id where e.age > t.avg_age; #查询每个部门最新入职的那位员工 #t2表里面取的是分组名称和最早入职时间，在内连接，post匹配相同，在where筛选相同时间 mysql> select * from emp t1 inner join (select post,max(hire_date) max_date from emp group by post) as t2 on t1.post=t2.post where t1.hire_date = t2.max_date; #简单方法： mysql> select e.name,e.hire_date from (select * from emp order by hire_date desc) as e group by e.post;
索引原理：
是存储引擎用于快速找到记录的一种数据结构 （数据结构 b+树）
索引的主要目的就是提高查询效率，就跟我们读书用的目录一样，通过目录在找到章，在找到页数，而数据库是将数据分成不同的树状图
磁盘IO与预读：
磁盘IO是非常昂贵的操作，计算机操作系统做了一些优化，当发生一次IO时，不光把当前磁盘地址的数据读取，相邻的数据也会读取到内存缓存区中，当计算机再次访问，数据会很快被访问到
树：
数他是一个有限节点组成，它具有层次关系的集合，一个根节点，每个根节点下有两个父节点，每个父节点下有一个子节点，树的高度为3层
b+树：
b+树的数据结构，他的作用是当你你每次查找数据时，他可以将磁盘IO数控制在一个很小的数量级，从而提高查找速度，分为辅助索引和聚集索引
数据只存储在叶子节点
在字典节点之间加入双向地址连接，更方便在子节点之间进行数据的读
innodb
聚集索引：
- 聚集索引就是把每张表的主键构建一颗B+树，同时叶子节点存放为整张表的行记录数据，也叫数据页，数据也成为索引的一部分，每个数据页通过一个双向链表来进行链接，聚合索引能询快速查询到数据
- 聚合索引好处：
- 1，聚合索引是双向链表，可以快速找到最后一个数据（双向链表：从头或尾节点都可遍历）
- 2，聚合索引查找数据，先通过叶子节点上层中间节点得到范围，之后再读取数据页
辅助索引：
- 除了主键之外所有的索引都是辅助索引
- 叶子节点不直接指向数据页，每张表可以有多个非聚集索引，但需要更多磁盘和内容
- 多个辅助索引引影响insert和update速度
回表：只查询一个索引，并不能解决查询中的问题，还需要到具体的表中去获取整行数据
myisam索引：
辅助索引，除了主键之外，所有的索引都是辅助索引
索引的种类四个：
primary key 创建自带索引效果 非空 + 唯一 + 聚集索引
unique 唯一约束的创建也自带索引效果 唯一 + 辅助索引
index 普通索引 辅助索引
联合索引（联合主键，联合唯一，联合普通索引）
索引-create：
- 创建了索引之后的效率大幅度提高
文件所占的硬盘资源也大幅度提高
数据库查询时间：加上网络和返回时间一般为为0.3秒
创建索引：
- mysql> create index ind_name on s1(id); 创建树形结构
删除索引：
- mysql> drop index ind_name on biao;
查看索引：
- show create table s1;
索引的优缺点：
优点：查找速度快
缺点：浪费空间，拖慢写的速度
联合索引：
联合索引是指对表上多个列合起来做一个索引：
创建：create index ind_mix on s1(id,email);
where a= xx and b=xxx
a/b分别创建了索引，正常情况下只能命中一个（解决：对ab创建联合索引）
在多个条件相连的情况下，使用联合索引速度高于单字段索引
索引的使用规则：
正确使用索引:
1，所查询的列不是创建索引的列
2，使用between或者>查询数据的范围不可过大，也不能有效利用索引（between and > < != not in ）
3，like 如果把% 放在最前面也不能命中索引
4，如果创建索引的列内容重复率高也不能有效利用索引（重复率不超过10%的列比较适合做索引）
5，查询条件中不能带运算或者函数，必须是字段 = “值“，where条件后面等号前面不能为运算符
6，多条件的情况：
and 只要有一个条件列是索引，就可以命中（区分两边一个分度高的字段进行查询）
or 要所有条件列都是索引才能命中索引（从左至右）
7，联合索引规则：
1.创建索引的顺序id email，其中一个字段查询开始出现了范围过大，索引效果失效
2.联合索引在使用的时候遵循最左前缀原则 s1(abcd),联合索引带着a就可以命中
3.联合索引中也是只能使用and能生效，使用or失效
mysql explain ：
查看是否索引成功
explain select count(*) from s1;
覆盖索引：从辅助索引中就可以得到查询记录，不需要查询聚集索引中的记录
索引合并：两个索引临时合并一起使用，不常用
mysql慢日志：
mysql 导入导出：
备份单表：
#语法： # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.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数据恢复：
#方法一： [root@egon backup]# mysql -uroot -p123 < /backup/all.sql #方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; #关闭二进制日志，只对当前session生效 mysql> source /root/db1.sql
七表联查速度慢怎么办：
表结构的角度：
- 尽量用固定长度的数据类型代替可变长度类型
- 把固定长度的字段放在前面
数据的角度：
- 如果表中的数据越多，查询效率越慢：
- 列多：垂直分表 （列长了）
- 行多：水平分表 （数据多了）
- 如果表中的数据越多，查询效率越慢：
从sql的角度：
- 尽量把条件写的细致点，where条件要多做筛选
- 多表尽量连表代替子查询
- 创建有效的索引，而规避无效的索引，使用联合索引
从配置的角度：
- 开启慢日志查询，确认具体有问题的sql
从架构的角度：
- 读写分离，解决读的瓶颈
4942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
