mysql基础
1.关系型数据库介绍
数据库管理系统
- DBMS
- RDBMS（关系型数据库管理系统）
- MySQL：MySQL，MariaDB（开源），Pernoca-Server
- PostgreSQL：pgsql
- Oracle（商业软件）
- MSSQL
1.1 数据结构模型
数据结构模型主要有：
- 层次模型
- 网状结构
- 关系模型（主要使用）
- 二维关系：row行（代表一个记录），column列（代表一个字段）
1.2 RDBMS专业名词
- SQL：Structure Query Language，结构化查询语言
- 约束：constraint，向数据表提供的数据要遵守的限制
- 主键约束:唯一且不为空（null）
- 唯一键约束：允许存在多个且可以为空
- 外键约束
- 检查性约束
- 索引：将表中一个或多个字段按一定顺序排列，类似目录功能
1.3 关系型数据库常见组件
- 数据库：database
- 表：table，由行（row）和列（column）组成
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler
1.4 SQL语句类型
- DDL：data definnation language
- DML：data manipulation language
- DCL：data control language
| sql语句类型 | 对应操作 |
|---|---|
| DDL | CREATE，DROP（删除），ALTER（修改） |
| DML | INSERT，DELETE，UPDATE，SELECT（查询） |
| DCL | GRANT（授权），REVOKE（移除授权） |
2.mysql安装与配置
- 安装
[root@localhost ~]yum -y install mariadb* //安装
[root@localhost ~]# systemctl enable --now mariadb
mariadb@ mariadb.service mariadb@.service
[root@localhost ~]# 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@localhost ~]# ss -antl //查看监听端口3306
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:22 0.0.0.0:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 128 *:443 *:*
- 设置密码
//进入mysql
[root@localhost ~]# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 11
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)]> ALTER USER root@localhost IDENTIFIED BY '123456'; //设置密码
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> quit //退出
Bye
//重新登录
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 22
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)]>
3 mysql数据库操作
3.1. DDL操作
3.1.1数据库操作
//创建数据库
[root@localhost ~]# mysql -uroot -p123456
MariaDB [(none)]> create if not exists database school; //创建数据库if not exists条件语句
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases; //查看当前数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.001 sec)
//删除school数据库
MariaDB [(none)]> DROP DATABASE if exists school;
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)]>
3.1.2 表操作
//创建数据库school（略）
//进入school数据库
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> create table student(id int NOT NULL,age tinyint NOT NULL,name VARCHAR(100) NOT NULL); //定义一个学生表
Query OK, 0 rows affected (0.003 sec)
MariaDB [school]> show tables; //查看已创建的表
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.000 sec)
MariaDB [school]> desc student;//查看当前表定义了那些数值
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| age | tinyint(4) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.001 sec)
//删除表
MariaDB [school]> drop table if exists student;
Query OK, 0 rows affected (0.003 sec)
MariaDB [school]> show tables;
Empty set (0.000 sec)
//创建student表（略）
//修改表定义的字段（这里增加一个“班级”变量）
MariaDB [school]> alter table student add class varchar(50);
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| age | tinyint(4) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| class | varchar(50) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
3.1.3查看帮助
MariaDB [school]> 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) }
...
3. mysql数据库操作
3.2 DML操作
DML操作包括对表的增（insert），删（delete）改（update），查（select）
3.2.1增加记录INSERT
- 增加记录//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
//启用数据库
MariaDB [(none)]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
//查看可用表
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.000 sec)
MariaDB [school]> insert into student values(1,24,'rua',2);
//增加一条记录
MariaDB [school]> select * from student; //查看记录
+----+-----+------+-------+
| id | age | name | class |
+----+-----+------+-------+
| 1 | 24 | rua | 2 |
+----+-----+------+-------+
1 row in set (0.000 sec)
//一条命令增加多个记录
MariaDB [school]> insert into student values(3,24,'wt',3),(4,18,'ywwy',1);
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
//查看
MariaDB [school]> select * from student;
+----+-----+------+-------+
| id | age | name | class |
+----+-----+------+-------+
| 1 | 24 | rua | 2 |
| 2 | 24 | quin | 3 |
| 3 | 24 | wt | 3 |
| 4 | 18 | ywwy | 1 |
+----+-----+------+-------+
4 rows in set (0.000 sec)
- 对于输入数据（如id）相同的情况
自动增长
MariaDB [school]> select * from student;
+----+-----+------+-------+
| id | age | name | class |
+----+-----+------+-------+
| 1 | 24 | rua | 2 |
| 2 | 24 | quin | 3 |
| 3 | 24 | wt | 3 |
| 4 | 18 | ywwy | 1 |
| 1 | 24 | rua | 2 |
+----+-----+------+-------+
5 rows in set (0.001 sec)
MariaDB [school]> select @t:=0;
+-------+
| @t:=0 |
+-------+
| 0 |
+-------+
1 row in set (0.000 sec)
MariaDB [school]> update student set id=(@t:=@t+1);
Query OK, 1 row affected (0.002 sec)
Rows matched: 5 Changed: 1 Warnings: 0
MariaDB [school]> alter table student add primary key(id);//设定id为一个主键
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> alter table student change id id int(11) not null auto_increment;
Query OK, 5 rows affected (0.014 sec)
Records: 5 Duplicates: 0 Warnings: 0
//查看定义的字段
MariaDB [school]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | tinyint(4) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| class | varchar(50) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
3.2.2查找记录（取值）SELECT
字段column表示法
| 表示符 | 代表什么 |
|---|---|
| * | 所有字段 |
| as | 字段别名，如 department AS 部门 |
条件判断语句WHERE
| 操作类型 | 常用操作符 |
|---|---|
| 操作符 | >,<,>=,<=,=,!=,BETWEEN a AND b,LIKE：%关键字模糊匹配，RLIKE正则表达式，IS NOT NULL,IS NULL |
| 条件逻辑操作 | AND,OR,NOT |
排序ORDER BY（默认升序）
| ORDER BY | 意义 |
|---|---|
| ORDER BY 字段名 | 根据字段名升序排序 |
- 字段名 DESC|根据字段名降序排序
- 字段名 LIMIT2|根据字段名升序排序且只取两个结果
- 字段名 LIMIT1，2|根据字段名升序排序且忽略第一个结果取后面2个结果
//查找teache表里所有的字段
MariaDB [school]> select * from teacher;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 1 | a | 20 | 5000 |
| 2 | bc | 24 | 7002 |
| 3 | b | 24 | 7002 |
| 4 | c | 36 | 2779 |
| 5 | bb | 16 | 2300 |
| 6 | ac | 25 | 4666 |
+----+------+------+--------+
6 rows in set (0.000 sec)
//别名取值（单字段取值）
MariaDB [school]> select name AS '名字' from teacher;
+--------+
| 名字 |
+--------+
| a |
| bc |
| b |
| c |
| bb |
| ac |
+--------+
6 rows in set (0.001 sec)
//取工资大于3k的字段
MariaDB [school]> select * from teacher where salary>3000;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 1 | a | 20 | 5000 |
| 2 | bc | 24 | 7002 |
| 3 | b | 24 | 7002 |
| 6 | ac | 25 | 4666 |
+----+------+------+--------+
4 rows in set (0.000 sec)
//按升序age排序取出全部字段
MariaDB [school]> select * from teacher order by age;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 5 | bb | 16 | 2300 |
| 1 | a | 20 | 5000 |
| 2 | bc | 24 | 7002 |
| 3 | b | 24 | 7002 |
| 6 | ac | 25 | 4666 |
| 4 | c | 36 | 2779 |
+----+------+------+--------+
6 rows in set (0.000 sec)
//取值满足工资在1k到3k之间的字段
MariaDB [school]> select * from teacher where salary between 1000 and 3000;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 4 | c | 36 | 2779 |
| 5 | bb | 16 | 2300 |
+----+------+------+--------+
2 rows in set (0.000 sec)
//取值age大于30或小于等于16的
MariaDB [school]> select * from teacher where age >30 or age <=16;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 4 | c | 36 | 2779 |
| 5 | bb | 16 | 2300 |
+----+------+------+--------+
2 rows in set (0.000 sec)
//取值工资为空的字段
MariaDB [school]> select * from teacher where salary is null;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 7 | cm | 40 | NULL |
+----+------+------+--------+
1 row in set (0.000 sec)
3.2.3更改记录UPDATE
MariaDB [school]> select * from teacher where id=2;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 2 | b | 24 | 7002 |
+----+------+------+--------+
6 rows in set (0.001 sec)
//更改id为2的字段的'name'为'bc'
MariaDB [school]> update teacher set name='bc' where id=2;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [school]> select * from teacher where id=2;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 2 | bc | 24 | 7002 |
+----+------+------+--------+
6 rows in set (0.000 sec)
3.2.4删除记录DELETE；TRUNCATE
- 二者区别
- delete 只删除表内容，保留表结构。每次删除日志都有记录。可找回，但占空间
- truncate 删除表内容且无法恢复，结构约束索引等保留
TRUNCATE table_name;
- truncate
MariaDB [school]> select * from student;
+----+-----+------+-------+
| id | age | name | class |
+----+-----+------+-------+
| 1 | 24 | rua | 2 |
| 2 | 24 | quin | 3 |
| 3 | 24 | wt | 3 |
| 4 | 18 | ywwy | 1 |
| 5 | 24 | rua | 2 |
+----+-----+------+-------+
5 rows in set (0.001 sec)
//删除表
MariaDB [school]> truncate student;
Query OK, 0 rows affected (0.016 sec)
MariaDB [school]> select * from student;
Empty set (0.000 sec)
MariaDB [school]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | tinyint(4) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| class | varchar(50) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.008 sec)
- delete
DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [school]> select * from teacher where salary is null;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 7 | cm | 40 | NULL |
+----+------+------+--------+
1 row in set (0.000 sec)
MariaDB [school]> delete from teacher where salary is null;
Query OK, 1 row affected (0.002 sec)
MariaDB [school]> select * from teacher where salary is null;
Empty set (0.001 sec)
3.3 DCL操作
远程授权需要关闭防火墙或添加规则放行ip
| 权限 | 代表什么 |
|---|---|
| ALL | 所有权限 |
| SELECT | 读取内容 |
| INSERT | 插入内容 |
| UPDATE | 更新内容 |
| DELETE | 删除内容 |
指定操作的对象
| 操作对象 | 意义 |
|---|---|
| *.* | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.table_name | 指定库的指定表 |
3.3.1 创建授权（grant）
远程授权需要关闭防火墙或添加规则放行ip
语法：GRANT priv_type（授权类型）,... ON [object_type] db_name.table_name TO ‘username'@'host（可为‘%’，表示所有主机）' [IDENTIFIED BY 'password'] [WITH GRANT OPTION]（将权限转给其他人，不推荐）;
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or
Your MariaDB connection id is 12
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab
Type 'help;' or 'h' for help. Type 'c' to clear the cur
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.94.1' identified by '123456'; //授权远程连接，允许root在.1的客户机上登录且拥有所有数据库和表的访问权限
Query OK, 0 rows affected (0.000 sec)
//授权用户eason对本机所有数据库表有授权
MariaDB [(none)]> grant all on *.* to 'eason'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;//刷新授权
MariaDB [(none)]> show grants; //查看授权
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
MariaDB [(none)]> show grants for 'root'@'192.168.94.1';
//查看特定ip的授权
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.94.1 |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.94.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
//查看特定用户授权
MariaDB [(none)]> show grants for 'eason'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for eason@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'eason'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
- 查看远程图形化数据库
3.3.2查看授权
show grants for 'username'@'hostname'；
- 对于授权主机名‘%’的，可以直接使用用户名查询
//授权所有主机的用户
MariaDB [(none)]> create user 'hei'@'%';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show grants for hei;
+---------------------------------+
| Grants for hei@% |
+---------------------------------+
| GRANT USAGE ON *.* TO 'hei'@'%' |
+---------------------------------+
1 row in set (0.000 sec)
//授权单个机的用户
MariaDB [(none)]> show grants for 'eason'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for eason@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'eason'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
3.3.2取消授权REVOKE
语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
MariaDB [(none)]> revoke all on *.* from 'root'@'localhost';//移除root在localhost上对数据库表的所有权限
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;//刷新
MariaDB [(none)]> show grants for 'root'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
//移除特定权限
MariaDB [(none)]> revoke select on *.* from root@192.168.94.1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show grants for 'root'@'192.168.94.1';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.94.1 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY ON *.* TO 'root'@'192.168.94.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中