三天学会MySQL（二）SQL之DDL

编程学习 · 2022/1/17 11:53:32 · 次浏览

一.SQL语言介绍

二.DDL（数据定义语言）

1.创建数据库

2.选择数据库

三.MySQL的数据类型

四.创建表与删除表

五.修改表

六.约束

一.SQL语言介绍

1.SQL语言简介

结构化查询语言(Structured Query Language) 简称 SQL( 发音：sequal['si:kw əl])，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

2.SQL 能做什么

SQL 面向数据库执行查询
SQL 可在数据库中插入新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限

3.SQL 标准

SQL 是 1986 年 10 月由美国国家标准局（ ANSI ）通过的数据库语言美国标准，接着，国际标准化组织（ ISO ）颁布了 SQL 正式国际标准。 1989 年 4 月， ISO 提出了具有完整性特征的 SQL89 标准， 1992 年 11 月又公布了 SQL92 标准，在此标准中，把数据库分为 21 三个级别：基本集、标准集和完全集。在 1999 年推出 99 版标准。最新版本为 SQL2016 版。比较有代表性的几个版本： SQL86 、 SQL92 、 SQL99 。

4.SQL语言分类

数据查询语言（DQL ： Data Query Language ）其语句，也称为 “ 数据检索语句 ” ，用以从表中获得数据，确定数据怎样在应用程序给出。关键字 SELECT 是 DQL （也是所有 SQL ）用得最多的词。

SELECT

FROM

WHERE

ORDER BY

HAVING

数据操作语言（ DML ： Data Manipulation Language ）其语句包括动词 INSERT ， UPDATE 和 DELETE 。它们分别用于添加，修改和删除表中的行。

INSERT ：添加数据

UPDATE ：更新数据

DELETE ：删除数据

数据定义语言（ DDL ： Data Definition Language ）定义数据库对象语言，其语句包括动词 CREATE 和 DROP 等。

CREATE ：创建数据库对象

ALTER ：修改数据库对象

DROP ：删除数据库对象

数据控制语言（ DCL ： Data Control Language ）它的语句通过GRANT 或 REVOKE 获得许可，确定用户对数据库对象的访问。

GRANT ：授予用户某种权限

REVOKE ：回收授予的某种权限

事务控制语言（ TCL ： Transaction Control Language ）它的语句能确保被 DML 语句影响的表的所有行及时得以更新。

COMMIT ：提交事务

ROLLBACK ：回滚事务

SAVEPOINT ：设置回滚点

注意：

数据操纵语言 DML （ insert 、 update 、 delete ）针对表中的数据；

而数据定义语言 DDL （ create 、 alter 、 drop ）针对数据库对象，比如数据库 database 、表 table 、索引 index 、视图 view 、存储过程 procedure 、触发器 trigger ；

5.SQL语言语法

1 SQL 语句不区分大小写，关键字建议大写。

2 SQL 语句可以单行或多行书写，以分号结尾。

二.DDL（数据定义语言）

1.创建数据库

（1）使用DDL语句创建与删除数据库（cmd下创建）

创建：

CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符编码 ;

示例：创建一个 test 的数据库，并查看该数据库，以该数据库的编码。

创建数据库：

create database test default character set utf8;

查看数据库：

show databases ;

查看数据库编码：

select

schema_name ,default_character_set_name

from information_schema .schemata

where schema_name = 'test' ;

删除：

使用DDL语言删除数据库

DROP DATABASE 数据库名称 ;

示例：

删除 test 数据库

drop database test;

（2）Navicat创建与删除数据库

右键下图左侧红框区域，选择新建数据库，填写数据库名称，选择字符集之后点击确定。

删除数据库，右键相应数据库，选择删除数据库即可！

2.选择数据库

在创建表时，需要先选择数据库。 USE 数据库名 ;

三.MySQL的数据类型

1.整数类型

最常用的就是tinyint和int，当表示性别或状态等可以用0/1来表示的属性时，常用tinyint，而其他的可变长的常用int。

数值类型中的长度 m 是指显示长度（不用必须指定），并不表示存储长度，只有字段指定 zerofill 时有用。

例如： int(3) ，如果实际值是 2 ，如果列指定了 zerofill ，查询结果就是 002 ，左边用 0 来填充。

2.浮点类型

必须指定两个参数！整数位有m-d位。

3.字符类型

最常用的是char，varchar，text，其中char，varchar必须指定括号里的最大长度，不同之处是char字符长度不满足最大长度n时，前面会用空格补齐；varchar字符长度不满足最大长度n时，不会用空格补齐。

char 和 varchar ：

1 char长度固定，即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。
2 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3 text不设置长度，当不知道属性的最大长度时，适合用text。

按照查询速度： char 最快， varchar 次之， text 最慢。

字符串型使用建议：

1 经常变化的字段用varchar
2 知道固定长度的用char
3 尽量用varchar
4 超过255字符的只能用varchar或者text
5 能用varchar的地方不用text

4.日期类型

当项目中时间是考虑时区的，则使用时间戳timestamp（支持时区处理），datetime不支持时区处理。

5.二进制数据(BLOB)

Byte Large Object的缩写，即字节大对象。

BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。
BLOB存储的数据只能整体读出。
TEXT可以指定字符集，BLOB不用指定字符集。

四.创建表与删除表

1.创建表

（1）使用DDL语句创建表

CREATE TABLE 表名 ( 列名类型 , 列名类型 ...... ) ;

示例：

创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。

create table employees ( employee_id int ,employee_name varchar ( 10 ) ,salary float ( 8 , 2 )) ;

查看已创建的表。

show tables ;

（2）使用Navicat创建表

点击一个数据库，点击表，点击右侧的创建表，输入各属性的规格，再点击保存或Ctrl+S，再输入表名，点击确定，就生成了一个表。

删除的话就是右键表删除即可。

2.删除表

使用 DDL语句删除表 DROP TABLE 表名 ;

示例：

删除 employees 表。

drop table employees;

五.修改表

1.修改表名

使用DDL语句修改表

ALTER TABLE 旧表名 RENAME 新表名 ;

示例一：

创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。

create table employees ( employee_id int ,employee_name varchar ( 10 ) ,salary float ( 8 , 2 )) ;

示例二：

将 employees 表名修改为 emp 。

alter table employees rename emp;

使用Navicat修改表名

鼠标右键选择重命名即（比较无脑，很多操作其实自己点点就看见了，很方便），或者选中表按F2也可重命名。

2.修改列名

使用DDL语句修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名新列名类型 ;

示例：

将 emp 表中的 employee_name 修改为 name 。

alter table emp change column employee_name name varchar ( 20 ) ;

使用Navicat修改列名

右键表，点击设计表，即可修改表的设置。

3.修改列类型

使用DDL语句修改列类型

ALTER TABLE 表名 MODIFY 列名新类型 ;

示例：

将 emp 表中的 name 的长度指定为 40 。

alter table emp modify name varchar ( 40 ) ;

使用Navicat修改列类型

还是右键表，选择设计表，修改之后保存（Ctrl+S）即可。

4.添加新列

使用DDL语句添加新列

ALTER TABLE 表名 ADD COLUMN 新列名类型 ;

示例：

在 emp 表中添加佣金列，列名为 commission_pct 。

alter table emp add column commission_pct float ( 4 , 2 ) ;

使用Navicat添加新列

还是设计表。。不过不是鼠标点击空白处，而是上下键。

5.删除指定列

使用 DDL 语句删除指定的列

ALTER TABLE 表名 DROP COLUMN 列名 ;

示例：

删除 emp 表中的 commission_pct 。

alter table emp drop column commission_pct;

使用 Navicat 删除指定的列

右键。

六.约束

1.MySQL中的约束

主键约束其实就是唯一性约束和非空约束的复合；MySQL不支持检查约束。

约束概述

数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

主键约束(Primary Key) PK

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。

主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

外键约束(Foreign Key) FK

外键约束经常和主键约束一起使用，用来确保数据的一致性。

唯一性约束(Unique)

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的。

非空约束(Not Null)

非空约束用来约束表中的字段不能为空。

检查约束(Check)

检查约束也叫用户自定义约束，是用来检查数据表中，字段值是否有效的一个手段，但目前 MySQL 数据库不支持检查约束。

2.添加主键约束 (Primary Key)

单一主键（使用较多）

使用一个列作为主键列，当该列的值有重复时，则违反唯一约束。

联合主键

使用多个列作为主键列，当多个列的值都相同时，则违反唯一约束。

修改表添加主键约束

使用 DDL 语句添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

若使用联合主键，则在括号里形式是(列名,列名,列名,....)

示例：

将 emp 表中的 employee_id 修改为主键。

alter table emp add primary key ( employee_id ) ;

主键自增长

MySQL 中的自动增长类型要求：

一个表中只能有一个列为自动增长。
自动增长的列的类型必须是整数类型。
自动增长只能添加到具备主键约束与唯一性约束的列上。
删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后再删除约束。

alter table 表名 modify 主键类型 auto_increment ;

示例：

将 emp 表中的 employee_id 主键修改为自增。

alter table emp modify employee_id int auto_increment ;

使用Navicat添加(删除)主键约束

在 不是null那一栏的后面一栏，在要添加为主键的属性后面单机即可，会出现一把小钥匙，即已经设置为主键，联合主键再在别的后面点击即可。

设置自动递增勾选即可。

删除的话就单击，取消勾选啥的就行了，没啥好说的。

3.删除主键

使用 DDL 语句删除主键

ALTER TABLE 表名 DROP PRIMARY KEY ;

注意：

删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。

示例：

删除 emp 表中的 employee_id 主键约束。

去掉自动增长：

alter table emp modify employee_id int ;

删除主键：

alter table emp drop primary key ;

4.添加外键约束(Foreign Key)

外键：允许有重复，允许有空，如果有值必须是另一个表的的主键的一个值。

修改表添加外键约束

使用DDL语句添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY ( 列名 ) REFERENCES 参照的表名 ( 参照的列名 ) ;

注：约束名一般是表名_fk，如果有多个外键约束，则可以使用表名_fk1，表名_fk2

示例一：

创建 departments 表包含 department_id 、department_name ， location_id 。

create table departments ( department_id int ,department_name varchar ( 30 ) ,location_id int ) ;

示例二：

修改 departments 表，向 department_id 列添加主键约束与自动

递增。

alter table departments add primary key ( department_id ) ;

alter table departments modify department_id int auto_increment ;

示例三：

修改 emp 表，添加 dept_id 列。

alter table emp add column dept_id int ;

示例四：

向 emp 表中的 dept_id 列添加外键约束。

alter table emp add constraint emp_fk

foreign key ( dept_id ) references

departments ( department_id ) ;

添加外键失败解决办法：将表的的引擎设置为InnoDB（支持外键）,在设计表中设置。

使用Navicat添加外键约束

设计表中在点击外键，填写即可（一些属性可选择栏，不用非得手敲！）

5.删除外键约束

使用DDL语句删除外键约束。

ALTER TABLE 表名 DROP FOREIGN KEY 约束名 ;

示例：

删除 dept_id 的外键约束。

alter table emp drop foreign key emp_fk;

使用Navicat删除外键约束

右键。

6.添加（删除）唯一性约束

查看唯一性约束在设计表中的索引一栏。

使用DDL语句添加唯一性约束。

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE ( 列名 ) ;

示例：

向 emp 表中的 name 添加唯一约束（即添加后每一个name都是唯一的，即不能有重复值）。

alter table emp add constraint emp_uk unique ( name ) ;

使用DDL语句添删除唯一性约束。

ALTER TABLE 表名 DROP KEY 约束名;

示例：

删除 name 的唯一约束。

alter table emp drop key emp_uk;

使用Navicat添加（删除）唯一性约束

添加：和添加外键差不多，只不过在索引栏添加。

删除：右键

7. 非空约束(Not Null)

使用DDL语句添加非空约束。

ALTER TABLE 表名 MODIFY 列名类型 NOT NULL ;

示例：

向 emp 表中的 salary 添加非空约束。

alter table emp modify salary float ( 8 , 2 ) not NULL ;

删除非空约束

使用 DDL 语句删除非空约束。

ALTER TABLE 表名 MODIFY 列名类型 NULL;

示例：

删除 emp 表中 salary 的非空约束。

alter table emp modify salary float ( 8 , 2 ) NULL ;

使用Navicat添加（删除）非空约束。

在不是null那一栏勾选或取消勾选即可。

创建表时添加约束

查询表中的约束信息：

SHOW KEYS FROM 表名 ;

示例：

创建 depts 表包含 department_id 该列为主键且自动增长，department_name 列不允许重复， location_id 列不允含有空值。

create table depts ( department_id int primary

key auto_increment ,department_name

varchar ( 30 ) unique ,location_id int not null ) ;

第二节:SpingBoot单元测试上一篇

下一篇prime.

elasticsearch笔记

对字段进行最细力度划分 GET _analyze {"analyzer":"ik_max_word","text":"java开发程序员" } ik_max_word和 ik_smart介绍学习过Solr或Elasticsearch的同学都知道IK分词器，它是一个针对中文的分词器。 IK分词器地址&…

三天学会MySQL（二）SQL之DDL

一.SQL语言介绍

二.DDL（数据定义语言）

1.创建数据库

2.选择数据库

三.MySQL的数据类型

四.创建表与删除表

五.修改表

六.约束

热门文章