MySQL数据库管理
一、SQL语句概述
SQL语言
- Structured Query Language的缩写，即结构化查询语言
- 关系型数据库的标准语言
- 用于维护管理数据库
- 包括数据查询，数据更新，访问控制，对象管理等功能
SQL分类
- DDL：数据定义语言
- DML：数据操纵语言
- DQL：数据查询语言
- DCL：数据控制语言
二、查看数据库结构
1、查看当前服务器中的数据库
# 查看数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28 |
+-----------+
1 row in set (0.00 sec)
# 列出所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2、查看当前数据库中有哪些表
# 进入数据库
mysql> use mysql
Database changed
# 列出当前库的所有表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
# …… //省略部分内容
| user |
+---------------------------+
31 rows in set (0.00 sec)
3、查看表结构
# 查询 kc65表结构
mysql> describe kgc.kc65;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(255) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
三、创建及删除数据库和表
1、创建数据库和表
- DDL语句可用于创建数据库对象，如库，表，索引等
- 使用DDL语句新建库、表
- 创建数据库
- CREATE DATABASE 数据库名;
- 创建数据表
- CREATE TABLE 表名(字段定义…);
- 创建数据库
# 创建 kgc 数据库
mysql> create database kgc;
Query OK, 1 row affected (0.01 sec)
# 切换到 kgc 库
mysql> use kgc
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
# 在 kgc 库中创建 kc65 表
mysql> create table kgc.kc65 (id int(11) not null,name VARCHAR(255) not null,age int(11) );
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_kgc |
+---------------+
| kc65 |
+---------------+
1 row in set (0.00 sec)
2、删除数据库和表
- 使用DDL语句删除库、表
- 删除指定的数据表
- DROP TABLE [数据库名.]表名
- 删除指定的数据库
- DROP DATABASE 数据库名
# 删除 kgc库 里面的 kc65 表
mysql> drop table kgc.kc65;
Query OK, 0 rows affected (0.01 sec)
# 查看表
mysql> show tables;
Empty set (0.00 sec)
# 删除 kgc库
mysql> drop database kgc;
Query OK, 0 rows affected (0.00 sec)
# 列出所有库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
四、管理表中的数据
- DML语句用于对表中的数据进行管理
- 包括的操作
- INSERT：插入新数据
- UPDATE：更新原有数据
- DELETE：删除不需要的数据
1、插入数据
- INSERT INTO 表名(字段 1, 字段 2,…) VALUES(字段 1 的值, 字段 2 的值,…)
# 插入一条数据
mysql> insert into kgc.kc65 (id,name) values (1,"张三");
Query OK, 1 row affected (0.00 sec)
mysql> insert into kgc.kc65 (id,name,age) values (2,"李四",20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into kgc.kc65 values (3,"王五",21);
Query OK, 1 row affected (0.00 sec)
2、查询数据
- SELECT 字段名 1,字段名 2,… FROM 表名 WHERE 条件表达式
# 查看 kc65表的所有内容
mysql> select * from kgc.kc65;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | NULL |
| 2 | 李四 | 20 |
| 3 | 王五 | 21 |
+----+--------+------+
4 rows in set (0.00 sec)
# 查询 user表中 host和 user信息
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
3、修改数据
- UPDATE 表名 SET 字段名 1=字段值 1[,字段名 2=字段值 2] WHERE 条件表达式
# 修改 kc65表 id为2的修改年龄为18
mysql> update kgc.kc65 set age=18 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 将数据库用户 root 的密码设为“123456”
mysql>UPDATE mysql.user SET authentication_string=PASSWORD('123456')
WHERE user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql>FLUSH PRIVILEGES; # 刷新用户授权信息
Query OK, 0 rows affected (0.01 sec)
# 将数据库用户root 的密码设置为“123456”
[root@bogon ~]# mysqladmin -u root -p'123457' password '123456
4、删除数据
- DELETE FROM 表名 WHERE 条件表达式
# 删除 kc65表中 age为20 的数据
mysql> delete from kgc.kc65 where age=20;
Query OK, 1 row affected (0.00 sec)
# 删除 kc65表中 name为王五 的数据
mysql> delete from kgc.kc65 where name="王五";
Query OK, 1 row affected (0.00 sec)
五、数据表的高级操作
1、清空表
- DELETE FROM tablename（删除表内数据）
- TRUNCATE TABLE tablename（清空表内记录）
- 两者的新者初始ID不同
mysql>truncate table tmp;
Query OK, 0 rows affected (0.01 sec)
2、临时表
- 临时建立的表，用于保存一些临时数据，不会长期存在
mysql>select * from mytmp; # 查看mytmp 表是否存在
ERROR 1146 (42S02): Table 'test.mytmp' doesn't exist
# 创建临时表
CREATE TEMPORARY TABLE kgc.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;
# 插入数据
insert into kgc.mytmp (name,level) values("张三",20);
# 退出当前连接
mysql> exit
Bye
[root@bogon ~]# mysql -u root -p
# 重新连接 MySQL 之后查看临时表状态
mysql> select * from kgc.mytmp;
ERROR 1146 (42S02): Table 'kgc.mytmp' doesn't exist
# 临时表创建成功之后，使用 SHOW TABLES 命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
# 如果在退出连接之前，也可以手动直接删除，使用 DROP TABLE 语句，具体操作如下所示。
mysql> drop table mytmp;
Query OK, 0 rows affected (0.00 sec)
3、克隆表
- LIKE方法
# 切换数据库
mysql> use kgc;
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_kgc |
+---------------+
| kc65 |
+---------------+
1 row in set (0.00 sec)
# 克隆表
mysql> create table kc6501 like kc65;
Query OK, 0 rows affected (0.03 sec)
# 列出所有表
mysql> show tables;
+---------------+
| Tables_in_kgc |
+---------------+
| kc65 |
| kc6501 |
+---------------+
2 rows in set (0.00 sec)
# 查看表内容
mysql> select * from kc6501;
Empty set (0.00 sec)
# 插入数据
mysql> insert into kc6501 select * from kc65;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 查看表内容
mysql> select * from kc6501;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 19 |
| 2 | 李四 | 18 |
+----+--------+------+
2 rows in set (0.00 sec)
# 克隆表
mysql> create table kc6502 as select * from kc65;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 列出所有表
mysql> show tables;
+---------------+
| Tables_in_kgc |
+---------------+
| kc65 |
| kc6501 |
| kc6502 |
+---------------+
3 rows in set (0.00 sec)
# 查看表内容
mysql> select * from kc6502;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 19 |
| 2 | 李四 | 18 |
+----+--------+------+
2 rows in set (0.00 sec)
六、数据库用户授权
1、授予权限
- DCL语句设置用户权限（用户不存在时，则新建用户）
- GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY ‘密码’ ]
# 授予权限
mysql> grant create on *.* to 'admin'@'localhost' identified by '200596';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
# 刷新生效
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
2、查看权限
mysql> show grants for 'admin'@'localhost';
+--------------------------------------------+
| Grants for admin@localhost |
+--------------------------------------------+
| GRANT CREATE ON *.* TO 'admin'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)
3、撤销权限
mysql> revoke CREATE ON *.* from 'admin'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
在MySQL中，当你执行show databases;命令时，你会看到几个默认的数据库。下面是每个数据库的含义：
- information_schema：
- 这是一个特殊的数据库，提供了关于MySQL服务器上的所有其他数据库的信息。你可以通过查询这个数据库来获取关于数据库、表、列以及权限等的信息。它是只读的，不能被修改。
- mysql：
- 这个数据库包含了MySQL服务器的用户账号和权限信息。例如，用户表user就存储在这个数据库中。它还包含了服务器需要的系统表，比如插件和时区信息。
- performance_schema：
- 这个数据库用于收集数据库服务器性能参数。它可以帮助你监视MySQL服务器的性能，比如查询的执行时间、锁的使用情况等。通过查询这个数据库，你可以获取到关于服务器性能的详细信息。
- sys：
- sys数据库是一个相对较新的特性，它提供了一系列视图和函数，用于帮助数据库管理员更方便地查询performance_schema和information_schema数据库中的信息。sys数据库的目的是简化性能监控和系统管理任务。
- 每个数据库都有其特定的用途，并且对于数据库的管理和优化都是非常重要的。
3957
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
