本文主要是介绍MySQL笔记（二），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、表的创建和数据类型

建表的语法格式

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

create table <表名>(
	<字段名> <数据类型>,
    <字段名> <数据类型>,
    <字段名> <数据类型>
);

注意：表名和字段名都属于标识符。要见名知意！！

表名：建议以 t_ 或 tbl_ 开始，可读性强。

字段名：见名知意。

数据类型

类型后面的长度不是字符也不是字节。，就是长度。英文'a'是1个长度，中文"啊"也是1个长度。

1. varchar（最长255）

   可变长度的字符串，比较智能，节省空间。会根据实际的数据长度动态分配空间。

   优点：节省空间

   缺点：需要动态分配空间，速度慢。

2. char（最长255）

   定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据。若使用不恰当，可能会导致空间的浪费。

   优点：不需要动态分配空间，速度快。

   缺点：使用不当可能导致空间的浪费。

varchar和char应该怎么选择？

​ 性别字段选什么？因为性别是固定长度的字符串（'男'、'女'），所以选char。（char(1)）

​ 姓名字段选什么？因为姓名的长度有长有短，所以选择varchar。

3. int（最长11）

   数字中的整数型。等同于Java中的int。

4. bigint

   数字中的长整型。等同于Java中的long。

5. float

   单精度浮点型数据。

6. double

   双精度浮点型数据。

7. date

   短日期类型。

8. datetime

   长日期类型。

   clob

   字符大对象，最多可以存储 4G 的字符串。比如：存储一篇文章，存储一个说明。

   超过255个字符的都要采用CLOB字符大对象来存储。

   Character Large OBject：CLOB

9. blob

   二进制大对象。专门用来存储图片、声音、视频等流媒体数据。

   向BLOB类型的字段上插入数据的时候，例如插入一张图片、视频等，需要使用IO流才行。

   Binary Large OBject：BLOB

查看表结构

-- 查看表结构
desc <表名>

模拟表

tbl_movie 电影表（专门存储电影信息的）

创建表

创建一个学生表？

学号、姓名、年龄、性别、邮箱地址

create table tbl_student(
	sno int,
    name varchar(32),
    age int(3),
    sex char(1),
    email varchar(255)
);

二、删除表

drop table tbl_student;	-- 表必须存在，否则会报错。	
-- 或
drop table if exists tbl_student;	-- 表如果存在则删除，不存在则不执行。

三、插入数据

insert 插入语句（DML）

-- 注意：是values
insert into <表名>(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);

注意：字段名和值要一一对应。数量要对应，数据类型也要对应。

若字段名全部省略，默认是都写上，插入所有字段，值要和数据表中的字段排序对应。（不建议，可读性差）

insert into tbl_student(sno,name,age,sex,email) values(211906,'张三',20,'男','211906@qq.com');
insert into tbl_student(sno,name,age,sex,email) values(211907,'法外狂徒',20,'男','211907@qq.com');

一个问题

只插入一个字段，不插入全部？

insert into tbl_student(sno) values(211906344);

/*
	mysql> insert into tbl_student(sno) values(211906344);
	Query OK, 1 row affected (0.01 sec)

    mysql> select * from tbl_student;
    +-----------+-----------+------+------+-------------------+
    | sno       | name      | age  | sex  | email             |
    +-----------+-----------+------+------+-------------------+
    | 211906344 | NULL      | NULL | NULL | NULL              |
    +-----------+-----------+------+------+-------------------+
    2 rows in set (0.00 sec)
*/

insert语句但凡执行成功了，那么必然会多一条记录。只插入一个字段，没有给其他字段指定值的话，默认值是NULL。

给字段设置默认值

给 年龄 设置一个默认值。然后执行插入一个字段的sql语句。

drop table if exists tbl_student;
create table tbl_student(
	sno int,
    name varchar(32),
    age int(3) default 20,
    sex char(1),
    email varchar(255)
);

insert into tbl_student(sno) values(2119063);

/*
    mysql> insert into tbl_student(sno) values(2119063);
    Query OK, 1 row affected (0.01 sec)

    mysql> select * from tbl_student;
    +---------+------+------+------+-------+
    | sno     | name | age  | sex  | email |
    +---------+------+------+------+-------+
    | 2119063 | NULL |   20 | NULL | NULL  |
    +---------+------+------+------+-------+
    1 row in set (0.00 sec)
*/

当为某一字段设置默认值后，插入数据时不指定该字段的值，则会默认填充默认值，而不是NULL。

插入日期

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

date_format：将date类型转换为具有一定格式的varchar类型

str_to_date

drop table if exists tbl_user;
create table tbl_user(
	id int,
    name varchar(32),
    birth date
);

插入数据。

insert into tbl_user(id,name,birth) values(1,'张三','01-10-1990');	-- 错误！

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

怎么办？可以使用str_to_date函数进行类型抓换。

怎么转换？

语法格式：str_to_date('字符串日期','日期格式');

MySQL的日期格式：

​ %Y 年

​ %m 月

​ %d 日

​ %h 时

​ %i 分

​ %s 秒

-- 使用 str_to_date 来将varchar转换为date。
insert into tbl_user(id,name,birth) values(1,'张三',str_to_date('01-10-1990','%d-%m-%Y'));

好消息！

如果你提供的日期字符串是这个格式，str_to_date函数就不需要了！！

%Y-%m-%d，能够自动转换为日期类型date。

insert into tbl_user(id,name,birth) values(1,'张三','1990-10-01');

date_format

这个函数可以将日期类型转换成特定格式的字符串。

语法格式：

date_format(日期类型数据, '日期格式');

这个函数通常使用在日期查询方面，设置展示的日期格式。

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

/*
    mysql> select id,name,date_format(birth, '%Y/%m/%d') from tbl_user;
    +------+--------+--------------------------------+
    | id   | name   | date_format(birth, '%Y/%m/%d') |
    +------+--------+--------------------------------+
    |    1 | 张三   | 1990/10/01                     |
    |    1 | 张三   | 1990/10/01                     |
    +------+--------+--------------------------------+
    2 rows in set (0.00 sec)
*/

若不给日期格式，直接查询date类型数据的话，MySQL会按默认日期格式 %Y-%m-%d 来转换date类型数据。

所以：想以默认格式展示，这个就没啥意义。但如果想按不同形式展示日期，就有用了。

Java中的日期格式？

​ yyyy-MM-dd HH:mm:ss

date和datetime的区别

date是短日期：只包括年月日的信息。默认格式：%Y-%m-%d

datetime是长日期：包括年月日时分秒的信息。默认格式：%Y-%m-%d %h-%i-%s

drop table if exists tbl_user;
create table tbl_user(
	id int,
    name varchar(32),
    birth date,
    create_time datetime
);

insert into tbl_user(id,name,birth,create_time) values(211,'法外狂徒','2022-04-30','2022-04-30 01:17:22');

在MySQL中怎么获取系统当前时间？

​ now()函数：能够获取时分秒信息，当然也可以直接存入date类型字段。

insert into tbl_user(id,name,birth,create_time) values(211,'法外狂徒','2022-04-30',now());
/*
mysql> select * from tbl_user;
+------+--------------+------------+---------------------+
| id   | name         | birth      | create_time         |
+------+--------------+------------+---------------------+
|  211 | 法外狂徒     | 2022-04-30 | 2022-04-30 01:17:22 |
|  211 | 法外狂徒     | 2022-04-30 | 2022-04-30 01:19:14 |
+------+--------------+------------+---------------------+
2 rows in set (0.00 sec)
*/

插入多条记录

insert into tbl_user(id, name, birth, create_time)
values
	(211, '法外狂徒', '2022-04-30', now()),
	(212, '张三', '2022-04-30', now()),
	(213, '李四', '2022-04-30', now());

四、更新数据

update 更新语句（DML）

update <表名> set 字段名1=值1,字段名2=值2,字段名3=值3 where <条件>;

注意：更新语句若没有条件限制会导致所有数据全部更新。

update tbl_user set name='法外狂徒',birth='2022-04-29' where id=211;
/*
    mysql> select * from tbl_user;
    +------+--------+------------+---------------------+
    | id   | name   | birth      | create_time         |
    +------+--------+------------+---------------------+
    |  211 | 张三   | 2022-04-30 | 2022-04-30 01:17:22 |
    |  212 | 张三   | 2022-04-30 | 2022-04-30 01:19:14 |
    +------+--------+------------+---------------------+
    2 rows in set (0.00 sec)

    mysql> update tbl_user set name='法外狂徒',birth='2022-04-29' where id =211;
    Query OK, 1 row affected (0.05 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> select * from tbl_user;
    +------+--------------+------------+---------------------+
    | id   | name         | birth      | create_time         |
    +------+--------------+------------+---------------------+
    |  211 | 法外狂徒     | 2022-04-29 | 2022-04-30 01:17:22 |
    |  212 | 张三         | 2022-04-30 | 2022-04-30 01:19:14 |
    +------+--------------+------------+---------------------+
    2 rows in set (0.00 sec)
*/

五、删除数据

delete 删除语句（DML）

delete from <表名> where <条件>;

注意：没有条件的话，会导致整张表的数据全部删除。

delete from tbl_user where id=212;
/*
    mysql> select * from tbl_user;
    +------+--------------+------------+---------------------+
    | id   | name         | birth      | create_time         |
    +------+--------------+------------+---------------------+
    |  211 | 法外狂徒     | 2022-04-29 | 2022-04-30 01:17:22 |
    |  212 | 张三         | 2022-04-30 | 2022-04-30 01:19:14 |
    +------+--------------+------------+---------------------+
    2 rows in set (0.00 sec)

    mysql> delete from tbl_user where id=212;
    Query OK, 1 row affected (0.02 sec)

    mysql> select * from tbl_user;
    +------+--------------+------------+---------------------+
    | id   | name         | birth      | create_time         |
    +------+--------------+------------+---------------------+
    |  211 | 法外狂徒     | 2022-04-29 | 2022-04-30 01:17:22 |
    +------+--------------+------------+---------------------+
    1 row in set (0.00 sec)
*/

六、增删改查总结

-- 增
insert into <表名>(字段1,字段2,...) values(值1,值2,...);
-- 删
delete from <表名> where <条件>;
-- 改
update <表名> set 字段名1=值1,字段名2=值2,... where <条件>;
-- 查（基础语法，“查”是最复杂的）
select 字段名1,字段名2,... from <表名>;

insert、delete、update都是小菜，它们的语法格式都是固定的。

select是大头。因为有分组查询、连接查询、子查询...，所以select是非常重要且复杂的。

七、快速处理（了解）

快速复制表

-- 将查询结果当作一张表新建
create table tbl_student2 as select * from tbl_student;

create table tbl_student3 as select name from tbl_student where age=20;

将 (select * from tbl_student;) 的结果集当作一张表新建！

可以完成表的快速复制。表结构和表数据都复制了！！

将查询结果插入到一张已存在的表中

-- 将查询结果插入到已存在的一张表中
insert into tbl_student2 select * from tbl_student;

快速删除大表中的数据

之前有一个删除表数据的方式是：

delete from <表名>;

delete语句删除数据的原理：

​ 表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放。

​ 这种删除的缺点是：删除效率比较低，速度比较慢。

​ 这种删除的优点是：支持数据回滚，后悔了可以再恢复数据！！！

/*
	-- 需要先开启事务
 mysql> start transaction;
 Query OK, 0 rows affected (0.00 sec)

 mysql> delete from tbl_student2;
 Query OK, 2 rows affected (0.00 sec)

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

 mysql> rollback;
 Query OK, 0 rows affected (0.01 sec)

 mysql> select * from tbl_student2;
 +---------+------+------+------+-------+
 | sno     | name | age  | sex  | email |
 +---------+------+------+------+-------+
 | 2119063 | NULL |   20 | NULL | NULL  |
 | 2119063 | NULL |   20 | NULL | NULL  |
 +---------+------+------+------+-------+
 2 rows in set (0.00 sec)
*/

快速删除数据：

truncate table <表名>;

原理：这种删除效率比较高，表被一次截断，物理删除。

缺点：不支持回滚。

优点：快速。

delete属于DML语句，truncate属于DDL语句。

truncate使用场景

一个大表，有上亿条数据。

删除的时候，使用delete，也许需要1个小时才能删完！效率较低。

若使用truncate来删除表中的数据，只需要1秒。但是不可恢复，删表前请仔细询问客户！

八、对表结构的增删改

什么是对表结构的修改？

​ 添加一个字段、删除一个字段、修改一个字段。

但是！！

1. 在实际的开发中，需求一旦确定之后，表一旦设计好之后，很少会进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。修改表的结构，对应的Java代码就需要进行大量的修改。程表是比较高的。这个责任应该由设计人员来承担！
2. 由于修改表结构的操作很少，所以——可以使用工具。

-- 增加一个字段
alter table <表名> add column 字段名 类型;
-- 删除一个字段
alter table <表名> drop column 字段名 类型;
-- 修改一个字段
alter table <表名> modify column 字段名 类型;

九、约束(⭐⭐⭐⭐⭐)

什么是约束？

约束对应的英语单词是：constraint

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！

约束的作用就是为了保证：表中的数据有效！！！

常见的约束

1. 非空约束：not null
2. 唯一性约束：unique
3. 主键约束：primary key （简称PK）
4. 外键约束：foreign key （简称FK）
5. 检查约束：check （MySQL不支持，Oracle支持）

重点学习：not null、unique、primary key、foreign key

非空约束

非空约束 not null 约束的字段不能为NULL。

drop table if exists tbl_test;
create table tbl_test(
	id int,
	name varchar(255) not null
);

这样的话，插入数据时，name 字段就不能不写了，否则会报错！

唯一性约束

唯一性约束 unique 约束的字段数据不能重复，但是可以为NULL。

drop table if exists tbl_test;
create table tbl_test(
	id int unique,
	name varchar(255),
    email varchar(255)
);

这样的话，插入数据时，id 字段中的数据就不能重复了，若插入数据中的id在表中已经存在，会报错！

一个需求

name和email两个字段联合起来具有唯一性。

drop table if exists tbl_test;
create table tbl_test(
	id int,
	name varchar(255) unique,
    email varchar(255) unique
);

这样创建表是错误的，两个字段分别有一个unique约束，表示：name具有唯一性，email也具有唯一性。

正确的做法是：

drop table if exists tbl_test;
create table tbl_test(
	id int,
	name varchar(255),
    email varchar(255),
    unique(name,email)
);

这样，name和email两个字段联合起来就具有唯一性了。当name相同的时候，只要email不相同就行；email相同的时候，name要不相同。

创建表的时候：

​ 若约束直接添加在字段的后面，这是列级约束。例：name varchar(255)

​ 若约束不是添加在字段的后面，这是表级约束。例：unique(name,email)

若要多个字段联合约束，则需要使用表级约束。

非空+唯一

drop table if exists tbl_test;
create table tbl_test(
	id int,
	name varchar(255) not null unique,
    email varchar(255)
);

在MySQL中，not null 和 unique 联合使用，就成为了 primary key——主键（Oracle中不一样）

主键约束（

这篇关于MySQL笔记（二）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！