MySQL学习

一、基本概念

1、简介

数据库（database） - 保存有组织的数据的容器（通常是一个文件或一组文件）。
数据表（table） - 某种特定类型数据的结构化清单。
模式（schema） - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。
列（column） - 表中的一个字段。所有表都是由一个或多个列组成的。
行（row） - 表中的一个记录。
主键（primary key） - 一列（或一组列），其值能够唯一标识表中每一行。

2、安装

（1）方式一【MySQL-5.7.34版本】

[root@localhost ~]# wget https://repo.mysql.com/mysql57-community-release-el7.rpm
[root@localhost ~]# rpm -ivh mysql57-community-release-el7.rpm
[root@localhost ~]# yum -y install mysql-community-server
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2021-07-20 01:35:24 EDT; 4h 9min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 12099 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 12050 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 12103 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─12103 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 20 01:35:21 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 20 01:35:24 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# systemctl enable mysqld
[root@localhost ~]# cat /var/log/mysqld.log |grep password
2021-07-20T05:35:22.498884Z 1 [Note] A temporary password is generated for root@localhost: Wm,dkEe4uwmd
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database novel;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by '1qaz@WSX';
Query OK, 0 rows affected (0.00 sec)

mysql> create database novel;
Query OK, 1 row affected (0.00 sec)

mysql>

3、配置

二、SQL语句

1、DDL

DDL 的主要功能是定义数据库对象（如：数据库、数据表、视图、索引等）。运用create，drop，alter命令，以Mysql环境为例。

create database db_name;
create table table_name(column_name,column_name1,column_name2,...);

drop database db_name;
drop table table_name;

alter table table_name add    [column] column_name data_type;
alter table table_name drop   [column] column_name data_type;
alter table table_name modify [column] column_name new_data_type;
alter table table_name change [column] column_name new_column_name new_data_type;
alter table table_name rename new_table_name;

show create database [if not exists] db_name;
show create table [if not exists] table_name;

select database();
select * from table_name;

show databases;
show tables;

show columns from table_name;
desc table_name
use db_name;

（1）数据表

mysql> create database mydata;
Query OK, 1 row affected (0.00 sec)

mysql> use mydata;
Database changed
mysql> create table user (
    ->   id int(10) unsigned NOT NULL COMMENT 'Id',
    ->   username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
    ->   password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
    ->   email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
    -> ) COMMENT='用户表';
Query OK, 0 rows affected (0.01 sec)

1.创建数据表结构时进行约束

SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 create table 语句），或者在表创建之后规定（通过 alter table 语句）。

约束类型
NOT NULL              - 指示某列不能存储 NULL 值。
UNIQUE                  - 保证某列的每行必须有唯一的值。
PRIMARY KEY       - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY       - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK 　　 　　   - 保证列中的值符合指定的条件。
DEFAULT 　　　　 - 规定没有给列赋值时的默认值。

2.添加主键

mysql> alter table user add primary key (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.删除主键

mysql> alter table user drop primary key ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.查看数据表结构

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。
什么是元数据呢？
元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

mysql> use information_schema;
Database changed
mysql> select * from columns where table_name ='score';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT | GENERATION_EXPRESSION |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
| def           | mydata       | score      | studentno   |                1 | NULL           | NO          | char      |                       11 |                     11 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | char(11)    | PRI        |       | select,insert,update,references |                |                       |
| def           | mydata       | score      | courseno    |                2 | NULL           | NO          | char      |                        6 |                      6 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | char(6)     | PRI        |       | select,insert,update,references |                |                       |
| def           | xuexi        | score      | daily       |                1 | 0.0            | YES         | float     |                     NULL |                   NULL |                 3 |             1 |               NULL | NULL               | NULL              | float(3,1)  |            |       | select,insert,update,references |                |                       |
| def           | xuexi        | score      | final       |                2 | 0.0            | YES         | float     |                     NULL |                   NULL |                 3 |             1 |               NULL | NULL               | NULL              | float(3,1)  |            |       | select,insert,update,references |                |                       |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
4 rows in set (0.00 sec)

5、迁移数据表

mysql> show tables from xuexi;
+-----------------+
| Tables_in_xuexi |
+-----------------+
| goods           |
| score           |
+-----------------+
2 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| score            |
| thankss          |
+------------------+
2 rows in set (0.00 sec)

mysql> alter table xuexi.goods rename everyone;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| everyone         |
| score            |
| thankss          |
+------------------+
3 rows in set (0.00 sec)

mysql> show tables from xuexi;
+-----------------+
| Tables_in_xuexi |
+-----------------+
| score           |
+-----------------+
1 row in set (0.00 sec)

（3）视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化表。
视图包含行和列，就像真正的表一样。视图中的字段是一个或多个数据库中真实表中的字段。

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。
您可以添加 SQL 函数，在哪里添加，并将语句连接到视图，或者您可以呈现数据，就像数据来自单个表一样。

注释：视图总是显示最新数据！每当用户查询视图时，数据库引擎就使用视图的 SQL 语句重新构建数据。

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| everyone         |
| score            |
| thankss          |
+------------------+
3 rows in set (0.00 sec)

mysql> show tables from xuexi;
+-----------------+
| Tables_in_xuexi |
+-----------------+
| name            |
| score           |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from xuexi.name;
+-------+------+--------+
| name  | age  | gender |
+-------+------+--------+
| ryan  | 18   | female |
| hello | 44   | male   |
| gong  | 23   | male   |
+-------+------+--------+
3 rows in set (0.00 sec)

#创建视图
mysql> create view joejack as select * from xuexi.name;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| everyone         |
| joejack          |
| score            |
| thankss          |
+------------------+
4 rows in set (0.00 sec)

mysql> select * from joejack;
+-------+------+--------+
| name  | age  | gender |
+-------+------+--------+
| ryan  | 18   | female |
| hello | 44   | male   |
| gong  | 23   | male   |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql> create view joehello as select name,age from xuexi.name;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from joehello;
+-------+------+
| name  | age  |
+-------+------+
| ryan  | 18   |
| hello | 44   |
| gong  | 23   |
+-------+------+
3 rows in set (0.00 sec)
mysql> alter view joehello as select gender from xuexi.name;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from joehello;
+--------+
| gender |
+--------+
| female |
| male   |
| male   |
+--------+
3 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| everyone         |
| joehello         |
| joejack          |
| score            |
| thankss          |
+------------------+
5 rows in set (0.01 sec)

#删除视图
mysql> drop view joehello;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| everyone         |
| joejack          |
| score            |
| thankss          |
+------------------+
4 rows in set (0.00 sec)

（4）索引

通过索引可以更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速查询。

更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

唯一索引表明此索引的每一个索引值只对应唯一的数据记录。

mysql> use xuexi;
Database changed
mysql> select * from name;
+-------+------+--------+
| name  | age  | gender |
+-------+------+--------+
| ryan  | 18   | female |
| hello | 44   | male   |
| gong  | 23   | male   |
| shang | 65   | male   |
+-------+------+--------+
4 rows in set (0.00 sec)

#创建索引
mysql> create index firstt on name (age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#创建唯一索引
mysql> create unique index firstts on name (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除索引
mysql> alter table name drop index firstt;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、DML

insert into: insert into table_name(column_name1,column_name1,...) values('char',4,...)
             insert into table_name select * from table_name1            　　　　　　　　　　　#把table_name1表所有数据复写到table_name里
             
delete: delete from table_name where column_name=value;    
        delete from table_name;
        
update: update table_name set column_name1=value1,column_name2=value2 where column_name=value;

select: select column_name1,column_name2   from table_name     where column_name=value;
        select column_name into @variable_name from table_name1 where column_name=value;    #把table_name1表某个数据赋值给某变量
        select 1+1;                                                                         #进行运算符运算
        select verison();
        select @variable_name:=,@variable_name1:=value1;                    　　　　　　　　　　##对于SELECT只能使用:=来赋值，没有赋值的话就是默认null值。
        select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE'   　　　　　　　#存储过程
        select * from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' and name='xx'
        select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION'   select * from information_schema.views;   　
        select * from information_schema.tables;

3、DCL

权限控制
grant 和 revoke 可在几个层次上控制访问权限：
　　整个服务器，使用 grant all 和 revoke all；
　　整个数据库，使用 on database.*；
　　特定的表，使用 on database.table；
　　特定的列。
　　特定的存储过程。
　　新创建的账户没有任何权限。
　　账户用 username@host 的形式定义，username@% 使用的是默认主机名。
MySQL 的账户信息保存在 mysql 这个数据库中。
　　use mysql;
　　select user from user;

（1）创建账户

语法：create   user   [用户名]    [identified  by  '普通的字符串组合的密码'];

#在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。

语法：create    user    ['用户名'@'主机号']    [identified by '普通的字符串组合的密码'];

语法：grant  all  privileges  on  [数据库名].[数据表名]  to  '用户名'@'主机号'  identified  by  'password';

语法：grant  all  privileges  on  [数据库名].[数据表名]  to  '用户名'@'主机号'  identified  by  'password'  with  grant  option;

语法：insert into mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) values ('主机名', '用户名', PASSWORD('password'), '', '', '');

由于 mysql 数据库的 user 表中，ssl_cipher、x509_issuer 和 x509_subject 这 3 个字段没有默认值，所以向 user 表插入新记录时，一定要设置这 3 个字段的值，否则 INSERT 语句将不能执行。

mysql> insert into mysql.user (Host,User,authentication_string, ssl_cipher, x509_issuer, x509_subject) values ('192.168.43.109','liu','','','','');
Query OK, 1 row affected (0.00 sec)

（2）删除账户

drop  user  '用户名'@'主机号' ;

（3）修改用户

alter user 'user_name'@'host_name' rename 'user_name1'@'host_name1';

set password [for 'user_name'@'host_name'] = 'string';

set password [for 'user_name'@'host_name'] = password'string';

ALTERUSER[IFEXISTS]user[auth_option][,user[auth_option]]...[REQUIRE {NONE|tls_option[[AND]tls_option]...}][WITHresource_option[resource_option]...][password_option|lock_option]

（4）查看用户权限

show grants for 'user_name'@'host_name';

show privileges;

（5）授予权限

grant:  grant  select,insert on *.*  to   'user_name'@'host_name';

（6）删除权限

revoke: revoke select,insert on *.*  from 'user_name'@'host_name';

4、TCL

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。
MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当COMMIT或 OLLBACK语句执行后，事务会自动关闭，重新恢复隐式提交。
通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

指令
start transaction - 指令用于标记事务的起始点。
savepoint rollbackpoint_name - 指令用于创建保留点。
rollback to rollbackpoint_name - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到start transaction语句处。
commint - 提交事务。

mysql> select * from name;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| ryan | 18   | female |
| jack | 19   | male   |
| jim  | 20   | male   |
| tom  | 21   | male   |
+------+------+--------+
4 rows in set (0.00 sec)

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

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

mysql> update name set age = 33 where name='tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from name;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| ryan | 18   | female |
| jack | 19   | male   |
| jim  | 20   | male   |
| tom  | 33   | male   |
+------+------+--------+
4 rows in set (0.00 sec)

mysql> update name set name='sam' where name='tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from name;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| ryan | 18   | female |
| jack | 19   | male   |
| jim  | 20   | male   |
| sam  | 33   | male   |
+------+------+--------+
4 rows in set (0.00 sec)

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

mysql> select * from name;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| ryan | 18   | female |
| jack | 19   | male   |
| jim  | 20   | male   |
| tom  | 33   | male   |
+------+------+--------+
4 rows in set (0.00 sec)

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

mysql> select * from name;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| ryan | 18   | female |
| jack | 19   | male   |
| jim  | 20   | male   |
| tom  | 33   | male   |
+------+------+--------+
4 rows in set (0.00 sec)

三、应用场景

1、存储过程

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

创建存储过程：
命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
包含 in、out 和 inout 三种参数。
给变量赋值都需要用 select into 语句。
每次只能给一个变量赋值，不支持集合的操作。

#语法：create [definer = {user | current_user}] procedure procedure_name (procedure_parameter,...) [characteristic] routine_body

procedure_parameter: {in | out | inout} parameter_name type
characteristic: comment 'string' 
                | language sql 
                | [not] deterministic 
                | {contains sql | not sql | reads sql data |modifies sql data} 
                | sql security {definer | invoker}
routine_body:
            begin
            sql_statement
            ...
            sql_statement1
            end end_label

简单运用

mysql> delimiter ;;
mysql> create procedure first_procedure()
    -> begin
    -> select * from xuexi.name;
    -> end ;;
Query OK, 0 rows affected (0.00 sec)

mysql> call first_procedure;;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| ryan | 18   | female |
| jack | 19   | male   |
| jim  | 20   | male   |
| tom  | 33   | male   |
+------+------+--------+
4 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> drop procedure first_procedure;
Query OK, 0 rows affected (0.00 sec)

存储函数

#用例表：customer（cust_id,cust_sex,cust_name）;
create function fn_name(cid int)   
        returns char(2)      
        deterministic       
begin                   
       declare sex char(2);    
       select cust_sex into sex from customer   
             where cust_id = cid;   
       if sex is null then          
              return(select '没有该客户');
       else if sex = '女' then
              return(select "女");
           else return(select "男");
           end if;   
      end if;   
end $$   

详细运用

2、游标

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。
在存储过程中使用游标可以对一个结果集进行移动遍历。
游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。
使用游标的四个步骤：
　　声明游标，这个过程没有实际检索出数据；
　　打开游标；
　　取出数据；
　　关闭游标；

3、触发器