MySQL数据的管理

1. 数据库的基本操作

1.1 查看数据库信息

mysql> show databases;  ##查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| myadm              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

1.2 创建数据库

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

1.3 创建表

 mysql> use tang;  ##进入tang这个数据库
Database changed
mysql> create table tang (id int(6) not null primary key auto_increment,name varchar(10),adress varchar (20)); ###创建tang这个表，后面是设置的表里面的属性
Query OK, 0 rows affected (0.01 sec)

1.4 查看数据库中的数据表信息

mysql> show tables;   ##查看数据表信息
+----------------+
| Tables_in_tang |
+----------------+
| tang           |
+----------------+
1 row in set (0.00 sec)

1.5 显示表结构

mysql> desc tang;  ###查看表结构
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(6)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10) | YES  |     | NULL    |                |
| adress | varchar(20) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

2. SQL语言概述

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

3. SQL分类

3.1 DDL（数据定义语言）

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

1.使用DDL语句新建库，表

create database auth;    ##新建库
use auth; 
create table users (user_name CHAR(16) NOT NULL,user_passwd CHAR(48) DEFAULT '',PRIMARY KEY (user_name));  ###新建表

2.使用DDL语句删除库，表

2.1 删除指定的数据表

mysql> drop table tang ; ##删除表
Query OK, 0 rows affected (0.01 sec)

2.2 删除指定的数据库

mysql> drop database tang; ###删除库
Query OK, 0 rows affected (0.00 sec)

3.2 DML（数据操纵语言）

DML语局用于对表中的数据进行管理
包括操作
.insert：插入新数据

 mysql> insert into users(user_name,user_passwd) values('zhangsan',password('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)   ##在表中插入信息
select * from auth.users;
mysql> select * from auth.users;  ##查看表信息
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

 

.updata：更新原有数据

mysql> update auth.users set    user_passwd=password('1234567') where user_name='lisi';   ##更新密码
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 1
mysql> flush privileges;  ##刷新
Query OK, 0 rows affected (0.00 sec)


 

.delete：删除不需要的数据

mysql> delete from auth.users where user_name='lisi';    ##删除表里面lisi这个账户的内容
Query OK, 0 rows affected (0.00 sec)V
delete from auth.users; ## 删除表里面所有的数据
Query OK, 1 row affected (0.01 sec)

3.2 DQL（数据查询语言）

DQL是火速局查询语句，只有select
用于从出具表中查询符合条件的数据记录
查询时客不指定条件

mysql> select * from auth.user;     ##查询表中所有内容
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| lisi      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>  select user_name,user_passwd from auth.user where user_name='zhangsan';  ##指定查询张三中name和passwd信息
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

3.2 DCL（数据控制语言）

设置用户权限（用户不存在时，则新建用户

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

授权权限
mysql> grant select on auth.* to 'xiaoqi'@'localhost' identified by '123456';   ##授权xiaoqi账号读的权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;   ##刷新
Query OK, 0 rows affected (0.00 sec)

mysql> create database bdqn;
Query OK, 1 row affected (0.00 sec)
mysql>  grant all on bdqn.* to 'dbuser'@'20.0.0.12' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for 'dbuser'@'20.0.0.12';  ## 查看用户的权限
+----------------------------------------------------------+
| Grants for dbuser@20.0.0.12                              |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser'@'20.0.0.12'               |
| GRANT ALL PRIVILEGES ON "bdqn".* TO 'dbuser'@'20.0.0.12' |
+----------------------------------------------------------+

2 rows in set (0.00 sec)

撤销用户权限

mysql>  revoke select on auth.* from 'xiaoqi'@'localhost';
Query OK, 0 rows affected (0.00 sec)

4. 数据库的三大范式

1.列不可分隔
2.属性完全依赖于主键
3.属性不依赖于其他非主属性，属性直接依赖于主键

5. 数据库表高级操作

5.1 清空表

清空一个数据表就是删除这个表内的所有数据。前面的小节已经学习过DELETE
FROM语句，可以删除表内的数据，除此之外还可以使用TRUNCATE TABLE语句实现清
空表内记录。DELETE FROM语句可以使用WHERE子句对删除的结果集进行过滤选择，
这样更方便、更灵活。TRUNCATE TABLE语句是删除表中所有记录数据，没法定制，灵活
性上稍差。清空表的具体操作如下所示。
#####上面几个是往ky表中写入数据

show tables;   查看当前用户
create table tm like ky;                     通过like方式，复制ky表生成tm表
insert into tm select * from ky;         通过ky表生成tmp表内数据记录
delete from tm;                                  清空tm表中的信息
insert into tm select * from ky;       通过ky表生成tmp表内数据记录
truncate table tm;                            清空tm表中的信息  
select * from tm;                              查看tm表中所有的信息，这边显示没有
select count(*) from tm;   

5.2临时表

MySQL的临时表，顾名思义，就是临时建立的表，并不会长期存在，主要用于保存一些临时数据。临时表有个特性，
就是只在当前连接可见，当前连接下可执行增删改查等操作，当连接被关闭后，临时表就会被MysQL删除，相关的资源也会被释放。
下面创建临时表mytmp，然后插入数据，之后断开当前连接，最后重新连到MySQL查看临时是否还存在，具体操作如下

use auth;    ###进入auth数据库中
CREATE TEMPORARY TABLE `mytm23` (`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 mytm(NAME,level) values('aa',10);
#####创建mytm23临时表  
nsert into mytm23(NAME,level) values('aa',10); ##往mytm23临时表写入信息
select * from mytm23;   ##查看mytm23临时表的信息

退出数据库，再次进入，这个临时表的信息就没有了

5.3 克隆表

在MySQL的开发和维护过程中，会有原样拷贝某个数据表的需求。怎么样才能够快速、
完整的拷贝数据表呢?先来看一下CREATE TABLE new_tablename AS SELECT这个语
句，具体实现的 SQL语句如下所示。
第一种方式 like方式复制表结构，不复制数据

create table tm like ky;                    ## 通过like方式，复制ky表生成tm表
show create table tm\G                   ##查看tm表的结构
insert into tm select * from ky;         ##通过ky表生成tmp表内数据记录

方法二:通过创建表的方式克隆表
首先，使用SHOW CREATE TABLE命令来获取源表的表结构、索引等信息。
其次，复制源表结构并修改表名为目标名字，然后执行创建新表的语句。通过这步操作，
就可以获得一个和源表结构一样的克隆表了。
最后，执行INSERTINTO…SELECT语句，从源表复制数据到新表内。

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;
###改名后创建新表
insert into test select * from mytmp;   导入原表数据