MySQL_07表的相关操作

MySQL_07表的相关操作

1.创建表的语法格式(DDL)

建表属于DDL语句，DDL包括：create、drop、alter。

create table 表名(字段名1 数据类型,字段名2 数据类型,字段名3 数据类型);

我习惯于使用如下格式：

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型
);

如何取表名和字段名？

1. 表名：建议以“t_”或者“tbl_”开始，可读性强，见名知意。
2. 字段名：见名知意。

表名和字段名都属于标识符。

2.MySQL中的数据类型

• varchar(255)

  • 可变长度的字符串，会根据实际的数据长度动态分配空间。
  • 优点：比较智能，节省空间。
  • 缺点：需要动态分配空间，速度慢。
  • 当使用的是姓名等非固定长度的字段时，选varchar。

• char(255)

  • 固定长度的字符串，不管数据实际长度，分配固定长度的空间去存储数据。
  • 优点：不需要动态分配空间，速度快。
  • 缺点：使用不当可能会造成内存空间上的浪费。
  • 当使用性别字段时，因为性别是固定长度的字符串，所以选char。

• int(11)

  • 数字中的整数型，相当于java中的int。

• bigint

  • 数字中的长整数型，相当于java中的long。

• float

  • 单精度浮点型

• double

  • 双精度浮点型

• date

  • 短日期类型

• datetime

  • 长日期类型

• clob(Character Large OBject)

  • 字符大对象
  • 最多可存储4G的字符串。
  • 比如：存储一篇文章。
  • 超过255个字符的都要采用clob字符大对象来存储。

• blob(Binary Large OBject)

  • 二进制大对象
  • 专门用来存储图片、声音、视频等流媒体数据。
  • 往blob类型的字段上插入数据的时候，需要使用IO流。

3.创建一个学生表并删除

3.1创建表

create table t_student(
	no int,
    name varchar(255),
    age int(3),
    email varchar(255)
);

3.2删除表

1. drop table t_student;
   • 当这张表不存在时会报错。
2. drop table if exists t_student;
   • 如果存在这张表，则删除，不会报错。

4.insert插入数据(DML)

插入数据的语法格式：

insert into 表名(字段1,字段2,字段3...) values(值1,值1,值1...);#格式1
insert into 表名(字段1) value(值1);#格式2
insert into 表名 value(值1,值2,值3...);#格式3

4.1插入格式1

字段名和值要相对应，数量要对应相等，数据类型也要对应相等。

如：

insert into t_student(no,name,age,email) values(1,'LiHua',20,'123@168.com');
insert into t_student(email,name,age,no) values('456@qq.com','ZhangSan',22,2);

查询t_student表：

mysql> select * from t_student;
+------+----------+------+-------------+
| no   | name     | age  | email       |
+------+----------+------+-------------+
|    1 | LiHua    |   20 | 123@168.com |
|    2 | ZhangSan |   22 | 456@qq.com  |
+------+----------+------+-------------+
2 rows in set (0.00 sec)

4.2插入格式2

只插入一个数据：

insert into t_student(no) values(3);

结果：

+------+----------+------+-------------+
| no   | name     | age  | email       |
+------+----------+------+-------------+
|    1 | LiHua    |   20 | 123@168.com |
|    2 | ZhangSan |   22 | 456@qq.com  |
|    3 | NULL     | NULL | NULL        |
+------+----------+------+-------------+
3 rows in set (0.00 sec)

再次插入一个数据：

insert into t_student(name) values('LiSi');

结果：

+------+----------+------+-------------+
| no   | name     | age  | email       |
+------+----------+------+-------------+
|    1 | LiHua    |   20 | 123@168.com |
|    2 | ZhangSan |   22 | 456@qq.com  |
|    3 | NULL     | NULL | NULL        |
| NULL | LiSi     | NULL | NULL        |
+------+----------+------+-------------+
4 rows in set (0.00 sec)

注意：insert语句只要执行成功，就一定会多一条记录。

没有给其他字段指定值的话，默认值是null。

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | int(3)       | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

4.3设置默认值

现在我们重新创建一个名为t_student的表，在该表中设置age默认值为18：

drop table if exists t_student;
create table t_student(
	no int,
    name varchar(255),
    age int(3) default 18,
    email varchar(255)
);

再次查看表结构：

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
| age   | int(3)       | YES  |     | 18      |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

在表中插入一条数据：

insert into t_student(no) values(1);

查询数据：

mysql> select * from t_student;
+------+------+------+-------+
| no   | name | age  | email |
+------+------+------+-------+
|    1 | NULL |   18 | NULL  |
+------+------+------+-------+
1 row in set (0.00 sec)

可见，如果没有给age指定值，默认值为18。

4.4插入格式3

insert语句中，跟在表名后的小括号中的字段名可以省略：

insert into t_student value(2);#错误

注意：前面的字段名省略的话，就等于是全写上了，后面的值都要写上。

insert into t_student value(2,'Lisi',20,'666@163.com');

查询：

mysql> select * from t_student;
+------+------+------+-------------+
| no   | name | age  | email       |
+------+------+------+-------------+
|    1 | NULL |   18 | NULL        |
|    2 | Lisi |   20 | 666@163.com |
+------+------+------+-------------+
2 rows in set (0.00 sec)

4.5一次插入多条记录

insert into t_student(no,name,age,email) values
(3,'wangwu',22,'123@123.com'),
(4,'zhaoliu',23,'123@123.com');

查询数据：

mysql> select * from t_student;
+------+---------+------+-------------+
| no   | name    | age  | email       |
+------+---------+------+-------------+
|    1 | NULL    |   18 | NULL        |
|    2 | Lisi    |   20 | 666@163.com |
|    3 | wangwu  |   22 | 123@123.com |
|    4 | zhaoliu |   23 | 123@123.com |
+------+---------+------+-------------+
4 rows in set (0.00 sec)

4.6插入日期

4.6.1数字格式化：format

员工薪资：

mysql> select ename,sal from emp;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

加入千分位：

mysql> select ename,format(sal,'$999,999') as sal from emp;
+--------+-------+
| ename  | sal   |
+--------+-------+
| SMITH  | 800   |
| ALLEN  | 1,600 |
| WARD   | 1,250 |
| JONES  | 2,975 |
| MARTIN | 1,250 |
| BLAKE  | 2,850 |
| CLARK  | 2,450 |
| SCOTT  | 3,000 |
| KING   | 5,000 |
| TURNER | 1,500 |
| ADAMS  | 1,100 |
| JAMES  | 950   |
| FORD   | 3,000 |
| MILLER | 1,300 |
+--------+-------+
14 rows in set, 14 warnings (0.00 sec)

4.6.2str_to_date

将字符串varchar类型转换为date类型。

现创建一个t_user表：

create table t_user(
	id int,
    name varchar(255),
    birth date//生日可以用日期类型
);

日期类型的数据该如何插入呢？

mysql> insert into t_user(id,name,birth) values(1,'zhangsan','01-02-1999');
ERROR 1292 (22007): Incorrect date value: '01-02-1999' for column 'birth' at row 1

报错，原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。

此时，我们可以使用str_to_date函数进行类型转换。

str_to_date函数可以将字符串转换为date日期类型。

语法格式：

str_to_date('字符串日期','日期格式')

mysql的日期格式：

• 年：%Y
• 月：%m
• 日：%d
• 时：%h
• 分：%i
• 秒：%s

插入日期数据：

insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-02-1999','%d-%m-%Y'));

查询数据：

mysql> select * from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1999-02-01 |
+------+----------+------------+
1 row in set (0.00 sec)

str_to_date函数通常使用在insert插入数据方面，因为插入时需要一个日期类型的数据，需要通过该函数将字符串转换为date类型。

好消息：

如果插入时的日期字符串为'%Y-%m-%d'，也就是”年-月-日“的格式时，就不需要使用str_to_date函数，mysql会自动转换为日期类型。

insert into t_user(id,name,birth) values(1,'zhangsan','1999-02-01');

4.6.3date_format

如果我们想在查询时以特定日期格式展示数据，就可以使用date_format函数。

date_format函数可以将date类型转换为具有一定格式的varchar字符串类型。

使用格式：date_format(日期类型数据,'日期格式')

select id,name,date_format(birth,'%d/%m/%Y') as birth
from t_user;

结果：

+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 01/02/1999 |
+------+----------+------------+

这个函数通常用于查询日期方面。可以设置日期的展示格式。

如果不使用date_format函数，直接查询birth字段：

mysql> select id,name,birth from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1999-02-01 |
+------+----------+------------+
1 row in set (0.00 sec)

如果不使用date_format函数，直接查询，会进行默认的日期格式化，自动将数据库中的date类型转换为varchar类型。

并且采用mysql默认的日期格式：'%Y-%m-%d'

4.6.4date和datetime的区别

date是短日期：只包括年月日信息。

datetime是长日期：包括年月日、时分秒信息。

现创建t_date表：

drop table if exists t_date;
create table t_date(
    name varchar(32),
	birth date,
    nowdate datetime
);

birth是短日期

• mysql短日期默认格式：%Y-%m-%d

nowdate是长日期

• mysql长日期默认格式：%Y-%m-%d %h:%i:%s

插入数据：

insert into t_date(name,birth,nowdate) values('Zhangsan','1999-02-01','2021-07-21 20:36:07');

查看数据：

mysql> select * from t_date;
+----------+------------+---------------------+
| name     | birth      | nowdate             |
+----------+------------+---------------------+
| Zhangsan | 1999-02-01 | 2021-07-21 20:36:07 |
+----------+------------+---------------------+
1 row in set (0.00 sec)

另外，在mysql中可以通过now()函数获取系统当前时间：

并且是datetime类型的。

insert into t_date(name,birth,nowdate) values('Lisi','2000-03-08',now());

查看数据：

mysql> select * from t_date;
+----------+------------+---------------------+
| name     | birth      | nowdate             |
+----------+------------+---------------------+
| Zhangsan | 1999-02-01 | 2021-07-21 20:36:07 |
| Lisi     | 2000-03-08 | 2021-07-21 20:40:12 |
+----------+------------+---------------------+
2 rows in set (0.00 sec)

5.update修改数据(DML)

语法格式：

update 表名 set 字段名1 = 值1,字段名2 = 值2...where 条件;

注意：如果没有条件限制会更新全部数据。

查询t_student表中数据：

mysql> select * from t_student;
+------+---------+------+-------------+
| no   | name    | age  | email       |
+------+---------+------+-------------+
|    1 | NULL    |   18 | NULL        |
|    2 | Lisi    |   20 | 666@163.com |
|    3 | wangwu  |   22 | 123@123.com |
|    4 | zhaoliu |   23 | 123@123.com |
+------+---------+------+-------------+

修改第一条记录：

update t_student set name = 'Zhangsan',email = '888@qq.com' where no = 1;

重新查询：

mysql> select * from t_student;
+------+----------+------+-------------+
| no   | name     | age  | email       |
+------+----------+------+-------------+
|    1 | Zhangsan |   18 | 888@qq.com  |
|    2 | Lisi     |   20 | 666@163.com |
|    3 | wangwu   |   22 | 123@123.com |
|    4 | zhaoliu  |   23 | 123@123.com |
+------+----------+------+-------------+
4 rows in set (0.00 sec)

如果不加条件的话：

update t_student set age = 3;

查询表：

mysql> select * from t_student;
+------+----------+------+-------------+
| no   | name     | age  | email       |
+------+----------+------+-------------+
|    1 | Zhangsan |    3 | 888@qq.com  |
|    2 | Lisi     |    3 | 666@163.com |
|    3 | wangwu   |    3 | 123@123.com |
|    4 | zhaoliu  |    3 | 123@123.com |
+------+----------+------+-------------+

所有age字段的值都改变了。

6.delete删除数据(DML)

语法格式：

delete from 表名 where 条件;

注意：如果不加条件，会删除整张表的数据。

查询t_student表数据：

mysql> select * from t_student;
+------+----------+------+-------------+
| no   | name     | age  | email       |
+------+----------+------+-------------+
|    1 | Zhangsan |    3 | 888@qq.com  |
|    2 | Lisi     |    3 | 666@163.com |
|    3 | wangwu   |    3 | 123@123.com |
|    4 | zhaoliu  |    3 | 123@123.com |
+------+----------+------+-------------+

删除no = 3的记录：

delete from t_student where no = 3;

重新查询：

mysql> select * from t_student;
+------+----------+------+-------------+
| no   | name     | age  | email       |
+------+----------+------+-------------+
|    1 | Zhangsan |    3 | 888@qq.com  |
|    2 | Lisi     |    3 | 666@163.com |
|    4 | zhaoliu  |    3 | 123@123.com |
+------+----------+------+-------------+

如果不加条件：

delete from t_student;

再次查询：

mysql> select * from t_student;
Empty set (0.00 sec)

所有数据已删除。

7.表的快速复制

如何快速复制一张表？

create table emp2 as select * from emp;

查看表：

mysql> show tables;
+-----------------+
| Tables_in_tsccg |
+-----------------+
| dept            |
| emp             |
| emp2            |
| salgrade        |
| t_date          |
| t_user          |
| user            |
+-----------------+
7 rows in set (0.00 sec)

原理：

• 将查询结果当作一张新表创建
• 可以完成表的快速复制
• 表中数据也会保留
• 可以查询出一张表的部分数据进行复制

8.将查询结果插入到一张表中

查询表t_date:

mysql> select * from t_date;
+----------+------------+---------------------+
| name     | birth      | nowdate             |
+----------+------------+---------------------+
| Zhangsan | 1999-02-01 | 2021-07-21 20:36:07 |
| Lisi     | 2000-03-08 | 2021-07-21 20:40:12 |
+----------+------------+---------------------+
2 rows in set (0.00 sec)

复制t_date表：

create table t_date2 as select * from t_date;

将t_date2表中的数据全部删除：

delete from t_date2;

查询t_date2表：

mysql> select * from t_date2;
Empty set (0.00 sec)

现在查询t_date表，将查询结果插入到t_date2表中：

insert into t_date2 select * from t_date;

再次查询t_date2表：

mysql> select * from t_date2;
+----------+------------+---------------------+
| name     | birth      | nowdate             |
+----------+------------+---------------------+
| Zhangsan | 1999-02-01 | 2021-07-21 20:36:07 |
| Lisi     | 2000-03-08 | 2021-07-21 20:40:12 |
+----------+------------+---------------------+
2 rows in set (0.00 sec)

9.快速删除表中数据(truncate)【重要】

使用delete删除t_date2表中数据：

mysql> delete from t_date2;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t_date2;
Empty set (0.00 sec)

这种方式比较慢。

delete语句删除数据原理：(DML)

• 表中数据被删除了，但是数据在硬盘上的真实存储空间不会被释放。
• 优点：支持回滚，可以恢复数据。
• 缺点：删除效率较低。

truncate语句删除数据原理：(DDL)

• 这种删除效率较高，表被一次截断，物理删除。
• 优点：快速。
• 缺点：不支持回滚。

用法：

truncate table t_date2;

truncate删除的是表中的数据，表还在。

删除表：drop table 表名;

10.对表结构的增删改

对表结构的修改就是添加一个字段、删除一个字段或者修改一个字段。

对表结构的修改需要使用alter(DDL)。

我们一般不需要掌握这方面的知识，理由如下：

第一：在实际开发中，需求一旦确定，表一旦设计好之后，很少需要进行表结构的修改。而且开发进行中的时候，修改表结构成本比较高。如果中途修改表结构，对应的java代码就需要进行大量的修改。这个责任应该由设计人员来承担。

第二：由于修改表结构的操作很少，所以我们不需要掌握。如果遇到一定要修改表结构的情况时，我们可以使用工具来操作。

修改表结构的操作是不需要写入java程序中的，也不是java程序员的范畴。