MySQL基础

最新推荐文章于 2022-09-08 15:13:10 发布

小安同学nice

最新推荐文章于 2022-09-08 15:13:10 发布

阅读量739

点赞数 2

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/m0_53374870/article/details/123386398

版权

1.把查询结果去除重复记录
注意：原表数据不会被修改，只是查询结果。
去重需要使用一个关键字：distinct
select distinct job,deptno from emp;
distinct只能出现在所有字段的最前方
distinct两个字段之前，表示两个字段联合起来去重。
例：统计一下工作岗位的数量？
select count(distinct job) from emp;
2.连接查询
什么是连接查询？
从一张表单独查询，称为单表查询
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。
这种跨表查询，多张表联合起来查询数据，被称为连接查询。
根据表连接的方式分类：
内分类：
等值分类
非等值分类
自连接

外连接：
	左外连接
	右外连接

全连接

当两张表进行连接查询，没有任何体哦阿健限制的时候，最终查询结果条数，是两张表条件的乘积，
这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个数学现象。）

怎么避免笛卡尔积现象？
连接时加条件，满足这个条件的记录被筛选出来！

怎么避免笛卡尔现象呢？
连接时加条件，满足这个条件的记录被筛选出来！
select ename,dname from emp,dept where emp.deptno = dept.deptno;
给表起别名
select e,d from emp e,dept d where e.deptno = d.deptno;

内连接之等值连接。

案例：查询每个员工所在部门名称，显示员工名和部门名？

条件是：e.deptno = d.deptno

SQL92语法： select 
                e.ename,d.dname 
            from 
                emp e,dept d
            where 
                e.deptno = d.deptno;

inner可以省略不写，若写上，可一眼就能看出这是内连接。
SQL99语法：select
           		e.ename,d.dname
            from
           		emp e
            inner join
           		dept d
            on
            	e.deptno = d.deptno;//条件是等量关系，所以被称为等值连接。

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到where后面。

sql99优点：表连接的条件是独立的，连接之后，乳沟还需要进一步筛选，再往后继续添加where。

例：长出每个员工的薪资等级，要求显示员工名，薪资，薪资等级？

select 
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;//条件不是一个等量关系，称为非等值连接

内连接之自连接

例：查询员工的上级领导，要求显示员工名和对应的领导名？

select
	a.ename as '员工名',b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mar = b.empno;

以上就是内连接中的：自连接，技巧：一张表看作两张表。

外连接（右外连接）

select
	a.ename,d.dname
from
	emp e 
right join
	dept d
on
	e.deptno = d.deptno;
	
//outer可以省略不写，写上可读性强
select
	a.ename,d.dname
from
	emp e right outer join dept d
on
	e.deptno = d.deptno;

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，携带者关联查询左边的表。

在外连接当中，两张表连接，产生了主次关系。

在内连接当中，两张表连接，没有产生主次关系，是平等关系。

思考：外连接的查询结果条数一定是>=内连接的查询结果条数？

正确

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名?

select
	a.ename as '员工名'，b.ename as '领导名'
from
	emp a left join emp b
on
	a.mgr = b.empno;

多表连接

三张表，四张表怎么连接？

语法：
	select
		...
	from
		a
	join
		b
	on
		a和b的连接条件
	join
		c
	on
		a和c的连接条件
	right join
		d
	on
		a和d的连接条件
一跳sql中内连接和外连接可以混合。都可以出现。

例：找出每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级？

select 
	e.ename as '员工名',e.sal,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.hiasl;

子查询

什么是子查询？

select语句中嵌套select语句，被嵌套的select语句称为子查询。

where子句中的子查询

案例：找出比最低工资高的员工姓名和工资？

1.查询最低工资

select min(sal) from emp;

2.查询出工资比800大的人

select ename,sal from emp where sal>800;

3.合并

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

from子句中的子查询

注意：from后面的子查询，可以将子查询的结果当作一张临时表（技巧）

案例：找出每个工作岗位的平均工资的薪资等级？

1.找出每个工作岗位的平均工资
select ename,job avg(sal) from emp where group by job;
2.找出每个工作岗位的平均工资的薪资等级？
select
	t.*,s.grade 
from 
	(select ename,job avg(sal) as avgsal from emp where group by job) t 
join 
	salgrade s 
on 
	t.avgsal between s.losal and s.hisal;

union合并查询集

例：查询工作岗位是MANAGER和SALESMAN的员工？

select ename,job from emp where job = 'manager' or job = 'salesman';
select ename,job from emp where job in('manager','salesman');

select ename,job from emp where job='manager';
union
select ename,job from emp where job='salesman';

union的效率要高一些。对于表连接来说，没链接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。但是union可以减少匹配次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

limit 是将查询结果集的一部分取出来，通常使用在分页查询当中

完整用法：limit startindex，length

starIndex是起始下标，length是长度。

缺省用法：limit 5；表示取前5

例：按照薪资降序，去除排名前五的员工

select ename,sal from emp order by sal limit 0,5;

注意：limit在ouder by 之后执行。

例：去除工资在【3-5】名之间的员工

select ename,sal from emp order by sal desc limit 2,3;

每页显示pagesize条记录第pageno页:limit(pageno - 1) * pagesize,pagesize;

公式：limit(pageno - 1) * pagesize,pagesize;

创建表

语句：create table 表名(

字段一数据类型,

字段二数据类型,

字段三数据类型,

);

创建一个学生表

create table s_student(
	namee varchar(20),
	age char(1),
	sex int
);

删除表

drop table s_student;//当这张表不存在的时候会报错
drop table if exists s_student;//如果这张表存在的话，删除

插入数据 insert

语法格式：

insert into 表名(字段名1，字段名2，字段名3…)values(值1，值2，值3);

注意：字段名和值要一一对应。数量要对应，数据类型要对应。

insert into s_student(no,name,sex,age,email) value(1,'张三','女',20,'zhangsan@123.com');
insert into t_student(no) value(2);
insert into t_student(name) value('wangwu');

注意：insert语句单反是执行成功了，那么必然会多一体哦啊记录。没有给其他字段指定值的话，默认值是null。

create table s_student(
	namee varchar(20),
	age char(1) default 'm',//给定默认值为m
	sex int
);

insert插入日期

数字格式化：format(数字，格式)

//查询员工工资
select ename,format(sal,$999,999) as sal from emp;

str_to_date:将字符串varchar类型转换成date类型

date_format:将date类型转换成具有一定格式的varchar字符串类型。

mysql的日期格式：
%Y	年
%m	月
%d	日
%h	时
%i	分
%s	秒
insert into s_student(id,namee,birth) value(1,'zhangsan',str_to_date('17-10-2002','%d-%m-%Y'));

str_to_date函数可以把字符串varchar站换乘日期date类型数据，通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。

查询的时候可以以某个特定的日期格式展示吗？

date_format

这个函数可以将日期类型转换成特定格式的字符串。

select id,namee,date_format(birth,’%d/%Y/%m’);

输出结果：1 zhangsan 17/2002/10

date_format函数怎么使用？

date_format(日期类型数据,‘日期格式’)

这个函数通常使用在查询日期方面。设置展示的日期格式。

select id,name,birth from t_user;
/*以上的sql语句实际上时进行了默认的日期格式化，自动将数据库中的date类型转换成varchar类型。
并且采用的格式是mysql默认的日期格式：'%y-%m-%d'*/

date和datetime两个类型的区别？

date时短日期：只包括年月日信息

datetime时长日期：包括年月日时分秒信息。

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(30),
	birth date,
	create_time datetime
);
insert into t_user(id,name,birth,create_time) value(1,'zhangsan','2021-10-15','2021-12-6 15:13:24');

在mysql当中怎么获取系统当前时间?

now()函数，并且获取的时间带有时分秒信息，是datetime类型的。

INSERT INTO t_user(id,NAME,birth,create_time) VALUE(2,'lisi','2021-10-15',NOW());

修改 update（DML）

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where条件;

注意：没有条件限制会导致所有的数据全部更新。

//改一下t_user表中2的名字，生日。
update t_user set name='jack',birth='2002-10-17' where id=2;

删除语句 delete(DML)

语法格式：delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除。

//表中2删除
delete from t_user where id=2;

删除所有：delete from t_user;

增（insert）删（delect）改（update）查（select）

insert语句怎么一次插入多条语句？

insert into t_user(id,namee,birth,create_time) value(1,'zhangsan','2021-10-10',now()),(2,'lisi','2021-10-12',now()),(3,'wangwu','2021-10-13',now());

快速创建表？

create table emp2 as select * from emp;

原理：将一个查询结果当作一张表新建！！！这个可以完成表的快速复制！！！表创建出来，同时表中的数据页存在了！！！

create table mytable as select empno,ename from emp where job = 'MANAGER';

快速删除表中的数据？

delete from dept_bak;//这种删除方式比较慢。

delete语句删除数据的原理是？

表中的数据被删除了，但是这个数据在硬盘上的真是储存空间不会被释放！！！这种删除缺点是：删除效率比较低。这种删除的优点是：支持回滚，后悔了可以再恢复数据。

//回滚
start transaction;
delete from dept_bak;
rollback;

truncate语句删除数据的原理？

这种删除效率比较高，表被一次截断，物理删除。这种删除的缺点是：不支持回滚。这种删除的优点：快速。

用法：truncate table dept_bak;

drop table 表名;//删除表

truncate //删除表中的数据，表还在。

约束（*****）

什么是约束？

结束对印的因与单词：constraint

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！

约束的作用就是为了保证：表中的数据有效！！

约束包括哪些？（*****）

非空约束：not null

唯一性约束：unique

主键约束：primary key（简称PK）

外键约束：foreign key（简称FK）

检查约束：check（mysql不支持，oracle支持）

//非空约束not null约束的字段不能为空null
drop table if exists t_vip;
create table t_vip(
	id int,
	ename varchar(20) not null
);
inster into t_vip(id,ename)values(1,'zhangsan'),(2,'lisi');（执行成功）
--------------------------------------------
inster into t_vip(id)value(3);(报错)

唯一性约束：unique

唯一性约束unique约束的字段不能重复，但是可以为null。

drop table if exists t_vip；
create table t_vip(
	id int,
	ename varchar(255) unique，//添加在列后面的约束，叫做列级约束。
	emial varchar(255)
);
insert into t_vip(id,ename,emial) values(1,'zhangsan','zhangsan@123.com'),(2,'lisi','lisi@123.com'),(3,'wangwu','wangwu@123.com');
select * from t_vip;（执行成功）
-----------------------------------------------------
insert into t_vip(id,ename,emial)valuse(4,'wangwu','wangwu@123.com');(报错)
------------------------------------------------------
insert into t_vip(id)valuse(5);(执行成功)

ename字段虽然被unique约束了，但是可以为null。

新需求:ename和email两个字段联合起来具有唯一性！！！

drop table if exists t_vip;
create table t_vip(
	id int,
	ename varchar(255)，
	emial varchar(255)，
	unique(ename,emial)//没有添加在列后面的约束，叫做表级约束
);
insert into t_vip(id,ename,emial)valuse(1,'zhangsan','zhangsan@123.com');
t_vip(id,ename,emial)valuse(2,'zhangsan','zhangsan@xinlang.com');

ename和email联合起来唯一。

什么时候使用表级约束呢？

需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

not null 和unique可以联合使用吗?

drop table if exists t_vip;
create table t_vip(
	id int,
	ename varchar(20) not null uniquq
);
desc t_vip;

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）

主键约束（primary key，简称pk）

主键约束：就是一种约束。

主键字段：该字段上添加主键约束，这样的字段叫做：主键字段。

主键值：主键字段中的每一个值都叫做：主键值。

什么是主键，有啥用？

主键值是每一行记录的唯一标识。

主键是每一行记录的省份证号。

注意：任何一张表都应该有主键，没主键，表无效。

主键的特征：not null + unique（主键值不能为null，同时页不能重复）

怎么给一张表添加主键约束呢?

drop table if exists t_vip;
create table t_vip(
	id int primary key,
	name varchar(255)
    /*primary key(id)表级约束*/
);
insert into t_vip(id,name) value(1,'zhangsan'),
							  (2,'zhangsan'),
							  (3,'lisi');

表级约束主要是给多个字段联合起来添加约束？

id和name联合起来做主键：复合主键

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255)，
    primary key(id,name)
);
insert into t_vip(id,name) value(1,'zhangsan'),
							  (1,'zhangsan'),
							  (3,'lisi');（执行成功）

在实际开发中不介意使用复合主键，建议使用单一主键。因为主键值存在的意义就是这行记录的省份证号，只要意义达到即可，单一主键可以做到，复合主键比较复杂，不建议使用。

注意：主键不能添加两个或多个，只能添加一个。

主键值建议使用：int bigint char 等类型。不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的。

主键除了：单一主键和复合主键之外，还可以这样分类？

自然主键：主键值是一个自然数，和业务没关系。

业务主键：主键值和业务紧密关联，例如拿银行卡装好做主键值。这就是业务主键。

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值？

drop table if exists t_vip;
create table t_vip(
	id int primary key auto_increment,
	name varchar(255)
);
insert into t_vip(name)value('zhangsan');
insert into t_vip(name)value('zhangsan');
insert into t_vip(name)value('zhangsan');
insert into t_vip(name)value('zhangsan');
insert into t_vip(name)value('zhangsan');
insert into t_vip(name)value('zhangsan');
insert into t_vip(name)value('zhangsan');
insert into t_vip(name)value('zhangsan');
select * from t_vip;

外键约束（foreign key，简称fk）

外键约束：一种约束

外键字段：该字段上添加了外键约束。

外键值：外键字段当中的每一个值。

当cno字段没有任何约束的时候，可能会导致数据无效。可能会出现一个102，但是102不存在。所以，为了保证cno字段中的值都是100，101，需要给cno字段添加外键约束。那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

drop table if exists t_student;
drop table if exists t_class;
create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)/*外键*/
);

子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？

不一定是主键，但至少有unique约束。

测试：外键可以为null吗？

外简直可以为null。

事务（重点：五颗*）

什么是事务？

一个事务其实就是一个完整的业务逻辑。

什么是一个完整的业务逻辑？

假设转账，从A账户中向B账户中转账一万，将A账户中减去10000，将B账户中加上10000，这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。

这两个update语句要求必须同时成果或者同时失败，这样才能保证钱是正确的。

只有DML语句才会有事务这么一说，其他语句与事务无关。

insert

delete

update

只有以上的三个语句和事务有关系，其他都没有关系。因为只有以上的三个语句是数据的增、删、改的。

事务时怎么做到多条DML语句同时成功，或者同时失败的呢？

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

事务开启了：
insert
insert
insert
insert
delete
update
update
update
update
事务结束了！

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。

在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务？

清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。

提价事务标志着，事务的结束。并且是一种全部成功的结束。

回滚事务？

将之前的所有的DML操作全部撤销，并且清空事务性活动的日志文件

回滚事务标志着，事务的结束。并且是一种全部失败的结果。

怎么提交事务，怎么回滚事务？

提交事务：commit；语句

回滚事务：rollback；语句（回滚永远只能回滚到上一次的提交点！）

事务对应的英语单词是：transaction

在mysql当中默认的事务行为是怎样的？

mysql默认情况下是支持自动提交事务的。（自动提交）

什么是自动提交？

没执行一条DML语句，则提交一次。

怎样将mysql的自动提交命令关闭呢？

先执行这个命令：start transaction；

------------------------------------------回滚事务--------------------------------------------------------------
drop table t_vip;
create table t_vip(
	id int,
	namee varchar(255)
);
start transaction;
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
select * from t_vip;
rollback;
select * from t_vip;
------------------------------------------提交事务--------------------------------------------------------------
drop table t_vip;
create table t_vip(
	id int,
	namee varchar(255)
);
start transaction;
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
insert into t_vip(id,namee)value(1,'zhangsan');
select * from t_vip;
commit;
rollback;/*回滚失败*/
select * from t_vip;

事务的四个特性：

A:原子性

说明事务是最小的工作单位，不可再分。

C:一致性

所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性

I:隔离性

A事务和B事务之间具有一定的隔离。

教室A和教室B之间有一道墙，这个墙就是隔离性。

A事务在操作一张表的时候，另一个事务B也操作这张表会怎样呢？

D:持久性

事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

事务的隔离性

事务和事务之间的隔离级别有哪些呢？

读未提交：read uncommitted（最低的隔离级别）

读已提交：read committed

可重复读：repeatable read

序列化读：serializable（最高的隔离级别）

视图

怎么创建视图？

create view dept_view1 as select * from dept_view1;

删除视图

drop view dept_view1;

注意：只有DQL语句才能以view的形式创建。

create view dept_view as 这里的语句必须是DQL语句；

用视图做什么？

我们可以面向视图对象进行增删改查，最试图对象的增删改查，会导致原表被操作！（使徒的特点：通过对试图的操作，会影响到原表数据。）

/*面向试图查询*/
select * from dept2_view;
/*面向视图插入*/
insert into dept2_view (deptno,dname,loc) vilues(60,'sales','beijin');
/*面向试图删除*/
delete from dept2_view;
/*查询原表数据*/
select * from dept2;

大总结

DROP TABLE IF EXISTS t_vip;
CREATE TABLE t_vip(
	id INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(255),
	sex CHAR(3),
	age INT,
	gonghao INT NOT NULL,
	emial VARCHAR(255),
	UNIQUE(ename,emial)
);
START TRANSACTION;
INSERT INTO t_vip(ename,sex,age,gonghao,emial)VALUES('yangbohao','女',45,103,'yangbohao@1234.com'),
('zhangsan','男',22,101,'zhangsan@1234.com'),
('lisi','男',26,101,'lisi@1234.com'),
('wangwu','女',29,101,'wangwu@1234.com'),
('liliu','男',30,102,'liliu@1234.com'),
('qiuxiaolong','女',30,102,'qiuxiaolong@1234.com'),
('liuxincai','男',37,103,'liuxincai@1234.com'),
('dapao','男',30,103,'dapao@1234.com');
/*rollback;回滚*/
COMMIT;/*提交*/
DROP TABLE IF EXISTS t_job;
CREATE TABLE t_job(
	gonghao INT NOT NULL,
	job VARCHAR(255)
);
INSERT INTO t_job(gonghao,job)VALUES(101,'设计部'),(102,'开发部'),(103,'销售部');


CREATE TABLE t_gongzi(
	gonghao INT NOT NULL,
	gongzi DOUBLE
);
INSERT INTO t_gongzi(gonghao,gongzi)VALUES(101,7468.87),(102,14000.5),(103,6000);
/*查询员工所有，以及员工的部门,还有工资*/
SELECT
	v.*,j.job,g.gongzi
FROM
	t_vip v
JOIN
	t_job j
ON
	v.gonghao = j.gonghao
JOIN
	t_gongzi g
ON
	v.gonghao = g.gonghao;