MySQL 数据库总结
MySQL 操作
显示数据库:show databases;
操作数据库:use 库名;
显示数据库内所有的表:show tables;
用户操作:
#用户操作语法:
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
修改密码
set password for '用户名'@'IP地址' = Password('新密码');
IP地址有如下几种:
固定IP:192.168.1.3;
某一段IP:192.168.1.%; %代指任意一个数字,也就是说这一个C的地址都可以访问。
都一个范围内:192.168.%.%; 意义同上;
所有IP:%;
举例:
create user 'some'@'192.168.1.88' identified by '123123';
create user 'wan'@'192.168.1.%' identified by '123123';
create user 'zh'@'%' identified by '123456';
用户创建之后,可以在mysql数据库下的user表中查到!(select user,host from user)
创建完用户之后,就应该给创建的用户(人)进行权限设置,省的发生不必要的冲突!
#授权管理操作语法:
show grants for '用户'@'IP地址' -- 查看权限
grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
库操作:
#创建库
- create database 库名 default charset=utf8;
#查看库
- show databases;
#删除库
- drop database 库名;
数据表操作:
#查看所有文件
- show tables;
#清空表
- delete from 表名;
- truncate table 表名;
#删除表
- drop table 表名;
#创建表(重点)
- 创建普通的表
create table 表名 (
列名 类型 是否为空 自增 主键,
列名 类型 是否为空,
列名 类型 是否为空,
唯一 unique 名称(列名)
)engine=innodb default charset=utf8;
类型:数据类型:[unsigned][无符号] [signed][有符号]【数值范围是否有符号】
整数:tinyint int bigint;
小数[(M,D)]【M是数字总个数,D是小数点后个数】
float double【精度低】 decimal【精度准确】
字符串类型:char()【定长】,varchar()【小等于定长】最大字符数255;
text 操作大文件,硬盘存放文件,数据库存放文件的绝对路径(url)
时间类型:datetime (YYYY-MM-DD HH:MM:SS) 结构化事件
枚举:enum 创建表过程中 给某列设置一个取值库(数组形式),设置过程中从表中选取一个,非表中内容会报错!
集合:set 创建表过程中 给某列设置一个取值库(数组形式),设置过程中从表中随机选取多个组合,非表中内容会报错!
是否为空:null; not null;
自增: auto_increment;
对于自增补充:
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; 设置自增长字段初始值
主键索引: primary key; 表示约束(不能重复且不能为空);加速查找
补充:一个表只能有一个主键;
主键可以由多列组成;
primary key(列名1,列名2);由多个列组合成唯一的主键,叫做联合主键
唯一索引:unique
unique 唯一索引名称 (列名) 名称自定义但是不能重复,括号中表示要操作的列
唯一索引:有约束作用但是不能重复(可以为空),也就是说所对应的列的数值是唯一的!也可加速查找!
PS:对于同一列,主键和唯一索引 二者选一!!!
引擎:engine=innodb 事务查找 原子性操作 :在文件处理过程中,若文件受不可控因素导致操作无法完成,会回滚当前操作的文件!
字符编码:default charset=utf8
起始值:
外键:两张创建的表1和表2,这两个表之间通过方法建立起来的某种关系!
举例:表1是员工表,表2是部门表;为表1创建外键,连接表2,对表1的员工添加部门就不用再手动的去添加,而是写入表2与表1建立连接的列值即可!
- 创建有关联的表:
create table 表名 (
列名 类型 是否为空 自增 主键,
列名 类型 是否为空,
列名 类型 是否为空,
unique 名称(列名1,列名2),
constraint fk_外键名 foreign key (列名1,列名2) references 外键表名(列1,列2)
)engine=innodb default charset=utf8;
外键语法:constraint fk_外键名 foreign key (列名1,列名2) references 外键表名(列1,列2)
fk_外键名 自定义,但是不能重复!
可以创建多个外键,外键之间用逗号,分开,注意结束的那一个不写逗号!
外键的变种:
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
===> 一对一
代码实例:
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 tb1(
tid int not null auto_increment primary key;
title varchar(20)
)engine=innodb default charset=utf8;
create table tb2(
id int not null auto_increment primary key,
name char(10),
department_id int,
p_id int,
unique uql (p_id),
constraint fk_tb2_tb1 foreign key (p_id) references tb2(tid)
)engine=innodb default charset=utf8;
数据行操作:注意有个where 判断,规定了操作查找的范围 (与python中的if语法一致)
create table tb12(
id int auto_increment primary key,
name varchar(32),
age int
)engine=innodb default charset=utf8;
增:
insert into tb12(name,age) values("some",18);
insert into tb12(name,age) values("some",18),("body",20);
insert into tb12(name,age) select name,age from tb11; #将表tb11中的内容插入到表tb12中!值一一对应并且每次只能是一个值
删:
truncate table 表名 #清除有自增类型的表,自增起始值重置为0;
delete from tb12; #简单粗暴的删除整个表;
delete from tb12 where id != 2; #删除表中id不为2的信息!
PS:!= 不等于;=等于;<小于;>大于;<=小于等于;>=大于等于;
delete from tb12 where id != 2 and name="body"; #删除表中id不为2,且name是body的信息!
PS:逻辑判断:and 与;or 或;
改:
update tb12 set age=22;
update tb12 set age=22 where id>2;
update tb12 set age=18 where id>2 and name="some";
update tb12 set age=20,name="body" where id>2 and name="some";
查:(方法很多!!!)
select * from tb12; #查看表中所有内容;
select name,age from tb12;# 指定查看的列 推荐使用这种方法查找 *代表全部,查找大数据会影响效率;
select id,name from tb12 where id>1 or name="some"; #指定查找范围
select id,name as cname from tb12 where id>1 or name="some"; #指定查找范围; as 给列重新做别名!(与python中导入模块做别名一个意思)
select id,name,11 from tb12; #给查到的内容之后加上一列,列名和内容都是11;
其他:
select * from tb12 where id != 1 # 在范围内查找
select * from tb12 where id in (1,3); # in 代指是,存在;括号里存放数值
select * from tb12 where id not in (1,3); #not in 不是;
select * from tb12 where id in (select id from tb11) #括号中也可以是根据另一个表的id查找;
select * from tb12 where id between 1 and 2; # 查找1-2之间的所有信息,闭区间能取头尾;
通配符:
select * from tb12 where name like "s%" # % 指任意多个字符!
select * from tb12 where name like "s_" # _ 指任意一个字符!
PS:%a%:指中间是a的任意字符!
分页:#limit 后跟一个值 num1 指查看这么多条数据;两个值 num1,num2 num1 指查找起始位置,num2 指取多少个值;
select * from tb12 limit 10;
select * from tb12 limit 0,10;
select * from tb12 limit 10,10;
select * from tb12 limit 10 offset 20; #offset 同理也是确定开始的初始行 查看多少条数据;
排序:正序 和 倒序 order by 列名 desc(从大到小)/asc(从小到大);
select * from tb12 order by id desc; 根据id值从大到小排序;
select * from tb12 order by id asc; 根据id值从小到大排序;
如果遇到排序列中的数据有重复的,就再选择表中另一个序列进行同类型排序;
比如:通过年龄从大到小排序肯定会遇到同龄的问题,此时再根据id进行排序,做到真正的从大到小排序!
select * from tb12 order by age desc,id desc;
取表格最后10条数据,先将这个表格倒序,然后再去取!
select * from tb12 order by id desc limit 10;
分组:分组就是对某一列的数据 对整个表格的数据进行统计!不同的罗列,相同的存于一组!
语法:select 运算法(其他列名)【可多个,逗号隔开】from 表 group by 列名;
运算法:count 统计;max 最大;min 最小;sum 求和;avg 取平均值
对tb12表操作:
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | some | 18 |
| 2 | some | 18 |
| 3 | body | 20 |
+----+------+------+
根据年龄分组:select count(id),max(id),age from tb12 group by age;
+-----------+---------+------+
| count(id) | max(id) | age |
+-----------+---------+------+
| 2 | 2 | 18 |
| 1 | 3 | 20 |
+-----------+---------+------+
也可对列做别名:select count(id) as num,max(id) as MAX,age from tb12 group by age;
+-----+------+------+
| num | MAX | age |
+-----+------+------+
| 2 | 2 | 18 |
| 1 | 3 | 20 |
+-----+------+------+
**** 如果对于聚合函数结果进行二次筛选时,必须使用having **** having 在此处与where一个意思!
例如 打印 同龄 统计个数大于1的信息:
select count(id) as num,max(id) as MAX,age from tb12 group by age having count(id) >1;
+-----+------+------+
| num | MAX | age |
+-----+------+------+
| 2 | 2 | 18 |
+-----+------+------+
****** 如果想用where 就必须在 group 聚合操作之间先进行判断,若是还需要对结果进行二次筛选,就在之后再加上having进行判断!*****
连表操作: 就是把多个表 以某种条件拼接到一起! !!!注意是对表操作,判断是以表内的数据操作!!!
select * from userinfo5,department5 where userinfo5.part_id = department5.id
左连接和右连接 以 方向 + join拼接,on加判断条件!以连接项为中,全部显示左(右)边的内容,如果表格内容中某项没有,该项信息对应的内容会全部打印为null,
而 inner 是将出现null的这一行隐藏!
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id;
# userinfo5左边全部显示
select * from userinfo5 right join department5 on userinfo5.part_id = department5.id;
# department5右边全部显示
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id;
#将出现null时一行隐藏
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
导出现有数据库数据:
mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构
导入现有数据库数据:
mysqldump -uroot -p密码 数据库名称 < 文件路径
临时表 :把一些临时的数据保存起来,方便操作使用!
两张表连表,不写条件的话,会按照 笛卡尔积 方式,重复显示
去重:distinct 但是效率不高!
两条判断语句:
case when 条件 then 结果 else 结果2 END
三元运算:if(isnull(列名),0,列名)