超实用的MySQL数据管理

一、数据库基本操作

1.1 查看已存在数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| fy                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
#大部分SQL操作命令必须以；结束

1.1.1 mysql默认4个数据库

● information_schema：定义访问数据库元数据的方式。数据库名和表名，列的数据类型、访问权限等。
● mysql：核心数据库，负责存储数据库用户、权限、关键字等用户需要使用的控制和管理信息。
● performance_schema：数据库的性能参数，存储引擎等。
● sys：sys系统库下包含许多视图，它们以各种方式对performance_schema表进行聚合计算展示。

1.2 查看当前数据库中有哪些表

mysql> use mysql;
mysql> show tables;

1.3 查看表的结构

mysql> use mysql;
mysql> describe user;#user为表

1.4 SQL数据库语音的分类

● DDL（Data Definition Language，数据定义语音）：用来建立数据库、数据库对象和定义字段，如CREATE、ALTER、DROP。
● DML（Data Manipulation Language，数据操纵语言）：用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE。
● DQL（Data Query Language，数据查询语言）：用来查询数据库中的数据，如SELECT。
● DCL（Data Control Language，数据控制语言）：用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE。

1.4.1 DDL（创建数据库和表）

1.创建新的数据库

mysql> create database auth;#新建库auth

2.创建新的表

#CREATE TABLE 表名（字段1名称类型，字段2名称类型，...，PRIMARY KEY（主键名））
mysql> create table users (user_name char(16) not null, user_passwd char(48)default '', primary key(user_name));

字段名称、类型、约束解释：
字段1名称：属性名称，自定义
字段1类型：int（4） 整型 代表0000-9999
double 浮点型
decimal（5,2）有效数字是5位，小数点后面保留2位 100.00；099.50
float 单精度浮点 4字节
char 字符
char （10）固定长度字符串，字符串要用单引号引起来
varchar（50）可变长度字符串
字段1约束：
非空约束：内容不允许为空 not null
主键约束：非空且唯一 标识 primary key（主键）
默认约束：假如没有填数据，默认预先设定的值填写 default ‘未知’
自增特性：id 1 2 3 4 auto_increment（自动增长）
存储引擎：myisam innodb
字符集：UTF-8

3.删除一个数据表

mysql> drop table users;

4.删除一个数据库

mysql> drop database auth;

1.4.2 DML（管理数据表中的数据）

1.插入数据记录

#INSERT INTO 表名（字段1，字段2，...） VALUES（字段1的值，字段2 的值，...）
mysql> use auth;
mysql> insert into users(user_name,user_passwd) values('zhangsan', password ('123456'));

2.查询数据记录

#select 字段名1，字段名2，...from 表名 where 条件表达式
mysql> mysqlt * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select user_name,user_passwd from auth.users where user_name='zhangsan';
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

3.修改数据记录

#update 表名 set 字段名 1=字段值 1 [，字段名 2=字段值 2] where 条件表达式
mysql> update auth.users set user_passwd=password('') where user_name='lisi';
mysql> select * from auth.users;

4.修改数据库密码

mysql> update mysql.user set authentication_string=password('123457') where user='root';
[root@server1 ~]# mysqladmin -u root -p'123457' password '123456'

5.删除数据库记录
● delete from 表名 where 条件表达式

mysql> delete from auth.users where user_name='lisi';
mysql> select * from auth.users;

1.5 数据库高级操作

1.5.1 清空表

● delete from 语句，可以删除表内的数据，除此之外还可以使用truncate table 语句实现清空表内记录。delete from 语句可以使用where子句对删除的结果集进行过滤选择，这样更方便、更灵活。truncate table语句是删除表中所有记录数据，没法定制，灵活性稍差。

mysql> create table tmp like player;#通过like方法，复制player表生成tmp表
mysql> insert into tmp select * from player;#通过player表生成tmp表内数据记录
mysql> delete from tmp where level >= 45;
mysql> delete from tmp;
mysql> insert into tmp select * from player;
mysql> truncate table tmp;
mysql> select count(*) from tmp;

1.5.2 临时表

● 是临时建立的表，并不会长期存在，主要用于保存一些临时数据。临时表有个特性，就是只在当前连接可见，当前连接下可执行增删改查等操作，当连接被关闭后，临时表就会被MySQL删除，相关的资源也会被释放。

mysql> select * from mytmp;#查看mytmp表是否存在
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=lnnoDB default charset=utf8;
mysql> insert into mytmp(name,level) values('aa',10);
mysql> select * from mytmp;#退出前
mysql> quit
mysql> select * from mytmp;#退出后

1.5.3 克隆表

mysql> drop table tmp;
mysql> create table tmp as select * from player;

方法一：通过like方式克隆表

mysql> create table test like mytmp;#通过like方法，复制mytmp表生成test表
mysql> show create table test\g
mysql> select * from test;
mysql> insert into test select * from mytmp;#将mytmp表的数据写入test表
mysql> select * from test;

方法二：通过创建表的方式克隆

mysql> show create table mytmp\g
mysql> create table `test` (`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;
#改名后创建新表
mysql> select * from mytmp;

二、数据库用户授权

● grant：当用户已存在时，直接提权。
● 当用户不存在时，先创建用户，再提权。
● revoke：只撤销权限，不删除用户

2.1 授予权限

● grant 权限列表 on 数据库名.表名 to 用户名@来源地址 [ identified by ‘密码’ ]

mysql> grant select on auth.* to ' xiaoqi'@'localhost' identified by '123456';
[root@server1 ~]# mysql -u xiaoqi -p

2.2 查看权限

● show grants for 用户名@来源地址

mysql> show grants; ##查看当前用户的权限
mysql> show grants for xiaoqi@localhost; ##查看从本地登录的xiaoqi用户的权限
mysql> select user from mysql.user; ##查看当前系统中的用户

2.3 撤销用户权限

● revoke 权限列表 on 数据库名. 表名 from 用户名@来源地址

mysql> revoke all privileges on auth.* from ‘xiaoqi’@‘localhost’;
mysql> show grants for 'xiaoqi'@'localhost';#确认撤销对auth库的权限