MySql的使用和DML

MySQL的使用和DML

序章：操作数据库>操作数据库中的表>操作表中的数据

一、操作数据库

SQL语句不区分大小写

1. 创建数据库

   create database [if not exists] westos;

2. 删除数据库

   drop database [if exists] westos;

3. 使用数据库

   --如果表名或字段名是一个特殊字符，就需要带``
   use `school`

4. 查看数据库

   show databases;--查看所有的数据库

二、操作数据库中的表

1.数据库的数据类型（常用的）

数值

• int

• decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

字符串

• varchar 可变的字符串 0~65535 常用的变量

• text 文本串 2^16-1 保存大文本

日期

• datatime

• timestamp 时间戳

Null

• 没有值，未知

• 注意，不要使用Null进行运算，结果为Null

2.数据库的字段属性（重点）

Unsigned：

• 无符号的整数

• 声明 该列不能为负数

Zerofill：

• 0填充

• 不足的位数，使用0来填充，比如int(3) 5 00005

自增

• 在上一条记录的基础上+1（默认的）

• 通常用来设计表的主键，id，必须是整数类型

• 可以自定义自增的起始值和步长

非空 Not Null

• Null 如果不填写值，默认就是Null

• 如果设置Not Null ，如果不赋值，就会报错

默认

• 设置默认的值

• 如果不填写值，则会有默认的值

拓展：

/*每一个表，都必须存在以下5个字段！未来做项目，表示一个记录存在的意义。
id  主键
`version`   乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

3.新建一个表

CREATE TABLE IF NOT EXISTS student(
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
 `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
 `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
 `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
 `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
 PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
​
show create database school --查看创建数据库的语句
show create table student --查看创建表的语句
desc student --查看表的结构
​

InnoDB和MyISAM的区别 事务支持，数据行锁定，外键约束，全文索引，表空间的大小 不同

常用的操作

MYISAM:节约空间，速度较快

INNODB：安全性高，支持事务的处理，多表多用户查询

4.修改表

--修改表名
ALTER TABLE student RENAME AS emp 
--增加新的字段
ALTER TABLE emp ADD cid INT(4)
--修改字段的名字，需要带上修改字段的数据类型
ALTER TABLE emp CHANGE cid did INT(4)
--修改字段的数据类型和属性
ALTER TABLE emp MODIFY `name` VARCHAR(50)

5. 删除表

--删除表的字段
ALTER TABLE student DROP cid;
--删除表
DROP TABLE IF EXISTS emp

重要点：

1. 使用``包裹字段名

2. 注释 -- /**/

3. Sql语句不区分大小写

4. 所有的符号都用英文。

三、MySql的数据管理

1.外键约束

• 第一种方式

--新建一张年级表
CREATE TABLE IF NOT EXISTS `grade`(
 `gradeid` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级id',
 `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
 PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
--新建一张学生表，引用年级id做外键
CREATE TABLE IF NOT EXISTS `student`(
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
 `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
 `gradeid` INT(4) NOT NULL COMMENT '年级id',
 `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
 `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
 `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
 PRIMARY KEY (`id`),
 KEY `FK_gradeid` (`gradeid`),
 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

• 第二种方式

--建grade表
CREATE TABLE IF NOT EXISTS `grade`(
 `gradeid` INT(4) 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(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
 `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
 `gradeid` INT(4) NOT NULL COMMENT '年级id',
 `birthday` DATETIME DEFAULT 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`)

提醒：删除被引用的表，需要先删除引用的表。所以我们开发基本上不使用外键约束，如果有需要，我们可以通过程序来实现。

2.DML语言（全部记住）

数据操作语言，添加，修改，删除

添加

INSERT INTO `student` (`name`,`gradeid`)
 VALUES ('张三',3),('李四',2),('王五',2),('赵六',4),('田七',1)
--insert into 表名 ([字段1，字段2，字段3]) values ([值1，值2，值3]),([值1，值2，值3])
INSERT INTO `grade` (`gradename`) VALUES ('大三'),('大四'); 

修改

--update 表名 set 字段名=value,[字段名=value] where [条件]
UPDATE `student` SET `gradeid`=gradeid+1; --不指定条件，会改变所有数据
--数据库时间 current_time:2021-12-08 13:01:10
UPDATE student SET birthday = CURRENT_TIME WHERE id = 1;

删除

--delete from 表名 where [条件]
DELETE FROM student WHERE id = 3;
--没有条件，会全部删除(避免这样使用)
DELETE FROM student;

--清空表
TRUNCATE TABLE student

delete和truncate的区别

• 相同点：都可以清空表中的数据，不会改变表的结构。

• 不同点：truncate速度快，并且重置自增量。不会影响事务。