目录
- 常见的数据类型
- 常见约束
- 标识列(自增长列)
- DDL语言(库的管理,表的管理)
- DML语言(插入、修改、删除)
常见的数据类型
数值型:
整型:TINYINT(1字节),SMALLINT(2字节),MEDIUMINT(3字节),INT/INTEGER(4字节),BIGINT(8字节)
1.默认是有符号。如果想设置无符号,需要添加UNSIGNED
2.如果插入的数值超过了整型的范围,会报out of ranger,并插入最大值
3.如果不设置长度,有默认长度。长度代表显示的最大宽度,使用ZEROFILL会在左边填充0
小数:定点数:DEC(M,D),DECIMAL(M,D)
浮点数:FLOAT(M,D)(4字节),DOUBLE(M,D)(8字节)
M:整数与小数的位数。D:小数部分
MD可以省略,FLOAT和DOUBLE动态调整精度,DECIMAL默认M 10,D 0
字符型:
较短的文本:CHAR(M)(固定长度,默认为1,效率高),VARCHAR(M)(可变长度,效率低)
BINARY,VARBINARY保存较短的二进制,name enum(…)
较长的文本:text
日期型:DATETIME,TIMESTAMP(4字节,1970-2038,受时区影响)
原则:所选择的类型越简单越好,保存的数值的类型越小越好
常见约束
常见约束:用于限制表中数据,确保表中数据的一致性和可靠性
- NOT NULL:非空约束,保证该字段不能为空
- DEFAULT:默认,保证该字段有默认值
- PRIMARY KEY:主键,保证该字段的值具有唯一性,并且非空
- UNIQUE:唯一,保证该字段的值具有唯一性,可以为空
- CHECK:检查约束
- FOREIGN KEY:外键,限制两个表的关系,保证该字段的值必须来自主表的关联列的值
从表添加外键约束,引用主表的某字段的值
从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
主表的关联列必须是一个key,一般是主键或唯一
创建表时,先创建主表,再创建从表
删除表时,先删除从表,再删除主表
添加和删除外键:索引+外键
主键和唯一的异同:
- 二者都保证唯一性
- PRIMARY KEY不允许null,UNIQUE允许为空
- 一个表可以有多个UNIQUE,只能有一个PRIMARY KEY
- 二者均允许联合,但不推荐,只有多个字段完全相同才重复
添加约束的时间:创建表、修改表
约束的添加分类:列级约束:6种约束都支持,但外键没有效果
表级约束:除了NOT NULL、DEFAULT,其它都支持
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
表级约束
[CONSTRAINT 约束名] 约束类型(字段名)
);
#一、创建表时添加约束
#创建一个表的通用的写法
CREATE TABLE IF NOT EXISTS student(
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,
CONSTRAINT fk_student_major FOREIGN KEY(majorId) REFERENCES major(id)
);
#二、修改表时添加约束
#添加非空约束
ALTER TABLE student MODIFY COLUMN seat INT UNIQUE NOT NULL;
#添加默认约束
ALTER TABLE student MODIFY COLUMN gender CHAR(1) CHECK(gender='男' OR gender='女') DEFAULT '男';
#添加主键
ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY;#列级约束
ALTER TABLE student ADD PRIMARY KEY(id);#表级约束
#添加外键
ALTER TABLE student ADD FOREIGN KEY(majorId) REFERENCES major(id);
ALTER TABLE student ADD CONSTRAINT ky_student_major FOREIGN KEY(majorId) REFERENCES major(id);
#三、修改表时删除约束
#删除非空约束
ALTER TABLE student MODIFY COLUMN seat INT UNIQUE;
#删除默认约束
ALTER TABLE student MODIFY COLUMN gender CHAR(1) CHECK(gender='男' OR gender='女');
#删除主键
ALTER TABLE student DROP PRIMARY KEY;
#删除唯一
ALTER TABLE student DROP INDEX seat;
#删除外键
ALTER TABLE student DROP FOREIGN KEY ky_student_major;
#级联删除
ALTER TABLE student ADD CONSTRAINT ky_student_major FOREIGN KEY(majorId) REFERENCES major(id)
ON DELETE CASCADE;
#级联置空
ALTER TABLE student ADD CONSTRAINT ky_student_major FOREIGN KEY(majorId) REFERENCES major(id)
ON DELETE SET NULL;
#查看表中的所有索引,包括主键、外键、唯一
SHOW INDEX FROM student;
标识列(自增长列)
标识列,又称自增长列:系统提供默认的序列值,使用AUTO_INCREMENT。
特点:
1.要求与一个key搭配
2.一个表只能有一个标识列
3.标识列的类型只能是数值型
修改起始值:插入的时候设置
#创建表时设置标识列
CREATE TABLE tab(
id INT PRIMARY KEY AUTO_INCREMENT
);
#设置初始值
INSERT INTO tab VALUES(10);
#修改步长
SET auto_increment_increment=3;
#修改表时设置标识列
ALTER TABLE tab MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#修改表时删除标识列
ALTER TABLE tab MODIFY COLUMN id INT PRIMARY KEY;
DDL语言(库的管理,表的管理)
DDL语言:数据定义语言
一、库的管理
创建:CREATE DATABASE IF NOT EXISTS 库名;
删除:DROP DATABASE IF EXISTS 库名;
二、表的管理
创建:语法:
CREATE TABLE IF NOT EXISTS 表名(
列名 列的类型 [(长度) 约束],
列名 列的类型 [(长度) 约束],
列名 列的类型 [(长度) 约束],
...
);
修改:ALTER TABLE 表名 ADD|CHANGE|DROP|MODIFY|RENAME COLUMN 列名 列的类型 [(长度) 约束]
删除:DROP TABLE IF EXISTS 表名;
#一、库的管理
#库的创建
CREATE DATABASE IF NOT EXISTS books;
#库的修改
#修改库名(现在不能使用了,不安全。直接修改文件夹)
RENAME DATABASE books TO book;
#修改字符集
ALTER DATABASE books CHARACTER SET GBK;
#库的删除
DROP DATABASE IF EXISTS books;
#二、表的管理
#表的创建
CREATE TABLE IF NOT EXISTS book(
id INT,#编号
bName VARCHAR(20),#书名
price DOUBLE,#价格
authorId INT#作者编号
);
DESC book;#查看表结构
#表的修改
#修改列名(要求加上类型)
ALTER TABLE book CHANGE COLUMN bName book_Name VARCHAR(50);
#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN book_Name INT;
#增加列
ALTER TABLE book ADD COLUMN annual DOUBLE;
#可以指定列的位置(默认在最后)
ALTER TABLE book ADD COLUMN annual DOUBLE FIRST;
ALTER TABLE book ADD COLUMN annuasl DOUBLE AFTER price;
#删除列
ALTER TABLE book DROP COLUMN annuasl;
#修改表名
ALTER TABLE book RENAME TO novel;
#表的删除
DROP TABLE IF EXISTS book;
#表的复制
#1.仅复制表的结构
CREATE TABLE copy LIKE book;
#2.复制表的结构和数据
CREATE TABLE copy SELECT * FROM book WHERE id=2;
#3.复制表的部分结构
CREATE TABLE copy SELECT id,price FROM WHERE 1=2;
创建mysql数据库用户:
create user tom identified by 'abc123';
授予权限
#授予通过网络方式登录的tom用户,对所有库所有表的全部权限,密码设为abc123.
grant all privileges on *.* to tom@'%' identified by 'abc123';
# 给tom用户使用本地命令行方式,授予db这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on db.* to tom@localhost identified by 'abc123';
DML语言(插入、修改、删除)
DML语言:数据操作语言
插入:INSERT INTO 表名(列名…) VALUES(列名…);
INSERT INTO 表名 SET 列名=值,列名=值…;
INSERT INTO 表名 子查询;
修改:UPDATE 表名 SET 列名=值,列名=值… WHERE 筛选条件;
修改多表:
92:UPDATE 表名1,表名2… SET 列名=值,列名=值… WHERE 连接条件 AND 筛选条件;
99:UPDATE 表名1 INNER|LEFT|RIGHT JOIN 表名2 ON 连接条件
SET 列名=值,列名=值… WHERE 筛选条件;
删除:DELETE FROM 表名 WHERE 筛选条件;
多表删除:
92:DELETE 表名1的表名 FROM 表名1 表名,表名2 表名 WHERE 连接条件 AND 筛选条件;
99:DELETE 表名1,2的表名 FROM 表名1 表名 INNER|LEFT|RIGHT JOIN 表名2 表名
ON 连接条件 WHERE 筛选条件;
删除整个表:TRUNCATE TABLE 表名;(不可以加WHERE)
DELETE和TRUNCATE的区别
- 假如要删除的表中有自增长列,如果用DELETE删除后再插入数据,自增长列的值从断点开始。
如果使用TRUNCATE删除后再插入数据,自增长列的值从1开始 - DELETE有返回值,返回删除了多少行;TRUNCATE没有返回值
- DELETE可以添加筛选条件,TRUNCATE不可以
- DELETE可以回滚,TRUNCATE不可以回滚
#一、插入
#1.插入的值的类型要与列的类型一致或兼容,数量要一致
INSERT INTO job_grades(grade_level,lowest_sal,highest_sal) VALUES('a',666,666);
#2.不可以为null的列必须插入值
INSERT INTO job_grades(grade_level,lowest_sal,highest_sal) VALUES('b',NULL,NULL);
#为null的可以不写列名
INSERT INTO job_grades(grade_level) VALUES('b');
#3.列的顺序可以与表头不一致,但插入顺序需要一致
INSERT INTO job_grades(highest_sal,grade_level,lowest_sal) VALUES(NULL,'c',NULL);
#4.可以省略列名,插入的顺序要与表中列的顺序一致
INSERT INTO job_grades VALUES('d',NULL,NULL);
#5.支持插入多行,
INSERT INTO job_grades VALUES('g',6,6),('g',7,7),('g',8,8);
#6.支持子查询,要省略VALUES
INSERT INTO job_grades(grade_level,lowest_sal,highest_sal) SELECT 'h',1,1;
#方式二
INSERT INTO job_grades SET grade_level='e';
INSERT INTO job_grades SET grade_level='f',lowest_sal=1,highest_sal=1;
有则更新,无则插入,要求传入主键,自增
INSERT INTO employee VALUES('Zhangsan',2000)
ON DUPLICATE KEY
UPDATE name='Zhangsan',salary=2000,
#二、修改
UPDATE job_grades SET lowest_sal=666,highest_sal=666 WHERE grade_level='h';
#三、删除
DELETE FROM job_grades WHERE grade_level='g';
#删除第一条记录
DELETE FROM job_grades LIMIT 1;