常见的数据类型
数值型：
整型：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;
355
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
