（六）MySql

最新推荐文章于 2023-02-20 16:28:53 发布
最新推荐文章于 2023-02-20 16:28:53 发布
阅读量394 收藏 1
点赞数 2
分类专栏： MySQL基础 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44771551/article/details/108854259
版权

（六）MySql


（一）数据定义语言：DDL

1）库的管理

（1）创建：create
（2）删除：drop
（3）修改：alter

2）表的管理

（1）创建：create
（2）删除：drop
（3）修改：alter

3）字段的管理

（1）添加：add
（2）删除：drop
（3）修改字段名：change
（4）修改字段类型：modify

（1）库的管理

1）创建数据库

//直接创建数据库，如果已存在名字相同的数据库，会报错
create database 库名;

//创建数据库之前会判断是否存在名字相同的数据库
create database if not exists 库名;

//创建数据库的同时设置编码格式
create database 库名 character set utf8;

2）查看数据库

//查看所有的数据库
show databases;

//查看正在使用的数据库
select database();

3）删除数据库

drop database 库名;

4）修改数据库

//修改数据库的字符集
alter database 库名 character set utf8;

5）使用数据库

use 库名;

（2）表的管理

1）创建表

create table 表名 (字段名 字段类型[长度][约束]...);

2）查看表

//查看所有表
show tables;

//查看表结构
desc 表名;

3）修改表

//修改表名，两种方式
//方式1
rename table 旧表名 to 新表名;

//方式2
alter table 旧表名 rename to 新表名;

//修改表的编码格式
alter table 表名 character set utf8;

4）删除和复制表

//删除表
drop table 表名;

//仅复制表的结构
create table 粘贴的表名 like 被复制的表名;

//完全复制（结构+数据）
create table 粘贴的表名 select * from 被复制的表名;
select * from 粘贴的表名;

（3）字段的管理

1）添加字段

alter table 表名 add 字段名 字段类型;

2）修改字段名

alter table 表名 change 旧字段名 新字段名 新字段类型;

3）修改字段类型

alter table 表名 modify 字段名 新字段类型;

4）删除字段

alter table 表名 drop 字段名;

（二）数据操作语言：DML

1）添加数据（insert）

//表名后的列名和values里的值要一一对应（个数、顺序、类型）
insert into 表名 (列1,列2...) values (值1,值2...);

2）修改数据（update）

//最后面绝大多数情况下都要加where条件，指定修改，否则为整表更新
update 表名 set 列1=新值1,列2=新值2...where 条件;

3）删除数据（delete）

//删除时，如若不加where条件，删除的是整张表的数据
delete from 表名 where 条件;

4）清空整表数据（truncate）

truncate table 表名;

5）truncate（截断）与delete（删除）的区别：

truncate：会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

delete：逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；


（三）数据查询语言：DQL

（1）数据库表的基本结构

关系结构数据库是以表格（Table）进行数据存储，表格由“行”和“列”组成

  • 执行查询语句返回的结果集是一张虚拟表。

（2）基本查询

语法：select 列名 from 表名;

  • select：指定要查找的列
  • from：指定要查找的表
1）查询部分列
//查询学生的学号、姓名和年龄
select id,name,age from student;
2）查询所有列
//查询学生表中的所有信息
select 所有列的列名 from student;

//生产环境下，优先使用列名查询；*的方式需转换成全列名，效率低，可读性差。
select * from student;
3）对列中的数据进行运算
//查询学生的姓名和出生日期
//算术运算符都可以使用
select name,2020-age from student;
4）列的别名
//as和空格都可以
select id as "学号",name as "姓名",age as "年龄" from student;
5）查询结果去重
//distinct 列名
select distinct id from student;

（3）排序查询

语法：select 列名 from 表名 order by 排序列 [排序规则]

  • asc：升序排序（默认）
  • desc：降序排序
1）依据单列排序
//查询学号、姓名和成绩；依据成绩来进行降序排序。
select id,name,score from student order by score desc;
2）依据多列排序
//查询学号、姓名和成绩；依据成绩来进行降序排序（成绩相同则依据学号来进行升序排序）。
select id,name,score from student order by score desc,id acs;

（4）条件查询

语法：select 列名 from 表名 where 条件;

1）等值判断（=）
//查询学号是101的学生信息
select id,name,age from student where id = 101;
2）逻辑判断（and、or、not）
//查询年龄等于20且成绩等于90的学生信息
select id,name,age,score from student where age = 20 and score = 90;
3）不等值判断（>、<、>=、<=、!=、<>）
//查询成绩在80~100之间的学生信息
select id,name,age,score from student where score >= 80 and score <= 100;
4）区间判断（between and）
//查询成绩在80~100之间的学生信息
select id,name,age,score from student where score between 80 and 100;
5）NULL值判断（is null、is not null）
//查询没有成绩的学生信息
select id,name,age,score from student where score is null;
6）枚举查询（in(值1,值2…)）
//查询年龄为18、19、20的学生信息
select id,name,age,score from student where age in(18,19,20);
7）模糊查询
  • LIKE _ （单个任意字符）
    列名 LIKE ‘张_’
  • LIKE %（任意长度的任意字符）
    列名 LIKE ‘张%’
  • 注意：模糊查询只能和 like 关键字结合使用
//查询姓名以吴为开头的学生信息
select id,name,age,score from student where name like '吴%';

//查询姓名以吴为开头且长度为3的学生信息
select id,name,age,score from student where name like '吴__';
8）分支结构查询
CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	...
	ELSE 结果
END
//类似java的switch

//根据成绩来分级别
select id,name,age,score,
	CASE
		WHEN scora = 100 and score <= 90 THEN "A"
		WHEN scora < 90 and score >= 80 THEN "B"
		WHEN scora < 80 and score >= 60 THEN "C"
		ELSE "D"
	END as level
from student;

（5）聚合函数

（1）SUM()求所有行中单列结果的总和
（2）AVG()平均值
（3）MAX()最大值
（4）MIN()最小值
（5）COUNT()求总行数

（6）分组查询

语法：select 列名 from 表名 where 条件 group by 分组依据(列);

（7）分组过滤查询

语法：select 列名 from 表名 where 条件 group by 分组依据(列) having 过滤规则;
例子：select id, name, number from test where number > 1 GROUP BY name HAVING id < 5;

（8）限定查询

语法：select 列名 from 表名 limit 起始行,查询行数;

（9）查询总结

//sql语句编写顺序
select 列名 from 表名 where 条件 group by 分组依据(列) having 过滤规则 
order by 排序规则(asc|desc) limit 起始行,查询行数;

//sql语句执行顺序
1.from：指定数据来源表
2.where：对查询数据做第一次过滤
3.group by：分组
4.having：对分组后的数据第二次过滤
5.select：查询各字段的值
6.order by：排序
7.limit：限定查询结果

（10）子查询（作为条件判断）

select 列名 from 表名 where 条件(子查询结果);

//查询工资大于Bruce 的员工信息

#1.先查询到 Bruce 的工资（一行一列）
select SALARY from t_employees where FIRST_NAME = 'Bruce';#工资是 6000

#2.查询工资大于 Bruce 的员工信息
select * from t_employees where SALARY > 6000;

#3.将 1、2 两条语句整合
select * from t_employees where SALARY > (select SALARY from t_employees where FIRST_NAME = 'Bruce' );

（11）子查询（作为枚举查询条件）

select 列名 from 表名 where 列名 in (子查询结果);

//查询与名为'King'同一部门的员工信息

#1.	先查询 'King' 所在的部门编号(多行单列)
select department_id from t_employees where last_name = 'King'; //部门编号：80、90

#2.	再查询80、90号部门的员工信息
select * from t_employees where department_id in (80,90); 

#3. SQL：合并
select * from t_employees where department_id in (select department_id from t_employees where last_name = 'King'); #N行一列

（12）子查询（作为一张表）

select 列名 from (子查询的结果集) where 条件;

//查询员工表中工资排名前 5 名的员工信息

#1.	先对所有员工的薪资进行排序（排序后的临时表）
select * from t_employees order by salary desc

#2.	再查询临时表中前5行员工信息
select * from (临时表) limit 0,5;

#3. SQL：合并
select * from (select * from t_employees order by salary desc) as temp limit 0,5;

（13）合并查询

//合并两张表的结果（去除重复记录）
select 列名 from 表名1 union select 列名 from 表名2;

//合并两张表的结果（保留重复记录）
select 列名 from 表名1 union all select 列名 from 表名2;

（14）表连接查询

select 列名 from 表1 连接方式 表2 on 连接条件;

//（1）内连接查询（inner join on）

#1.查询所有有部门的员工信息（不包括没有部门的员工）SQL标准
select * from t_employees inner join t_jobs on t_employees.JOB_ID = t_jobs.JOB_ID;

#2.查询所有有部门的员工信息（不包括没有部门的员工）MYSQL
select * from t_employees,t_jobs where t_employees.JOB_ID = t_jobs.JOB_ID;

//（2）三表连接查询

#查询所有员工工号、名字、部门名称、部门所在国家ID
select * from t_employees e inner join t_departments d on e.department_id = d.department_id inner join t_locations l on d.location_id = l.location_id;

//（3）左外连接（left join on）

#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL 填充）
select e.employee_id , e.first_name , e.salary , d.department_name from t_employees e left join t_departments d on e.department_id = d.department_id;

//（4）右外连接（right join on）

#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL 填充）
select e.employee_id , e.first_name , e.salary , d.department_name from t_employees e right join t_departments d on e.department_id = d.department_id;

（四）数据控制语言：DCL

数据控制语言，用来定义访问权限和安全级别。

（1）创建用户

create user 用户名 identified by 密码;

（2）给用户授权

grant 权限1,权限2...(all 表示所有) on 数据库.表名 to 用户名;

（3）撤销授权

revoke 权限1,权限2...(all 表示所有) on 数据库.表名 from 用户名;

（4）查看用户权限

show grants for 用户名;

（5）删除用户

drop user  用户名;

（6）修改用户密码

use mysql;
upsate user set PASSWORD=PASSWORD("密码") where user="用户名" and host="ip地址";
flush privileges;

（五）约束

（1）实体完整性约束

表中的一行数据代表一个实体（entity），实体完整性的作用即是标识每一行数据不重复、实体唯一。

//（1）主键约束
PRIMARY KEY
唯一，标识表中的一行数据，不可重复，不能为 NULL

//（2）唯一约束
UNIQUE
唯一，标识表中的一行数据，不可重复，可以为 NULL

//（3）自动增长列
AUTO_INCREMENT
自动增长，给主键数值列添加自动增长。从 1 开始，每次加 1。不能单独使用，和主键配合使用。

（2）域完整性约束

限制列的单元格的数据正确性。

//（1）非空约束
NOT NULL
非空，此列必须有值。

//（2）默认值约束
DEFAULT 值
为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

//（3）引用完整性约束（外键）
语法：CONSTRAINT 引用名 FOREIGN KEY(列名) REFERENCES 被引用表名(列名)
详解：FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。

注意：

  • 创建关系表时，要先创建主表，再创建从表；
  • 删除关系表时，要先删除从表，再删除主表。

（六）事务

（1）事务的概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

（2）事务的边界

  • 开始：连接到数据库，执行一条DML语句。 上一个事务结束后，又输入了一条DML语句，即事务的开始
  • 结束：
    (1)提交：
    ​ a. 显示提交：commit;
    ​ b. 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）;
    ​ 2). 回滚：
    ​ a. 显示回滚：rollback;
    ​ b. 隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

（3）事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL 语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

（4）事务的特性

  • Atomicity(原子性)
    表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败
  • Consistency(一致性)
    表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态
  • Isolation(隔离性)
    事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

（5）事务的应用

#A 账户给 B 账户转账。
#1.开启事务
START TRANSACTION;
	#setAutoCommit=0;#禁止自动提交 
	#setAutoCommit=1;#开启自动提交
#2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1;
UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2;
#3.事务内语句都成功了，执行 COMMIT；
COMMIT;
#4.事务内如果出现错误，执行 ROLLBACK;
ROLLBACK;

（七）视图

（1）视图的概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

（2）视图的特点

  • 优点
    • 简单化，数据所见即所得。
    • 安全性，用户只能查询或修改他们所能见到得到的数据。
    • 逻辑独立性，可以屏蔽真实表结构变化带来的影响。
  • 缺点
    • 性能相对较差，简单的查询也会变得稍显复杂。
    • 修改不方便，特变是复杂的聚合视图基本无法修改。

（3）视图的创建

语法：CREATE VIEW 视图名 AS 查询数据源表语句;

1）创建视图
#创建 t_empInfo 的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t_empInfo 
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;
2）使用视图
#查询 t_empInfo 视图中编号为 101 的员工信息
SELECT * FROM t_empInfo where employee_id = '101';

（4）视图的修改

1）修改视图
#方式 1：如果视图存在则进行修改，反之，进行创建
CREATE  OR  REPLACE VIEW t_empInfo 
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT_ID from t_employees;

#方式 2：直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

（5）视图的删除

语法：DROP VIEW 视图名

1）删除视图
#删除t_empInfo视图
DROP VIEW t_empInfo;

注意：删除视图不会影响原表

（6）视图的注意事项

注意：

  • 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
  • 如果视图包含以下结构中的一种，则视图不可更新
    • 聚合函数的结果
    • DISTINCT 去重后的结果
    • GROUP BY 分组后的结果
    • HAVING 筛选过滤后的结果
    • UNION、UNION ALL 联合后的结果
呼哈呼
关注
专栏目录
实验报告六 MYSQL备份与恢复.docx
10-25
根据提供的实验报告“MYSQL备份与恢复”的相关内容，我们可以总结出以下几个关键知识点： ### 一、MYSQL日志管理 #### 1. 错误日志 - **定义**: 记录MySQL运行过程中的错误信息和警告信息。 - **作用**: 帮助管理...
datetime格式提取年月日
lixinpeng16的博客
08-30 4864
datetime格式提取年月日
datetime类型的取年月日 sql_SQL Server返回DATETIME类型，年、月、日、时、分、秒、毫秒...
weixin_39736047的博客
02-06 2651
SQL Server返回DATETIME类型的年、月、日，有两种方法，如下所示：DECLARE @now DATETIME=GETDATE()--第一种方法SELECT @now,YEAR(@now),MONTH(@now),DAY(@now),DATEPART(HH,@now),DATEPART(MI,@now),DATEPART(SS,@now),DATEPART(MS,@now)--第二种方...
关于sql语句中的count（*），count（1），count（具体字段）的介绍
z_ssyy的博客
12-28 4860
在sql语句中有一个聚合函数count（）；这个函数里面可以写*，可以写数字（不仅仅只可以写1），可以写具体字段，他们之间有什么区别呢？ 查阅资料得知，count（*）与count（1）在各种数据库服务器底层中，效果是一样的。就是分组后，能查出数据，就会记录1条，依次往上加。所以就算只有一个字段有值，也会被+1。在外连接查询的时候，经常会碰到很多字段为空（null）的情况。所以这个时候就得根据需...
datetime类型的取年月日 sql_sql中datetime类型的获取
weixin_28782823的博客
02-23 4747
sql中datetime类型的获取(2014-01-20 11:59:41)标签：datetime格式it1、获取年月日Select CONVERT(varchar(100), f_datae, 23) fromt_system;//2014-01-21获取当前则Select CONVERT(varchar(100), getdate(), 23)2、获取年月日时分秒Select CONVERT(...
用sql语句获取datetime类型的年月日
hydy的博客
12-21 1万+
用sql语句获取datetime类型的年月日
北邮大三下数据库实验六mysql版本.pdf
12-25
【实验报告】北邮大三下数据库实验六MySQL版本 实验目标： 本次实验的主要目的是通过对比不同查询语句在不同情况下的执行效果，巩固和深化对数据库查询与优化理论的理解，提升实际操作中的数据库系统优化能力。同时...
Mysql文件身份证号前六位对应地区表单
最新发布
08-21
Mysql文件身份证号前六位对应地区表单 Mysql文件身份证号前六位对应地区表单 Mysql文件身份证号前六位对应地区表单 Mysql文件身份证号前六位对应地区表单 Mysql文件身份证号前六位对应地区表单 Mysql文件身份证号前...
实验报告六MYSQL备份与恢复.doc
10-03
实验报告六——MySQL备份与恢复主要探讨了MySQL数据库在日常管理中至关重要的两个方面：数据备份和恢复。这些操作对于确保数据的安全性和可恢复性至关重要，尤其是在系统出现故障或者需要回滚到特定状态时。 首先，...
mysql-client
07-09
六、优化与性能监控 通过使用`EXPLAIN`关键字，可以在客户端分析SQL查询的执行计划，从而优化查询性能。此外，`SHOW STATUS;`和`SHOW VARIABLES;`命令可用于查看服务器状态和配置信息，帮助诊断性能问题。 总之，...
Oracle多字段统计大于某值的sql写法
挣钱花388的博客
04-28 3439
合在一起的写法 select (select count(h2s) from T_RECEIVE_DATA where h2s>0 ) as h2s,(select count(stench) from T_RECEIVE_DATA where stench>0 ) as stench ,(select count(nh3) from T_RECEIVE_DATA where ...
python datatime 获取年月日时分秒
高压锅博客
05-25 1万+
1.当前时间 import datetime #取当前时间 print(datetime.datetime.now()) 2. 获取年、月、日 import datetime #获取年份 print(datetime.datetime.now().year) #取月 print(datetime.datetime.now().month) #取日 print(datetime.datetime.now().day) 3. 获取时、分、秒 import datetime #取时 print(datetim
datetime判断日期不要时间_C#中DateTime实现的功能，比我们想像的要多？
weixin_39871562的博客
11-29 1024
DateTime，就是一个世界的大融合。日期和时间，在我们开发中非常重要。DateTime在C#中，专门用来表达和处理日期和时间。本文算是多年使用DateTime的一个总结，包括DateTime对象的整体应用，以及如何处理不同的区域、时区、格式等内容。一、什么是DateTime跟我们想的不一样，DateTime不是一个类(class)，而是一个结构(struct)，它存在于System命...
SQL查询in大于1000处理
qq_42129245的博客
02-20 1465
sql查询中in查询数量限制解决办法
MySQL分组与聚合函数
weixin_46834417的博客
07-17 1046
分组操作的目的:就是为了统计. 分组的关键字: GROUP BY. 分组筛选:用 HAVING进行条件筛选. 常见的分组:分组统计，分组排序，多分组统计，回溯统计、统计筛选. 分组统计 ：查询时在 WHERE条件后添加 GROUP BY即可进行分组统计 SELECT [select选项] 字段列表 FROM 数据表名 [WHERE 条件表达式] GROUP BY 字段名; 通过聚合函数MAX()获取每个分类下商品的最高价格 Select category_id,max(price) Fr.
软件测试基础篇三之数据库
qq_45169795的博客
02-01 746
数据库、MySQL、navicat的关系与使用
sql datetime字段 取年月日_属于菜鸟的SQL干货 （1）
weixin_39801165的博客
11-26 1704
近期遇到一位运营朋友初学sql，发现初学者对此无头绪。所以我试着总结了一些简单案例，在此我不会将专业知识点单独拎出，我会在案例操作过程中 一 一 说明，我尽量以简单的“白话文”形式写出，希望对初学者有所帮助。（话不多说，直接上）1.了解数据库的相关概念。（市面学习资源丰富，此处需搞明白，才懂得sql 在其中起到什么作用）。2.安装相关必要软件，以方便跟随事例进行操作达到知识巩固效果。（相信广大网友...
datetimepicker获取年月日_MFC 使用datetimepicker获取时间
weixin_42282116的博客
02-15 2347
CTime time = CTime::GetCurrentTime();int year = time.GetYear(); //返回年int mouth = time.GetMouth(); //返回月份int date = time.GetDays(); // 返回日数int hour = time.GetHours(); // 返回小时数(-23至23之间)int min = time.G...
SQL语句统计个数大于一的记录
热门推荐
面朝大海，春暖花开
01-19 2万+
1、主要是利用Having语句进行，由于where不能与聚合函数一起使用，所以用having SELECT MC,COUNT(MC) AS SL FROM JSB GROUP BY MC HAVING(COUNT(MC)>1)
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值