MySql基础
数据结构模型
数据结构模型主要有：
* 层次模型
* 网状结构
* 关系模型
关系模型：
二维关系：row，column
数据库管理系统：DBMS
关系：Relational，RDBMS
RDBMS专业名词
常见的关系型数据库管理系统：
* MySQL：MySQL，MariaDB，Percona-Server
* PostgreSQL：简称为pgsql
* Oracle
* MSSQL
SQL：Structure Query Language，结构化查询语言
约束：constraint，向数据表提供的数据要遵守的限制
* 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
一个表只能存在一个
* 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
一个表可以存在多个
* 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
* 检查性约束
索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储
关系型数据库的常见组件
关系型数据库的常见组件有：
* 数据库：database
* 表：table，由行（row）和列（column）组成
* 索引：index
* 视图：view
* 用户：user
* 权限：privilege
* 存储过程：procedure
* 存储函数：function
* 触发器：trigger
* 事件调度器：event scheduler
SQL语句
SQL语句有三种类型：
* DDL：Data Defination Language，数据定义语言
* DML：Data Manipulation Language，数据操纵语言
* DCL：Data Control Language，数据控制语言
SQL语句类型和对应的操作
DDL-----------------------CREATE：创建；DROP：删除A；LTER：修改；
DML-----------------------INSERT：向表中插入数据；DELETE：删除表中数据；UPDATE：更新表中数据；SELECT：查询表中数据；
DCL-----------------------GRANT：授权；REVOKE：移除授权；
mariadb安装与配置
安装，设置开机启动查看状态端口号，并且安全初始化数据库（使用：mysql_secure_installation）
~~~
[root@chouyu ~]# yum -y install mariadb* -q
[root@chouyu ~]# echo $?
0
[root@chouyu ~]#
~~~
~~~
[root@chouyu ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@chouyu ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: dis>
Active: active (running) since Tue 2020-12-22 02:39:17 EST; 17s ago
~~~
~~~
[root@chouyu ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 0.0.0.0:5355 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:5355 [::]:*
LISTEN 0 128 [::]:22 [::]:*
[root@chouyu ~]#
~~~
~~~
[root@chouyu ~]# mysql_secure_installation
登录到你的MySql
~~~
[root@chouyu ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 15
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]>
~~~
这里用刚刚我们初始化的时候输入的密码登录就可以了我重置设置的密码是123456
你可以尝试修改密码例如
~~~
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'chouyuxixi' ;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> quit
Bye
~~~
查看数据库的方式
~~~
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
~~~
~~~
[root@chouyu ~]# mysql -uroot -pchouyuxixi -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@chouyu ~]#
~~~
DLL操作
数据库操作
创建数据库
~~~
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS shcool;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shcool |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]>
~~~
删除数据库
~~~
MariaDB [(none)]> DROP DATABASE IF EXISTS shcool;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]>
~~~
表操作
~~~
MariaDB [(none)]> CREATE DATABASE shcool;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use shcool
Database changed
~~~
创建进入shcool数据库
添加表school，查看表，查看表属性
~~~
MariaDB [shcool]> CREATE TABLE shcool (id int NOT NULL,name VARCHAR(10) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.002 sec)
MariaDB [shcool]> SHOW TABLES;
+------------------+
| Tables_in_shcool |
+------------------+
| shcool |
+------------------+
1 row in set (0.000 sec)
MariaDB [shcool]>
~~~
~~~
MariaDB [shcool]> desc shcool
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
MariaDB [shcool]>
~~~
修改表，插入班级class再删除ID。然后删除表
~~~
MariaDB [shcool]> alter table shcool add class varchar(10);
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [shcool]> desc shcool;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| class | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
MariaDB [shcool]> alter table shcool drop id;
Query OK, 0 rows affected (0.004 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [shcool]> desc shcool;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| class | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
MariaDB [shcool]> drop table shcool;
Query OK, 0 rows affected (0.003 sec)
MariaDB [shcool]> show tables;
Empty set (0.000 sec)
MariaDB [shcool]>
~~~
获取帮助文档
~~~
MariaDB [(none)]> HELP CREATE TABLE;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
.....
~~~
DCL操作
-创建授权grant
-权限类型(priv_type)
* ALL-----------所有权限
* SELECT-----读取内容的权限
* INSERT------插入内容的权限
* UPDATE-----更新内容的权限
* DELETE-----删除内容的权限
指定要操作的对象db_name.table_name
* *.*-------------------------------------所有的库所有的表
* db_name----------------------------指定的库所有的表
* db_name.table_name------------指定的库指定的表
授权chouyu用户在数据库本机上登录访问所有数据库
~~~
（授权用户chouyu在数据库本机上登录访问素有数据库）
MariaDB [(none)]> GRANT ALL ON *.* TO 'chouyu'@'localhost' IDENTIFIED BY 'chouyuxixi';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> GRANT ALL ON *.* TO 'chouyu'@'192.168.43.66' IDENTIFIED BY 'chouyuxixi';
Query OK, 0 rows affected (0.000 sec)
（授权用户chouyu在192.168.43.66上远程登录school数据库）
MariaDB [(none)]> GRANT ALL ON shcool.* TO 'chouyu'@'192.168.43.66' IDENTIFIED BY 'chouyuxixi';
Query OK, 0 rows affected (0.000 sec)
（授权用户chouyu在所有位置上远程登录school数据库）
MariaDB [(none)]> GRANT ALL ON *.* TO 'chouyu'@'%' IDENTIFIED BY 'chouyuxixi';
Query OK, 0 rows affected (0.001 sec)
~~~
登录测试
查看授权
查看当前登录用户的授权信息
~~~
MariaDB [(none)]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
~~~
查看指定用户chouyu的授权信息
~~~
MariaDB [(none)]> SHOW GRANTS for chouyu;
+----------------------------------------------------------------------------------------------------------------+
| Grants for chouyu@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'chouyu'@'%' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
~~~
~~~
MariaDB [(none)]> show grants for 'chouyu'@'localhost'
-> ;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for chouyu@localhost |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'chouyu'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
~~~
~~~
MariaDB [(none)]> show grants for 'chouyu'@'192.168.43.66';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for chouyu@192.168.43.66 |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'chouyu'@'192.168.43.66' |
| GRANT ALL PRIVILEGES ON `shcool`.* TO 'chouyu'@'192.168.43.66' |
+----------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
MariaDB [(none)]>
~~~
取消授权REVOKE
语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
~~~
MariaDB [(none)]> REVOKE ALL ON *.* FROM 'chouyu'@'192.168.43.66';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]>
~~~
mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
* GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
* 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
~~~
MariaDB [(none)]> flush privileges;
~~~
DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
首先来看看增（INSERT）
~~~
MariaDB [(none)]> use shcool
Database changed
~~~
插入操作的语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
这里有一张学生表student，我现在给他插入数据
~~~
MariaDB [shcool]> desc shcool.student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
MariaDB [shcool]>
~~~
~~~
MariaDB [shcool]> insert into student (name,age) values ('xiaolv',18),('xiaohong',16),('zhangsan',17),('lisi',19);
Query OK, 4 rows affected (0.001 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [shcool]> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | xiaolv | 18 |
| 2 | xiaohong | 16 |
| 3 | zhangsan | 17 |
| 4 | lisi | 19 |
+----+----------+------+
4 rows in set (0.000 sec)
MariaDB [shcool]>
~~~
我往里面加了四个学生的名字和年龄，但是没设置ID，他自己冒出来的，因为创建表的时候设置了ID为主键和自动递增，所以只要你往里面写数据他就会自己递增写入，非常的方便好用
SELECT语句
字段column表示法
| 表示符 | 代表什么？ |
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1 当表名很长时用别名代替 |
条件判断语句WHERE
| 操作类型 | 常用操作符 |
| 操作符 | >，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空 |
| 条件逻辑操作 | AND OR NOT |
ORDER BY：排序，默认为升序（ASC）
| ORDER BY语句 | 意义 |
| ORDER BY ‘column_name' | 根据column_name进行升序排序 |
| ORDER BY 'column_name' DESC | 根据column_name进行降序排序 |
| ORDER BY ’column_name' LIMIT 2 | 根据column_name进行升序排序 并只取前2个结果 |
| ORDER BY ‘column_name' LIMIT 1,2 | 根据column_name进行升序排序 并且略过第1个结果取后面的2个结果 |
(这是查询student表以年龄age的方式排序，默认升序)
MariaDB [shcool]> select * from student order by age; +----+----------+------+ | id | name | age | +----+----------+------+ | 2 | xiaohong | 16 | | 3 | zhangsan | 17 | | 1 | xiaolv | 18 | | 4 | lisi | 19 | +----+----------+------+ 4 rows in set (0.000 sec)
(这是查询student表以年龄age的方式排序，降序排序)
MariaDB [shcool]> select * from student order by age DESC;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 4 | lisi | 19 |
| 1 | xiaolv | 18 |
| 3 | zhangsan | 17 |
| 2 | xiaohong | 16 |
+----+----------+------+
4 rows in set (0.000 sec)
(这是查询student表以年龄age的方式排序，默认升序，只显示排行前三)
MariaDB [shcool]> select * from student order by age limit 3;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | xiaohong | 16 |
| 3 | zhangsan | 17 |
| 1 | xiaolv | 18 |
+----+----------+------+
3 rows in set (0.000 sec)
(这是查询student表以年龄age的方式排序，默认升序，显示第一条然后显示两条)
MariaDB [shcool]> select * from student order by age limit 1,2;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 3 | zhangsan | 17 |
| 1 | xiaolv | 18 |
+----+----------+------+
2 rows in set (0.000 sec)
（这是查询年龄大于等于18的内容）
MariaDB [shcool]> select * from student where age >=18 ;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | xiaolv | 18 |
| 4 | lisi | 19 |
+----+--------+------+
2 rows in set (0.000 sec)
（这是查询年龄大于等于18 并且名字是xiaolv的内容）
MariaDB [shcool]> select * from student where age >=18 AND name='xiaolv';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | xiaolv | 18 |
+----+--------+------+
1 row in set (0.000 sec)
（这是查询年龄在16到19岁之间的内容）
MariaDB [shcool]> select * from student where age BETWEEN 16 and 19;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | xiaolv | 18 |
| 2 | xiaohong | 16 |
| 3 | zhangsan | 17 |
| 4 | lisi | 19 |
+----+----------+------+
4 rows in set (0.000 sec)
（这是查询年龄不为空值的内容）
MariaDB [shcool]> select * from student where age is not NULL;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | xiaolv | 18 |
| 2 | xiaohong | 16 |
| 3 | zhangsan | 17 |
| 4 | lisi | 19 |
+----+----------+------+
4 rows in set (0.000 sec)
（这是查询年龄为空的内容）
MariaDB [shcool]> select * from student where age is NULL;
Empty set (0.000 sec)
update语句
Update的语法为：
UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
（这条的意思是把修改名字为wangwu，对象是xiaolv）
MariaDB [shcool]> update student set name = 'wangwu' where name = 'xiaolv'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [shcool]> select * from student; +----+----------+------+ | id | name | age | +----+----------+------+ | 1 | wangwu | 18 | | 2 | xiaohong | 16 | | 3 | zhangsan | 17 | | 4 | lisi | 19 | +----+----------+------+ 4 rows in set (0.000 sec)
delete语句
delete的语法是：
DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [shcool]> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | wangwu | 18 |
| 2 | xiaohong | 16 |
| 3 | zhangsan | 17 |
| 4 | lisi | 19 |
+----+----------+------+
4 rows in set (0.000 sec)
（这条的意思就是删除表中名字叫lisi的内容）
MariaDB [shcool]> delete from student where name= 'lisi';
Query OK, 1 row affected (0.001 sec)
MariaDB [shcool]> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | wangwu | 18 |
| 2 | xiaohong | 16 |
| 3 | zhangsan | 17 |
+----+----------+------+
3 rows in set (0.000 sec)
（这条是直接删除整张表）
MariaDB [shcool]> delete from student ;
Query OK, 3 rows affected (0.001 sec)
MariaDB [shcool]> select * from student;
Empty set (0.000 sec)
truncate语句
truncate与delete的区别：
| 语句类型 | 特点 |
| delete | DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
| truncate | 删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
MariaDB [shcool]> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 5 | zhangsan | 18 |
| 6 | lisi | 16 |
| 7 | wangwu | 17 |
| 8 | chenliu | 19 |
+----+----------+------+
4 rows in set (0.000 sec)
MariaDB [shcool]> truncate student;
Query OK, 0 rows affected (0.006 sec)
MariaDB [shcool]> select * from student;
Empty set (0.000 sec)
MariaDB [shcool]> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
MariaDB [shcool]>
可见这种方式删除表的话会清空表的内容但是会保留表的格式。