第37次文章：数据库基本语法

鹏-程-万-里

发布于 2019-09-27 17:27:03

1.2K0

发布于 2019-09-27 17:27:03

文章被收录于专栏：Java小白成长之路Java小白成长之路

本周我们结束了最为复杂的dql语法，完成最后一个进阶9—联合查询，然后进入剩下的dml和ddl语法介绍。一起来看看吧~

进阶9：联合查询

一、含义

union：合并、联合，将多次查询结果合并成一个结果

二、语法

查询语句1

union【all】

查询语句2

union【all】

...

三、意义

1、将一条比较复杂的查询语句拆分成多条语句

2、适用于查询多个表的时候，查询的列基本是一致的

四、特点

1、要求多条查询语句的查询列数必须一致

2、要求多条查询语句的查询的各列类型、顺序最好一致

3、union去重，union all包含重复项

以上就结束了查询语言（dql）的全部介绍，下面我们对其进行一个基本总结，将所有学到的dql语法汇总起来，写出一个模板，以后需要用到哪一条直接往里面放相应的查询语句即可。具体如下：

select 查询列表

from 表1 别名连接类型 join 表2 别名 on 连接条件

where 筛选条件

group by 分组条件

having 分组后筛选条件

order by 排序条件

limit 起始索引，条目数

union 联合查询

DML语言

DML语言称为数据管理语言，主要负责对表格中的数据进行增删改操作。

一、插入

1、方式一

（1）语法：insert into 表名（字段名,...） values（值,....）；

（2）特点：

要求的类型的字段的类型要一致或兼容
字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应
假如表中有可以为null的字段，注意可以通过以下两种方式插入null值
字段名和值都省略
字段写，值使用null
字段和值的个数必须一致
段名可以省略，默认所有列

2、方式二

语法：insert into 表名 set 字段=值,字段=值,...;

3、两种方式的区别

（1）方式一支持一次性插入多行，语法如下：

insert into 表名 values(值1，值2，....),(值1，值2，....),(值1，值2，....),(值1，值2，....)

（2）方式一支持子查询，语法如下：

insert into 表名查询语句；

4、案例

我们根据两种方式向一个beauty表格中插入数据，beauty表格中的各个属性如下所示：

对于各个属性参数的插入，具体语法如下：

#1、方式一支持插入多行，方式二不支持
INSERT INTO beauty
VALUES (23,'杨幂1','女','1985-10-1','19988886666',NULL,2)
,(24,'杨幂2','女','1985-10-1','19988886666',NULL,2)
,(25,'杨幂3','女','1985-10-1','19988886666',NULL,2)
#2、方式一支持子查询，方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','5436546';
#3、方式二插入数据
INSERT INTO beauty
SET id=17,NAME='刘亦菲',phone='999'

tips：在上面的3条插入语句中，我们仅仅展示一下语法输入。在表格属性中我们看到不可以为空的属性有：id、name、phone。所以这三个属性是我们必须插入的值。

第一条语句中，我们没有填写需要添加的属性，所以默认的所有属性都需要添加进去，同时，我们使用到了多行插入。

第二条语句中，我们填写了需要插入的属性，所以我们需要对应着属性来插入相关的值，与此同时，我们使用到了子查询语句，在select语句后面对应的插入参数值。

第三条语句中，我们使用的是方式二的set方法来，此时在表名后面不用填写需要插入的属性，因为set后面的语法中，已经直接填写了对应的属性。

总体而言，方式二较为简单，但是由于只能单行插入，所以使用的较少，一般都是采取方式一的语法进行操作。

二、修改

1、修改单表的记录

update 表1 别名连接类型 join 表2 别名 on 连接条件

set 字段=值，字段=值【where 筛选条件】;

2、修改多表的记录【补充】

（1）sql92语法：update 表1 别名,表2 别名set 列=值,....where 连接条件and 筛选条件

（2）sql99语法：update 表1 别名【join type】 join 表2 别名 on 连接条件set 列=值,....where 筛选条件

3、案例

#1.修改单表中的记录
#案例：修改beauty表中姓杨的女神电话为11122233344
UPDATE beauty 
SET phone='11122233344'
WHERE NAME LIKE '杨%';
#2.修改多表的记录
#案例：修改张无忌的女朋友的手机号114
UPDATE beauty b JOIN boys bo ON b.`boyfriend_id`=bo.`id`
SET b.`phone`=114
WHERE bo.`boyName`='张无忌';

tips：在上面的代码中，我们主要展示对表中数据的修改语法。查看这段代码，我们可以将其类比为插入语法中的方式二，使用set关键字，将特定的列值修改为设定的参数值。对比单表修改和多表修改，我们可以发现，不同点就在于多表中有一个多表连接，连接语法与我们在dql语言中使用的类似。

三、删除

1、方式一

使用delete

（1）删除单表的记录

delete from 表名【where 筛选条件】【limit 条目数】

（2）级联删除【补充】

delete 别名1,别名2 from 表1 别名连接类型 join 表2 别名 on 连接条件

【where 筛选条件】

2、方式二

使用truncate

语法：truncate table 表名

3、案例

#1.单表删除
#案例：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
#2.级联删除
#案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
#方式二：truncate语句
#无法添加筛选条件，直接将整张表清空
TRUNCATE TABLE boys;

tips：对于单表删除，直接查看语句就会一目了然，我们不再赘述。主要提一下级联删除，级联删除还是属于多表删除，使用到了多表互连的语法。在对表中数据进行删除时，需要注意一点，delete后面，需要加上需要删除的表，因为语法中涉及到了多表，而我们可能只是删除其中某个表格中的数据，其他表格仅仅是用于筛选，如果需要删除所有表格中符合筛选条件的数据，那么我们就需要将每个表名放在delete关键字后面。

对于truncate语句，其默认的是将整个表格的数据全部清空，所以该语法中不需要加入筛选条件。对于两种删除语法的区别，我们在下面将会加以介绍。

4、两种方式的区别【面试题】

（1）truncate删除后，如果再插入，标识列从1开始；delete删除后，如果再插入，标识列从断点开始

（2）delete可以添加筛选条件；truncate不可以添加筛选条件

（3）truncate效率较高，delete效率较低

（4）truncate没有返回值，delete有返回值

（5）truncate是彻底删除，不能回滚，delete可以回滚

DDL语言

date define language数据定义语言，主要负责对数据库和表的结构进行修改，而不是对表中数据进行修改。

一、库的管理

1、创建库

create database 【if not exists】库名【character set 字符集】；

2、修改库

alter database 库名 character set 字符集；

3、删除库

drop database 【if exists】库名；

4、案例

#1、创建库Books
CREATE DATABASE IF NOT EXISTS books;
#2、库的修改
#更改库的字符集
ALTER DATABASE books CHARACTER SET utf8;
#3、库的删除
DROP DATABASE IF EXISTS books;

tips：在一般的工程中，我们不会对库有什么具体的修改，因为对库的修改容易导致整个数据的不稳定。数据库的命名以及字符集等等属性设置，一般在创建的初始时就需要被设置好。在以后的运维过程中基本也不会有什么变动，可能变化的主要就是数据库的字符集。所以在对库的操作中，我们主要介绍了创建和删除，以及对数据库字符集的修改。对比后面对表的管理，使用的关键字也是一样的！

二、表的管理

1、创建表

create table 【if not exists】表名(

字段名数据类型【约束】,

......

字段名数据类型【约束】

)；

2、修改表

（1）添加新列

alter table 表名 add column 列名类型【first|after 字段名】；

（2）修改列的类型

alter table 表名 modify column 旧字段名新类型【新约束】；

（3）对字段重命名

alter table 表名旧字段名 change column 新字段名数据类型

（4）删除列

alter table 表名 drop column 列名；

（5）修改表名

alter table 表名 rename 【to】新表名

3、删除表

drop table 【if exists】表名；

4、复制表

（1）仅仅复制表的结构

create table 表名1 like 表名2

（2）复制表的结构+数据

create table 表名1

select * from 表名2 【where 筛选】

5、案例

#1、表的创建
#案例：创建表Book
CREATE TABLE IF NOT EXISTS book(
  id INT,#图书编号
  bName VARCHAR(20),#图书的名称
  price DOUBLE,#价格
  authorId INT,#作者编号
  publishDate DATETIME#出版日期
);
#案例：创建表author
CREATE TABLE IF NOT EXISTS author(
  id INT,#作者id
  au_name VARCHAR(20),#作者姓名
  nation VARCHAR(20)#国籍
)

#2.表的修改
#（1）修改列名
ALTER TABLE book CHANGE COLUMN publishDate puDate DATETIME;
#（2）修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pudate TIMESTAMP;
#（3）添加列
ALTER TABLE author ADD COLUMN annual DOUBLE;
#（4）删除列
ALTER TABLE author DROP COLUMN annual;
#（5）修改表名
ALTER TABLE author RENAME book_author;

#3.表的删除
DROP TABLE IF EXISTS book_author;

#4.表的复制
INSERT INTO author
VALUES(1,'村上春树','日本'),(2,'莫言','中国'),(3,'金庸','中国'),(4,'古龙','中国');
#（1）仅仅复制表的结构
CREATE TABLE copy LIKE author;
#（2）复制表的结构+数据
CREATE TABLE copy2 SELECT * FROM author;
#（3）仅仅复制表的部分数据
CREATE TABLE copy3 SELECT * FROM author WHERE nation = '中国';
#（4）仅仅复制表的部分结构，不复制数据
CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;

tips：在上面对表的增删改中，全都是按照上述的基本语法进行操作，修改列的时候，一定要注意将列的类型重新更新一下。还有一点就是表的复制，通过上面的案例可以发现一点，表的复制，使用的是create关键字，在表的后面可以添加子查询语句，有点类似于dml语句中的插入语法。

三、数据类型

1、数值型

（1）整型

tinyint、smallint、mediumint、int、integer、bigint

特点：

都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
如果超出了范围，会报out of range异常，插入临界值
长度可以不指定，默认会有一个长度

长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型。

tips：当设置为无符号时，插入负数，将会在数据库中用0进行填充。

（2）浮点型

定点数：decimal（M,D）

浮点数：

float（M，D）

double（M，D）

特点：

M代表整数部位+小数部位的个数，D代表小数部位
如果超出范围，则报out of range 异常，并且插入临界值
M和D都可以省略，但对于定点数，M默认为10，D默认为0，如果是float和double，则会根据插入的数值的精度来决定精度。
如果精度要求较高，则优先考虑使用定点数。如：货币运算等则考虑使用。

tips：double所占字节数为8，float所占字节数为4，无其他区别。

2、字符型

char、varchar、binary、varbinary、enum、set、text、blob

char：固定长度的字符，写法为char（M），最大长度不能超过M，其中M可以省略，默认为1

varchar：可变长度的字符，写法为varchar（M），最大长度不能超过M，其中M不可以省略

tips：对于char和varchar两种类型，需要根据实际情况进行选择。当某一列确定为性别或学号等等信息时，已经可以知道其长度，此时应该选择char，可以占用更小的内存空间。

3、日期型

year年

date日期

time时间

datetime 日期+时间 8字节

timestamp 日期+时间 4字节比较容易受时区，语法模式、版本的影响，更能反映当前时区的真实时间

tips：timestamp可以根据所在时区的不同，自动的将存储的时间转换为对应时区的时间，所以，timestamp类型所反映的时间更加精准。

四、常见的约束

1、含义

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠

2、分类

（1）not null ：非空，用于保证该字段的值不能为空。比如姓名，学号等（2）default：默认，用于保证该字段有默认值。比如性别

（3）primary key：主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等

（4）unique：唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号（5）check：检查约束【mysql中不支持】。比如年龄。性别

（6）foreign key：外键。用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外加约束，用于引用主表中某列的值。比如学生表的专业编号

3、添加约束

（1）添加约束的时机

创建表时
修改表时

（2）约束的添加分类

列级约束：六大约束语法上都支持，但是外键约束没有效果
表级约束：除了非空、默认，其他的都支持

4、案例

#一、创建表时添加约束
#1、添加列级约束
/*
语法
直接在字段名和类型后面追加 约束类型即可。
只支持：默认、非空、主键、唯一
*/
CREATE TABLE stuinfo(
  id INT PRIMARY KEY,#主键
  stuName VARCHAR(20) NOT NULL,#非空
  gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
  seat INT UNIQUE,#唯一
  age INT DEFAULT 18,#默认约束
  majorId INT REFERENCES major(majorid)#外键
);
#2.添加表级约束
/*
语法：在各个字段的最下面
【constraint 约束名】 约束类型（字段名）
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
  id INT,
  stuname VARCHAR(20),
  gender CHAR(1),
  seat INT,
  age INT,
  majorid INT,
  CONSTRAINT pk PRIMARY KEY(id),#主键
  CONSTRAINT uq UNIQUE(seat),#唯一键
  CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
  CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(majorid)#外键
);

#二、修改约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名） 【外键的引用】;
*/
#1、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2、添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3、添加主键
#(1)列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#(2)表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4、添加唯一
#(1)列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#(2)表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5、添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(majorid);

#三、修改表时删除约束
#1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);
#2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
#3、删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4、删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5、删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

tips：在上面的案例中，我们将每种情况都列出了相应的案例。最主要的语法并没有特别大的改变，主要是反复修改相应的约束关键字。在列级修改和表级修改中，主要在于约束类型的限制。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-09-22，如有侵权请联系 cloudcommunity@tencent.com 删除

join

本文分享自 Java小白成长之路微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度