这篇博客详细介绍了MySQL数据库管理，包括常用的数据类型、查看数据库的方法、SQL语句的使用，如创建、删除数据库和表，以及如何管理表中的数据记录。还涉及到修改表结构、创建临时表、外键约束和数据库用户管理，包括用户创建、权限授予和撤销。
文章目录
MySQL数据库管理
1、常用的数据类型
| 类型 | 说明 |
|---|---|
| int | 整型，用于定义整数类型的数据 |
| fload | 单精度浮点4字节32位，准确表示到小数点后六位 |
| double | 双精度浮点8字节64位 |
| char | 固定长度的字符类型，用于定义字符类型数据。 |
| varchar | 可变长度的字符类型 |
| text | 文本 |
| image | 图片 |
| decima(5,2) | 5个有效长度数字，小数点后面有2位。指定长度数组 |
char
char的长度是不可变。char如果存入数据的实际长度比指定长度要小 会补空格至指定长度 如果存入的数据的实际长度大于指定长度，低版本会被截取高版本会报错。
varchar
varchar长度是可变的，默认会加一个隐藏的结束符，因此结束符会多算一个字节。2、查看数据库
2、查看数据库
2.1查看当前服务器中的数据库
SHOW DATABASES; #大小写不区分，分号“;”表示结束
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| kxy |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
2.2查看数据库中包含的表
-
法一 ：在库中查看表
-
USE 数据库名;
-
SHOW TABLES;
-
法二 ：在库外查看表
-
show tables from mysql;
补充： 创建表时设置id自增。create table if not exists userInfo (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(50) NOT NULL,
password varchar(50) NOT NULL);
方法一：
mysql> use mysql; ###进入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; ###查看mysql数据库中包含的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
方法二：
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
2.3查看表的结构（字段）
方法一
USE 数据库名;
DESCRIBE 表名
方法二
DESCRIBE[数据库名.]表名；
#DESCRIBE可缩写成DESC
mysql> use mysql; #进入mysql库
Database changed
mysql> desc user; #查看mysql库中的user表的结构
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
mysql> desc mysql.user; #在mysql库外查看mysql库中的user表的表结构
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
3、SQL语句
SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。
3.1SQL语句分类：
| 语句 | 代表的意思 |
|---|---|
| DDL | 数据定义语言，用于创建数据库对象，如库、表、索引等（create ） |
| DML | 数据操纵语言，用于对表中的数据进行管理(insert drop delete update ) |
| DQL | 数据查询语言，用于从数据表中查找符合条件的数据记录(select ) |
| DCL | 数据控制语言，用于设置或者更改数据库用户或角色权限(grant ) |
3.2创建及删除数据库和表
3.2.1创建新的数据库create
CREATE DATABASE 数据库名;
mysql> create database bwl1998; #创建一个自定义的数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; #查看发现已经存在
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| bwl1998 |
| kxy |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
3.2.2创建新的表create
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,…][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
例如：
CREATE TABLE my (id int NOT NULL,name char(10) NOT NULL,score decimal (5,2),passwd char(48) DEFAULT ‘’,PRIMARY KEY (id));
mysql> use bwl1998; #进入库
Database changed
mysql> create table bwl (id int,name char(10) not null,age int,sex char(2),score deecimal (5,2));
#创建 名叫bwl的表，并插入字段
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; #查看库中的表
+-------------------+
| Tables_in_bwl1998 |
+-------------------+
| bwl |
+-------------------+
1 row in set (0.00 sec)
mysql> desc bwl; #查看表内容
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
3.2.3删除指定的数据表drop
- #如不用USE进入库中，则需加上数据库名
- DROP TABLE [数据库名.]表名;
- #在库中的话 直接DROP TABLE 表名;
3.2.4删除指定的数据库drop
DROP DATABASE 数据库名;
4、管理表中的数据记录
4.1向数据表中插入新的数据记录insert
- 方法一
- insert into 表名（字段1,字段2[,…]） values（字段1的值,字段2的值,…）；
- 方法二
- insert into 表名 values（字段1的值,字段2的值,…）；
例如：
#指定全部字段加入值
insert into my(id,name,age,sex,score) values(1,‘小明’,18,‘男’,30.5);
#不指定字段为表中字段插入所有值
insert into my values (3,‘小红’,17,‘女’,60);
#指定个别字段添加值
insert into my(name,age,sex,score) values(‘小强’,18,‘男’,31.5);
#如果此处不指定name那么会报错，因为name设为not null，name字段不能为空
方法一：
mysql> insert into bwl(id,name,age,sex,score) values(1,'康康',18,'女',59);
Query OK, 1 row affected (0.01 sec)
#插入一条记录 指定ID为1 name为康康 age为18 sex为女 score为59
mysql> insert into bwl(id,name,age,sex,score) values(2,'颖颖',20,'女',80);
Query OK, 1 row affected (0.00 sec)
#插入一条记录 指定ID为2 name为颖颖 age为20 sex为女 score为80
mysql> mysql> select * from bwl; #查看bwl表中的所有记录
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 1 | 康康 | 18 | 女 | 59.00 | #score为decimal(5,2)的类型小数点后最多两位，少于两位自动补全。多余两位四舍 五入到两位。整数部分三位报错。
| 2 | 颖颖 | 20 | 女 | 80.00 |
+------+--------+------+------+-------+
2 rows in set (0.00 sec)
方法二：
mysql> insert into bwl values (3,'雯雯',19,'女',60); #不指定字段 按照创建表时结构顺序插入值
Query OK, 1 row affected (0.00 sec)
mysql> select * from bwl;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 1 | 康康 | 18 | 女 | 59.00 |
| 2 | 颖颖 | 20 | 女 | 80.00 |
| 3 | 雯雯 | 19 | 女 | 60.00 |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)
4.2查询表中数据记录 select
#查看所有数据
select * from 表名; *代表所有字段
#查询指定数据
SELECT 字段名1,字段名2[,…] FROM 表名 [WHERE 条件表达式];
#以列表方式纵向显示
select * from 表名 \G;
#查询前两行记录
select * from 表名 limit 2;
#表示查看n行后的m行数据记录
select * from 表名 limit n，m; #查看（n+1 ,n+m）行记录
查询指定数据
mysql> select id,age,name from bwl; #查询bwl表中的id，age，那么字段
+------+------+--------+
| id | age | name |
+------+------+--------+ #此时表的排列按照查询顺序排列而不是表结构顺序
| 1 | 18 | 康康 |
| 2 | 20 | 颖颖 |
| 3 | 19 | 雯雯 |
+------+------+--------+
3 rows in set (0.00 sec)
根据条件查询指定数据
mysql> select id,age from bwl where id=2 or id=3;
+------+------+
| id | age | #查询bwl表中id为2或者3的记录中的id字段和age字段
+------+------+
| 2 | 20 |
| 3 | 19 |
+------+------+
2 rows in set (0.00 sec)
以列表方式纵向显示
mysql> select * from bwl\G
*************************** 1. row ***************************
id: 1
name: 康康
age: 18
sex: 女
score: 59.00
*************************** 2. row ***************************
id: 2
name: 颖颖
age: 20
sex: 女
score: 80.00
*************************** 3. row ***************************
id: 3
name: 雯雯
age: 19
sex: 女
score: 60.00
3 rows in set (0.00 sec)
查询前两行记录
mysql> select * from bwl limit 2;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 1 | 康康 | 18 | 女 | 59.00 |
| 2 | 颖颖 | 20 | 女 | 80.00 |
+------+--------+------+------+-------+
2 rows in set (0.00 sec)
查询3~5行记录
mysql> select * from bwl limit 2,3;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 3 | 雯雯 | 19 | 女 | 60.00 |
| 4 | 妍妍 | 21 | 女 | 99.00 |
| 4 | 玉儿 | 22 | 女 | 60.00 |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)
4.3修改、更新数据表中的数据记录update
使用update的时候一定要加where语句去匹配条件否则会修改表中所有记录
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
mysql> select * from bwl;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 1 | 康康 | 18 | 女 | 59.00 |
| 2 | 颖颖 | 20 | 女 | 80.00 |
| 3 | 雯雯 | 19 | 女 | 60.00 |
| 4 | 妍妍 | 21 | 女 | 99.00 |
| 4 | 玉儿 | 22 | 女 | 60.00 |
+------+--------+------+------+-------+
5 rows in set (0.00 sec)
mysql> update bwl set age=23 where id=2; #将bwl表中id为2的记录中的字段age改为23
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bwl;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 1 | 康康 | 18 | 女 | 59.00 |
| 2 | 颖颖 | 23 | 女 | 80.00 |
| 3 | 雯雯 | 19 | 女 | 60.00 |
| 4 | 妍妍 | 21 | 女 | 99.00 |
| 4 | 玉儿 | 22 | 女 | 60.00 |
+------+--------+------+------+-------+
5 rows in set (0.00 sec)
4.4在数据表中删除指定的数据记录delete
DELETE FROM 表名 [WHERE 条件表达式];
mysql> select * from bwl;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 1 | 康康 | 18 | 女 | 59.00 |
| 2 | 颖颖 | 23 | 女 | 80.00 |
| 3 | 雯雯 | 19 | 女 | 60.00 |
| 4 | 妍妍 | 21 | 女 | 99.00 |
| 4 | 玉儿 | 22 | 女 | 60.00 |
+------+--------+------+------+-------+
5 rows in set (0.00 sec)
mysql> delete from bwl where name='玉儿'; #删除bwl表中字段name为玉儿的记录
Query OK, 1 row affected (0.01 sec)
mysql> select * from bwl;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 1 | 康康 | 18 | 女 | 59.00 |
| 2 | 颖颖 | 23 | 女 | 80.00 | #删除成功
| 3 | 雯雯 | 19 | 女 | 60.00 |
| 4 | 妍妍 | 21 | 女 | 99.00 |
+------+--------+------+------+-------+
4 rows in set (0.00 sec)
5、修改表名和表结构alter
5.1修改表名
ALTER TABLE 旧表名 RENAME 新表名;
mysql> alter table bwl rename yyds; #将bwl表改名为yyds
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_bwl1998 |
+-------------------+
| yyds | #修改成功
+-------------------+
1 row in set (0.00 sec)
5.2扩展表结构（增加字段）
ALTER TABLE 表名 ADD 字段名 数据类型;
例如：
ALTER TABLE tieniu ADD address varchar(50) not null default ‘地址不详’;
#增加“address"字段，不允许为空值，默认值为“地址不详”。
#default '地址不详：表示此字段设置默认值地址不详；可与NOT NULL配合使用
mysql> alter table yyds add address varchar(50) not null default'地址不详';
Query OK, 0 rows affected (0.03 sec) #添加字段address不为控且默认为地址不详
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yyds;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
mysql> select * from yyds;
+------+--------+------+------+-------+--------------+
| id | name | age | sex | score | address |
+------+--------+------+------+-------+--------------+
| 1 | 康康 | 18 | 女 | 59.00 | 地址不详 |
| 2 | 颖颖 | 23 | 女 | 80.00 | 地址不详 |
| 3 | 雯雯 | 19 | 女 | 60.00 | 地址不详 |
| 4 | 妍妍 | 21 | 女 | 99.00 | 地址不详 |
+------+--------+------+------+-------+--------------+
4 rows in set (0.00 sec)
5.3修改字段（列）名，添加唯一键
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 [unique key];
mysql> desc yyds;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
mysql> alter table yyds change age phone int unique key;
Query OK, 0 rows affected (0.01 sec) #将yyds表中的age字段改为phone字段并设为唯一键
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yyds;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | UNI | NULL | | #可以发现已经添加了唯一键，age字段改为phone字段
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
5.4添加主键
#创建表时设置主键
create table 表名(字段1 XXX, 字段2 XXX, …primary key(字段));
create table 表名(字段1 XXX primary key, . . …); #将主键作为字段1的属性
#现有表中添加主键
ALTER TABLE 表名 add primary key(字段名);
mysql> desc yyds;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
mysql> alter table yyds add primary key(id); #设id为主键
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yyds;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
唯一键与主键区别
共同点：字段的值都是唯一性，不允许有重复的值
不同点：
- 一个表中只能有1个主键，但是可以有多个唯一键
- 主键字段中不允许有null值，唯一键是允许有null
5.5删除字段
ALTER TABLE 表名 drop 字段名;
mysql> desc yyds;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
mysql> alter table yyds drop address; #删除yyds表中的address字段
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc yyds;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | UNI | NULL | | #address字段被删除
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
MYSQL6种常见的约束
- 主键约束（primary key）
- 外键约束（foreign key）
- 非空约束（not null）
- 唯一性约束（unique）
- 默认值约束（default）
- 自增长约束（auto_increment）
6、数据表高级操作
6.1克隆表
方法一： 通过like复制
create table 新表名 like 旧表名;
insert into 新表名 select * from 旧表名; #将旧表数据导入新表
方法二：创建表的同时导入数据
create table 新表名 (select * from 旧表名);
方法一：
mysql> create table yyds1 like yyds; #克隆一个表
Query OK, 0 rows affected (0.01 sec)
mysql> insert into yyds1 select * from yyds; #将旧表内容结构导入新建表中
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc yyds; #查看旧表
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> desc yyds1; #查看新表发现表结构一样
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
方法二：
mysql> mysql> create table select * from yyds); #创建表时导入数据
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc yyds2;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| phone | int(11) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
6.2查看表结构
获取数据表的表结构、索引等信息
-
show create table表名\G;
-
desc 表名\G;
6.3清表——删除表内的所有数据
6.3.1delete
DELETE清空表后，返回的结果内有删除的记录条目；
DELETE工作时是一行一行的删除记录数据的；
如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。
delete from 表名；
mysql> select *from yyds2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 康康 | 123456 |
| 2 | 颖颖 | 123456 |
+----+--------+----------+
2 rows in set (0.00 sec)
mysql> delete from yyds2; #删除表，一行一行的删
Query OK, 2 rows affected (0.00 sec)
mysql> select *from yyds2;
Empty set (0.00 sec)
mysql> insert into yyds2 (name,password) values ('雯雯','123456');
Query OK, 1 row affected (0.00 sec)
mysql> select *from yyds2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 3 | 雯雯 | 123456 | #因为id自增长字段，使用delete删除后，id会从最大的记录后自增
+----+--------+----------+
1 row in set (0.00 sec)
6.3.2truncate删除
- TRUNCATE 清空表后，没有返回被删除的条目；
- TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；
- 使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录
truncate table 表名;
mysql> select *from yyds2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 3 | 雯雯 | 123456 |
+----+--------+----------+
1 row in set (0.00 sec)
mysql> truncate table yyds2; #相当于格式化表格
Query OK, 0 rows affected (0.01 sec)
mysql> select *from yyds2;
Empty set (0.00 sec)
mysql> insert into yyds2 (name,password) values ('雯雯','123456');
Query OK, 1 row affected (0.00 sec)
mysql> select *from yyds2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 雯雯 | 123456 | #自增字段从1开始了
+----+--------+----------+
1 row in set (0.00 sec)
7、创建临时表
临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。
CREATE TEMPORARY TABLE 表名（字段1 数据类型, 字段2 数据类型（，…] [,PRIMARY KEY（主键名）]）；
添加临时表test
create temporary table test (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
查看当前库中所有表
show tables; #查看不到临时表
在临时表中添加数据
insert into test values(0001,'zhangsan',123456789,'play');
#查看当前表中所有数据
select * from test;
#退出数据库
quit
重新登录后进行查看
mysql -u root -p
#查看之前临时表中所有数据，发现已经被自动销毁
select * from test;
8、创建外键约束,保证数据的完整性和一致性
外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）
注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。
#创建主表 class
create table class (cid int, cname char(10));
#创建从表 student
create table student (id int, name char(10), age int, classid int);
#为主表class添加一个主键约束。主键名建议以“PK"开头。
alter table class add constraint PK_CID primary key(cid);
#为从表student表添加外键，并将student表的cid字段和student表的classid字段建立外键关联。外键名建议以“FK"开头。
alter table student add constraint FK_CLASSID foreign key(classid) references class(cid);
#插入新的数据记录时要先插入主表的在插入从表的
#在主表插入数据
insert into class values(1,'下棋');
insert into class values(2,'跳舞');
insert into class values(3,'游泳');
#在从表插入数据
insert into student values(1,'张三',22,3);
insert into student values(2,'李四',34,2);
insert into student values(3,'王五',45,1);
#删除数据记录时要先删除从表再删主表，也就是说删除主键表必须先删除其他关联的表中记录
delete from student where name='张三';
delete from class where cid=3;
select * from xy;
select * from kc;
9、数据库用户管理
CREATE USER ‘用户名’@‘来源地址’ [IDENTIFIED BY [PASSWORD] ‘密码’];
‘用户名’：指定将创建的用户名
‘来源地址’：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
‘密码’：
若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密；
若使用加密密码，需要先使用SELECT PASSWORD(‘密码’); 获取密文，再在语句中添加 PASSWORD ‘密文’;
若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
9.1使用明文创建用户
#创建用户zhangsan只允许从本机登录密码为123123
create user '张三'@'localhost' identified by '123123';
9.2使用密文创建用户
#获取密文
select password('123123');
#创建用户
create user 'my'@'localhost'identified by password'*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1';
9.3查看用户信息
创建后的用户保存在 mysql 数据库的 user 表里
use mysql; #切换库
SELECT User,authentication_string,Host from user; #查看user表中的用户信息
#指定字段用户，登录认证字符串，登录主机，查看数据记录
9.4重命名用户
rename user '原用户名'@'登录主机' to '新用户名'@'登录主机';
9.5删除用户
DROP USER '用户名'@'登录主机'；
9.6修改当前登录用户密码
SET PASSWORD = PASSWORD('123123');
9.7修改其他用户密码
SET PASSWORD FOR '用户'@'登录主机'=password（'新的密码'）
9.8忘记 root 密码的解决办法
1、#修改配置文件/etc/my.cnf，不使用密码直接登录到mysql。
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加此行，使登录mysql不使用授权表
systemctl restart mysqld #重启服务
mysql #直接登录
2、#使用update命令修改root密码，刷新数据库。
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('123123') where user='root';
flush privileges; #刷新数据库
quit #退出
mysql -uroot -p123123 #免交互登录"-p密码"不能有空格
3、#删除配置文件中的配置，之后重启服务。
注意：最后再把/etc/my.conf 配置文件里的skip-grant-tables 删除，并重启mysql服务。
vim /etc/my.cnf
systemctl restart mysqld #重启服务
10、数据库用户授权
10.1授予权限
GRANT语句：专门用来设置数据库用户的访问权限。
- 当指定的用户名不存在时，GRANT语句将会创建新的用户；
- 当指定的用户名存在时，GRANT 语句用于修改用户信息。
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“”。例如，使用“kgc.”表示授权操作的对象为 kgc数据库中的所有表。
‘用户名@来源地址’：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.my.com”、“192.168.52.%”等。
IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分， 则用户的密码将为空。
10.2查看权限
SHOW GRANTS FOR 用户名@来源地址;
10.3撤销权限
REVOKE ALL ON *.* FROM '用户'@'来源地址'; #撤销所有权限
扫一扫
3269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
