MySQL --- SQL 语句
DDL 数据定义语言
库
数据库属性
engine=innodb
default charset=utf8
---增
create database xxx charset utf8;
规范:
1.库名:小写,与业务有关,不要数字开头,不要太长,关键字不能使用
2.必须指定字符集
---查
show databases;
show create database xxx;
---改
alter database xxx charset utf8mb4;
规范:
1.从小改大
---删
drop database 库名;
表
数据库字段属性
unique
-- 唯一约束
unsigned
-- 无符号整数,该列不能为负数
zerofill
-- 不足的位数用0填充 ,int(3) 5 --> 005
auto_increment
-- 自增,一般用于设置主键
null not null
null --表示可以为空
not null --表示不可以为空
default
-- 默认值
comment
-- 注释
primary key
-- 设置主键列
primary key('id')
增
---增
create table wp_user(id int not null auto_increment unique comment '用户序号',sname varchar(64) not null comment '用户名',age tinyint unsigned not null default 18 comment '年龄'
)engine=innodb charset=utf8mb4;
规范:
1.库名:小写(多平台兼容性问题,比如win10不区分大小写),与业务有关,不要数字开头,不要太长,关键字不能使用
2.必须指定存储引擎和字符集
3.数据类型:合适,简短,足够
4.必须有主键
5.每个列尽量设置not null 不知道填什么设置默认值
6.每个列要有注释
7.列名不要太长
查
show tables;
show create database student;
show create table xxx01;
desc xxx01;
改
常用操作
-- 修改表名
-- alter table 旧表名 rename as 新表名
-- 添加新列 column
-- alter table 表明 add column 列名 列约束
添加手机号列
alter table xxx01 add column phone_num bigint not null unique key comment '手机号';
-- 添加新列 column,并且外键约束
alter table xxx01 add constraint FK_xxx02_id foreign key xxx02_id references xxx02 xxx02_id
-- 修改列约束 modify 无法修改列名
-- alter table 表名 modify 列名 列约束
alter table xxx01 modify phone_num char(11) not null unique key comment '手机号';
-- 既可以修改列约束也可以列重命名change
-- alter table 表名 change 旧列名 新列名 列约束
alter table xxx01 change phone_num char(11) not null unique key comment '手机号';
-- 删除某列 drop
-- alter tabel 表明 drop 列名
删除手机号列
alter table xxx01 drop phone_num
设置自增
alter table student change id id int(11)not null auto_increment;
修改主键
-- 添加主键
alter table student add primary key(id);
-- 如果有主键,但是想修改主键
-- 先删除主键约束
alter table student drop primary key;
alter table student add columen id integer not null primary key auto_increment;
删表
drop table xxx01;
清空表
truncate 表名
线上DDL(alter)对生产环境的影响
SQL审核平台:yearing,inception
说明:在MySQL中,DDL语句在对表进行操作时,需要锁元数据表,此时所有修改类的命令无法正常运行
在对于大表,业务繁忙的表,进行线上DDL操作时,需要谨慎,所以避开业务繁忙时进行DDL
DCL 数据控制语言
1.用户管理
1.用户的作用
1.登录mysql数据库
2.管理mysql对象
2.用户的定义
用户名@'白名单'
白名单:地址列表,允许白名单的IP登录MySQL,管理MySQL
xxx@'localhost' -------- xxx用户能够通过本地socket登录MySQL
xxx@'10.0.0.10' -------- xxx用户能够通过10.0.0.10远程登录MySQL服务器
xxx@'10.0.0.%' -----------xxx用户能够通过10.0.0.10/24远程登录MySQL服务器
xxx@'10.0.0.5%' ----50-59
xxx@'10.0.0.0/255.255.254.0'
3.用户的管理
查
-- 查之前
desc user
select user,host,authentication_string from mysql.user;
### root@'localhost'
增
-- 创建用户,默认空密码
create user xxx@'localhost'
-- 同时创建密码
create user xxx01@'10.0.0.%' identified by '123'
改
-- 修改密码
alter user xxx@'localhost' identified by '456'
-- 改用户密码
set password for 用户名 = password(密码)
-- 修改用户名
rename user 旧用户名 to 新用户名
删
drop user xxx@'10.0.0.%'
2.用户权限管理
1.权限作用
用户对数据库对象,有哪些管理能力
2.权限的表现方式
CRUD 具体命令
3.查询可以设置的权限列表
show privileges
4.授权权限
语法:
8.0-
grant 权限1,2,3,4 on 对象 to 用户 identified by '密码';
ALL ---- 管理员
Grant option ----- 给别的用户授权
--注意 8.0版本以前,可以通过grant命令 建立用户+授权,8.0先创建用户再授权
grant 权限1,2,3,4 on 对象 to 用户 identified by '密码' with Grant option;
对象: 库,表
*.* --- 全部 --- 管理员
xxx.* --- xxx库下的所有表 ---普通用户
xxx.t1 --- xxx库下的t1表
# 创建并授权管理员用户,能够荣国10.0.0.% 网段登录mysql
grant all on *.* to xxx@'10.0.0.%' identified by '122' with grant option;
# 查询用户权限
show grants for xxx@'10.0.0.%';
# 查询用户权限具体权限
select * from mysql.user\G;
5.回收权限
mysql中不能通过重复授权修改权限,只能通过回收权限,再重新授权
-- revoke 权限名 on 库名@用户
revoke create on app@'10.0.0.%';
6.mysql的表权限级别
user --- *.*
db ---app.*
tables_priv ---app.t1
columns_priv ---列
orcole中删除用户表会删除,mysql中 权限没了 表不会
DML 数据操作语言
增
1.查询表结构
desc student;
2.增加行数据
insert into student(id,sname,age,xingbie,cometime) values (1,'张珊',18,0,'2020-01-11 11:22:00')
--简约方法,字段从头到尾一一对应
insert into student values(2,'张三',18,1,'2020-01-11 11:22:00')
--批量录入
insert into student values('张闪'),('张闪1'),('张闪2')
改
--不加where语句会修改所有的值,必须加where条件
update student set name='xx' where id=4;
删
--不加where语句会清空表
delete from student where id=3;
-- 清空表
truncate table 表名
-- 区别:
truncate 会重置自增列,计数器会重新从开始,不会影响事务
innodb在delect清空玩表中的所有数据时,重启数据库,自增列会从1开始(自增量存储在内存中)
myisam则不会,因为自增量存储在文件中
DQL 数据查询语言(属于DML)
1.单表查询
1.1select 单独使用(Mysql独家)
1.1.1 配合函数使用
select now() --当前时间
select database() --当前库名字
select concat(user,'@',host) from mysql.user; --用@拼接
select user()
select version()
1.1.2查询数据库的参数
select @@port
select @@datadir
select @@socket
执行顺序
-- 默认执行顺序
1.select from
2.where
3.group by
3.5 select_list ===列名
4.having
5.order by
6.limit
select from
--查询表中的所有数据
select * from world.city;
---查询部分列
select name,population from world.city;
select+where
配合比较运算符 =,<,>,<=,>=,!=
查询city表中中国所有的城市信息
select * from city where countrycode='CHN';
查询city表中人数不到1000人的城市
select countrycode from city where population<1000;
where+like模糊查询
查询city中国家代号是CH开头的城市所有信息
-- 为空
is null
-- 不为空
is not null
-- in
in (1,2,3)
-- like
-- 以CH开头,不限制字符个数
select * from city where countrycode like 'CH%'
--以CH开头,并只有一个字符
select * from city where countrycode like 'CH_'
---切记不要出现前面代%的模糊查询,不走索引
where+逻辑连接符
and or
查询中国城市中人口大于500w的城市所有信息
select * from city where countrycode='CHN' and population>5000000;
查询中国或美国的城市信息
select * from city where countrycode='CHN' or countrycode='USA';
查询中国和美国,并且人口大于500w的城市
select * from city where countrycode in ('CHN' ,'USA') and population>5000000;
where+between and
查询城市人口数在100w到200w之间(包含)的
select * from city where population between 1000000 and 2000000;
select + group by
group by 配合聚合函数使用
max(), min(), avg() , count(), sum(), group_concat()
最大值 最小值 平均值 统计个数 求和 列转行
统计各个省份有多少个学生
select addr,count(id) from student group by addr;
统计男生女生各有多少人
select gender,count(id) from student group by gender;
统计city表中每个国家的城市个数
select countrycode,count(id) from city group by countrycode;
统计中国每个省的城市个数
select district,count(id) from city where countrycode='CHN' group by district;
统计每个国家的总人口
select population from city group by countrycode;
统计中国每个省的总人口
select district,sum(population) from city where countrycode='CHN' group by district;
统计中国每个省总人口,城市个数 城市名称
select district,sum(population),count(id) group_concat(name) from city group by district;
----对于不能合并的多行,整合成一行显示
select+having后过滤
在group by +聚合函数后 再过滤
统计中国每个省的总人口,只显示总人口数大于500w
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 ;
select + order by
统计中国每个省的总人口,只显示总人口数大于500w,并按从大到小排序
-- ASC 升序, DESC 降序
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc;
select + limit
分页显示结果集
统计中国每个省的总人口,只显示总人口数大于500w,并按从大到小排序,只取前五个
-- limit 取几个
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5;
统计中国每个省的总人口,只显示总人口数大于500w,并按从大到小排序,取8-10
-- limit 起始位置(下标),取几个
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 7,3;
-- limit 取几个 offset 起始位置(下标)
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 3 offset 7;
distinct 去重复
select distinct * from student
union和union all
查询中国和美国的城市信息
select * from world.city where countrycode='CHN' and countrycode='USA';
select * from world.city where countrycode in ('CHN','USA');
select * from world.city where countrycode='CHN'
union all
select * from world.city where countrycode='USA';
case + when
-- case when 判断条件 then 结果 end
select case when sc.score>90 then sc.score end from sc;
-- 统计各位老师,所教课程的及格率**
select
concat(teacher.tname,'.',teacher.tno),concat(count(case when sc.score>60 then 1 end)/count(sc.sno)*100,'%' )as 及格率
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.no,teacher.name
子查询(效率要高些,因为不要连表)
-- 括号里的查询作为条件
select district from city where countrycode=(
select countrycode from city where name = (
select name from xxx where xxx=xxx
))
2.多表连接查询
查询类型
笛卡尔乘积
多行相乘,交叉连接
表1 join 表2
内连接(inner join)
next loop算法
循环 尽量使用少量数据的表作为外循环,去循环多量数据的表
找到两张表的关联关系(取两张表的交集)
select * from teacher inner join course on teacher.tnu=course.tnu;
外连接(left join /right join)
左外链接
会查出左表中所有的数据,每一行数据如果满足条件则拼接右表结果,如果不满足条件则以null 填充
select * from teacher left join course on teacher.tnu=course.tnu
- 查询一下世界上人口数量小于100人的城市名和国家名
SELECT b.name ,a.name ,a.population
FROM city AS a
JOIN country AS b
ON b.code=a.countrycode
WHERE a.Population<100
- 查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）
SELECT a.name,a.population,b.name ,b.SurfaceArea
FROM city AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';
右外链接
会查出右表中所有的数据,每一行数据如果满足条件则拼接右表结果,如果不满足条件则以null 填充
自连接
select a.name as '父级',b.name as '子级' from title as a,title as b where a.id = b.pid
驱动表
在多表连接中,充当外循环的角色,此时mysql会拿着驱动表的每个满足on条件的关联列的值,去一次循环内循环中的关联值,进行一一判断和匹配
建议:将结果集小的表设置为驱动表更加合适,可以降低next loop的次数,对于内连接来说,无法控制驱动表是哪一张,完全由优化器决定.如果需要自定义干预,将内连接改成外连接.
系统函数
-- 数值
ABS(-8) --绝对值
CEILING(9.4) --向上取整
FLOOR(9.4) -- 向下取整
RAND() -- 返回0-1之间的随机数
SIGN(8) -- 判断数值的符号,负数返回-1,整数返回1,0返回0
-- 字符串函数
CHAR_LENGTH() -- 字符串长度
CONCAT(str1,str2) -- 字符串拼接
INSERT('你好',0,1,'我') -- 指定位置插入字符串
REPLACE(str,from_strm,to_str) -- 字符串替换
LOWER() -- 变小写
UPPER() --变大写
INSTR() -- 字符串中出现第一个xx字符的下标
substr(str,index,number) -- 返回指定的子字符串
reverse -- 反转
current_date() -- 当前时间
curdate()
now() -- 当前时间
聚合函数
count()
count(字段) -- 会忽略所有的该字段为null的值
count(*) -- 查所有列,也就是计算行数,不会忽略null值
count(1) -- 1代表行,不会忽略null值
-- 区别:列名为主键,count(列名)快,列名不为主键,count(1)快,表中有多列且没有主键,count(1)快,count(主键)最快,表中只有一个字段count(*)最快
sum()
max
min()
avg()
别名
列别名,表别名(全局调用)
SELECT
a.Name AS an ,
b.name AS bn ,
b.SurfaceArea AS bs,
a.Population AS bp
FROM city AS a JOIN country AS b
ON a.CountryCode=b.Code
WHERE a.name ='shenyang';
- 统计zhang3,学习了几门课
SELECT student.sno,st.sname , COUNT(sc.cno)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
WHERE st.sname='zhang3' group by st.sno;
- 查询zhang3,学习的课程名称有哪些?
SELECT st.sname , GROUP_CONCAT(co.cname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
WHERE st.sname='zhang3'
- 查询oldguo老师教的学生名.
SELECT te.tname ,GROUP_CONCAT(st.sname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
JOIN teacher AS te
ON co.tno=te.tno
WHERE te.tname='oldguo';
- 查询oldguo所教课程的平均分数
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
WHERE te.tname='oldguo'
4.1 每位老师所教课程的平均分,并按平均分排序
SELECT te.tname,AVG(sc.score)
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
GROUP BY te.tname
ORDER BY AVG(sc.score) DESC ;
- 查询oldguo所教的不及格的学生姓名
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE te.tname='oldguo' AND sc.score<60;
5.1 查询所有老师所教学生不及格的信息
SELECT te.tname,st.sname,sc.score
FROM teacher AS te
JOIN course AS co
ON te.tno=co.tno
JOIN sc
ON co.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE sc.score<60;
information_schema.tables查询元数据的视图
每次数据库启动时,会自动在内存中生成I_S,生成查询MySQL部分元数据的视图
1.做公司资产统计
DESC information_schema.TABLES
TABLE_SCHEMA ---->表所在库名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行数(不是实时)
AVG_ROW_LENGTH ---->表中行的平均行（字节）
INDEX_LENGTH ---->索引的占用空间大小（字节）
查询整个数据库中所有库和所对应的表信息
SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;
查询业务数据库(系统库除外),所有非innodb表
select table_schema,table_name from information_schema.tables where engine != 'Innodb' and table_schema not in ('sys','performance_schema','information_schema','mysql')
统计所有库下的表个数
SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema
查询所有innodb引擎的表及所在的库
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';
统计world数据库下每张表的磁盘空间占用
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';
统计所有数据库的总的磁盘空间占用
TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH 一张表的字节数
SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"
生成整个数据库下的所有表的单独备份语句
模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;
CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
107张表，都需要执行以下2条语句
ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
转换成sql脚本,但是有安全限制,需要修改配置信息,并重启数据库(restart)
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';
how语句
show variables ---显示所有参数
show variables like '%trx%' --模糊匹配
show databases; #查看所有数据库
show tables; #查看当前库的所有表
SHOW TABLES FROM #查看某个指定库下的表
show create database world #查看建库语句
show create table world.city #查看建表语句
show grants for root@'localhost' #查看用户的权限信息
show charset； #查看字符集
show collation #查看校对规则
show processlist; #查看数据库连接情况
show full processlist; #查看数据库连接详细情况
show index from #表的索引情况
show status #数据库状态查看
SHOW STATUS LIKE '%lock%'; #模糊查询数据库某些状态
SHOW VARIABLES #查看所有配置信息
SHOW variables LIKE '%lock%'; #查看部分配置信息
show engines #查看支持的所有的存储引擎
show engine innodb status\G #查看InnoDB引擎相关的状态信息
show binary logs #列举所有的二进制日志
show master status #查看数据库的日志位置信息
show binlog evnets in #查看二进制日志事件
show slave status \G #查看从库状态
SHOW RELAYLOG EVENTS #查看从库relaylog事件信息
desc (show colums from city) #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html
伪删除
--伪删除
删除id为1的数据行
1.添加状态列
alter table student add state tinyint not null default 0;
MD5加密
-- 数据库函数
md5()
insert into user values(1,'xiaoming',md5('123456'))
数据库导出命令
-- 导出命令 -h 主机ip(localhost表示为本机) 表名/库名
mysqldump -hlocalhost -uroot -p123456 school student >导出路径
sql文件导入
-- 未登录
mysql -uroot -p123456 <导入路径
-- 登录进数据库
source sql文件路径
from django.db import models
class User(models.Model):
"""
用户表
"""
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=18, default="匿名", verbose_name="用户名")
phone = models.CharField(max_length=11, unique=True, verbose_name="手机号")
password = models.CharField(max_length=32, verbose_name="密码")
pk_role = models.IntegerField()
class Role(models.Model):
"""
角色表
"""
role_type_choices = [
(0, "经销商"),
]
id = models.AutoField(primary_key=True)
role_type = models.CharField(max_length=2, choices=role_type_choices, verbose_name="角色类型")
class Corporation(models.Model):
"""
公司表
"""
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=32, unique=True, verbose_name="公司名称")
enterprise_num = models.CharField(max_length=20, unique=True, verbose_name="企业识别号")
pk_user = models.IntegerField()
博客
https://www.cnblogs.com/wang-meng/p/17838563.html
python防脱发技巧
浙公网安备 33010602011771号