MySQL常用操作总结

root用户密码忘记，重置的操作

#1: 通过任务管理器或者服务管理，关掉mysqld(服务进程)
#2: 通过命令行+特殊参数开启mysqld
mysqld --defaults-file="D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini" --skip-grant-tables

#3: 此时，mysqld服务进程已经打开。并且不需要权限检查
4: mysql -uroot #无密码登陆服务器。另启动一个客户端进行
#5: 修改权限表
use mysql;
update user set authentication_string=password('新密码') where user='root' and Host='localhost'; 
flush privileges;
#6: 通过任务管理器，关掉mysqld服务进程。
#7: 再次通过服务管理，打开mysql服务。
#8: 即可用修改后的新密码登陆。

修改密码

#修改'root'@'localhost'用户的密码规则和密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123'; 

查看所有字符集

SHOW VARIABLES LIKE 'character_set_%';

查询语句模板

select 
from 
where
group by
having
order by

创建新用户

create user jack;	#以‘%’做为host，并且没有密码
creat user ‘jack’@‘lacalhost’ indentifid with mysql_native_password by "123456"	#以localhost做为主机，并且以123456作为登录密码

删除用户

drop user jack;#删除‘jack’@‘%’，如果没有这条记录，删除失败
drop user ‘jack’@'localhost‘; # 删除jack

用户权限

#新用户创建没有任何权限需要手动赋予
show grants for 用户名;
#给用户授权
grant 权限1[,权限2,....] on *.* to 用户@主机;
#赋予所有权限
grant all on *.* to 用户@主机;
#收回用户权限
revoke 同上

库基本命令

create database 库名;	#创建数据库
show databases; #查看所有数据库
show create database 库名;	#查看创建库的命令
drop database 库名;	 #删除数据库

创建表基本操作

create table 表名(
	列名1 类型 约束信息，
    列名2 类型 约束信息，
    列名3 类型 约束信息，
    ...
    列名n 类型 约束信息，
)[options];

rename table tablename1 to tablename2; #修改表名
show create table 表名;	#查看建表命令
drop table 表名;
alter table 表名 
   #子命令
   alter table 表名 add column_name datatype [默认信息] [约束信息] #添加列
   alter table 表名 drop column column_name;  #删除列
   alter table 表名 rename column column_name to new_column_name #修改列明
   alter table 表名 modify column_name datatype [default value] #修改列的类型
   

视图

# 创建
create [or replace]  [algorithm = {UNIFINED | merge | temptable}]
view view_name [(column_list)]
as
	select_statemrnt
[with [cascade | local] check option]
#备注
#使用时某个普通用户，则需要授予：CREATE view 和drop view权限
#用户要有创建视图的权限 【create view】
#一般用权限管理配合

事务语句

commit 
rollback
savepoint
select @@transaction_isolation;	#查看事务的隔离级别
set session transaction isolation level read committed; #修改事务的隔离级别
read uncommitted
read committed
repeatable read
serializable
set autocommit = 0;	#取消事务的自动提交
## 表级锁
lock table 表名 read | write -- 显示调用
unlock #释放表级锁
# 在read模式下，其他事务可以做select操作
# write模式下，其他事务不能做任何操作，完全的串行化
## 行级锁 INNODB数据引擎中才有 #自动分配

索引

索引创建方式

1. 自动创建

   当我们给表中的列添加了主键约束或唯一性约束时，rambs会自动帮我们创建唯一性索引

   注：只有当我们的查询条件是创建了索引的列时，RDBMS才会帮助我们使用索引进行查询

2. 手动创建

   -- 语法
   create index index_name on 表名 (列名 [,列名]) [visible]
   

3. 何时创建索引

   • 列中null太多，不适合
   • 列中的值重复率太高
   • 列经常做更新操作

4. 有了索引后，rdbms是如何做查询的

   • rdbms会自动判断出你的查询条件列是否加了索引
   • 首先，去查询索引，找到‘’行记录的物理位置‘’
   • 其次，再根据‘’行记录的物理位置‘’去表中定位记录
   • 最后把记录值返回结果集

5. 索引失效

   • 你的查询条件没有使用添加了索引列
   • 查询条件中使用了表达式或函数

6. 索引小结

   • 查询条件尽可能使用主键或唯一性键
   • 能不用函数尽量不要在查询条件中使用函数或表达式

函数

基本函数

函数	用法
ABS(x)	返回x的绝对值
SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
PI()	返回圆周率的值
CEIL(x)，CEILING(x)	返回大于或等于某个值的最小整数
FLOOR(x)	返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL

角度与弧度互换函数

函数	用法
RADIANS(x)	将角度转化为弧度，其中，参数x为角度值
DEGREES(x)	将弧度转化为角度，其中，参数x为弧度值

三角函数

函数	用法
SIN(x)	返回x的正弦值，其中，参数x为弧度值
ASIN(x)	返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x)	返回x的余弦值，其中，参数x为弧度值
ACOS(x)	返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)	返回x的正切值，其中，参数x为弧度值
ATAN(x)	返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)	返回两个参数的反正切值
COT(x)	返回x的余切值，其中，X为弧度值

指数与对数

函数	用法
POW(x,y)，POWER(X,Y)	返回x的y次方
EXP(X)	返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)，LOG(X)	返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X)	返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X)	返回以2为底的X的对数，当X <= 0 时，返回NULL

进制间的转换

函数	用法
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(x)	返回x的八进制编码
CONV(x,f1,f2)	返回f1进制数变成f2进制数

mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
    -> FROM DUAL;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010    | A       | 12      | 2            |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)

字符串函数

函数	用法
ASCII(S)	返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,…,sn)	连接s1,s2,…,sn为一个字符串
CONCAT_WS(x, s1,s2,…,sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
SPACE(n)	返回n个空格
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

注意：MySQL中，字符串的位置是从1开始的。

获取日期、时间

函数	用法
CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间

日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

获取月份、星期、星期数、天数等函数

函数	用法
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份：January，…
DAYNAME(date)	返回星期几：MONDAY，TUESDAY…SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)	返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2，。。。周六是7

流程控制函数

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END	相当于Java的switch…case…

加密与解密函数

函数	用法
PASSWORD(str)	返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。
ENCODE(value,password_seed)	返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)	返回使用password_seed作为加密密码解密value

MySQL信息函数

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

关键字单词

auto_increment 	#自动增长
primary key #主键
CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 要引入的表名(列名)
REGEXP #正则表达式
distinct #去重
join #关联查询  from 表名 别名 join 表名 别名 on 关联条件
left outer join #左外连接 以左边为准，右边不满足以null代替
right outer join #右外连接 以右边为准，左边不满足以null代替
inner join #内连接	 两边都满足
IN		#等于列表中的**任意一个**
ANY 	#需要和单行比较操作符一起使用，和子查询返回的**某一个**值比较
ALL     #需要和单行比较操作符一起使用，和子查询返回的**所有**值比较
SOME    #实际上是ANY的别名，作用相同，一般常使用ANY
## 事务相关
begin #事务开始
start transaction #事务开始
commit #提交事务
rollback #回滚

约束类型

1. 主键约束：非空且唯一
2. 非空约束 NOT NULL
3. 唯一性约束 unique
4. 外键约束
5. 自定义约束 CHECK（条件）

添加约束

1. 在列定义之前
2. 在列定义以后约束

数据类型

1. 数字型
   1. tinyint byte
   2. smaliint 相当于short
   3. mediumint 三个字节
   4. int
   5. bigin 相当于long
   6. float
   7. double
2. 字符型
   1. char 定长字符
   2. varchar(length) 可变长字符
   3. text 文本类型
   4. json
3. 日期型
   1. data
   2. time
   3. datatime
   4. timetamp
4. 枚举
   1. enum
5. 二进制类型
   1. clob
   2. blob

练习

-- 1) 查询数学分数为65,75,85的同学
select *
from exam
where math in (65,75,85);
-- 2) 查询英语成绩，排除完全相同重复数据
select distinct english  
fr om exam;
--3) 对学生成绩按照英语进行降序排序，英语相同学员按照数学降序
select *
from exam
order by math desc,english desc;
-- 4) 统计总分大于220的人数有多少
select count(*)
from exam
where english+chinese+math>220;
-- 5) 统计英语成绩大于90的学生有多少个
select count(*)
from exam
where english>90;
-- 6) 统计一个班级语文、英语、数学的成绩总和
select sum(chinese) 语文,sum(math) 数学,sum(english) 英语
from exam;
-- 7) 求班级最高分和最低分
select max(chinese+math+english) 最高分,min(chinese+math+english) 最低分
from exam;

-- （1）求购买了供应商"宝洁"产品的所有顾客；
select distinct customer.* 
from purcase left join product on purcase.productId = product.productId 
			 left join customer on purcase.customerId = customer.customerId
where product.provider  = '宝洁';
--（2）求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）
--  ---------------------------------------------------------------------------------------------------
select name
	from (
		select customer.name name,count(purcase.productId) num
		from purcase left join customer on purcase.customerId = customer.customerId 
					 right join (
						select distinct productId,name
						from purcase left join customer on purcase.customerId = customer.customerId
						where name = 'dennis'
					 ) cus on purcase.productId = cus.productId
		group by customer.name) a;
	

#（3）求牙膏卖出数量最多的供应商。
select id,max(num)
from (
	select purcase.productId id,sum(quantity) num
	from purcase left join product on product.productId = purcase.productId
	where category='牙膏'
	group by purcase.productId
) a;
-- DML操作
-- （1）将所有的牙膏商品单价增加10%。 
update product set unitPrice=unitPrice*1.1 where category='牙膏';
-- （2）删除从未被购买的商品记录。
delete from product where productId not in(
				select distinct productId
				from purcase
			);

-- 1) 对订单表中商品归类后，显示每一类商品的总价
select product,sum(price) 总价
from orders
group by product;
-- 2) 查询购买了几类商品，并且每类总价大于100的商品
select * 
from (
	select product,sum(price) 总价
	from orders
	group by product
	) o
where o.总价>100;

--------------------------------------------------------------------------------------------------------------------
-- 1) 查询平均成绩大于70分的同学的学号和平均成绩
select *
from (
	select s.id id ,avg(sc.score) 平均成绩
	from student s left join studentcource sc on s.id= sc.student_id
	group by s.id
) a
where a.平均成绩>70;

-- 2) 查询所有同学的学号、姓名、选课数、总成绩

select s.id 学生ID,s.name 学生姓名,选课数,总成绩
from student s left join (
	select student_id id,count(*) 选课数, sum(score) 总成绩
	from studentcource
	group by student_id
	) a on a.id = s.id
order by s.id;	

-- 3) 查询没学过关羽老师课的同学的学号、姓名
select id,name
from student
where id not in (
	select student_id id
	from studentcource sc right join(
		select cource.id 课程ID
		from cource
		where cource.teacher_id =(
			select id 老师ID
			from teacher
			where name = '关羽'
		)
	) a on a.课程ID = sc.cource_id
)
order by id;
-- 4) 查询学过赵云老师所教的所有课的同学的学号、姓名
select id,name
from student
where id in (
	select student_id id
	from studentcource sc right join(
		select cource.id 课程ID
		from cource
		where cource.teacher_id =(
			select id 老师ID
			from teacher
			where name = '赵云'
		)
	) a on a.课程ID = sc.cource_id
)
order by id;
-- 5) 查询没有学三门课以上的同学的学号、姓名
select ID,学生姓名
from (
	select  s.id ID, s.name 学生姓名, count(*) 选课数
	from studentcource  sc left join student s on sc.student_id=s.id
	group by s.id
	) a
where a.选课数<=3;
-- 6) 查询各科成绩最高和最低的分
select cource_id,max(sc.score) 最高分, min(sc.score) 最低分
from studentcource sc
group by sc.cource_id;
-- 7) 查询上海和北京学生数量
select city 城市, count(*) 数量
from student
where city='上海' or city='北京'
group by city;

-- 8) 统计每门课程的学生选修人数（超过两人的进行统计）
select c.id ,name ,数量
from cource c left join (
	select cource_id id,count(*) 数量
	from studentcource sc
	group by cource_id
	) a on c.id = a.id
where a.数量>2;
-- 9) 查询 生物 成绩排名前3的同学
select *
from student
where id in (
	select *
	from (
		select sc.student_id
		from studentcource sc right join(
			select id
			from cource
			where name='生物' ) a on sc.cource_id = a.id
		order by score
		limit 3 offset 0 ) b 
);