一、MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。
临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加,如果MySQL版本低于 3.23版本就无法使用MySQL的临时表
CREATE TEMPORARY TABLE table_name
使用 SHOW TABLES命令显示数据表列表时,将无法看到 table_name 表。
如果退出当前MySQL会话,再使用 SELECT命令来读取原先创建的临时表数据,会发现数据库中没有该表的存在,因为在退出时该临时表已经被销毁了。
默认情况下,当断开与数据库的连接后,临时表就会自动被销毁。当然也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
DROP TABLE table_name;
二、MySQL 复制表
完全的复制MySQL的数据表,包括表的结构,索引,默认值
(1)使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句,该语句包含了原数据表的结构,索引等。
(2)复制以下命令显示的SQL语句,修改数据表名,并执行SQL语句,通过以上命令 将完全的复制数据表结构。
(3)如果想复制表的内容,你就可以使用 INSERT INTO ... SELECT 语句来实现。
1、复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用"alter"添加,而且容易搞错。
2、只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
或
CREATE TABLE 新表 LIKE 旧表
3、复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表 SELECT * FROM 旧表
4、复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表
5、可以将表1结构复制到表2
SELECT * INTO 表2 FROM 表1 WHERE 1=2
6、可以将表1内容全部复制到表2
SELECT * INTO 表2 FROM 表1
7、show create table 旧表;
这样会将旧表的创建命令列出。我们只需要将该命令拷贝出来,更改table的名字,就可以建立一个完全一样的表
三、MySQL 元数据
查询结果信息: SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息: 包含了数据库及数据表的结构信息。
MySQL服务器信息: 包含了数据库服务器的当前状态,版本号等
命令 | 描述 |
---|---|
SELECT VERSION( ) | 服务器版本信息 |
SELECT DATABASE( ) | 当前数据库名 (或者返回空) |
SELECT USER( ) | 当前用户名 |
SHOW STATUS | 服务器状态 |
SHOW VARIABLES | 服务器配置变量 |
四、 MySQL 序列使用
MySQL 序列是一组整数:1, 2, 3, ...,由于一张数据表只能有一个字段自增主键, 如果想实现其他字段也实现自动增加,就可以使用MySQL序列来实现。
1、使用 AUTO_INCREMENT
MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义序列
在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。
2、重置序列
如果删除了数据表中的多条记录,并希望对剩下数据的AUTO_INCREMENT列进行重新排列,那么可以通过删除自增的列,然后重新添加来实现。 不过该操作要非常小心,如果在删除的同时又有新记录添加,有可能会出现数据混乱
ALTER TABLE insect DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);
3、设置序列的开始值
般情况下序列的开始值为1,但如果需要指定一个开始值100
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;
或者也可以在表创建成功后
ALTER TABLE t AUTO_INCREMENT = 100;
五、MySQL 处理重复数据
有些 MySQL 数据表中可能存在重复的记录,有些情况我们允许重复数据的存在,但有时候我们也需要删除这些重复的数据
1、防止表中出现重复数据
在 MySQL 数据表中设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性
设置表中字段 first_name,last_name 数据不能重复,可以设置双主键模式来设置数据的唯一性, 如果设置了双主键,那么那个键的默认值不能为 NULL,可设置为 NOT NULL
CREATE TABLE user
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);
如果设置了唯一索引,那么在插入重复数据时,SQL 语句将无法执行成功,并抛出错
INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据
INSERT IGNORE INTO 当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回; 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录,则先删除掉,再插入新记录
另一种设置数据的唯一性方法是添加一个 UNIQUE 索引
2、统计重复数据
SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;
以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下,查询重复的值,请执行以下操作
(1)确定哪一列包含的值可能会重复
(2)在列选择列表使用COUNT(*)列出的那些列
(3)在GROUP BY子句中列出的列
(4)HAVING子句设置重复数大于1
3、过滤重复数据
(1)在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据
SELECT DISTINCT last_name, first_name
FROM table_name;
(2)也可以使用 GROUP BY 来读取数据表中不重复的数据
SELECT last_name, first_name
FROM table_name
GROUP BY (last_name, first_name);
4、删除重复数据
删除数据表中的重复数据
CREATE TABLE tmp SELECT last_name, first_name, sex FROM user GROUP BY (last_name, first_name, sex);
DROP TABLE user;
ALTER TABLE tmp RENAME TO user;
当然也可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录
ALTER IGNORE TABLE table_name
ADD PRIMARY KEY (last_name, first_name);