理论+实验详解MySQL数据库管理

最新推荐文章于 2023-06-13 16:11:30 发布

Mr_ChenWJ

最新推荐文章于 2023-06-13 16:11:30 发布

阅读量640

点赞数

分类专栏：实验理论

本文链接：https://blog.csdn.net/Mr_ChenWJ/article/details/108512473

版权

实验同时被 2 个专栏收录

73 篇文章 0 订阅

订阅专栏

理论

54 篇文章 0 订阅

订阅专栏

一数据库基本操作

1.1 查看数据库结构

查看数据库信息

[root@localhost ~]# mysql -uroot -p    ##进入数据库
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

查看数据库中的表信息

mysql> use mysql;
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
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
......
31 rows in set (0.00 sec)

显示数据表的结构（字段）

mysql> describe user;    ##也可以使用“desc user”
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N

1.2 SQL语句概述

1.2.1 SQL语言

● Structured Query Language的缩写，即结构化查询语言
● 关系型数据库的标准语言
● 用于维护管理数据库（包括数据查询，数据更更新，访问控制，对象管理等功能）

1.2.2 SQL分类

● DDL：数据定义语言
● DML：数据操纵语言
● DQL：数据查询语言
● DCL：数据控制语言

1.3 创建数据库和表

● DDL语句可用于创建数据库对象，如库，表，索引等

创建数据库

mysql> create database auth;    ##创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

创建数据表

mysql> use auth;    ##进入auth数据库
Database changed
mysql> create table test1 (user_name CHAR(16)NOT NULL,user_passwd CHAR(48) DEFAULT '',PRIMARY KEY (user_name));    ##创建数据表
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;    ##查看创建的数据表
+----------------+
| Tables_in_auth |
+----------------+
| test1          |
+----------------+
mysql> desc test1;    ##查看创建表单内容
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name   | char(16) | NO   | PRI | NULL    |       |
| user_passwd | char(48) | YES  |     |         |       |
+-------------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)

1.4 管理表中的数据

● DML语句用于对表中的数据进行管理

插入新数据

mysql> use auth;
Database changed
mysql> insert into test1(user_name,user_passwd) values('zhangfei',password('123456'));    ##向数据表中插入数据记录
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from test1;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangfei  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into test1 values('liubei',password('123456'));    ##如果这个记录包含表中所有字段的值，则插入语句中的制定字段可以省略
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test1;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| liubei    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangfei  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

修改，更新原有数据表中的数据记录

mysql>  update auth.test1 set user_passwd=password('abc123')where user_name='zhangfei';    ##修改张飞的user_passwd数据
Query OK, 1 row affected, 1 warning (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> select * from test1;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| liubei    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangfei  | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

在数据表中删除指定的数据记录

mysql> delete from auth.test1 where user_name='zhangfei';    ##删除zhangfei这条数据记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| liubei    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

1.5 管理表中的数据

● DQL是数据查询语句，只有SELECT
● 用于从数据表中查找符合条件的数据记录

查询时可不指定条件

mysql> select * from auth.test1;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| liubei    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

查询时指定条件

mysql> insert into test1 values('zhangfei',password('123456'));    ##先创建zhangfei数据记录
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from auth.test1;    ##查看表中所有数据记录
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| liubei    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangfei  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user_name,user_passwd from auth.test1 where user_name='zhangfei';    ##只查看表中zhangfei的数据记录
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangfei  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select user_passwd from auth.test1 where user_name='zhangfei';    ##只查看zhangfei的user_passwd数据记录
+-------------------------------------------+
| user_passwd                               |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)

二数据库用户授权

DCL语句设置用户权限（用户不存在时，则新建用户）

mysql> grant select on auth.* to 'cwj'@'localhost' identified by '123456';    ##设置用户cwj在auth里只有查看的权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -u cwj -p
Enter password: 
mysql> use auth;
Database changed
mysql> create table test2 (user_name CHAR(16)NOT NULL,user_passwd CHAR(48) DEFAULT '',PRIMARY KEY (user_name));    ##创建表单，没有权限
ERROR 1142 (42000): CREATE command denied to user 'cwj'@'localhost' for table 'test2'

查看用户的权限

mysql> show grants for cwj@localhost;
+-----------------------------------------------+
| Grants for cwj@localhost                      |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'cwj'@'localhost'       |
| GRANT SELECT ON "auth".* TO 'cwj'@'localhost' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

撤销用户的权限

mysql> exit    ##此时cwj用户是没有权限使用撤销命令的
Bye
[root@localhost ~]# mysql -u root -p    ##用root账户使用撤销命令
mysql> revoke all on auth.* from 'cwj'@'localhost';    #撤销用户的权限
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for cwj@localhost;   ##再查看cwj用户的权限
+-----------------------------------------+
| Grants for cwj@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'cwj'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

三数据表高级操作

3.1 清空列表

mysql> use auth;
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
mysql> create table ceshi1 (user_name CHAR(16)NOT NULL,user_passwd CHAR(48) DEFAULT '',PRIMARY KEY (user_name));    ##创建表单结构
Query OK, 0 rows affected (0.01 sec)
mysql> insert into ceshi1 values('10',password('123456'));    ##写入多组数据
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into ceshi1 values('20',password('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into ceshi1 values('30',password('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into ceshi1 values('40',password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into ceshi1 values('50',password('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show tables; 
+----------------+
| Tables_in_auth |
+----------------+
| ceshi1         |
| test1          |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from ceshi1;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| 10        | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 20        | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 30        | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 40        | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 50        | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> delete from ceshi1;    ##清空列表
Query OK, 5 rows affected (0.00 sec)

mysql> select * from ceshi1;
Empty set (0.00 sec)

3.2 临时表

mysql> CREATE TEMPORARY TABLE `mytmp` (
    -> `id` int(10) NOT NULL AUTO_INCREMENT,
    -> `NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin
    -> NOT NULL,
    -> `level` int(10) NOT NULL,
    -> PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
'    ##创建临时表mytmp
mysql> insert into mytmp(name,level) values('aa',10);
Query OK, 1 row affected (0.00 sec)     ##插入数据
mysql> select * from auth.mytmp;     ##查看内容
+----+------+-------+
| id | NAME | level |
+----+------+-------+
|  1 | aa   |    10 |
+----+------+-------+
1 row in set (0.00 sec)
mysql> exit    '//退出数据库'
[root@localhost ~]# mysql -u root -p
Enter password: 
mysql> select * from auth.mytmp;      ##查看数据表，发现这个表不存在
ERROR 1146 (42S02): Table 'auth.mytmp' doesn't exist

3.3 克隆表

mysql> create table test3 like auth.test1;    ##克隆test1的表结构
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test3\G
*************************** 1. row ***************************
       Table: test3
Create Table: CREATE TABLE "test3" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test3;    ##只复制表结构，不复制数据
Empty set (0.00 sec)
mysql> insert into test3 select * from test1;    ##将test1表的数据写入test3表中
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from test3;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| liubei    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangfei  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)