MySQL常用指令
数据库指令
创建数据库
create database `dbName`;
- 指定字符集和校对规则3
create database `dbName` character set utf8 collate utf8_bin;
查看数据库
- 查看数据库服务器中的所有数据库
show databases;
- 查看创建某个数据库的创建信息
show create database `dbName`;
删除数据库
删除某个数据库
drop database `dbName`;
备份数据库
表指令
创建表
- 字符集、校验类型、引擎可省略，默认跟随数据库属性
create table `user`( `id` int, `name` varchar(255), `password` varchar(255), `birthday` date) charset utf8 collate utf8_bin engine innodb;
修改表配置
修改表名
rename table `table_name` to `new_table_name`；修改字符集
alter table `table_name` character set utf8;修改校验类型
alter table `table_name` collate utf8_general_ci;修改引擎
alter table `table_name` engine innodb修改列
添加列
alter table `table_name` add `new_column_name` varchar(32) not null default '默认内容' after `column_name`;修改列
##修改列名、配置 alter table `table_name` change `column_name` `new_name` varchar(60) not null default '默认内容'; ##修改列配置 alter table `table_name` modify `column_name` varchar(60) not null default '默认内容';删除列
alter table `table_name` drop `column_name`;
删除表
drop table `table_name`;
查看表配置
查看表结构
desc `table_name`;查看创建表的信息
show create table `table_name`;
表的数据CRUD
insert 语句
- 在指定位置插入数据
注：字符和日期数据应包含在单引号中insert into `table_name`(column01,column02...) values(data01,data02...);
- 在所有位置插入数据
注：可以省略插入位置insert into `table_name` values(data01,data02...);
- 添加多行数据
insert into `table_name`(column01,column02...) values(data01,data02...) , (data01,data02...),...;
uprate语句
在列的指定位置修改数据
注：使用where定位哪一行#修改column02 = data02这一行的column01为data01 update `table_name` set column01 = data01 where column02 = data02;在列的所有位置修改数据
注：可以省略where指定哪一行，慎用#修改column01这一列的所有数据为data01 update `table_name` set column01 = data01;修改多列数据
#修改column02 = data02这一行的column01为data01，column03为data03... update `table_name` set column01 = data01 ,column03 = data03 ,... where column02 = data02;
delete语句
删除某一行记录
注：需要where指定哪一行delete from `table_name` where column01 = data01;删除表中的所有记录
注： 省略where指定位置，慎用delete from `table_name`;
select语句
查询表中所有列的信息
注：distinct可以过滤重复信息select [distinct] * from `table_name`;查询表中指定列的信息
select column01,column02... from `table_name`;查询表中某一条数据的信息（可指定列）
#查询column01=data01这一条数据的所有信息 select * from `table_name` where column01 = data01;配合表达式、别名的使用
select column01 as `别名1`,(column02+column03+...) as `别名2` from `table_name`;where语句筛选信息
- 判断：> 、< 、=、<=、>=、!=
#查询column小于100的所有信息 select * from `table_name` where column01 < 100;
- 逻辑：and、or、not
#查询column大于100且小与200； select * from `table_name` where (column01 > 100) and (column01 <200); #查询column小于100或大于200 select * from `table_name` where (column01 > 100) or (column01 <200); #查询column不大于100； select * from `table_name` where not(column01>100);
- 比较：between…and… 、in(set) 、is null、is not null;
#查询column属于[100,200]； select * from `table_name` where column01 between 100 and 200; #查询column在集合{100,200,300}； select * from `table_name` where column01 in(100,200,300); #查询column为空; select * from `table_name` where column01 is null;
- 模糊：like
#模糊查询column=张 开头的； select * from `table_name` where column01 like '张%'; # '%'表示还有0个或多个字符 '_'表示单个字符order by语句排序查询结果
注：asc为升序，desc为降序，不写则默认升序#将查询结果按column01升序 select * from `table_name` order by column01; #将查询结果按column01降序 select * from `table_name` order by column01 desc; #配合表达式、别名和where语句使用排序 select (column01 + column02 +...) as `total` from `table_name` where `total` > 100 order by `total`;##先查再排
函数
统计函数
- count函数
#统计表中所有数据总条数 select count(*) from `table_name`； #统计表中某列非空的总条数 select count(column01) from `table_name`; #统计表中满足某条件的数据总条数 select count(column01 > 100 and column02 < 200) from `table_name`; #可以统计多个count满足各自统计条件且每个count都满足where的数据 select count(column01>100),count(column02 >60) from `table_name` where count03 not null; #配合where语句，在指定范围内(where name = '日奈')统计满足某条件(total_score between 60 and 100)的数据总条数 select count(total_score between 60 and 100) as '日奈历次考试的及格次数' from `table_name` where name = '日奈'; #只有一个count时，也可以写成 select count(*) as '日奈历次考试的及格次数' from `table_name` where (total_score between 60 and 100) and name = '日奈';
- sum函数
#统计表中math列的数据总和、Chinese列的数据总和 select sum(math) as '班级数学总分' ,sum(Chinese) as '班级语文总分' from `table_name`; #统计小明的历次math成绩总和与Chinese成绩总和 select sum(math) as 'math_total_score',sum(Chinese) as 'Chinese_total_score' from `table_name` where name = '小明';
- avg函数
#统计班级数学平均分和总分平均分 select avg(math) as '班级数学平均分',avg(Chinese + math + English)as'班级总分平均分' from `table_name`;
- max、min函数
#统计班级数学最高分和总分最低分 select max(math) as '数学最高分',min(Chinese+math+English) as '总分最低分' from `table_name`;
分组统计
- group by 分组+ having 过滤信息
#显示各年级各班级的最高分 #先按年级分组，再按班级分组，并显示最高分 select max(score),grade,class from student group by grade,class; #显示各年级各班级高于60分的的最高分 #先按年级分组，再按班级分组，显示最高分，最后筛选最高分数高于60的保留显示 select max(score) as score_max ,grade,class from student group by grade,class having score_max > 60;
字符串函数
charset(str) 返回字串字符集
select charset(name) from `table_name`;concat(str1,str2,str3…) 连接字串
select concat(name,' job is ',job) from `table_name`;instr(str,substr) 返回substr在str中出现的位置，没有则返回0
select instr(name,'张') from `table_name`;ucase(str) 转换成大写、lcase(str)转换成小写
select ucase(name) from `table_name`;left(str,len) 从str左边取len个字符
select left(id,3) from `table_name`;length(str) 返回str长度（按字节）
select length(name) from `table_name`;replace(str,search_str,replace_str) 在str中用replace_str 替换 search_str
select replace(name,'tom','汤姆') from `table_name`;strcmp(str1,str2) 逐字比较两字符串大小，注意比较规则是否区分大小写
select strcmp(name,'tom') from `table_name`;substring(str, position [,len]) 从str的position开始（从1计算）取len个字符，不指定len则取到字串尾；
select substring(id,1,5) from `table_name`;ltrim(str) 去除左空格 rtrim(str) 去除右空格 trim(str) 去除左右空格
select trim(name) from `table_name`;
数学函数
abs(num) 绝对值
#返回-3的绝对值 = 3 select abs(-3) from dual;bin(decimal_num) 十进制转二进制
#返回6的二进制 select bin(6) from dual;celling(num) 向上取整
#返回1.1向上取整的值 = 2 select celling(1.1) from dual;floor(num) 向下取整
#返回1.1向下取整的值 = 1 select floor(1.1) from dual;conv(num, from_base, to_base) 进制转换
#将十进制的6转换成8进制 select conv(6,10,8) from dual;format(num, decimal_places) 格式化数字、保留小数
#格式化12345.6789 保留两位 = 12,345.68(四舍五入) select format(12345.6789,2) from dual;least(num1,num2,num3…) 求最小值
#最小值为5 select least(7,5,8) from dual;mod(numerator, denominator) 求余
#求3%2 = 1 select mod(3,2) from dual;rand([seed]) 返回[0,1]随机数
#每次返回不同的随机数 select rand() from dual; #种子未改变的情况，每次返回同一个随机数.种子改变则随机数改变 select rand(3) from dual;
日期函数
current_date()、current_time() 返回当前日期、时间
current_timestamp() 、now() 返回当前日期和时间#返回当前日期 YYYY-MM-DD select current_date() from dual; #返回当前时间 HH:MM:SS select current_time() from dual; #返回当前时间戳（日期于时间） select current_timestamp() from dual; select now() from dual;date(datetime) 取出datetime日期返回
time(datetime) 取出时间返回
year|month|day(datetime) 取出 年、月、日返回#返回当前日期 select date(current_timestamp) from dual; #返回当前时间 select time(current_timestamp) from dual;date_add(date\datetime , interval d_value d_type) 返回date 加上日期或时间
date_sub(date\datetime , interval d_value d_type) 返回date 减去日期或时间
注：interval 后面的时间单位可以是 year month day hour minute second#查询10分钟内添加的数据 select * from `table_name` where date_add(`datetime`, interval 10 minute) >= now(); select * from `table_name` where date_sub(now(), interval 10 minute) <= `datetime`;datediff(date1\datetime , date2) 返回两个日期差(天)
timediff(datetime1 , datetime2) 返回两个时间差(时分秒)#返回 天 select datediff('2023-06-21','2001-07-04') from dual; #返回时：分：秒 select timediff(now(),'2001-07-04 12:00:00') from dual;unix_timestamp() 返回1970-01-01 到现在的秒数
from_unixtime(second , ‘%Y-%m-%d %H:%i:%s’) 将unix_timestamp()得到的秒数转成年月日时分秒
意义：在实际开发中，可以通过unix_timestamp()得到一个int保存时间戳，再通过from_unixtime(second , ‘%Y-%m-%d %H:%i:%s’)还原时间#1970-01-01到现在 过去的秒数 select unix_timestamp() from dual; #秒数对应的时间 即1970-01-01 加上 秒数 select from_unixtime(unix_timestamp(), '%Y-%m-%d %H:%i:%s') from dual;last_day(datetime) 返回该日期所在月份的最后一天
#返回2020年6月的最后一天 select last_day('2020-06-05') from dual;
加密函数
user() 查询用户
#查看登录到mysql的用户有哪些,返回用户@ip地址 select user() from dual;database()
#查询当前使用的数据库名称 select database();md5(str) 加密str，算出MD5 32的字符串
select md5('mika666') from dual;password(str) mysql登录密码的加密函数，返回加密后的字符串
select password('mika666') from dual;
流程控制函数
if(expr1,expr2,expr3) 如果expr1为true，返回expr2，否则返回expr3
select if(true,1,0) from dual;ifnull(expr1,expr2) 如果expr1不为空，返回expr1，否则返回expr2
select ifnull(null,'mika') from dual;case when expr1 then expr2 when expr3 then expr4 else expr5 end 如果expr1为真，返回expr2，如果expr3为真，返回expr4 否则返回expr5
select case when false then 'hina' when true then 'mika' else 'nobody' end from dual;
查询增强
分页查询
limit start , rows 从start+1行开始查找rows行
#从第1行开始，共显示5行 select * from `table_name` where... order by... limit 0,5; #每页有k行数据，则第i页为 select * from `table_name` limit (i-1)*k ,k;
分组查询
#统计每个班的男生人数
select count(class) from `table_name` where sex = '男' group by class;
多表查询
table_name.column 表名.列名 作为筛选条件
注：多表查询时，筛选条件不少于表数-1#显示雇员名，雇员工资和所属部门 select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;
外连接
左连接，显示左表的全部信息
select … 左表 left join 右表 on 匹配条件外连接解决多表查询时，只能显示两表匹配条件的数据的问题；实际开发中，左表/右表数据需要全部显示，另一张表不存在（为null）可以进行匹配判断的数据，导致左/右表信息显示不全。此时需要外连接
#显示每个部门名，及对应的成员数量 #从emp表查询每个部门的成员数量 select count(*) from emp group by deptno; #显示所有部门名，并匹配上相应成员数（此时会出现emp的查询中缺少一个部门，即无法匹配显示出dept表所有的部门名和成员数，所以需要外连接） select dept.deptno,dname,loc,ifnull(dcount,0) as dcount from dept left join (select count(*) as dcount,deptno from emp group by deptno) temp on dept.deptno= temp.deptno;#因为emp查询中缺少一个部门，所以进行左连接时，显示的多表查询结果中，emp缺失的那个部门对应的成员数量为null，此时用ifnull(dcount,0)解决右连接 select… table01 right join table02 on expr
自连接
table_name name01 , table_name name02 通过给同一张表取别名实现自连接
#显示员工和对应上级 select worker.ename as '员工',boss.ename as '所属上级' from emp worker,emp boss where worker.mgr=boss.empno;
子查询
单行子查询，将返回的一行数据直接拿来使用
#查询数学最高分 select max(math) from students; #查询数学最高分的学生信息 select * from students where math = ( select max(math) from students);多行子查询，配合in(set) 将多个数据直接拿来使用
#查询10号部门的岗位 select job from emp where deptno = 10; #查询这些岗位有哪些非10号部门的员工 select name,job,deptno from emp where job in ( select job from emp where deptno = 10) and deptno != 10;临时表子查询 将查询得到的表拿来联查
#查询各科成绩的最低分 select min(Cinese) as chinese_min ,min(math) as math_min , min(English) as english_min from student; #查询各科最低分的学生 select name,Chinese,math,English from student , ( select min(Cinese) as chinese_min ,min(math) as math_min , min(English) as english_min from student) min_score where Chinese = chinese_min or math = math_min or English = english_min;all 和 any
select sal from emp where deptno = 30;#部门30的所有员工工资表 #显示工资比部门30的所有员工的工资高的员工信息 select * from emp where sal > all(select sal from emp where deptno = 30)；#all : sal大于所有的子查询 #显示工资比部门30的部分员工的工资高的员工信息 select * from emp where sal > any(select sal from emp where deptno = 30)；#any : sal大于部分的子查询多列子查询 语法：（column01,column02…）= (select column03,column04 from …)
#查找和SMITH相同岗位和部门的员工信息 select * from emp where (job,deptno) = (select job,deptno from emp where name = 'SMITH');
表复制
复制表结构(列名、列类型、字符集等)
#复制emp表的结构，创建一张temp表 create table `temp` like `emp`;复制表内容
#将emp表的内容复制到temp表 insert into (column01,column02...) select column01,column02... from `emp`; #也可以一键复制 insert into `temp` select * from `emp`;表的自我复制(蠕虫复制)
#将temp表的内容复制插入给自己 insert into `temp` select * from `temp`;拓：对temp表去重
#复制temp结构，创建temp02 create table `temp02` like `temp`; #复制去重的temp表查询插入到temp02 insert into `temp02` select distinct * from `temp`; #删除temp表所有内容 delete from `temp`; #将temp02的查询插入到temp表 insert into `temp` select * from `temp02`； #删除temp02 drop table `temp02`;
表合并
union 取两个查询并集，要求两个查询的column一样
select ... union select ...union all 将两个查询拼接，不会去除重复行，要求两个查询的column一样
select ... union all select ...
mysql约束
主键 字段名 字段类型 primary key
复合主键 create table `table_name`(column01 … ,column02 … ，primary key(column01,column02) ; c1、c2不能同时重复1. 用于唯一的标识表行的数据，当定义主键约束后，该列数据不能重复，也不能为null
2. 一张表只能使用一个主键，但是可以使用复合主键
#创建一张用户表，包含用户名和邮箱（不可重复） create table `user` ( `name` varchar(32) , `email` varchar(32) primary key); #也可以写成 create table `user` ( `name` varchar(32) , `email` varchar(32), primary key(`email`)); #如果要name和email都使用主键，可以用复合主键(name、email不能同时相同) create table `user` ( `name` varchar(32) , `email` varchar(32), primary key(`name`,`email`));not null 字段名 字段类型 not null
当定义not null 约束后，该列数据不能为nullunique 字段名 字段类型 unique
当定义unique 约束后，该列数据不能重复，但是可以有多个null外键 foreign key(从表字段名) refernces 主表名(主键名或unique字段名)
注：innodb引擎才支持外键，从表允许为null，被从表绑定数据的主表无法随意删除数据#主表 create table cls (id int , primary key(id)); #从表 create table stu (class_id int , name varchar(32) , foreign key(class_id) refernces cls(id));check(条件) 强制行数据必须满足条件
#创建工人表，工资必需大于0 create table emp(name varchar(32), sal double, check(sal>0));auto_increment 自增长(默认开始值为1)
需配合主键或unique使用#创建一张学生表，学号随插入数据自增长 create table stu(id int primary key auto_increment, name char(32)); #修改自增长的开始值为10 alter table stu auto_increment = 10;
索引
创建索引
普通索引 create index 索引名 on 表名(字段名)
alter table 表名 add index 索引名(字段名)
该索引对字段没有要求，但是效率一般#给column添加索引 create index `column_index` on `table_name`(column01); alter table `table_name` add index `column_index`(column01);唯一索引 create unique index 索引名 on 表名(字段名)
alter table 表名 add unique index 索引名(字段名)可以在创建表的时候直接指定unique约束，相当于创建了唯一索引。unique要求字段不可重复，但是索引效率较高
#给column添加唯一索引 create unique index `index_name` on `table_name`(column01); #在建表时指定unique约束，相当于创建了唯一索引 create table `table_name`(... , column01 unique);主键索引 create primary key index 索引名 on 表名(字段名)
alter table 表名 add primary key index 索引名(字段名)
可以在创建表的时候直接指定主键约束，相当于创建了主键索引。主键要求字段不可重复不为null，但是索引效率高create primary key index `index_name` on `table_name`(column01);
删除索引
drop index `index_name` on `table_name`;
#特殊的：删除主键索引可以如下，因为它是唯一的
alter table `table_name` drop primary key;
查询索引
#查询表中创建了哪些索引
show index from `table_name`;
#其他方式，效果一样
show indexes from `table_name`;
show keys from `table_name`;
修改索引
#方式就是先删除索引，再重建索引
drop index `index_name` on `table_name`;
create index `column_index` on `table_name`(column01);
事务
开始事务
需要innodb存储引擎
#两种方式
start transaction;
set autocommit = off;
设置保存点
savepoint `point_name`;
保存点回退
#回退到指定保存点
rollback to `point_name`;
#回退到事务开始时
rollback;
提交保存点
提交后无法再回退，会删除所有保存点，结束事务，释放锁
commit;
隔离
查看隔离级别
select @@transaction_isolation;
设置隔离级别
set session transaction isolation level [read uncommit | read committed | repeatable read | serializable ]
存储引擎
- 不需要考虑事务，只进行CRUD，则选择myisam 效率高；
- 如果需要使用事务操作，选择innodb；
- 对于临时表，选择memory
视图
创建视图 create view 视图名 as select语句
create view `my_view` as select * from `table_name`;修改视图(结构和数据都会被修改)
alter view 视图名 as select 语句alter view `my_view` as select * from `table_name02`查看视图结构 show create view 视图名
查看视图结构信息 desc 视图名
show create view `my_view`; desc `my_view`;删除视图 drop view 视图1,视图2
drop view `my_view01`,`my_view02`,...;
mysql管理
用户管理
创建用户
如果不指定inet，则所有地址都可以登录，默认为 %create user '用户名'@'inetAddress' identified by '密码'; #指定某段地址192.168.1.xxx create user '用户名'@'192.168.1.%' identified by '密码';删除用户
drop user '用户名'@'inetAddress';修改密码
修改自己的密码
set password = password('123456');修改其他用户密码(需要权限)
set password for '用户名'@'inetAddress' = password('123456');权限
授权
grant 权限列表 on 库.对象名 to 用户名@登陆位置 [identified by 密码] 7grant select,delete,create ... on 库.对象名 '用户名'@'inetAddress'; #开放所有权限 grant all [privileges] on...撤销权限
revoke 权限列表 on 库.对象名 from '用户名'@'inetAddress'；
499
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
