查看MySQL运行状态：systemctl status mysql(参考：https://blog.csdn.net/weixin_45793065/article/details/106574616)
关闭MySQL :systemctl stop mysqld(注意：是mysqld不是mysql)
启动MySQL :systemctl start mysqld
重启MySQL :systemctl restart mysqld
desc tablename;(查看表的字段属性)
展示创建语句：
show create table tablename G；
show create database databasename;
改变自增id的数值：
alter table tablename AUTO_INCREMENT=10;
基于会话级别的步长：show session variables like 'auto_incre%';
基于全局级别的步长：show global variables like 'auto_incre%';
唯一索引：
create table t6(id int auto_increment primary key,name varchar(255),phone char(11),unique uu(name,phone))engine=innodb default charset utf8;
去重：select distinct student_id from score where num<60;
MySQL连表操作可以一直连下去
SELECT * from score LEFT JOIN course on score.course_id=course.cid LEFT JOIN student on student.sid=score.student_id 只要连上去,就可以用它的连表操作
数据库全局操作：
show variables like 'validate_password%' show session variables like 'auto_increment%' set global validate_password_policy = low set session auto_increment_increment =2 会话步长 set global auto_increment_increment =2
用户操作:
增:
create user 'alex'@'%' identified by '123456';
删:
drop user
改:
可以对用户名和IP都进行修改
rename user 'alex'@'%' to 'dd'@'127.0.0.1';
对密码进行修改
set password for 'dd'@'127.0.0.1'=password('123456')；（在mysql8.0中或许要改成 set password for 'twz'@'%'='123456';链接）
alter user 'dd'@'127.0.0.1' identified by '123456';
查:
在mysql数据库下(use mysql)执行
select user,host from user
也可以像操作数据表一样进行操作
select user,host from user where user='alex';
授权：
grant all privileges on *.* to 'alex'@'%';
数据库操作
增:
create database db1 default charset utf8;
删:
drop database db1;
查: show databases;
数据表操作:
增:
create table t1(id int auto_increment primary key,name varchar(255),age int)engine=innodb default charset=utf8;
创建指定长度的float: float(6,2)
还有外键:constraint fk_t1_t2 foreign key(t1_id) references t2(id)
主键:primary key(id,nid)
唯一索引:unique uu(age)
联合唯一索引:unique uu(age,nid)
改：
alter table t1 auto_increment =4
删:
drop table t1;
查:
show tables;
数据行操作:
增: insert into t1(name,age)values('alex',18) insert into t1(name,age)values('alex',18),('tom',19) insert into t1(name,age) select name,age from t2(不用括号,后面的列名与前面可以不一样,但列数最好一样) 删: delete from t1; delete from t1 where id>1; truncat table t1; 改: update t1 set name ='文章' update t1 set name ='文章' where id <5
update t1 set name ='文章',age=12 where id =1 查: select * from t1 select * from t1 where id<1 select * from t1 where id<1 and name='alex' select * from t1 where id=1 or id=2 select * from t1 where id in(1,2) select * from t1 where id not in(1,2) select * from t1 where id between 1 and 2; 通配符: select * from t1 name like 'a%'; 以a开头的 select * from t1 name like 'a_' a后面跟一个字符的 分页: SELECT * from score LIMIT 0,2; 从第0个数开始(偏移量为0,一般id为1)取两个数 SELECT * from score LIMIT 2 offset 2; 从第0个数开始(偏移量为0,一般id为1)取两个数 分组:
select score.student_id from score group by score.student_id
一些聚合函数：
avg(num) sum(num) max(num) min(num) count(1)
对数据进行二次筛选必须用到having
select score.student_id,avg(num) from score group by score.student_id having avg(num)>60;
如果是像order by 就不用（反正不能用where）
select avg(num) from score group by score.student_id order by avg(score.num) asc/desc
排序：
根据一个条件进行排序
select avg(num) from score group by score.student_id order by avg(score.num) asc/desc
根据多个条件进行排序
select avg(num) from score group by score.student_id order by avg(score.num) asc/desc，score.student_id desc 会先根据
score.num的排序规则来进行排序，如果排序后仍然有无法排序的（第一个排序规则中有相同数据），对其无法排序的做二次筛选，根据后面的排序规则进行排序。
连表：
select * from score left join student on score.student_id = student.sid left join class on class.cid = student.class_id
可以多次连表
临时表
(select * from score)as A
SELECT * from (select * from score)as A 像使用普通表一样
把查出来的一列数据放到select后面
SELECT student_id,2,(SELECT AVG(num) from score WHERE course_id=2) as '平均值' from score WHERE score.student_id not in (SELECT student_id FROM score WHERE course_id=2) GROUP BY student_id;
只能有一列数（只能有一个结果），不能两列（两个结果）
取别名的话 select * from score as s1 这个就是帮score表取一个别名
(select * from score) as s2 帮临时表取名字
在子查询里面可以用到外层的数据 SELECT s1.student_id,
(SELECT num FROM score as s2 WHERE s2.student_id=s1.student_id AND course_id=1),
(SELECT num from score as s3 WHERE s3.student_id=s1.student_id AND s3.course_id=2),
(SELECT num FROM score as s4 WHERE s4.student_id=s1.student_id AND course_id=3),
(SELECT count(1) FROM score WHERE score.student_id=s1.student_id),
(SELECT avg(num) from score WHERE score.student_id=s1.student_id)
FROM score as s1 GROUP BY student_id;
有点像for循环一样，所有可以在子查询里面用到s1.student_id
for s1 in score:
print(s1.student_id)
条件选择：
SELECT course_id,MAX(num),MIN(num),CASE WHEN min(num)<10 THEN 0 ELSE MIN(num) END as '条件'from score GROUP BY course_id
CASE WHEN min(num)<10 THEN 0 ELSE MIN(num) END as '条件' 如果小于10，那就为0，大于10，输出 MIN(num)
求平均值的防止有ｎｕｌｌ出错
SELECT avg(if(ISNULL(score.num),0,score.num)) from score GROUP BY score.course_id;
求及格率：
SELECT course_id,sum(case when num<60 THEN 0 ELSE 1 END)/sum(1) as cc from score GROUP BY course_id
把小于60的数据置0，大于60置1，再加1列全为1