MySQL- DDL

李信本

已于 2022-09-02 20:12:22 修改

阅读量450

点赞数

文章标签： mysql 数据库 sql

于 2022-09-01 20:06:07 首次发布

本文链接：https://blog.csdn.net/qq_51978429/article/details/126649681

版权

MySQL- DDL

MySQL-DML&DCL

一：什么是DDL

数据定义语言（DDL：Data Definition Language）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。
DDL 只操作数据库、表、视图、索引，但是不操作表中的数据

二：创建表

语法：
create table 表名(
  字段名1  字段类型 [约束1 约束2 ....],
  字段名2  字段类型 [约束1 约束2 ....]
)

CREATE TABLE course(
	c_id VARCHAR(20) NOT NULL DEFAULT '',
	c_name VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY(c_id)
)

三：字段类型

3.1 数字类型

1：区分有符号和无符号，如果有符号高位表示正数（0）和负数（1），如果无符号，那么高位是数字的一部分

2：正数型len（长度）无意义的，决定整数只有它的字节大小

3：可以用float和double去表示浮点数，但是float和double存在精度丢失

4：如果不能容忍精度丢失，强烈建议使用decimal数据类型

CREATE TABLE t_user (

id INT ,
age TINYINT ,
balance DECIMAL(4,2)

)

DECIMAL(m,n) m = 正数部分+小数部分 n：小数部分，如果小数部分超过n会自动四舍五入

在这里插入图片描述

3.2 字符类型

char:定长，例如char(5)，如果数据没有超过5，会使用空格补，定长的优点是读取效率快，缺点是占用磁盘空间。
varchar：变长，例如varchar(5)，如果数据只占用1个字符，不会使用空格补，优点是节省磁盘空间，缺点是读取效率低

text:大本文存储
1： TINYTEXT(255字符)
2：TEXT(65535字符)
3：MEDIUMTEXT（16M）
4：LONGTEXT(long最大值4G)
BLOB (binary large object)，二进制大对象，是一个可以存储二进制文件（文件、视频、音频、图片）等
1.TinyBlob 最大 255字节
2.Blob 最大 65K
3.MediumBlob 最大 16M
4.LongBlob 最大 4G

3.3 日期类型

datetime与timestamp的区别
1.时间范围的区别
2.datetime不会随着失去变化，但是timestamp随着时区自动变化

DROP TABLE IF EXISTS t_user

CREATE TABLE t_user (

id INT ,
f1 DATETIME,
f2 TIMESTAMP
)

INSERT INTO t_user(f1,f2) VALUES(NOW(),NOW())

SELECT @@time_zone  

SET time_zone = '+9:00'

SELECT * FROM t_user;

SET time_zone = '+10:00'
SELECT * FROM t_user

SET time_zone = '+8:00'
SELECT * FROM t_user

datetime : 时间日期型，格式是YYYY-mm-dd HH:ii:ss时间范围（1000-01-01 00:00:00 到 9999-12-31 23:59:59）

date：日期，就是datetime中的date部分；

time：时间，就是datetime中的time部分；

timestamp：格式是YYYY-mm-dd HH:ii:ss时间范围，时间范围（1970-01-01 00:00:00 到 2037-12-31 23:59:59）

year：yyyy的范围是1901-2155

四：约束

约束可以保证数据的完整性

4.1 主键约束

主键（primary key）：唯一标记数据表中一行数据
主键约束：一旦一个字段被设置为主键，那么这个字段的值必须唯一而且不能为null
主键约束：一旦一个字段被设置为主键，那么这个字段的值必须唯一而且不能为null
主键自增长 :当主键的字段类型为int类型时，可以设置主键自增
注意点：一张表中只能有一个主键，不允许多个主键但是可以联合主键

DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(
  
  id INT PRIMARY KEY,
  `name` VARCHAR(20) 

)

INSERT INTO t_user (id,NAME) VALUES(1,'jack');
INSERT INTO t_user (id,NAME) VALUES(1,'rose');  -- 违背主键约束
INSERT INTO t_user (id,NAME) VALUES(NULL,'rose');-- 违背主键约束

-- 主键自增
DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(
  
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(20) 

)

INSERT INTO t_user (id,NAME) VALUES(1,'jack');
INSERT INTO t_user (NAME) VALUES('rose'); -- 主键自增

--联合主键
DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(
  
  
   username VARCHAR(20) ,
  `PASSWORD` VARCHAR(20) ,
   PRIMARY KEY(username,`PASSWORD`) 

)

4.2 唯一约束

唯一约束：一旦一个字段设置了唯一约束，那么这个字段的值必须唯一，可以为null
一张表可以给多个字段设置唯一约束

DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(
  
  id INT PRIMARY KEY,
  username VARCHAR(20) UNIQUE -- 唯一约束

)

INSERT INTO t_user (id,username) VALUES(1,'jack');
INSERT INTO t_user (id,username) VALUES(2,'jack');  -- 违背唯一约束
INSERT INTO t_user (id,username) VALUES(3,NULL);  -- 不会违背唯一约束
INSERT INTO t_user (id,username) VALUES(4,NULL); -- 不会违背唯一约束

4.3非空约束

非空约束，一旦表字段设置了非空约束，那么这个字段的值必须不能为null

DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(
  
   id INT PRIMARY KEY,
   username VARCHAR(20)  NOT NULL 
   

)

INSERT INTO t_user (id,username) VALUES(1,'jack')
INSERT INTO t_user (id,username) VALUES(1,NULL) -- 违背非空约束

4.4 默认值约束

默认值约束：一旦表字段设置了默认值，如果在插入的时候没有给值，那么会使用默认值

DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(
  
   id INT PRIMARY KEY,
   username VARCHAR(20)  NOT NULL ,
   sex CHAR(2) DEFAULT '男'  -- 默认值约束 
   

)

INSERT INTO t_user (id,username) VALUES(1,'jack')
INSERT INTO t_user (id,username,sex) VALUES(2,'rose','女')


SELECT * FROM t_user

4.5 外键约束

外键（foreign key）简称FK ，外键的值包含在另外一张表的主键中，设计外键的目的就是为了连表查询

外键约束：一旦一个外键设置了外键约束，那么
1：外键的值必须来源于另外一张表的主键，不能无中生有
2：删除主键表中的数据是，如果有外键引用了这条数据，那么这条数据不允许被删除

DROP TABLE IF EXISTS emp;
CREATE TABLE emp 
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  dept_id INT , -- 外键
  -- 加一个外键约束
  CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES  dept(id) 
  
  	
)

DROP TABLE IF EXISTS dept;
CREATE TABLE dept(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)

)



INSERT INTO dept (id,NAME)VALUES(1,'研发中心')
INSERT INTO emp (id,NAME,dept_id) VALUES(1,'jack',1)
INSERT INTO emp (id,NAME,dept_id) VALUES(1,'jack',2) -- 违背外键约束

DELETE FROM  dept WHERE id =1   -- 违背外键约束

4.6 alter&drop

drop database if exists test;

drop table if exists tb_user;


DROP TABLE IF EXISTS emp;
CREATE TABLE emp 
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL	
)

ALTER TABLE emp  ADD email VARCHAR(200);
ALTER TABLE emp DROP email


注意：谨慎使用alter和drop
alter 添加列 可以允许操作
alter删除列，导致程序运行报错