【MySQL】基础概念

最新推荐文章于 2022-09-02 16:31:47 发布
最新推荐文章于 2022-09-02 16:31:47 发布
阅读量210 收藏
点赞数
分类专栏： MySQL 文章标签： MYSQL 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lsy1072/article/details/79469143
版权

事务管理

事务就是针对数据库中的一组操作，可以有一条或者多条SQL语句组成，
同一个事务具有同步的特点，也就是事务中的所有语句要么都执行，要么都不执行。

存储引擎

  • MyISAM

    提供了高速存储和检索与全文搜索的功能，该存储引擎插入速度快，但是空间和内存使用率较低。

  • MEMORY

    提供了“内存中”的表，该存储引擎所有数据都存储在内存中，所以在数据处理速度很快，但是安全性不高。

  • InnoDB

    提供了事务处理的应用程序，支持外键，同时还支持崩溃修复能力和并发控制。

变量

MySQL中分为用户变量和系统变量两种。

用户变量

使用SET进行定义变量，变量名称前使用@符号；使用变量前必须进行定义和初始化，如果使用没有初始化的变量，值为NULL；

SET @name=‘liushiyao’;

定义特殊表达式

SET @name=name from student;
SELECT @name;

修改变量的值

使用:=作为分配符

SELECT @num:=1 AS num;

系统变量

系统变量是由系统生成的变量，这些变量在MySQL数据库服务器启动的时候被赋值，系统变量的取值决定了系统接下来的运行。

常用的系统变量
1. CURRENT_DATE:系统日期
2. CURRENT_TIME:系统时间
3. CURRENT_USER:SQL用户的名字
4. CURRENT_TIMESTAMP:系统日期和时间

控制语句

在控制台上只能执行条件语句，而存储过程和函数中可以使用所有的控制语句。

条件语句

  1. 控制台下的IF条件语句
IF(条件,结果1,结果2)

控制台下的IF语句相当于一个三目运算符，条件成立这返回结果1，否则返回结果2.

SELECT IF( sex="男","我是男人","我是女人")from student where id=910
  1. 存储过程和函数中的IF条件语句
create procedure has_id(IN a INTEGER)
BEGIN
IF (a = 0) then
select "输入的是0";
else
select "输入的不是0";
END if;
END;

注：END IF需要加分号

  1. 控制台下的CASE语句
select case (select count(id)FROM student)
when 16 then "16"
when 17 then "17"
else "other"
END AS "ID数量"
;

CASEz中可以是字段或者是查询语句。

  1. 存储过程或者函数的CASE语句
    • 匹配值的方式
CASE case_value
WHEN when_value THEN statment_list
[WHEN when_value THEN statment_list]
[ELSE statment_list]
END CASE
  • 查询结果的方式
CASE 
WHEN search_condition THEN statment_list
[WHEN search_condition THEN statment_list]
[ELSE statment_list]
END CASE

循环结构

  1. WHILE DO
    执行循环体之前进行条件判断 
CREATE DEFINER=`root`@`%` PROCEDURE `while_insert`(IN num INTEGER)
BEGIN
declare i INTEGER;
set i=num;
WHILE i < 10 DO
INSERT INTO student (name,sex) values(concat('我是',i),"女");
set i=i+1;
END WHILE;
END
  1. REPEAT END REPEAT
    执行循环体后才进行条件判断 
CREATE DEFINER=`root`@`%` PROCEDURE `while_insert`(IN num INTEGER)
 BEGIN
 declare i INTEGER;
 set i=num;
 REPEAT 
 INSERT INTO student (name,sex) values(concat('我是',i),"女");
 set i=i+1;
 UNTIL i>10
 END REPEAT;
 END
的表是主表，引用外键的表是从表，外键列只能插入参照列中存在的值，参照列被参照的值不能被删除。

存储过程、函数以及触发器

存储过程

相当于函数的作用，用于减少重复SQL语句的编写

a. 创建存储过程 
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...]
BEGIN
  //代码部分
END
  • sp_name:存储过程的名称；
  • proc_parameter:表示存储过程的参数列表，如果没有参数，也要使用空参数列表（）；
    参数列表由三部分组成：
[IN|OUT|INOUT] param_name type
//表示输入、输出、输入输出参数(OUT的还没实践过)
//参数名称
//参数类型，MySQL的任意类型
  • character：指定存储过程中的特性，包含多个值
  • BEGIN END：表示SQL代码的开始和结束
**注**：存储过程中默认把分号作为结束符，可以使用DELIMITER命令定义新的结束符。 
create procedure insert_stu(
 IN id INTEGER,
 IN name VARCHAR(32)
 )
 BEGIN
 INSERT INTO students VALUES(id,name,11,1);
 END;
b. 调用存储过程 
CALL sp_name([parameter[,...]]);
CALL insert_stu(1,'lsy');
c. 查看存储过程 - 查看存储过程的状态 
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'];
SHOW PROCEDURE STATUS LIKE 'insert_stu'
+--------+------------+-----------+----------------+---------------------+---------------------+-----------------+-----------+------------------------+------------------------+----------------------+
| Db     | Name       | Type      | Definer        | Modified            | Created             | Security_type   | Comment   | character_set_client   | collation_connection   | Database Collation   |
|--------+------------+-----------+----------------+---------------------+---------------------+-----------------+-----------+------------------------+------------------------+----------------------|
| mydata | insert_stu | PROCEDURE | root@localhost | 2017-12-25 00:01:16 | 2017-12-25 00:01:16 | DEFINER         |           | utf8                   | utf8_general_ci        | utf8_general_ci      |
+--------+------------+-----------+----------------+---------------------+---------------------+-----------------+-----------+------------------------+------------------------+----------------------+
1 row in set
Time: 0.002s
  • 查看存储过程的具体信息
show create PROCEDURE insert_stu;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+------------------------+------------------------+-------------
| Procedure   | sql_mode                                                                                                                                  | Create Procedure   | character_set_client   | collation_connection   | Database Col
|-------------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+------------------------+------------------------+-------------
| insert_stu  | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_stu`(
IN id INTEGER,
IN name VARCHAR(32)
)
BEGIN
INSERT INTO students VALUES(id,name,12,1);
END                    | utf8                   | utf8_general_ci        | utf8_general_ci      |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+------------------------+------------------------+-------------
1 row in set
Time: 0.002s
d. 修改存储过程

修改存储过程只能修改存储过程的名字和定义，不可以修改具体内容。但是可以通过删除存储过程，再创建存储过程的方式实现修改存储内容的目的。

ALTER {PROCEDURE|FUNCTION} sp_name[characteristic...]
d. 删除存储过程 

DROP PROCEDURE sp_name;

函数

函数的作用与存储过程相似，但是也有一些区别。

  1. 创建自定义函数
CREATE [AGGREGATE] FUNCTION function_name([func_parameter[,...]])
RETURNS type
BEGIN
  //函数体
END

AGGREGATE:创建的函数是自定义的普通函数还是AGGREGATE函数；
function_name:函数名
function_parameter:参数列表
RETURNS：返回列表（必须是ＲＥＴＵＲＮＳ）

create FUNCTION query_student_sum()returns INTEGER 
BEGIN 
DECLARE sum INTEGER;
SELECT(select COUNT(*) from student ) INTO sum;
RETURN sum;
END
  1. 调用自定义函数
    跟内部函数一样调用
SELECT query_student_sum()
  1. 查看自定义函数
SHOW CREATE FUNCTION query_student_sum

  1. 修改自定义函数

  2. 删除自定义函数

DROP FUNCTION query_student_sum;

存储过程的函数的区别
1. 定义方式不同：存储过程是使用PROCEDURE定义的，而函数则使用的是FUNCTION定义的
2. 返回值不同：存储过程中的参数列表是可以设置参数是IN，OUT，INOUT，所以可以有多个返回值；而函数的参数
列表都是IN类型的，而且只有一个返回值
3. 调用方式不同，PROCEDURE是通过CALL调用的，FUNCTION则是直接使用即可。

查询该数据库中所有的自定义存储过程和函数
1. 查询该数据库中的所有自定义存储过程

SHOW PROCEDURE STATUS WHERE db="db_name"
  1. 查询该数据库中的所有自定义函数
SHOW FUNCTION STATUS WHERE db="db_name"cre

触发器

有某些事件触发的操作，这些事件可以是INSERT，UPDATE,DELETE的操作。
1. 创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name
FOR EACH ROW trigger_stmt;

trigger_name:触发器的名称；
trigger_time:触发时间，例如AFTER，BEFORE；
trigger_event:触发事件，例如INSERT,UPDATE,DELETE
table_name:表的名称；
FOR EACH ROW：任何一条记录满足触发事件都会触发该触发器。
trigger_stmt:触发器触发后执行的语句，如果语句有多条可以使用BEGIN/END编辑多条语句

注：
使用触发器时，不允许执行有返回结果的语句 

create trigger insert_trigger after INSERT ON student for each row 
begin 
select "我会触发器";
select "成功往student表中插入一条记录";
end 
(1415, 'Not allowed to return a result set from a trigger')

触发器中不可以有START TRANSACTION,COMMIT或者ROLLBACK等关键字
如果不需要使用触发器，则应该及时删除，否则会造成数据变化。

  1. 查看触发器
show triggers\G;
  1. 删除触发器
drop trigger if exists insert_trigger;

字段的约束

防止数据表中插入错误数据，维护数据表中的完整性的规则。

  1. 主键约束

    表中的每一行都应该具有可以唯一标识自己的一列(或一组列)。而这个承担标识作用的列称为主键。


满足以下条件
1. 任何两行都不具有相同的主键值。就是说这列的值都是互不相同的。
2. 每个行都必须具有一个主键值。主键列不允许设置为NULL。
3. 主键列的值不允许进行修改和更新。
- 设置主键
创建表的时候，添加PRIMARY KEY关键字 
PRIMARY KEY (字段1)
ALTER TABLE table_name ADD PRIMARY KEY (id)
  • 修改主键

    主键是不可以修改的，之后删除主键后在添加新的主键


删除主键 
ALTER TABLE table_name DROP PRIMARY KEY ;
添加主键 
ALTER TABLE table_name ADD PRIMARY KEY (id);
  1. 外键约束
    外键是指引用了另外一个表中的一列或者多列，被引用的列应该具有主键约束或者唯一性约束。
    建立外键是为了保证数据的完整性和统一性。
    • 为表添加约束
      如果表中有记录，则无法创建外键约束。
 ALTER TABLE students ADD CONSTRAINT FK_ID foreign key(gid) references grade(id);

注：1. 建立外键的表必须是InnoDB型，不是临时表
2. 定义外键名时，不能加引号。

  • 查看外键
SHOW CREATE TABLE table_name
  • 删除外键
ALTER TABLE table_name DROP FOREIGN KEY 外键名
  1. 非NULL约束
    字段的值不可以为NULL 
字段名 数据类型 NOT NULL
  1. 默认约束
    给数据表中的字段设置默认值
    • 添加默认约束
字段名 数据类型 DEFAULT 默认值
alter table student alter sex set default "男";
  • 删除默认约束
alter table student alter create_time drop default;
  1. 唯一约束
    用于保证表中字段的唯一性，即该字段的值不可以相同。
    • 添加唯一性约束
字段名 数据类型 UNIQUE
ALTER TABLE table_name ADD UNIQUE (字段列表)
  1. 自增约束

    只适用于正整型数据类型，一个表中只有一个AUTO_INCREMENT字段，必须具备主键或者外键属性，NOT NULL。


不同数据表中的自增属性
1. ISAM表
（1）插入NULL，从0开始
（2）插入非NULL，若与已有的重复，报错；插入值大于最大值，自增从该值开始；可重用编号
2. MyISAM
（1）编号不可重用；
（2）可以通过AUTO_INCREMENT=n指定自增初始值/可通过ALTER TABLE table_name AUTO_INCREMENT=n重设自增初始值
（3）支持复合约束
3. InnoDB
（1）不可通过AUTO_INCREMENT=n指定自增初始值/不可通过ALTER TABLE table_name AUTO_INCREMENT=n重设自增初始值
（2）不可重用编号
（3）不支持复合约束

删除指定名称下的约束

ALTER TABLE table_name DROP INDEX 约束名;

内部函数

字符串函数

函数名说明
CONCAT(str1,str2,…)将st1，st2等多个字符串合并成一个字符串返回；没有任何参数则返回NULL
CONCAT_WS(separator,str1,str2,…)使用separator将多个字符串分割相连
LENGTH(str)返回字符串的字节数；
CHAR_LENGTH(str)返回字符串的字符数
CHAR(N,…)将参数解释为整数并且返回这些整数的ASCII代码字符组成的字符串。
UPPER(str)将字符串字母转成大写字母
LOWER(str)将字符串字母转成小写字母
TRIM()删除前后的空格

数学函数

函数名说明
ABS(X)返回X的绝对值
CEIL(X)返回大于或者等于X的最小整数
FLOOR(X)返回小于或者等于X的最大整数
PI()返回圆周率
ROUND()返回四舍五入的数
RAND()返回一个0~1的随机浮点数

视图

视图是一个虚拟的表，其内容由查询语句来定义的，视图引用了数据表中的字段和数据，其本身并不存储数据。

**特点**：视图被引用的时候才派生出数据，不占用空间，对视图的操作与对数据表的操作一样，可以对其进行查询，修改删除。

创建视图

CREATE  VIEW view_name [(column_list)] AS select_statment
  1. column_list:创建视图后的列名，其中数目必须与SELECT语句中的检索列数一致。
create VIEW teacher_view AS (select id AS "编号",name AS "名字" FROM teacher where id >8);
  1. 视图和表是共享名字空间，所以数据库中不可以具有相同的表和视图；
  2. 视图必须具有唯一的列名，不可以重复；
  3. SELECT语句中不可以含有FROM子句中的子查询；
  4. SELECT语句不可以引用系统或者用户变量；
  5. SELECT语句不能引用预处理语句参数；

可更新的视图

因为视图中包含了多个基础表的信息，所以对其的添加，修改和删除操作哦有所限制。

视图包含以下结构中的任何一种，则视为不可更新的：
1. 聚合函数；
2. DISTINCT(过滤重复字段)关键字；
3. GROUP BY子句；
4. ORDER BY子句；
5. HAVING子句；
6. UNION或UNION ALL运算符；
7. 位于选择列表的子查询；
8. FROM子句中包含多个表；
9. SELECT语句中引用了不可更新的视图；
10. WHERE语句中的子查询，引用了FROM子句中的表；
11. 使用了临时表

插入数据

对视图进行插入操作，视图必须具有可插入性，满足以下条件；

  1. 不得有重复的视图列名称；
  2. 视图必须包含没有默认值的基表中所有的列；
  3. 视图列必须是简单的列引用而不是导出列；
select *from teacher_view;

注:对视图进行插入操作，基表也会造成影响；

修改数据

视图的修改跟基表语法是一样的，若视图中的数据来自于多个不同的表，在修改数据时需谨慎，最好修改基表数据。

删除数据

视图的删除与基表语法是一样的，对于依赖多表的视图，不能使用DELETE语句。

视图操作

修改视图

ALTER VIEW view_name AS (select_statment)

查看视图

SHOW CREATE VIEW view_name;

删除视图

DROP VIEW view_name;

视图的作用

  1. 集中数据，简化用户的数据查询处理。
  2. 便于数据共享。
  3. 实现复杂的查询。

索引

索引是由数据库表中一列或者多列的值进行排序的一种结构。
作用：用来提高查询速度的，通过索引，查询数据的时候可以不用读完所有记录中的所有数据。

类型：索引有两种存储类型，B型树（BTREE）索引和哈希树（HASHTREE）索引

InnoDB和MyISAM存储引擎支持BTREE索引；
MEMORY存储引擎支持HASH索引和BTREE索引；

索引的优点
1. 对于有依赖于子表和父表之间的联合查询，可以提高速度。
2. 使用分组和排序的子句进行数据的查询。

索引的缺点
1. 创建和维护表需要消耗时间，消耗时间会随着数据量而增大。
2. 索引需要占据一定的物理内存。
3. 增加，删除，修改数据的时候要动态的维护索引。
4. 索引会影响插入速度

索引的分类

普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。

CREATE INDEX <索引名> ON tablename；
ALTER TABLE talbename ADD INDEX[索引名字]

唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);

全文索引

全文索引只在CHAR，VARCHAR或者TEXT字段上，默认全文索引的不区分大小写的。

单列索引

在表中对一个字段进行索引，只要保证该索引只对应一个字段即可；

多列索引

在表的多个字段上创建索引。
添加多列索引的时候，只有使用了索引的第一个字段的时才触发索引。

空间索引

索引的创建

  1. 在创建表的时候定义
[UNIQUE | FULLTEXT |SPATIAL] [INDEX|KEY] [别名] （属性1 [长度][ASC|DESC]）
  1. 在已经存在的表中创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL ] INDEX ]索引名 ON 表名 (属性1 [长度] [ASC|DESC]);
  1. 使用ALTER创建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL]索引名 属性1(长度) [ASC|DESC];

删除索引

DROP INDEX 索引名 ON 表名
北岭山下
关注
专栏目录
MySQL数据库的简单使用（基础篇）
冰冰二号的博客
08-03 3569
MySQL数据库的简单使用（基础篇）
数据库(mysql)基本概念
m0_71521555的博客
09-11 1668
数据库(mysql)基本概念
MySQL事物的概念
天高任鸟飞
07-19 742
事务处理机制，在程序的开发过程中非常重要，可以使整个系统更加安全，保证在同一个事务中的操作具有同步性 比如，人们经常进行的转账操作，转账分为两部分来完成，转入和转出，只有这两部分都完成，才认为转账成功事务就是针对数据库的一组操作，可以由一条或多条SQL语句组成，同一个事务的操作，具备同步的特点，如果，其中有一条语句无法执行，那么，所有的语句都不会执行 事务中的语句，要么都执行，要么都不执行开启事
MySQL中获取一个或多个序列
付之一笑的专栏
09-02 2189
项目应用中，曾有以下一个场景：接口中要求发送一个int类型的流水号，由于多线程模式，如果用时间戳，可能会有重复的情况(当然概率很小)。所以想到了利用一个独立的自增的sequence来解决该问题。当前数据库为：mysql由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能，
mysql create definer,创建存储过程时是否需要`definer`？
weixin_31283549的博客
01-27 2512
I've written all of MySQL procedures as root@localhost:CREATE DEFINER=`root`@`localhost` PROCEDURE `p_add_user`(...)Trouble is, when deploying to another server, I have to replace root with current us...
mysql数据库实现nextval函数【转存】
艾利克斯冰的博客
01-28 1397
在oracle中使用序列（Sequence）来处理主键字段，现在想要在Mysql中也实现类似的效果。 1、新建序列表 drop table if exists sequence; create table sequence ( seq_name VARCHAR(50) NOT NULL, -- 序列名称 current_val INT NOT NULL, -- 当前值 increment_val INT NOT NULL DEFAULT 1,.
Mysql基础概念及常见面经.pdf
最新发布
05-23
在MySQL中，了解基础概念和常见面试题对于数据库管理和开发至关重要。以下是对这些概念的详细解释： 1. **数据库类型**： - MySQL提供多种数值类型，如TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示不同...
MySQL基础PDF版
05-16
2. **MySQL基础** - **DBMS**：数据库管理系统（DataBase Management System），如MySQL，用于科学地组织和存储数据，高效地获取和维护数据。 - **SQL语句分类**：主要包括DDL（数据定义语言）、DML（数据操作语言...
mysql数据库基础.pdf
01-02
MySQL数据库是世界上最流行的开源关系型数据库管理系统之一，其工作流程涉及多个步骤，这些步骤在处理SQL语句时至关重要。以下是对这些步骤的详细说明： ...理解这些基本概念对于有效管理和使用MySQL数据库至关重要。
MySQL数据库基础实例教程(第2版)教学大纲.pdf
06-24
通过这门课程的学习，学生不仅能够掌握MySQL的基本操作，还能进一步理解数据库管理和维护的高级概念，如存储过程、存储函数和触发器等，为成为专业的程序员、系统管理员或系统操作员打下坚实的基础。
MySQL 中int和integer什么区别
热门推荐
qq_43679849的博客
02-22 2万+
MySQL 中int和integer什么区别
mybatis返回值为int_Mybatis返回int或者Integer类型报错的解决办法
weixin_31186111的博客
01-12 2262
会报错如下：org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.apache.ibatis.binding.BindingException: Mapper method 'com.bill.springMybatis.dao.UserDao....
Mysql function(函数)
JHON07的博客
12-08 2497
1.mysql 拼接函数         1. 1CONCAT(string1,string2,…)   说明 : string1,string2代表字符串,concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL           1.2 CONCAT_WS(separator,str1,str2,...)      说明 : string1,str...
Mysql数据类型---INTEGER
RisenWang 的博客
09-07 1万+
标签（空格分隔）： MYSQL<numeric data type> ::= <integer data type> | <decimal data type> | <float data type> | <bit data type> <integer data type> ::= BOOLEAN
MySql视图
weixin_30715523的博客
07-19 247
创建视图 创建视图是指在已经存在的MySQL数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。 可以使用 CREATE VIEW 语句来创建视图。 语法格式如下： CREATE VIEW <视图名> AS <SELECT语句> 语法说明如下。 <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表...
视图
小魏的博客
03-15 352
1.ALTER VIEW语法 ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]     VIEW view_name [(column_list)]     AS select_statement     [WITH [CASCADED | LOCAL] CHECK OPTION] 2.CREATE VIEW语法 C
MySQL数据库——事务
sem00000的博客
06-28 1297
1 事务处理 事务处理在数据库开发过程中有着非常重要的作用，它可以保证在同一个事务中的操作具有同步性。本节将针对事务处理的基础知识进行讲解。 1.1 事务的概念 现实生活中，人们经常会进行转账操作，转账可以分为转人和转出两部分，只有这两个部分都完成才认为转账成功。在数据库中，这个过程是使用两条SQL语句来实现的，如果其中任意一条语句出现异常没有执行，则会导致两个账户的金额不同步，造成错误。为了防止上述情况的发生，就需要使用MySQL中的事务(Transaction)。 在MySQL中，事务就是针对数据库的一
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值