表的创建
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。