表(table)
表是数据库的基本组成单元,所有的数据都以表格的形式组织,目的是可读性强。
一个表包括行和列:
- 行:被称为数据/记录(data)
- 列:被称为字段(column)
学号(int) | 姓名(varchar) | 年龄(int) |
---|---|---|
001 | 张三 | 20 |
002 | 李四 | 21 |
每一个字段应该包括哪些属性?
字段名、数据类型、相关的约束
表的创建
关于MySQL当中字段的数据类型,以下只说常见的
int 整数型(java中的int)
bigint 长整形(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长度字符串(StringBuffer/StringBuilder)
date 日期类型(对应java中的java.sql.Date类型)
BLOB 二进制大对象(存储图片、视频等流媒体信息) Binary Large OBject
CLOB 字符大对象(存储较大文本,比如,可以存储4G的字符串)Character Large OBject
...
char 与 varchar
在实际开发中,当某个字段中的数据长度不可发生改变的时候,是定长的,例如:性别、生日等都是采用char。
当一个字段的数据长度不确定,例如:简介、姓名等都采用varchar。
BLOB 和CLOB 类型的使用
电影表:t_movie
id(int) name(varchar) playtime(date/ char) haibao(BLOB) history(CLOB)
------------------------------------------------------------------------------------------------
1 蜘蛛侠
2
3
------------------------------------------------------------------------------------------------
注意:表名在数据库当中一般建议以:t_或者tbl_开始
创建表
建表语句的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
...
);
创建学生表:
-
学生信息包括:学号、姓名、性别、班级编号、生日
学号:bigint
姓名:varchar
性别:cahr
班级编号:int
生日:char
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
- 查看创建表的语句
show creae table 表名;
- 查看表中的数据
select * from 表名;
插入数据
语法格式:
insert into 表名(字段名1,字字段名2,字段名3,...) values(值1,值2,值3,...)
要求:字段的数量和值的数量相同,并且数据类型要对应相同。
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1999-10-12');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1999-10-12 |
+------+----------+------+------------+------------+
1 row in set (0.00 sec)
insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban','1999-11-11',2);
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1999-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1999-11-11 |
+------+----------+------+------------+------------+
2 rows in set (0.00 sec)
insert into t_student(name) values('wangwu');//除name字段之外,剩下的所有字段自动插入NULL
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1999-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1999-11-11 |
| NULL | wangwu | NULL | NULL | NULL |
+------+----------+------+------------+------------+
3 rows in set (0.00 sec)
insert的其他写法
insert into t_student values(2,'jack','0','gaosan2ban','1998-10-23');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 2 | jack | 0 | gaosan2ban | 1998-10-23 |
+------+----------+------+------------+------------+
2 rows in set (0.00 sec)
注意:这里字段可以省略不写,但是对后面的字段的顺序和数量有要求,要一样,不能随意改动
//多行插入
insert into t_student
(no,name,sex,classno,birth)
values
(3,'rose','0','gaosan2ban','1998-10-19'),
(4,'abo','1','gaosan1ban','1997-10-1');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 2 | jack | 0 | gaosan2ban | 1998-10-23 |
| 3 | rose | 0 | gaosan2ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
4 rows in set (0.00 sec)
注意:当一条insert语句执行成功之后,表格当中必然会多一行记录,即使多的这一行记录当中某些字段是NULL,后期也没有办法再执行insert语句插入数据了,只能使用update进行更新。
设定默认值
drop table if exists t_student;//当这个表存在的话,删除
create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1,//设定性别默认值为1
classno varchar(255),
birth char(10)
);
insert into t_student(name) values('zhangsan');
mysql> select * from t_student;
+------+----------+------+---------+-------+
| no | name | sex | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1 | NULL | NULL |
+------+----------+------+---------+-------+
1 row in set (0.00 sec)
表的复制
语法格式:
create table 表名 as select语句;
//将查询结果当做表创建出来
create table t_student1 as select * from t_student;
mysql> select * from t_student1;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 2 | jack | 0 | gaosan2ban | 1998-10-23 |
| 3 | rose | 0 | gaosan2ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
4 rows in set (0.00 sec)
//将查询结果插入到一张表
insert into dept1 select * from dept;
mysql> select * from t_student1;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 2 | jack | 0 | gaosan2ban | 1998-10-23 |
| 3 | rose | 0 | gaosan2ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
| NULL | zhangsan | 1 | NULL | NULL |
| 2 | jack | 0 | gaosan2ban | 1998-10-23 |
| 3 | rose | 0 | gaosan2ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
8 rows in set (0.00 sec)
修改数据
语法格式
update 表名 set 字段名1=值1,字段名2=值2...where 条件;
注意:没有条件的话整张表数据全部更新。
案例:将classno为gaosan2ban的修改为gaosan1ban,将sex修改为1
update t_student1 set classno = 'goasan1ban',sex = '1' where classno = 'gaosan2ban';
mysql> select * from t_student1;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 2 | jack | 1 | goasan1ban | 1998-10-23 |
| 3 | rose | 1 | goasan1ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
| NULL | zhangsan | 1 | NULL | NULL |
| 2 | jack | 1 | goasan1ban | 1998-10-23 |
| 3 | rose | 1 | goasan1ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
8 rows in set (0.00 sec)
//将name为'zhangsan'的no改为1,classno改为'gaosan1ban'
update t_student1 set no = 1,classno = 'gaosan1ban' where name = 'zhangsan';
mysql> select * from t_student1;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | NULL |
| 2 | jack | 1 | goasan1ban | 1998-10-23 |
| 3 | rose | 1 | goasan1ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
| 1 | zhangsan | 1 | gaosan1ban | NULL |
| 2 | jack | 1 | goasan1ban | 1998-10-23 |
| 3 | rose | 1 | goasan1ban | 1998-10-19 |
| 4 | abo | 1 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
8 rows in set (0.00 sec)
//将sex = 1 改为 sex = 0
update t_student set sex = '0' where sex = '1';
mysql> select * from t_student1;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 0 | gaosan1ban | NULL |
| 2 | jack | 0 | goasan1ban | 1998-10-23 |
| 3 | rose | 0 | goasan1ban | 1998-10-19 |
| 4 | abo | 0 | gaosan1ban | 1997-10-1 |
| 1 | zhangsan | 0 | gaosan1ban | NULL |
| 2 | jack | 0 | goasan1ban | 1998-10-23 |
| 3 | rose | 0 | goasan1ban | 1998-10-19 |
| 4 | abo | 0 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
8 rows in set (0.00 sec)
更新所有记录
//将no的数据全部改为78,sex全部改为1
update t_student1 set no = 78,sex = '1';
mysql> select * from t_student1;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 78 | zhangsan | 1 | gaosan1ban | NULL |
| 78 | jack | 1 | goasan1ban | 1998-10-23 |
| 78 | rose | 1 | goasan1ban | 1998-10-19 |
| 78 | abo | 1 | gaosan1ban | 1997-10-1 |
| 78 | zhangsan | 1 | gaosan1ban | NULL |
| 78 | jack | 1 | goasan1ban | 1998-10-23 |
| 78 | rose | 1 | goasan1ban | 1998-10-19 |
| 78 | abo | 1 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
8 rows in set (0.00 sec)
关于表结构的修改
- 可以使用工具完成即可,因为在实际开发中表一旦设计好之后,对表结构的修改是很少的,修改表结构就是对之前的设计进行了否定,即使需要修改表结构,我们也可以直接使用工具操作。
- 修改表结构的语句不会出现在java代码当中。出现在java代码当中的sql语句包括:insert、delete、select(这些都是表中的数据操作。)
增删改查有一个术语:CRUD操作
Create(增)、Retrieve(检索)、Update(修改)、Delete(删除)
删除数据
语法格式
delete from 表名 where 条件;
注意:没有条件的话全部删除
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 0 | gaosan1ban | NULL |
| 2 | jack | 0 | gaosan2ban | 1998-10-23 |
| 3 | rose | 0 | gaosan2ban | 1998-10-19 |
| 4 | abo | 0 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
4 rows in set (0.00 sec)
//删除no为2的记录
delete from t_student where no = 2;
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 0 | gaosan1ban | NULL |
| 3 | rose | 0 | gaosan2ban | 1998-10-19 |
| 4 | abo | 0 | gaosan1ban | 1997-10-1 |
+------+----------+------+------------+------------+
3 rows in set (0.00 sec)
//删除t_student1的所有记录
delete from t_student1;
mysql> select * from t_student1;
Empty set (0.00 sec)
怎么删除大表?(重点)
truncate table 表名;
//表被截断,不可回滚,永久丢失。