MySQL语法规则学习

参考资料：https://www.bilibili.com/video/BV1DE411n7fU?p=6

MySQL简介

MySQL是一个管理文件的软件，用于构建关系型数据库管理系统。包含两个部分：客户端和服务端。

1. 服务端
   • socket服务端
   • 本地文件操作
   • 解析SQL语句
2. 客户端
   • socket客户端
   • 发送指令
   • 解析SQL语句

MySQL打开方法

1. 打开两个终端，分别运行mysqld（mysql服务端）和mysql (-u XXX -p)(客户端)
2. 制作windows服务：路径/mysqld --install
   1. net start MySQL（后台就会一直运行着MySQL服务端，我们下次使用就不用专门再打开一个终端运行MySQL服务端了）
   2. net stop MySQL (关闭MySQL的Windows服务)
   PS:移除MySQL的Windows服务：路径/mysqld --remove

MySQL语法规范

管理用户

创建、删除用户

create user 'david'@'168.192.1.1' identified by '123123';
create user 'david'@'168.192.1.%' identified by '123123';
create user 'david'@'%' identified by '123123';
--format: create user '用户名'@'IP地址' identified by 'password'
--%是mysql中的通配符，是可以匹配任意数目的字符的符号。

--删除用户
drop user 'david'@'168.192.1.1'
--以上IP地址为举例随便写的，如相同，纯属巧合。

授权

--提升权限
grant select, insert, update on db1.t1 to 'david'@'localhost';
grant all privileges on db1.t1 to 'david'@'localhost';
--取消权限
revoke all privileges from db1.t1 to 'david'@'localhost';

管理数据库（本质上就是文件夹）

--';'是MySQL里默认的语句终止符。
--展示本地有多少数据库（文件夹）
show databases;
--创建数据库
create database db1;
create database db2 default charset utf8;--后面指定字符编码为utf8,使得中文可以正常显示。
--删除数据库
drop database db1;

管理数据表（文件）

--展示当前数据库中有多少数据表
show tables;


--创建数据表
create table t1(id int, name char(10)) default charset = utf8;
create table t2(id int, name char(10)) engine=innodb default chaset=utf8;
create table t3(id int auto_increment, name char(10)) engine=innodb default charset=utf8;
format: create table tt(
	列名 类型 null,
	列名 类型 not null,
	列名 类型 not null primary key,
	id int,
	name char(10))engine=innodb default charset=utf8;
--engine=innodb，支持事务，支持原子性操作。
--primary key ,主键，一张数据表只能有一个主键，但是主键可以由多列联合组成。主键表明约束（不能重复且不能为null），而且可以加速查找。
--auto_increment 表示自增

--清空表
truncate table t1;--自增的列会从0开始增加
delete from t1;--自增的列会继续从之前的最大值增加
--删除表
drop table t1;

管理数据行（具体数据）

增（插入数据）
insert t1(id, name) values(1, 'david');
insert t1(id, name) values(1, 'david'), (2, 'bob');
insert t1(id, name) select * from t2;

删
delete from t1 where id < 6;
改
update t1 set age = 18;
update t1 set age = 18 where id = 10;
查
select * from t1 where id in (2, 3, 4);
select * from t1 where id not in (1, 2, 3);
select * from t1 where id between 10 and 20;
select * from t1 where id limit 10;--取出t1表的前十行数据
select * from t1 where id limit 20, 10;--取出t1表的第20行后的10行数据。
select count(1) as num, avg(score) as AVG from t1 group by name having AVG > 60;--group by 后面的条件必须使用having而不是where.group by 跟的函数还有max, min, sum等。
select * from t1 order by score desc;--按照降序排
select * from t1 order by score asc;--按照升序排
select * from t1 where name like 'd%';
select * from t1 where name like '%d';
select * from t1 where name like '%d%';
select * from t1 where name like 'd_';
select * from t1 where name like '_d';
--_和%都是MySQL中的通配符，但是_只能匹配一个字符，%可以匹配任意多个字符。
select * from t1 left join t12 on t12.id = t1.id;
select * from t1 inner join t12 on t12.id = t1.id;
--inner join 会去除存在null数据的数据行。left join 以左边的表为准，即左边的表的每一行都会显示出来。right join 也存在.
--下面是临时表
select * from （select * from tb where id < 10） as B;
--union (all) 是将两张具有相同列的表进行上下拼接，union可以去重（将多行完全相同的数据行简化为一行，union all 会全部显示）
select * from t1 union select * from t2;
select * from t1 union all select * from t2;

数据类型

数据类型	备注
int	-
tinyint	_
bigint	_
FLOAT	不精确
DOUBLE	不精确
decimal	准确值，内部是以字符串形式存储的
char	定长字符串，例如char(10)，存储器中始终是以10字节宽度存储，查询速度较快
varchar	变长字符串，例如varchar(10)，表示能够存储的最大宽度是10字节，但是如果用不到10字节，如’david’就只会占用存储器中的5个字节而非char(10)中的10个字节，但节省空间的同时，查询速度就变慢了。
text	存储较长的文本信息，不过一般不推荐，要在表中存储文本，可以直接存储文本的路径
DATETIME	年月日时分秒
enum	如num enum(1, 2, 3)，则num这一列中的值可以去1或2或3.
set	如num set(1,2,3)，则num这一列的值中可以取集合（1， 2，3）的任意子集

数据备份与恢复

• 备份数据结构+数据

mysqldump -u root db1 > db1.sql -p;

• 备份数据结构

mysqldump -u root -d db1 > db1.sql -p;

-执行文件

create database db2;
mysqldump -u root db2 < db1.sql -p;

外键（foreign key）

用以形成表之间一对一（加唯一索引），一对多，多对多（2个及以上外键，从而联系多张表）等关系。

create table department(id int, department_name char(32))engine=innodb default charset=utf8;

create table userinfo(
uid int auto_increment primary key,
name char(10),
department_id int,
xx_id int,
constraint fk_user_depart foreign key ('department_id') references department('id'),
constraint fk_user_xx foreign key ('xx_id') references XX('id')
)engine=innodb default charset=utf8;

其他

desc t1;
show create table t1;
show create table t1 \G;
--下面设置自增
alter table t1 AUTO_INCREMENT=20;
--基于会话级别
show session variables like "auto_inc%";
set session auto_increment_increment=20;
set session auto_increment_offset=2;
--基于全局级别
show global variables like "auto_inc%";
set global auto_increment_increment=20;
set global auto_increment_offset=2;

--查询时列使用条件
select course_id avg(num), sum(case when num < 60 THEN 0 ELSE 1 END), sum(1), sum(case when num < 60 THEN 0 ELSE 1 END)/sum(1) as jgl from score group by course_id order by avg(num);