目录
一、insert一次插入多条记录
mysql> create table t_user(
-> id int,
-> name varchar(32),
-> birth date,
-> create_time datetime
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t_user(id,name,birth,create_time)
-> values //!!!!
-> (10001,'zs','2001-3-7',now()),
-> (10002,'ls','2002-5-8',now()),
-> (10003,'ww','2004-7-7',now());
mysql> select * from t_user;
+-------+------+------------+---------------------+
| id | name | birth | create_time |
+-------+------+------------+---------------------+
| 10001 | zs | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)
二、快速创建表
语法:
create table 新表 as select ... from 旧表;
原理:
将查询出来的结果,当做一个新的表新建,实现表的快速复制。
示例:
mysql> create table t_user2 as select * from t_user;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_user;
+-------+------+------------+---------------------+
| id | name | birth | create_time |
+-------+------+------------+---------------------+
| 10001 | zs | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)
三、将查询结果插入一张表
语法:
insert into 被插入表 select ... from 插入数据表;
示例:
mysql> insert into t_user2 select * from t_user;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_user2;
+-------+------+------------+---------------------+
| id | name | birth | create_time |
+-------+------+------------+---------------------+
| 10001 | zs | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww | 2004-07-07 | 2022-10-03 09:34:12 |
| 10001 | zs | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
6 rows in set (0.00 sec)
四、删除表中的数据
删除表数据,留下表结构。
delete命令(DML语句):
特点:
- 表中的数据被删除了,但是硬盘上真实储存空间不会被释放;
- 优点:支持回滚,可以恢复数据
- 缺点:一个一个删除数据,删除效率较低
语法:
delete from 表名;
示例:
mysql> insert into t_user2 select * from t_user;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_user2;
+-------+------+------------+---------------------+
| id | name | birth | create_time |
+-------+------+------------+---------------------+
| 10001 | zs | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)
mysql> delete from t_user2;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from t_user2;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user2;
+-------+------+------------+---------------------+
| id | name | birth | create_time |
+-------+------+------------+---------------------+
| 10001 | zs | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)
truncate命令(DDL语句):
特点:
- 一般使用在确定永久删除的大表(上亿条数据)中
- 优点:物理删除,效率高,表中的数据被一次截断
- 缺点:不支持回滚,删除后无法找回
语法:
truncate table 表名;
示例:
mysql> insert into t_user2 select * from t_user;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> truncate table t_user2;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t_user2;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user2;
Empty set (0.00 sec)
五、约束
定义:
- 在创建表时,我们可以给表中的字段加上一些约束,来保证表中数据完整和有效。.
表级约束:
- 约束没有添加在列的后面,一般在多个字段联合起来进行约束时使用。
列级约束:
- 约束直接添加在列的后面。
分类:
- 非空约束:not null
- 唯一性约束:unique
- 主键约束:primary key
- 外键约束:foreign key
- 检查约束:check
1.非空约束:not null
只有列级约束,没有表级约束。
作用:
非空约束的字段不能为NULL
语法:
在字段的数据类型后加上'not null'关键字。
mysql> drop table if exists t_vip;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table t_vip(
-> id int,
-> name varchar(32) not null
-> );
Query OK, 0 rows affected (0.02 sec)
示例:
mysql> insert into t_vip(id,name) values('10001','zs');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_vip(name) values('ls');
Query OK, 1 row affected (0.01 sec)
mysql> inser into t_vip(id) values('10003');
ERROR 1064 (42000): 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 'inser into t_vip(id) values('10003')' at line 1
//报错
mysql> select * from t_vip;
+-------+------+
| id | name |
+-------+------+
| 10001 | zs |
| NULL | ls |
+-------+------+
2 rows in set (0.00 sec)
2.唯一性约束 :unique
作用:
唯一性约束的字段不能重复,但是可以为NULL。
语法:
在字段的数据类型后,加上unique关键字。
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t_vip(
-> id int,
-> name varchar(32) unique,
-> age int
-> );
Query OK, 0 rows affected (0.03 sec)
示例:
mysql> insert into t_vip(id,name,age) values
-> (001,'zs',17),
-> (002,'ls',20),
-> (003,'ww',19);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t_vip(id,name,age) values
-> (004,'zs',27);
ERROR 1062 (23000): Duplicate entry 'zs' for key 't_vip.name'
//报错,name字段的数据出现重复
mysql> insert into t_vip(id) values
-> (004),(005);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
//unique约束的字段可以为NULL
mysql> select * from t_vip;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | zs | 17 |
| 2 | ls | 20 |
| 3 | ww | 19 |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
+------+------+------+
5 rows in set (0.00 sec)
两个字段联合使用唯一性约束
语法:
在约束的字段后,加上unique(约束字段1,约束字段2.....)
mysql> create table t_vip(
-> id int,
-> name varchar(32),
-> age int,
-> unique(name,age) //name和age联合起来进行唯一性约束
-> );
Query OK, 0 rows affected (0.03 sec)
示例:
mysql> insert into t_vip(id,name,age) values
-> (001,'zs',18),
-> (002,'zs',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t_vip;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | zs | 18 |
| 2 | zs | 20 |
+------+------+------+
2 rows in set (0.00 sec)
对一个字段同时使用unique和not null关键字
注意:MySQL遇到这种情况,会自动把该约束变为主键约束(Primary Key)
示例:
mysql> create table t_vip(
-> id int,
-> name varchar(32) unique not null,
-> age int,
-> );
3.主键约束(primary key)
1.相关术语:
- 主键约束:一种约束
- 主键字段:添加了主键约束的字段
- 主键值:主键字段下的每一个值
2.主键的作用:
- 主键是一张表中,每一行记录的唯一标识,相当于身份证号。
- 任何一张表都应该有主键,没有主键,表无效。
3.特点:
- 相当于unique+not null,不能重复也不能为空。
- 一张表中,主键只能有一个(可以两个字段做一个主键构成一个复合主键)
- 主键值通常是定长的数字,建议使用int,bigint,char类型的数据作为主键,不建议用varchar
4.主键的分类:
第一种:
- 单一主键
- 复合主键
第二种:
- 自然主键:一般使用自然主键
- 业务主键:不建议使用业务主键,因为若主键与业务挂钩,业务发生变动时可能会影响到主键
5.语法:
- 列级约束:在字段名和数据类型后,加上primary key关键字
- 表级约束:在最后补上"primary key(约束字段)"
//列级约束
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t_vip(
-> id int primary key, //添加主键约束
-> name varchar(32),
-> age int
-> );
Query OK, 0 rows affected (0.03 sec)
//表级约束
mysql> create table t_vip(
-> id int,
-> name varchar(32),
-> age int,
primary key(id)
-> );
Query OK, 0 rows affected (0.03 sec)
6.auto_increment:
MySQL中的一种维护主键的机制,会从1开始自增。
使用了auto-increment的主键,在insert插入数据时可以省略字段名。
语法:
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t_vip(
-> id int primary key auto_increment, //维护机制,表示从1开始自增
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_vip(name) values //id有auto_increment维护机制,可以省略字段名
-> ('zs'),
-> ('zs'),
-> ('zs');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_vip;
+----+------+
| id | name |
+----+------+
| 1 | zs |
| 2 | zs |
| 3 | zs |
+----+------+
3 rows in set (0.00 sec)
4.外键约束(foreign key)
1.相关术语:
- 外键约束:一种约束
- 主键字段:添加了外键约束的字段
- 主键值:外键字段下的每一个值
2.作用:
让表与表之间的数据建立关联,使数据更加完整,关联性更强。
3.语法:
foreign key(外键) references 父表(被引用的字段)
示例:
请设计数据库表,描述“班级和学生”的信息。
方法一:
将班级与学生的信息放在一张表上,显示学生编号、学生姓名、学生班级编号和学生班级名称。
缺点:数据沉余,空间浪费。
方法二:
分为学生表t_student和班级表t_class两张表。学生表显示:学生编号、学生姓名、班级编号;
班级表显示:班级编号和班级名称,两张表通过班级编号的连接起来。
注意:
当t_student表中的cno没有条件约束时,可能会出现数据的失误(eg:不小心把101输入为102等情况)。为避免这种情况,保证cno的数据都是100或者101,可以通过对cno字段使用外键约束来实现。则cno是外键字段,cno字段中的每一个值都是外键值。
使用外键约束:
1.被引用来实现外键约束的表称为父表,使用外键约束的表称为子表。
在这个案例中,通过引用t_class表中的班级编号classno,对t_student表中的cno字段进行外键约束。因此,t_class是父表,t_student是子表。
注意:
父表中被引用的字段:不一定是主键,但是一定具有唯一性unique。
子表中的外键:可以为NULL。
一些顺序:
创建表时:先创建父表,再创建子表。
插入数据时:先插入父表,再插入子表。
删除数据时:先删除子表,再删除父表。
删除表时:先删除子表,再删除父表。
--创建父表t_class
mysql> create table t_class(
-> classno int primary key, //classno作为t_class的主键
-> classname varchar(255)
-> );
Query OK, 0 rows affected (0.04 sec)
--创建子表t_student
mysql> create table t_student(
-> no int primary key auto_increment, //no作为t_class的主键
-> name varchar(255),
-> cno int,
-> foreign key(cno) references t_class(classno)
-> ); //cno作为外键,cno引用父表中的classno字段
Query OK, 0 rows affected (0.03 sec)
--给父表t_class插入数据
mysql> insert into t_class(classno,classname) values
-> (101,'Guangdong,Guangzhou,Sunshine High School Class 1'),
-> (102,'Guangdong,Guangzhou,Sunshine High School Class 2');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
--给子表t_student插入数据
mysql> insert into t_student(name,cno) values
-> ('zs',101),
-> ('ls',101),
-> ('ww',101),
-> ('zl',101),
-> ('mn',102),
-> ('sg',102);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
--显示父表t_class
mysql> select * from t_class;
+---------+--------------------------------------------------+
| classno | classname |
+---------+--------------------------------------------------+
| 101 | Guangdong,Guangzhou,Sunshine High School Class 1 |
| 102 | Guangdong,Guangzhou,Sunshine High School Class 2 |
+---------+--------------------------------------------------+
2 rows in set (0.00 sec)
--显示子表
mysql> select * from t_student;
+----+------+------+
| no | name | cno |
+----+------+------+
| 1 | zs | 101 |
| 2 | ls | 101 |
| 3 | ww | 101 |
| 4 | zl | 101 |
| 5 | mn | 102 |
| 6 | sg | 102 |
+----+------+------+
6 rows in set (0.00 sec)
六、存储引擎
1.定义:
- 一个表存储/组织数据的方式
- 不同的存储引擎,存储方式不同
2.指定存储引擎和字符编码方式
表的存储引擎和字符编码方式是在建表的时候指定的,
建表的时候在最后小括号')'的后面指定存储引擎和字符编码方式。
ENGINE指定存储引擎,CHARSET指定字符编码方式。
mysql> show create table t_class;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_class | CREATE TABLE `t_class` (
`classno` int NOT NULL,
`classname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`classno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
结论:
MySQL默认的存储引擎是InnoDB,MySQL默认的字符编码方式是utf8mb4。
//创建t_product表,将字符编码方式改为gbk(可以输入中文)
mysql> create table t_product(
-> id int primary key auto_increment;
ERROR 1064 (42000): 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 '' at line 2
mysql> create table t_product(
-> id int primary key auto_increment,
-> name varchar(255)
-> )ENGINE=InnoDB DEFAULT CHARSET=gbk; --修改为gbk
Query OK, 0 rows affected (0.02 sec)
//展示创建t_product表的sql语句
mysql> show create table t_product;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_product | CREATE TABLE `t_product` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk | --修改成功
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.查看当前MySQL的版本:
命令:
mysql> select version();
命令操作:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.00 sec)
4.查看当前的MySQL支持哪些存储引擎:
命令:
mysql> show engines \G;
命令操作:
mysql> show engines \G;
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
ERROR:
No query specified
5.MySQL常用的存储引擎
MyISAM存储引擎:
他管理的表使用三个文件表示每个表:
- 格式文件 —— 存储表结构的定义(mytable.frm)
- 数据文件 —— 存储表行的内容(mytable.MYD)
- 索引文件 —— 存储表上索引(mytable.MYI)
优点:节省空间(可以被转换为压缩、只读表)
缺点:安全性低(不支持事务)
索引是一本书的目录,可以被转换为压缩、只读表来节省空间。
在一张表中,只要是主键或者加有unique约束的字段上,会自动创建索引。
InnoDB存储引擎:
- 是MySQL默认的存储引擎
- 支持事务,支持MySQL数据库崩溃后的自动恢复引擎
他管理的表有如下特征:
- 在数据库目录里以.frm格式文件表示
- 提供一组用来提供事务性活动的日志文件
- 表的内容存储在InnoDB的表空间tablespace中(表空间用来存储数据+索引)
- 用COMMIT,SAVEPOINT和ROLLBACK支持事务的处理。
- 提供全ACID兼容
- 多版本(MVCC)和行级锁定
- 支持外键及引用的完整性,包括级联删除和更新
优点:安全性很高(支持事务)
缺点:效率较低,不能节省空间(不能压缩,转换为只读表)
MEMORY存储引擎:
特点:其数据存储在内存中(一断电就消失),且行的长度固定,因此速度极快。
他管理的表有如下特征:
- 在数据库目录里以.frm格式文件表示
- 表数据与索引被存储在内存中(目的:为了查询快)
- 表级锁机制
- 不能包含TEXT或BLOB字段
优点:查询效率最高(不需要和硬盘进行交互)
缺点:安全性低(因为数据和索引都在内存当中,关机后数据就消失)