MySQL数据库的基本操作
01-创建数据库
1、创建数据库
查看数据库
show databases;
创建数据库test
create database test;
查看test数据库所有信息
show create database test\G
2、删除数据库
删除数据库
drop database test; 删除数据库时连带数据一起删除
delete database test; 删除时不删除数据
delete * from test1;
3、数据库存储引擎
mysql数据目录：/home/mysql/data
mysql的核心是存储引擎
常见存储引擎InnoDB MyISAM Memory CSV
查看mysql支持的存储引擎
show engines\G
*************************** 9. row ***************************
Engine: InnoDB 存储引擎名称
Support: DEFAULT 是否支持存储引擎
Comment: Supports transactions, row-level locking, and foreign keys 存储引擎功能
Transactions: YES
XA: YES
Savepoints: YES
1)InnoDB（事务型）
提供了提交、回滚、系统崩溃前恢复的安全性操作。
支持行级索，专为处理大数据量数据库而设计
2）MyISAM（web数据库存储不支持事务）
混合使用命令，减少碎片的产生，最大的索引数64，最大键长度1000B
使用MyISAM引擎创建的数据库，产生3个文件
frm文件存储表定义（.MYD） 索引（.MYI）
4、数据表的基本操作
1）创建数据表
按行和列的方式存储，每一行唯一一条记录，每一列代表记录中的某个字段或者是域。
create table <table_name>
(
字段1，数据类型 [约束条件]，
字段1，数据类型 [约束条件]，
..............
[表级别约束条件]
);
mysql> create table test 创建数据表test
-> (id int(11),
-> name varchar(30));
mysql> show tables; 查看数据表
+------------------+
| Tables_in_test01 |
+------------------+
| test |
+------------------+
mysql> select * from test; 查看数据表内容
mysql> desc test; 查看数据表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
主键约束(PRIMARY KEY)，数据表中一个列或者多个列，它能唯一标识表中的某一条记录
单字段主键，由一个字段组成
列级别主键约束
mysql> create table test01
-> (id int(11) PRIMARY KEY,
-> name varchar(30));
mysql> desc test01;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
表级别约束条件
mysql> create table test02 (id int(11), name varchar(30), PRIMARY KEY(id));
mysql> desc test02;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
多字段联合主键，由多个字段组成
mysql> create table test03 (id int(11), name varchar(30), PRIMARY KEY(id,name));
mysql> desc test03;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(30) | NO | PRI | | |
+-------+-------------+------+-----+---------+-------+
外键约束(FOREIGN KEY),一个表可以有一个或多个外键
1.能够在两个表之间建立数据连接，数据引用时保证数据的一致性完整性
2.定义外键后，不允许删除另一个表中具有关联关系的记录
主表：对于两个表具有关联关系的，且具有主键的表
从表：对于两个表具有关联关系的，且具有外键的表
格式
CONSTARAINT <外键名> FOREIGN KEY <字段名> REFERENCES <主表名> 主键列
mysql> CREATE TABLE test05 (id int(11) PRIMARY KEY,name varchar(30));
mysql> CREATE TABLE test06 (id int(11) PRIMARY KEY,name varchar(30),CONSTRAINT test0506 FOREIGN KEY(id) REFERENCES test05(id));
非空约束 （NOT NULL)
mysql> CREATE TABLE test04 (id int(11),name varchar(30) NOT NULL);
mysql> DESC test04;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
唯一性约束 （UNIQUE）
该列唯一 但只能出现一个空值（可以出现空值）
mysql> CREATE TABLE test07 (id int(11) UNIQUE,name varchar(30));
mysql> DESC test07;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
默认约束（DEFAULT)某列的默认值
mysql> CREATE TABLE test08 (id int(11) DEFAULT 1 PRIMARY KEY,name varchar(30));
mysql> DESC test08;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 1 | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
属性值自动增加（AUTO_INCREMENT）
mysql> CREATE TABLE test09 (id int(11) PRIMARY KEY AUTO_INCREMENT,name varchar(30));
mysql> desc test09;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
验证结果
mysql> INSERT INTO test09 (name) VALUES('chenyun');
mysql> select * from test09;
+----+---------+
| id | name |
+----+---------+
| 1 | chenyun |
| 2 | chenyun |
+----+---------+
02-数据表的结构
查看数据表结构
mysql> describe test09;
mysql> desc test09;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
Field 字段名称
Type 数据类型
Null 是否为空
Key PRI 主键 UNI UNIQUE（唯一性约束）
Default 默认值
Extra 扩展的值（设置增加的属性）
mysql> show create table test09\G
*************************** 1. row ***************************
Table: test09
Create Table: CREATE TABLE `test09` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
03-修改数据表
修改数据表（ALTER TABLE）
3.1 修改表名称（RENAME）
mysql> alter table test09 rename ceshi09;
mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| ceshi09 |
+------------------+
3.2 修改字段名（CHANGE）
mysql> alter table test09 change id newid int(11);
mysql> describe test09;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| newid | int(11) | NO | PRI | 0 | |
| name | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3.3 修改字段数据类型（MODIFY）
mysql> alter table ceshi09 modify name varchar(40);
mysql> desc ceshi09;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3.4 添加字段（ADD）
mysql> ALTER TABLE test09 ADD user int(10); （默认）
mysql> describe test09;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| newid | int(11) | NO | PRI | 0 | |
| name | varchar(40) | YES | | NULL | |
| user | int(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> ALTER TABLE test09 ADD users int(10) first; （在第一列添加）
mysql> desc test09;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| users | int(10) | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| user | int(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> ALTER TABLE test09 ADD passwd int(20) AFTER newid; （在某列后添加）
mysql> desc test09;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| users | int(10) | YES | | NULL | |
| newid | int(11) | NO | PRI | 0 | |
| passwd | int(20) | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| user | int(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3.5 删除字段
mysql> ALTER TABLE test09 DROP users;
mysql> describe test09;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| newid | int(11) | NO | PRI | 0 | |
| passwd | int(20) | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| user | int(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3.6 移动字段位置
mysql> ALTER TABLE test09 MODIFY name varchar(40) first|after;
ysql> desc test09;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(40) | YES | | NULL | |
| newid | int(11) | NO | PRI | 0 | |
| passwd | int(20) | YES | | NULL | |
| user | int(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3.7 更改存储引擎
mysql> show create table test09\G 修改前
*************************** 1. row ***************************
Table: test09
Create Table: CREATE TABLE `test09` (
`id` int(10) NOT NULL DEFAULT '0',
`name` varchar(40) DEFAULT NULL,
`passwd` int(20) DEFAULT NULL,
`user` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> ALTER TABLE test09 ENGINE=MyISAM;
mysql> show create table test09\G 修改后
*************************** 1. row ***************************
Table: test09
Create Table: CREATE TABLE `test09` (
`id` int(10) NOT NULL DEFAULT '0',
`name` varchar(40) DEFAULT NULL,
`passwd` int(20) DEFAULT NULL,
`user` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
04-删除数据表
4.1 删除表的外键
格式
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
添加外键
mysql> CREATE TABLE wj1 (id int(11) PRIMARY KEY,name varchar(20),CONSTRAINT test0101 FOREIGN KEY (id) REFERENCES test08(id));
删除外键
mysql> ALTER TABLE wj1 DROP FOREIGN KEY test0101;
4.2 删除数据表
4.2.1 删除未被关联的表
mysql> DROP TABLE test01;
mysql> DROP TABLE IF EXISTS test02;
mysql> SHOW TABLES;
+------------------+
| Tables_in_test01 |
+------------------+
| test |
| test03 |
| wj1 |
+------------------+
4.2.2 删除被关联的表
mysql> show create table test06\G
*************************** 1. row ***************************
Table: test06
Create Table: CREATE TABLE `test06` (
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `test0506` FOREIGN KEY (`id`) REFERENCES `test05` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
首先删除关联的外键
mysql> ALTER TABLE test06 DROP FOREIGN KEY test0506;
再删除数据表
mysql> drop table if exists test05;
mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| test |
| test04 |
| test06 |
+------------------+
05-MySQL数据类型
5、MySQL数据类型
1）整数类型
TINYINT 很小的整数，占用1字节
SMALLINT 小的整数，占用2字节
MEDIUMINT 中等整数，占用3字节
INT（INTEGER）普通整数，占用4字节
BIGINT 大的整数，占用8字节
TINYINT： 1字节=8位(bits) 无符号： 2的8次方减1 255 有符号： 2的7次方减1 127
SMALLINT：2字节=16位(bits） 无符号： 2的16次方减1 65535 有符号： 2的15次方减1 32767
+---------+--------------------------------------------+----------------------------+
|类型 | 有符号 | 无符号 |
+---------+--------------------------------------------+----------------------------+
|TINYINT | -128~127 | 0~255 |
|SMALLINT | -32768 ~ 32767 | 0 ~ 65535 |
|MEDIUMINT| -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT | -2147483648 ~ 2147483647 | 0 ~ 4294967295 |
|BIGINT | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 184467440703709551615 |
+---------+--------------------------------------------+----------------------------+
2）浮点数类型
FLOAT 单精度浮点数 4字节
DOUBLE 双精度浮点数 8字节
格式
create table type3 (a FLOAT(m,n), b DOUBLE(m,n)); m：精度 总位数 n：标度 小数的位数
mysql> create table type3 (a FLOAT(5,1), b DOUBLE(5,1));
mysql> INSERT INTO type3 values(5.12,5.15);
mysql> select * from type3;
+------+------+
| a | b |
+------+------+
| 5.1 | 5.2 |
+------+------+
3）定点数类型
DECIMAL
mysql> CREATE TABLE type4 ( c DECIMAL(5,1));
mysql> INSERT INTO type4 VALUES(5.124);
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'warinings' at line 1 |
+-------+------+---------------------------------------------------------------------------------------------------------
mysql> SELECT * FROM type4;
+------+
| c |
+------+
| 5.1 |
+------+
4）日期与时间类型
DATETIME YYYY-MM-DD HH:MM:SS 8字节
DATE YYYY-MM-DD 3字节
TIMESTAMP YYYY-MM-DD HH:MM:SS 4字节
TIME HH:MM:SS 3字节
YEAR YYYY 1字节 表示年 1901-2155
YEAR
mysql> CREATE TABLE type5 (y YEAR);
mysql> INSERT INTO type5 values(2010),('2010'); 不带''的是整数类型，带''的是字符串类型
mysql> SELECT * FROM type5;
+------+
| y |
+------+
| 2010 |
| 2010 |
+------+
TIME
mysql> CREATE TABLE type6 (t TIME);
mysql> INSERT INTO type6 values ('12:12:12'),('22:25:22');
mysql> select * from type6;
+----------+
| t |
+----------+
| 12:12:12 |
| 22:25:22 |
+----------+
5）文本字符串类型
CHAR 固定，不可变长的
VARCHAR 可变长字符串
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM
SET
CHAR VARCHAR
mysql> CREATE TABLE type7 (user CHAR(4),name VARCHAR(4));
mysql> INSERT INTO type7 values ('ch ','cy ');
mysql> SELECT * FROM type7;
+------+------+
| user | name |
+------+------+
| ch | cy |
+------+------+
mysql> SELECT CONCAT('(', user, ')'), concat('(', name,')') from type7;
+------------------------+-----------------------+
| CONCAT('(', user, ')') | concat('(', name,')') |
+------------------------+-----------------------+
| (ch) | (cy ) |
+------------------------+-----------------------+
06-插入数据
INSERT插入数据
1、向表中所有的字段插入数据
格式：INSERT INTO 表名 (字段1，字段2，...) VALUES (值1，值2，...）;
mysql> CREATE TABLE insert2 (id INT(10) AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, user VARCHAR(50) NOT NULL, passwd VARCHAR(50) DEFAULT '123456' NOT NULL, data FLOAT(5,2), time DATE);
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| user | varchar(50) | NO | | NULL | |
| passwd | varchar(50) | NO | | 123456 | |
| data | float(5,2) | YES | | NULL | |
| time | date | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
mysql> INSERT INTO insert2 (id,name,user,passwd,data,time) VALUES (1,'chenyun','cy','qq123',3.1314,20190828);
mysql> SELECT * FROM insert2;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | qq123 | 3.13 | 2019-08-28 |
+----+---------+------+--------+------+------------+
2、向表中某字段插入数据
mysql> INSERT INTO insert2 (name,user,data,time) VALUES ('yanglin','yl',2.37,19981027);
mysql> INSERT INTO insert2 VALUES (3,'xumao','xm','xm123',2.299,19800807);
mysql> SELECT * FROM insert2;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | qq123 | 3.13 | 2019-08-28 |
| 2 | yanglin | yl | 123456 | 2.37 | 1998-10-27 |
| 3 | xumao | xm | xm123 | 2.30 | 1980-08-07 |
+----+---------+------+--------+------+------------+
mysql> SELECT id,name,user,time FROM insert2 WHERE name = 'chenyun';
+----+---------+------+------------+
| id | name | user | time |
+----+---------+------+------------+
| 1 | chenyun | cy | 2019-08-28 |
+----+---------+------+------------+
3、 向表中某字段同时插入多条数据
mysql> INSERT INTO insert2 (name,user,time) VALUES ('zhaya','zy',19931111),('luojun','lj',19911214);
mysql> select * from insert2;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | qq123 | 3.13 | 2019-08-28 |
| 2 | yanglin | yl | 123456 | 2.37 | 1998-10-27 |
| 3 | xumao | xm | xm123 | 2.30 | 1980-08-07 |
| 4 | zhaya | zy | 123456 | NULL | 1993-11-11 |
| 5 | luojun | lj | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
4、 将查询结果插入至数据表
格式：INSERT INTO 表名 （字段1，字段2） SELECT （字段1） FROM 表2 WHERE 查询条件
查询insert2数据插入insert1
mysql> INSERT INTO insert1 (id,name,user,passwd,data,time) SELECT * FROM insert2 WHERE name = 'chenyun';
mysql> select * from insert1;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | qq123 | 3.13 | 2019-08-28 |
+----+---------+------+--------+------+------------+
07-数据表中更新与删除数据
1、UPDATE更新数据
格式：UPDATE 表名 SET 字段1 = 值1， 字段2=值2 WHERE (条件);
mysql> UPDATE update1 SET name = 'yangjun',passwd = 159369 WHERE id = 2;
mysql> SELECT * FROM update1 WHERE id = 2;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 2 | yangjun | yl | 159369 | 2.37 | 1998-10-27 |
+----+---------+------+--------+------+------------+
mysql> INSERT INTO insert1 select * from update1 WHERE id > 1;
mysql> SELECT * FROM insert1;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | qq123 | 3.13 | 2019-08-28 |
| 2 | yangjun | yl | 159369 | 2.37 | 1998-10-27 |
| 3 | xumao | xm | xm123 | 2.30 | 1980-08-07 |
| 4 | zhaya | zy | 123456 | NULL | 1993-11-11 |
| 5 | luojun | lj | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
mysql> UPDATE insert1 set passwd = '123456';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 5 Changed: 3 Warnings: 0
mysql> SELECT * FROM insert1;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | 123456 | 3.13 | 2019-08-28 |
| 2 | yangjun | yl | 123456 | 2.37 | 1998-10-27 |
| 3 | xumao | xm | 123456 | 2.30 | 1980-08-07 |
| 4 | zhaya | zy | 123456 | NULL | 1993-11-11 |
| 5 | luojun | lj | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
注：使用UPDATE更新数据时，如果后面没有加WHERE条件语句，则指定更改的该列数据则全部更改，慎重使用
2、如何删除数据表中的数据
格式： DELETE FROM 表名 WHERE 字段 = 值
mysql> SELECT * FROM update1;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | qq123 | 3.13 | 2019-08-28 |
| 2 | yangjun | yl | 159369 | 2.37 | 1998-10-27 |
| 3 | xumao | xm | xm123 | 2.30 | 1980-08-07 |
| 4 | zhaya | zy | 123456 | NULL | 1993-11-11 |
| 5 | luojun | lj | 123456 | NULL | 1991-12-14 |
+----+---------+------+--------+------+------------+
mysql> DELETE FROM update1 WHERE id = 5;
mysql> SELECT * FROM update1;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | qq123 | 3.13 | 2019-08-28 |
| 2 | yangjun | yl | 159369 | 2.37 | 1998-10-27 |
| 3 | xumao | xm | xm123 | 2.30 | 1980-08-07 |
| 4 | zhaya | zy | 123456 | NULL | 1993-11-11 |
+----+---------+------+--------+------+------------+
mysql> DELETE FROM update1 WHERE id BETWEEN 1 AND 3;
mysql> SELECT * FROM update1;
+----+-------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+-------+------+--------+------+------------+
| 4 | zhaya | zy | 123456 | NULL | 1993-11-11 |
+----+-------+------+--------+------+------------+
3、清空数据表内容
清空前
mysql> select * from update1;
+----+---------+------+--------+------+------------+
| id | name | user | passwd | data | time |
+----+---------+------+--------+------+------------+
| 1 | chenyun | cy | 123456 | 3.13 | 2019-08-28 |
| 2 | yangjun | yl | 123456 | 2.37 | 1998-10-27 |
| 3 | xumao | xm | 123456 | 2.30 | 1980-08-07 |
| 4 | zhaya | zy | 123456 | NULL | 1993-11-11 |
+----+---------+------+--------+------+------------+
mysql> truncate table update1;
清空后
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| insert1 |
| test |
| test1 |
| update1 |
+----------------+
mysql> desc update1;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| user | varchar(50) | NO | | NULL | |
| passwd | varchar(50) | NO | | 123456 | |
| data | float(5,2) | YES | | NULL | |
| time | date | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
mysql> select * from update1;
Empty set (0.00 sec)
mysql> delete from update1;
Query OK, 5 rows affected (0.29 sec)
mysql> select * from update1;
查询列数据，清空成功
4、truncate和delete
truncate 语句
格式 ：truncate table 表名
mysql> truncate table update1;
不能与where一起使用
truncate删除数据后是不可以rollback的
truncate删除数据后会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数
truncate删除数据后不写服务器log，整体删除速度快
truncate删除数据后不激活trigger(触发器)
delete 语句
格式：delete from 表名 (where 条件= 值)
mysql> delete from update1;
可以where子句一起使用
不会重置Identity字段，主键id继续增加
会写入数据log文件(主从、主备需要)
可以激活trigger(触发器)
08-MySQL索引的创建与删除
1、索引的含义
索引即存储在磁盘空间中的数据库结构，包含数据表中所有创建索引的记录。所有的存储引擎支持大于或等于16索引，索引的长度256字节以上。
BTREE：MyISAM InnoDB
HASH：MEMORY HEAP
2、索引和特点
1）创建唯一索引，可以保证数据表中的每一行数据唯一
2）加快数据库的查询速度
3）加快表与之间的连接
4）耗费时间
5）占用磁盘空间
3、索引的分类
1）普通索引：允许定义索引的列中出现重复值和空值
2）唯一索引：索引列的值可以为空，但必须唯一
组合索引：组合的字段值必须唯一
主键索引：不允许有空值
3）单列索引：一个索引只包含一个列，一个表可以有多个单列索引
4）组合索引：组合多个字段创建索引，（id，name，age）
4）全文索引：FULLTEXT全文查找，允许定义索引的列中出现插入重复值和空值。仅支持CHAR VARCHAR TEXT 类型的字段中创建
4、索引的设计要求
1）索引并非越多越好：（占用磁盘突然间、降低管理语句的性能）
2）避免对经常发生变动的数据表建立索引
3）数据量比较小无需创建索
******** 实践 *********
CREATE TABLE #在创建表的同时指定索引列
ALTER TABLE #在已存在的表创建索引
CREATE INDEX #在已存在的表添加索引
1、创建表的同时创建索引
1.1 创建普通索引
格式：CREATE TABLE 表名 （字段1，字段2） [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] (索引名) （字段名）;
mysql> CREATE TABLE test1 ( id INT(11), name VARCHAR(40), year YEAR NOT NULL, INDEX (year));
mysql> SHOW CREATE TABLE test1 \G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) DEFAULT NULL,
`name` varchar(40) DEFAULT NULL,
`year` year(4) NOT NULL,
KEY `year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> explain select * from test1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: NULL
mysql> INSERT INTO test1 VALUES (1,'chenyun',2016);
mysql> explain select * from test1 where year = 2016 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
type: ref
possible_keys: year
key: year
key_len: 1
ref: const
rows: 1
Extra: Using index condition
mysql> explain select * from test1 where name = 'chenyun' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where
1.2 创建唯一索引
mysql> create table test2 (id int(11) NOT NULL, name VARCHAR(40), time DATE,age int(11),unique index name_index (id));
mysql> insert into test2 values (1,'chenyun','20190115',28),(2,'chenxuan','20110802',22),(3,'wangxu','20111111',27);
mysql> explain select * from test2 where id = 2;
+----+-------------+-------+-------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | test2 | const | name_index | name_index | 4 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+------------+---------+-------+------+-------+
mysql> explain select * from test2 where name = 'chenyun';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1.3 单列索引
mysql> create table test3 (id int(11) AUTO_INCREMENT PRIMARY KEY,name VARCHAR(40) NOT NULL,time DATE,INDEX one_index(name(40)));
mysql> INSERT INTO test3 VALUES (1,'chenyun',20110101),(2,'chenxuan',20170404),(3,'wangxu',20181010),(4,'yangjun',19920202);
mysql> EXPLAIN SELECT * FROM test3 WHERE name = 'chenyun' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test3
type: ref
possible_keys: one_index
key: one_index
key_len: 122
ref: const
rows: 1
Extra: Using index condition
1.4 组合索引
mysql> create table test4 ( id int(10) auto_increment primary key, name varchar(40) not null, age int(11) not null, time YEAR, INDEX more_index(id,name,age));
mysql> insert into test4 values (1,'chenyun',26,1993),(2,'chenxuan',22,1997),(3,'wangxu',33,1986);
mysql> explain select * from test4 where id = 2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test4
type: const
possible_keys: PRIMARY,more_index
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1.5 全文索引
mysql> create table test4 ( id int(10) auto_increment primary key, name varchar(40) not null, age int(11) not null, time YEAR, FULLTEXT INDEX fulltext_1(name(40))) ENGINE=MyISAM;
mysql> SHOW CREATE TABLE test4 \G
*************************** 1. row ***************************
Table: test4
Create Table: CREATE TABLE `test4` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL,
`age` int(11) NOT NULL,
`time` year(4) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `fulltext_1` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
mysql> EXPLAIN SELECT * FROM test4 WHERE name='chenyun';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test4 | ALL | fulltext_1 | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
