MySQL数据库的基础操作及用户管理
一.数据库的基本命令
1.登录数据库
指定登录用户和密码登录数据库
[root@server ~]# mysql -uroot -pqwer1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2.查询库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
3.进入使用库
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
4.查询表
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 |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
5.查询库结构或者表结构
describe 数据库名
describe 表名
describe可以简写desc
mysql> describe user;
字段说明
Field:字段名称;比如姓名、年龄、身高、体重
Type:数据类型/属性
Null :是否允许为空
Key :主键
Default :默认值;默认值可以设为空
Extra :扩展属性，例如:标志符列(标识了种子(起始位置)，增量/步长)
比如我的种子是1，增量/步长是2，那就是1 3 5 7 …
6.退出数据库
exit或者quit
mysql> exit
Bye
[root@server ~]#
二.常用的数据类型
int: 整型
用于定义整数类型的数据
float:单精度浮点4字节32位
准确表示到小数点后六位
double:双精度浮点8字节64位
loat的双倍
char:固定长度的字符类型
用于定义字符类型数据；比如：手机号码char(11)
varchar: 可变长度的字符类型 ，设置上限
比如：varchar(11)字符上限11个
text: 文本
image:图片
decimal(5,2): 5个有效长度数字，小数点后面有2位
指定长度数组；比如：若为12345.899和1 2345.891会显示12345.90和12345.89;四舍五入
Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错
说明：截取:四舍五入 ；截断:不四舍五入
三.主键和外键
1.主键
数据表中的每行记录都必须是唯一的，而不允许出现相同的记录，通过定义主键可以保证记录（实体）的唯一性。
键，就是关键字，它是关系模型中一个非常重要的元素。
主键唯一标识表中的行数据，一个主键值对应一行数据。
主键由一个或者多个字段组成，其值具有唯一性，不允许取控制（NULL）。
一个表只能有一个主键。
2.外键
如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
外键是用于建立和加强两个表数据之间的链接的一列或多列。
一个关系数据库通常包含多个表，通过外键可以使这些表关联起来。
3.主键表和外键表的理解
以公共关键字作主键的表为主键表(父表、主表)
以公共关键字作外键的表为外键表(从表、外表)
说明
与外键关联的主表的字段必须设置为主键。要求从表不能是临时表。
主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。
4.主表从表以及主键外键的创建
mysql> create table yy1 (hobid int(4),hobname varchar(50));
Query OK, 0 rows affected (0.00 sec) #创建主表yy1
mysql> create table yy2 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));
Query OK, 0 rows affected (0.00 sec) #创建从表yy2
mysql> alter table yy1 add constraint PK_hobid primary key(hobid);
Query OK, 0 rows affected (0.01 sec) #主表添加主键约束，constraint表示约束
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table yy2 add constraint FK_hobid foreign key(hobid) references yy1(hobid);
Query OK, 0 rows affected (0.01 sec) #从表添加外键，并且将2表的hobid字段建立外键关联
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table yy2; #查看外键关联
mysql> desc yy1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| hobid | int(4) | NO | PRI | NULL | |
| hobname | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc yy2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| hobid | int(4) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
说明：
插入新的数据时要先插入主表，再插入从表
删除数据时要先删除从表再删除主表，就是说要删除主键表时必须先删除其他与之相关联的表
如果要删除外键约束字段，要先删除外键约束，再删除外键名
四.MySQL中常见的约束
（1）主键约束(primary key)
（2）外键约束(foreign key)
作用：误删，修改时可以保证数据的完整性和一致性
（3）非空约束(not null)
（4）唯一性约束(unique [ key l index] )
（5）默认值约束(default)
（6）自增约束(auto_increment)
五.数据库的增删改查SQL语句
Structured Query Language的缩写，即结构化查询语言；关系型数据库的标准语言，用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能(增、删、改、查)，分为以下四类：
DDL:数据定义语言，用于创建数据库对象，如库、表、索引等
DML:数据操纵语言，用于对表中的数据进行管理
DQL:数据查询语言，用于从数据表中查找符合条件的数据记录
DCL:数据控制语言，用于设置或者更改数据库用户或角色权限
1.DDL:数据定义语言
（1）创建数据库和表
① 创建新的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database kk; #创建新的数据库
Query OK, 1 row affected (0.01 sec)
mysql> show databases; ##查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| kk |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
② 创建新的表
mysql> use kk; #进入库使用库
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test1(id int not null,name char(12)not null,score decimal(5,2),passwd char(48) default'',primary key(id)); #创建表
Query OK, 0 rows affected (0.03 sec)
mysql> show tables; #查看表
+--------------+
| Tables_in_kk |
+--------------+
| test1 |
+--------------+
1 row in set (0.00 sec)
mysql> desc test1; #查看表结构
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(12) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| passwd | char(48) | YES | | | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
（2）删除数据库和表
① 删除表
使用use在库的情况下使用drop table 表名；
没有用use在库的情况下使用drop table 数据库名.表名；
mysql> drop table test1; #删除表test1
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
② 删除库
drop database 数据库名；
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kk |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database kk; #删除库kk
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.DML:数据操纵语言
（1）insert 插入新数据
mysql> show tables
-> ;
+----------------+
| Tables_in_koko |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> insert into test1(id,name,score,passwd) values(1,'zhangsan',86.5,password('123456'));
#插入新数据，说明：password('123456')表示查询数据记录时密码字串以加密形式显示；若使用password()表示查询时以明文显示
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> seelect * from test1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'seelect * from test1' at line 1
mysql> select * from test1; #查看表里数据内容
+----+----------+-------+-------------------------------------------+
| id | name | score | passwd |
+----+----------+-------+-------------------------------------------+
| 1 | zhangsan | 86.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+----------+-------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into test1 values(2,'lisi',89.5,234567); #再插入一条新数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1; #查看表里数据内容
+----+----------+-------+-------------------------------------------+
| id | name | score | passwd |
+----+----------+-------+-------------------------------------------+
| 1 | zhangsan | 86.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 2 | lisi | 89.50 | 234567 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)
（2）update 更新原有数据
修改、更新数据表中的数据记录
mysql> select * from test1; #查看原表的内容
+----+----------+-------+-------------------------------------------+
| id | name | score | passwd |
+----+----------+-------+-------------------------------------------+
| 1 | zhangsan | 86.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 2 | lisi | 89.50 | 234567 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> update test1 set passwd=password('') where name='zhangsan'; #修改表内数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test1; #查看修改后的表内容
+----+----------+-------+--------+
| id | name | score | passwd |
+----+----------+-------+--------+
| 1 | zhangsan | 86.50 | |
| 2 | lisi | 89.50 | 234567 |
+----+----------+-------+--------+
2 rows in set (0.00 sec)
mysql> update test1 set name='wangwu',score=84.5 where id=2; #再修改表内容多个字段
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test1; #查看修改后的表内容
+----+----------+-------+--------+
| id | name | score | passwd |
+----+----------+-------+--------+
| 1 | zhangsan | 86.50 | |
| 2 | wangwu | 84.50 | 234567 |
+----+----------+-------+--------+
2 rows in set (0.00 sec)
（3）delete 删除不需要的数据（表内容）
数据表中删除指定的数据记录
mysql> select * from test1; #查看原表内容
+----+----------+-------+--------+
| id | name | score | passwd |
+----+----------+-------+--------+
| 1 | zhangsan | 86.50 | |
| 2 | wangwu | 84.50 | 234567 |
+----+----------+-------+--------+
2 rows in set (0.00 sec)
mysql> delete from test1 where id=1; #删除表中ID为1的行的数据
Query OK, 1 row affected (0.01 sec)
mysql> select * from test1; #查看删除后的表的数据内容
+----+--------+-------+--------+
| id | name | score | passwd |
+----+--------+-------+--------+
| 2 | wangwu | 84.50 | 234567 |
+----+--------+-------+--------+
1 row in set (0.00 sec)
mysql>
3.DQL:数据查询语言
查询使用select
mysql> select * from test1; #查看表test1的内容
+----+----------+-------+-------------------------------------------+
| id | name | score | passwd |
+----+----------+-------+-------------------------------------------+
| 1 | zhangsan | 86.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 2 | wangwu | 84.50 | 234567 |
| 3 | tianqi | 90.00 | 987987 |
| 4 | zhaoliu | 76.00 | 567567 |
| 5 | wudi | 63.00 | 123123 |
| 6 | yaoming | 98.00 | 345345 |
+----+----------+-------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> select id,name from test1; #查看test1表中id和name字段的数据
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 3 | tianqi |
| 4 | zhaoliu |
| 5 | wudi |
| 6 | yaoming |
+----+----------+
6 rows in set (0.00 sec)
mysql> select name from test1; #查看test1表中name字段的数据
+----------+
| name |
+----------+
| zhangsan |
| wangwu |
| tianqi |
| zhaoliu |
| wudi |
| yaoming |
+----------+
6 rows in set (0.00 sec)
mysql> select id,name,score from test1 where id=3; #查看test1表中id=3行的id和name字段的数据
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 3 | tianqi | 90.00 |
+----+--------+-------+
1 row in set (0.00 sec)
mysql> select * from test1 limit 2; #查看test1表中头3行的内容
+----+----------+-------+-------------------------------------------+
| id | name | score | passwd |
+----+----------+-------+-------------------------------------------+
| 1 | zhangsan | 86.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 2 | wangwu | 84.50 | 234567 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from test1 limit 2,2; #查看test1表中第3行后开始前2行的数据
+----+---------+-------+--------+
| id | name | score | passwd |
+----+---------+-------+--------+
| 3 | tianqi | 90.00 | 987987 |
| 4 | zhaoliu | 76.00 | 567567 |
+----+---------+-------+--------+
2 rows in set (0.00 sec)
mysql> select * from test1 limit 2,3; #查看test1表中第3行后开始前3行的数据
+----+---------+-------+--------+
| id | name | score | passwd |
+----+---------+-------+--------+
| 3 | tianqi | 90.00 | 987987 |
| 4 | zhaoliu | 76.00 | 567567 |
| 5 | wudi | 63.00 | 123123 |
+----+---------+-------+--------+
3 rows in set (0.00 sec)
4.DCL:数据控制语言
alter 修改表名和表结构，不是内容
（1）修改表名
alter table 旧表名 rename 新表名;
mysql> show tables;
+----------------+
| Tables_in_koko |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> alter table test1 rename test1_new; #修改表名
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_koko |
+----------------+
| test1_new |
+----------------+
1 row in set (0.00 sec)
（2）扩展表结构（增加字段）
alter table 表名 add address varchar(50) default ‘地址不祥’;
add表示增加，字段为地址address，数据类型为varchar，
default ’ 地址不详’:表示此字段设置默认值为地址不详，可与NOT NULL配合使用
mysql> alter table test1_new add address varchar(50) default '地址不祥';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test1_new;
+----+----------+-------+-------------------------------------------+----------- ---+
| id | name | score | passwd | address |
+----+----------+-------+-------------------------------------------+----------- ---+
| 1 | zhangsan | 86.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 地址不祥 |
| 2 | wangwu | 84.50 | 234567 | 地址不祥 |
| 3 | tianqi | 90.00 | 987987 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 567567 | 地址不祥 |
| 5 | wudi | 63.00 | 123123 | 地址不祥 |
| 6 | yaoming | 98.00 | 345345 | 地址不祥 |
+----+----------+-------+-------------------------------------------+----------- ---+
6 rows in set (0.00 sec)
（3）修改字段列名，添加唯一键
alter table 表名 change 旧列名 新列名 数据类型；
说明：
unique key:唯一键（特性：唯一但可以为空，空值允许出现一次）
primary key:唯一且非空
change：可修改字段名、数据类型、约束等所有项
mysql> alter table test1_new change name user_name varchar(20) unique key; #修改表test1_new的字段name名改成user_name并且添加唯一键unique key
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from test1_new; #查看表tset1_new的内容看字段name名是否修改成功
+----+-----------+-------+-------------------------------------------+---------- ----+
| id | user_name | score | passwd | address |
+----+-----------+-------+-------------------------------------------+---------- ----+
| 1 | zhangsan | 86.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 地址不祥 |
| 2 | wangwu | 84.50 | 234567 | 地址不祥 |
| 3 | tianqi | 90.00 | 987987 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 56756s7 | 地址不祥 |
| 5 | wudi | 63.00 | 123123 | 地址不祥 |
| 6 | yaoming | 98.00 | 345345 | 地址不祥 |
+----+-----------+-------+-------------------------------------------+---------- ----+
6 rows in set (0.00 sec)
mysql> desc test1_new; #查看表test1_new的表结构看唯一键是否修改成功
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| passwd | char(48) | YES | | | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
5 rows in set (0.00 sec)
（4）删除字段
alter table 表名 drop 字段名;
mysql> alter table test1_new drop passwd; #删除test1_new表中passwd的字段
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test1_new;
+----+-----------+-------+--------------+
| id | user_name | score | address |
+----+-----------+-------+--------------+
| 1 | zhangsan | 86.50 | 地址不祥 |
| 2 | wangwu | 84.50 | 地址不祥 |
| 3 | tianqi | 90.00 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 地址不祥 |
| 5 | wudi | 63.00 | 地址不in祥 |
| 6 | yaoming | 98.00 | 地址不祥 |
+----+-----------+-------+--------------+
6 rows in set (0.00 sec)
5.扩展`
mysql> create table if not exists info (id int(4) zerofill primary key auto_increment,name varchar(10) not null, cardid int(18) not null unique key,hobby varchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values(1,'tianqi',320158,'跳舞'),(2,'liuxiang',25896,'跑步');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info;
+------+----------+--------+--------+
| id | name | cardid | hobby |
+------+----------+--------+--------+
| 0001 | tianqi | 320158 | 跳舞 |
| 0002 | liuxiang | 25896 | 跑步 |
+------+----------+--------+--------+
2 rows in set (0.00 sec)
说明：
id int (4) zerofill primary key auto increment #这是指定主键的第二种方式
if not exists: 表示检测要创建的表是否已存在，如果不存在就继续创建
int(4) zerofill: 表示若数值不满4位数，则前面用"0"填充，例0001
auto increment: 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且
添加失败也会自动递增一次
#unique key:表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一-张表中可以有多个唯一键
not null:表示此字段不允许为NULL
六.基于数据表基础命令的高级操作
1.复制表结构like
create table test1 like test;
说明：创建新的表test1（复制test表的表结构），复制的是表的结构，不复制表的内容
mysql> show tables;
+----------------+
| Tables_in_koko |
+----------------+
| test |
+----------------+
1 row in set (0.01 sec)
mysql> select * from test;
+----+-----------+-------+--------------+
| id | user_name | score | address |
+----+-----------+-------+--------------+
| 1 | zhangsan | 86.50 | 地址不祥 |
| 2 | wangwu | 84.50 | 地址不祥 |
| 3 | tianqi | 90.00 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 地址不祥 |
| 5 | wudi | 63.00 | 地址不祥 |
| 6 | yaoming | 98.00 | 地址不祥 |
+----+-----------+-------+--------------+
6 rows in set (0.00 sec)
mysql> desc test;
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)
mysql> create table test1 like test; #创建表test1复制test表的格式
Query OK, 0 rows affected (0.00 sec)
mysql> show tables; #查看表有没有创建成功
+----------------+
| Tables_in_koko |
+----------------+
| test |
| test1 |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from test1; #查看新创的表test1的数据内容为空
Empty set (0.00 sec)
mysql> desc test1; #查看test1表的表结构
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)
2.克隆表数据创建生成新的表create
create table test2 (select *from test);
说明：将数据表的内容克隆复制生成到新的表中，复制的是表的结构和数据内容，但是表的唯一键属性不复制
数据表的数据内容备份也可以使用插入insert into
insert into test1 select * from test;
前提 test1必须是存在的表
mysql> create table test2 (select *from test); #创建表test2（内容克隆test表的内容）
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show create table test2\G #获取数据表的创建信息，包含表结构索引等信息
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE "test2" (
"id" int(11) NOT NULL,
"user_name" varchar(20) DEFAULT NULL,
"score" decimal(5,2) DEFAULT NULL,
"address" varchar(50) DEFAULT '地址不祥'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test2; #查看新创建表test2的数据内容
+----+-----------+-------+--------------+
| id | user_name | score | address |
+----+-----------+-------+--------------+
| 1 | zhangsan | 86.50 | 地址不祥 |
| 2 | wangwu | 84.50 | 地址不祥 |
| 3 | tianqi | 90.00 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 地址不祥 |
| 5 | wudi | 63.00 | 地址不祥 |
| 6 | yaoming | 98.00 | 地址不祥 |
+----+-----------+-------+--------------+
6 rows in set (0.00 sec)
mysql> desc test2; #查看新创表test2的表结构信息
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | | NULL | |
| user_name | varchar(20) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)
mysql> insert into test1 select * from test; #将表test的内容备份插入到表test1
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from test1; #查看插入后test1表的数据内容
+----+-----------+-------+--------------+
| id | user_name | score | address |
+----+-----------+-------+--------------+
| 1 | zhangsan | 86.50 | 地址不祥 |
| 2 | wangwu | 84.50 | 地址不祥 |
| 3 | tianqi | 90.00 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 地址不祥 |
| 5 | wudi | 63.00 | 地址不祥 |
| 6 | yaoming | 98.00 | 地址不祥 |
+----+-----------+-------+--------------+
6 rows in set (0.00 sec)
mysql> desc test1;
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)
复制过表的内容后是可以使用alter table更改表的信息，比如字段名和唯一键
mysql> alter table test2 change id id_new int(11) primary key;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+--------+-----------+-------+--------------+
| id_new | user_name | score | address |
+--------+-----------+-------+--------------+
| 1 | zhangsan | 86.50 | 地址不祥 |
| 2 | wangwu | 84.50 | 地址不祥 |
| 3 | tianqi | 90.00 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 地址不祥 |
| 5 | wudi | 63.00 | 地址不祥 |
| 6 | yaoming | 98.00 | 地址不祥 |
+--------+-----------+-------+--------------+
6 rows in set (0.00 sec)
mysql> desc test2;
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id_new | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)
3.删除表数据delete和truncate
（1）delete
delete from 表名;
delete删除的是表的数据内容，不会删除表的结构
delete清空表后，返回的结果内有删除的记录条目
delete删除时是一行一行删除数据记录的，如果表中有自增长的字段，使用delete from 删除后，再添加新的记录会从原来最大的记录id后面继续自增长写入数据
mysql> delete from test2; #删除表test2的数据内容
Query OK, 6 rows affected (0.01 sec)
mysql> select * from test2;
Empty set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_koko |
+----------------+
| test |
| test1 |
| test2 |
+----------------+
3 rows in set (0.00 sec)
mysql> desc test2;
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id_new | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)
（2）truncate
truncate table test1;
truncate’删除的是表的数据内容，也不会删除表结构
但是，turncate工作时是将表结构按照原样重新建立，所以速度会比delete清空表的速度快，清空后数据id会从1重新记录
truncate清空表后，没有返回被删除记录的条目
mysql> truncate table test1; #删除表test1的数据内容
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1;
Empty set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_koko |
+----------------+
| test |
| test1 |
| test2 |
+----------------+
3 rows in set (0.00 sec)
mysql> desc test1;
+-----------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+--------------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 地址不祥 | |
+-----------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)
4.创建临时表
mysql> create temporary table zzz(id int(4) zerofill primary key auto_increment,name varchar(10) not null,
cardid int(18) not null unique key,hobby varchar(50)); #创建临时表
Query OK, 0 rows affected (0.00 sec)
mysql> insert into zzz values(1,'zhangsan',123456,'running'); #插入数据到临时表zzz中
Query OK, 1 row affected (0.00 sec)
mysql> select * from zzz; #查看临时表zzz的数据内容
+------+----------+--------+---------+
| id | name | cardid | hobby |
+------+----------+--------+---------+
| 0001 | zhangsan | 123456 | running |
+------+----------+--------+---------+
1 row in set (0.00 sec)
mysql> show tables; #查看表发现没有临时表的信息
+----------------+
| Tables_in_koko |
+----------------+
| test |
| test1 |
| test2 |
+----------------+
3 rows in set (0.00 sec)
mysql> quit
Bye
[root@server ~]# mysql -uroot -pqwer1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from zzz; #退出后再连接查看表数据已不存在
ERROR 1046 (3D000): No database selected
说明：
临时表无法创建外键
数据记录的过程
insert into test——>test这张表，会先复制一份表数据到内存里面，给我们进行修改
插入数据确定提交了，才会写入数据表中然后再保存在磁盘里面
create table test01——>只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉
七.数据库的用户管理
1.新建用户
create user ‘用户名’@‘来源地址’[identified by [password]‘密码’];
说明：
用户名：指定将创建的用户
来源地址：指定新创建的用户可在哪些主机上登录，可使用IP地址（192.168.206.77）、网段（192.168.206.0/24）、主机名（localhost）的形式，本地可用localhost，允许任意主机登录，可以使用通配符%
密码：[password]表示密码加密，若使用明文密码，直接输入’密码’，插入到数据库是由mysql自动加密；若使用加密密码，需要先使用【select password（‘密码’）先获取密文再添加进入 password ‘密文’
如果省略identified by 部分，用户密码将会为空（不建议使用)
mysql> create user 'user1'@'localhost' identified by '123456'; #创建用户user1以明文密码的方式
Query OK, 0 rows affected (0.02 sec)
mysql> select password('abc123'); #查询明文密码对应的密文
+-------------------------------------------+
| password('abc123') |
+-------------------------------------------+
| *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> create user 'user2'@'localhost' identified by password'*6691484EA6B50DDDE1926A220DA01FA9E575C18A';
Query OK, 0 rows affected, 1 warning (0.00 sec) #以密文加密方式创建用户user2
2.查询用户信息
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> select user,authentication_string,host from user; #查询用户信息
+---------------+-------------------------------------------+-----------------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------------+
| root | *D75CC763C5551A420D28A227AC294FADE26A2FF2 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | % |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | localhost |
| root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 192.168.206.188 |
| user1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| user2 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost
3.重命名
rename重命名
mysql> rename user 'user1'@'localhost' to 'zhangsan'@'localhost'; #将用户user1重命名为user2
Query OK, 0 rows affected (0.01 sec)
mysql> select user,authentication_string,host from user; #查询用户信息看用户名是否重命名成功
+---------------+-------------------------------------------+-----------------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------------+
| root | *D75CC763C5551A420D28A227AC294FADE26A2FF2 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | % |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | localhost |
| root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 192.168.206.188 |
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| user2 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
+---------------+-------------------------------------------+-----------------+
8 rows in set (0.00 sec)
4.删除用户
mysql> drop user 'user2'@'localhost'; #删除用户user2
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user; #查看用户user2是否删除成功
+---------------+-------------------------------------------+-----------------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------------+
| root | *D75CC763C5551A420D28A227AC294FADE26A2FF2 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | % |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | localhost |
| root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 192.168.206.188 |
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
+---------------+-------------------------------------------+-----------------+
7 rows in set (0.00 sec)
5.修改密码
（1）修改当前登录用户的密码
mysql> set password = password('123123'); #修改当前用户密码
Query OK, 0 rows affected, 1 warning (0.00 sec)
（2）修改其他用户的密码
mysql> set password for 'zhangsan'@'localhost' = password('123123'); #修改其他用户zhangsan的密码
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------------+
| root | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | % |
| bbsuser | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | localhost |
| root | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 192.168.206.188 |
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |
+---------------+-------------------------------------------+-----------------+
7 rows in set (0.00 sec)
6.root用户密码忘记解决方法
修改/etc/my.cnf配置文件，设置免密登录
[root@server ~]# vim /etc/my.cnf
[root@server ~]# systemctl restart mysqld #重启数据库服务
[root@server ~]# mysql #mysql登录直接进入
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> update mysql.user set authentication_string = password('abc123') where user='root'; #修改root用户密码
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 1
mysql> flush privileges; #刷新数据表内的权限
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@server ~]# mysql -uroot -pabc123 #使用修改后的密码重新登录数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
密码修改成功后，要到/etc/my.cnf配置文件里把免密登录设置删除后重启服务。
7.数据库提权
（1）用户授权
grant 提权
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘来源地址’ [ IDENTIFIED BY ‘密码’] ;
grant all on * . *：代表提权所有表
说明：
权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select，insert,update”， 使用"all"表示所有权限，可授权执行任何操作
数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符" * "。
例如，使用“kgc.*"表示授权操作的对象为kgc数据库中的所有表
‘用户名@来源地址’：用于指定用户名称和允许访问的客户机地址。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%jkj.com"、“192. 168.226.%”等
IDENTIFIEDBY:用于设置用户连接数据库时所使用的密码字符串。
在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。
mysql> grant select on koko.* to 'wangwu'@'localhost' identified by '456789';
Query OK, 0 rows affected, 1 warning (0.00 sec) #给用户wangwu对于数据库koko下面的所有表有查询的权限
mysql> flush privileges; #刷新数据表内的权限
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@server ~]#
[root@server ~]# mysql -uwangwu -p456789 #使用wangwu用户登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use koko
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_koko |
+----------------+
| test |
| test1 |
| test2 |
+----------------+
3 rows in set (0.00 sec)
mysql> select * from test; #查看表内容，检查用户wangwu是否有查看权限
+----+-----------+-------+--------------+
| id | user_name | score | address |
+----+-----------+-------+--------------+
| 1 | zhangsan | 86.50 | 地址不祥 |
| 2 | wangwu | 84.50 | 地址不祥 |
| 3 | tianqi | 90.00 | 地址不祥 |
| 4 | zhaoliu | 76.00 | 地址不祥 |
| 5 | wudi | 63.00 | 地址不祥 |
| 6 | yaoming | 98.00 | 地址不祥 |
+----+-----------+-------+--------------+
6 rows in set (0.00 sec)
（2）查看权限
mysql> show grants for 'wangwu'@'localhost'; #查看用户wangwu拥有的权限
+--------------------------------------------------+
| Grants for wangwu@localhost |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'wangwu'@'localhost' |
| GRANT SELECT ON "koko".* TO 'wangwu'@'localhost' |
+--------------------------------------------------+
2 rows in set (0.00 sec)
（3）撤销权限
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址
USAGE权限只能用于数据库登陆，不能执行任何操作;
USAGE权限不能被回收，即REVOKE不能删除用户。
mysql> revoke select on koko.* from 'wangwu'@'localhost'; #移除用户wangwu对于数据库koko下面所有表的查询权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'wangwu'@'localhost';
+--------------------------------------------+
| Grants for wangwu@localhost |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'wangwu'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
八.总结
这里主要介绍了数据库的基础操作和用户管理。
小结
1.删除类型的总结比较（drop、delete、truncate)
drop table 表名
属于DDL，不可回滚，不可带where，表内容和结构删除，删除速度快
delete from 表名
属于DML，可回滚(可恢复)，可带where，表结构在，表内容要看where执行的情况，删除速度慢,需要逐行删除
truncate table 表名
属于DDL，不可回滚，不可带where，表内容删除，删除速度快
适用场景
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete， 并且带上where子句
保留表而删除所有数据的时候用truncate
删除速度比较
drop快于truncate快于delete
数据安全性比较
delete最安全，因为是逐行删除且有记录，易于恢复
2.mysql中常见的约束
主键约束
外键约束
非空约束
唯一性约束
默认值约束
自增约束
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
