(一)语法格式
create table 表名(字段名1 数据类型,字段名2 数据类型...);
或
create table 表名(
字段名 1 数据类型,
字段名 2 数据类型,
字段名3 数据类型
);
说明:
① 表名:建议以t_或者tbl_开头,可读性强,见名知意。
② 字段名:见名知意。
③ 表名和字段名都属于标识符
(二)MYSQL中常见的基本数据类型
数据类型 | 说明 |
varchar | 可变长度字符串,可以实际的数据长度动态分配空间(最长255位) 优点:节省空间 缺点:需要动态的分配空间,速度慢
|
char | 定长字符串,分配固定长度去存储数据,使用不当会造成空间的浪费(最长255) 优点: 不需要动态分配空间,速度快 缺点: 使用不恰当会导致空间的浪费
|
int | 数字中的整数型,等同于java中的int(最长11位) |
bigint | 数字中的长整型,等同于java中的long |
double | 双精度浮点型整数
|
date | 短日期 类型 |
datetime | 长日期类型 |
clob | Character Large Object :Clob 字符大对象,最多可以存储4G的字符串 超过255个字符的都要采用CLOB字符大对象来进行存储 |
blob | Binary Large OBject 二进制大对象 专门用来存储图片、声音、视频等流媒体数据(通过IO流) |
enum |
接受最多64k个串组成的一个预定义的集合的某个串 |
longtext | 与text相同,但是最大长度为4GB |
mediumtext | 与text相同,但是最大长度为16K |
set | 接受最多64组成的一个预定义集合的零个或多个串 |
text | 最大长度为64k的变长文本 |
tinytext | 与text相同,但最大长度为255字节 |
实际举例:
t_move 电影表(专门用来存放电影信息)
编号 名字 描述信息 上映日期
no(bigint) name(varchar) descrpition(clob) playtime(date)
-----------------------------------------------------------------------------
10003 《战狼2》 爱国大片 2017.7.27
创建一个学生表,包括 学号、姓名、年龄、邮箱地址
mysql> create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
#创建成功
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| bjpowernode |
| dept |
| emp |
| salgrade |
| t_student |
+-----------------------+
5 rows in set (0.00 sec)
(三)操作演示
(1)创建表
create
mysql> create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
#创建成功
Query OK, 0 rows affected, 1 warning (0.04 sec)
(2)删除表
drop
格式一: drop table 表名
格式二:drop table if exists 表名(优先使用)
说明:对于格式一,如果表存在,会直接删除,但是如果不存在,就会报错
对于格式二,仅当表存在时候,将其删除,即使表不存在,也不会报错
#方式一(表存在删除)
mysql> drop table t_student;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| bjpowernode |
| dept |
| emp |
| salgrade |
+-----------------------+
4 rows in set (0.00 sec)
#方式一(表不存在时删除)
mysql> drop table t_student;
ERROR 1051 (42S02): Unknown table 'bjpowernode.t_student'
mysql> create table t_student(no int,name varchar(32),sex char(1),age int(3),email varchar(255));
Query OK, 0 rows affected, 1 warning (0.02 sec)
#方式二(表存在时删除)
mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| bjpowernode |
| dept |
| emp |
| salgrade |
+-----------------------+
4 rows in set (0.00 sec)
#方式二(表不存在时候删除)
mysql> drop table if exists t_student;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(3)插入数据
insert
格式: insert into 表名(字段名1,字段名2,字段名3 ...) values(值1,值2,值3...);
注意:字段名和值要一一对应(数量要对应、数据类型也要对应)
mysql> insert into t_student(no,name,sex,age,email) values(1,'张三','男',20,'zhang123@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+------+------+------+-----------------+
| no | name | sex | age | email |
+------+------+------+------+-----------------+
| 1 | 张三 | 男 | 20 | zhang123@qq.com |
+------+------+------+------+-----------------+
1 row in set (0.00 sec)
mysql> select * from t_student;
+------+------+------+------+-----------------+
| no | name | sex | age | email |
+------+------+------+------+-----------------+
| 1 | 张三 | 男 | 20 | zhang123@qq.com |
| 1 | NULL | NULL | NULL | NULL |
+------+------+------+------+-----------------+
2 rows in set (0.00 sec)
#查看表中的数据及其默认值(可以在创建表的时候进行指定)
mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#初始化指定默认值
mysql> create table t_student(no int,name varchar(32) default 'm',sex char(1),age int(3),email varchar(255));
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| bjpowernode |
| dept |
| emp |
| salgrade |
| t_student |
+-----------------------+
5 rows in set (0.00 sec)
mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int | YES | | NULL | |
| name | varchar(32) | YES | | m | |
| sex | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
注意:
① insert一旦执行成功,必然会多一条记录,如果为给其它字段指定值,默认值为null.
②在进行数据插入的过程中,字段名如果省略不写的话,默认就是全部,在values后要填写所有的值,不然就会报错。
mysql> insert into t_student values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
插入日期:
数字格式化:format(数字,‘格式’)
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)
str_date:将字符串varchar类型转化成date类型
使用格式:str_date('字符串日期','日期格式')
功能
将字符串转化为varchar转化为日期date类型的数据,通常使用在插入insert
方面,插入所需要的是一个日期类型,需要通过该函数来进行转化。
mysql日期格式:
%Y:年 %m: 月 %d: 日 %h: 时 %i: 分 %s: 秒
使用演示:
mysql> insert into t_user(id,name,birth)values(1,'张飞',str_to_date('01-10-1990','%d-%m-%Y'));
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+------+------------+
| id | name | birth |
+------+------+------------+
| 1 | 张飞 | 1990-10-01 |
+------+------+------------+
1 row in set (0.00 sec)
说明:如果在进行插入日期时候,填写的格式是<%Y-%m-%d>,则不需要使用str_to_date()函数进行转化,因为它会自动将该varchar字符串转化为date类型数据。mysql> insert into t_user(id,name,birth)values(2,'张三','1996-10-12');
mysql> insert into t_user(id,name,birth)values(2,'张三','1996-10-12');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+------+------------+
| id | name | birth |
+------+------+------------+
| 1 | 张飞 | 1990-10-01 |
| 2 | 张三 | 1996-10-12 |
+------+------+------------+
2 rows in set (0.00 sec)
说明:默认展示格式“ %Y-%m-%d ”
date_format:将date类型转换为具有一定格式的varchar字符串类型
使用格式:date_format(日期类型数据,’日期格式 ‘);
应用场景:查询日期方面,设置展示的日期格式(日期转化为字符串)
需求:进行查询的时候将以某个特定统一格式展示
mysql> select id,name,date_format(birth,'%m/%d/%Y') as birt from t_user;
+------+------+------------+
| id | name | birt |
+------+------+------------+
| 1 | 张飞 | 10/01/1990 |
| 2 | 张三 | 10/12/1996 |
+------+------+------------+
2 rows in set (0.00 sec)
注意:
数据库中的命名规范:
所有的标识符全部都是小写,单词和单词之间使用下划线进行衔接。
date和datetime两个类型的区分?
date是短日期,只包括年月日等基本信息。
datetime是长日期,包括年月日时分秒信息。
mysql短日期默认格式为:%Y-%m-%d
mysql长日期默认格式为: %Y-%m-%d %h:%i:%s
在mysql中,如何获取系统的当前时间呢??------>now() #带有时分秒
(4)修改数据
update(DML)
语法格式:
update 表名 set 字段名1=值1 ,字段名2=值2,字段名3=值3...where 条件;
注意:没有条件限制会导致所有的数据全部更新
mysql> update t_user set name ='Jack',birth='1999-10-21' where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_user;
+------+------+------------+
| id | name | birth |
+------+------+------------+
| 1 | 张飞 | 1990-10-01 |
| 2 | Jack | 1999-10-21 |
+------+------+------------+
2 rows in set (0.00 sec)
(5)删除数据
delete(DML)
语法格式:delete from 表名 where 条件;
原理:
delete删除数据只是将表中的数据删除了,但是这个数据在硬盘上的真是分配的存储空间并不会被释放。
优缺点:
①优点:支持回滚,可以恢复已删除数据。
②缺点:删除的效率较低。
注意:如果没有条件,整张表的数据(空间还是在的)全部会被删除
mysql> delete from t_user;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from t_user;
Empty set (0.00 sec)
mysql> insert into t_user(id,name,birth)values(2,'张三','1996-10-12');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_user(id,name,birth)values(1,'张三','1995-10-12');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+------+------------+
| id | name | birth |
+------+------+------------+
| 2 | 张三 | 1996-10-12 |
| 1 | 张三 | 1995-10-12 |
+------+------+------------+
2 rows in set (0.00 sec)
mysql> delete from t_user where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+------+------+------------+
| id | name | birth |
+------+------+------------+
| 2 | 张三 | 1996-10-12 |
+------+------+------------+
1 row in set (0.00 sec)
truncate
使用方式:
truncate table 表名;(删除表名)
基本介绍:
通过该语句进行删除,表中的数据和物理空间均会被删除。
优缺点:
优点:删除速度快
缺点: 不支持回滚(一旦删除,就不能再恢复)
说明:
如果在实际开发中使用到truncate来进行数据的删除,在进行操作之前,一定要仔细询问客户是否真的要一次删除,并且警告删除之后不可恢复。
(表的结构的修改会对应大量java代码的更改,所以一般是不会修改表的结构的)
约束(constraint)
基本介绍
在创建表的时候,通过给表中的字段加上一些约束,来保证这个表中的数据的整体性、有效性。
基本分类
① 非空约束 :not null
② 唯一性约束: unique
③ 主键约束: primary key(简称PK)
④ 外键约束: foreign key(简称FK)
⑤ 检查约束: check(mysql不支持 ,oracle支持)
具体使用
① not null
在进行表的创建的时候,在字段后面添加 not null 再添加数据时候必须填写,不存在默认值
使用演示:
#创建表
mysql> create table volunteer(id int,name varchar(32) not null);
Query OK, 0 rows affected (0.02 sec)
#插入数据
mysql> insert into volunteer(id,name)values(1,'小明');
Query OK, 1 row affected (0.01 sec)
#展示表中的数据
mysql> select * from volunteer;
+------+------+
| id | name |
+------+------+
| 1 | 小明 |
+------+------+
1 row in set (0.00 sec)
#在添加元素时候,必须要添加name
mysql> insert into volunteer(id)values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
②唯一性约束 unique
唯一性字段的数据不能够重复,但是可以为null
实际应用如下:
mysql> create table volunteer(id int,name varchar(32) unique,email varchar(32));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into volunteer(id,name,email)values(1,'小明','2771762659@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from volunteer;
+------+------+-------------------+
| id | name | email |
+------+------+-------------------+
| 1 | 小明 | 2771762659@qq.com |
+------+------+-------------------+
1 row in set (0.00 sec)
mysql> insert into volunteer(id,name,email)values(2,'小明','2771762659@qq.com');
ERROR 1062 (23000): Duplicate entry '小明' for key 'volunteer.name'
拓展:两个字段联合起来具有唯一性??
#错误写法
drop table if exists volunteer;
create table volunteer(
id int ;
name varchar(255) unique;
email varchar(255) unique;
);
#以上创建方式只能保证单个字段具有唯一性(不存在联合唯一性的关系)
#通俗来说就是:添加两个名字相同但是email的数据是不被允许的
#正确创建方法如下:
drop table if exists volunteer;
mysql> create table volunteer(id int ,name varchar(32),email varchar(32),unique(name,email));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into volunteer(id,name,email) values(1,'zhang','33445678@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into volunteer(id,name,email) values(2,'zhang','22345678@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from volunteer;
+------+-------+-----------------+
| id | name | email |
+------+-------+-----------------+
| 1 | zhang | 33445678@qq.com |
| 2 | zhang | 22345678@qq.com |
+------+-------+-----------------+
2 rows in set (0.00 sec)
列级约束
指的是约束添加在列的后面,这种约束被称为列级约束。
表级约束
约束没有添加在列的后面,这种约束被称为表级约束。
null和unique可以联合吗??
#当然是可以的喽
mysql> drop table volunteer;
Query OK, 0 rows affected (0.01 sec)
mysql> create table volunteer(id int ,
name varchar(32),
email varchar(32) not null unique
);
Query OK, 0 rows affected (0.02 sec)
mysql> desc volunteer;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| email | varchar(32) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#说明:如果一个字段同时被not null和unique约束,该字段自动变为主键字段(Primary)
mysql> insert into volunteer(id,name,email) values(3,'li','2771763434@qq.com');
Query OK, 1 row affected (0.01 sec)
#错误:email是不能够重复的
mysql> insert into volunteer(id,name,email) values(4,'li','2771763434@qq.com');
ERROR 1062 (23000): Duplicate entry '2771763434@qq.com' for key 'volunteer.email'
#错误:email是没有默认值
mysql> insert into volunteer(id,name) values(5,'li');
ERROR 1364 (HY000): Field 'email' doesn't have a default value
③主键约束
什么是主键呢??
主键值是每一行记录的唯一标识。
特征: 非空(not null)并且独一无二(unique)
注意:存在主键的表示有效的,不存在主键的表是无效的。
相关术语:
I. 主键约束: 约束的一种。
II.主键字段: 添加了主键约束的字段。
III.主键值:主键字段中的每一个值。
#列级约束
mysql> create table volunteer1(id int primary key ,name varchar(32),email varchar(32));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| bjpowernode |
| dept |
| emp |
| salgrade |
| t_student |
| t_user |
| volunteer |
| volunteer1 |
+-----------------------+
8 rows in set (0.00 sec)
mysql> insert into volunteer(id,name,email) values(1,'zhang','zhangliqiang@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into volunteer(id,name,email) values(2,'xia','xialiqiang@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from volunteer;
+------+-------+---------------------+
| id | name | email |
+------+-------+---------------------+
| 3 | li | 2771763434@qq.com |
| 2 | xia | xialiqiang@qq.com |
| 1 | zhang | zhangliqiang@qq.com |
+------+-------+---------------------+
3 rows in set (0.00 sec)
mysql> insert into volunteer1(id,name,email) values(1,'zhang','zhangliqiang@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into volunteer1(id,name,email) values(2,'xia','xialiqiang@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| bjpowernode |
| dept |
| emp |
| salgrade |
| t_student |
| t_user |
| volunteer |
| volunteer1 |
+-----------------------+
8 rows in set (0.00 sec)
mysql> select * from volunteer1;
+----+-------+---------------------+
| id | name | email |
+----+-------+---------------------+
| 1 | zhang | zhangliqiang@qq.com |
| 2 | xia | xialiqiang@qq.com |
+----+-------+---------------------+
2 rows in set (0.00 sec)
#不可以插入相同id的记录
mysql> insert into volunteer1(id,name,email) values(2,'huang','huangliqiang@qq.com');
ERROR 1062 (23000): Duplicate entry '2' for key 'volunteer1.PRIMARY'
#id没有默认值(null)---->必须在插入记录的时候进行初始化
mysql> insert into volunteer1(name,email) values('huang','huangliqiang@qq.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> drop table volunteer1;
Query OK, 0 rows affected (0.02 sec)
#表级约束 (也可以是使用复合主键-->将多个字段连接起来的一种表级约束 <不推荐使用>)
mysql> create table volunteer1(id int ,name varchar(32),email varchar(32) ,primary key(id));
Query OK, 0 rows affected (0.03 sec)
主键值存在是为了表示某一行记录的唯一身份,单一主键完全可以实现,就不需要复合主键(比较麻烦)
主键也可以进行如下分类:
业务主键和自然主键
业务主键:主键值和业务紧密联系。(如将银行卡号作为主键值)
自然主键:主键值是一个自然数,与业务无关。
在实际的应用中,运用自然主键更多一点,对于业务主键,当业务发生变动的时候,可能会影响到主键值。
#可以通过auto_increment的方式来实现主键值的自增(从1开始)
mysql> create table scholor(id int primary key auto_increment,name varchar(32));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into scholor(name)values('小张');
Query OK, 1 row affected (0.01 sec)
mysql> insert into scholor(name)values('小李');
Query OK, 1 row affected (0.01 sec)
mysql> insert into scholor(name)values('小明');
Query OK, 1 row affected (0.00 sec)
mysql> select * from scholor;
+----+------+
| id | name |
+----+------+
| 1 | 小张 |
| 2 | 小李 |
| 3 | 小明 |
+----+------+
3 rows in set (0.00 sec)
注意:
一张表,主键约束只能有一个
主值键 建议使用 “ int 、bigint、char ”等类型(定长)
⑤外键约束
用来约束特定输入内容的一种方式(控制输入的内容范围),比如如下例子中,在插入学生信息的时候,班级号只能填100或102,其它的均不可以填写。
相关术语:
外键约束:约束的一种(foreign key)
外键字段:添加了外键约束的字段。
外键值:外键字段当中的每一个值。
实际应用:
设计数据库表,描述班级和学生的表??
#删除时,先删除子表,再删除父表
mysql>drop table if exists t_student;
mysql>drop table if exists t_class;
#创建班级表(父表)
mysql>create table t_class(classno int primary key,classname varchar(255));
mysql> desc t_class;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| classno | int | NO | PRI | NULL | |
| classname | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> drop table t_student;
Query OK, 0 rows affected (0.02 sec)
#创建子表
mysql> create table t_student(no int primary key auto_increment,name varchar(255),cno int,foreign key(cno) references t_class(classno));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t_class(classno,classname) values('100','北京大学第一附中');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_class(classno,classname) values('102','北京大学第一附中');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_student(name,cno) values('jack',100);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_student(name,cno) values('lucy',100);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_student(name,cno) values('lilei',100);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_student(name,cno) values('lihua',102);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_student(name,cno) values('xiaoming',102);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+----+----------+------+
| no | name | cno |
+----+----------+------+
| 1 | jack | 100 |
| 2 | lucy | 100 |
| 3 | lilei | 100 |
| 5 | lihua | 102 |
| 6 | xiaoming | 102 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> select * from t_class;
+---------+------------------+
| classno | classname |
+---------+------------------+
| 100 | 北京大学第一附中 |
| 102 | 北京大学第一附中 |
+---------+------------------+
2 rows in set (0.00 sec)
注意:
t_class是父类 t_student是子类
I . 删除表的顺序: 先删除子表,后删除父表。
II. 创建表的顺序: 先创建父表,后创建子表。
III. 删除数据的顺序:先删除子表,后删除父表。
IV.插入数据的顺序 :先插入父表,后插入子表。
thinking ??
子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗
答:不一定,只要保证其具有unique约束(引用前提)就可以(外键值可以是null)。
拓展:
SQL脚本文件(***)的运行方式和重要意义?
通过SQL脚本文件,我们可以一次执行大量的sql语句(批量执行)
如何执行呢?
source 物理地址