SQLyog基本操作(三)-表(修改、删除)、外键、DML、添加insert、修改update、删除delete数据

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值

操作符含义举例结果
= 等于 5=6 false
> 大于 5>6 false
< 小于 5<6 true
>= 大于等于 5>=6 false
<= 小于等于 5<=6 true
<> 或 != 不等于 5<>6 或 5!=6 true
BETWEEN 条件1 AND 条件2 在某个范围内 [1,5]  
AND && 1>2 && 3<4 false
OR || 1>2 || 3<4 true

注意:

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

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

  • 值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 继续从上一个自增量开始(因为是存在文件中的,不会丢失)

posted @ 2021-06-08 19:26  Coder_Cui  阅读(5352)  评论(0编辑  收藏  举报