mysql-基础-01
目录
1.关系型数据库
1.1常用的关系型数据库
- Msyql,MariaDB,Percona-Server
- PosrgreSQL
- Oracle
- MSSQL
1.2专业名词
-SQL：Structure Query Language，结构化查询语言
-约束：
主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。一个表只能存在一个
惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）一个表可以存在多个
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束
- 索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储
1.3关系型数据库常见组件
- database:数据库
- table:表，由行(row)和列(colum)组成
-index: 索引
-view:视图
-user:用户
-privilege:权限
-procedure:存储过程
-function:存储函数
-trigger:触发器
-event scheduler:事件调度器
1.4SQL语句
- DDL：数据定义语言
- DML：数据操纵语言
- DCL：数据控制语言
| SQL语句类型 | 对应操作 |
|---|---|
| DDL | CREATE：创建 DROP：删 ALTER：修改 |
| DML | INSERT：向表中插入数据 DELETE：删除表中数据 PDATE：更新表中数据 SELECT：查询表中数据 |
| DCL | GRANT：授权 REVOKE：移除授权 |
2. 数据库安装与配置
MariaDB
- 1.通过yum安装
[root@localhost ~]# yum -y install mariadb mariadb-server
- 2.启动MariaDB服务
##3306端口
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since 二 2020-10-20 22:46:36 CST; 6s ago
Process: 27817 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 27727 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 5 *:902 *:*
LISTEN 0 50 *:3306 *:*
- 3.查看版本
##此时说明安装成功
[root@localhost ~]# mysqladmin --version
mysqladmin Ver 9.0 Distrib 5.5.65-MariaDB, for Linux on x86_64
- 4.运行mysql
##刚开始默认root用户登陆，且密码为空
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-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)]>
##设置root用户密码
[root@localhost ~]# mysqladmin -u root password xxxxxx
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.65-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 [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
3.1 DDL操作
- CREATE：创建 DROP：删除 ALTER：修改
3.1.1用户操作
用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录
HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：IP地址或者是通配符（%和_）
-
%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
-
_：匹配任意单个字符
-
创建用户
CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
##创建wisan用户
MariaDB [(none)]> create user 'wisan'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> quit
Bye
##使用wisan用户登陆
[root@localhost ~]# mysql -uwisan -h127.0.0.1 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.65-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)]>
- 删除用户
DROP USER 'username'@'host';
MariaDB [(none)]> drop user 'wisan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uwisan -hlocalhost -p
Enter password:
ERROR 1045 (28000): Access denied for user 'wisan'@'localhost' (using password: YES)
3.1.2数据库操作
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
##root用户登陆
##其他用户没有给权限则操作不了
MariaDB [(none)]> create database if not exists wisan_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases>;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wisan_db |
+--------------------+
- 删除数据库
DROP DATABASE [IF EXISTS] 'DB_NAME';
MariaDB [(none)]> drop database if exists wisan_db;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
3.1.3表操作
- 创建表
CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
##先进入数据库
MariaDB [(none)]> use wisan_db;
Database changed
MariaDB [wisan_db]> create table student(id int not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.05sec)
MariaDB [wisan_db]> show tables from wisan_db;
+--------------------+
| Tables_in_wisan_db |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
MariaDB [wisan_db]> desc wisan_db.student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
- 删除表
DROP TABLE [ IF EXISTS ] 'table_name';
MariaDB [wisan_db]> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)
MariaDB [wisan_db]> show tables ;
Empty set (0.00 sec)
3.1.4查看show
- show character set
MariaDB [wisan_db]> show character set ;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
- show engines
MariaDB [wisan_db]> show engines ;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
- show databases
MariaDB [wisan_db]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wisan_db |
+--------------------+
5 rows in set (0.00 sec)
- show tables from db_name
MariaDB [wisan_db]> show tables from wisan_db;
+--------------------+
| Tables_in_wisan_db |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
- desc db_name.tb_name
MariaDB [wisan_db]> desc wisan_db.student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
- show create table db_name.tb_name
MariaDB [wisan_db]> show create table wisan_db.student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- show table status like 'tb_name'\G
MariaDB [wisan_db]> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2020-10-21 00:03:03
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
- 查询帮助
- HELP keyword;
MariaDB [wisan_db]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
........................................................
3.2 DCL操作
- GRANT：授权 REVOKE：移除授权
权限类型
- priv_type
WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户
| 类型 | 意义 |
|---|---|
| ALL | 所有权限 |
| SELECT | 读取内容的权限 |
| INSERT | 插入内容的权限 |
| UPDATE | 更新内容的权限 |
| DELETE | 删除内容的权限 |
操作对象
- 表: db_name.tb_name
- 存储 函数
- 存储 过程
| 对象 | 意义 |
|---|
- . * | 所有库的所有表
db_name.* | 指定库的所有表
db_name.tb_name | 指定库的指定表
3.2.1创建权限grant
GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
##授权wisan用户本地登陆数据库访问所有库的表
MariaDB [(none)]> grant all on *.* to 'wisan'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
##授权wisan用户在172.16.104.132远程登陆数据库访问wisan_db库的所有表
MariaDB [(none)]> grant all on wisan_db.* to 'wisan'@'172.16.104.132' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
##授权wisan用户在所有位置远程登陆数据库访问所有库的所有表
MariaDB [(none)]> grant all on *.* to 'wisan'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
3.2.2 查看权限
- 查看当前用户权限
- show grants
MariaDB [(none)]> show grants;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wisan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 查看指定用户的权限
- show grants for user
MariaDB [(none)]> show grants for 'wisan'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@localhost |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wisan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show grants for 'wisan'@'172.16.104.132';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@172.16.104.132 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wisan'@'172.16.104.132' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `wisan_db`.* TO 'wisan'@'172.16.104.132' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3.2.3 取消授权Revoke
REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
MariaDB [(none)]> show grants for 'wisan'@'172.16.104.132';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@172.16.104.132 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wisan'@'172.16.104.132' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `wisan_db`.* TO 'wisan'@'172.16.104.132' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> revoke all on wisan_db.* from 'wisan'@'172.16.104.132';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for 'wisan'@'172.16.104.132';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wisan@172.16.104.132 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wisan'@'172.16.104.132' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：
GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
3.3DML操作
- 增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的数据操作
3.3.1 INSERT
INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
##插入一条数据
MariaDB [(none)]> use wisan_db;
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 [wisan_db]> insert into student(id,name,age) value(1,'tom',20);
Query OK, 1 row affected (0.02 sec)
##插入多条数据
MariaDB [wisan_db]> insert into student(id,name,age) values(2,'tom',20),(3,'null',15),(4,'jerry',null),(5,'cat',NULL);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | null | 15 |
| 4 | jerry | NULL |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
3.3.2 UPDATE
UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | null | 15 |
| 4 | jerry | NULL |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
'
MariaDB [wisan_db]> update student set name = 'mouse' where name = 'null';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [wisan_db]> update student set age = 18 where id = 4;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | mouse | 15 |
| 4 | jerry | 18 |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
3.3.3 DELETE
DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | tom | 20 |
| 3 | mouse | 15 |
| 4 | jerry | 18 |
| 5 | cat | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
MariaDB [wisan_db]> delete from student where id = 1;
Query OK, 1 row affected (0.02 sec)
MariaDB [wisan_db]> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | tom | 20 |
| 3 | mouse | 15 |
| 4 | jerry | 18 |
| 5 | cat | NULL |
+----+-------+------+
4 rows in set (0.00 sec)
MariaDB [wisan_db]> delete from student;
Query OK, 4 rows affected (0.02 sec)
MariaDB [wisan_db]> select * from student;
Empty set (0.00 sec)
MariaDB [wisan_db]> desc wisan_db.student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
3.3.4 TRUNCATE
TRUNCATE table_name;
truncate删除表中所有数据，新添加的行计数值重置为初始值，且无法恢复（通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放）。表结构、约束和索引等保持不变。对于有外键约束引用的表不能使用它删除数据，也不能用于加入了索引视图的表。
delete删除表内容时仅删除内容且每次删除一行，但会保留表结构。并在事务日志中为所删除的每行记录一项，所以可以通过回滚事务日志恢复数据
3.3.5 SELECT
- select * 表示查询所有字段
- select column as alias 表示查询column并用alias替代
- 常用操作符号:< ，>，>= ，<= ，= ，!= ，BETWEEN * AND *
- LIKE（模糊匹配），RLIKE（正则表达式匹配），IS NOT NULL，IS NULL
- 逻辑操作符号: AND OR NOT
- 排序:ORDER BY COLUMN (默认ASC升序，DESC降序)，LIMIT [n],m(略过第n个)取第m个
SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
浙公网安备 33010602011771号