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 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 数据库与文件夹对应关系
2.2 数据库操作
create database t1 default charset utf8;
2.3 表操作
创建表格式: not null null 可以省略 默认not null
)engine=innodb default charset=utf8;
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id,int,name char(10))engine=innodb default charset=utf8;
# primary key 主键 表约束(不能为空,不能重复),快速查找,
create table t1(id int auto_increment primary key,name char(10))engine=innodb default charset=utf8;
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;
update t1 set age=18 where age=17;
delete from t1 where id<6;
2.5 其他操作
show create table class \G; 改成一列查看
3. like
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;
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;
8.连表操作
# 表只要连上,任意都可以使用拿数据,比如5张表连在一起,可以任意两张有关系的表,拿数据
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 Servehttps://www.cnblogs.com/wupeiqi/articles/5713315.html
9.2 类型
数字(无符号:unsigned 有符号:signed)
decimal(精度最高,实际上存储的是char类型)
decimal(10,5) 总共10位,小数点后占5位
9.3 int有(signed)/无符号(unsigned)创建表示例
id int(32) signed not null auto_increment primary key,
id int(32) unsigned not null auto_increment primary key,
9.4 enum/set示例
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
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+外键
一对多/多对一
正常的外键
多对多
外键的创建方式
创建单个/多个外键
constraint fk_usrer_depar foreign key ("department_id") references deparment('id')
constraint(关键字) fk_usrer_depar(起的名字) foreign key(关键字) ("department_id")(自己的列) references(关键字) deparment('id')(关联的列)
uid bigint auto_increment primary key,
constraint fk_usrer_depar foreign key (department_id) references deparment(id)
id bigint auto_increment primary key,
uid bigint auto_increment primary key,
constraint fk_usrer_depar foreign key (department_id) references deparment(id),
constraint fk_xx_depar foreign key (xx_id) references xx(id)
id bigint auto_increment primary key,
id bigint auto_increment primary key,
联合主键
id int(11) not null auto_increment primary key,
pid int(11) defalut null,
id int(11) not null auto_increment,
pid int(11) defalut null,
nid int(11) not null auto_increment,
name char(10) defalut null,
constraint fk_t1_t2 foreign key t2(id1,id2) references t1(id,pid)
唯一索引(unique)
id int(11) not null auto_increment,
pid int(11) defalut null,
ps主键与索引
外键主键unique联合示例
id int(11) not null auto_increment primary key,
pid int(11) defalut null,
nid int(11) not null auto_increment primary key,
name char(10) defalut null,
constraint fk_t1_t2 foreign key t2(user_id) references t1(id)
联合唯一索引外键
id int(11) not null auto_increment primary key,
id int(11) not null auto_increment primary key,
nid int(11) not null auto_increment primary key,
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),
创建表作业
cid int auto_increment primary key,
sid int auto_increment primary key,
constraint fk_student_class foreign key (class_id) references class(cid)
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);
tid int auto_increment primary key,
cid int auto_increment primary key,
constraint fk_course_teacher foreign key (teacher_id) references teacher(tid)
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);
********************************************
sid int auto_increment primary key,
constraint fk_score_student foreign key (student_id) references student(sid),
constraint fk_score_course foreign key (course_id) references course(cid)
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 score where number >60) as B
student on B.student_id = student.sid;