MySQL-简单的创建表，简单的增删改表中数据，约束

最新推荐文章于 2023-04-18 11:12:10 发布

我是咸鱼丶

最新推荐文章于 2023-04-18 11:12:10 发布

阅读量538

点赞数

分类专栏：学习经验文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/wdnmdnm/article/details/122432406

版权

学习经验专栏收录该内容

46 篇文章 3 订阅

订阅专栏

表的创建

1.建表的语法格式（DDL语句）

create table 表名（
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型
）；

表名建议以t_或者以tbl_开始，可读性强
字段名：见名知意
表名和字段名都属于标识符

2.关于mysql中的数据类型

varchar（255）：可变长度的字符串，可节省空间，根据实际的数据长度动态的分配空间，速度慢
char（255）：定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据，不需要动态分配空间，速度快。使用不恰当会造成空间的浪费。
int（11）：数字中的整数型，等同于java中的int
bigint：长整型，等同于java的long
float：单精度浮点型
double：双精度浮点型
date：短日期类型
datetime：长日期类型
clob：Character Large OBject ；字符大对象，最多可以存储4G的字符串，比如存储一篇文章，超过255个字符的采用CLOB大对象来存储
blob：Binary Large OBject，二级制大对象，专门用来存储图片、声音、视频等媒体流数据，往blob类型的字段上插入数据的时候，需要使用io流才行

例如：
t_movie 电影表（专门存储电影信息）

编号：no（bigint）
名字：name（varchar）
描述信息：description（clob）
上映日期：playtime（date）
时长：time（datetime）
海报：image（blob）
类型：type（char）

date和datetime两个类型的区别
date是短日期，只包括年月日信息；默认格式%Y-%m-%d
datetime是长日期，包括年月日时分秒信息；默认格式%Y-%m-%d %h:%i:%s
在mysql中如何获取当前系统时间
now()：获取到的时间带有时分秒信息，是datetime类型

3.创建一个学生表

创建表

create table t_student(
no int(11),
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);

复制表：
create table 表名 like 被复制的表名;

创建表时可以使用default来给字段指定一个默认值，不指定就是默认null

create table t_student(
no int(11),
name varchar(32),
sex char(1) default '男',
age int(3),
email varchar(255)
);
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | 男      |       |
| age   | int(3)       | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表：

drop table t_student;
如果这张表存在的话，删除
drop table if exists t_student;

3.1快速创建一张表

原理：
将一个查询结果当做一张表新建，这个可以完成表的快速复制，表创建出来，同时表中的数据也存在了。

create table emp2 as select * from emp;//as可省略

Query OK, 14 rows affected (0.06 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> select * from emp2;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

create table mytable as select empno,ename from emp where job = 'MANAGER';

Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)

4.往表中插入数据（insert）

语法格式：

insert into 表名 （字段名1，字段名2，字段名3.....）values（值1，值2，值3....）；

字段名和值要一一对应，数量要对应，数据类型要对应

insert into t_student (no,name,sex,age,email) values(1,'张三','男',18,'zhangsan@qq.com');
Query OK, 1 row affected (0.11 sec)

mysql> select * from t_student;
+------+--------+------+------+-----------------+
| no   | name   | sex  | age  | email           |
+------+--------+------+------+-----------------+
|    1 | 张三   | 男   |   18 | zhangsan@qq.com |
+------+--------+------+------+-----------------+
1 row in set (0.00 sec)
insert语句只要执行成功了，就必然会多一条记录

insert 可以只给一个字段指定值而其他字段不指定，那么其他字段的值就是null。
insert语句中的字段名可以省略吗？
可以，前面的字段名省略了就等于都写上了，所以值都要写上。并且需要和字段一一对应。

4.1insert一次插入多条记录

语法：

insert into 表名(字段名1,字段名2...) values(),(),(),();

mysql> desc t_user;
	+-------------+-------------+------+-----+---------+-------+
	| Field       | Type        | Null | Key | Default | Extra |
	+-------------+-------------+------+-----+---------+-------+
	| id          | int(11)     | YES  |     | NULL    |       |
	| name        | varchar(32) | YES  |     | NULL    |       |
	| birth       | date        | YES  |     | NULL    |       |
	| create_time | datetime    | YES  |     | NULL    |       |
	+-------------+-------------+------+-----+---------+-------+

一次可以插入多条记录：
		insert into t_user(id,name,birth,create_time) values
		(1,'zs','1980-10-11',now()), 
		(2,'lisi','1981-10-11',now()),
		(3,'wangwu','1982-10-11',now());
		
mysql> select * from t_user;
	+------+--------+------------+---------------------+
	| id   | name   | birth      | create_time         |
	+------+--------+------------+---------------------+
	|    1 | zs     | 1980-10-11 | 2020-03-19 09:37:01 |
	|    2 | lisi   | 1981-10-11 | 2020-03-19 09:37:01 |
	|    3 | wangwu | 1982-10-11 | 2020-03-19 09:37:01 |
	+------+--------+------------+---------------------+

4.2将查询结果插入到一张表当中（了解）

create table dept_bak as select * from dept;//as可省略
Query OK, 4 rows affected (0.10 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept_bak;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	+--------+------------+----------+
	
insert into dept_bak as select * from dept; //很少用！as可省略
mysql> select * from dept_bak;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	+--------+------------+----------+

4.3insert 插入日期

数字格式化：format(数字，格式)

select ename,format(sal,'$999.999') from emp;

+--------+------------------------+
| ename  | format(sal,'$999.999') |
+--------+------------------------+
| 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)

str_to_date(‘字符串日期’,‘日期格式’)：将字符串varchar类型转换为date类型，通常使用在insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。如果你提供的日期字符串正好是**%Y-%m-%d**格式，那么函数就不需要了，可以进行自动类型转换。

mysql 的日期格式：
%Y年
%m月
%d日
%h时
%i分
%s秒

insert into t_user (id,name,birth) values (1,'张三',str_to_date('2000-1-1','%d-%m-%Y'));

+------+--------+---------+
| id   | name   | birth   |
+------+--------+---------+
|    1 | 张三   | 1-1-2000 |
+------+--------+----------+

date_format（日期类型数据，‘日期格式’）：将date类型转换成具有一定格式的varchar字符串类型。通常使用在查询日期方面，设置展示日期的格式。如果存储的日期类型数据是按照默认的格式存储（%Y-%m-%d），那么查询是会自动将数据库中的date类型转换成varchar类型，并且采用的格式是默认的日期格式：’%Y-%m-%d’

select id,name,date_format(birth,'%Y/%m/%d') as birthday from t_user;
+------+--------+------------+
| id   | name   | birthday   |
+------+--------+------------+
|    1 | 张三   | 2000/1/1    |
+------+--------+------------+

5.修改update（DML）

语法格式：

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

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

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
	+------+----------+------------+---------------------+
	| id   | name     | birth      | create_time         |
	+------+----------+------------+---------------------+
	|    1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
	|    2 | jack     | 2000-10-11 | 2020-03-18 15:51:23 |
	+------+----------+------------+---------------------+

update t_user set name = 'abc';

+------+----------+------------+---------------------+
	| id   | name     | birth      | create_time         |
	+------+----------+------------+---------------------+
	|    1 | abc	  | 1990-10-01 | 2020-03-18 15:49:50 |
	|    2 | abc      | 2000-10-11 | 2020-03-18 15:51:23 |
	+------+----------+------------+---------------------+

6.删除数据 delete （DML）

语法格式？

delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 删除所有！

//删除dept_bak表中的数据
delete from dept_bak; //这种删除数据的方式比较慢。

select * from dept_bak;
Empty set (0.00 sec)

delete语句删除数据的原理？（delete属于DML语句）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放；
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！

6.1快速删除表中的数据（truncate的使用）重点需掌握

truncate语句删除数据的原理？
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。
用法：truncate table 表名; （这种操作属于DDL操作。）

如果有表非常大，甚至上亿条记录，删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。但是使用truncate之前，必须确认是否真的要删除，并明白删除之后不可恢复！truncate是删除表中的数据，表还在！

删除表操作
drop table 表名; // 这不是删除表中的数据，这是把表删除。

7.对表结构的增删改-DDL（了解）

什么是对表结构的修改？
添加一个字段，删除一个字段，修改一个字段
对表结构的修改需要使用：alter（修改）
DDL包括：create（创建） drop（删除） alter（修改）

第一：在实际的开发中，需求一旦确定之后，表一旦设计好之后，很少的进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。修改表的结构，对应的java代码就需要进行大量的修改。成本是比较高。

第二：由于修改表结构的操作很少，所以我们不需要掌握，如果有一天真的要修改表结构，可以使用工具操作！
修改表结构的操作是不需要写到java程序中的。实际上也不是java程序员的范畴。

8.约束（重点必须掌握）

8.1什么是约束？

约束对应的英语单词：constraint；在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性；约束的作用就是为了保证：表中的数据有效。

8.2约束包括哪些？

非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

8.2.1非空约束

非空约束not null约束的字段不能为NULL。not null只有列级约束，没有表级约束。

drop table if exists t_vip;

	create table t_vip(
		id int,
		name varchar(255) not null  // not null只有列级约束，没有表级约束！
	);
	insert into t_vip(id,name) values(1,'zhangsan');
	insert into t_vip(id,name) values(2,'lisi');

mysql> select * from t_vip;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)
//not null约束的字段不能为NULL
insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

8.2.2唯一性约束: unique

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

drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);
mysql> select * from t_vip;
Empty set (0.00 sec)

insert into t_vip (id,name,email) values(1,'张三','12314@qq.com');
insert into t_vip (id,name,email) values(2,'李四','87432456@qq.com');
insert into t_vip (id,name,email) values(3,'王五','2343217@qq.com');
+------+--------+-----------------+                                                       
| id   | name   | email           |
+------+--------+-----------------+
|    1 | 张三   | 12314@qq.com    |
|    2 | 李四   | 87432456@qq.com |
|    3 | 王五   | 2343217@qq.com  |
+------+--------+-----------------+
3 rows in set (0.00 sec)
insert into t_vip (id,name,email) values(4,'王五','764654@qq.com');

ERROR 1062 (23000): Duplicate entry '王五' for key 'name'//王五重复,插入失败

//唯一性约束unique约束的字段不能重复，但是可以为NULL。
insert into t_vip (id) values(5);
+------+--------+-----------------+
| id   | name   | email           |
+------+--------+-----------------+
|    1 | 张三   | 12314@qq.com    |
|    2 | 李四   | 87432456@qq.com |
|    3 | 王五   | 2343217@qq.com  |
|    5 | NULL   | NULL            |
+------+--------+-----------------+
4 rows in set (0.00 sec)

8.2.2.1两个字段联合唯一

把两个数据当做一个整体，里面只要有不一样的就不算出重复，只有完全一样的才算重复
需求：name和email两个字段联合起来具有唯一性

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');

drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255) unique,  // 约束直接添加到列后面的，叫做列级约束。
			email varchar(255) unique
		);
		
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'
这张表这样创建是不符合以上“需求”的。zhangsan和zhangsan重复了
这样创建表示：name具有唯一性，email具有唯一性。各自唯一。

怎么创建这样的表，才能符合需求呢？

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255),
	email varchar(255),
	//name和email两个字段联合起来唯一
	unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
select * from t_vip;
+------+----------+-------------------+
| id   | name     | email             |
+------+----------+-------------------+
|    1 | zhangsan | zhangsan@123.com  |
|    2 | zhangsan | zhangsan@sina.com |
+------+----------+-------------------+
2 rows in set (0.00 sec)

insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

什么时候使用表级约束？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

注意： unique( )里面只能放同一种数据类型的字段，不然会报错，unique 和 not null 可以联合使用。在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) not null unique
);
mysql> desc t_vip;
	+-------+--------------+------+-----+---------+-------+
	| Field | Type         | Null | Key | Default | Extra |
	+-------+--------------+------+-----+---------+-------+
	| id    | int(11)      | YES  |     | NULL    |       |
	| name  | varchar(255) | NO   | PRI | NULL    |       |
	+-------+--------------+------+-----+---------+-------+
	
insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'zhangsan'); //错误了：name不能重复

insert into t_vip(id) values(2); //错误了：name不能为NULL。

我是咸鱼丶

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-简单的创建表，简单的增删改表中数据，约束

表的创建1.建表的语法格式（DDL语句）create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型）；表名建议以t_或者以tbl_开始，可读性强字段名：见名知意表名和字段名都属于标识符2.关于mysql中的数据类型varchar（255）：可变长度的字符串，可节省空间，根据实际的数据长度动态的分配空间，速度慢char（255）：定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据，不需要动态分配空间，速度快。使用不恰当会造成空间的浪费。
复制链接

扫一扫