MySQL精讲之三:DDL数据定义语句
免费学习推荐:mysql视频教程
文章目录
- 一、DDL数据定义语句
- 二、数据类型
- 2.1 整型
- 2.2 小数
- 2.3 字符型
- 2.4 日期类型
- 二、六大约束
- 四、标识列
- 五、级联删除与置空
一、DDL数据定义语句
库的管理
创建:create database [if not exists] 库名;
修改:若需要修改库名,直接修改文件夹
删除:DROP DATABASE IF EXISTS 库名;
表的管理
创建
create table IF NOT EXISTS 表名(
列名 列的类型[长度、约束],
列名 列的类型[长度、约束],
列名 列的类型[长度、约束],
...
)
修改alter table 表名 add|drop|modify|change column 列名 [列类型 约束];
删除drop table 表名;
【库的管理】# 创建库CREATE DATABASE IF NOT EXISTS books;# 修改库的字符集ALTER DATABASE books CHARACTER SET gbk;# 删除库DROP DATABASE IF EXISTS books;【表的管理】# 1.创建表CREATE TABLE IF NOT EXISTS book( id INT, bName VARCHAR(20), authorID INT, publishDate DATETIME);DESC book;CREATE TABLE author( id INT, au_name VARCHAR(20), nation VARCHAR(10));DESC author;# 2.修改表# 修改列名ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;# 修改列的类型或约束ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;# 添加新列ALTER TABLE author ADD COLUMN annual DOUBLE; # 添加新列作为第一个字段ALTER TABLE author ADD COLUMN newT1 INT FIRST;# 添加新列在指定的列后ALTER TABLE author ADD COLUMN newT2 INT AFTER newT1;# 删除列ALTER TABLE author DROP COLUMN annual;# 修改表名ALTER TABLE author RENAME TO book_author;# 3.表的删除DROP TABLE IF EXISTS book_author;SHOW TABLES;# 4.表的复制INSERT INTO author VALUES(1,'小樱','日本'),(2,'悟空','中国'),(3,'绿巨人','美国'),(4,'哪吒','中国');# 仅复制表的结构CREATE TABLE author2 LIKE author;# 复制表的结构+数据CREATE TABLE author3 SELECT * FROM author;# 只复制部分数据CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation='中国';# 仅复制某些字段CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1=2;
二、数据类型
2.1 整型
整数类型 | 别名 | 字节 | 无符号范围 | 有符号范围 |
---|---|---|---|---|
Tinint | 微整型 | 1 | 0~255 | -128~127 |
Smallin | 小整型 | 2 | 0~65535 | -32768~32767 |
Mediumint | 中整型 | 3 | 0~1677215 | -8388608~8388607 |
Int或Integer | 整型 | 4 | 0~4294967295 | -2147483648~2147483647 |
Bigint | 大整型 | 8 | 0~9223372036854775807*2+1 | -9223372036854775808~9223372036854775807 |
【整型】# 1.默认为有符号,可以添加unsigned设置为无符号CREATE TABLE IF NOT EXISTS tab_int( t1 INT, # 有符号 t2 INT UNSIGNED, # 无符号 t3 INT ZEROFILL #添加zerofill后自动变更为无符号整型,位数不够0填充.);DESC tab_int;SELECT * FROM tab_int;# 2.如果插入的数值超出了整型的范围,会报out of range异常INSERT INTO tab_int VALUES(2147483648,1);# 3.如果不设置长度,会有默认的长度,位数不够时0填充(前提是字段有ZEROFILL).
2.2 小数
浮点数类型 | 字节 | 范围 |
---|---|---|
float(M,D) | 4 | -2^128 ~ +2^128 |
double(M,D) | 8 | -2^1024 ~ +2^1024 |
定点数类型 | \ |
\ |
DEC(M,D) | M+2 | 最大取值范围与double相同,给定decimal的有效范围由M和D决定 |
注意:
M:整数部位+小数部位的总长度
D:小数部位
D和M都省略时:
1、如果是decimal类型,则M默认为10,D默认为0;
2、如果是floact和double,会根据插入的数值的精确度来决定精度。
3、定点型的精确度较高,如果要求插入数值的精确度较高如货币运算则考虑使用。
2.3 字符型
字符串类型 | M是否可以省略 | 特点 | 空间耗费 | 效率 | 范围 |
---|---|---|---|---|---|
char(M) | M可以省略,默认为1 | 定长 | 比较耗费 | 高 | M为0~255之间的整数 |
varchar(M) | M不可以省略 | 可变长 | 比较节省 | 低 | M为0~65535之间的整数 |
binary
和varbinary
类型,类似于char和varchar,不同的是它们包含二进制字符而不包含非二进制字符,即保存较短的二进制。
Bit(M)类型,字节为1~8,范围为Bit(1)~Bit(8)
。
Enum类型,即枚举类型,要求插入的值必须属于列表中指定的值之一,如果列成员为1~255
,则需要1个字节存储;如果列成员为255~65535
,则需要2个字节存储,最多需要65535个成员。
Set类型,和Enum类似,可以保存0~64个成员。和Enum最大的区别是:Set类型一次可以选取多个成员,而Enum只能选一个,根据成员个数不同,存储所占的字节也不同。
成员数 | 字节数 |
---|---|
1~8 | 1 |
9~16 | 2 |
17~24 | 3 |
25~32 | 4 |
33~64 | 8 |
【枚举】CREATE TABLE tab_set( s1 SET('a','b','c'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('c,a');INSERT INTO tab_set VALUES('a,b,c');# 插入后,内部会进行排序,如插入c,a会变成a,c
2.4 日期类型
日期和时间类型 | 特点 | 字节 | 最小值 | 最大值 |
---|---|---|---|---|
date | 只保存日期 | 4 | 1000-01-01 | 9999-12-31 |
datetime | 保存日期+时间 | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp(使用较多) | 保存日期+时间 | 4 | 187001080001 | 2038年的某个时刻 |
time | 只保存时间 | 3 | -838:59:59 | 838:59:59 |
year | 只保存年 | 1 | 1901 | 2155 |
timestamp和实际时区有关,更能反映实际的日期;datetime则只能反映出插入时的当地时区。
timestamp的属性受Mysql版本和SQLMode的影响很大。
【日期类型】CREATE TABLE tab_date( t1 DATETIME, # 不受时区影响 t2 TIMESTAMP # 受时区影响);INSERT INTO tab_date VALUES(NOW(),NOW());SET time_zon='+8:00';# 设置时区SHOW VARIABLES LIKE 'time_zone'; #显示当前时区
二、六大约束
含义:一种限制,用于限制表中的数据,为了保证表中数据的准确性和可靠性。
NOT NULL | 非空约束,规定某个字段不能为空 |
---|---|
UNIQUE | 唯一约束,规定某个字段在整个表汇中是唯一的 |
PRIMARY KEY | 主键(唯一且非空) |
FOREIGN KEY | 外键 |
CHECK | 检查约束(mysql中不支持) |
DEFAULT | 默认值,保证该字段有默认值 |
列级约束:六大约束语法上都支持,但外键约束没有效果。
表级约束:除了非空、默认,其他都支持。
常用的做法是:其他约束都写在列级,外键约束写在表级。
主键和唯一对比 | 是否保证唯一性 | 是否允许为空 | 允许有几个 | 是否允许组合(不推荐) |
---|---|---|---|---|
主键 | √ | × | 至多一个主键 | 允许组合主键 |
唯一 | √ | √(允许有一个null) | 可以有多个唯一 | 允许组合唯一 |
外键的特点:
- 要求在从表设置外键关系。
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求。
- 主表的关联列必须是一个key(一般是主键或唯一)
- 插入数据时,先插入主表再插入从表;删除数据时,先删除从表再删除主表。
列级约束和表级约束比较 | 位置 | 支持的约束类型 | 是否可以起别名 |
---|---|---|---|
列级约束 | 列的后面 | 语法都支持,但外键没有效果 | 不可以 |
表级约束 | 所有列的下面 | 默认和非空不支持,其他支持 | 可以(主键没有效果) |
【列级约束】 直接在字段名和类型后面追加约束类型。 注意:只支持默认、非空、主键、唯一,不支持外键约束CREATE DATABASE students;USE students;CREATE TABLE major( id INT PRIMARY KEY,# 主键 majorName VARCHAR(20));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(id) # 外键);DESC stuinfo;SHOW INDEX FROM stuinfo; # 查看表中所有的索引,外键,唯一【表级约束】# 语法:[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(id) # 外键);SHOW INDEX FROM stuinfo;【修改表时添加约束】# 添加非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 添加默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;# 添加主键ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;# 添加唯一键ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;# 添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);DESC stuinfo;SHOW INDEX FROM stuinfo;【修改表时删除约束】# 删除非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;# 删除默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT;# 删除主键ALTER TABLE stuinfo DROP PRIMARY KEY;# 删除唯一ALTER TABLE stuinfo DROP INDEX seat;# 删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
学习了约束,尝试完成籼米的测试题
1、列级约束:ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# 列约束不支持起名字
表级约束:ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);# 实际上主键起了名字也没效果
2、与1类似
3、ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);
四、标识列
标识列又称为自增长列,其实也可以将标识列纳入约束的范围。
含义:可以不用手动的插入值,系统提供默认的序列值。
特点:
1.标识列不一定非要和主键搭配,但要求是一个key。
2.一个表至多可以有一个表示列。
3.表示列的类型只能为数值型。
4.标识列可以通过SET auto_increment_increment=3;
设置步长,也可以通过手动插入值来设置起始值。
【创建表时设置标识列】DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity( id INT PRIMARY KEY AUTO_INCREMENT, #设置自动自增 NAME VARCHAR(20));INSERT INTO tab_identity VALUE(NULL,'花花');INSERT INTO tab_identity(NAME) VALUE('Hudie');SELECT * FROM tab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=3;#设置步长为3# 起始值可以通过改变第一条记录的值来更改TRUNCATE TABLE tab_identity;【修改表时设置标识列】CREATE TABLE tab_identity( id INT, #设置自动自增 NAME VARCHAR(20));# 设置主键和标识列ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;修改表时删除标识列【】ALTER TABLE tab_identity MODIFY COLUMN id INT;
五、级联删除与置空
级联删除:ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
级联置空:ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
【演示级联删除、级联置空】DROP TABLE major,stuinfoCREATE TABLE IF NOT EXISTS major( id INT PRIMARY KEY, majorName VARCHAR(20));INSERT INTO majorVALUES(1,'Java'),(2,'Python'),(3,'Go');CREATE TABLE IF NOT EXISTS stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT);INSERT INTO stuinfoSELECT 1,'join1','女',NULL,NULL,1 UNION ALLSELECT 2,'join2','女',NULL,NULL,1 UNION ALLSELECT 3,'join3','女',NULL,NULL,2 UNION ALLSELECT 4,'join4','女',NULL,NULL,2 UNION ALLSELECT 5,'join5','女',NULL,NULL,1 UNION ALLSELECT 6,'join6','女',NULL,NULL,3 UNION ALLSELECT 7,'join7','女',NULL,NULL,3 UNION ALLSELECT 8,'join8','女',NULL,NULL,1);SELECT * FROM major;SELECT * FROM stuinfo;# 传统方式添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);# 删除major表的3号专业# 方式1:级联删除# 先删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外键时添加级联删除ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;DELETE FROM major WHERE id =3;# 方式2:级联置空# 先删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外键时添加级联置空ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;DELETE FROM major WHERE id =2;
更多相关免费学习推荐:mysql教程(视频)
Atas ialah kandungan terperinci MySQL精讲之三:DDL数据定义语句. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

Alat AI Hot

Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool
Gambar buka pakaian secara percuma

Clothoff.io
Penyingkiran pakaian AI

AI Hentai Generator
Menjana ai hentai secara percuma.

Artikel Panas

Alat panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas

Kemahiran pemprosesan struktur data besar: Pecahan: Pecahkan set data dan proseskannya dalam bahagian untuk mengurangkan penggunaan memori. Penjana: Hasilkan item data satu demi satu tanpa memuatkan keseluruhan set data, sesuai untuk set data tanpa had. Penstriman: Baca fail atau hasil pertanyaan baris demi baris, sesuai untuk fail besar atau data jauh. Storan luaran: Untuk set data yang sangat besar, simpan data dalam pangkalan data atau NoSQL.

Prestasi pertanyaan MySQL boleh dioptimumkan dengan membina indeks yang mengurangkan masa carian daripada kerumitan linear kepada kerumitan logaritma. Gunakan PreparedStatements untuk menghalang suntikan SQL dan meningkatkan prestasi pertanyaan. Hadkan hasil pertanyaan dan kurangkan jumlah data yang diproses oleh pelayan. Optimumkan pertanyaan penyertaan, termasuk menggunakan jenis gabungan yang sesuai, membuat indeks dan mempertimbangkan untuk menggunakan subkueri. Menganalisis pertanyaan untuk mengenal pasti kesesakan; gunakan caching untuk mengurangkan beban pangkalan data;

Membuat sandaran dan memulihkan pangkalan data MySQL dalam PHP boleh dicapai dengan mengikuti langkah berikut: Sandarkan pangkalan data: Gunakan arahan mysqldump untuk membuang pangkalan data ke dalam fail SQL. Pulihkan pangkalan data: Gunakan arahan mysql untuk memulihkan pangkalan data daripada fail SQL.

Bagaimana untuk memasukkan data ke dalam jadual MySQL? Sambung ke pangkalan data: Gunakan mysqli untuk mewujudkan sambungan ke pangkalan data. Sediakan pertanyaan SQL: Tulis pernyataan INSERT untuk menentukan lajur dan nilai yang akan dimasukkan. Laksanakan pertanyaan: Gunakan kaedah query() untuk melaksanakan pertanyaan sisipan Jika berjaya, mesej pengesahan akan dikeluarkan.

Salah satu perubahan utama yang diperkenalkan dalam MySQL 8.4 (keluaran LTS terkini pada 2024) ialah pemalam "Kata Laluan Asli MySQL" tidak lagi didayakan secara lalai. Selanjutnya, MySQL 9.0 mengalih keluar pemalam ini sepenuhnya. Perubahan ini mempengaruhi PHP dan apl lain

Untuk menggunakan prosedur tersimpan MySQL dalam PHP: Gunakan PDO atau sambungan MySQLi untuk menyambung ke pangkalan data MySQL. Sediakan penyata untuk memanggil prosedur tersimpan. Laksanakan prosedur tersimpan. Proses set keputusan (jika prosedur tersimpan mengembalikan hasil). Tutup sambungan pangkalan data.

Mencipta jadual MySQL menggunakan PHP memerlukan langkah berikut: Sambung ke pangkalan data. Buat pangkalan data jika ia tidak wujud. Pilih pangkalan data. Buat jadual. Laksanakan pertanyaan. Tutup sambungan.

Pangkalan data Oracle dan MySQL adalah kedua-dua pangkalan data berdasarkan model hubungan, tetapi Oracle lebih unggul dari segi keserasian, skalabiliti, jenis data dan keselamatan manakala MySQL memfokuskan pada kelajuan dan fleksibiliti dan lebih sesuai untuk set data bersaiz kecil. ① Oracle menyediakan pelbagai jenis data, ② menyediakan ciri keselamatan lanjutan, ③ sesuai untuk aplikasi peringkat perusahaan ① MySQL menyokong jenis data NoSQL, ② mempunyai langkah keselamatan yang lebih sedikit, dan ③ sesuai untuk aplikasi bersaiz kecil hingga sederhana.
