数据定义语言(DDL)
1、概念
英文叫做 Data Definition Language,也就是数据定义语言,它用来定义我们的数据库对象,可以创建,删除和修改数据库和表结构
2、SQL语句的语法规范和基本使用
-
语法规范:
- 不区分大小写,但是建议关键字大写,表名和字段名就小写。
- 在终端(cmd)操作时,每条语句结尾都需要以分号结尾,在可视化工具的查询中不要求。
- 在sql中,表名和字段名是不可以用关键字命名,如果有需要,需要用````符号标识。
-
SQL语句中的注释:
-
单行注释:
单行注释一般使用
--
加空格,或者#
-- 这是单行注释 #这也是单行注释
-
多行注释
多行注释一般使用
/* */
/* 这是多行注释... */
-
-
可以在终端或者可视化工具的查询中操作
- 进入数据库:mysql -u root -p
- 退出数据库:exit
- 连接其他端口的mySql:mysql -h localhost -p3306 -uroot -p
- 查看当前所有的数据库:show databases
- 切换使用的数据库:use
数据库名
- 查看当前数据库的表:show tables
- 查看其他数据库表:show tables from
数据库名
- 查看当前表结构:desc
表名
- 查看当前表的所有数据:select * from
表名
- 向指定表插入一条数据:insert into
表名
values(插入的数据1),(插入的数据2) - 导入sql文件:source 文件路径+文件名
3、数据库的管理操作
-
3.1、创建数据库
-
语法:CREATE DATABASE [IF NOT EXISTS]
数据库名
CREATE DATABASE IF NOT EXISTS `school`; -- if not exitst 可以加也可以不加,判断数据库是否存在
-
-
3.2、修改数据库的名字
-
语法:
RENAME DATABASE oldName TO newName; -
注意 :这个是5.1.7到5.1.23版本可以用的,一般不建议使用,毕竟数据库内存存放数据,如果随意更改可能会导致数据的丢失)
-
所以修改数据库的名字一般会利用 MySQL 更改表名的方法来批量把旧库的所有表依次遍历,改名为新库的表
RENAME DATABASE `school` TO `student`; -- 修改school数据库的名字为student
-
-
3.3、修改数据库的字符集
-
语法:ALTER DATABASE
表名
CHARACTER SET需要修改成的字符集
;ALTER DATABASE `school` CHARACTER SET utf8; -- 修改学school据库的字符集为utf-8
-
4、表的管理操作
-
4.1、创建第一张表
-
语法:CREATE TABLE
表名
(字段名
字段属性
,字段名2
字段属性
)CREATE TABLE `student`( s_number INT, -- 学生学号 s_name VARCHAR(20), -- 学生姓名 s_age INT, -- 学生年龄 s_gender VARCHAR(10) -- 学生性别 );
-
创建完成后可以使用
desc student;
来查看表的结构
-
-
4.2、修改表的名字
-
语法:ALTER TABLE
旧表名
RENAME TO新表名
;-- 将学生表的名字改为teacher教师表 ALTER TABLE `student` RENAME TO `teacher`; -- 将教师表的名字改为student学生表 ALTER TABLE `teacher` RENAME TO `student`;
-
-
4.3、删除一张表
-
语法:DROP TABLE [IF EXISTS]
表名
;-- 如果存在student学生表,那么删除 DROP TABLE IF EXISTS `student`;
-
注意:一般为了防止出错,都会加上是否存在的判断
-
-
4.4、修改表的字段名
-
语法:ALTER TABLE
表名
CHANGE COLUMN原来的字段名
新字段名
该字段的数据类型
;-- 将学生表中的s_number字段修改成了s_id,此字段的数据类型是int ALTER TABLE `student` CHANGE COLUMN s_number s_id INT;
-
-
4.5、修改表的一个字段的数据类型
-
语法:ALTER TABLE
表名
MODIFY字段名
需要修改成的数据类型
;-- 将学生表中的s_age的属性由建表时的int类型修改成了varchar类型 ALTER TABLE `student` MODIFY s_age VARCHAR(10);
-
-
4.6、给表中添加一个字段
-
语法:ALTER TABLE
表名
ADD COLUMN新字段名
新字段的属性
;-- 为学生表添加一个新的字段 s_adderss 属性为varchar ALTER TABLE `student` ADD COLUMN s_address VARCHAR(50);
-
-
4.7、删除表中的指定字段
-
语法:ALTER TABLE
表名
DROP COLUMN需要删除的字段名
;-- 删除学生表中的字段 s_address ALTER TABLE `student` DROP COLUMN s_address;
-
5、MySql中常见的数据类型
-
5.1、数值型
-
整型
-
数值整型可以设置符号位,默认是有符号位==(有负数),在字段类型后面加上
unsigned
约束后则设置为无符号位(无负数)== -
在数值整型的括号里可以指定当前类型的最大值
类型 大小 范围(有符号位) 范围(无符号位) 描述 tinyint(size) 1字节 -128 ~ 127 0 ~ 255 非常小的数 smallint(size) 2字节 -32 768 ~ 32 767 0 ~ 65536 短整型 mediumint(size) 3字节 -2^24-1 ~ 2^24-1 0 ~ 2^24 中等长度的整型 integer(size) 4字节 -2^32-1 ~ 2^32-1 0 ~ 2^32 整型(常用int) bigint(size) 8字节 -2^64-1 ~ 2^64-1 0 ~ 2^64 长整型
-
-
-
5.2、字符型
-
较短的字符文本类型
-
M:最多输入的字符长度
-
区别:char是给定多少M,就会在内存中占用多少,属于固定长度,而varchar是给定多少M,先开辟开,使用后如果有多余的空间则会返回给内存,属于可变长度
类型 范围 描述 区别 char(M) M的范围0-255 M可以省略,默认为1 固定长度的字符 varchar(M) M的范围0-65535 M不可以省略 可变长度的字符
-
-
较长的字符文本类型
-
Blob有4种类型:tinyblob、Blob、mediumBlob和longBlob。它们可容纳值的最大长度不同。
-
Text也有4种类型:tinyText、Text、mediumText和longText。这些类型同Blob类型一样.
类型 描述 区别 text 长文本数据类型 用来保存字符数据类型 blob 二进制形式的长文本数据 用来保存二进制数据类型
-
-
特殊的字符类型
-
相同点:被enum或者set定义的字段,输入的数据只能是该字段定义过的数据
-
不同点:enum只能选取一个数据,类似于html中的radio单选按钮
set可以选取多个数据,类似于html中的checkbox多选框
类型 描述 enum(‘数据1’,‘数据2’,…) 使用枚举类型定义,该字段只能插入当前枚举类型定义的数据 set(‘数据1’,‘数据2’,…) 和枚举类型类似,区别是set类型可以取多值,而enum只能取单值 CREATE TABLE `student` ( `id` int, `name` varchar(20), `gender` enum('男','女'), `hobby` set('篮球','足球','羽毛球') ); -- 由enum定义的性别,只能选择男或女 -- 由set定义的爱好类,可以选择多项 insert into `student` values(1,'小明','男','篮球,足球'); insert into `student` values(2,'小红','女','羽毛球,篮球');
-
-
-
5.3、日期型
类型 大小 最大值 最小值 格式 date 3字节 9999-12-31 1000-01-01 YYYY-MM-DD year 1字节 2155 1901 YYYY time 3字节 838:59:59 -838:59:59 HH:MM:SS datetime 8字节 9999-12-31 23:59:59 1000-01-01 00:00:00 YYYY-MM-DD HH:MM:SS timestamp 4字节 2038年的某一个时刻 1970年某一个时刻 YYYY-MM-DD HH:MM:SS -
5.4、二进制类型
-
存储二进制数据的,可以存储一些文件
类型 描述 binary(M) 字节数为M,允许长度为0~M的定长二进制字符串 varbinary(M) 允许长度0~M的可变长度二进制字符串 bit(M) M位二进制数,M最大值为64 tinyblob 可变长度的二进制数据,最大值255字节 blob 可变长度的二进制数据,最大值2^(16-1)字节 mediumblob 可变长度的二进制数据,最大值2^(24-1)字节 longblob 可变长度的二进制数据,最大值2^(32-1)字节
-
6、MySql中的约束
-
6.1、约束
-
什么是约束
给以一定的条件,约束你,就是一种限制,用来限制表中的数据的,可以保证表中的数据的准确性和可靠性
-
六大主要约束的分类
primary key 主键 用于保证该字段的唯一性,并且非空,一张表中只能有一个主键,比如身份证 not null 非空 用于约束当前字段的值不可以为空 unique 唯一 用于保证该字段的值的唯一性,但是可以为空,比如电话 default 默认 用于保证该字段必须有默认值,比如性别 check 检查 用于检查插入数据时,是否是规定的数据(MySql中不支持) foreign key 外键 用于限制和关联俩张表,保证该字段的数据必须是关联的主表的存在的数据 -
自动增长
auto_increment :自动增涨,约束的字段自动加1,可以用于编号
-
注意:我们在开发中,一般不会使用外键来关联俩张表,会影响实际的表的操作与速率
-
-
6.2、创建约束的方式
-
在创建表的时候添加约束
在创建表时添加约束有俩种方式,一种是在字段的数据类型后面直接添加约束关键字,另一种是在最后声明约束的字段
-- 第一种方式,直接在字段后声明 CREATE TABLE IF NOT EXISTS student( s_id INT PRIMARY KEY AUTO_INCREMENT, -- 主键和自动增长 s_name VARCHAR(20) NOT NULL, -- 非空 s_gender VARCHAR(4) NOT NULL DEFAULT "男", -- 非空和默认 s_phone VARCHAR(20) UNIQUE, -- 唯一 s_class_id INT CHECK(s_class_id=12 OR s_class_id=13) -- 检查(只能为2或者3) );
-- 第二种方式,在末尾声明约束字段 CREATE TABLE IF NOT EXISTS student( s_id INT, s_name VARCHAR(20), s_gender VARCHAR(4), s_phone VARCHAR(20), s_class_id INT, -- 通过constraint关键字添加 CONSTRAINT PRIMARY KEY(s_id), CONSTRAINT uni UNIQUE(s_phone) -- 在constraint关键字后面也可以给当前的约束起一个名字 );
-
在表已建好后添加约束
我们创建表的时候不一定能把所有的情况都预料到,这时候难免会在表建好以后对约束进行修改
-- 方式一 : 修改字段的信息,可以修改数据类型和约束,也可以为没有约束的字段添加约束 -- --- 语法:ALTER TABLE 表名 MODIFY 字段名 数据类型 [约束] ALTER TABLE student MODIFY s_name VARCHAR(20) NOT NULL; -- 方式二 : 添加一个字段,也可以为其添加约束 -- --- 语法:ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 [约束] ALTER TABLE student ADD COLUMN s_teacher VARCHAR(20) NOT NULL; -- 方式三 : 直接为表中字段添加约束 -- --- 语法:ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(字段名) ALTER TABLE student ADD CONSTRAINT uni UNIQUE(s_phone);
-
关于外键的创建
- 关于外键的创建,也是可以通过以上的俩种方式,但是外键的创建需要指定连接的表
- 注意外键关联的字段必须为主键,也就是主表的主键
-- 关于外键的创建 -- 方式一 :直接约束 -- 语法:REFERENCES 主表(主表的字段) CREATE TABLE IF NOT EXISTS student( s_id INT, s_name VARCHAR(20), s_gender VARCHAR(4), s_phone VARCHAR(20), s_class_id INT REFERENCES school(s_id) -- 通过references连接到表school的s_id字段 ); -- 方式二 : 末尾约束 -- 语法:CONSTRAINT 外键名 FOREIGN KEY(当前表的字段) REFERENCES 主表(主表的字段) CREATE TABLE IF NOT EXISTS student( s_id INT, s_name VARCHAR(20), s_gender VARCHAR(4), s_phone VARCHAR(20), s_class_id INT, CONSTRAINT fk FOREIGN KEY(s_class_id) REFERENCES school(s_id) -- 创建 名为fk的外键约束,约束(s_class_id)字段关联到 学校表的(s_id)字段 ); -- 方式三 : 建完表添加约束 -- 语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(当前表字段名) REFERENCES 主表名(主表字段) ALTER TABLE student ADD CONSTRAINT fk FOREIGN KEY(s_class_id) REFERENCES teacher(s_id);
为什么我们不建立外键:
- 有外键关联的时候,从表的信息必须是主表的数据中存在的值,所以有外间关联时需要先插入主表信息。
- 在我们正常的开发中,一般使用虚拟外键的思想,这样有助于对表进行管理,提高效率。
- 有外键关联时,添加信息需要先添加主表信息,删除信息需要先删除从表的信息,这样使得效率下降
-
-
6.3、修改约束
对约束的修改就是通过表创建完后,对约束进行添加更新的操作,具体方法上述已写到
-
6.4、删除约束
对约束进行删除也是通过alter对表的操作实现的
-- 删除约束 DROP 如果创建的约束取名了,那么删除时就需要通过名称删除,如果没有写名称,会有默认名字 ALTER TABLE student DROP FOREIGN KEY fk; -- 删除唯一约束 ALTER TABLE cars DROP INDEX s_phone;
-
6.5、数据库引擎
数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以 获得特定的功能。
myisam(早些年使用) innodb(默认使用) 事务支持 不支持 支持 数据行锁定 不支持 支持 外键约束 不支持 支持 全文索引 支持 不支持 表空间大小 较小 较大(约为2倍)
7、创建一张完整的表
CREATE TABLE IF NOT EXISTS student( -- 如果表不存在就创建
s_id INT PRIMARY KEY AUTO_INCREMENT, -- 字段名 字段数据类型 约束
s_name VARCHAR(20) NOT NULL,
s_gender VARCHAR(4) NOT NULL DEFAULT "男",
s_phone VARCHAR(20) UNIQUE,
s_class_id INT CHECK(s_class_id=12 OR s_class_id=13)
)ENGINE=INNODB DEFAULT CHARSET=utf8 -- 指明引擎使用的innodb,默认字符集为utf8