MySQL入门基础

MySQL入门基础

该文章适合已经学过基础的人快速查询知识,不适合完全新生用来入门

MySQL实际上是DBMS(数据库管理系统)

SQL:结构化查询语言,即在MySQL上使用这种语言来达到增删改操作

可知SQL在其他的DBMS中也是可用的

下面所有的 [内容] 表示此处应该是中括号里的东西,并不需要加上[]

如果输出中的中文乱码则输入

set names gbk;

如果表格不不对齐则输入

mysql --default-character-set=gbk -uroot -pxxxx

MySQL入门命令

以下均为命令行中的命令,均不区分大小写,用于最基础的操作

• exit 退出mysql
• mysql -uroot -p 隐藏密码输入

1.SQL入门命令

以下均为命令行中的命令,均不区分大小写,用于最基础的操作**,SQL语句都带;,所有的语句在没有输入;之前都不会执行**,输入\c可以在没输入;之前终止该次输入

• show databases; 显示目前数据库
• use [数据库名字]; 使用该数据库
• select version() ;查看MySQL的版本
• show database(); 查看当前使用的数据库
• create database [新建的数据库名]; 创建数据库
• show tables; 查看该数据下的表
• source [后缀名为sql的文件的路径]; 导入该数据库
• select * from [表名]; 查看该表下的数据
• desc [表名]; 查看该表的结构

2.SQL基础

2.1 SQL结构

SQL中的基础组成部分为table(表)，表中有行有列.其中

row(行)被称为是数据或者记录

column(列)被称为字段,通常用于指定数据类型

2.2 SQL语法分类

SQL的语法主要可以分为5大类

• DQL:数据查询语言 比如select …
• DML:数据操作语言 比如insert…,delete…,update…
• DDL:数据定义语言 比如create…,drop…,alter…
• TCL:事务控制语言 比如commit; rollback;
• DCl: 数据控制语言 比如grant和revoke

3. SQL基础操作

3.1 简单查询

select [字段名] from [表名];

查询多个字段名:

select [字段名],[字段名] from [表名];

查询所有字段

select * from [表名];   这种方法效率低且可读性差,实际开发不常用

查询字段且在显示的时候显示为指定的名字

select [字段名] as [想要显示的字段名] form [表名]; 不会更改实际的字段名,as可以省略,也就是

select [字段名] [想要显示的字段名] form [表名]; 

如果想要修改或者查询的字段名有空格，则需要把字段名用‘ '或者“ ”括起来,双引号在一些环境中用不了,所以更推荐单引号

select ‘i am blank’ as ”i am blank too“ form [表名]; 

可以在查询的时候,数字字段名可以用*或者/来改变显示数据

select [字段名]*12 form [表名];
select [字段名]/12 form [表名]; 

需要注意的是如果想把字段名修改为中午需要用""或者’'括起来

select [字段名] as '一段中文' form [表名]; 

distinct关键字可以去除重复的名字

select distinct [字段名],[字段名].... from [表名];
有多个字段名时代表联合字段名

distinct 只能出现在所有字段名的最前方

3.2 条件查询

条件符号

• < > = != <= >= <>(<>和!=同义)

select [字段名] from [表名] where [字段名] [上述符号，如=][数字或者字符串];

• between and 在两个值之间, 必须遵循左小右大 为闭区间

select [字段名] from [表名] where [字段名] between [数字或者字符串] and [数字或者字符串];

• is (not) null

null无法用=查询,固有了is null的语句

select [字段名] from [表名] where [字段名] is (not) null;

• and 顾名思义

可以连接判断句

select [字段名] from [表名] where [字段名] ... and [字段名]...;

• or 顾名思义

相当于或

select [字段名] from [表名] where [字段名] ... or [字段名] ...;

值得注意的是and的优先级比or高，所以要注意()的添加

• in 相当于多个or

select [字段名] from [表名] where [字段名] (not) in ([具体值，如字符串和数字],[具体值],....);

• not 顾名思义 主要与is和in连用

• like 模糊查询

% 匹配多个字符

下面三条语句分别对应o在中间,以o开头,以o结尾的字符串

select [字段名] from [表名] where [字段名] like '%o%';
select [字段名] from [表名] where [字段名] like 'o%';
select [字段名] from [表名] where [字段名] like '%o';

下面三条语句分别对应o为第二个单词,o为第三个单词的字符串

select [字段名] from [表名] where [字段名] like '_o%';
select [字段名] from [表名] where [字段名] like '__o%';

如果要查询的字符串中含有%或者_，则应该使用转义字符

select [字段名] from [表名] where [字段名] like '%\%%';
select [字段名] from [表名] where [字段名] like '%\_%';

• case … when…then…then…else…end

相当于if语句,用于判断多种情况

一个例子
select enamm, job,sal oldsal,
(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
from emp;

3.3 排序

select [字段名] from [表名] order by [字段名] (asc);升序,asc因为是默认值可以不加
select [字段名] from [表名] order by [字段名] desc;

符合排序则需要这样写

select [字段名] from [表名] order by [字段名],[字段名];先按第一个字段名排序,再按第二个字段名

综合执行顺序:from where select order by

4. 函数

引入:

select [常量,即数字或者字符串] from [表名];

上面这行代码会打印一列 字段名与常量相同 值也与常量相同的表,如

+------+
| 1000 |
+------+
| 1000 |
| 1000 |
+------+

可以用as重命名

4.1 单行函数

单行函数指一个输入对应一个输出

• lower upper

select upper/lower([字段名]) from [表名];此时字段名会变成带有upper或者lower的形式,可以用as重命名,下面的函数同理

• concat

用于连接字符串

select concat(字段名1,字段名2) from emp;

• substr

参数:substr(字符串,起始下标,结束下标/截取长度)

注意起始下标从1开始而不是0

查询第一个字母是A
select ename from emp where substr(ename,1,1) = 'A';
一个设置首字母大写的例子
select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) from t_student;

• length

计算字符串长度

select length([字段名]) from [表名];

• trim

可以去掉参数字符串左右两边的空格

select * from [字段名] from [表名] = trim(' test ');
此时相当于
select * from [字段名] from [表名] = 'test';

• format

format用于指定输入的格式

format(字段名,'格式’) 格式通常为$99.999，其中$后面的数字是你想要显示的格式

• str_to_date

用于将varchar类型转为date类型

• date_format

用于将date类型转为varchar类型

• round

用于指定精度的四舍五入 round(数字,精度) 其中四舍五入到整数为0,到保留一位小数为1,保留到整数第二位则为-1,以此类推

select round(123.456,-1) result from [表名];
+--------+
| result |
+--------+
|    120 |
|    120 |
+--------+

• rand()

rand()函数会生成0~1内的小数

select round(rand()*100,0) from [表名];
生成100之类的随机正整数

• ifnull()

有null在内的运算结果一定为null,ifnull就是为了处理这个情况诞生的

ifnull([字段名],[指定的结果])

如计算年薪,有些员工的补贴为null
select ename,(sal + ifnull(comm,0)) * 12 as yearsal from emp;

此外一行数值不能所有值都为null

4.2 分组函数

分组函数:多个输入只输出一个结果,没分组的情况下整张表默认为一组

• count

字段名下面的项的个数

select count([字段名]) from [表名];
如
select count(Host) from db;
+-------------+
| count(Host) |
+-------------+
|           2 |
+-------------+

• sum

用于给字段名求和

select max([字段名]) from [表名];

• avg max min

求平均值,最大值,最小值

select max/min/avg([字段名]) from [表名];

注意事项

• 分组函数自动忽略null,count不计null入个数,其他分组函数则将其忽略(不会导致结果为null)

• count(*) 会计算整个表格的行数 而count([字段名])则只会计算这列不为null的个数

• 分组函数不同于单行函数 不能在where之后使用

• 分组函数可以连续使用 如

select sum(sal),max(sal) from....;

统计工作岗位的数量

select count(distinct job) from emp;

5. 分组查询

5.1 基础应用

执行顺序 from where (group by) select (order by)

一个简单的例子

select job,sum(sla) where group by job; 

注意下面的语句没有意义

select ename ,job,sum(sal) from emp group by job;

因为ename并没有按照job分组，这样的输出是错误的,在其他环境中有可能报错

由上可以得出在使用group by时select后面只能跟对应的字段和分组函数

5.2 联合分组

现在要分别求每个部门的中每个职业最高薪资

select deptno,job,max(sal) from emp group by deptno,job;

5.3 having语句

having语句只能与group by连用,用于进一步的过滤

select deptno,max(sal) from emp group by deptno having max(sal) >3000;

也可以先将大于3000的数据都先找出来

select deptno,max(sal) where sal>3000 group by deptno;

实际应用中优先考虑下面这种写法,having用于where无法替代的场景

如 要求平均工资大于2500,这种情况无法使用where,主要原因是where后面不能使用分组函数

6. 执行顺序

所有语句的执行顺序(从上而下)为

• from
• where
• group by
• having
• select
• order by
• limit

而他们的语句顺序为

• select
• from
• where
• group by
• having
• order by
• limit

即先选表,然后从表中第一轮筛选 ,然后分组,继续第二轮分组,接着选择要显示的字段名,最后决定排序

7. 连接查询

简要的意思就是从一张表中查一些字段名,在另一张表上查另一些字段名,sql99与sql92的语法区别略过

表的连接方式主要有

• 内连接
• • 等值连接
  • 非等值连接
  • 自连接
• 外连接
• • 左连接
  • 右连接
• 全连接

7.1 内连接-等值连接

select ename,dname from emp,dept where emp.deptno = dept.deptno;
两种表的一行的deptno是否相等作为匹配条件 打印两张表的ename与dname
在select后改为 emp.ename与dept.dname 可以减少匹配条数

一些表的名字也许过于冗长,可以在写的时候添加临时名字
select a.ename,b.dname from emp a,dept b where a.deptno = b.deptno;
上面的语法其实是sql92的语法,如果要加其他过滤条件的话会和连接条件杂糅在一起.可读性低
select e.ename,d.dname from emp e join dept d on e.depton = d.deptno;
上面sql99的语法可以在后面加上where来添加过滤条件 解决了杂糅问题

在实际操作中应该考虑尽可能减少匹配次数,表的连接次数越多,效率越低

7.2 内连接-非等值连接

例子:按工资水平取等级

select e.name,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

7.3 内连接-自连接

案例:显示员工名和对应的领导名字

select a.ename as '员工',b.ename as '领导' from emp a join emp b on a.mgr=b.empno;
一张表看作两张表可以更方便的理解

7.4 外连接-右连接与左连接

在外连接中表存在着主次关系

emp right join dept 中的主表就是dept,在打印时无论是否匹配,主表的内容都会全部打印出来，同理left意味着左边是主表

左连接与右连接是可以相互转化的,且外连接的查询结果大于等于内连接

以7.3的例子

select a.ename as '员工',b.ename as '领导' from emp a left (right 也可) join emp b on a.mgr=b.empno;

8. 多个表连接查询

内连接与外连接可以混合

例子:

select e.ename,e.sal,d.name,s.grade from emo e join dept on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

9. 子查询

指select的嵌套语句

select

​ …(select)

from

​ …(select)

where

​ …(select)

9.1 where语句中的子查询

例子: 查询比最低工资高的所有员工的信息

select ename ,sal from emp where sal > (select min(sal) from emp);

9.2 from子句的子查询

例子:找出每个岗位的平均工资的薪资等级

 select t.*,s.grade from 
 (select job,avg(sal) as avgsal from emp group by job) t
 join salgrade s on 
 t.avgsal between s.losal and s.hisal;
 注意avg(sal)一定要取别名,否则在on中调用会出错

9.3 select的子查询

查询每个员工的部门名称,要求显示员工名,部门名?

select e.name,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
注意select后面的子查询只能返回一条结果,否则会报错

10 union语句

union可以减少匹配的次数相比连接多个表格

 语句1; union 语句二;
 这样输出的结果是两张表合并的结果，但是合并之前的结果的列数应该相同,类型不同在MySQL中可以相加,但是其他环境可能会报错

11. limit语句

limit可以将查询结构的一部分取出来,防止一次性显示太多,在order by之后执行

select ename,sal from emp order by sal desc limit 5;
只显示前五条
select ename,sal from emp order by sal desc limit 0,5;
0为其实下标,第二个参数为长度，这里第一条语句和第二条等效 

12. 分页

和limit语句配合使用可以达到跳转到指定页数的效果

起始位置的下标=(页数-1)*每页显示的条目个数

13. 表

13.1 表的建立

create table 表名(字段名1 数据类型,字段名2 数据类型....);
可以在数据类型之后指定默认值
create table 表名(字段名1 数据类型 default 默认值,字段名2 数据类型....);
字段名必须小写，_可以使用

快速创建一张表
create table [表名] as select * from emp;相当与复制
根据这个原理可知查询结果也可作为复制对象
create table [表名] as select ......;

13.2 数据类型

• varchar

varchar(数字)指定建议的长度,而实际上会根据实际的长度分配空间,最大255,中文这里占一个字符

• char

char(数字) 不会动态分配空间,总是分配固定的空间,速度较varchar块但是可能浪费空间,最大同上

• int

整型,最大有11位,int(数字)指定数字长度，可以不指定长度,下同

• bigint

相当于long

• float

单精度浮点型

• double

双精度浮点型

• date

短日期类型

• datetime

长日期类型

• clob

字符大对象 最多可以存储4G的字符串,用于存储文章

• blob

二进制大对象,用于存储图片，声音，视频之类的流媒体数据

13.3 表的删除

drop table [表名];//默认表是存在的
drop table if exists [表名];//表存在才删除

13.4 插入数据

insert into 表名(字段名1,字段名2...) values(值1....);
字段和值要一一对应,没有赋值的字段名默认为null
如果每个字段名都要赋值的话那么表名()这一部分可以省略,直接按默认顺序输入value(值1....)即可

可以一次插入多条数据
insert into 表名(字段名1,字段名2...) values(值1....) ,values(值2...),......;

插入的数据也可以是查询结果,不演示

13.5 插入日期

date类型:

首先要把字符串转化为date类型,这要用到str_to_date(‘日期’,‘格式’)

其中格式有

%Y 年

%m 月

%d 日

%h 小时

%i 分

%s 秒

insert into values(str_to_date('01-10-1990',%d-%m-%Y));
如果日期是2021-04-26这样的格式则可以不使用str_to_date转化
insert into values('2021-04-26');

同样的,select显示date类型的数据时默认也是2021-04-26这样的格式，如果要指定格式则需要date_format([字段名],‘格式’)

select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;

datetime类型:

除开date类型记录的数据外还额外记录了时分秒

insert into t_user(create_time) values('2014-09-16 12:12:12');
当输入为如上格式时可以不用用函数转化
now()函数可以获取当前的时间
insert into t_user(create_time) values(now());

13.6 更新数据

update [表名] set [字段名1]=[值1],...... where [限制条件];
没有限制条件时每条记录都会被更新

13.7 删除数据

delete from [表名] where [限制条件];
没有限制条件时整张表都会被删除,delete删除知识删除了数据,不会删除占用的空间，效率低但支持回滚

truncate table [表名];
truncate删除内存和空间,效率高但不支持回滚

drop table 表名;
直接删除整个表

14.约束

约束主要包括

• not null非空约束
• unique 唯一性约束
• primary key 主键约束
• foreign key 外键约束
• check 检查约束

14.1 非空约束

create table mytable{
id int,
name varchar(255) not null
};
增加非空约束后记录对应该字段不能为null
insert into mytable(id) values(3);这样会报错

14.2 唯一约束

create table mytable{
id int,
name varchar(255) unique
};
增加唯一约束后记录对应该字段不能重复
insert into mytable values(3,'name1');
insert into mytable values(2,'name1');
这样会报错

唯一约束的字段可以为null,也就是对应的记录可以存在多个null
insert into mytable values(3,null);
insert into mytable values(2,null);
不会报错

假如我们想让两个字段名联合起来有唯一约束可以这样写

create table mytable{
id int,
name varchar(255),
unique(id,name)
};
insert into mytable values(3,'name1');
insert into mytable values(3,'name1');
这样会报错,而
insert into mytable values(3,'name2');
insert into mytable values(3,'name1');
则不会报错

上面写在一个字段名的后面的约束称之为列级约束

反之为表级约束

两种约束可以结合起来

create table mytable{
id int,
name varchar(255) not null unique
};
在MySQL中如果一个字段同时被唯一约束和非空约束,则该字段自动成为主键字段

14.3 主键约束

主键字段相当于辨识字段,用于特征区分

任何一张表都应该有主键,否则表是无效的

主键字段自带not null与unique

create table mytable{
id int,
name varchar(255) primary key
};
给name添加主键约束,name成为主键字段,带有null,与unique的性质

也可以用表级约束来添加主键约束
create table mytable{
id int,
name varchar(255), 
primary key(name)
};

复合主键也是可行的
create table mytable{
id int,
name varchar(255), 
primary(id,name)
};
但是不推荐使用

主键只能有一个但是可以让多个字段名成为主键字段

主键值的自我维护

create table mytable{
id int primary key auto_increment,
name varchar(255) 
};
这样在插入记录时如果不给id复制,id会自动自增赋值,从1开始
insert into mytable(name) values('name1');id自动为1
insert into mytable(name) values('name2');id自动为2

14.4 外键约束

外键约束指把字段值约束在另一张表指定的范围内,避免越界或者输入错误.

create table limit{
classlimit int primary key
};父表

create table mytable{
classnumber int,
foreign key(classnumber) references limit(classlimit)
};子表
这样的情况下第二张表的classnumber值被约束在第一张表的classlimit中,只能取classlimit中的值
insert into limit values(101);
insert into limit values(102);
这样会报错

表的删创,数据的删改增都要遵从父子原则

值得注意的是,父表的被引用字段不一定要是主键,但至少要有unique约束
外键值可以为null

15 存储引擎

不同的存储引擎,表存储的方式不同,可以在建表时指定存储引擎和编码方式

create table mytable{
classlimit int
}engine-InnoDB default charset-gbk;

常见引擎

• MyISAM
• InooDB
• MEMORY

引擎不再多做赘述

16 事务

16.1 简介

事务指最小的工作单元

本质上指多条同时成功或者同时失败的DML语句

在事务过程中我们可以提交事务或者回滚事务

16.2 特性

• 原子性 事务是最小的工作单元 不可再分
• 一致性 所有操作同时成功或者同时失败
• 隔离性 之后介绍
• 持久性 没提交之前不会保存操作带来的结果

16.3 代码介绍

在默认情况下MySQL时自动提交事务,所以要输入

start transaction;

来关闭自动提交机制，以这行代码作为起始点,之后的一系列DML语句都会在输入

submit;

后才会生效,或者是输入

rollback

回到初始状态

16.4 隔离性

事务之间的隔离性主要有四个级别

• read uncommitted 读未提交

事务A可以读取到事务B未提交的数据,存在脏数据的问题

set global transaction isolation level read uncommitted;
将全局调整为读未提交隔离性

事务A可以在事务B插入数据后马上读取到

• read committed 读已提交

set global transaction isolation level read committed;
将全局调整为读已提交隔离性

事务A不可以读取到事务B未提交的数据,解决脏数据的问题,但是不可重复读取数据

• repeatable read 可重复读

set global transaction isolation level repeatable read;

事务开启后无论过去多久该事务读取的数据都是相同的,即可以重复读取

• serializable 序列化/串行化

set global transaction isolation level serializable;

无法并发,解决了所有潜在的问题但是效率低

在一个事务submit之前另一个事务无法进行

查看隔离级别

MySQL8.0+

select @@global.transaction_isolation;

MySQL5.0+

select @@global.tx_isolation;

17 索引

索引用于提高查找的效率,体现在查询条件有索引时

在MySQL中索引是一个二叉树结构

17.1 特性

以下字段会被自动添加索引

• 主键字段
• 带有唯一约束的字段

在MySQL中索引是一个单独的对象,不同的存储引擎以不同的形式存储

索引存储的是记录的值和对应的物理存储编号,用中序遍历

当数据量庞大且该字段经常出现在where后面且很少修改该字段对应记录的值时,推荐使用记录

17.2 分类及代码操作

根据着重点不同索引可以分为单一索引与复合索引

还可以分为 主键索引和唯一性索引

创建索引

create index [索引名] on [表名]([字段名]);

复合索引
create index [索引名] on [表名]([字段名1],[字段名2],....);

删除索引

drop index [索引名] on [表名];

查看是否使用了索引

explain [语句];
如
explain select * from emp where ename = 'KING';

如果使用了索引则type会变成ref

17.3 索引的失效

以下是几种常见的索引失效情况

• 模糊查询未指出首字符

select * from emp where ename like '%T';

• 使用了or但是or两边的字段不都含有索引

select * from emp where ename = 'KING' or job = 'MANAGER';

• 使用了复合索引但是没有使用左边的字段名

create index myindex on emp(job,sal);

select * from emp where job = 'MANAGER';
这样没问题

select * from emp where sal = 800;
不会使用索引

• where中有索引的字段参加了运算或者使用了函数

select * from emp where sal+1=800;
失效

select * from emp where lower(ename)='myname'；

18 视图

视图为从不同角度去阅读一份数据

18.1 视图的代码操作

视图的创建

create view [视图名] as [查询结果]; 

视图的删除

drop view [视图名];

18.2 视图特性

根据查询结果创建的表的CRUD会影响原来的数据

注:CRUD为增查改删

用于处理特别复杂的SQL语句,把自己想要的部分作为视图提取出来可以简化开发

19 DBA常用命令

数据导出:

在没有登录之前输入

mysqldump [数据库名] [表名]>[导出路径] -uroot -p[密码]

不指定表名时导出整个数据库

数据导入:

在登录MySQL的情况下输入

先要创建导入的对象数据库
create database mydatabase;
use mydatabase;
source [导入数据的路径];

20 数据库设计三范式

20.1 简介

第一范式:

任何一张表必须有主键,每一个字段具有原子性

第二范式:

建立在第一范式的基础之上,要求所有非主键字段完全依赖主键

第三范式:

建立在第二范式的基础之上,要求所有非主键字段直接依赖主键

20.2 设计口诀

一对多：

两张表,多的表加外键

多对多:

多对多,三张表,关系表两个外键

一对一：

首字符

select * from emp where ename like '%T';

• 使用了or但是or两边的字段不都含有索引

select * from emp where ename = 'KING' or job = 'MANAGER';

• 使用了复合索引但是没有使用左边的字段名

create index myindex on emp(job,sal);

select * from emp where job = 'MANAGER';
这样没问题

select * from emp where sal = 800;
不会使用索引

• where中有索引的字段参加了运算或者使用了函数

select * from emp where sal+1=800;
失效

select * from emp where lower(ename)='myname'；

18 视图

视图为从不同角度去阅读一份数据

18.1 视图的代码操作

视图的创建

create view [视图名] as [查询结果]; 

视图的删除

drop view [视图名];

18.2 视图特性

根据查询结果创建的表的CRUD会影响原来的数据

注:CRUD为增查改删

用于处理特别复杂的SQL语句,把自己想要的部分作为视图提取出来可以简化开发

19 DBA常用命令

数据导出:

在没有登录之前输入

mysqldump [数据库名] [表名]>[导出路径] -uroot -p[密码]

不指定表名时导出整个数据库

数据导入:

在登录MySQL的情况下输入

先要创建导入的对象数据库
create database mydatabase;
use mydatabase;
source [导入数据的路径];

20 数据库设计三范式

20.1 简介

第一范式:

任何一张表必须有主键,每一个字段具有原子性

第二范式:

建立在第一范式的基础之上,要求所有非主键字段完全依赖主键

第三范式:

建立在第二范式的基础之上,要求所有非主键字段直接依赖主键

20.2 设计口诀

一对多：

两张表,多的表加外键

多对多:

多对多,三张表,关系表两个外键

一对一：

信息庞大的表可以拆分成两张表,外键唯一