MySQL数据库管理(一)---数据表高级操作

文章目录


一、清空表

####两种方法####
##第一种##
delect from 表名

#DELETE清空表后,返回的结果内有删除的记录条目; 
#DELETE 工作时是一行一行的删除记录数据的;如果表中有自增长字段,使用DELETE FROM 删除
#所有记录后,再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。

##第二种##
truncate table 表名

#TRUNCATE清空表后,没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立,
#因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE 清空表内数据后,
#ID会从1开始重新记录

####两种方法的区别就是:两者的新值初始ID不同,并且delect可以回滚
##下面实验一下两种发发的区别##
例:
#先创建一个新表
mysql> create table dlh (id int(4) primary key auto_increment,
    -> name varchar(10) not null,
    -> cardid int (18),
    -> hobby varchar(50));


mysql> show tables;		//查看表
+----------------+
| Tables_in_zone |
+----------------+
| dlh            |
| dsj            |
+----------------+
2 rows in set (0.00 sec)

#插入两条数据
mysql> insert into dlh (name,cardid,hobby) values('wangwu',2424552,'打游戏');
mysql> insert into dlh (name,cardid,hobby) values('zhangsan',2424552,'打游戏');
#查看数据
mysql> select * from dlh;
+----+----------+---------+-----------+
| id | name     | cardid  | hobby     |
+----+----------+---------+-----------+
|  1 | wangwu   | 2424552 | 打游戏    |
|  2 | zhangsan | 2424552 | 打游戏    |
+----+----------+---------+-----------+
2 rows in set (0.00 sec)


######此时用第一种方法清空表#######
mysql> delete from dlh;
Query OK, 2 rows affected (0.00 sec)
#查看数据已经没了
mysql> select * from dlh;
Empty set (0.00 sec)

#再次插入一个新的数据
mysql> insert into dlh (name,cardid,hobby) values('lisi',2424224,'打游戏');
Query OK, 1 row affected (0.00 sec)
#此时查看数据
mysql> select * from dlh;
+----+------+---------+-----------+
| id | name | cardid  | hobby     |
+----+------+---------+-----------+
|  3 | lisi | 2424224 | 打游戏    |
+----+------+---------+-----------+
1 row in set (0.00 sec)
#发现数据初始id是从3开始

######此时用第二种方法清空表#######
mysql> truncate table dlh;
#查看数据已经被清空
mysql> select * from dlh;
Empty set (0.00 sec)

#再次插入一个新的数据
mysql> insert into dlh (name,cardid,hobby) values('lisi',2425252,'打游戏');
mysql> select * from dlh;
+----+------+---------+-----------+
| id | name | cardid  | hobby     |
+----+------+---------+-----------+
|  1 | lisi | 2425252 | 打游戏    |
+----+------+---------+-----------+
1 row in set (0.00 sec)
#发现数据初始id是从1开始


##########小结#############
truncate table table_name
1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快

delete from table_name
1)属于DML
2)可回滚(可恢复)
3)可带where
4)表结构在,表内容要看where执行的情况
5)册删除速度慢,需要逐行删除

#####所以一般在生产环境中都是用delect,而不是truncate

二、创建临时表

  • 建立临时表,用于保存一些临时数据,不会长期存在
  • 创建完成后,使用show tables命令是看不见的,
  • 一旦退出数据库就会被自动销毁
  • 如果在退出连接之前,也可以执行增删改查等操作,比如使用 drop table等语句手动直接删除临时表
  • 注:无法创建外键(关联的外键表)
######创建过程######
sql写入 ——> 数据表中 ——> 先把数据保存在内存中 ——> 写入到磁盘 insert into info ——>
 info这张表,会先复制一份表数据到内存里面,给我们进行修改 ——> 敲完回车之后,确定提交了,才会写入数据表中
 ——> 再保存在磁盘里面

他只会保存在内存中,在数据库退出连接之前的所有操作,都是在内存中进行的,不会保存在磁盘里面,退出连接后,临时表会释放掉

########例#########
#创建临时表
mysql> create temporary table linshi (
    -> 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.01 sec)
#插入一个新的数据
mysql> insert into linshi values (1,'zhangsan',123456,'running');
Query OK, 1 row affected (0.00 sec)

#使用show tables却看不见刚才创建的临时表
mysql> show tables;
+----------------+
| Tables_in_zone |
+----------------+
| dlh            |
| dsj            |
+----------------+
2 rows in set (0.00 sec)

#使用sleect 却可以看见
mysql> select * from linshi;
+------+----------+--------+---------+
| id   | name     | cardid | hobby   |
+------+----------+--------+---------+
| 0001 | zhangsan | 123456 | running |
+------+----------+--------+---------+
1 row in set (0.00 sec)

###退出数据库之后,就看不到临时表了####
#退出数据库
mysql> exit 
Bye
#再重登陆数据库
mysql -uroot -p123456
#此时查看已经没有 linshi 这张临时表了
mysql> select * from linshi;
ERROR 1146 (42S02): Table 'zone.linshi' doesn't exist
  • 创建临时表的字段的含义
int(4) zerofill:表示若数值不满4位数,则前面用"0"填充,例0001

auto_increment:表示此字段为自增长字段,即每条记录自动递增1,默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且
添加失败也会自动递增一次

unique key:表示此字段唯一键约束,此字段数据不可以重复:一张表中只能有一个主键,但是一张表中可以有多个唯一键

not null:表示此字段不允许为NULL

三、克隆表

  • 将数据表的数据记录生成到新的表中

  • 克隆表有两种方法
    like 方法
    create table 方法

  1. like方法
#格式
create table 复制的表名 like 被复制的表名
#例
mysql> create table xsj like dsj;
#查看
mysql> show tables;
+----------------+
| Tables_in_zone |
+----------------+
| dlh            |
| dsj            |
| xsj            |
+----------------+
3 rows in set (0.00 sec)

#此时查看xsj这章张表是没有任何数据的
mysql> select * from xsj;
Empty set (0.00 sec)

#但是表结构是一模一样的
mysql> desc xsj;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | char(10)     | NO   |     | NULL    |       |
| score  | decimal(5,0) | YES  |     | NULL    |       |
| passwd | char(48)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#必须手动把被复制的表(dsj)数据,插入到被复制的表(xsj)里
mysql> insert into xsj select * from dsj;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from xsj;
+----+------+-------+---------+
| id | name | score | passwd  |
+----+------+-------+---------+
|  1 | lz   |    80 | 12345   |
|  2 | gl   |    81 | 12345   |
|  3 | cpw  |    82 | 12345   |
|  4 | zc   |    84 | 23112   |
|  5 | jzy  |    85 | 536464  |
|  6 | xf   |    86 | 4537856 |
+----+------+-------+---------+
6 rows in set (0.00 sec)

  1. create table 方法
#先查看被复制的表的完整结构,根据此结构创建名字不同结构相同的表,再导入数据
mysql> show create table dsj\G;
*************************** 1. row ***************************
       Table: dsj
Create Table: CREATE TABLE "dsj" (
  "id" int(11) NOT NULL,
  "name" char(10) NOT NULL,
  "score" decimal(5,0) DEFAULT NULL,
  "passwd" char(48) DEFAULT '',
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

#克隆
mysql> create table lsj (select * from dsj);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_zone |
+----------------+
| dlh            |
| dsj            |
| lsj            |
| xsj            |
+----------------+
4 rows in set (0.00 sec)

mysql> select * from lsj;
+----+------+-------+---------+
| id | name | score | passwd  |
+----+------+-------+---------+
|  1 | lz   |    80 | 12345   |
|  2 | gl   |    81 | 12345   |
|  3 | cpw  |    82 | 12345   |
|  4 | zc   |    84 | 23112   |
|  5 | jzy  |    85 | 536464  |
|  6 | xf   |    86 | 4537856 |
+----+------+-------+---------+
6 rows in set (0.00 sec)


上一篇:MySQL高级SQL语句


下一篇:[剑指 Offer 10- I. 斐波那契数列]