2.6 修改、删除表

修改表的属性

 -- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
 ALTER TABLE teacher RENAME AS teacher1
 ?
 -- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
 ALTER TABLE teacher1 ADD age INT(11)
 ?
 -- 修改表的字段（重命名，修改约束！）
 -- ALTER TABLE 表名 MODIFY 字段名 列属性[]
 ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
 -- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
 ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名
 ?
 -- 删除表的字段
 -- ALTER TABLE 表名 DROP 字段名
 ALTER TABLE teacher1 DROP age1
 ?

删除表

 -- 删除表（存在的时候再删除，不加判断的话，会报错）
 DROP TABLE IF EXISTS teacher1

注意点：

• 所有的创建和删除操作尽量加上判断，以免报错！！！

• 字段名使用``修饰，避免与关键字重复！！！

• 注释：-- 或者 /* */

• sql关键字大小写不敏感，建议大家写小写！！！

• 所有的符号全部用英文！！！

3 MySQL数据管理

3.1 外键（了解即可）

方式1：在创建表的时候，增加约束（麻烦，比较复杂）

 CREATE TABLE IF NOT EXISTS `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘年级id‘,
   `gradename` VARCHAR(50) NOT NULL COMMENT ‘年级名称‘,
   PRIMARY KEY(`gradeid`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8
 -- 学生表的gradeid字段要去引用年级表的gradeid
 -- 定义外键key
 -- 给这个外键添加约束（执行引用） references引用
 CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
   `name` VARCHAR(20) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
   `pwd` VARCHAR(30) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
   `sex` VARCHAR(2) NOT NULL DEFAULT ‘女‘ COMMENT ‘性别‘,
   `birthday` DATETIME DEFAULT NULL COMMENT ‘出生日期‘,
   `gradeid` INT(10) NOT NULL COMMENT ‘学生年级‘,
   `address` VARCHAR(100) DEFAULT NULL COMMENT ‘家庭住址‘,
   `email` VARCHAR(50) DEFAULT NULL COMMENT ‘邮箱‘,
   PRIMARY KEY(`id`),
   KEY `FK_graded`(`gradeid`),
   CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8

　　添加外键成功后，会多出来定义的外键FK_gradeid

　　改变表student，选择“3个外部键”

　　即可查看外部键

　　删除有外键关系的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

　　对于上面而言，要先删除student表，才能删除grade表。

方式2：创建表成功后，再添加外键约束

 -- 创建grade表
 CREATE TABLE IF NOT EXISTS `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘年级id‘,
   `gradename` VARCHAR(50) NOT NULL COMMENT ‘年级名称‘,
   PRIMARY KEY(`gradeid`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8
 ?
 -- 创建student表
 CREATE TABLE IF NOT EXISTS `student`(
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号‘,
   `name` VARCHAR(20) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘姓名‘,
   `pwd` VARCHAR(30) NOT NULL DEFAULT ‘123456‘ COMMENT ‘密码‘,
   `sex` VARCHAR(2) NOT NULL DEFAULT ‘女‘ COMMENT ‘性别‘,
   `birthday` DATETIME DEFAULT NULL COMMENT ‘出生日期‘,
   `gradeid` INT(10) NOT NULL COMMENT ‘学生年级‘,
   `address` VARCHAR(100) DEFAULT NULL COMMENT ‘家庭住址‘,
   `email` VARCHAR(50) DEFAULT NULL COMMENT ‘邮箱‘,
   PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8
 ?
 -- 创建表的时候没有外键关系，之后添加外键关系
 ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
 -- ALTER TABLE `要添加引用的表名` ADD COSTRAINT `约束名` FOREIGN KEY(`要作为外键的列`) REFERENCES `被引用的表名`(`被引用的字段`)

　　以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰，这里了解即可）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）

• 我们想使用多张表的数据，想使用外键（程序去实现）

附：数据库中为什么不推荐使用外键约束？_pojocheng的博客-CSDN博客 https://blog.csdn.net/pojocheng/article/details/85760648

3.2 DML语言（全部记住）

数据库意义：数据存储 、数据管理

DML语言：数据操纵语言

• Insert

• update

• delete

3.3 添加insert

语法：insert into 表名(字段名1,字段名2,...) values(‘值1‘,‘值2‘,...)，(‘值1‘,‘值2‘,...)，...

 -- 语法:insert into `表名`(`字段名1`,`字段名2`,...) values(‘值1‘,‘值2‘,...)，(‘值1‘,‘值2‘,...)，...
 -- 插入语句（添加）
 -- insert into `表名`(`字段名1`,`字段名2`,...) values(‘值1‘,‘值2‘,...)
 INSERT INTO `grade`(`gradename`)VALUES(‘大四‘)
 ?
 -- 如果不写表的字段，它就会一一匹配，所有字段值都必须写全，此时主键值不能省略
 INSERT INTO `grade`VALUES(‘研一‘) -- 报错
 INSERT INTO `grade`VALUES(5,‘研二‘) -- 成功
 ?
 -- 一般写插入语句，我们一定要使数据和字段一一对应！
 -- 插入一个字段多个值，主键自增可以忽略
 INSERT INTO `grade`(`gradename`)VALUES(‘大三‘),(‘大二‘)
 ?
 INSERT INTO `student`(`name`)VALUES(‘赵四‘) -- 插入一个字段
 INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES(‘张三‘,‘111111‘,‘男‘) -- 插入多个字段
 ?
 -- 插入全部字段，不省略字段名
 INSERT INTO `student`(`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`)
 VALUES(‘王五‘,‘222222‘,‘男‘,‘2020-02-02‘,‘3‘,‘吉林‘,‘111@163.com‘),
 (‘小黄‘,‘222222‘,‘男‘,‘2020-02-02‘,‘3‘,‘吉林‘,‘111@163.com‘),
 (‘小红‘,‘222222‘,‘男‘,‘2020-02-02‘,‘3‘,‘吉林‘,‘111@163.com‘)
 ?
 -- 插入全部字段，省略全部字段名，此时不可以省略主键值
 INSERT INTO `student`VALUES
 (4,‘王七‘,‘333333‘,‘男‘,‘2000-01-01‘,‘8‘,‘黑龙江‘,‘222@qq.com‘)
 -- 插入多个字段多个值，忽略字段名，此时不可以省略主键值
 INSERT INTO `student`VALUES
 (5,‘1‘,‘1‘,‘男‘,‘2001-01-01‘,‘11‘,‘1‘,‘1@qq.com‘),
 (6,‘2‘,‘1‘,‘男‘,‘2002-01-01‘,‘12‘,‘1‘,‘2@qq.com‘),
 (7,‘3‘,‘1‘,‘男‘,‘2003-01-01‘,‘13‘,‘1‘,‘3@qq.com‘),
 (8,‘4‘,‘1‘,‘男‘,‘2004-01-01‘,‘14‘,‘1‘,‘4@qq.com‘),
 (9,‘5‘,‘1‘,‘男‘,‘2005-01-01‘,‘15‘,‘1‘,‘5@qq.com‘),
 (10,‘6‘,‘1‘,‘男‘,‘2006-01-01‘,‘16‘,‘1‘,‘6@qq.com‘)

注意事项：

• 字段和字段之间用英文“,”隔开

• 字段是可以省略的，但是后面的值要一一对应，不能少，字段都省略时，主键值就不能忽略

• 可以同时插入多条数据，values后面的值需要使用“,”隔开即可

3.4 修改update

语法：update 表名 set 字段名1=‘值1‘,字段名2=‘值2‘,... where 条件

 -- 语法：update `表名` set `字段名1`=‘值1‘,`字段名2`=‘值2‘,... where 条件
 -- 修改学员名字
 UPDATE `student` SET `name`=‘小红‘ WHERE id=1
 -- 不指定条件的情况下，会改动所有表
 UPDATE `student` SET `name`=‘小明‘ 
 -- 修改多个属性，之间用”,“隔开
 UPDATE `student` SET `name`=‘小红‘,`pwd`=‘123456‘ WHERE id=1
 -- 通过多个条件定位数据，修改多个属性
 UPDATE `student` SET `name`=‘刘壮实‘,`pwd`=‘654321‘ WHERE `id`=1 AND `sex`=‘男‘
 -- 值可以是一个具体的值，也可以是一个变量
 UPDATE `student` SET `birthday`=CURRENT_TIME,`sex`=‘女‘ WHERE `name`=‘刘壮实‘ AND `sex`=‘男‘

条件：where 字段名 运算符 匹配条件，操作会返回boolean值

注意：

• 字段名是数据库的列，尽量带上``

• 筛选的条件如果没有指定，则会修改所有的列

• 值value可以是一个具体的值，也可以是一个变量

• 多个设置的属性之间使用英文“,”隔开

3.5 删除delete

语法：delete from 表名 where 条件

 -- 删除数据（避免这样写，会全部删除），要指定条件
 DELETE FROM `student`
 ?
 -- 删除指定数据
 DELETE FROM `student` WHERE id =1

TRUNCATE命令：完全清空一个数据表，表的结构和索引约束不会变！

 -- 清空student表
 TRUNCATE `student`

DELETE和TRANCATE的区别

• 相同点：都能删除数据，都不会删除表结构

• 不同点：

  • TRANCATE 重新设置自增列，计数器会归零

  • TRANCATE 不会影响事务

 -- 测试delete和TRANCATE区别
 CREATE TABLE IF NOT EXISTS`test`(
   `id` INT(4) NOT NULL AUTO_INCREMENT,
   `col` VARCHAR(20) NOT NULL,
   PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8
 INSERT INTO `test`(`col`)VALUES(‘1‘),(‘2‘),(‘3‘)
 DELETE FROM `test` -- 不会影响自增
 TRUNCATE TABLE `test` -- 自增会清零

delete删除结果：创建表---高级，显示如下

TRANCATE删除效果：查看方式同上

以下内容了解即可：DELETE删除问题，重启数据库的现象

• INNODB 自增列会从1开始（因为是存在内存当中的，断电即失）

• MYISAM 继续从上一个自增量开始（因为是存在文件中的，不会丢失）

SQLyog基本操作（三）

原文：https://www.cnblogs.com/XiaoCui-blog/p/14864137.html