Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

本文使用软件为:Mysql版本8.0.29,Navicat PreMium 15

第一章 mysql介绍

1.1什么是数据库

  • 数据库:数据的仓库,database简称DB
  • 介质:硬盘和内存,数据仓库的位置
  • sql:用来和数据库DB打交道,完成和数据库DB的通信。

有一套自己的逻辑标准,但是每个数据库都有别人没有的特性,当使用某个数据库相关功能的特性功能时,此时sql可能就不是标准的了。95%以上的sql都是通用的。

1.2数据库的分类

1.关系型数据库

将复杂的数据结构归结为简单的二关系。通过对表的关联、结合和链接,在一个或多个关系表中实现对数据库的管理。

优点:存储在磁盘中/二维表结构中,断电不会丢失,符合人类的认知

缺点:浪费空间

2.非关系型数据库

优点:存储在内存中,效率高

缺点:断电易丢失

【举例1,如手机中的8g+128g存储,8g是内存,128g是磁盘;举例2,如插在电脑的磁盘,断电不会丢失】

3.不同的数据产品DBMS

大型数据库:oracle/DB2

中型数据库:mysql/SQL server

小型数据库:access

DBMS–>(执行)SQL–>(操作)DB

dbms:数据库管理系统 database management system

db:数据库 database

sql:结构化查询语言,适用于所有dbms

4.表

表table是数据库的基本组成单位,所有的数据都是以表的形式存储,目的是可读性强。

5.SQL语言的分类

DQL(数据查询语言):关键字select;data query language

DML(数据操纵语言):对表中的数据进行修改关键字:insert插入/delete删除/update修改,data manipulation language

DDL(数据修正语言): create创建/drop删除/alter更新,data definition language

TCL (事务控制语言):rollback 回滚/commit 提交,transaction control language

DCL (数据控制语言):grant 授权/revoke 撤销,database control language

第二章:数据库与表的逻辑

2.1查看有哪些数据库

show databases;

2.2查看你现在打开的是哪个数据库

select database ();

2.3查看当前数据库的版本

select version();

2.4创建数据库

create database 表名;

2.5切换/使用某个数据库

use 数据库名称;

2.7创建表

create table 表名(字段1 字段类型1,字段2 字段类型2…)

2.8查看这个数据库里有哪些表

show tables;

2.9往表中插入数据

  • 全部:insert into 表名(字段1,字段2…)values(字段1的值1,字段2的值2,…)

——–只要字段顺序和值顺序一样,那么插入的数据不会受到影响

  • 部分:insert into 表名(字段1,字段2…)values(字段1的值1,字段2的值2,…)

=insert into 表名values(值1,值2…)

——–保证值的数量和所有字段的数量相等

  • 批量添加:insert into values(值1,值2…),(值1,值2…)

2.10表的复制和批量插入

create table 新表名 as select*from 旧表名;

2.11终止一条sql语句

ctrl+c

2.12删除数据库/表(慎用)!!!

drop database 数据库名称;

drop table 表名; (注:如果这个表在就会被删除,不在 会错)

drop table if exists 表名; (注:如果这个表在就会被删除,不在 不会错)

delete from 表名 where 条件; (注:不写where会删除整个表数据,但表结构在)

第三章:表

3.1常用字段类型:

int 整数

float 小数

date 日期–存储年、月、日的值

注:日期可以升序降序,也可以比较大小

char 字符/字符串:定长型–固定长度n

vachar 字符/字符串:变长型–可变长度,最大长度n

numeric(a,b) 精确数值,总位数a,小数点后位数b

例:numeric(19,4):长度为19位的数,4代表小数的精度为4,保留4位小数

bit:只能是0,1,空值

blob:二进制对象

clob:字符大对象

char和varchar的区别:

char(n):表示最长可以写n个字符,如果我们写的字符少于3个,那也按照3个字符去存储

varchar(n):实际写了几个字符就是几个字符,但是不能超过n个

注 :char效率更高,大部分情况下用varchar

3.2创建表

语法:create table表名(字段1 字段类型1,字段2 字段类型2…);

例 create table student(sid int,sname varchar(5),age char(10),sex char(1));

3.3看数据库中是否有新表

语法:show tables;

3.4在表中插入数据

语法:insert into 表名(字段1,字段2…)values(字段1的值1,字段2的值2…);

注意:只要字段顺序和值顺序应保持一致,那么插入的数据不会受到影响

#案例:insert into student(sid,sname,age,sex)values(1,’世纪’,’1970-1-1′,’男’);

insert into student(sid,sname,age,sex) values(1,’世纪’,’1970-1-1′,’男’);

插入部分字段和对应的值语法:insert into student(sid,sname)values(003,’李四’);

省略字段添加数据语法:insert into表名values(值1,值2…);

注意 这种添加数据的方式必须保证所有字段的数量和值的数量相等

insert into student values(002,’张六’,’19’,’女’);

添加多条数据语法:insert into 表名values(值1,值2…),(值1,值2…);

=insert into student(字段1 字段类型1,字段2 字段类型2…) values(值1,值2),(值1,值2…);

3.5查询表中的数据

3.51 查询表中所有数据

语法:select*from 表名;

select*from 表名 where 条件;

3.52 查询表中部分数据

语法:select 字段1,字段2… from 表名;

select 字段1,字段2… from 表名 where 条件;

3.6 查询表的结构:

语法:desc 表名

共有六个字段:

字段名称,字段类型,可不可以为空,主外键,默认值,附加条件

+——-+————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————+——+—–+———+——-+
| sid | int | YES | | NULL | |
| sname | varchar(5) | YES | | NULL | |
| age | char(10) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+——-+————+——+—–+———+——-+

3.7 删除表

drop table 表名;

注 如果在会被删除,不在 会错

drop table if exists 表名;

注 如果在会被删除,不在 不会错

3.8 修改表中数据

语法格式:

update 表名 set 字段名1=新值1,字段2=新值2

#案例:将emp表中的King改成世纪

update emp11 set ename =’世纪’ where ename=’king’;

#练习:将smith的员工编号改为6666

update emp11 set empno=’6666′ where ename=’smith’;

3.9 修改默认值

创建表时给特定字段设置默认值

语法:create table 表名(字段1 字段类型,字段2 字段类型,字段3 字段类型 default ‘ ‘…);

3.10 表的复制和批量插入

语法:create table 新表名 as select*from 旧表名;

*指的是所有字段,如果要插入部分字段,如下图语法:create table 新表名 as select*from 旧表名;

3.11 删除数据/表(慎用!!)

删数据

语法:delete from 表名 where 条件;

注意 不写where删除整个表数据,但表结构还在;

删表

语法:drop table 表名;

3.12 表结构的修改

3.12.1 修改表的字段

语法:alter table 表名 modify 字段 字段类型;

案例:将emp表中的ename字段从varchar改成char(20);

alter table emp modify ename char(20);

(desc 表名:查看表结构)

3.12.2 添加字段

语法:alter table 表名 add column 字段 字段类型;

案例:给emp表加一个salrank字段 字段类型为int

alter table emp add column salrank int(10);

3.12.3 删除字段

语法:alter table 表名 drop column 字段;

案例:删除emp表的salrank字段

alter table emp drop column salrank;

3.12.4 修改表名

语法:alter table 旧表名 rename to 新表名;

案例:将emp表改成century表

alter table emp rename to century;

3.12.5 修改字段名

语法:alter table 表名 change column 旧字段名 新字段名 字段类型;

案例:将emp表中的empno改成id

alter table emp change column empno id int;

第四章 约束

约束会带索引的一种

约束:为了保证数据的完整性,合法性,有效性。比如id不能重复,name不能为空

1.非空约束(not null):被约束的字段不能为空

2.唯一性约束(unique):被约束的字段不能重复

3.主键约束(primary key):(有带索引) unique+not null被约束的字段不能重复且不能为空

4.外键约束(foreign key)

5.检查约束(check)

4.1非空约束(not null)

被约束的字段不能为空

创建stu表,添加id和name字段,我们给id字段添加非空约束

create table stu (id int not null, name varchar(5));

4.2唯一性约束(unique)

被约束的字段不能重复

创建stu表,添加id和name字段,我们给name字段添加唯一性约束

create table stu(id int,name vachar(10) unique);

insert into stu values(1,’张三’);

被唯一性约束的字段具有唯一性

null可以有多个,因为null是一种特殊的数据类型

4.3主键约束(primary key)

unique+not null被约束的字段不能重复且不能为空

练习:创建stu表添加字段id/name/age,给id字段添加主键约束

create table stu(id int primary key,name varchar(5),age int);

4.3.1主键自增(primary key auto_increment)

练习:创建stu 表,包含字段id和name,并给id字段添加主键自增约束

create table stu(id int primary key auto increment,name varchar(5));

4.4父子表

假如有一个业务需求,让你设计一张表来维护学生信息

4.4.1数据冗余

数据冗余:指一个字段在多个表里重复出现。

缺点:会造成数据异常和损坏,应该从设计上避免,同时浪费空间,因此要使用外键,使得数据冗余降到最低

4.4.2父表与子表关系

1.子表中字段引用的是父表

2.在创建表时,应该先创建父表,再创建子表

3.在添加数据时,先添加父表中的数据,再添加子表中的数据

4.在删除表中数据的时候,先删子表中的数据,再删父表中的数据

5.删除表的时候,先删子表,再删父表

(爸爸为大,爸爸为尊)

#案例:

先创建父表class

create table class(classno int primary key,classname varchar(10));

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

再创建stu表

create table stu(id int,name varchar(5),classno int,foreign key(classno) references class(classno));

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

往父表中插入数据

insert into class values(101,’清华附中’),(102,’北大附中’);

往子表中插入数据

insert into kid values(2001,’张三’,101),(2002,’李四’,102),(2003,’王五’,102);

如果在子表中的外键字段中插入的数据在父表中没有,就会报错。

insert into bro values(2001,’张三’,101),(2002,’李四’,102),(2003,’王五’,103);

【报错提示:出现的原因即外键中的数据在父表的主键中不存在】

#问题:外键字段引用其他表(父表)中的字段时,被引用的字段必须是有主键约束的吗?

#答:不一定。但是必须保证有唯一性约束(unique),比如父表班级表中的classno,如果要是有重复的值,那么在子表学生信息表中的classno就找不出唯一对应的值

第五章 数据查询

5.1查询字段

5.11查询单个字段

select 字段1 from emp;

5.12查询多个字段

select 字段1,字段2… from emp;

5.13起别名

给字段名起别名直接在字段后面加个空格然后写名字就好,或者加个as

select 字段1 字段1 名字,字段2 字段2 名字 from emp;

select 字段1 as 字段1 名字,字段2 as 字段2 名字 from emp;

5.2查询条件

条件查询必须用到where语句中,where必须放在from的 后面

语法:select 字段…from 表名 where 条件

一定要记住!!执行顺序是from,然后where(相当于筛选),最后select

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

5.2.1 等于 =

#案例:查询月薪为5000的员工信息

select ename,sal from emp where sal=5000;

5.2.2 不等于 !=或<>

#案例:查询员工部门编号不为30的员工姓名和员工薪资

select ename,sal from emp where deptno !=30;

select ename,sal from emp where deptno <>30;

5.2.3 大于 >

#案例:查询工资大于2000的员工编号和员工姓名

select ename,empno from emp where sal>2000;

5.2.4 小于<

#案例:查询工资小于2000的员工编号和员工姓名

select ename,empno from emp where sal<2000;

5.2.5 在…之间 包括端点值 between…and…

等同于 … 大于等于>= … and … 小于等于<=…

#查询工资在1250 到 2850的员工姓名和工资

select ename,sal from emp where sal between 1250 and 2850;

select ename,sal from emp where sal >=1250 and sal <=2850;

5.2.6 null 的使用

#案例:找出emp表中那些人没有津贴,输出员工姓名

select ename from emp where comm is null or comm = 0;

#补充 null和0的区别:

null是取不到,没有值;0是取得到值,值为0

5.2.7 and/or的使用

#找出薪水大于2000,并且部门编号为20或者30的员工姓名

select ename from emp where sal>2000 and (deptno=20 or deptno=30);

易错点:select ename from emp where sal>2000 and deptno=20 or deptno=30,此书写是错的; 记得加括号,此为从左到右运算

5.28 in/not in

#找出工作岗位是经理或者销售的员工姓名和入职日期

select ename,hiredate from emp where job = ‘manager’ or job = ‘salesman’;

select ename,hiredate from emp where job in (‘manager’ , ‘salesman’);

#案例:找出部门编号不是10或者20的员工名称

select ename from emp where deptno!=10 and deptno!=20;

select ename from emp where deptno not in (10,20);

5.2.8模糊查询

% 任意多个字符

_ 任意单个字符

#案例1:找出姓名中带‘A’的员工姓名

select ename from emp where ename like ‘%A%’;

#注意:’A%’表示的是名字的首字母为A

#案例2:找出名字第二个字母为A的员工姓名

select ename from emp where ename like ‘_A%’;

#练习:找出名字第三个字母为A的员工姓名

select ename from emp where ename like ‘__A%’;

#练习:找出名字倒数第二个字母为‘R’的员工姓名

select ename from emp where ename like ‘%R_’;

第六章:数据排序

6.1 升降序

order by默认升序

升序:asc

以上两个都可以为作为升序

降序:desc

语法:select 字段 from 表名 where 条件 order by 排序字段 升序/降序;

#案例:按照员工的工资升序排序,并输出所有的员工信息

select*from emp order by sal asc;

select*from emp order by sal;

此处并未要求需要筛选或者过滤掉某种信息,所以不用where

#案例:找出部门编号为30的员工信息,并按照薪水降序排序

select * from emp where deptno = 30 order by sal desc;

#练习:按照员工的工资降序排序并输出职业为经理或者销售的员工信息。

select * from emp where job in (‘manager’,’salesman’) order by sal desc;

6.2 多字段排序

案例:按照员工的部门编号降序,如果编号相同那么按照薪资升序排序,最后显示所有员工信息

select * from emp order by deptno desc,sal asc ;

#有的同学说可不可以这样↓

select * from emp order by sal asc,deptno desc ;

#答:虽然结果不会报错,但是这样写表示的是先按照工资升序排序,如果工资相同再按照部门编号降序

第七章:聚合函数(分组函数)

聚合函数语法格式:select 聚合函数(字段/*) from 表名; ———-不可以!!直接!!使用在where子句中

7.1 count 计数

#求emp表中一共有多少人

select count(*) from emp;

select count(*),字段1 from emp group by 字段1;

#聚合函数一般和group by连用时,分组除聚合函数外的字段都需要加在group by后面

select count(ename) from emp;

7.2 sum 求和

#所有员工的工资和

select sum(sal) from emp;

注:()里面不能加*

7.3 avg 平均值

#求所有员工的平均薪资

select avg(sal) from emp;

注:()里面不能加*

7.4 max 最大值

#求最高工资

select max(sal) from emp;

#求出最晚的员工入职时间

select max(hiredate) from emp;

7.5 min 最小值

select min(sal) from emp;

聚合函数一共有五个,又名:多行聚合函数

特点:处理多行数据,返回一行结果(如果不分组的话,为单行函数);不计入字段数量

7.6 聚合函数直接忽略null,不忽略0

#公司一共多少员工

select count(*) from emp;

7.7 count(*)和count(字段)的区别

count(*):是统计表中有多少条(行)数据

count(字段):统计这个字段有多少个非空值

#count(字段1,字段2…),给各个字段都计数,这样书写也可以

7.8 任何数据+null=null

9999/null=null

#案例:emp表中每个员工的总收入是多少?

select sal +comm from emp;

#注意:上面的sql不能求出员工的总收入因为没有加班费的最后计算结果都为null

解决方法:使用ifnull函数

ifnull:空处理函数(可能会为null的字段,被当做什么处理)

#案例:emp表中每个员工的总收入是多少?

select sal+ifnull(comm,0) 总收入 from emp;

#练习:emp表中所有员工的年薪是多少?

select (sal+ifnull(comm,0))*12 年薪 from emp;

7.9 分组函数 不可以!直接!使用在where子句中

#求出员工工资大于平均工资的员工姓名

mysql> select ename from emp where sal>avg(sal);

ERROR 1111 (HY000): Invalid use of group function

#会报错,如何不报错,后面讲!

7.10 聚合函数可以组合使用

案例:输出emp表中的最高工资,最低工资和平均工资。

select max(sal) 最高工资,min(sal) 最低工资,avg(sal)平均工资 from emp;

7.11group by 按照某个字段进行分组

语法:select 字段 from 表名 group by 分组字段;

#按照job对emp表中的数据进行分组,显示emp表中有哪些工作

select job from emp group by job;

#按照deptn对emp表中的数据进行分组,显示emp表中有哪些部门编号

select deptno from emp group by deptno;

注意:一般来说我们不会在一个sql里去单独使用group by,单独使用的效果和去重distinct()无异

那分组函数用在哪里/怎么用呢?往下看

#和聚合函数一起使用

#案例:找到每个工作岗位的最高薪资

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

#练习:找到每个部门的最高薪资

select max(sal),empno from emp group by empno;

7.12 having 的应用

语法:select 字段 from 表名 where 条件 group by分组字段 having 过滤条件;

having在某种情况下和where的意思相同,但是having能用在group by的后面,且having后面既可以接条件,也可以接聚合函数

where 和having 的区别:过滤对象不同

where是初筛-选出满足条件的;having是进一步细分筛选

或者说,where子句是对被选择的列进行过滤,having子句是对group by 子句所产生的组进行过滤

#案例找出每个部门的最高薪资,要求显示部门最高薪资大于3500的

select max(sal),empno from emp group by empno having max(sal)>3500;

一个完整语句的执行顺序:

select 5

from 1

where 2

group by 3

having 4

order by 6

limit 7

注意:select中,不能查询没有被分组(聚合)的字段

如:

select 字段1 from 表 group by 字段1; —–√

select 字段1 字段2 字段3 from 表 group by 字段1;—-×

select 字段1 字段2 字段3 from 表 group by 字段1 字段2 字段3; —√

例如:找出每个月工作岗位的最高薪资,同时输出员工姓名

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

+———-+——–+——-+
| max(sal) | deptno | ename |
+———-+——–+——-+
|  3000.00 |     20 | SMITH |
|  2850.00 |     30 | ALLEN |
|  5000.00 |     10 | CLARK |
+———-+——–+——-+

#虽然可能不会报错,但是结果必然是错的。比如上表clark的工资实际不是5000

修正思路1:select max(sal),deptno from emp group by deptno;

修正思路2:select max(sal),deptno,ename from emp group by deptno,ename;—-注:5种聚合函数不算字段

7.13多字段联合分组

#案例,找出每个部门不同工作岗位的最高薪资,输出部门编号/工作岗位/最高薪资

#提示:在sql题中,遇到‘每个’,‘不同’这两个词的时候,要立马想到分组 group by

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

7.14 distinct 数据去重

案例:emp表中工种有哪些

select distinct(job) from emp;

练习:emp表中薪资有哪些

select distinct(sal) from emp;

#补充:涉及到id之类的,distinct可以直接写上去

第八章:单行函数

返回值是一条记录的时候,成为单行函数

单行函数包括五种:

1.字符函数(有6个)

2.数字函数(有4个)

3.日期函数(有6个)

4.其他函数

5.流程控制

8.1字符函数(有6个)

8.1.1 length 长度

#查询’smith’的长度

select length (‘smith’) from dual;

+—————–+
| length(‘smith’) |
+—————–+
|               5 |
+—————–+

#注:dual是一个虚拟表,只有一行一列

select length(‘世纪’) from dual;

+—————–+
| length (‘世纪’) |
+—————–+
|               4 |
+—————–+

#一个汉字占3/2个字节,一个字母占一个字节

#至于汉字占2还是3个字符,由字符集决定。

select length(”) from dual;

+——————+
| length(”) |
+——————+
|                6 |
+——————+

#一个数字占一个字节

8.1.2 concat 字符串拼接

语法:select concat(字段1,链接符号,字段2) from 表名;

#将emp表中的员工姓名和对应的职业链接输出,并使用下划线链接

select concat(ename,’_’,job) from emp;

+———————–+
| concat(ename,’_’,job) |
+———————–+
| SMITH_CLERK           |
| ALLEN_SALESMAN        |
| WARD_SALESMAN         |
| JONES_MANAGER         |
| MARTIN_SALESMAN       |
| BLAKE_MANAGER         |
| CLARK_MANAGER         |
| SCOTT_ANALYST         |
| KING_PRESIDENT        |
| TURNER_SALESMAN       |
| ADAMS_CLERK           |
| JAMES_CLERK           |
| FORD_ANALYST          |
| MILLER_CLERK          |
+———————–+

select concat(ename,’_’,job) as 姓名_职业 from emp;

+—————–+
| 姓名_职业       |
+—————–+
| SMITH_CLERK     |
| ALLEN_SALESMAN  |
| WARD_SALESMAN   |
| JONES_MANAGER   |
| MARTIN_SALESMAN |
| BLAKE_MANAGER   |
| CLARK_MANAGER   |
| SCOTT_ANALYST   |
| KING_PRESIDENT  |
| TURNER_SALESMAN |
| ADAMS_CLERK     |
| JAMES_CLERK     |
| FORD_ANALYST    |
| MILLER_CLERK    |
+—————–+

8.1.3大小写转换

1.转大写upper

2.转小写lower

#案例 将‘hello world’转成大写

select upper(‘hello world’) from dual;

+———————-+
| upper(‘hello world’) |
+———————-+
| HELLO WORLD          |
+———————-+

#练习:将’HELLO WORLD‘转成小写

select lower(‘HELLO WORLD’) from dual;

+———————-+
| lower(‘HELLO WORLD’) |
+———————-+
| hello world          |
+———————-+

8.1.4 substr 字符截取函数

语法:substr(字符串,开始的索引位置,取得长度)

如果不写取的长度,默认取到最后

mysql中索引从左往右由1开始

例子:’人生苦短,我爱mysql’取出mysql

select substr(‘人生苦短,我爱mysql’,8,5) from dual;

+———————————-+
| substr(‘人生苦短,我爱mysql’,8,5) |
+———————————-+
| mysql                            |
+———————————-+

#练习:从 ’人生苦短,我爱mysql ’ 中取出苦短

select substr(‘人生苦短,我爱mysql’,3,2) from dual;

+———————————-+
| substr(‘人生苦短,我爱mysql’,3,2) |
+———————————-+
| 苦短                             |
+———————————-+

#案例:把emp表中所有员工名字中的第一个字母提取出来

select substr(ename,1,1) from emp;

+——————-+
| substr(ename,1,1) |
+——————-+
| S                 |
| A                 |
| W                 |
| J                 |
| M                 |
| B                 |
| C                 |
| S                 |
| K                 |
| T                 |
| A                 |
| J                 |
| F                 |
| M                 |
+——————-+

其实,索引也可以从后往前找,从-1开始

#案例:把emp表中所有员工名字中的最后一个字母提取出来

select substr(ename,-1,1) from emp;

+——————–+

| substr(ename,-1,1) |

+——————–+

| H |

| N |

| D |

| S |

| N |

| E |

| K |

| T |

| G |

| R |

| S |

| S |

| D |

| R |

+——————–+

#练习:把emp表中所有员工名字中的倒数第二个字母提取出来

select substr(ename,-2,1) from emp;

+——————–+

| substr(ename,-2,1) |

+——————–+

| T |

| E |

| R |

| E |

| I |

| K |

| R |

| T |

| N |

| E |

| M |

| E |

| R |

| E |

+——————–+

8.1.5 replace 替换

语法:select replace(字符串,被替换的值,替换后的值) from dual

#案例:将’人生苦短,我爱mysql‘中的mysql换成python

select replace(‘人生苦短,我爱mysql’,’mysql’,’python’) from dual;

+————————————————+
| replace(‘人生苦短,我爱mysql’,’mysql’,’python’) |
+————————————————+
| 人生苦短,我爱python                            |
+————————————————+

8.1.6 trim 去除空格

#案例:去除’ 世纪 ‘的空格

select trim(‘ 世纪 ‘) from dual;

+———————+
| trim(‘    世纪   ‘) |
+———————+
| 世纪                |
+———————+

ltrim:去左边空格

rtrim:去右边空格

8.2 数学函数(有4个)

8.2.1 round 四舍五入

–保留整数

select round(3.5555);

+—————+
| round(3.5555) |
+—————+
|             4 |
+—————+

–保留小数点后两位

select round(3.5555,2);

+—————–+
| round(3.5555,2) |
+—————–+
|            3.56 |
+—————–+

–保留整数到百位

select round(12345.44,-2);

+——————–+
| round(12345.44,-2) |
+——————–+
|              12300 |
+——————–+

8.2.2 truncate 截取  无四舍五入
select truncate(1.19999,1);
+———————+
| truncate(1.19999,1) |
+———————+
|                 1.1 |
+———————+

select truncate(12345.9,-3);
+———————–+
| truncate(12345.9,-3) |
+———————–+
|                 12000 |
+———————–+

truncate以小数点为端点,从右(+)/ 左(-)第一个数字开始截取:
小数点往右开始为正数,从1开始
小数点从右往左截取,小数点往左开始为负数,从0开始

8.2.3 ceil 向上取整 floor 向下取整

向上取整:返回大于该值的最小整数

select ceil(6.66);

+————+
| ceil(6.66) |
+————+
|          7 |
+————+

select ceil(-0.1);

+————+
| ceil(-0.1) |
+————+
|          0 |
+————+

向下取整:返回小于该值的最大整数
select floor(6.55);
+————-+
| floor(6.55) |
+————-+
|           6 |
+————-+

select floor(-6.55);
+————–+
| floor(-6.55) |
+————–+
|           -7 |
+————–+

8.2.4 mod 取余

10/3=3…1,此时取余为1

select mod(10,3);

+———–+
| mod(10,3) |
+———–+
|         1 |
+———–+

8.3 日期函数(有6个)

8.3.1 now() 返回当前的系统日期+时间

select now() from dual;

+———————+
| now()               |
+———————+
| 2022-06-02 09:21:10 |
+———————+

8.3.2 curdate() 返回当前系统的日期

select curdate() from dual;

+————+
| curdate()  |
+————+
| 2022-06-02 |
+————+

8.3.3 curtime() 返回当前系统的时间

select curtime() from dual;

+———–+
| curtime() |
+———–+
| 09:22:29  |
+———–+

8.3.4 year(时间字段) 获取年份,month(时间字段) 获取月份

–获取今年的年份

select year(now()) from dual;

+————-+
| year(now()) |
+————-+
|        2022 |
+————-+

–获取现在的月份

select month(now()) from dual;

+————–+
| month(now()) |
+————–+
|            6 |
+————–+

–获取现在月份名称

select monthname(now()) from dual;

+——————+
| monthname(now()) |
+——————+
| June             |
+——————+

补充:日day 小时hour 分钟minute 秒second

8.3.5 str_to_date 将系统日期转换为字符串格式

语法:str_to_date(字符串格式,字符串的日期格式)

#案例:将字符串’01-01-2022′ 转换成标准的日期格式

select str_to_date(’01-01-22′,’%m-%d-%y’) from dual;

+————————————+
| str_to_date(’01-01-22′,’%m-%d-%y’) |
+————————————+
| 2022-01-01                         |
+————————————+

select str_to_date(’01-01-22′,’%m-%d-%Y’) from dual;

+————————————+
| str_to_date(’01-01-22′,’%m-%d-%Y’) |
+————————————+
| 2022-01-01                         |
+————————————+

#Y要大写,Y小写的话就是年份的后两位。

Y大写时

select str_to_date(’01-01-2022′,’%m-%d-%Y’) from dual;

Y小写时

select str_to_date(’01-01-22′,’%m-%d-%y’) from dual;

8.3.6 date format 将日期格式转换为字符串格式

语法:date_format(日期,具有日期格式的字符串)

案例:将系统日期转换为字符串格式’2022年6月1日

select date_format(‘2022年6月1日’,’%Y年%m月%d日’) from dual;

+——————————————–+
| date_format(‘2022年6月1日’,’%Y年%m月%d日’) |
+——————————————–+
| NULL                                       |
+——————————————–+

8.3.7 TimeStampDiff 时间间隔函数/时间差函数

语法:timestampdiff(间隔类型,前一个日期,后一个日期)

间隔类型有:second秒,minute分,hour时,day天,week周,month月,quarter季度,year年

例如:距离现在差了多少年:timestampdiff(year, 前一个日期, now())

#例题1:给任职日期超过30年的员工加薪10%。

#思路:

update emp set sal=sal*1.1 where 任职日期 > 30年;

即:

update emp

set sal = sal * 1.1

where timestampdiff(year, hiredate, now()) > 30;

#例题2:列出在每个部门工作的员工数量,平均工资,平均服务期限。

#思路:使用外连接。

select d.deptno, count(e.ename) ‘人数’, ifnull(avg(e.sal), 0) ‘平均月薪’, ifnull(avg(timestampdiff(year, e.hiredate, now())), 0) ‘平均服务年数’from emp e right join dept d on e.deptno = d.deptno group by d.deptno;

8.4流程控制函数

8.4.1 case…when…语句

语法格式1

select 字段

case 要判断的字段或者表达式

when 常量1 then 要显示的值1或者语句1

when 常量2 then 要显示的值2或者语句2…

else 要显示的值n或者语句n

end as 别名–最终要显示的数据

from 表名;

#案例:查询员工工资,要求:部门编号为10的上涨0.1倍,部门编号为20的上涨0.2倍,部门编号为30的上涨0.3倍,输出:员工姓名、薪水、部门编号

select ename,sal,deptno, case deptno when 10 then sal*1.1 when 20 then sal*1.2 when 30 then sal*1.3 else sal end as 涨薪后的薪水 from emp;

语法格式2

select 字段

case

when 条件1 then 值1或语句1

when 条件2 then 值2或语句2

else 要显示的值n或者语句n

end as 别名–最终要显示的数据

from 表名

注:条件中可以加函数

#案例:查询员工的工资情况,如果工资大于3000,则显示等级A,如果工资大于2000,则显示等级B,如果工资大于1000,显示等级C,否则显示等级D。要求输出:员工姓名,薪资,和薪资等级

select ename,sal,case when sal >=3000 then ‘A’ when sal>=2000 then ‘B’ when sal>=1000 then ‘C’ else ‘D’ end as salgrade from emp;

即:

select ename,sal,

case

when sal >=3000 then ‘A’

when sal>=2000 then ‘B’

when sal>=1000 then ‘C’

else ‘D’

end as salgrade

from emp;

注: case后面加不加字段,是根据when后面接的是常量,还是条件决定的 。

–常量就是可以具体表达出来的某个数据,条件可能会展示出来一个范围

–何老师上的课

case … when 条件1 then 结果1

when 条件2 then 结果2

when … then …

else 以上条件的否定结果

end as 别名…

#’case…when…else…end as 别名’中的’as 别名’中的as一定要写

#when后面可以加聚合函数

语法:

select case when (条件1) then (结果1) else (以上条件的否定结果) end as 别名 from 表名;

select 字段, case when (条件1) then (结果1) else (以上条件的否定结果) end as 别名 from 表名;

#例如:

select case when 姓名=’李世继’ then’女’ else ‘暂不展示’ end as 性别 from 点名表;

即:

select case when 姓名=’李世继’ then’女’

else ‘暂不展示’

end as 性别

from 点名表;

#案例:查询部门号为20的个数

select sum(部门号) from (select deptno,case when deptno=20 then 1 else 0 end as 部门号 from emp) a;

#案例:

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

分4类,分别是不及格,及格,良,优秀的数量

8.4.2 if函数

在if函数中有三个参数

语法格式:

if(表达式,True时返回的结果,False时返回的结果)

mysql> select if(10>5,’大’,’小’);
+——————–+
| if(10>5,’大’,’小’) |
+——————–+
| 大                 |
+——————–+

#案例:查询emp表中的员工是否有津贴,如果有津贴返回’happy’,没有返回’sad’,要求输出员工的姓名,津贴和心情

select ename,comm,if(comm>0,’happy’,’sad’) from emp;

#优化:

select ename,comm,if(comm!=0 and comm is not null,’happy’,’sad’) 心情 from emp;

–何老师上的课

if(条件,条件满足时的结果,条件不满足的结果)

#案例:工资大于1000为工资高,否则为工资低

select sal,if(sal>1000,’工资高’,’工资低’) from emp;

第九章:链接查询

链接查询:也可以叫做跨表查询,需要关联多个表进行查询。

9.1 SQL92语法 (自链接)不常用不推荐

案例:显示每个员工的姓名和部门名称

select ename,dname from emp,dept;

以上输出不正确,输出了56条数据,就是两个表记录的乘积,这种情况我们称为 笛卡尔积

笛卡尔积的场景,例如:

1 4

2 5

3 6

(14 15 16 24 25 26 34 35 36 共有9种可能)

出现错误的原因:没有指定链接条件

#指定链接条件:emp.deptno=dept.deptno

正确:select ename,dname from emp,dept where emp.deptno=dept.deptno;

以上结果输出正确,因为假如了正确的链接条件,即:emp.deptno=dept.deptno,上面的查询也可以成为‘内链接’,只查询相等的数据(链接条件相等的数据)

#练习:查询员工名字和工作地点

#链接条件 emp.deptno=dept.deptno

select emp.ename,dept.loc from emp,dept where emp.deptno=dept.deptno;

#我们还可以给表起别名(在from后的表名后面先空格,再用单字母起名)

select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno;

即:

select

e.ename,d.loc

from

emp e,dept d

where

e.deptno=d.deptno

#查询员工姓名和直属领导姓名

select e.ename 员工姓名,m.ename 直属领导姓名 from emp e,emp m where e.mgr=m.empno;

即:

select

e.ename 员工姓名,m.ename 直属领导姓名

from

emp e,emp m

where

e.mgr=m.empno

#上面链接的方式称为‘自连接’,因为实际只有一张表,具体查询方法就是把一张表看成两张表,如上面的例子,第一个表时emp e 代表员工表,第二个emp m代表了领导表

9.2 SQL99语法(自链接)

语法:select 字段 from 表a join 表b on 链接条件 where 查询条件

注意:

from 和 join 里的字段位置前后可以换;

内连接 join,取得是交集

#案例:显示薪资大于2000的员工姓名并显示薪水和部门名称

92写法:(不推荐)

select e.ename,e.sal, d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;

99写法:(常用写法)

select e.ename,e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;

92和99结合写法:

select e.ename,e.sal, d.dname from emp e join dept d where e.deptno=d.deptno and e.sal>2000;

#sql92和sql99语法区别:sql99语法能做到链接条件和查询条件分离,特别是多个表链接的时候会比sql92结构清晰

9.3 left/right (outer) join(外链接)

例如:

表1 表2

世纪(有宿舍) A

明飞(有宿舍) B

顺哥(无宿舍) C

(表1 left join 表2) (表1 right join 表2)

世纪A 世纪A

明飞B 明飞B

顺哥C Cnull

#案例:查询员工姓名,并显示员工部门名称和薪资,如果部门没有员工,该部门也要显示出来

select e.ename 员工姓名,e.sal 薪资,d.dname 员工部门 from dept d left join emp e on e.deptno=d.deptno;

select e.ename 员工姓名,e.sal 薪资,d.dname 员工部门 from emp e right join dept d on e.deptno=d.deptno;

即:

select

e.ename 员工姓名,e.sal 薪水,d.dname 员工部门

from

emp e

right join

dept d

on

e.deptno=d.deptno;

+———-+———+————+
| 员工姓名 | 薪资    | 员工部门   |
+———-+———+————+
| MILLER   | 1300.00 | ACCOUNTING |
| KING     | 5000.00 | ACCOUNTING |
| CLARK    | 2450.00 | ACCOUNTING |
| FORD     | 3000.00 | RESEARCH   |
| ADAMS    | 1100.00 | RESEARCH   |
| SCOTT    | 3000.00 | RESEARCH   |
| JONES    | 2975.00 | RESEARCH   |
| SMITH    |  800.00 | RESEARCH   |
| JAMES    |  950.00 | SALES      |
| TURNER   | 1500.00 | SALES      |
| BLAKE    | 2850.00 | SALES      |
| MARTIN   | 1250.00 | SALES      |
| WARD     | 1250.00 | SALES      |
| ALLEN    | 1600.00 | SALES      |
| NULL     |    NULL | OPERATIONS |
+———-+———+————+

左链接能完成的功能,右链接一定可以完成

小结:

链接分类:

1.内连接:

语法:表1 innner join 表2 on 链接条件(where查询条件)

注意:做链接查询的时候一定要写上关联条件,inner可以省略

2.外链接:

语法:表1 left/right outer join 表2 on 关联条件(where查询条件)

注意:做链接查询的时候一定要写上关联条件,outer可以省略

左外链接和右外链接的区别:

左链接以左边的表为准和右边的表链接,右表与左表相等和不等的结果都会显示出来,右表符合条件的显示,不符合条件的为null;

右链接以右边的表为准和左边的表链接,左表与右表相等和不等的结果都会显示出来,左表符合条件的显示,不符合条件的为null;

#补充:主表的数据一定会展示,但副表的数据不一定

9.4三张表及以上的链接

案例:查询员工姓名和部门名称和薪资等级

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

即:

select

e.ename,d.dname,s.grade

from

emp e

join dept d on e.deptno=d.deptno

join salgrade s on e.sal betweem s.losal and s.hisal;

+——–+————+——-+
| ename  | dname      | grade |
+——–+————+——-+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+——–+————+——-+

#练习:查询员工姓名、领导名、部门名称、薪资等级

select e.ename, m.ename,d.dname,s.grade from emp e join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;

第十章 嵌套子查询

什么是子查询??

子查询就是嵌套的select语句,可以理解为子查询是一张表、

方法:用此方法时,可以参照英语断句把需求分解再组合

10.1 在where语句中使用子查询

其实就是在where语句中假如select语句

#案例:查询为管理者的员工编号和姓名

(断句:查询哪些是管理者,再查出姓名)

#用之前学过的知识做法:

select distinct(empno) 员工编号,m.ename 管理者姓名 from (select m.empno,m.ename from emp e join emp m on e.mgr=m.empno) m ;

#新思路:

1.首先查询管理者的empno

select distinct(mgr) from emp where mgr is not null;

2.查询员工编号和姓名在1的查询结果之内的

select ename,empno from emp where empno in(select distinct(mgr) from emp where mgr is not null);

==================================

1.首先查询管理者的empno

select mgr from emp;
+——+
| mgr  |
+——+
| 7902 |
| 7698 |
| 7698 |
| 7839 |
| 7698 |
| 7839 |
| 7839 |
| 7566 |
| NULL |
| 7698 |
| 7788 |
| 7698 |
| 7566 |
| 7782 |
+——+

去重

select distinct(mgr) from emp;
+——+
| mgr  |
+——+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+——+

去空值

select distinct(mgr) from emp where mgr is not null;
+——+
| mgr  |
+——+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+——+

上面这六个empno就是六个领导的empno

2.从emp里找到有这六个编号的人名

select ename,empno from emp where empno in(select distinct(mgr) from emp where mgr is not null);
+——-+——-+
| ename | empno |
+——-+——-+
| FORD  |  7902 |
| BLAKE |  7698 |
| KING  |  7839 |
| JONES |  7566 |
| SCOTT |  7788 |
| CLARK |  7782 |
+——-+——-+

============================================

#练习:查询哪些人的薪水高于员工的平均薪水,显示员工号,员工姓名,员工薪水

(断句:查询员工的平均薪水,再查那些人高于平均薪水的员工号,员工姓名,员工薪水)

select empno,ename,sal from emp where sal>(select avg(sal) from emp);

+——-+——-+———+
| empno | ename | sal     |
+——-+——-+———+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+——-+——-+———+

10.2 在from后面使用子查询

可以将子查询看成一张表

#案例:查询为管理者的员工编号和姓名

第一步:先查询管理者去重后的员工编号

select distinct(mgr) from emp where mgr is not null;

+——+
| mgr  |
+——+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+——+———————-m

把它看作临时表起名为 m,这个表有一个mgr字段

#sql92写法(不推荐)

select e.empno,e.ename from emp e,(select distinct(mgr)from emp where mgr is not null) m where m.mgr=e.empno;

#sql99写法(常用)

select e.empno,e.ename from emp e join(select distinct(mgr)from emp where mgr is not null) m on m.mgr=e.empno;

即:

select

e.empno,e.ename

from

emp e

join

(select distinct(mgr) from emp where mgr is not null) m

on

m.mgr=e.empno

#练习:查询各个部门平均薪水等级,需要显示部门号,平均薪水和薪水等级

思路:

1.首先查询各个部门的平均薪水

select avg(sal) pj,deptno from emp group by deptno;

+————-+——–+

| pj | deptno |

+————-+——–+

| 2175.000000 | 20 |

| 1566. | 30 |

| 2916. | 10 |

+————-+——–+—————–a表

2.将部门的平均薪水作为一张a表和salgrade表建立链接,取得等级

+——-+——-+——-+
| GRADE | LOSAL | HISAL |
+——-+——-+——-+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+——-+——-+——-+————–salgrade表

select a.deptno,a.pj,s.grade from (select avg(sal) pj,deptno from emp group by deptno) a join salgrade s on a.pj between s.losal and s.hisal

即:

select

a.deptno,a.pj,s.grade

from

(select avg(sal) pj,deptno from emp group by deptno) a

join

salgrade s

on

a.pj between s.losal and s.hisal

+——–+————-+——-+
| deptno | pj          | grade |
+——–+————-+——-+
|     20 | 2175.000000 |     4 |
|     30 | 1566. |     3 |
|     10 | 2916. |     4 |
+——–+————-+——-+

简单总结:大多数情况下,where后面子查询是一个字段(一列,一列的数据,一组值)或者一个值;from后面的子查询是一个表。

10.3 select语句中使用子查询(了解)

#查询员工信息显示员工所属部门名称和员工名

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

#第二种:select语句中使用子查询

select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

第十一章:limit分页

语法:select 字段 from 表名 where 条件 group by 字段 order by 字段 limit m,n;

m:记录开始的索引值,索引值是从0开始的,表示第一条记录

n:表示从m+1条开始的记录,取n条

#案例:取emp表中数据的第四和第五条

select*from emp limit 3,2;

#查询工资前五名的员工信息

select*from emp order by sal desc limit 0,5;

select*from emp order by sal desc limit 5;

#查询工资在5-10名的员工信息

select*from emp order by sal desc limit 4,6;

#查询工资在3-8名的员工信息

select*from emp order by sal desc limit 2,6;

补充一点:如果m为0,可以省略

#例如:查询工资前五名的员工信息

select*from emp order by sal desc limit 5;

第十二章 union

union可以将结果相加合并

#找出emp表工作岗位是销售或经理的员工名

第一种 or

select ename from emp where job =’manager’ or job=’salesman’;

第二种 in

select ename from emp where join in (‘manager’,’salesman’);

第三种 union

select ename from emp where job=’manager’

union

select ename from emp where job=’salesman’;

1.字段类型数量必须一致

(字段类型:int,float,date,char,varchar,numeric,bit)

2.select时,字段顺序要保持完全一致

union 去重连接

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

union all 不去重连接

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

#union 上下拼,join 左右拼

第十三章 窗口函数 over

注:针对排名,如成绩排名,薪资排名等

语法:

select 字段, 分析函数()over(partition by 字段 order by 字段) from 表名;

13.1 分析函数

分析函数分为两类:排名类和聚合类

13.1.1 排名类与over()连用

1.row_number() :

按照值排序产生一个自增编号,不会重复1,2,3,4,5…(不用于成绩类排名)

案:1:给emp表中的sal字段进行降序排序,并添加一个自增序号

select sal,row_number()over(order by sal desc) 薪资排名 from emp;

案例2:统计每个部门的sal(薪资排名),并添加一个自增编号

select sal,deptno,row_number()over(partition by deptno order by sal desc) 薪资排名 from emp;

2.rank() :

跳跃排序,按照值排序产生一个自增编号,值相等时这个编号会重复,同样产生空位 1,2,3,4,4,4,7

案例:给emp表中sal字段进行降序排序,输出名次,薪资相同排名相同

select sal,rank()over(order by sal desc) 薪资排名 from emp;

select sal,deptno,rank()over(partition by deptno order by sal desc) 薪资排名 from emp;

3.dense_rank() :

连续排序,按照值排序产生一个自增编号,值相等时会重复,但是不会产生空位 1,2,2,3,3,3,3,4

案例:给emp表中的sal字段进行降序排序,输出名次,要求不许有间隔

select sal,dense_rank()over(order by sal desc) 薪资 排名 from emp;

select sal,deptno,dense_rank()over(partition by deptno order by sal desc) 薪资排名 from emp;

13.1.2 聚合类与over()连用

聚合函数:

1.count 计数

–案例1:统计emp表中记录的数量并在随后新增一个字段显示

select*,count(*)over() 总人数 from emp;

–案例2:统计emp表中每个部门的人数并在随后新增一个字段显示

select*,count(*)over(partition by deptno) 部门人数 from emp;

–案例3:统计emp表中记录的数量,并根据empno进行累计计数

select*,count(*)over(order by empno) 总人数 from emp;

–案例4:按照deptno进行分组,要求显示每个组内根据empno进行累计计数

select*,count(*)over(partition by deptno order by empno) 部门人数 from emp;

2.sum 求和

3.avg 平均

4.max 最小

5.min 最大

13.2 partiton by

用法和 group by 一样,只能在窗口函数中使用,搭配分析函数时,分析函数按照每一组去进行计算

13.3 over() 窗口函数

注意 窗口函数中:可以不分组,可以不排序

第十四章 索引

14.1 索引原理

索引被用来快速找出在一个列上用一特定值的行。

如果没有索引,Mysql不得不首先从第一条记录开始,找出相关的行然后读完整个表,表越大,花费的时间越多。

比如:我们查询工资大于1500 的薪资

select sal from emp where sal>1500;

+———+
| sal     |
+———+
| 1600.00 |
| 2975.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 3000.00 |
+———+

现在我们没有使用索引,找到这些记录是通过全表查找

explain select sal from emp where sal>1500;

+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    33.33 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

Extra

Using where

什么时候需要给表中字段添加索引?

1.表中该字段数据量庞大

2.经常被查询,经常出现在where子句中的字段

3.经常被DML(delete insert update)操纵的字段不建议添加索引

优点:索引相当于一本书的目录,主键会自动添加索引,所以我们以后查询的时候通过主键查询效率会更快

14.2 创建索引格式

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

–案例 给emp表中的sal字段添加一个索引,索引名为sindex

create index sindex on emp11(sal);

14.3 查看索引

show index from 表名;

–查看emp表中的索引

+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
| emp11 |          1 | sindex   |            1 | SAL         | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+

添加索引后再来查询工资大于1500的薪资

select sal from emp where sal>1500;

explain select sal from emp where sal>1500;

+—-+————-+——-+————+——-+—————+——–+———+——+——+———-+————————–+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                    |
+—-+————-+——-+————+——-+—————+——–+———+——+——+———-+————————–+
|  1 | SIMPLE      | emp   | NULL       | range | sindex        | sindex | 9       | NULL |    7 |   100.00 | Using where; Using index |
+—-+————-+——-+————+——-+—————+——–+———+——+——+———-+————————–+

此时查询结果出现了:Using index

14.4 删除索引

drop index 索引名 on 表名;

案例:删除emp表中的索引sindex

drop index sindex on emp;

证明:

show index from emp;

+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
| emp   |          0 | PRIMARY  |            1 | EMPNO       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+

第十五章 视图

老板

一张表

奶茶名字 售价 甜度 成本 受欢迎等级 顾客评价 杯盈利

针对不同的对象,展示不同的字段结果

15.1 什么是视图

视图是站在不同的角度去看待同一份数据,它是一张虚拟表,本身不存数据,简单说就是获取想要看到的和使用的表的局部数据

视图的好处:

1.访问数据表的简单

2.可用来对不同用户显示不同的表中内容

视图的作用:

1.提高效率

2.隐藏表的实现细节

3.提供了数据的独立性,让用户不知道数据来源是什么

15.2 视图的创建和删除

创建视图:

create view 视图名称 as select 字段 from 表名;

删除视图:

drop view 视图名称;

15.3 视图的增删改

对视图的增删改会影响到原表中的数据,有一些业务需要通过视图的形式对原表中的数据进行增删改,但是又不能直接访问原表

–将emp表复制出一个emp1表

create table emp1 as select*from emp;

select*from emp1;

–给emp表创建视图eview 包含字段empno,enamel,job,sal

create view 视图名称 as select 字段 from 表名;

create view eview as select empno,ename,job,sal from emp;

–查看视图中的数据

select*from eview;

-修改视图中姓名为smith工资为90000

update eview set sal=90000 where ename=’smith’;

–查看原表中的数据(原表的smith薪资已经通过视图eview被修改)

select*from emp1;

Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)

–删除视图

drop view eview;

–删除emp1

drop table emp1;

第十六章 存储引擎

16.1 存储引擎的使用

数据库中的每个表都(在被创建的时候)被指定了存储引擎来处理

服务器可用的引擎依赖于以下因素:

1.mysql版本

2.服务器在开发时如何配置

3.启动选项

16.2 查看存储引擎

show engines G;

#案例:查看某个表使用了哪个存储引擎

show create table 表名G;

MyISAM:mysql默认的存储引擎

* 6. row *

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

InnoDB:用于事务的处理,事务的支持

* 7. row *

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

16.3 补充

在创建表时可使用engine选项,为表指定存储引擎

create table 表名(字段 字段类型…) engine=存储引擎名;

如果在创建表时没有指定存储引擎,则该表使用当前默认的存储引擎

(默认的存储引擎和字段的default一样,可以设置修改)

第十七章 事务

我 国庆

10000 0

10000-8000 0+8000–怎么保证这两项操作是同时成功???需要事务

概念:

事务可以保证多个操作的原子性,要么全成功,要么全失败。对于数据库来说事务保证批量的DML(insert delete upate)要么全部成功,要么全部失败。

17.1 事务的四个特征:ACID

a. 原子性(Atomicity):整个事务中所有的操作,必须作为一个单全部完成(全部取消)。

c. 一致性:在事务开始之前和事务开始扣数据库保持一致状态

i. 隔离性:一个事务不会影响其它事务的运行

d. 持久性:在事务完成后,该事务对数据库所做的更改将持久的保存在数据库找那个,不会被回滚

17.2 事务的一些概念

事务:(transaction)

开启事务:start transaction

提交事务:commit

回滚事务:rollback

注意 关于回滚要注意,只能回滚insert,delete和update,不能回滚select(回滚select没意义)

17.3 事务的提交和回滚演示

1.建表

create table stu (id int(5),name varchar(10));

2.查询表中数据

select*from stu;

–结果:Empt set(0.00 sec)

3.插入数据

insert into stu values (1,’张三’);

4.开启事务

start transaction;

–结果:Query OK, 0 rows affected (0.00 sec)

5.插入数据

insert into stu values (2,’世纪’);

6.查看数据

select*from stu;

+——+——+
| id   | name |
+——+——+
|    1 | 张三 |
|    2 | 世纪 |
+——+——+

7.回滚rollback

rollback;

8.查看数据

select*from stu;

+——+——+
| id   | name |
+——+——+
|    1 | 张三 |
+——+——+

17.4 事务的隔离级别

当多个用户并发地访问同一个表时,可能会出现下面的问题:

1.脏读取(read uncommitted)

一个事务开始读取了某行数据,但是另外一个事务已经更新了但没提交,就会出现脏读

2.不可重复读(read committed,r u)

在同一个事务中,同一个读操作对同一个数据前后两次读取产生了不同的结果,这就是不可重复读

3.幻像读(r r)

指在一个事务中以前没有的行,由于其他事务提交而出现新的行

17.5 InnoDB实现的四个隔离级别

1.读未提交

read uncommitted

–隔离级别:窗户纸

–会出现脏读取和可重复读

【a与b可以同时修改数据,也可以彼此同步看到彼此修改的数据】

2.读已提交

read committed

–隔离级别:墙

–可重复读

【a与b可以同时修改数据,直到b进行commit后,a才能看到b修改的数据】

3.可重复读

repeatable read

–隔离级别:钢门

–幻像读

【a与b可以同时修改数据,直到a进行commit后,a才能看到b修改的数据】

4.串行化(序列化)

–隔离级别:完全隔Navicat Premium 8.1.12激活离

设置隔离级别:set global transaction isolation level + 上面的1/2/3/4

【a与b不能同时修改数据,直到a进行commit后,b才能查看数据】

今天的文章
Navicat Premium 8.1.12激活(mysql从零基础到基础熟练)分享到此就结束了,感谢您的阅读。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/105584.html

(0)
编程小号编程小号
上一篇 2024-08-04
下一篇 2024-08-04

相关推荐