MySQL 数据库中的基础操作
数据库中的表操作
1、创建表
表的表名命名规则：
-- 数据库表命名规则重要说明：
-- (1)数据库表名称可以支持大写字母A-Z,小写字母a-z,数字0-9,字符包括下划线 _ ,可以组合使用;
-- (2) 数据库表名称一般以字母开头;
-- (3)数据库表名称不建议以纯数字命名;
-- (4) 数据库表名称在1-30 个字符之间;
-- (5) 数据库表名称可以支持中文，但不建议使用中文命名。
语法：
CREATE TABLE 库名.表名（字段名1 字段类型1（字符长度），字段名2 字段类型2（字符长度2））；
举例：在school库中，创建表班级表sclass，表中有字段班级编号cno,数据类型为int，长度为5，班级名称cname,数据类型为char,长度为20，系号为dno，数据类型为int，长度为3
-- 建表准备，先建一个数据库
CREATE DATABASE school DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;-- 创建sclass表
CREATE TABLE school.sclass ( cno INT(5), cname CHAR(20), dno INT(3) );
2、修改表
-- 说明：修改表就是修改表的表名、增加字段、删除字段、修改数据类型及数据长度、修改表的约束等。
为一个表增加字段（interview）
语法：
ALTER TABLE 库名.表名
ADD COLUMN 字段名 数据类型（数据长度）;
举例：为招生表增加字段zsbh(招生老师编号)，int型，长度为4.
ALTER TABLE 51testing.`zhaosheng` ADD COLUMN zsbh INT(4); DESC zhaosheng; -- 查看表结构
3、删除表
语法：
DROP TABLE 库名.表名；
举例：删除school数据库中的表sclass
DROP TABLE school.`sclass`; SHOW TABLES; -- 查看表
4、复制表(只复制表结构，不复制数据)
（interview）
语法：
CREATE TABLE 库名.表名1 LIKE 库名.表名2；
意思：创建一个类似于表名2的表名1。
-- like 类似
举例：创建一个表zhaosheng1，表结构从表zhaosheng复制
CREATE TABLE test.`zhaosheng1` LIKE test.`zhaosheng`;
5、复制表（复制表结构+数据）
（interview）
语法：
CREATE TABLE 库名.表名1 [AS] SELECT * FROM 库名.表名2;
-- 说明 as关键字可以省略
举例：在test数据库中，创建一个表zhaosheng2，表结构和数据从zhaosheng表复制
-- 方法一 CREATE TABLE test.`zhaosheng2` AS SELECT * FROM test.`zhaosheng`; -- 方法二 CREATE TABLE test.`zhaosheng2` SELECT * FROM test.`zhaosheng`;
MySQL数据类型
1、数值型
CREATE TABLE test.stu ( sno TINYINT(4), sno1 SMALLINT(4), sno2 MEDIUMINT(4), sno3 INTEGER(4), sno4 BIGINT(4), sno5 INT(4), fenshu FLOAT(6,2), height FLOAT(6,2), fenshu1 DECIMAL(6,2) );INSERT INTO test.`stu`(sno,sno1,sno2,sno3,sno4,sno5,fenshu,height,fenshu1) VALUES(10000000,10000000,10000000,10000000,10000000,10000000,150,180.89,147.56); SHOW WARNINGS -- 查看告警信息TINY：微小
SMALL：小
MEDIUM：中等的
INTEGER：整数，
int 为 integer 的简写
Bigint：大-- zerofill 和unsigned（二者配合使用） CREATE TABLE test.aa(salary MEDIUMINT(5) UNSIGNED ZEROFILL);-- unsigned 关键字: 无符号类型，该字段的值只能是非负数，该关键字只对整数有效。
-- zerofill 关键字:默认无符号，零填充，指的是位数固定，插入的数据如果长度不足所定义 字段类型的长度时，mysql 则自动在数据的左侧使用 0 来补充；
INSERT INTO test.`aa`(salary) VALUES(300); -- 300位数不足5为，插入数据后，由于插入数据时存在zerofill关键字，mysql自动在300前加2个0.-- 一般 unsigned 关键字和 zerofill(零填充) 关键字搭配在一起使用。
Zero：零的意思
Fill：填充
2、字符型
CREATE TABLE test.student ( sname CHAR(10), sname1 VARCHAR(10), sname2 TINYBLOB, sname3 TINYTEXT, sname4 BLOB ); INSERT INTO test.`student`(sname,sname1,sname2,sname3,sname4) VALUES("张三","普拉即米尔.京","要求编写SQL语句，在数据库student表中进行如下操作" ,"要求编写SQL语句，在数据库student表中进行如下操作", "要求编写SQL语句，在数据库student表中进行如下操作");
3、日期时间型
CREATE TABLE test.cc(hire_date DATETIME);
举例：往cc表中插入入职日期 方法一、直接插入日期时间 INSERT INTO test.`cc`(hire_date) VALUES("2020-08-08 23:20:12"); 方法二、借助日期时间操作函数sysdate()、now()插入日期时间 INSERT INTO test.`cc`(hire_date) VALUES(SYSDATE());-- 插入当前日期时间 INSERT INTO test.`cc`(hire_date) VALUES(NOW());-- 插入当前日期时间 INSERT INTO test.`cc`(hire_date) VALUES("NOW()");-- now()两边带引号，将导致插入数据异常
4、混合型
CREATE TABLE test.xuanke (sid INTEGER(5), sname VARCHAR(20), sub SET("语文","数学","英语"), -- subject 科目 sex ENUM("M","F") ); # 举例：往选课表xuanke中插入一条数据： # 学号sid为1，姓名sname为张三，选择的科目有语文，数学 # 学生性别男。 INSERT INTO test.`xuanke`(sid,sname,sub,sex) VALUES(1,"张三","语文,数学","M");
表的约束及修改
1、表的约束（constraint）
mysql常用的约束有如下6种：
作用：约束是一种表级别的限制，它通过对表的行或列的数据做出限制，保证数据的完整性和一致性
1.1 主键约束（Primary Key，简称PK）
主键约束特点：
(1)表中添加了主键约束，数据将不允许重复;
(2)主键字段的值不允许为空（主键字段的值不能为null）;
(3)一个表中，只能有一个主键；
(4)主键可以由一个字段构成，也可以由多个字段构成；
-- 举例：在test数据库中，创建客户表customer，表中包括（客户编号cid，客户名称cname -- ）等字段，建表时，将cid字段定义为主键 方法一、 CREATE TABLE test.customer ( cid INT(4) PRIMARY KEY, -- 为cid字段添加主键约束 cname CHAR(30) ); 方法二、 CREATE TABLE test.customer1 ( cid INT(4), cname CHAR(30), PRIMARY KEY(cid) -- 为cid字段添加主键约束 );
-- 当主键由多个字段构成时，称为复合主键； 举例：test数据库中，创建表stu1，表中包括字段学号sid,名称sname,分数score,班号cno， 将字段sid和sname设置为复合主键。 CREATE TABLE test.stu1 ( sid INTEGER(5), sname VARCHAR(40), score FLOAT(4,2), cno INT(4), PRIMARY KEY(sid,sname) -- 将sid和sname设置为复合主键 );
1.2 非空约束(NOT NULL)
说明：非空约束，表中的某个字段如果添加了非空约束，那么该字段的数据不能为null
-- 举例：在test数据库，创建表classa,将cno字段设置为主键，将cname设置为非空 CREATE TABLE test.classa ( cno INT(4) PRIMARY KEY, cname CHAR(30) NOT NULL ); INSERT INTO test.`classa`(cno,cname) VALUES(3,"终极一班"),(4,NULL); -- 因为字段cname添加了非空约束，不能插入null，所以cname字段插入异常 INSERT INTO test.`classa`(cno,cname) VALUES(NULL,"终极一班1"); -- 因为主键约束不能为空，所以插入数据失败，系统提示“Column 'cno' cannot be null”。 SHOW WARNINGS;
1.3唯一性（Unique）约束
唯一性约束特点：可以为空，数据不允许重复，一个表中可以有多个
-- 举例：在test库中创建表stu2(sno,sname,cowner（班主任）,cno),将字段sno定义为主键， 将字段cowner定义为唯一键 CREATE TABLE test.stu2 ( sno INT(4) PRIMARY KEY, sname CHAR(20), cowner CHAR(20) UNIQUE, -- 班主任字段添加了唯一性约束 cno INT(5) ); INSERT INTO test.stu2(sno,sname,cowner,cno) VALUES(1,"zs","黎明",3),(2,"zs1","黎明",4); -- 由于班主任字段添加了唯一性约束， -- 插入的两条数据班主任都是黎明，所有数据重复，违反唯一性约束，插入失败
1.4外键（Foreign Key，简称FK）约束
外键特点：
(1)一个表中可以由多个外键
(2)一个表【从表】中的外键字段的数据，引用自另一个表【主表】的主键或者唯一键
-- 举例：在test数据库中，创建学生表student1，表中有学生编号字段sid，学生名称字段sname，班级编号字段cno，cno字段的数据，引用自classa表中cno字段的数据。 CREATE TABLE test.student1 -- 从表 ( sid INT(4) PRIMARY KEY,-- 为sid字段添加主键约束 sname CHAR(30), cno INT(4), FOREIGN KEY(cno) REFERENCES test.`classa`(cno)-- student1表的外键cno数据 );-- 引用自classa表的主键字段cno的值 INSERT INTO test.`student1`(sid,sname,cno) VALUES(1,"张学友",1); INSERT INTO test.`student1`(sid,sname,cno) VALUES(2,"张学友1",3);
总结：
(1)往表中插入数据时，需要先往主表中插入数据，再往从表中插入数据，否则出错。
(2)删除数据时，需要先删除从表中的数据，再删除主表中的数据。
1.5 默认值（Default）约束
说明：DEFAULT 约束用于向列中插入默认值，如果插入数据时，一个字段如果没有给值，那么会将默认值添加到表中。
-- 举例：在test数据库中，创建表wclass,将cno字段设置为主键，为字段cowner设置默认值为“郭富城”。 CREATE TABLE test.wclass ( cno INT(4) PRIMARY KEY, cowner CHAR(30) DEFAULT "郭富城" ); INSERT INTO test.`wclass`(cno,cowner) VALUES(1,"刘德华"); INSERT INTO test.`wclass`(cno) VALUES(2); -- 只插入cno字段的值，cowner字段的值默认被插入郭富城
1.6 主键字段自增长 AUTO_INCREMENT
-- 举例：在test库中，创建表食物表foods，包括字段食物编号fid，fid为主键，食物名称fname，其中为fid字段添加自增长约束auto_increment. CREATE TABLE test.foods ( fid INT(4) PRIMARY KEY AUTO_INCREMENT, -- 为fid字段添加主键约束和自增长约束 fname CHAR(20) ); DESC foods; -- 查看表结构 INSERT INTO test.`foods`(fid,fname) VALUES(1,"cake"); INSERT INTO test.`foods`(fname) VALUES("lychee"); -- 从插入第二条数据开始，如果只插入fname字段的数据时，fid字段的值 -- 会被自动插入，并且是在上一条数据的基础上自动增长1.
2、 查看一个表的键值（主键等）
SHOW KEYS FROM 库名.表名；
-- 举例：查看hrdb库员工表（employees）表的外键有哪些 SHOW KEYS FROM hrdb.`employees`; SHOW KEYS FROM test.`classa`;
3、表的修改
对表的表名、字段、字段类型、字段长度、约束等进行修改。
3.1 表的名称修改
语法：
ALTER TABLE 库名.表名
RENAME TO 新表名;
-- 举例：将foods表的名字修改为空food ALTER TABLE test.`foods` RENAME TO food;
3.2 修改表的字段或者约束（修改表结构）
3.2.1 为表添加主键约束
语法：
ALTER TABLE 库名.表名
ADD PRIMARY KEY(字段名);
-- 为表aa中的字段salary 添加主键约束 ALTER TABLE test.`aa` ADD PRIMARY KEY(salary); DESC test.`aa`; -- 查看添加主键约束结果
