MySQL：高级部分

标签：set 0.00 MySQL 高级 rows sec mysql 部分 select

视图：

1.view视图创建、使用、作用

创建视图：create view /视图名/ as select /查询内容/ from /表名/;

查询视图：select /查询内容/ from /视图名/
视图操作和表基本一致

2.显示视图、

为了和表便于区分，视图尽量加前缀

查出所有的隐形表（视图）：show table status where comment='view'\G

3.更新和删除视图

操作和表的操作类似 alter, drop...

4.视图算法temptable , merge

如果把子查询用在视图里，结果会和查询结果不一样

创建视图时, 可以指定视图算法

temptable: 临时表算法(子查询可用) merge: 合并算法 undefined: 未定义

create algorithm=/指定的算法/ view /视图名/ as select /查询内容/ from /表名/;

事务

mysql存储引擎innodb提供事务处理

1.transaction

事务：要么一起执行，要么全部回滚（eg: 电商发货收货）

开启事务：start transaction;

更新事务：update /表名/ set /字段变化/ (/条件限定/)

回滚：rollback; (回滚全部操作)

提交事务：commit; (不提交不会产生变化)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |      0.87 |
|  2 |    586.57 |
|  3 | 888838.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> update wallet set balance=balance+50;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |     50.87 |
|  2 |    636.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update wallet set balance=balance-50 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update wallet set balance=balance+50 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |      0.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> update wallet set balance=balance-50;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

2. rollback to回滚点

虚拟机快照就类似于回滚点

建立事务保存点: savepoint /点的名字/

回滚到指定点: rollback to /点的名字/


mysql> insert into wallet values(4,1000);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint four;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values(5,199999);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint five;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to four;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wallet
    -> ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |      0.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
|  4 |   1000.00 |
+----+-----------+
4 rows in set (0.00 sec)

3.ACID

事务的4大特性：

原子性(atomicity)：不能再分了

一致性(consistency)：操作前与操作后的状态一致，事务不管做或者没做，数据库都处于一致状态

隔离性(isolation)：事务不能互相扰乱，保证数据和事务的相对隔离，一个事务的执行不能被其他事务干扰

持久性(durability)：事务commit了，事务就完成，数据是持久化储存的，存在磁盘里。

索引(index)

设置索引，查找快，但是其他操作(增删改等)效率会变低

1. 主键索引(Primary key)

2. 唯一索引(unique index)

3. 普通索引(index)：

4. 全文索引(fulltext)：搜索引擎使用。

设置索引：create /索引类型/ /索引名/ on /表名/(字段名);

对索引的操作和对键的操作类似

mysql> create index balance_index on wallet(balance);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index balance_index on wallet;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

存储过程

存储过程就相当于函数，通过调用执行。

1. delimiter

“delimiter /标志/……/标志/”: 以前每个’;‘(一条语句)是一个执行体，现在可以把多条语句用'delimiter /标志/'包起来,,用’/标志/‘结束，合并执行。标志是可以自定义的。

用完后，可以再还原，重新设置为’;‘: delimiter ;

mysql> delimiter ?/>
mysql> select * from t2;
    -> select * from student;
    -> ;
    -> ?/>
+--------+--------+
| score1 | score2 |
+--------+--------+
|     99 |     11 |
|     99 |     61 |
+--------+--------+
2 rows in set (0.00 sec)

+----+---------+--------+------+----------+
| id | name    | gender | age  | phone    |
+----+---------+--------+------+----------+
|  1 | Tom     | M      |   18 | 1555555  |
|  2 | BaZhang | W      |   20 | 16666666 |
|  3 | Jerry   | M      |   31 | 17777777 |
|  4 | LiZhang | W      |   12 | 19999999 |
+----+---------+--------+------+----------+
4 rows in set (0.00 sec)

mysql> delimiter ;
mysql> select * from student;
+----+---------+--------+------+----------+
| id | name    | gender | age  | phone    |
+----+---------+--------+------+----------+
|  1 | Tom     | M      |   18 | 1555555  |
|  2 | BaZhang | W      |   20 | 16666666 |
|  3 | Jerry   | M      |   31 | 17777777 |
|  4 | LiZhang | W      |   12 | 19999999 |
+----+---------+--------+------+----------+
4 rows in set (0.00 sec)

2. procedure 存储过程的用途

procedure：存储过程

创建存储过程：delimiter /结束符/

create procedure /存储过程名字(类似于函数名)/()

begin

/你想要执行的操作.../

end

/结束符/

(创建完后，不要忘了恢复结束符：demiliter ;)

执行存储过程：call /存储过程名字/();

删除存储过程：drop procedure /存储过程名字/

mysql> delimiter //
mysql> create procedure procc()
    -> begin
    -> update wallet set balance=balance+50;
    -> update t3 set name='Tom';
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call procc();
Query OK, 6 rows affected (0.01 sec)

mysql> drop procedure procc;
Query OK, 0 rows affected (0.01 sec)

mysql> call procc();
ERROR 1305 (42000): PROCEDURE nooryes_school.procc does not exist

结果：

（显示所有的存储过程： show procedure status \G）

会显示超多内容，包含mysql系统等等全部procedure。

.。。。。。。。

其他函数

只说几个

1. number

rand(); ：生成随机数

#生成一个随机数
mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6477823626695036 |
+--------------------+
1 row in set (0.00 sec)

#抽奖————从学生表里选两个人
mysql> select * from student order by rand() limit 2;
+----+---------+--------+------+----------+
| id | name    | gender | age  | phone    |
+----+---------+--------+------+----------+
|  2 | BaZhang | W      |   20 | 16666666 |
|  1 | Tom     | M      |   18 | 1555555  |
+----+---------+--------+------+----------+
2 rows in set (0.00 sec)

#随机排序
mysql> select * from student order by rand();
+----+---------+--------+------+----------+
| id | name    | gender | age  | phone    |
+----+---------+--------+------+----------+
|  3 | Jerry   | M      |   31 | 17777777 |
|  4 | LiZhang | W      |   12 | 19999999 |
|  1 | Tom     | M      |   18 | 1555555  |
|  2 | BaZhang | W      |   20 | 16666666 |
+----+---------+--------+------+----------+
4 rows in set (0.00 sec)

ceil(number);：向上取整

round(number);：四舍五入

floor(number);：向下取整

truncate(/number/,/保留位数/);保留小数多少位

mysql> select ceil(3.1);
+-----------+
| ceil(3.1) |
+-----------+
|         4 |
+-----------+
1 row in set (0.01 sec)

mysql> select round(3.1);
+------------+
| round(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

mysql> select truncate(3.1415926,2);
+-----------------------+
| truncate(3.1415926,2) |
+-----------------------+
|                  3.14 |
+-----------------------+
1 row in set (0.00 sec)

2.string

ucase(/字符串/);：转换成大写

lcase(/字符串/);：转换成小写

left(/字符串/,/截取长度/);：左截取

right(/字符串/,/截取长度/);：右截取

substring(/字符串/,/开始位置/,/截取长度/);：从指定位置截取

concat(/字符串/,/字符串/,/字符串/,......);：拼接字符串

mysql> select ucase('fuck!');
+----------------+
| ucase('fuck!') |
+----------------+
| FUCK!          |
+----------------+
1 row in set (0.01 sec)

mysql> select lcase('FUCK!');
+----------------+
| lcase('FUCK!') |
+----------------+
| fuck!          |
+----------------+
1 row in set (0.00 sec)

mysql> select left('FUCK!',2);
+-----------------+
| left('FUCK!',2) |
+-----------------+
| FU              |
+-----------------+
1 row in set (0.00 sec)

mysql> select right('FUCK!',2);
+------------------+
| right('FUCK!',2) |
+------------------+
| K!               |
+------------------+
1 row in set (0.01 sec)

mysql> select substring('FUCK!',2,3);
+------------------------+
| substring('FUCK!',2,3) |
+------------------------+
| UCK                    |
+------------------------+
1 row in set (0.00 sec)

mysql> select concat('FUCK!','you');
+-----------------------+
| concat('FUCK!','you') |
+-----------------------+
| FUCK!you              |
+-----------------------+
1 row in set (0.00 sec)

字符串操作应用于查询：

eg:拼接字段查询：select concat(/字段/,/'自定义连接符'/,/字段/,/'自定义连接符'/,/字段/……) from /表名/

mysql> select concat(name,'||',age,'??',name) from student;
+---------------------------------+
| concat(name,'||',age,'??',name) |
+---------------------------------+
| Tom||18??Tom                    |
| BaZhang||20??BaZhang            |
| Jerry||31??Jerry                |
| LiZhang||12??LiZhang            |
+---------------------------------+
4 rows in set (0.00 sec)

3. other

时间类的函数：

#获取当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-03-16 16:16:07 |
+---------------------+
1 row in set (0.00 sec)

#获取当前unix时间戳
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1710576979 |
+------------------+
1 row in set (0.00 sec)

#字段形式分别获取年、月、日
mysql> select year(now()) year, month(now())month, day(now())day;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2024 |     3 |   16 |
+------+-------+------+
1 row in set (0.01 sec)

#字段形式分别获取年、月、日
mysql> select year(now()) 年, month(now()) 月, day(now()) 日;
+------+------+------+
| 年   | 月   | 日   |
+------+------+------+
| 2024 |    3 |   16 |
+------+------+------+
1 row in set (0.01 sec)

加密函数：

eg:单向散列加密：md5(要加密的内容);

sha(要加密的内容);

(单向：不可逆，只能通过信息计算出散列值，无法通过散列值反算出信息。)

mysql> select sha('saddsa');
+------------------------------------------+
| sha('saddsa')                            |
+------------------------------------------+
| 37dd83780f1e3ce428efa819f48a4477ebaa25fb |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select sha(123343);
+------------------------------------------+
| sha(123343)                              |
+------------------------------------------+
| f95e329a232aab587d7df17211c10ad8c7715ecf |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select md5(123343);
+----------------------------------+
| md5(123343)                      |
+----------------------------------+
| 6e0a23b6ba9f545e9710e84d6abbbbb7 |
+----------------------------------+
1 row in set (0.00 sec)

标签：set,0.00,MySQL,高级,rows,sec,mysql,部分,select
From： https://blog.csdn.net/Bob516516/article/details/136723183

视图：