与MySQL的零距离接触(三.函数、存储过程、引擎、图形化管理工具)

本文链接：https://blog.csdn.net/wtdask/article/details/79286023

金山竹影几千秋，云索高飞水自流，万里长江飘玉带，一轮银月滚金球，
远自湖北三千里，近到江南十六州，美景一时观不透，天缘有分画中游！
——祝大家小年快乐！2018福气生财！

一、运算符和函数

准备：

//连接数据库
 mysql -uroot -proot -P3306 -h127.0.0.1

//查看数据库
SHOW DATABASES;

//使用数据库
USE test;

//查看数据库字段属性
 DESC test;

//创建表
CREATE TABLE test1(
    -> first_name VARCHAR(20),
    -> last_name VARCHAR(20)
    -> );

//查看表属性
SHOW COLUMNS FROM test1;

这里写图片描述

//添加记录
INSERT test1(first_name,last_name) VALUES('A','B');
INSERT test1(first_name,last_name) VALUES('C','D');
INSERT test1(first_name,last_name) VALUES('Tom%','123');
INSERT test1(first_name,last_name) VALUES('',11);
INSERT test1(last_name) VALUES(11);

//删除记录
DELETE FROM test1 WHERE first_name='';

//查看表记录
SELECT * FROM test1;

这里写图片描述

1、字符函数

这里写图片描述

SELECT CONCAT('imooc','MySQL');

SELECT CONCAT(first_name,last_name) AS fullname FROM test1;

这里写图片描述

//指定分隔符进行连接
SELECT CONCAT_WS('|','A','B','C');

//显示
+----------------------------+
| CONCAT_WS('|','A','B','C') |
+----------------------------+
| A|B|C                      |
+----------------------------+

//数字格式化
SELECT FORMAT(12560.75,2);
//显示
+--------------------+
| FORMAT(12560.75,2) |
+--------------------+
| 12,560.75          |
+--------------------+

//转换大小写字符
SELECT LOWER('MySQL');
SELECT UPPER('MySQL');

//获取左侧字符，右侧同理
SELECT LEFT('MySQL',2);
+-----------------+
| LEFT('MySQL',2) |
+-----------------+
| My              |
+-----------------+

//函数嵌套
 SELECT LOWER(LEFT('MySQL',2));
+------------------------+
| LOWER(LEFT('MySQL',2)) |
+------------------------+
| my                     |
+------------------------+

这里写图片描述

SELECT LENGTH('My SQL');
+------------------+
| LENGTH('My SQL') |
+------------------+
|                6 |
+------------------+

 SELECT LTRIM('  MySQL   ');
+---------------------+
| LTRIM('  MySQL   ') |
+---------------------+
| MySQL               |
+---------------------+

//删除字符串前导的某个字符
SELECT TRIM(LEADING '?' FROM '??MySQL???');
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MySQL???') |
+-------------------------------------+
| MySQL???                            |
+-------------------------------------+
//后续
SELECT TRIM(TRAILING '?' FROM '??MySQL???');
//全删（但是中间部分有？则删不掉）
SELECT TRIM(BOTH '?' FROM '??MySQL???');

//字符替换
SELECT REPLACE('??My??SQL???','?','');
+--------------------------------+
| REPLACE('??My??SQL???','?','') |
+--------------------------------+
| MySQL                          |
+--------------------------------+

//字符串截取
SELECT SUBSTRING('MySQL',1,2);
+------------------------+
| SUBSTRING('MySQL',1,2) |
+------------------------+
| My                     |
+------------------------+

//模式匹配 %代表任意0个或多个字符
//获得的值1代表true
SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+

 SELECT * FROM test1;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A          | B         |
| C          | D         |
| Tom%       | 123       |
| NULL       | 11        |
+------------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE first_name LIKE '%o%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)

//查找带有%的字符，第二个%需转义
SELECT * FROM test1 WHERE first_name LIKE '%\%%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)

或者
SELECT * FROM test1 WHERE first_name LIKE '%1%%' ESCAPE '1';//代表1后面的%不需要解析

注意：%(百分号)：代表任意个字符
_（下划线）：代表任意一个字符

2、MySQL数值运算符和函数

这里写图片描述

3、MySQL 比较运算符和函数

这里写图片描述

 SELECT * FROM test1 WHERE first_name IS NULL;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| NULL       | 11        |
+------------+-----------+

4、MySQL日期时间函数

这里写图片描述

 SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);
+----------------------------------------+
| DATE_ADD('2014-3-12',INTERVAL 365 DAY) |
+----------------------------------------+
| 2015-03-12                             |
+----------------------------------------+

//计算两个时间的差值
SELECT DATEDIFF('2013-3-12','2014-3-12');
+-----------------------------------+
| DATEDIFF('2013-3-12','2014-3-12') |
+-----------------------------------+
|                              -365 |
+-----------------------------------+
1 row in set (0.02 sec)

SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');
+------------------------------------+
| DATE_FORMAT('2014-3-2','%m/%d/%Y') |
+------------------------------------+
| 03/02/2014                         |
+------------------------------------+

5、MySQL 信息函数

这里写图片描述

SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               3 |
+-----------------+

6、聚会函数

这里写图片描述

7、MySQL加密函数

这里写图片描述

//主要是用MD5进行加密，实际上是信息摘要
SELECT MD5('admin');
+----------------------------------+
| MD5('admin')                     |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+

//主要用于修改数据库密码
SELECT PASSWORD('admin');
+-------------------------------------------+
| PASSWORD('admin')                         |
+-------------------------------------------+
| *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-------------------------------------------+

二、自定义函数

这里写图片描述

1、MySQL自定义函数简介

这里写图片描述

2、MySQL 创建不带参数的自定义函数

CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
SELECT f1();

3、MySQL创建带有参数的自定义函数

 CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED
    -> RETURN (num1+num2)/2;

SELECT f2(10,15);

4、MySQL创建具有复合结构函数体的自定义函数

 CREATE FUNCTION adduser(username VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT test(username) VALUES(username);

这样报错

修改为下面的方式：

DELIMITER //;

CREATE FUNCTION adduser (username VARCHAR(30))

RETURNS int UNSIGNED

BEGIN

INSERT INTO test (username) VALUES (username);

RETURN LAST_INSERT_ID();

END 

//

这里写图片描述

三、MySQL存储过程

1、MySQL 存储过程简介

这里写图片描述

2、MySQL存储过程语法结构解析

这里写图片描述

3、MySQL创建不带参数的存储过程

CREATE PROCEDURE sp1() SELECT VERSION();

这里写图片描述

CALL sp1;
+-----------+
| VERSION() |
+-----------+
| 5.7.20    |
+-----------+

4、MySQL创建带有IN类型参数的存储过程

DESC users;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | tinyint(1)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

insert users(username,password,age,sex) values('A',md5('A'),20,0);

insert users(username,password,age,sex) values('B',md5('B'),23,1);

insert users(username,password,age,sex) values('C',md5('C'),23,1);

insert users(username,password,age,sex) values('D',md5('D'),24,1);

insert users(username,password,age,sex) values('E',md5('E'),24,0);

insert users(username,password,age,sex) values('F',md5('F'),23,0);

insert users(username,password,age,sex) values('G',md5('G'),22,0);

insert users(username,password,age,sex) values('H',md5('H'),23,0);

insert users(username,password,age,sex) values('I',md5('I'),23,0);

insert users(username,password,age,sex) values('J',md5('J'),22,1);

insert users(username,password,age,sex) values('K',md5('K'),22,1);

insert users(username,password,age,sex) values('L',md5('L'),22,0);

insert users(username,password,age,sex) values('M',md5('M'),24,1);

insert users(username,password,age,sex) values('N',md5('N'),21,0);

insert users(username,password,age,sex) values('O',md5('O'),20,0);

insert users(username,password,age,sex) values('P',md5('P'),20,1);

insert users(username,password,age,sex) values('Q',md5('Q'),24,1);

insert users(username,password,age,sex) values('R',md5('R'),24,1);

SELECT * FROM users;

这里写图片描述

封装删除存储过程

DELIMITER //

CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE id = id;
    -> END 
    -> //

DELIMITER ;

调用

 CALL removeUserById(3);

我们发现数据都被删除了，因为我们在封装存储过程时，使用的参数id和字段名id，同名，编译器并不能识别，所以要特别声明：一定不要把参数名设置成字段名

这里写图片描述

//删除上面封装的存储过程
DROP PROCEDURE removeUserById;

然后把数据重新加到表中，再重新构建存储过程

 DELIMITER //

 CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
 BEGIN
DELETE FROM users WHERE id = p_id;
 END
 //


DELIMITER ;

 CALL removeUserById(28);

这里写图片描述

5、MySQL创建带有IN和OUT类型参数的存储过程

 DELIMITER //
mysql> CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE id = p_id;
    -> SELECT count(id) FROM users INTO userNums;
    -> END
    -> //

DELIMITER ;

SELECT COUNT(id) FROM users;
+-----------+
| COUNT(id) |
+-----------+
|        17 |
+-----------+

CALL removeUserAndReturnUserNums(27,@nums);

SELECT @nums;
+-------+
| @nums |
+-------+
|    16 |
+-------+

1.用户变量：以”@”开始，形式为”@变量名”

用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效

2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名或者 set @@global.变量名

对所有客户端生效。只有具有super权限才可以设置全局变量

3.会话变量：只对连接的客户端有效。

4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量

declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

6、MySQL创建带有多个OUT类型参数的存储过程

创建根据年龄为条件进行删除数据的存储过程，返回2个值，分别是删除用户的数量和剩余用户的数量

ROW_COUNT();//返回操作插入，删除，更新被影响的数据的数量

DELIMITER //

 CREATE PROCEDURE removeUserByAgeReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
    -> BEGIN
    ->  DELETE FROM users WHERE age = p_age;
    -> SELECT ROW_COUNT() INTO deleteUsers;
    -> SELECT COUNT(id) FROM users INTO userCounts;
    -> END
    -> //

DELIMITER ;

CALL removeUserByAgeReturnInfos(23,@a,@b);

SELECT @a;
+------+
| @a   |
+------+
|    4 |
+------+

SELECT @b;
+------+
| @b   |
+------+
|    9 |
+------+

7、MySQL存储过程与自定义函数的区别

这里写图片描述

四、MySQL存储引擎

1、MySQL存储引擎简介

SHOW CREATE TABLE test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

这里写图片描述

2、MySQL相关知识点之并发处理

这里写图片描述

并发控制

当多个连接对记录进行修改时保证数据的一致性和完整性。系统使用锁系统来解决这个并发控制，这种锁分为：

共享锁（读锁）：在同一时间内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。

排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

锁的力度（锁的颗粒：锁定时的单位）：

表锁，是一种开销最小的锁策略。得到数据表的写锁（禁止其他用户进行读写，只能有一个锁）

行锁，是一种开销最大的锁策略。并行性最大（有多少条记录，就有可能对每条记录都进行锁）

表锁的开销最小，因为使用锁的个数最小，行锁的开销最大，因为可能使用锁的个数比较多

3、MySQL相关知识之事物处理

这里写图片描述

4、MySQL相关知识点之外键和索引

这里写图片描述

5、MySQL各个存储引擎特点

这里写图片描述

6、MySQL设置存储引擎

这里写图片描述

CREATE TABLE tp1(
s1 VARCHAR(10)
) ENGINE =MyISAM;

这里写图片描述

ALTER TABLE tp1 ENGINE = InnoDB;

这里写图片描述

五、MySQL图形化管理工具

这里写图片描述

MySQL图形管理工具之Navicat for MySQL

这里写图片描述

//新建数据表

这里写图片描述

//查询

这里写图片描述