MYSQL数据库基础
目录
数据库基础
DBMS(数据库管理系统)
DBMS 是“数据库管理系统”的简称(全称 DataBase Management System)。实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库（DB） + 管理程序.
Oracle,MySQL都是数据库管理系统，即DBMS
常见DBMS
DBMS类型
- 关系型数据库
关系型这是最常见的经典的数据库模式。关系数据库管理系统（RDBMS），是基于集合理论的系统，实现方式是具有行和列的二维表
- 键值型数据库
KV将简单的键映射到（可能）更复杂的值，就像一个巨大的哈希表。由于它们相对简单，因此这种类型的数据库实现起来最灵活。哈希查找速度快，在Redis的例子中就是这样，速度是其主要的关注。哈希查找也容易分布化，所以Riak利用这一事实，侧重于简单管理的集群。
- 列型数据库
不同于前两个数据库，列型数据库按列存储类似的数据，而不是按行存储数据。列的添加很容易，版本控制是小菜一碟，并且对于空值没有存储成本。我们看到了HBase是对这一类型的经典实现
- 文档型数据库
文档数据库适合于涉及高度可变领域的问题。当你事先不知道你的数据看起来究竟像什么样子，文档型数据库是一个不错的选择
SQL语句
结构化查询语言（sql）
sql语句三种类型
- DDL：数据定义语言
- Create
- drop
- Alter等
- DML：数据操纵语言（增删改查等）
- update(改)
- insert(增)
- select(查)
- delete(删)
- DCL：数据控制语言（权限等）
- grant(授权)
- revoke(取消授权)
关系型数据库的常见组件
- 数据库：database
- 表table:（行，列）
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程: procedure
- 触发器: trigger
安装及初始化mariadb
[root@localhost ~]# yum install -y mariadb mariadb-common mariadb-devel meriadb-server
[root@localhost ~]# systemctl enable --now mariadb
[root@localhost ~]# mysql -uroot -p #root账户登录
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
......
设置密码
MariaDB [(none)]> set password = password('123456');
Query OK, 0 rows affected (0.000 sec) # password()是加密函数，可以让密码进行加密。
mysql服务的组成
- 客户端
- mysql：交互式客户端程序
- mysql_secure_installation: 安全初始化
- mysqldump：备份工具
- navicat 导航猫，图形化客户端工具
- workbench 跨平台图形化客户端工具
- 服务端
- mysqld/mariadb
mysql交互工具使用
- mysql
-
-u：指定用户名，默认是root
-
-p：指定密码
-
-V：显示版本号
-
-e：不登陆的情况下执行sql语句后退出
-
-h：指定服务器主机。默认localhost.
-
DDL操作
创建数据库
- CREATE DATABASE student;
- IF NOT EXISTS：如果存在就创建。不存在给予警告。不会ERROR。
MariaDB [(none)]> create database sawyer;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> create database if not exists sawyer;
Query OK, 0 rows affected, 1 warning (0.000 sec) #db_sawyer已经存在，但是加了ifnotexists后 并不会报错
查看数据库
- SHOW DATABASES;
删除数据库
- DROP DATABASE student;
- IF EXISTS：如果存在删除，如果不存在也不会ERROR。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sawyer |
| sy |
+--------------------+
5 rows in set (0.000 sec)
进入数据库
- USE databasename;
MariaDB [(none)]> use sawyer;
Database changed
MariaDB [sawyer]>
创建表
- CREATE TABLE tablename(columnname1 datastructure1(长度) not null,columname2 ......)；
create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4))
MariaDB [sawyer]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
数据库常见数据结构
| 数据结构名 | 举例 |
|---|---|
| 字符型数据 | char,varchar,text |
| 日期时间数据 | datatime,smalldatatime |
| 整数型数据 | int,tinyint(0-255) |
| 精确小数型数据 | decimal,numeric |
| 近似数值类型 | float,real |
| 位类型数据 | 常用于性别等 |
删除表
- DROP TABLE tablename;
MariaDB [sy]> drop table student; # 删除sy下的student这张表
Query OK, 0 rows affected (0.003 sec)
查看数据库内所有表清单
- SHOW TABLES;
MariaDB [sawyer]> show tables;
+------------------+
| Tables_in_sawyer |
+------------------+
| student |
+------------------+
1 row in set (0.000 sec)
查看表结构
- DESC tablename;
MariaDB [sawyer]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
注：\G代替；来更换查看方式
查看创建表的过程
- SHOW CREATE TABLE databasename.tablename;
MariaDB [sawyer]> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
查看表的状态
- SHOW TABLE STATUS LIKE 'tablename'\G
MariaDB [sawyer]> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2020-10-20 12:25:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.001 sec)
通配符
like 'table%' ：匹配table开头，其后任意长度字符
like 'table_':匹配table开头，其后一个长度字符
修改表的结构
- ALTER TABLE tablename add/drop/modify column 数据类型 选项;
MariaDB [sawyer]> alter table student modify age tinyint(5);
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [sawyer]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(5) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
用户的创建/删除
格式：username@客户端ip或%或_
表示只能从指定客户端Ip通过username远程登录到数据库服务器中。%表示允许所有主机。
例子：CREATE/DROP USER 'username@ip' [identified by '123456']
MariaDB [(none)]> create user 'test@192.168.197.133';
Query OK, 0 rows affected (0.000 sec)
##客户端登录
[root@localhost ~]# mysql -u'test@192.168.197.133' -h192.168.197.132
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
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)]>
DCL操作
授权grant
指定操作对象：
| 表示方式 | 意义 |
|---|---|
| *.* | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.tablename | 指定库的指定表 |
权限类型：
名称|含义|
ALL|所有的权限|
INSERT|新增的权限|
DELETE|删除内容的权限|
UPDATE|修改内容的权限|
SELECT|查看内容的权限|
GRANT ALL ON *.* TO 'test@localhost' identified by '123456';
#test账户默认查看不到数据库信息
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.001 sec)
## 客户端给test@192.168.197.133增加student表的查询权限
MariaDB [(none)]> grant select on sawyer.student to 'test@192.168.197.133';
Query OK, 0 rows affected (0.000 sec)
## 刷新权限
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
## test再次查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sawyer |
+--------------------+
2 rows in set (0.001 sec)
查看权限
show grants;(默认查看当前用户的权限)
show grants for tom@ip;(查看指定用户的权限)
## root登录，所以默认查看root的权限。
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.000 sec)
## 查看指定用户的权限
MariaDB [(none)]> show grants for 'test@192.168.197.133';
+------------------------------------------------------------------+
| Grants for test@192.168.197.133@% |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test@192.168.197.133'@'%' |
| GRANT SELECT ON `sawyer`.`student` TO 'test@192.168.197.133'@'%' |
+------------------------------------------------------------------+
2 rows in set (0.000 sec)
移除权限(revoke)
revoke select on databasename.tablename from username@ip;
MariaDB [(none)]> revoke select on sawyer.student from 'test@192.168.197.133';
Query OK, 0 rows affected (0.001 sec)
DML操作
insert(新增)
格式一：添加单行内容
insert tablename value(value1,value2,value3);
格式二：添加多行内容
insert tablename values(value1,value2,value3),(value1,value2,value3),......;
格式三: 添加部分字段的值，其余默认为null。
insert tablename(column1,column2) value(value1,value2);
delete(删除)
语法：
DELETE FROM tablename [WHERE column1=value];
update(修改)
语法：
UPDATE tablename SET column1=value [WHERE clause]
实例
1.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age）。
MariaDB [sawyer]> create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.006 sec)
## 查看
MariaDB [sawyer]> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)
2.往新建的student表中插入数据
MariaDB [sawyer]> insert student values(1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',null),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);
Query OK, 11 rows affected (0.001 sec)
Records: 11 Duplicates: 0 Warnings: 0
#查看效果
MariaDB [sawyer]> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.000 sec)
3.修改lisi的年龄为50
MariaDB [sawyer]> update student set age=50 where name='lisi';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#查看
MariaDB [sawyer]> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
4.查询student表中名字叫zhangshan的记录
MariaDB [sawyer]> select * from student where name='zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
+----+-----------+------+
2 rows in set (0.000 sec)
5.查询student表中名字叫zhangshan且年龄大于20岁的记录
MariaDB [sawyer]> select * from student where name='zhangshan' and age>20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.001 sec)
6.查询student表中年龄在23到30之间的记录
MariaDB [sawyer]> select * from student where age between 23 and 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.000 sec)
7.修改wangwu的年龄为100
MariaDB [sawyer]> update student set age=100 where name='wangwu';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查看
MariaDB [sawyer]> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.000 sec)
8.删除student中名字叫zhangshan且年龄小于等于20的记录
MariaDB [sawyer]> delete from student where name='zhangshan' and age<=20;
Query OK, 1 row affected (0.001 sec)
MariaDB [sawyer]> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.000 sec)
