常见数据类型
数值型:
整型
tinyint: 1字节,-128~127或0~255
smallint: 2字节,-32768~32767或0~65535
mediumint: 3字节
int,integer:4字节
bigint: 8字节
小数:
定点数:精确度高
dec(M,D): M+2字节
decimal(M,D)
浮点数
float(M,D): 4字节
double(M,D):8字节,#默认为(10,0)
#M为规定整数位和小数位的位数和,如超过则设为最大临界值,
D为保留的小数点后位数,超过会四舍五入
字符型:
较短的文本: char(M),可省略默认为1 #固定长度字符 耗费空间 高效率
varchar(M),不可省略 #可变长度字符 节省空间 低效率
M:最大字符数
较长的文本: text,
blob(较长的二进制数据)
日期型:
date: 4字节 2019-10-14
datetime: 8字节 2019-10-14 15:15:15
timestamp: 4字节 20191014151515
time: 3字节 15:15:15
year: 1字节 2019
一.整型数据
#如何设置无符号和有符号
CREATE TABLE tab_int(
t1 INT,#默认为有符号
t2 INT UNSIGNED,#无符号
t3 INT(7) ZEROFILL#用0补充
);
INSERT INTO tab_int VALUES(-123456,123456);
#插入的数值超出的整型的范围,会报错,会设为临界值
#有符号默认长度为11,无符号默认为10
#插入数值长度超过亦可以,zerofill如不超过会在左边用0补充显示
二.小数
特点:
(M,D)可省略
CREATE TABLE tab_float(
f1 FLOAT(5,2),
f2 DOUBLE(5,2),
f3 DECIMAL(5,2)
f4 decimal #默认为(10,0)
);
SELECT * FROM tab_float;
INSERT INTO tab_float
VALUES (123.45,123.45,123.45);
VALUES (123.456,123.456,123.456);#则为123.46
VALUES (123456,55)#则为999.99
#M为规定整数位和小数位的位数和,如超过则设为最大临界值,
D为保留的小数点后位数,超过会四舍五入
三.字符型
CREATE TABLE tab_char(
c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('M');#超过枚举,不显示
INSERT INTO tab_char VALUES('A');#能显示a
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('A,b');
#其他:
binary和varbinary勇于保存较短的二进制
enum用于保存枚举,插入值只能插入一个值
set 用于保存集合,插入值能插入多个值
四.日期型
CREATE TABLE tab_date(
t1 DATETIME,#跟随插入时间的时区
t2 TIMESTAMP#跟随实时系统的时区
);
INSERT INTO tab_date
VALUES (NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone';
#更改时区
SET time_zone ='+9:00';
**DDL**
数据定义语言
库和表的管理
一、库的管理
创建,修改,删除
二、表的管理
创建,修改,删除
创建:create
修改:alter
删除:drop
一、库的管理
1.库的创建
语法:
create database 库名;
#创建库Books,查询是否已存在,不会报错
CREATE DATABASE IF NOT EXISTS books;
2.库的修改
RENAME DATABASE 库名 TO 新库名;#已废弃
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk
3.库的删除
DROP DATABASE IF EXISTS books;
二、表的管理
1.表的创建※
语法:
create table if not exists 表名(
列名1 列的类型[(长度)约束],
列名2 列的类型[(长度)约束],
........
列名n 列的类型[(长度)约束]
)
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
);
DESC book;
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
2.表的修改
语法:
alter table 表名
add|drop|change|modify column 列名 [列类型 约束]
[first|after 字段名];#插入原有的字段名的前或后,不写字段名,默认为第一个或最后
#修改列名,可以同时修改类型
ALTER TABLE book
CHANGE COLUMN publishDate pubDate DATETIME;
#修改列的类型或约束
ALTER TABLE book
MODIFY COLUMN pubdate TIMESTAMP;
#添加新列
ALTER TABLE book
ADD COLUMN annual DOUBLE;
#删除列
ALTER TABLE book
DROP COLUMN annual;
#修改表名
ALTER TABLE author
RENAME TO book_author;
3.表的删除
DROP TABLE book_author;
show tables;
通用的写法:
drop database if exists 旧库名;
create database 新库名;
drop table if exists 旧表名;
create table 新表名();
4.表的复制
INSERT INTO book_author
VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
SELECT * FROM book_author;
TRUNCATE TABLE book_author;
#仅复制的表的结构
CREATE TABLE copy LIKE book_author;
#复制表的结构和数据
CREATE TABLE copy2
SELECT * FROM book_author;
#仅复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM book_author
WHERE nation ='中国';
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM book_author
WHERE 0;#填false
#跨库,表数据复制
create table 新表名
select 被复制表的字段1,被复制表的字段2...
from 被复制的库名.表名;
create table 新表名 from 被复制的库名.表名;
5.常见约束
一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
六大约束:
1.NOT NULL:非空,用于该字段的值不能为空,比如姓名,学号等...
2.DEFAULT:默认,用于该字段有默认值,比如性别等..
3.PRIMARY KEY:主键,用于该字段的值具有唯一性,并且非空,比如编号
4.UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空,比如座位号
5.CHECK:检查约束[mysql不支持],比如年龄,性别
6.FOREIGN KEY:外键,用于限制两个表的关系,保证该字段的值必须来自于主表的关联列的值
在从表设置外键约束,用于引用主表中某列的值
#约束可添加多个,用空格隔开,没空格要求
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
1.列级约束:除了外键约束没效果,其余都支持
2.表级约束:除了非空,默认,其他的都支持
主键和唯一的区别:
保证唯一性 是否允许为空 一个表中可以创建多少个 是否允许组合
主键 √ × 一个 √ primary key(字段1,字段2,...)
唯一 √ √ 多个 √
不能有相同值 不能完全重复,不推荐
外键的特点:
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求去一致或兼容,名称无要求
3.主表的关联列必须是一个key(一般是主键或唯一)
4.插入数据时,应先插入主表,在插入从表.删除数据时,先删除从表,再删除主表.
位置 支持的约束类型 是否可以起别名
列级约束: 列的后面 语法都支持,除了外键 不可以
表级约束: 所有列的后面 默认和非空不支持 可以(主键没效果)
SELECT TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
5.1.创建表级添加约束
#1.添加列级约束
语法:
直接在字段名喝类型后面追加 约束类型即可
支持 默认,非空,主键,唯一
CREATE DATABASE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键,默认为非空
stuname VARCHAR(20) NOT NULL,#非空
geabder CHAR(1) CHECK(gender='男'OR gender ='女'),#检查
seat INT ,#唯一
age INT DEFAULT 18,#默认约束
majorid INT REFERENCES major(id)#外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorname VARCHAR(20)
);
#查看stuinfo中的所有的索引,包括主键,外键,唯一
SHOW INDEX FROM stuinfo;
#2.添加表级约束
语法:在各个字段的最下面
【constraint 约束名】约束类型(字段名)
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20) ,
geabder 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)
);
CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20) ,
geabder CHAR(1),
seat INT ,
age INT ,
majorid INT,
#加主键
PRIMARY KEY(id),
#加唯一键
UNIQUE(seat),
#检查
CHECK(gender='男' OR gender ='女'),
#外键
FOREIGN KEY(majorid) REFERENCES major(id)
);
show index from stuinfo
※通用的写法
CREATE TABLE if exists stuinfo(
id INT PRIMARY KEY,#主键,默认为非空
stuname VARCHAR(20) NOT NULL,#非空
geabder CHAR(1) CHECK(gender='男'OR gender ='女'),#检查
seat INT ,#唯一
age INT DEFAULT 18,#默认约束
majorid INT ,
constraint kf_stuinfo_major foreign key (majorID) references major(id)
);
5.2.修改表时添加约束
语法:
1.添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
2.添加表级约束
ALTER TABLE 表名 ADD [constraint 约束名] 约束类型(字段名) [外键的引用:references 主表名(字段)]
#1.添加非空约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20) ,
geabder CHAR(1),
seat INT ,
age INT ,
majorid INT
);
DESC stuinfo;
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#添加主键
#1.列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#2.表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#添加唯一
#1.列级约束
ALTER TABLE stuinfo MODIFY COLUMNseat INT UNIQUE;
#2.表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#添加外键
ALTER TABLE stuinfo
ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
5.3.修改表时删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname varchar(20) null;
#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;
#标识列
又称自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1.标识列不一定和主键搭配,但要求是一个key
2.一个表至多只有一个标识列
3.标识列的类型只能是数值型
4.标识列可以通过SET auto_increment_increment设置步长
5.
一.创建表时设置标识列
CREATE TABLE tab_identity(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
DROP TABLE IF EXISTS tab_identity;
INSERT INTO tab_identity;
VALUES (1,'john');
自增长
TRUNCATE TABLE tab_identity;
SELECT * FROM tab_identity;
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO tab_identity
VALUES (NULL,'lily');
INSERT INTO tab_identity(name)
VALUES ('lily');
#修改步长,不建议修改
SET auto_increment_increment=3;
二.修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
三.修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
小白菜学Mysql笔记(三)--DDL定义语言
最新推荐文章于 2024-07-26 19:34:19 发布