s4 mysql基础 笔记
MySQL一
____oldboy fullstack4
参考资料：
Python开发【第十七篇】：MySQL（一）
http://www.cnblogs.com/wupeiqi/articles/5713315.html
课程安排：
Web框架：Django
- Python
- 前端
- MySQL
项目实战：
- 简单CURD
- 报障系统（博客+BBS+后台管理）
- CMDB资产管理
- CRM
- 堡垒机
爬虫:
- Scrapy
其他：
...
- MySQL介绍
MySQL：是用于管理文件的一个软件
- 服务端软件
- socket服务端
- 本地文件操作zz
- 解析指令【SQL语句】
- 客户端软件（各种各样）
- socket客户端
- 发送指令
- 解析指令【SQL语句】
- DBMS数据库管理系统
- SQL语句
其他类似软件：
关系型数据库：sqllite,db2,oracle,access,sql server,MySQL
非关系型数据库：MongoDB,redis
1. MySQL安装
Windows压缩包:
- 下载
MySQL Community Server 5.7.16
http://dev.mysql.com/downloads/mysql/
- 解压
让MySQL安装在指定目录，将解压后的文件夹移动到指定目录
如：C:\mysql-5.7.16-winx64
- 初始化
MySQL解压后的 bin 目录下有一大堆的可执行文件，执行如下命令初始化数据：
cd c:\mysql-5.7.16-winx64\bin
mysqld --initialize-insecure
# Run as administrator
- 启动MySQL服务
执行命令从而启动MySQL服务
# 进入可执行文件目录
cd c:\mysql-5.7.16-winx64\bin
# 启动MySQL服务
mysqld
- 启动MySQL客户端并连接MySQL服务
由于初始化时使用的【mysqld --initialize-insecure】命令，其默认未给root账户设置密码
# 进入可执行文件目录
cd c:\mysql-5.7.16-winx64\bin
# 连接MySQL服务器
mysql -u root -p
# 提示请输入密码，直接回车
- 到此为止，MySQL服务端已经安装成功并且客户端已经可以连接上
- 以后再操作MySQL时，只需要重复上述4、5步骤即可
- 但是，在4、5步骤中重复的进入可执行文件目录比较繁琐，如想日后操作简便，可以做如下操作。
a. 添加环境变量
将MySQL可执行文件添加到环境变量中，从而执行执行命令即可
【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】
【在第二个内容框中找到 变量名为Path 的一行，双击】
【将MySQL的bin目录路径追加到变值值中，用 ； 分割】
# ;C:\Python35;C:\mysql-5.7.16-winx64\bin
如此一来，以后再启动服务并连接时，仅需：
# 启动MySQL服务，在终端输入
mysqld
# 连接MySQL服务，在终端输入：
mysql -u root -p
b. 将MySQL服务制作成windows服务
上一步解决了一些问题，但不够彻底，因为在执行【mysqd】启动MySQL服务器时，当前终端会被hang住
那么做一下设置即可解决此问题：
# 制作MySQL的Windows服务，在终端执行此命令：
"c:\mysql-5.7.16-winx64\bin\mysqld" --install
# 移除MySQL的Windows服务，在终端执行此命令：
"c:\mysql-5.7.16-winx64\bin\mysqld" --remove
注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令：
# 启动MySQL服务
net start mysql
# 关闭MySQL服务
net stop mysql
2. 用户授权
文件夹【数据库】
文件【表】
数据行【行】
数据行
默认：用户root
mysql -u root -p
show databases;
use 数据库名称;
show tables;
创建用户：
create user 'alex'@'192.168.1.1' identified by '123123';
create user 'alex'@'192.168.1.%' identified by '123123';
create user 'alex'@'%' identified by '123123';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
授权：
权限、人
grant select,insert,update on db1.t1 to 'alex'@'%';
grant all privileges on db1.t1 to 'alex'@'%';
grant all privileges on db1.* to 'alex'@'%';
# 除grant外的所有权限
revoke all privileges on db1.t1 from 'alex'@'%';
show grants for '用户'@'IP地址' -- 查看权限
grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
- DBA提供用户名密码
- 数据库操作
create database db2;
create database db2 default charset utf8; *****
show databases;
drop database db2;
- 数据表操作
show tables;
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id int,name char(10)) engine=innodb default charset=utf8;
create table t3(id int auto_increment primary key,name char(10))engine=innodb default charset=utf8; *****
create table t1(
列名 类型 null,
列名 类型 not null default 1,
列名 类型 not null auto_increment primary key,
id int,
name char(10)
)engine=innodb default charset=utf8;
# innodb 支持事务（回滚），原子性操作
# myisam 全局索引、速度更快
auto_increment 自增,只能有一个
primary key 约束(不能重复且不能为空); 加速查找，只能有一个。
not null 是否为空
清空表：
delete from t1; 自增ID延续清空前
truncate table t1; Id也清零
删除表：
drop table t1;
- 数据类型：
数字：
tinyint 有符号：-128~127 ；无符号：0~255
int 0~4294967295
bigint
# signed 有符号
# unsigned 无符号
FLOAT 不太精准的小数
0.00000100000123000123001230123
DOUBLE 不太精准的小数
0.00000000000000000000100000123000123001230123
0.00000100000123000000000000000
decimal 精准小数
0.1
字符串：创建时需设定长度
char(10) 速度快()
root 自动填充空位
varchar(10) 节省空间，最多255字符
root
PS: 创建数据表定长列放前面
text
上传文件：
文件存硬盘
db存路径
时间类型
DATETIME
枚举类型：
enum
集合类型：
set
示例：
create table t1(
id int signed not null auto_increment primary key,
num decimal(10,5), # 总位数，小数点后位数
name char(10)
)engine=innodb default charset=utf8;
- 自增：
desc t10;
show create table t10; # 查看表
show create table t10 \G; # 查看表换样式
alter table t10 AUTO_INCREMENT=20; # 设置自增
MySQL: 自增步长
基于会话级别：
show session variables like 'auto_inc%'; 查看全局变量
set session auto_increment_increment=2; 设置会话步长
# set session auto_increment_offset=10; 设置起始值
基于全局级别：
show global variables like 'auto_inc%'; 查看全局变量
set global auto_increment_increment=2; 设置会话步长
# set global auto_increment_offset=10;
SqlServer：自增步长：
基础表级别：
CREATE TABLE `t5` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
CREATE TABLE `t6` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8
- 唯一索引
create table t1(
id int ....,
num int,
xx int,
unique 唯一索引名称 (列名,列名),
constraint ....
)
# 约束不能重复（可以为空）
PS: 主键不能重复（不能为空）
# 加速查找
# 联合唯一索引
- 外键：
create table userinfo(
uid int auto_increment primary key,
name varchar(32),
department_id int,
xx_id int,
constraint fk_user_depar foreign key (department_id) references department(id),
constraint fk_user_xx foreign key (xx_id) references xx(id)
)engine=innodb default charset=utf8;
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;
innodb原子操作
- 主键外键
# 一个表只能有一个主键
# 主键可以由多列组成
# 两列主键
CREATE TABLE t5 (
nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) not NULL,
num int(11),
primary key(nid,pid)
# 两列主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t6(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
)engine=innodb default charset=utf8;
- 外键的变种
a. 用户表和部门表
用户：
1 alex 1
2 root 1
3 egon 2
4 laoyao 3
部门:
1 服务
2 保安
3 公关
====> 一对多
b. 用户表和博客表
用户表：
1 alex
2 root
3 egon
4 laoyao
博客表：
FK() + 唯一
1 /yuanchenqi/ 4
2 /alex3714/ 1
3 /asdfasdf/ 3
4 /ffffffff/ 2
# 用户表
ID username usertype
# admin FK+唯一
ID user_id password
# 仅管理员设置密码登陆时 FK+唯一
====> 一对一
create table userinfo1(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;
create table admin(
id int not null auto_increment primary key,
username varchar(64) not null,
password VARCHAR(64) not null,
user_id int not null,
unique uq_u1 (user_id),
CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;
c. 用户表（百合网） 相亲记录表
示例1：
用户表
相亲表
示例2：
用户表
主机表
用户主机关系表
# 联合唯一
====>多对多
create table userinfo2(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;
create table host(
id int auto_increment primary key,
hostname char(64)
)engine=innodb default charset=utf8;
create table user2host(
id int auto_increment primary key,
userid int not null,
hostid int not null,
unique uq_user_host (userid,hostid),
CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;
- 数据行操作
create table tb12(
id int auto_increment primary key,
name varchar(32),
age int
)engine=innodb default charset=utf8;
- 增
insert into tb11(name,age) values('alex',12);
insert into tb11(name,age) values('alex',12),('root',18);
insert into tb12(name,age) select name,age from tb11;
- 删
delete from tb12;
delete from tb12 where id !=2
delete from tb12 where id =2
delete from tb12 where id > 2
delete from tb12 where id >=2
delete from tb12 where id >=2 or name='alex'
delete from tb12 where id >=2 and name='alex'
- 改
update tb12 set name='alex' where id>12 and name='xx'
update tb12 set name='alex',age=19 where id>12 and name='xx'
- 查
select * from tb12;
select id,name from tb12;
select id,name from tb12 where id > 10 or name ='xxx';
select id,name as cname from tb12 where id > 10 or name ='xxx';
select name,age,11 from tb12;
# 多加一列全是11
其他：
select * from tb12 where id != 1
select * from tb12 where id <> 1
select * from tb12 where id in (1,5,12);
select * from tb12 where id not in (1,5,12);
select * from tb12 where id in (select id from tb11) # id处只能一列
select * from tb12 where id between 5 and 12; # 两边都取
通配符：
select * from tb12 where name like "a%" # a后面n个
select * from tb12 where name like "a_" # 共两位，a开头
分页：
select * from tb12 limit 10; # 前十条
select * from tb12 limit 0,10; # 0开始取10条
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20;
# 从第20行开始读取，读取10行；
结合Python分页：
# page = input('请输入要查看的页码')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2
排序：
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc;
取后10条数据
select * from tb12 order by id desc limit 10;
分组：（聚合函数）
select count(id),max(id),part_id from userinfo5 group by part_id;
count
max
min
sum
avg
select count(id) from userinfo5;
**** 如果对于聚合函数结果进行二次筛选时 必须使用having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
连表操作：
select * from userinfo5,department5
select * from userinfo5,department5 where userinfo5.part_id = department5.id
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
# userinfo5左边全部显示
# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5右边全部显示
select * from userinfo5 inner join department5 on userinfo5.part_id = department5.id
将出现null时一行隐藏
select * from
department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id
select
score.sid,
student.sid
from
score
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
left join class on student.class_id = class.cid
left join teacher on course.teacher_id=teacher.tid
连表操作2：
左右连表： join
上下连表： union
# 自动去重
select id,name from tb1
union
select num,sname from tb2
# 不去重
select sid,sname from student
UNION ALL
select sid,sname from student
- 临时表
SELECT num,course_id FROM(SELECT num,course_id from score WHERE num >60) as B;
select * from a as a1,a as a2;
select * from a as a1,a a2;
select
id,
name,
1,
(select count(1) from tb)
from tb2
SELECT
student_id,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
from score as s1;
- 指定映射：
select id,name,1,sum(x)/count()
- 条件：
case when id>8 then xx else oo end
select
course_id,
max(num),
min(num),
min(num)+1,
case when min(num) <10 THEN 0 ELSE min(num) END as c
from score GROUP BY course_id
select
course_id,
avg(num),
sum(case when num <60 THEN 0 ELSE 1 END),
sum(1),
sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl
from
score
GROUP BY
course_id order by AVG(num) asc,jgl desc;
- 三元运算：
if(isnull(xx),0,1)
agg(if(isnull(score.num),0,score.num))
- 其他：
- navicat
创建表
新建查询
转储SQL文件
- 命令行备份：
mysqldump -u root db1 > db1.sql -p
# 数据表结构+ 数据
mysqldump -u root -d db1 > db1.sql -p
# 数据表结构
- 命令行恢复：
create database db5;
mysql -u root -d db1 < db1.sql -p
