1. 用户相关
1.1 创建用户
# 创建一个用户，用户名是xx在什么主机上可以登录，密码是什么
# 格式
create user '用户名'@'主机' identified by '密码';
create user 'llp'@'192.168.1.1' identified by '0528';
cerate user 'llp'@'192.168.1.%' identified by '0258';
# 在任意主机可以登录 % 代表任意
create user 'llp'@'%' identified by '0528';
1.2 授予权限
grant 权限 在哪个数据库.表 给谁
grant select,insert,update on db1.t1 to 'llp'@'%';
grant all privileges on db1.* to 'llp'@'%';
# 所有权限+任意库表 * 任意
grant all privileges on *.* to 'llp'@'%';
# 移除权限
revoke all privileges from db1.t1 to 'llp'@'%';
2. 数据库的操作
2.1 数据库与文件夹对应关系
mysql-----excel
数据库 ---- 文件夹
表 -------- 文件
数据 ------- 数据
2.2 数据库操作
# 新建
create database t1 default charset utf8;
# 查看库
show databases;
# 删除库
drop database t1;
2.3 表操作
# 查
show tables;
创建表格式： not null null 可以省略 默认not null
create table t1(
列名 类型 null,
列名 类型 not null,
)engine=innodb default charset=utf8;
# 引擎
innodb 支持事务 原子性
myisam 速度快
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id,int,name char(10))engine=innodb default charset=utf8;
# auto_increment 自增
# primary key 主键 表约束(不能为空，不能重复)，快速查找，
create table t1(id int auto_increment primary key,name char(10))engine=innodb default charset=utf8;
# 清空表
# 新增的数据接着之前的
delete from t1;
# 新增的数据，从头开始，这个速度快
truncate table t1
2.4 数据操作
# 插入书
# 单个插入
insert into t1(id,name) values(1,'liuyaping');
# 多个插入
insert into t1(id,name) values(1,'liuyaping'),(2,'xxx'),(3,'aaa');
# 从另一张表导入进来
insert into t1(id,name) select id,name from tb2;
# 查看
select id,name from t1;
select * from t1;
# 修改
update t1 set age=18;
update t1 set age=18 where age=17;
# 删除
delete from t1 where id<6;
and
or
>=
<=
!=
not in (1,2,3)
in (1,2,3)
in (select id from t2)
between 5 and 7
2.5 其他操作
# desc 表名 -----------查看表结构
desc class
# show create table 表名 -----------查看创建表的完整sql语句
show create table class;
show create table class \G; 改成一列查看
3. like
like: 匹配
%:匹配任意多个，
a%:以a开头
%a:以a结尾
_：匹配一个
a_:只能匹配一个 列如 ab
列子
select id,name from t1 where id like "3%";
select id,name from t1 where id like "3_";
4. limit(取多少)
# 取两条
select * from class limit 2;
# 从0开始取，闭区间
select * from class limit 0,3;
5. offset(从什么地方取)
select * from class limit 1 offset 2;
6. 排序
select * from t1 order by id desc; 大到小
select * from t1 order by id asc; 小到大(默认)
例子：
取后十条数据
select * from t1 order by id desc limit 10;
7. 分组
# 坑：分完组之后不能用where 只能用having
求每一个部门最大的
select max(id) from t1 group by part_id;
count(id)
min
sum
avg
8.连表操作
# 表只要连上，任意都可以使用拿数据，比如5张表连在一起，可以任意两张有关系的表，拿数据
# 保证左表完整，对于左表没有，右表有的不显示
left join
# 保证右表完整，对于右表没有，右表有的不显示
right join
# 将出现一行的null隐藏
inner join
例子：
select t1.id,t2.id from t1 left join t2 on t1.part_id = t2.id;
9.数据类型
9.1 更多
Python开发【第十七篇】：MySQL（一） - 武沛齐 - 博客园一、概述 1、什么是数据库 ？ 答：数据的仓库，如：在ATM的示例中我们创建了一个 db 目录，称其为数据库 2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Serve
https://www.cnblogs.com/wupeiqi/articles/5713315.html
9.2 类型
字符串
char(10) 定长 查询速度快
varchar(10) 不定长 节省空间
数字(无符号：unsigned 有符号：signed)
tinyint
int
bigint
float
double
decimal(精度最高，实际上存储的是char类型)
decimal(10,5) 总共10位，小数点后占5位
时间
text
enum 枚举
set 集合
9.3 int有(signed)/无符号(unsigned)创建表示例
有符号创建表示例
create table t1(
id int(32) signed not null auto_increment primary key,
name char(32)
)engine=innodb;
无符号创建表示例
create table t1(
id int(32) unsigned not null auto_increment primary key,
name char(32)
)engine=innodb;
9.4 enum/set示例
enum
枚举类型，
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
# 插入数据示例
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合类型
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
10. 外键关系及创建
外键的种类
一对一
# 本质就是unique+外键
一对多/多对一
正常的外键
多对多
多个外键
两种形式(具体按照业务需求)
两个外键加unique
多个外键不见unique
外键的创建方式
创建单个/多个外键
# 格式
constraint fk_usrer_depar foreign key ("department_id") references deparment('id')
constraint(关键字) fk_usrer_depar(起的名字) foreign key(关键字) ("department_id")(自己的列) references(关键字) deparment('id')(关联的列)
# 创建单个外键
create table userinfo(
uid bigint auto_increment primary key,
name varchar(32),
department_id int,
# 外键
constraint fk_usrer_depar foreign key (department_id) references deparment(id)
)engine=innodb;
create table deparment(
id bigint auto_increment primary key,
title char(32)
)engine=innodb;
# 创建多个外键
create table userinfo(
uid bigint auto_increment primary key,
name varchar(32),
department_id int,
xx_id int,
constraint fk_usrer_depar foreign key (department_id) references deparment(id),
constraint fk_xx_depar foreign key (xx_id) references xx(id)
)engine=innodb;
create table deparment(
id bigint auto_increment primary key,
title char(32)
)engine=innodb;
create table xx(
id bigint auto_increment primary key,
title char(32)
)engine=innodb;
联合主键
# 正常主键t1表
create table t1(
id int(11) not null auto_increment primary key,
pid int(11) defalut null,
num int(11) defalut null
)engine=innodb;
# 联合主键t1表
create table t1(
id int(11) not null auto_increment,
pid int(11) defalut null,
num int(11) defalut null
primary key(id,pid)
)engine=innodb;
# 两个表都是联合唯一
create table t2(
nid int(11) not null auto_increment,
name char(10) defalut null,
id1 int,
id2 int,
primary key(id1,id2)
constraint fk_t1_t2 foreign key t2(id1,id2) references t1(id,pid)
)engine=innodb;
唯一索引(unique)
create table t1(
id int(11) not null auto_increment,
pid int(11) defalut null,
unique uq1 (id,pid)
)engine=innodb;
ps主键与索引
约束不能重复且可以为空-------索引
约束不能重复且不能为空------主键
都是为了加速寻找
外键主键unique联合示例
可以有多个外键unique
只能有一个主键
create table t1(
id int(11) not null auto_increment primary key,
pid int(11) defalut null,
num int(11) defalut null
primary key(id,pid),
)engine=innodb;
create table t2(
nid int(11) not null auto_increment primary key,
name char(10) defalut null,
user_id int(10),
unique uq_q1(user_id)
constraint fk_t1_t2 foreign key t2(user_id) references t1(id)
)engine=innodb;
联合唯一索引外键
# t1表
create table t1(
id int(11) not null auto_increment primary key,
name char(32)
)engine=innodb;
# t2表
create table t2(
id int(11) not null auto_increment primary key,
host char(32)
)engine=innodb;
# t3表(外键)
create table t3(
nid int(11) not null auto_increment primary key,
t1_id int(32) unsigned,
t2_id int(32) signed,
unique t1_t2_id(t1_id,t2_id)
constraint fk_t1_t2 foreign key t3(t1_id) references t1(id),
constraint fk_t1_t2 foreign key t3(t2_id) references t2(id),
)engine=innodb;
创建表作业
# 班级 学生表 创建
# 班级表创建
create table class(
cid int auto_increment primary key,
caption char(32)
)engine=innodb;
# 学生表创建
create table student(
sid int auto_increment primary key,
sname char(32),
gender char(32),
class_id int,
constraint fk_student_class foreign key (class_id) references class(cid)
)engine=innodb;
# 插入班级数据
insert into class(caption) value("三年级二班");
insert into class(caption) value("一年级二班");
insert into class(caption) value("三年级一班");
# 插入学生数据
insert into student(sname,gender,class_id) value("钢弹","女",1);
insert into student(sname,gender,class_id) value("铁锤","女",1);
insert into student(sname,gender,class_id) value("三炮","男",2);
**********************
# 老师---课程
# 老师创建
create table teacher(
tid int auto_increment primary key,
tname char(32)
)engine=innodb;
# 课程创建
create table course(
cid int auto_increment primary key,
cname char(32),
teacher_id int,
constraint fk_course_teacher foreign key (teacher_id) references teacher(tid)
)engine=innodb;
# 老师插入数据
insert into teacher(tname) value("剥夺");
insert into teacher(tname) value("苍空");
insert into teacher(tname) value("反到");
# 课程插入
insert into course(cname,teacher_id) value("生物",1);
insert into course(cname,teacher_id) value("体育",1);
insert into course(cname,teacher_id) value("物理",2);
********************************************
# 成绩*************
create table score(
sid int auto_increment primary key,
student_id int,
course_id int,
number int,
constraint fk_score_student foreign key (student_id) references student(sid),
constraint fk_score_course foreign key (course_id) references course(cid)
)engine=innodb;
# 插入数据
insert into score(student_id,course_id,number) value(1,1,60);
insert into score(student_id,course_id,number) value(1,2,59);
insert into score(student_id,course_id,number) value(2,2,100);
自增
步长修改
alter table class auto_increment=20 修改步长
auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
基于会话级别(当前窗口有效)
# 查看当前窗口的步长
show session variables like 'auto_incre%';
# 会话步长修改
set session auto_increment_increment=2;
set session auto_increment_offset=10;
基于全局变量
show global variables like 'auto_incre%';
# 会话步长修改
set global auto_increment_increment=2;
set global auto_increment_offset=10;
临时表
select * from
(select * from score where number >60) as B
left join
student on B.student_id = student.sid;