MYSQL基础（一）

最新推荐文章于 2022-11-30 09:59:59 发布

weixin_41249041

最新推荐文章于 2022-11-30 09:59:59 发布

阅读量68

点赞数

分类专栏： JAVA基础

本文链接：https://blog.csdn.net/weixin_41249041/article/details/116907951

版权

JAVA基础专栏收录该内容

4 篇文章 0 订阅

订阅专栏

概念：

DB 数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。

DBMS 数据库管理系统（Database Management System）。数据库是通过DBMS创 建和操作的容器

SQL 结构化查询语言（Structure Query Language）：专门用来与数据库通信的语 言。

SQL语言分类：

1、DML（Data Manipulation Language):  数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性是对数据库中具体的数据操作

2、DDL（Data Definition Language):   数据定义语句，用于库和表的创建、修改、删除。是对数据库和表的操作

3、DCL（Data Control Language):   数据控制语句，用于定义用户的访问权限和安全级别。是对用户的访问权限和安全级别操作

DML

DML用于查询与修改数据记录，包括如下SQL语句：

 INSERT：添加数据到数据库中

 UPDATE：修改数据库中的数据

 DELETE：删除数据库中的数据

 SELECT：选择（查询）数据

 SELECT是SQL语言的基础，最为重要。

DDL

DDL用于定义数据库的结构，比如创建、修改或删除

数据库对象，包括如下SQL语句：

 CREATE TABLE：创建数据库表

 ALTER TABLE：更改表结构、添加、删除、修改列长度

 DROP TABLE：删除表

 CREATE INDEX：在表上建立索引

 DROP INDEX：删除索引

DCL

DCL用来控制数据库的访问，包括如下SQL语句：

 GRANT：授予访问权限

 REVOKE：撤销访问权限

 COMMIT ：提交事务处理

 ROLLBACK ：事务处理回退

 SAVEPOINT ：设置保存点

 LOCK：对数据库的特定部分进行锁定

数据处理----查询

SELECT

SELECT *|{[ DISTINCT ] column | expression [ alias ],...}

FROM table;

• SQL 语言 大小写不敏感 。

• SQL 可以写在一行或者多行

• 关键字不能被缩写也不能分行

• 各子句一般要分行写。

• 使用缩进提高语句的可读性。

列的别名:

• 重命名一个列。

• 便于计算。

• 紧跟列名， 也可以在列名和别名之间加入关键字 ‘AS’，别名使用 双引号 ， 以便在别名中包含空 格或特殊的字符并区分大小写 。

字符串

• 字符串可以是 SELECT 列表中的一个字符 , 数字 , 日期。

• 日期和字符只能在 单引号 中出现 。

• 每当返回一行时，字符串被输出一次。

显示表结构：DESCRIBE

使用 DESCRIBE 命令，表示表结构 DESC[RIBE] tablename

如： DESCRIBE employees

LIKE

使用 LIKE 运算选择类似的值

• 选择条件可以包含字符或数字:

– % 代表零个或多个字符(任意个字符)。

– _ 代表一个字符。

• ‘%’和‘-’可以同时使用。

ORDER BY子句

• 使用 ORDER BY 子句排序

– ASC （ ascend ） : 升序

– DESC （ descend ） : 降序

• ORDER BY 子句在 SELECT 语句的 结尾。

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

• AVG() 平均值 可以对 数值型数据 使用 AVG 和 SUM 函数。

• COUNT()   返回表中记录总数 , 适用于 任意数据类型

• MAX()  最大值   可以对 任意数据类型 的数据使用 MIN 和 MAX 函数

• MIN()  最小值 可以对 任意数据类型 的数据使用 MIN 和 MAX 函数

• SUM() 合计   可以对 数值型数据 使用 AVG 和 SUM 函数。

组函数语法：

GROUP BY 子句：

包含在 GROUP BY 子句中的列不必包含在 SELECT 列表中

非法使用组函数

• 不能在 WHERE 子句中使用组函数。 WHERE 子句中不能使用组函数

• 可以在 HAVING 子句中使用组函数。

过滤分组： HAVING 子句

使用 HAVING 过滤分组 :

1. 行已经被分组。

2. 使用了组函数。

3. 满足HAVING 子句中条件的分组将被显示。

HAVING 子句

多表查询

笛卡尔集

语法： select name,boyName from beauty,boys;

笛卡尔集的错误情况：

select count(*) from beauty;

假设输出 12 行

select count(*)from boys;

假设输出 4 行

最终结果： 12*4=48 行

• 笛卡尔集会在下面条件下产生 :

– 省略连接条件

– 连接条件无效

– 所有表中的所有行互相连接

• 为了避免笛卡尔集，可以在 WHERE 加入有效的连接条件。

• 在 WHERE 子句中写入连接条件。

• 在表中有相同列时，在列名之前加上表名前缀

使用ON 子句创建连接

• 自然连接中是以具有相同名字的列为连接条件的。

• 可以使用 ON 子句指定额外的连接条件 。

• 这个连接条件是与其它条件分开的。

• ON 子句使语句具有更高的易读性

Join连接

分类：

– 内连接 [inner] join on

– 外连接

• 左外连接 left [outer] join on

• 右外连接 right [outer] join on

常见函数

字符函数

大小写控制函数

这类函数改变字符的大小写。

字符控制函数

这类函数控制字符:

数字函数

日期函数

条件表达式

• 在 SQL 语句中使用 IF-THEN-ELSE 逻辑

• 使用方法:

– CASE 表达式

CASE expr WHEN comparison_expr1 THEN return_expr1

[ WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr ]

END

数据处理之增删改以及事务控制

数据操纵语言

•

DML(Data Manipulation Language –数据操纵语言) 可以在下列条件下执行 :

– 向表中插入数据

– 修改现存数据

– 删除现存数据

• 事务是由完成若干项工作的DML 语句组成的

INSERT 语句语法

INSERT INTO table [( column [ , column... ])]

VALUES (value [ , value... ]);

使用这种语法一次只能向表中插入一条数据。

字符和日期型数据应包含在 单引号 中。

从其它表中拷贝数据

在 INSERT 语句中加入子查询。

INSERT INTO emp2

SELECT *

FROM employees

WHERE department_id = 90;

不必书写 VALUES 子句。

• 子查询中的值列表应与 INSERT 子句中的列名对应

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM employees

WHERE job_id LIKE '%REP%';

更新数据

使用 UPDATE 语句更新数据。

UPDATE table

SET column = value [, column = value, ... ]

[WHERE condition ];

• 可以一次更新多条数据。

• 如果需要回滚数据，需要保证在DML 前，进行设置： SET AUTOCOMMIT = FALSE;

不同的sql用事务自动提交设置各有什么影响？

删除数据

DELETE FROM table

[ WHERE condition ];

子查询

概念：出现在其他语句内部的select语句，称为子查询或内查询内部嵌套其他select语句的查询，称为外查询或主查询

• 子查询要包含在括号内 。

• 将子查询放在比较条件的右侧 。

• 单行操作符对应单行子查询，多行操作符对应多行子查询

单行子查询

只返回一行。

• 使用单行比较操作符。

• 子查询 ( 内查询 ) 在主查询之前一次执行完成。

• 子查询的结果被主查询 ( 外查询 ) 使用。

子查询中的 HAVING 子句

• 首先执行子查询。

• 向主查询中的HAVING 子句返回结果。

非法使用子查询

子查询中的空值问题

多行子查询

创建和管理表

创建数据库

创建一个保存员工信息的数据库

– create database employees;



命名规则

• 数据库名不得超过30 个字符，变量名限制为 29 个

• 必须只能包含 A–Z, a–z, 0–9, _ 共 63 个字符

• 不能在对象名的字符间留空格

• 必须不能和用户定义的其他对象重名

• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突

• 保持字段名和类型的一致性, 在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数, 那在另一个表里可就别变成字符型了

CREATE TABLE 语句

• 必须具备:

– CREATE TABLE权限

– 存储空间

CREATE TABLE [ schema .] table

( column datatype [DEFAULT expr ][, ...]);

• 必须指定:

– 表名

– 列名, 数据类型 , 尺寸

CREATE TABLE dept (deptno INT(2), dname VARCHAR(14), loc VARCHAR(13));

查看创建的表： DESCRIBE dept

常用数据类型

使用子查询创建表

• 使用 AS subquery 选项， 将创建表和插入 数据结合起来

CREATE TABLE table

[( column , column ...)]

AS subquery;

• 指定的列和子查询中的列要一一对应

• 通过列名和默认值定义列

ALTER TABLE 语句

使用 ALTER TABLE 语句可以实现：

 向已有的表中添加列 ALTER TABLE dept80 ADD job_id varchar(15);

 修改现有表中的列，可以修改列的数据类型 , 尺寸和默认值，对默认值的修改只影响今后对表的修改 ALTER TABLE dept80 MODIFY (last_name VARCHAR(30));

 删除现有表中的列 ALTER TABLE dept80 DROP COLUMN job_id;

 重命名现有表中的列，使用 CHANGE old_column new_column dataType子句重命名列 ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

删除表

• 数据和结构都被删除

• 所有正在运行的相关事务被提交

• 所有相关索引被删除

• DROP TABLE 语句不能回滚

清空表

• TRUNCATE TABLE 语句 :

– 删除表中所有的数据

– 释放表的存储空间

• TRUNCATE语句 不能回滚

• 可以使用 DELETE 语句删除数据 , 可以回滚

delete from emp2;

select * from emp2;

rollback;

select * from emp2;

改变对象的名称

执行RENAME语句改变表, 视图的名称；

ALTER table dept

RENAME TO detail_dept;

Table renamed.

必须是对象的拥有者；

常见的数据类型

数值类型

字符类型

char和varchar类型

说明：用来保存 MySQL 中较短的字符串。

binary 和 varbinary 类型

说明：类似于 char 和 varchar ，不同的是它们包含二进制字符串而不包含非二 进制字符串。

Enum 类型

说明 : 又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。

如果列表成员为 1~255 ，则需要 1 个字节存储

如果列表成员为 255~65535 ，则需要 2 个字节存储

最多需要 65535 个成员！

Set 类型

说明：和 Enum 类型类似，里面可以保存 0~64 个成员。和 Enum 类型最大的区 别是： SET 类型一次可以选取多个成员，而 Enum 只能选一个

根据成员个数不同，存储所占的字节也不同

成员数 字节数

1~8 1

9~16 2

17~24 3

25~32 4

33~64 8

日期类型

datetime和timestamp的区别

1 、 Timestamp 支持的时间范围较小，取值范围： 19700101080001——2038年的某个时间 Datetime的取值范围： 1000-1-1 ——9999—12-31

2 、 timestamp 和实际时区有关，更能反映实际的日期，而datetime 则只能反映出插入时的当地时区

3 、 timestamp 的属性受 Mysql 版本和 SQLMode 的影响很大

约束和分页

描述约束
创建和维护约束
数据库分页

什么是约束

为了保证数据的一致性和完整性， SQL 规范以约束的方式对表数据进行额外的条件限制。

• 约束是表级的强制规定

•可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

约束

有以下六种约束 :

– NOT NULL 非空约束，规定某个字段不能为空

– UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

– PRIMARY KEY 主键 ( 非空且唯一 )

– FOREIGN KEY 外键

– CHECK 检查约束

– DEFAULT 默认值

具体细节可以参阅 W3Cschool 手册

注意： MySQL 不支持 check 约束，但可以使用 check 约束，而没有任何效果；

根据约束数据列的限制， 约束可分为：

– 单列约束：每个约束只约束一列

– 多列约束：每个约束可约束多列数据

•

根据约束的作用范围 ，约束可分为：

– 列级约束只能作用在一个列上，跟在列的定义后面

– 表级约束可以作用在多个列上，不与列一起，而是单独定义

NOT NULL 约束

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

 Null 类型特征：

 所有的类型的值都可以是 null ，包括 int 、 float等数据类型

空字符串””不等于null，0也不等于null

UNIQUE 约束

唯一约束，允许出现多个空值： NULL 。

同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。

添加唯一约束：

ALTER TABLE USER ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE ;

删除约束

ALTER TABLE USER DROP INDEX uk_name_pwd;

PRIMARY KEY 约束

主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值

• 如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

• 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。

• MySQL 的主键名总是 PRIMARY ，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

删除主键约束：

ALTER TABLE emp5 DROP PRIMARY KEY ;

添加主键约束：

ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

修改主键约束：

ALTER TABLE emp5 MODIFY id INT PRIMARY KEY;

FOREIGN KEY 约束

• 外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。

• 还有一种就是级联删除子表数据。

• 注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列

• 同一个表可以有多个外键约束

删除外键约束：

ALTER TABLE emp DROP FOREIGN KEY emp_dept_id_fk;

增加外键约束：

ALTER TABLE emp

ADD [ CONSTRAINT emp_dept_id_fk ] FOREIGN KEY(dept_id)

REFERENCES dept(dept_id);

FOREIGN KEY 约束的关键字

– FOREIGN KEY: 在表级指定子表中的列

– REFERENCES: 标示在父表中的列

– ON DELETE CASCADE( 级联删除 ) : 当父表中的列被删除

时，子表中相对应的列也被删除

– ON DELETE SET NULL( 级联置空 ) : 子表中相应的列置空

CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20),
classes_name VARCHAR(20),
classes_number INT,
/*表级别联合外键*/
FOREIGN KEY(classes_name, classes_number)
REFERENCES classes(NAME, number) ON DELETE CASCADE);

CHECK 约束

• MySQL 可以使用 check 约束，但 check 约束对数据验证没有任何作用, 添加数据时，没有任何错误或警告

MySQL中使用limit实现分页

• 怎么分段，当前在第几段（每页有几条，当前在第几页）

 前 10 条记录： SELECT * FROM table LIMIT 0,10;

 第 11 至 20 条记录： SELECT * FROM table LIMIT 10,10;

 第 21 至 30 条记录： SELECT * FROM table LIMIT 20,10;

• 公式：

（当前页数 -1 ） * 每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

• 注意：

 limit 子句必须放在整个查询语句的最后！

事务

事务： 事务由单独单元的一个或多个 SQL 语句组成，在这个单元中，每个MySQL 语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL 语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL 语句均执行成功，则事物被顺利执行。

MySQL 中的存储引擎

1、概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。

2、通过 show engines ；来查看mysql支持的存储引擎。

3、在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。 其中innodb支持事务 ，而myisam、memory等不支持事务

事务的特点

事务的 ACID(acid) 属性

 1. 原子性（ Atomicity ）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

 2. 一致性（ Consistency ）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

 3. 隔离性（ Isolation ）

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

 4. 持久性（ Durability ）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响；

事务的使用：
COMMIT 或 ROLLBACK 语句

数据库的隔离级别

对于同时运行的多个事务 , 当这些事务访问数据库中相同的数据时 , 如果没有采取必要的隔离机制, 就会导致各种并发问题 :



脏读 : 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还 没有被提交 的字段 . 之后, 若 T2 回滚 , T1 读取的内容就是临时且无效的 .



不可重复读 : 对于两个事务 T1, T2, T1 读取了一个字段 , 然后 T2 更新了该字段 . 之后, T1 再次读取同一个字段 , 值就不同了 .



幻读 : 对于两个事务 T1, T2, T1 从一个表中读取了一个字段 , 然后 T2 在该表中插入了一些新的行 . 之后 , 如果 T1 再次读取同一个表 , 就会多出几行 .



数据库事务的隔离性 : 数据库系统必须具有隔离并发运行各个事务的能力 , 使它们不会相互影响, 避免各种并发问题 .



一个事务与其他事务隔离的程度称为隔离级别 . 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度 , 隔离级别越高 , 数据一致性就越好, 但并发性越弱

在 MySql 中设置隔离级别

每启动一个 mysql 程序 , 就会获得一个单独的数据库连接 . 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别.

 查看当前的隔离级别: SELECT @@tx_isolation;

 设置当前 mySQL 连接的隔离级别 :

 set transaction isolation level read committed ;

 设置数据库系统的全局的隔离级别:

 set global transaction isolation level read committed ;

视图：

视图： MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

• 应用场景：

– 多个地方用到同样的查询结果

– 该查询结果使用的sql语句较复杂

CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE s.majorid=1;

视图的好处：

重用sql语句

简化复杂的sql操作，不必知道它的查询细节

保护数据，提高安全性

创建或者修改视图：

• 创建视图的语法：

create [or replace] view view_name

As select_statement

[with|cascaded|local|check option]

•

修改视图的语法：

alter view view_name

As select_statement

[with|cascaded|local|check option]

视图的可更新性和视图中查询的定义有关系，以下类型的 视图是不能更新的。

• 包含以下关键字的sql语句：分组函数、distinct、group by 、having、union或者union all

• 常量视图

• Select中包含子查询

• join

• from一个不能更新的视图

• where子句的子查询引用了from子句中的表

• 删除视图的语法：

用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限。

drop view [if exists] view_name,view_name …[restrict|cascade]

查看视图的语法：

show tables;

如果需要查询某个视图的定义，可以使用show create view

命令进行查看：

show create view view_name \G

存储过程和函数：

存储过程和函数：

事先经过编译并存储在数据库中的一段 sql 语句的集合。

使用好处：

1、简化应用开发人员的很多工作

2、减少数据在数据库和应用服务器之间的传输

3、提高了数据处理的效率

• 创建存储过程：

create procedure 存储过程名 ([proc_parameter[,…]])

[characteristic…]routine_body

• 创建函数：

create function 函数名([func_parameter[,…]])

returns type

[characteristic…]routine_body

•

proc_parameter:

[in|out|inout] param_name type

•

Func_paramter:

param_name type

•

Type:

任何有效的mysql数据类型

•

Characteristic:

language sql(默认，且推荐)

|[not] deterministic

|{contains sql|no sql|reads sql data|modifies sql data}

|sql security{definer|invoker}

|comment ‘string’

•

Rountine_body:

有效的sql 过程语句

• 调用存储过程：

call 存储过程名(参数列表)

• 调用函数：

Select 函数名(参数列表)

• 修改存储过程：

alter procedure 存储过程名 [charactristic…]

• 修改函数：

alter function 函数名 [charactristic…]

characteristic:

{contains sql|no sql|reads sql data|modifies sql data}

|sql security{definer|invoker}

|comment ‘string’

说明：一次只能删除一个存储过程或者函数，并且要求有该

过程或函数的alter routine 权限

删除存储过程：

drop procedure [if exists] 存储过程名

删除函数：

drop function [if exists] 函数名

1.查看存储过程或函数的状态：

show {procedure|function} status like 存储过程或函数名

2.查看存储过程或函数的定义：

show create {procedure|function} 存储过程或函数名

3.通过查看information_schema.routines了解存储过程和函数的

信息（了解）

select * from rountines where rounine_name =存储过程名|函

数名

流程控制结构

case结构——作为表达式：

case结构——作为独立的语句：

循环结构：

weixin_41249041

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MYSQL基础（一）

概念：DB 数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。 DBMS 数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器 SQL 结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。SQL语言分类：1、DML（Data Manipulation Language):数据操纵语句，用于添加、删除、修改、查询数...
复制链接

扫一扫