MySQL数据库(2)

今天刷题遇见个没见过的insert or ignore，干脆贴这了= - =（当然很多人可能见过了QAQ）
insert into:插入数据,如果主键重复，则报错
insert repalce:插入替换数据,如果存在主键或unique数据则替换数据
insert ignore:如果存在数据,则忽略。
一些基础概念:
1、表的约束（constraint）
主键，外键，唯一键，默认值，非空约束,自动增长。
请简要描述sql语言的分类【5大分类】
2、SQL语言分为五大类：
DDL 语言(数据定义语言) - Create、Alter、Drop、truncate
DQL 语言(数据查询语言) - Select 查询语句
DML 语言(数据操纵语言) -对表中数据 Insert、Update、Delete
TCL 语言(事务控制语言) - Commit(提交)、Rollback(回滚) 事务提交与回滚语句
DCL 语言(数据控制语言) - Grant(授权)、Revoke(回收权限) 授予权限与回收权限语句
3、主键约束和唯一性约束的区别
主键不可以重复，不可以为空，一个表中只能有一个主键，但主键可以由一个字段构成，也可以由多个字段构成；
唯一键可以为空，数据不可以重复，一个表中可以由多个唯一键。

-- ---------------------------------------------------
1、数值型数据
-- unsigned关键字: 无符号类型，该字段的值只能是非负数，该关键字只对整数有效。
-- zerofill 关键字: 默认无符号，零填充，mysql中，在数字长度不够的时候数据前面填充0，以达到设定的长度，比如报表开发，报表中的数据按照一定的格式和长度来显示时，需要用到zerofill；
-- 如果只希望表示零和正整数，可以使用无符号关键字“unsigned”
-- 如果往表中特定字段中输入的数据前面有0，建表时可以考虑使用关键字zerofill
-- zerofill一般和unsigned 配合使用
-- 特别说明：建表时，整型数据类型后的字符长度，
-- 只有在使用了unsigned和zerofill关键字时才有意义

举例：在schoola数据库中，创建表student，表中字段sid 数据类型为int型，要求建好的表中，
插入数据时，sid字段的数据长度显示5位，如果插入的数据长度不够5位，前面自动补零。
-- 建表
CREATE TABLE schoola.student
(
sid INT(5) UNSIGNED ZEROFILL
);

-- 如果一个表中的数值型字段后出现了UNSIGNED关键字，那么这个字段中的数据只能插入正整数
-- 如果表中数值型字段限制了字段长度【比如5】，字段后也出现了zerofill关键字，
-- 并且那么该字段中可以插入的数据长度不受字段长度的影响，但如果实际插入的字段长度少于
-- 指定长度时，前面会自动补零；

-- 插入数据
INSERT INTO schoola.`student`(sid) VALUES(4);
INSERT INTO schoola.`student`(sid) VALUES(123456789);

2、字符型

CREATE TABLE test.sclass
(
cid INTEGER,  -- 班号
cname CHAR(20),
cowner VARCHAR(30), -- 班主任名称
memo TEXT -- 班级说明
);

INSERT INTO test.`sclass` (cid, cname, cowner, memo) 
VALUES(1,"zhongjiyiban","dujuan","banzhangshiwangdadong");

3、日期时间型

举例：在test数据库，创建分数表score
CREATE TABLE test.score
(
sid TINYINT, -- 学号
sname CHAR(30), -- 学生姓名 
score FLOAT(6,2), -- 分数 
sdate DATE -- 入学日期
);

INSERT INTO test.score VALUES(1,"zs",99.1,"2020-10-8");

INSERT INTO test.score VALUES(1,"zs",99.1,CURDATE());
-- CURDATE():用来获取mysql数据库所在电脑的当前日期
-- cur:current 当前的意思

4、混合型
CREATE TABLE test.teacher
(
tid INTEGER, -- 教师编号
tname CHAR(30), -- 教师名称
sex ENUM("M","F"), -- 姓名 ：M：male 男，F：female 女 
subjects SET("java","linux","mysql") -- 科目
);

举例：往teacher表中，插入一条数据,[编号:1,教师姓名:zs,性别：男,科目:java,linux]
INSERT INTO test.`teacher`(tid,tname,sex,subjects) 
VALUES(1,"zs","M","java,linux");

-- 说明，往enum和set类型的字段中插入数据时，插入的值只能从建表时设定的之中选取
七、数据库中表的操作
1、创建表

#建表时指定数据库引擎和字符集
-- 数据库引擎是什么？
-- 数据库引擎是一种存储、处理、保护数据库数据的核心服务。
举例：查看mysql数据库的引擎
SHOW ENGINES; -- 查看数据库引擎
-- SHOW 展示，查看
-- ENGINE：引擎
举例：查看指定数据库中表的引擎
SHOW TABLE STATUS;

举例：在test库，创建表商品goods，商品编号gid，商品名称gname，
创建表时，指定数据库的引擎为innodb和字符集为utf8
CREATE TABLE test.goods
(
gid INT,
gname CHAR(30)
) ENGINE=INNODB DEFAULT CHARACTER SET=utf8;

-- ENGINE=INNODB:定义字符集位innodb
-- DEFAULT CHARACTER SET=utf8 自定字符集位utf8

2、修改表
-- 说明：修改表就是修改表的表名、增加字段、删除字段、修改字段、修改数据类型及数据长度、修改表的约束等。
（1）为一个表增加字段 -- 考试题
-- 语法：
ALTER TABLE 库名.表名
ADD [COLUMN] 字段名 数据类型(数据长度); 
这里 column关键字可以省略
-- 解释
-- Alter：修改
-- Add column：增加字段
-- Column：列，字段
-- Add：增加

举例：为表sclass增加系编号dno字段，数据类型为int
ALTER TABLE test.`sclass`
ADD COLUMN dno INT;

-- 添加系主任字段downer
ALTER TABLE test.`sclass`
ADD downer CHAR(30);

举例：修改test数据库的表sclass的字符集为utf8
-- 语法
ALTER TABLE 库名.表名 CONVERT TO CHARACTER SET utf8；

ALTER TABLE test.`sclass` CONVERT TO CHARACTER SET utf8;

-- convert 转换

3、删除表
-- 语法：
DROP TABLE 库名.表名；

-- Drop 删除
举例：删除test数据库中的表sclass
DROP TABLE test.`sclass`;

#特别说明：Mysql中，库名、表名、字段名都不允许重名；表中的数据，如果表中已经添加主键约束或者唯一性约束的情况下，数据不可以重复。
八、表的约束
1、表的约束（constraint）【6种】
说明：mysql常用的约束有如下6种：
作用：约束是一种表级别的限制，它通过对表的行或列的数据做出限制，保证数据的完整性和一致性
#非空（NOT NULL）约束
-- 非空约束，指定某列字段不能为null值；mysql中的NULL值，
-- 意思是不确定，没有具体值；所有数据类型的值都可以是null，包括int、float、char等；
-- 空字符串不等于null，0也不等于null；null不区分大小写。

举例：在test库创建表classa,将cname字段设置为非空
CREATE TABLE test.classa
(
cid INT, -- 班号
cname CHAR(30) NOT NULL -- 班级名称，添加非空约束
);

INSERT INTO test.`classa`(cname) VALUES(NULL); -- 执行失败 
-- 添加了非空约束，就不能插入null值

#唯一性（Unique）约束
唯一性约束用来限制不受主键约束的列上的数据的唯一性；同一个表中可以有多个唯一键。
Unique：唯一
唯一性键特点：可以为空，数据不允许重复，一个表中可以有多个唯一键
举例：在test库中创建表stu(sno,sname,cowner(班主任),cno),
将字段cowner定义为唯一键【意思是为字段cowner添加唯一性约束】
CREATE TABLE test.stu
(
sno INT,
sname CHAR(20),
cowner CHAR(30) UNIQUE, -- 定义唯一性约束
cno INT
);

INSERT INTO test.`stu`(sno,sname,cowner,cno) VALUES(1,"zs","ldh",2);
INSERT INTO test.`stu`(sno,sname,cowner,cno) VALUES(2,"ls","zxy",3);
#主键（Primary Key，简称PK）约束
-- 主键(PRIMARY KEY)是用于约束表中的每一行，主键字段，对于表中的数据来说，就类似于一个人的身份证号，不允许重复。
-- 定义主键的作用：避免表中的数据重复。
-- 特殊说明：一个表只能有一个主键，这个主键可以由一个字段构成，也可以由多个字段构成。
-- 主键特点：唯一(unique)，非空(not null)，可以由单个字段或者多个字段构成，一个表只能有一个主键
-- PRIMARY：主要的，基本的
-- KEY：键，关键字
-- 往表中插入数据时，违反主建约束，系统会做如下提示：
-- Duplicate entry '1' for key 'PRIMARY'：由于主键原因导致数据重复。
-- Duplicate 重复
举例：在schoola库，创建teacher表，
将表中的字段tid定义为主键【意思是为字段tid添加唯一性约束】

CREATE TABLE schoola.teacher
(
tid INT PRIMARY KEY, -- 定义主键约束
tname CHAR(30)
); 

-- 当主键由多个字段构成时，称为复合主键；-- 面试题
举例：在schoola数据库中，创建表stu7，表中包括字段学号sid,名称sname,
分数score,班号cno，将字段sid和sname设置为复合主键。
CREATE TABLE schoola.stu7
(
sid INT,
sname CHAR(20),
score FLOAT(5,2),
cno INT,
PRIMARY KEY(sid,sname) -- 定义复合主键
);
INSERT INTO schoola.`stu7`(sid,sname,score,cno)
VALUES(1,"ls",100,2);
-- 作为主键的两个字段的值同时重复，数据才叫重复

INSERT INTO schoola.`teacher`(tid,tname) VALUES(2,"zs");

#外键(Foreign Key,简称FK)约束
/*外键(FOREIGN KEY)既能确保数据完整性，也可以用来建立一个表和另一个表的直接关系；
一个表可以有一个或者多个外键，每个外键的数据必须REFERENCES引用另一个表的主键或者唯一键
添加了外键约束的列，取值必须在它引用的列（另一个表的主键或者唯一键）中有对应值。*/

举例：在schoola库中:
创建班级表classa[ 班级编号cid 主键，班级名称cname]
创建表student[学号sid,学生名称sname,班号classno,classno定义为外键]
-- 创建班级表

CREATE TABLE schoola.classa
(
cid INT,
cname CHAR(30),
PRIMARY KEY(cid) -- 定义主键
);

-- 创建student表
CREATE TABLE schoola.`student`
(
sid INT,
sname CHAR(30),
classno INT,
FOREIGN KEY(classno) REFERENCES schoola.classa(cid) -- 定义外键约束
);
-- 说明：当两个表建立了数据引用关系时，被引用数据的表，称为主表，
-- 引用其他表数据的表，称为从表
-- 当往表中，插入数据时，需要先往主表插入数据，才可以往从表插入数据，否则，报错
INSERT INTO schoola.`classa`(cid,cname) VALUES(1,"一班");
INSERT INTO schoola.`student`(sid,sname,classno) VALUES(1,"zs",1);
-- student表中classno的数据引用自classa表的cid字段的数据

#默认值（Default）约束
DEFAULT 约束用于向列中插入默认值。
-- 如果表中的一个字段，在建表时，设定了默认值，
-- 往表中插入数据时，插入语句中没有指定插入的值，那么会将默认值插入到表中；
-- 如果指定了插入的值，那么将插入指定的值。

举例：创建表wclass,将cno字段设置为主键，为字段cowner设置默认值为“zhangxueyou”
CREATE TABLE test.wclass
(
cno INT PRIMARY KEY,
cowner CHAR(30) DEFAULT "zhangxueyou" -- 定义默认值约束
);

INSERT INTO test.`wclass`(cno,cowner) VALUES(1,"liudehua");
INSERT INTO test.`wclass`(cno) VALUES(2);

#主键字段自增长 auto_increment
-- auto 自动
-- increment 增长
举例：在test库创建表stua[sid 主键 添加主键字段自增长约束,sname]

CREATE TABLE test.stua
(
sid INT PRIMARY KEY AUTO_INCREMENT, -- 添加自增长约束
sname CHAR(20)
);

INSERT INTO test.`stua`(sid,sname) VALUES(1,"sz");


INSERT INTO test.`stua`(sname) VALUES("ls");
 -- 插入数据时，sid的字段没有给值，自动在上一条数据的基础上增加1

2、表的修改
-- 说明：修改表，就是对表的表名、字段【添加、修改、删除】、字段类型、字段长度、约束等进行修改。
2.1 表的名称修改
-- 修改表名语法：
ALTER TABLE 库名.表名 RENAME TO 新表名;

举例：修改test库中stu表名称为sstuu
ALTER TABLE test.`stu`
RENAME TO sstuu;

2.2 修改表的字段或者约束（修改表结构）

-- 建销售表sales表以备练习：
在test数据库中创建销售表sales：
CREATE TABLE test.sales
(customer_no INT(10),
customer_name CHAR(30),
sales_no INT(10),
product_name VARCHAR(30));

2.2.1 为表添加主键约束
 -- 语法：
ALTER TABLE 库名.表名
ADD PRIMARY KEY(字段名);

举例1： 
修改sales表，为字段customer_no添加主键约束
ALTER TABLE test.`sales`
ADD PRIMARY KEY(customer_no);

举例2：删除sales表的主键customer_no
ALTER TABLE test.`sales`
DROP PRIMARY KEY;   -- 删除主键
-- 语法：
ALTER TABLE 库名.表名
DROP PRIMARY KEY; 

INSERT INTO test.`sales`(customer_no) VALUES(NULL);

举例：修改test库的字段customer_no的非空约束
ALTER TABLE test.`sales`
MODIFY customer_no INT(10) NULL;

2.2.2 为表添加非空约束
举例3：修改sales表约束，使客户名称sales_no不允许为空
-- 语法：
ALTER TABLE 库名.表名
MODIFY 字段名 数据类型(数据长度) NOT NULL;
-- modify 修改

ALTER TABLE test.`sales`
MODIFY sales_no INT(10) NOT NULL;

2.2.3 修改表中某个字段的名称
举例4：修改表sales，将字段`product_name`名称修改为pct_name
-- 语法：
ALTER TABLE 库名.表名
CHANGE 原字段名 新字段名  数据类型(数据长度);
-- 解释：change为更换的意思

ALTER TABLE test.`sales`
CHANGE product_name pct_name VARCHAR(30);

2.2.5 修改表中指定字段的数据类型和数据长度
举例6：修改sales表中字段`pct_name`的字段类型为char(20)
-- 语法：
ALTER TABLE 库名.表名
MODIFY [COLUMN] `字段名` 新数据类型(新数据长度);

ALTER TABLE test.`sales`
MODIFY pct_name CHAR(20);

2.2.6 删除表中指定的字段
举例7：删除表sales中的字段pct_name
-- 语法：
ALTER TABLE 库名.表名
DROP 【COLUMN】 字段名;  -- column可以省略

ALTER TABLE test.`sales`
DROP pct_name; 

3、复制表
3.1只复制表结构,不复制数据
-- 语法：
CREATE TABLE 库名.表名1 LIKE 库名.表名2；
意思：创建一个类似于表名2的表名1。
like：像，类似

举例:在test库中，创建一个表wclass1，表结构从wclass表复制
CREATE TABLE test.wclass1 LIKE test.`wclass`;

3.2同时复制表结构和数据
-- 语法：
CREATE table库名.表名1 [AS] SELECT * FROM 库名.表名2;

举例：在hrdb数据库，创建一个表temp，表结构和数据从employees表复制
CREATE TABLE hrdb.temp AS SELECT * FROM hrdb.employees;