MySQL数据库_教程超详细目录:1.概念2.关系型数据库3.MySQL命令4.高级查询5.事务6.视图7.变量8.存储过程9.函数10.分支11.循环12.触发器MySQL数据库MySQL 是最流行的关系型数据库管理系统在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System关系数据库管理系统)应用软件之一。MySQL 是一个关系型数据库管理系统由瑞典 MySQL AB 公司开发目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统关联数据库将数据保存在不同的表中而不是将所有数据放在一个大仓库内这样就增加了速度并提高了灵活性。MySQL 是开源的所以你不需要支付额外的费用。MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL 使用标准的 SQL 数据语言形式。MySQL 可以运行于多个系统上并且支持多种语言。这些编程语言包括 C、C、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。MySQL 支持大型数据库支持 5000 万条记录的数据仓库32 位系统表文件最大可支持 4GB64 位系统支持最大的表文件为8TB。MySQL 是可以定制的采用了 GPL 协议你可以修改源码来开发自己的 MySQL 系统。数据库相关概念DB数据库保存一组有组织的数据的容器DBMS数据库管理系统又称为数据库软件产品用于管理DB中的数据SQL:结构化查询语言用于和DBMS通信的语言关系型数据库数据库Database是按照数据结构来;每个数据库都有一个或多个不同的 API 用于创建访问管理搜索和复制所保存的数据。我们也可以将数据存储在文件中但是在文件中读写数据速度相对较慢。所以现在我们使用关系型数据库管理系统RDBMS来存储和管理的大数据量。所谓的关系型数据库是建立在关系模型基础上的数据库借助于集合代数等数学概念和方法来处理数据库中的数据。RDBMS 即关系数据库管理系统(Relational Database Management System)的特点数据以表格的形式出现每行为各种记录名称每列为记录名称所对应的数据域许多的行和列组成一张表单若干的表单组成databaseRDBMS 术语数据库:数据库是一些关联表的集合。数据表:表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。列字段:一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。**行记录**一行元组或记录是一组相关的数据例如一条用户订阅的数据。冗余存储两倍数据冗余降低了性能但提高了数据的安全性。主键主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。**外键**外键用于关联两个表。复合键复合键组合键将多个列作为一个索引键一般用于复合索引。**索引**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件目的是保证数据的一致性。MySQL命令启动或停止MySQL服务方式一计算机——右击管理——服务 方式二通过管理员身份运行 net start 服务名启动服务 net stop 服务名停止服务MySQL服务的登录和退出mysql [-h主机名 -P端口号] -u用户名 -p密码参数描述host可选。规定主机名或 IP 地址。username可选。规定 MySQL 用户名。password可选。规定 MySQL 密码。port可选。规定尝试连接到 MySQL 服务器的端口号。MySQL操作数据库命令1.创建数据库 CREATE DATABASE 数据库名; 2.删除数据库 drop database 数据库名; 3.选择数据库 use 数据库名; 4.显示MySQL管理的所有数据库 SHOW DATABASES; 5.查看当前打开的数据库 SELECT DATABASE();数据库数据类型数值类型类型大小范围有符号范围无符号用途TINYINT1 字节(-128127)(0255)小整数值SMALLINT2 字节(-32 76832 767)(065 535)大整数值MEDIUMINT3 字节(-8 388 6088 388 607)(016 777 215)大整数值INT或INTEGER4 字节(-2 147 483 6482 147 483 647)(04 294 967 295)大整数值BIGINT8 字节(-9,223,372,036,854,775,8089 223 372 036 854 775 807)(018 446 744 073 709 551 615)极大整数值FLOAT(8,2)4 字节(-3.402 823 466 E38-1.175 494 351 E-38)0(1.175 494 351 E-383.402 823 466 351 E38)0(1.175 494 351 E-383.402 823 466 E38)单精度 浮点数值DOUBLE(5,1)8 字节(-1.797 693 134 862 315 7 E308-2.225 073 858 507 201 4 E-308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)双精度 浮点数值DECIMAL对DECIMAL(M,D) 如果MD为M2否则为D2依赖于M和D的值依赖于M和D的值小数值时间类型类型大小 (字节)范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2038结束时间是第2147483647秒北京时间2038-1-19 11:14:07格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值时间戳字符串类型类型大小用途CHAR(255)0-255字节定长字符串VARCHAR(255)0-65535 字节变长字符串TINYBLOB0-255字节不超过 255 个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65 535字节二进制形式的长文本数据TEXT0-65 535字节长文本数据MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215字节中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-4 294 967 295字节极大文本数据数据表操作命令1.创建数据库 CREATE TABLE table_name (column_name column_type); 例子 CREATE TABLE department ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, manager VARCHAR(25) ); CREATE TABLE employee( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL, sex ENUM(男,女) DEFAULT 男, salary DOUBLE(8,1), d_id INT UNSIGNED, FOREIGN KEY(d_id) REFERENCES department(id) ) 2.删除表 DROP TABLE table_name ; 3.查看表结构 DESC table_name;字段约束主键约束(PRIMARY KEY每张表只能存在一个主键主键的作用主要用于保证记录的唯一性主键约束默认添加非空约束NOT NULL主键值必须唯一自增约束(AUTO/_INCREMENT)自增约束必须配合主键使用默认情况下起始值为1每次的增加量为1外键约束FOREIGN KEY值必须为指向的表的主键值。外键约束主要用于实现一对一一对多多对多关系外键约束的要求父表和子表必须使用相同的存储引擎。外键和参考列的数据类型必须相同。外键列和参考列必须创建索引。如果外键列不存在索引的话MySQL将自动创建索引。CREATE TABLE user ( id SMALLINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, pid TINYINT, FOREIGN KEY (pid) REFERENCES province(id) );唯一约束UNIQUE唯一约束可以保证记录的唯一性唯一约束的字段可以为空值(NULL)每张表可以有多个唯一约束非空约束NOT NULL字段默认为可以为NULL非空约束的字段不能为NULL默认约束DEFAULT当插入记录时如果没有明确为字段赋值则自动赋予默认值。无符号约束UNSIGNED通过UNSIGNED修饰的整型数只能保存正数3NF范式1NF : 字段不可再分2NF必须要有主键字段和主键相关3NF字段必须和主键直接相关表之间的关联关系1-1:外键可以在任意一方外键需要添加唯一约束1-n/n-1外键放在N端n-n需要创建中间表中间表中有两个字段分别创建外键指向两张表把两个外键组合生成复合主键。修改表结构1.添加列 ALTER TABLE blname ADD [COLUMN] colname columndefinition [FIRST | AFTER colname] 例 ALTER TABLE user ADD age TINYINT UNSIGNED; ALTER TABLE user ADD password VARCHAR(20) AFTER name; 2.删除列 ALTER TABLE tblname DROP [COLUMN] colname 例 ALTER TABLE user DROP password; 3.添加外键约束 ALTER TABLE tblname ADD FOREIGN KEY (indexcolname) referencedefinition 例 ALTER TABLE user02 ADD FOREIGN KEY (pid) REFERENCES province(id); 4.添加非空约束 ALTER TABLE user MODIFY id TINYINT UNSIGNED NOT NULL; 5.修改字段数据类型 ALTER TABLE tbl_name MODIFY COLUMN name TEXT数据操作命令插入语句INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN);查询数据SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]查询语句中你可以使用一个或者多个表表之间使用逗号(,)分割并使用WHERE语句来设定查询条件。SELECT 命令可以读取一条或者多条记录。你可以使用星号/*来代替其他字段SELECT语句会返回表的所有字段数据你可以使用 WHERE 语句来包含任何条件。你可以使用 LIMIT 属性来设定返回的记录数。你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。WHERE语句你可以在 WHERE 子句中指定任何条件。你可以使用 AND 或者 OR 指定一个或多个条件。WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。WHERE 子句类似于程序语言中的 if 条件根据 MySQL 表中的字段值来读取指定的数据。WHERE支持条件运算符 ! 更新数据UPDATE table_name SET field1new-value1, field2new-value2 [WHERE Clause]你可以同时更新一个或多个字段。你可以在 WHERE 子句中指定任何条件。你可以在一个单独表中同时更新数据。删除数据DELETE FROM table_name [WHERE Clause]如果没有指定 WHERE 子句MySQL 表中的所有记录将被删除。你可以在 WHERE 子句中指定任何条件您可以在单个表中一次性删除记录。高级查询排序查询ORDER BY使用order by 可以对查询出来的结果进行排序默认采用升序的方式可以使用DESC降序、ASC升序控制排序的方式SELECT * FROM student ORDER BY birth DESC;常用函数1.字符函数 concat拼接 substr截取子串 upper转换成大写 lower转换成小写 trim去前后指定的空格和字符 ltrim去左边空格 rtrim去右边空格 replace替换 lpad左填充 rpad右填充 instr返回子串第一次出现的索引 length 获取字节个数 2、数学函数 round 四舍五入 rand 随机数 floor向下取整 ceil向上取整 mod取余 truncate截断 3、日期函数 now当前系统日期时间 curdate当前系统日期 curtime当前系统时间 /* %Y代表4位的年份 %y代表2为的年份 %m代表月, 格式为(01……12) %c代表月, 格式为(1……12) %d代表月份中的天数,格式为(00……31) %e代表月份中的天数, 格式为(0……31) %H代表小时,格式为(00……23) %k代表 小时,格式为(0……23) %h 代表小时,格式为(01……12) %I 代表小时,格式为(01……12) %l 代表小时,格式为(1……12) %i 代表分钟, 格式为(00……59) 【只有这一个代表分钟大写的I 不代表分钟代表小时】 %r代表 时间,格式为12 小时(hh:mm:ss [AP]M) %T代表 时间,格式为24 小时(hh:mm:ss) %S代表 秒,格式为(00……59) %s代表 秒,格式为(00……59) */ str_to_date 将字符转换成日期 date_format将日期转换成字符 4、流程控制函数 if 处理双分支 ifnull SELECT id,stu_id,c_name, CASE WHEN IFNULL(grade,0) 60 THEN E WHEN IFNULL(grade,0) 70 THEN D WHEN IFNULL(grade,0) 80 THEN C WHEN IFNULL(grade,0) 90 THEN B WHEN IFNULL(grade,0) 100 THEN A ELSE 错误 END AS grade FROM score; 5.聚合函数 sum 求和 max 最大值 min 最小值 avg 平均值 count 计数分组查询GROUP BY把查询的结构根据所选字段进行分组字段值相同则分为一组。打印当前组的第一条数据我们可以先分组再排序不支持先排序再分组如果对查询结果进行了分组则再添加过滤条件时需要用HAVINGSELECT stu_id,c_name,grade FROM score GROUP BY stu_id SELECT stu_id,c_name,grade FROM score GROUP BY stu_id HAVING stu_id 903; SELECT stu_id,c_name,grade FROM score WHERE stu_id 903 GROUP BY stu_id ;子查询一个语句查询的结果可以作为另一个查询的条件使用子查询指嵌套在查询内部且必须始终出现在圆括号内。子查询可以包含多个关键字或条件如GROUP BY, ORDER BY, LIMIT函数等。子查询的外层查询可以是SELECT, INSERT, UPDATE, SET 或 DO。#查询成绩大于平均成绩的成绩 SELECT avg(grade) FROM score; SELECT * FROM score WHERE grade 85.6; #子查询 SELECT * FROM score WHERE grade (SELECT avg(grade) FROM score);当子查询的返回不是一个值时分页查询limitSELECT * FROM score WHERE stu_id LIMIT 1,1; #第一个参数起始下标 #第二个参数返回的记录个数 #使用limit实现分页查询 /* 当前显示的页数 2 index 每页显示个数 3 number */ SELECT * FROM score WHERE stu_id LIMIT (index-1)*number,number;多表联查多表联查主要分为三种INNER JOIN内连接,或等值连接获取两个表中字段匹配关系的记录。**LEFT JOIN左连接**获取左表所有记录即使右表没有对应匹配的记录。RIGHT JOIN右连接与 LEFT JOIN 相反用于获取右表所有记录即使左表没有对应匹配的记录。SELECT * FROM student INNER JOIN teacher ON student.t_id teacher.id; SELECT * FROM student LEFT JOIN teacher ON student.t_id teacher.id; SELECT * FROM student RIGHT JOIN teacher ON student.t_id teacher.id;联合查询union#联合查询默认取出重复数据 #可以使用 UNION ALL保留重复数据 SELECT id,name FROM student UNION SELECT id,name FROM teacher ...;模糊查询like使用 %任意个任意字符 进行模糊查询时/_ (任意一个字符)SELECT * FROM student WHERE name LIKE %曹%;正则查询REGEXP SELECT * FROM student WHERE name REGEXP ^.{9}$;数据库去重#把查询出的数据保存到新创建的表中 CREATE TABLE test SELECT name,sex FROM teacher;# 通过分组去掉重复数据并保存到新表中 CREATE TABLE test SELECT * FROM teacher GROUP BY name,sex; # 删除旧表 DROP TABLE teacher; #把新表进行重命名 ALTER TABLE test RENAME TO teacher;多表更新UPDATE student INNER JOIN sex ON student.sex sex.sex SET student.sex sex.id;事务通过一组逻辑操作单元SQL语句将数据从一种稳定状态切换到另外一种稳定状态特点一般来说事务是必须满足4个条件ACID原子性Atomicity或称不可分割性、一致性Consistency、隔离性Isolation又称独立性、持久性Durability。**原子性**一个事务transaction中的所有操作要么全部完成要么全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样。**一致性**在事务开始之前和事务结束以后数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。**隔离性**数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别包括读未提交Read uncommitted、读提交read committed、可重复读repeatable read和串行化Serializable。**持久性**事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。使用事务隐式事物insert、update、delete语句本身就是一个事务显示事物# 1.开启事务 BEGIN #或 SET AUTOCOMMIT0 #禁止自动提交 START TRANSACTION #2.执行SQL语句 SAVEPOINT identifier # 添加保存点 ROLLBACK TO identifier # #3.提交或回滚事务 ROLLBACK #事务回滚 COMMIT #事务确认隔离级别事务并发问题如何发生当多个事务同时操作同一个数据库的相同数据时,数据库数据可能发生损坏。对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.数据库提供的事务隔离级别Oracle 支持的 2 种事务隔离级别READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITEDMysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READInnoDB和Falcon存储引擎通过多版本并发控制MVCCMultiversion Concurrency Control机制解决了幻读问题。查看和设置数据库隔离级别每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 tx/_isolation, 表示当前的事务隔离级别 查看当前的隔离级别:#设置当前连接的会话级别 set session transaction isolation level Serializable; set tx_isolation 2; READ-UNCOMMITTED : 0; READ-COMMITTED : 1; REPEATABLE-READ : 2; SERIALIZABLE : 3; #设置整个系统会话级别 set global.tx_isolation 3; #查看当前连接会话级别 SELECT tx_isolation; #查看整个系统会话级别 SELECT global.tx_isolation;视图视图虚拟表从一个表或多个表中导出来的表作用和真实表一样包含一系列带有行和列的数据 视图中用户可以使用SELECT语句查询数据也可以使用INSERTUPDATEDELETE修改记录视图可以使用户操作方便并保障数据库系统安全优点简单化数据所见即所得安全性用户只能查询或修改他们所能见到得到的数据逻辑独立性可以屏蔽真实表结构变化带来的影响缺点性能相对较差简单的查询也会变得稍显复杂修改不方便特变是复杂的聚合视图基本无法修改视图的作用简化复杂的sql操作编写sql查询后重用便捷而无需关注查询细节对重构数据库提供逻辑独立保护数据仅部分显示数据保障机密数据安全让数据展示更加精简清晰创建视图CREATE VIEW 视图名 AS 查询语句;视图注意事项视图名与表名一样必须唯一创建视图的必须有足够权限权限由数据库管理员授予视图可以嵌套即可以从其他视图中检索数据创建新的视图不能在视图上建立索引也不能关联触发器视图可以和物理表共同使用对视图进行更新时实际更新的是创建视图的表有下列内容之一视图不能做增删改操作①select子句中包含多表联查②select子句中包含组函数③select语句中包含group by子句④select语句中包含order by子句⑤select语句中包含union 、union all等集合运算符⑥where子句中包含相关子查询⑦from子句中包含多个表⑧如果视图中有计算列则不能更新⑨如果基表中有某个具有非空约束的列未出现在视图定义中则不能做insert操作修改视图#方法一 CREATE OR REPLACE VIEW test_v7 AS SELECT ... #方法二 ALTER VIEW test_v7 AS SELECT ...删除视图DROP VIEW test_v1,test_v2,test_v3;查看视图结构#方法一 DESC test_v7; #方法二 SHOW CREATE VIEW test_v7;变量系统变量全局变量作用域针对于所有会话连接有效但不能跨重启查看所有全局变量 SHOW GLOBAL VARIABLES; 查看满足条件的部分系统变量 SHOW GLOBAL VARIABLES LIKE %char%; 查看指定的系统变量的值 SELECT global.autocommit; 为某个系统变量赋值 SET global.autocommit0; SET GLOBAL autocommit0;会话变量作用域针对于当前会话连接有效查看所有会话变量 SHOW SESSION VARIABLES; 查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE %char%; 查看指定的会话变量的值 SELECT autocommit; SELECT session.tx_isolation; 为某个会话变量赋值 SET session.tx_isolationread-uncommitted; SET SESSION tx_isolationread-committed;自定义变量用户变量声明并初始化SET 变量名值; SET 变量名:值; SELECT 变量名:值;赋值方式一一般用于赋简单的值 SET 变量名值; SET 变量名:值; SELECT 变量名:值;方式二一般用于赋表 中的字段值 SELECT 字段名或表达式 INTO 变量 FROM 表;使用select 变量名;局部变量声明declare 变量名 类型 【default 值】; 注此声明只能放到begin end中进行使用且只能定义在第一行可以连续定义多个变量进行使用。赋值方式一一般用于赋简单的值 SET 变量名值; SET 变量名:值; SELECT 变量名:值;方式二一般用于赋表 中的字段值 SELECT 字段名或表达式 INTO 变量FROM 表;使用SELECT 变量名存储过程(PROCEDURE)存储过程(Procedure),是数据库操作语言SQL的可编程性实现传统的SQL语句通常是在数据库服务器中执行一条命令命令在数据库引擎内部需要经过创建与分析的过程因此在运行时间上开销较大而存储过程是预编译的在首次运行一个存储过程时查询优化器对其进行分析、优化并给出最终被存在系统表中的存储计划这样后期的使用只需通过命令调用即可因此在执行过程时便可节省此开销。存储过程优点提高性能降低网络开销分担应用程序需要执行的业务逻辑(如:订单生成通用分页等)更强的安全性比sql语句更为强大的处理能力存储过程缺点可移植性差(不同数据库服务器存储过程语法差异较大一旦变更DBMS则存储过程需要整体重构)逻辑复杂不如sql灵活实际应用相对较少存储过程创建语法在命令行中编写存储过程时可通过DELIMITER修改默认结束标记(推荐图形界面工具)DELIMITER $ (使用“$”符号作为结束标记符号可自定义)创建存储过程CREATE PROCEDURE 过程名(in|out|inout 参数名 参数类型,...) BEGIN [DECLARE] 临时变量 -- 执行体 END 参数前面的符号的意思 in:该参数只能作为输入 该参数不能做返回值 out该参数只能作为输出该参数只能做返回值 inout既能做输入又能做输出调用存储过程CALL 存储过程名();删除存储过程DROP PROCEDURE 存储过程名;函数function函数有着跟存储过程类似的实现原理但是两者之间也存在本质区别具体如下一般来说存储过程实现的功能要复杂一点而函数的实现的功能针对性比较强。存储过程功能强大可以执行包括修改表等一系列数据库操作用户定义函数不能用于执行一组修改全局数据库状态的操作。对于存储过程来说可以返回参数如记录集而函数只能返回值或者表对象。函数只能返回一个变量而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型而函数只能有IN类存储过程声明时不需要返回类型而函数声明时需要描述返回类型且函数体中必须包含一个有效的RETURNS语句。存储过程可以使用非确定函数不允许在用户定义函数主体中内置非确定函数。存储过程一般是作为一个独立的部分来执行 EXECUTE 语句执行而函数可以作为查询语句的一个部分来调用SELECT调用由于函数可以返回一个表对象因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程而可以使用函数。MySQL函数分为以下两类:预定义函数自定义函数其中预定义函数为mysql内置函数主要包含以下:字符串函数数值函数日期函数系统信息函数加密函数格式化函数创建函数CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型 BEGIN 函数体 END int add(int a, int b){ return ab; }函数的调用SELECT 函数名实参列表删除函数DROP FUNCTION 函数名;分支if函数语法if(条件值1值2) 特点可以用在任何位置case语句语法情况一类似于switch case 表达式 when 值1 then 结果1或语句1(如果是语句需要加分号) when 值2 then 结果2或语句2(如果是语句需要加分号) ... else 结果n或语句n(如果是语句需要加分号) end [case]如果是放在begin end中需要加上case如果放在select后面不需要 情况二类似于多重if case when 条件1 then 结果1或语句1(如果是语句需要加分号) when 条件2 then 结果2或语句2(如果是语句需要加分号) ... else 结果n或语句n(如果是语句需要加分号) end 【case】如果是放在begin end中需要加上case如果放在select后面不需要特点 可以用在任何位置if elseif语句语法if 情况1 then 语句1; elseif 情况2 then 语句2; ... else 语句n; end if;特点 只能用在begin end中三者比较 应用场合 if函数 简单双分支 case结构 等值判断 的多分支 if结构 区间判断 的多分支循环语法Label:while 循环条件 do 循环内容 END while label; Label:repeat 循环内容 Until 结束条件 END repeat label; Label:loop 循环内容 END loop labeel;特点iterate跳过当次循环 leave跳出循环触发器触发器TRIGGER是MySQL的数据库对象之一从5.0.2版本开始支持。该对象与编程语言中的存储过程以及函数非常类似都是SQL语句可编程性的实现并且都需要编写、编译以及调用。但是触发器的执行不是由程序调用也不是由手动启动而是由事件来触发、激活以及执行。那么为什么要使用数据库对象触发器呢在具体开发项目时经常会遇到如下实例1. 在删除部门表中记录时员工表因为设置外键约束关联部门表导致部门表中记录无法删除如何在执行部门删除时同时将关联的员工表中部门号列设置为NULL。2. 在对员工表中员工数据删除时如何自动将被删除的员工信息记录到备份表中。上面的例子使用触发器完成时具有这样的特点需要在表发生改变时自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作对SELECT语句则不会激活触发器。触发器语法触发器创建语法CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_body END;trigger/_name触发器的名称不能与已经存在的触发器重复trigger/_time{ BEFORE | AFTER }表示在事件之前或之后触发trigger/_event{ INSERT |UPDATE | DELETE }触发该触发器的具体事件tbl/_name该触发器作用在tbl/_name上删除触发器:DROP TRIGGER 触发器名;CREATE TRIGGER #创建触发器 myt1 #出发器的名字 BEFORE DELETE #什么时候触发 ON teacher #触发事件的表 FOR EACH ROW BEGIN UPDATE student SET t_id null WHERE t_id OLD.id; END DROP TRIGGER myt1; DELETE FROM teacher WHERE id 1; NEW和OLDNEW在触发器为INSERT/UPDATE事件类型时有效表示当前正在插入的数据同理OLD在触发器类型为DELETE/UPDATE事件类型时有效表示当前正在删除的数据。具体如下表:INSERTUPDATEDELETENEW可用可用无效OLD无效可用可用《网络安全从零到精通全套学习大礼包》96节从入门到精通的全套视频教程免费领取如果你也想通过学网络安全技术去帮助就业和转行我可以把我自己亲自录制的96节 从零基础到精通的视频教程以及配套学习资料无偿分享给你。网络安全学习路线图想要学习 网络安全作为新手一定要先按照路线图学习方向不对努力白费。对于从来没有接触过网络安全的同学我帮大家准备了从零基础到精通学习成长路线图以及学习规划。可以说是最科学最系统的学习路线大家跟着这个路线图学习准没错。配套实战项目/源码所有视频教程所涉及的实战项目和项目源码学习电子书籍学习网络安全必看的书籍和文章的PDF市面上网络安全书籍确实太多了这些是我精选出来的面试真题/经验以上资料如何领取(https://i-blog.csdnimg.cn/direct/88f12e9068e14b728b215f939a9ed28e.gif)网络安全学习路线图想要学习 网络安全作为新手一定要先按照路线图学习方向不对努力白费。对于从来没有接触过网络安全的同学我帮大家准备了从零基础到精通学习成长路线图以及学习规划。可以说是最科学最系统的学习路线大家跟着这个路线图学习准没错。配套实战项目/源码所有视频教程所涉及的实战项目和项目源码学习电子书籍学习网络安全必看的书籍和文章的PDF市面上网络安全书籍确实太多了这些是我精选出来的面试真题/经验以上资料如何领取
MySQL数据库_教程（超详细）
发布时间：2026/5/30 2:06:52
MySQL数据库_教程超详细目录:1.概念2.关系型数据库3.MySQL命令4.高级查询5.事务6.视图7.变量8.存储过程9.函数10.分支11.循环12.触发器MySQL数据库MySQL 是最流行的关系型数据库管理系统在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System关系数据库管理系统)应用软件之一。MySQL 是一个关系型数据库管理系统由瑞典 MySQL AB 公司开发目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统关联数据库将数据保存在不同的表中而不是将所有数据放在一个大仓库内这样就增加了速度并提高了灵活性。MySQL 是开源的所以你不需要支付额外的费用。MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL 使用标准的 SQL 数据语言形式。MySQL 可以运行于多个系统上并且支持多种语言。这些编程语言包括 C、C、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。MySQL 支持大型数据库支持 5000 万条记录的数据仓库32 位系统表文件最大可支持 4GB64 位系统支持最大的表文件为8TB。MySQL 是可以定制的采用了 GPL 协议你可以修改源码来开发自己的 MySQL 系统。数据库相关概念DB数据库保存一组有组织的数据的容器DBMS数据库管理系统又称为数据库软件产品用于管理DB中的数据SQL:结构化查询语言用于和DBMS通信的语言关系型数据库数据库Database是按照数据结构来;每个数据库都有一个或多个不同的 API 用于创建访问管理搜索和复制所保存的数据。我们也可以将数据存储在文件中但是在文件中读写数据速度相对较慢。所以现在我们使用关系型数据库管理系统RDBMS来存储和管理的大数据量。所谓的关系型数据库是建立在关系模型基础上的数据库借助于集合代数等数学概念和方法来处理数据库中的数据。RDBMS 即关系数据库管理系统(Relational Database Management System)的特点数据以表格的形式出现每行为各种记录名称每列为记录名称所对应的数据域许多的行和列组成一张表单若干的表单组成databaseRDBMS 术语数据库:数据库是一些关联表的集合。数据表:表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。列字段:一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。**行记录**一行元组或记录是一组相关的数据例如一条用户订阅的数据。冗余存储两倍数据冗余降低了性能但提高了数据的安全性。主键主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。**外键**外键用于关联两个表。复合键复合键组合键将多个列作为一个索引键一般用于复合索引。**索引**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件目的是保证数据的一致性。MySQL命令启动或停止MySQL服务方式一计算机——右击管理——服务 方式二通过管理员身份运行 net start 服务名启动服务 net stop 服务名停止服务MySQL服务的登录和退出mysql [-h主机名 -P端口号] -u用户名 -p密码参数描述host可选。规定主机名或 IP 地址。username可选。规定 MySQL 用户名。password可选。规定 MySQL 密码。port可选。规定尝试连接到 MySQL 服务器的端口号。MySQL操作数据库命令1.创建数据库 CREATE DATABASE 数据库名; 2.删除数据库 drop database 数据库名; 3.选择数据库 use 数据库名; 4.显示MySQL管理的所有数据库 SHOW DATABASES; 5.查看当前打开的数据库 SELECT DATABASE();数据库数据类型数值类型类型大小范围有符号范围无符号用途TINYINT1 字节(-128127)(0255)小整数值SMALLINT2 字节(-32 76832 767)(065 535)大整数值MEDIUMINT3 字节(-8 388 6088 388 607)(016 777 215)大整数值INT或INTEGER4 字节(-2 147 483 6482 147 483 647)(04 294 967 295)大整数值BIGINT8 字节(-9,223,372,036,854,775,8089 223 372 036 854 775 807)(018 446 744 073 709 551 615)极大整数值FLOAT(8,2)4 字节(-3.402 823 466 E38-1.175 494 351 E-38)0(1.175 494 351 E-383.402 823 466 351 E38)0(1.175 494 351 E-383.402 823 466 E38)单精度 浮点数值DOUBLE(5,1)8 字节(-1.797 693 134 862 315 7 E308-2.225 073 858 507 201 4 E-308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)双精度 浮点数值DECIMAL对DECIMAL(M,D) 如果MD为M2否则为D2依赖于M和D的值依赖于M和D的值小数值时间类型类型大小 (字节)范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2038结束时间是第2147483647秒北京时间2038-1-19 11:14:07格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值时间戳字符串类型类型大小用途CHAR(255)0-255字节定长字符串VARCHAR(255)0-65535 字节变长字符串TINYBLOB0-255字节不超过 255 个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65 535字节二进制形式的长文本数据TEXT0-65 535字节长文本数据MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215字节中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-4 294 967 295字节极大文本数据数据表操作命令1.创建数据库 CREATE TABLE table_name (column_name column_type); 例子 CREATE TABLE department ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, manager VARCHAR(25) ); CREATE TABLE employee( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL, sex ENUM(男,女) DEFAULT 男, salary DOUBLE(8,1), d_id INT UNSIGNED, FOREIGN KEY(d_id) REFERENCES department(id) ) 2.删除表 DROP TABLE table_name ; 3.查看表结构 DESC table_name;字段约束主键约束(PRIMARY KEY每张表只能存在一个主键主键的作用主要用于保证记录的唯一性主键约束默认添加非空约束NOT NULL主键值必须唯一自增约束(AUTO/_INCREMENT)自增约束必须配合主键使用默认情况下起始值为1每次的增加量为1外键约束FOREIGN KEY值必须为指向的表的主键值。外键约束主要用于实现一对一一对多多对多关系外键约束的要求父表和子表必须使用相同的存储引擎。外键和参考列的数据类型必须相同。外键列和参考列必须创建索引。如果外键列不存在索引的话MySQL将自动创建索引。CREATE TABLE user ( id SMALLINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, pid TINYINT, FOREIGN KEY (pid) REFERENCES province(id) );唯一约束UNIQUE唯一约束可以保证记录的唯一性唯一约束的字段可以为空值(NULL)每张表可以有多个唯一约束非空约束NOT NULL字段默认为可以为NULL非空约束的字段不能为NULL默认约束DEFAULT当插入记录时如果没有明确为字段赋值则自动赋予默认值。无符号约束UNSIGNED通过UNSIGNED修饰的整型数只能保存正数3NF范式1NF : 字段不可再分2NF必须要有主键字段和主键相关3NF字段必须和主键直接相关表之间的关联关系1-1:外键可以在任意一方外键需要添加唯一约束1-n/n-1外键放在N端n-n需要创建中间表中间表中有两个字段分别创建外键指向两张表把两个外键组合生成复合主键。修改表结构1.添加列 ALTER TABLE blname ADD [COLUMN] colname columndefinition [FIRST | AFTER colname] 例 ALTER TABLE user ADD age TINYINT UNSIGNED; ALTER TABLE user ADD password VARCHAR(20) AFTER name; 2.删除列 ALTER TABLE tblname DROP [COLUMN] colname 例 ALTER TABLE user DROP password; 3.添加外键约束 ALTER TABLE tblname ADD FOREIGN KEY (indexcolname) referencedefinition 例 ALTER TABLE user02 ADD FOREIGN KEY (pid) REFERENCES province(id); 4.添加非空约束 ALTER TABLE user MODIFY id TINYINT UNSIGNED NOT NULL; 5.修改字段数据类型 ALTER TABLE tbl_name MODIFY COLUMN name TEXT数据操作命令插入语句INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN);查询数据SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]查询语句中你可以使用一个或者多个表表之间使用逗号(,)分割并使用WHERE语句来设定查询条件。SELECT 命令可以读取一条或者多条记录。你可以使用星号/*来代替其他字段SELECT语句会返回表的所有字段数据你可以使用 WHERE 语句来包含任何条件。你可以使用 LIMIT 属性来设定返回的记录数。你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。WHERE语句你可以在 WHERE 子句中指定任何条件。你可以使用 AND 或者 OR 指定一个或多个条件。WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。WHERE 子句类似于程序语言中的 if 条件根据 MySQL 表中的字段值来读取指定的数据。WHERE支持条件运算符 ! 更新数据UPDATE table_name SET field1new-value1, field2new-value2 [WHERE Clause]你可以同时更新一个或多个字段。你可以在 WHERE 子句中指定任何条件。你可以在一个单独表中同时更新数据。删除数据DELETE FROM table_name [WHERE Clause]如果没有指定 WHERE 子句MySQL 表中的所有记录将被删除。你可以在 WHERE 子句中指定任何条件您可以在单个表中一次性删除记录。高级查询排序查询ORDER BY使用order by 可以对查询出来的结果进行排序默认采用升序的方式可以使用DESC降序、ASC升序控制排序的方式SELECT * FROM student ORDER BY birth DESC;常用函数1.字符函数 concat拼接 substr截取子串 upper转换成大写 lower转换成小写 trim去前后指定的空格和字符 ltrim去左边空格 rtrim去右边空格 replace替换 lpad左填充 rpad右填充 instr返回子串第一次出现的索引 length 获取字节个数 2、数学函数 round 四舍五入 rand 随机数 floor向下取整 ceil向上取整 mod取余 truncate截断 3、日期函数 now当前系统日期时间 curdate当前系统日期 curtime当前系统时间 /* %Y代表4位的年份 %y代表2为的年份 %m代表月, 格式为(01……12) %c代表月, 格式为(1……12) %d代表月份中的天数,格式为(00……31) %e代表月份中的天数, 格式为(0……31) %H代表小时,格式为(00……23) %k代表 小时,格式为(0……23) %h 代表小时,格式为(01……12) %I 代表小时,格式为(01……12) %l 代表小时,格式为(1……12) %i 代表分钟, 格式为(00……59) 【只有这一个代表分钟大写的I 不代表分钟代表小时】 %r代表 时间,格式为12 小时(hh:mm:ss [AP]M) %T代表 时间,格式为24 小时(hh:mm:ss) %S代表 秒,格式为(00……59) %s代表 秒,格式为(00……59) */ str_to_date 将字符转换成日期 date_format将日期转换成字符 4、流程控制函数 if 处理双分支 ifnull SELECT id,stu_id,c_name, CASE WHEN IFNULL(grade,0) 60 THEN E WHEN IFNULL(grade,0) 70 THEN D WHEN IFNULL(grade,0) 80 THEN C WHEN IFNULL(grade,0) 90 THEN B WHEN IFNULL(grade,0) 100 THEN A ELSE 错误 END AS grade FROM score; 5.聚合函数 sum 求和 max 最大值 min 最小值 avg 平均值 count 计数分组查询GROUP BY把查询的结构根据所选字段进行分组字段值相同则分为一组。打印当前组的第一条数据我们可以先分组再排序不支持先排序再分组如果对查询结果进行了分组则再添加过滤条件时需要用HAVINGSELECT stu_id,c_name,grade FROM score GROUP BY stu_id SELECT stu_id,c_name,grade FROM score GROUP BY stu_id HAVING stu_id 903; SELECT stu_id,c_name,grade FROM score WHERE stu_id 903 GROUP BY stu_id ;子查询一个语句查询的结果可以作为另一个查询的条件使用子查询指嵌套在查询内部且必须始终出现在圆括号内。子查询可以包含多个关键字或条件如GROUP BY, ORDER BY, LIMIT函数等。子查询的外层查询可以是SELECT, INSERT, UPDATE, SET 或 DO。#查询成绩大于平均成绩的成绩 SELECT avg(grade) FROM score; SELECT * FROM score WHERE grade 85.6; #子查询 SELECT * FROM score WHERE grade (SELECT avg(grade) FROM score);当子查询的返回不是一个值时分页查询limitSELECT * FROM score WHERE stu_id LIMIT 1,1; #第一个参数起始下标 #第二个参数返回的记录个数 #使用limit实现分页查询 /* 当前显示的页数 2 index 每页显示个数 3 number */ SELECT * FROM score WHERE stu_id LIMIT (index-1)*number,number;多表联查多表联查主要分为三种INNER JOIN内连接,或等值连接获取两个表中字段匹配关系的记录。**LEFT JOIN左连接**获取左表所有记录即使右表没有对应匹配的记录。RIGHT JOIN右连接与 LEFT JOIN 相反用于获取右表所有记录即使左表没有对应匹配的记录。SELECT * FROM student INNER JOIN teacher ON student.t_id teacher.id; SELECT * FROM student LEFT JOIN teacher ON student.t_id teacher.id; SELECT * FROM student RIGHT JOIN teacher ON student.t_id teacher.id;联合查询union#联合查询默认取出重复数据 #可以使用 UNION ALL保留重复数据 SELECT id,name FROM student UNION SELECT id,name FROM teacher ...;模糊查询like使用 %任意个任意字符 进行模糊查询时/_ (任意一个字符)SELECT * FROM student WHERE name LIKE %曹%;正则查询REGEXP SELECT * FROM student WHERE name REGEXP ^.{9}$;数据库去重#把查询出的数据保存到新创建的表中 CREATE TABLE test SELECT name,sex FROM teacher;# 通过分组去掉重复数据并保存到新表中 CREATE TABLE test SELECT * FROM teacher GROUP BY name,sex; # 删除旧表 DROP TABLE teacher; #把新表进行重命名 ALTER TABLE test RENAME TO teacher;多表更新UPDATE student INNER JOIN sex ON student.sex sex.sex SET student.sex sex.id;事务通过一组逻辑操作单元SQL语句将数据从一种稳定状态切换到另外一种稳定状态特点一般来说事务是必须满足4个条件ACID原子性Atomicity或称不可分割性、一致性Consistency、隔离性Isolation又称独立性、持久性Durability。**原子性**一个事务transaction中的所有操作要么全部完成要么全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样。**一致性**在事务开始之前和事务结束以后数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。**隔离性**数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别包括读未提交Read uncommitted、读提交read committed、可重复读repeatable read和串行化Serializable。**持久性**事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。使用事务隐式事物insert、update、delete语句本身就是一个事务显示事物# 1.开启事务 BEGIN #或 SET AUTOCOMMIT0 #禁止自动提交 START TRANSACTION #2.执行SQL语句 SAVEPOINT identifier # 添加保存点 ROLLBACK TO identifier # #3.提交或回滚事务 ROLLBACK #事务回滚 COMMIT #事务确认隔离级别事务并发问题如何发生当多个事务同时操作同一个数据库的相同数据时,数据库数据可能发生损坏。对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.数据库提供的事务隔离级别Oracle 支持的 2 种事务隔离级别READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITEDMysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READInnoDB和Falcon存储引擎通过多版本并发控制MVCCMultiversion Concurrency Control机制解决了幻读问题。查看和设置数据库隔离级别每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 tx/_isolation, 表示当前的事务隔离级别 查看当前的隔离级别:#设置当前连接的会话级别 set session transaction isolation level Serializable; set tx_isolation 2; READ-UNCOMMITTED : 0; READ-COMMITTED : 1; REPEATABLE-READ : 2; SERIALIZABLE : 3; #设置整个系统会话级别 set global.tx_isolation 3; #查看当前连接会话级别 SELECT tx_isolation; #查看整个系统会话级别 SELECT global.tx_isolation;视图视图虚拟表从一个表或多个表中导出来的表作用和真实表一样包含一系列带有行和列的数据 视图中用户可以使用SELECT语句查询数据也可以使用INSERTUPDATEDELETE修改记录视图可以使用户操作方便并保障数据库系统安全优点简单化数据所见即所得安全性用户只能查询或修改他们所能见到得到的数据逻辑独立性可以屏蔽真实表结构变化带来的影响缺点性能相对较差简单的查询也会变得稍显复杂修改不方便特变是复杂的聚合视图基本无法修改视图的作用简化复杂的sql操作编写sql查询后重用便捷而无需关注查询细节对重构数据库提供逻辑独立保护数据仅部分显示数据保障机密数据安全让数据展示更加精简清晰创建视图CREATE VIEW 视图名 AS 查询语句;视图注意事项视图名与表名一样必须唯一创建视图的必须有足够权限权限由数据库管理员授予视图可以嵌套即可以从其他视图中检索数据创建新的视图不能在视图上建立索引也不能关联触发器视图可以和物理表共同使用对视图进行更新时实际更新的是创建视图的表有下列内容之一视图不能做增删改操作①select子句中包含多表联查②select子句中包含组函数③select语句中包含group by子句④select语句中包含order by子句⑤select语句中包含union 、union all等集合运算符⑥where子句中包含相关子查询⑦from子句中包含多个表⑧如果视图中有计算列则不能更新⑨如果基表中有某个具有非空约束的列未出现在视图定义中则不能做insert操作修改视图#方法一 CREATE OR REPLACE VIEW test_v7 AS SELECT ... #方法二 ALTER VIEW test_v7 AS SELECT ...删除视图DROP VIEW test_v1,test_v2,test_v3;查看视图结构#方法一 DESC test_v7; #方法二 SHOW CREATE VIEW test_v7;变量系统变量全局变量作用域针对于所有会话连接有效但不能跨重启查看所有全局变量 SHOW GLOBAL VARIABLES; 查看满足条件的部分系统变量 SHOW GLOBAL VARIABLES LIKE %char%; 查看指定的系统变量的值 SELECT global.autocommit; 为某个系统变量赋值 SET global.autocommit0; SET GLOBAL autocommit0;会话变量作用域针对于当前会话连接有效查看所有会话变量 SHOW SESSION VARIABLES; 查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE %char%; 查看指定的会话变量的值 SELECT autocommit; SELECT session.tx_isolation; 为某个会话变量赋值 SET session.tx_isolationread-uncommitted; SET SESSION tx_isolationread-committed;自定义变量用户变量声明并初始化SET 变量名值; SET 变量名:值; SELECT 变量名:值;赋值方式一一般用于赋简单的值 SET 变量名值; SET 变量名:值; SELECT 变量名:值;方式二一般用于赋表 中的字段值 SELECT 字段名或表达式 INTO 变量 FROM 表;使用select 变量名;局部变量声明declare 变量名 类型 【default 值】; 注此声明只能放到begin end中进行使用且只能定义在第一行可以连续定义多个变量进行使用。赋值方式一一般用于赋简单的值 SET 变量名值; SET 变量名:值; SELECT 变量名:值;方式二一般用于赋表 中的字段值 SELECT 字段名或表达式 INTO 变量FROM 表;使用SELECT 变量名存储过程(PROCEDURE)存储过程(Procedure),是数据库操作语言SQL的可编程性实现传统的SQL语句通常是在数据库服务器中执行一条命令命令在数据库引擎内部需要经过创建与分析的过程因此在运行时间上开销较大而存储过程是预编译的在首次运行一个存储过程时查询优化器对其进行分析、优化并给出最终被存在系统表中的存储计划这样后期的使用只需通过命令调用即可因此在执行过程时便可节省此开销。存储过程优点提高性能降低网络开销分担应用程序需要执行的业务逻辑(如:订单生成通用分页等)更强的安全性比sql语句更为强大的处理能力存储过程缺点可移植性差(不同数据库服务器存储过程语法差异较大一旦变更DBMS则存储过程需要整体重构)逻辑复杂不如sql灵活实际应用相对较少存储过程创建语法在命令行中编写存储过程时可通过DELIMITER修改默认结束标记(推荐图形界面工具)DELIMITER $ (使用“$”符号作为结束标记符号可自定义)创建存储过程CREATE PROCEDURE 过程名(in|out|inout 参数名 参数类型,...) BEGIN [DECLARE] 临时变量 -- 执行体 END 参数前面的符号的意思 in:该参数只能作为输入 该参数不能做返回值 out该参数只能作为输出该参数只能做返回值 inout既能做输入又能做输出调用存储过程CALL 存储过程名();删除存储过程DROP PROCEDURE 存储过程名;函数function函数有着跟存储过程类似的实现原理但是两者之间也存在本质区别具体如下一般来说存储过程实现的功能要复杂一点而函数的实现的功能针对性比较强。存储过程功能强大可以执行包括修改表等一系列数据库操作用户定义函数不能用于执行一组修改全局数据库状态的操作。对于存储过程来说可以返回参数如记录集而函数只能返回值或者表对象。函数只能返回一个变量而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型而函数只能有IN类存储过程声明时不需要返回类型而函数声明时需要描述返回类型且函数体中必须包含一个有效的RETURNS语句。存储过程可以使用非确定函数不允许在用户定义函数主体中内置非确定函数。存储过程一般是作为一个独立的部分来执行 EXECUTE 语句执行而函数可以作为查询语句的一个部分来调用SELECT调用由于函数可以返回一个表对象因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程而可以使用函数。MySQL函数分为以下两类:预定义函数自定义函数其中预定义函数为mysql内置函数主要包含以下:字符串函数数值函数日期函数系统信息函数加密函数格式化函数创建函数CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型 BEGIN 函数体 END int add(int a, int b){ return ab; }函数的调用SELECT 函数名实参列表删除函数DROP FUNCTION 函数名;分支if函数语法if(条件值1值2) 特点可以用在任何位置case语句语法情况一类似于switch case 表达式 when 值1 then 结果1或语句1(如果是语句需要加分号) when 值2 then 结果2或语句2(如果是语句需要加分号) ... else 结果n或语句n(如果是语句需要加分号) end [case]如果是放在begin end中需要加上case如果放在select后面不需要 情况二类似于多重if case when 条件1 then 结果1或语句1(如果是语句需要加分号) when 条件2 then 结果2或语句2(如果是语句需要加分号) ... else 结果n或语句n(如果是语句需要加分号) end 【case】如果是放在begin end中需要加上case如果放在select后面不需要特点 可以用在任何位置if elseif语句语法if 情况1 then 语句1; elseif 情况2 then 语句2; ... else 语句n; end if;特点 只能用在begin end中三者比较 应用场合 if函数 简单双分支 case结构 等值判断 的多分支 if结构 区间判断 的多分支循环语法Label:while 循环条件 do 循环内容 END while label; Label:repeat 循环内容 Until 结束条件 END repeat label; Label:loop 循环内容 END loop labeel;特点iterate跳过当次循环 leave跳出循环触发器触发器TRIGGER是MySQL的数据库对象之一从5.0.2版本开始支持。该对象与编程语言中的存储过程以及函数非常类似都是SQL语句可编程性的实现并且都需要编写、编译以及调用。但是触发器的执行不是由程序调用也不是由手动启动而是由事件来触发、激活以及执行。那么为什么要使用数据库对象触发器呢在具体开发项目时经常会遇到如下实例1. 在删除部门表中记录时员工表因为设置外键约束关联部门表导致部门表中记录无法删除如何在执行部门删除时同时将关联的员工表中部门号列设置为NULL。2. 在对员工表中员工数据删除时如何自动将被删除的员工信息记录到备份表中。上面的例子使用触发器完成时具有这样的特点需要在表发生改变时自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作对SELECT语句则不会激活触发器。触发器语法触发器创建语法CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_body END;trigger/_name触发器的名称不能与已经存在的触发器重复trigger/_time{ BEFORE | AFTER }表示在事件之前或之后触发trigger/_event{ INSERT |UPDATE | DELETE }触发该触发器的具体事件tbl/_name该触发器作用在tbl/_name上删除触发器:DROP TRIGGER 触发器名;CREATE TRIGGER #创建触发器 myt1 #出发器的名字 BEFORE DELETE #什么时候触发 ON teacher #触发事件的表 FOR EACH ROW BEGIN UPDATE student SET t_id null WHERE t_id OLD.id; END DROP TRIGGER myt1; DELETE FROM teacher WHERE id 1; NEW和OLDNEW在触发器为INSERT/UPDATE事件类型时有效表示当前正在插入的数据同理OLD在触发器类型为DELETE/UPDATE事件类型时有效表示当前正在删除的数据。具体如下表:INSERTUPDATEDELETENEW可用可用无效OLD无效可用可用《网络安全从零到精通全套学习大礼包》96节从入门到精通的全套视频教程免费领取如果你也想通过学网络安全技术去帮助就业和转行我可以把我自己亲自录制的96节 从零基础到精通的视频教程以及配套学习资料无偿分享给你。网络安全学习路线图想要学习 网络安全作为新手一定要先按照路线图学习方向不对努力白费。对于从来没有接触过网络安全的同学我帮大家准备了从零基础到精通学习成长路线图以及学习规划。可以说是最科学最系统的学习路线大家跟着这个路线图学习准没错。配套实战项目/源码所有视频教程所涉及的实战项目和项目源码学习电子书籍学习网络安全必看的书籍和文章的PDF市面上网络安全书籍确实太多了这些是我精选出来的面试真题/经验以上资料如何领取(https://i-blog.csdnimg.cn/direct/88f12e9068e14b728b215f939a9ed28e.gif)网络安全学习路线图想要学习 网络安全作为新手一定要先按照路线图学习方向不对努力白费。对于从来没有接触过网络安全的同学我帮大家准备了从零基础到精通学习成长路线图以及学习规划。可以说是最科学最系统的学习路线大家跟着这个路线图学习准没错。配套实战项目/源码所有视频教程所涉及的实战项目和项目源码学习电子书籍学习网络安全必看的书籍和文章的PDF市面上网络安全书籍确实太多了这些是我精选出来的面试真题/经验以上资料如何领取
