参考资料:https://www.bilibili.com/video/BV1DE411n7fU?p=6
MySQL简介
MySQL是一个管理文件的软件,用于构建关系型数据库管理系统。包含两个部分:客户端和服务端。
- 服务端
- socket服务端
- 本地文件操作
- 解析SQL语句
- 客户端
- socket客户端
- 发送指令
- 解析SQL语句
MySQL打开方法
- 打开两个终端,分别运行mysqld(mysql服务端)和mysql (-u XXX -p)(客户端)
- 制作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);