狂神说MySQL学习笔记

MySQL学习笔记

1.操作数据库

1.1数据库的列类型

数值:

• tinyint 1个字节

• smallint 2个字节

• mediumint 3个字节

• int 4个字节

• bigint 8个字节

• float 浮点数 4个字节

• double 浮点数 8个字节

• decimal 字符串形式的浮点数,金融计算是用到

字符串:

• char 字符串固定大小 0-255

• varchar 可变字符串 0-65535

• tinytext 微型文本 0-255

• text 文本串 0-65535

时间日期

• date YYYY-MM-DD 日期格式

• time HH: mm: ss 时间格式

• datetime YYYY-MM-DD HH: mm: ss 常用的时间格式

• timestamp 时间戳

• year 年份

1.2数据库的字段属性

Unsigned:无符号整数,声明该列不能为负数.

zerofill:0填充,不足的位数使用0填充.

Auto_Increment自增长:自动在上一条记录基础上加1

拓展

-- 每一个表都应该存在以下五个字段
​
id    主键
varsion      乐观锁
is_delete    伪删除
gmt_create   创建时间
gmt_update   修改时间

1.3创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '学生名字',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    PRIMARY KEY (`id`)-- 主键
)ENGINE=INNODB DEFAULT CHARSET=utf8 -- 引擎和字符集编码

常用查询创建语句:

SHOW CREATE DATABASE kuang;  -- 查看创建数据库的语句
SHOW CREATE TABLE student; -- 查看student数据表的定义语句
DESC student; -- 查看表结构

1.4数据库的类型

数据库引擎:

INNODB 默认使用

MYISAM 早些年使用5.5之前

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大,约为2倍

• MYISAM 节约空间,速度较快

• MYISAM 对应文件

  .frm 表结构的定义文件

  .MYD 数据文件(data)

  .MYI 索引文件(index)

• INNODB 安全性高,事务的处理,多表多用户操作

• INNODB 在只有一个.frm文件,以及上级目录下的ibdata1文件

1.5修改删除表

修改

-- 修改表名,ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE student RENAME AS student1;
​
-- 添加表字段,ALTER TABLE 表名 ADD 字段名 属性;
ALTER TABLE student ADD age INT(3);
​
-- 修改表的字段  (重命名,修改约束)
ALTER TABLE student MODIFY age VARCHAR(11);-- 修改约束
ALTER TABLE student CHANGE age age1 INT(3);-- 既能重命名也能修改约束
​
-- 删除字段
ALTER TABLE student DROP age1;

删除表:

-- 删除表
DROP TABLE IF EXISTS teacher;-- 如果存在

2.数据管理

2.1 外键

创建时添加外键:

key 外键名称 (添加外键的字段名)

CONSTRAINT 外键名 FOREIGN KEY (添加外键的字段名) REFERENCES 被引用的表名(被引用的字段名)

-- 定义外键key
-- key `外键名称` (`添加外键的字段名`)
​
-- 给这个外键添加约束(执行引用) references 引用
-- CONSTRAINT `外键名` FOREIGN KEY (`添加外键的字段名`) REFERENCES `被引用的表名`(`被引用的字段名`)
​
CREATE TABLE `grade`(
  `grade_id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `grade_name` VARCHAR(20) NOT NULL DEFAULT '一年级' COMMENT '年级名字',
  PRIMARY KEY (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
​
CREATE TABLE `student1` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '学生名字',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `grade_id` INT(4) NOT NULL COMMENT '年级id',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`grade_id`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

删除表示要先删除引用表,再删除被引用表

已经创建表后再添加外键:

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (添加外键的字段名) REFERENCES 被引用的表名(被引用的字段名);

CREATE TABLE `grade`(
  `grade_id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `grade_name` VARCHAR(20) NOT NULL DEFAULT '一年级' COMMENT '年级名字',
  PRIMARY KEY (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
​
​
CREATE TABLE `student1` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '学生名字',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `grade_id` INT(4) NOT NULL COMMENT '年级id',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
​
-- ALTER TABLE `表名` ADD CONSTRAINT `外键名` FOREIGN KEY (`添加外键的字段名`) REFERENCES `被引用的表名`(`被引用的字段名`);
ALTER TABLE `student1`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`);
​
​

2.2 DML(数据操作语言)

数据库意义:数据存储,数据管理

DML语言:数据操作语言

• insert

• update

• delete

2.21添加

• insert into 表名(字段名1,字段名2,....) values('值1','值2',...)字段名和值要一一对应

-- 插入数据 insert into 表名(字段名1,字段名2,....) values('值1','值2',...)   字段名和值要一一对应
INSERT INTO `grade` (`grade_name`) VALUES('大一');
​
-- 不写字段名则自动匹配
INSERT INTO `grade` VALUES(2,'大二');
​
-- 插入多个值  INSERT INTO 表名 (字段名1,字段名2) VALUES(值1,值2),(值1,值2),(值1.值2)....;
INSERT INTO `grade` (`grade_name`) VALUES('大三'),('大四');
INSERT INTO `grade` (`grade_id`,`grade_name`) VALUES(5,'大五'),(6,'大六');
​

2.22修改

• update 表名 set 字段名1=值1 where 作为条件的字段名=值;

UPDATE `grade` SET `grade_name`='研一' WHERE `grade_id`=5;
​
UPDATE `grade` SET `grade_name`='研二' WHERE `grade_name`='大六';
​

• 修改多个值 update 表名 set 字段名1=值1,字段名2=值2,字段名3-值3 where 作为条件的字段名 = 值;

UPDATE `grade` SET `grade_id`=7,`grade_name`='研三' WHERE `grade_name`='研二';

• 不指定条件会改变整个表的所有的数据!(不建议使用)

• 操作符

操作符	含义
=	等于
<>或!=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN ... AND ...	在某个范围
AND &&	同时为真,结果才为真
OR ||	一个为真,结果为真
Not !	非

2.23删除

• delete from 表名 where 条件

DELETE FROM `grade` WHERE `grade_id`=7;

• 不写条件则删除所有数据(不建议)

• 清空表数据 TRUNCATE 表名

TRUNCATE `grade`;

DELETE 和 TRUNCATE 区别:

• 相同点: 都能删除数据且不会删除表结构

• 不同:

  • TRUNCATE 重新设置自增长 计数器会归零

  • TRUNCATE 不会影响事务

  • DELETE 自增长不变

  • DELETE 会影响事务

• DELETE 删除重启数据库现象:

  • InnoDB 自增长会从1开始 (存在内存当中的,断电即失)

  • MyISAM 继续从上一个自增长开始 (存在文件中的,不会丢失)

3. DQL(Data Query LANGUAGE)查询数据

3.1 简单查询

-- 查询全部信息
SELECT * FROM `grade`;
​
-- 查询指定字段
SELECT `grade_id`,`grade_name` FROM `grade`;
​
-- 别名,给结果字段另起一个名字 AS
SELECT `grade_id` AS 年级编号,`grade_name` AS 年级名字 FROM `grade`;
​
-- 函数 Concat(a,b)拼接字符串
SELECT CONCAT('年级: ',`grade_name`) AS 新名字 FROM `grade`;-- 结果  年级: 大一

去重 DISTINCT

-- 去掉查询出来的结构中重复的数据,重复的数据只显示一条
SELECT DISTINCT `grade_name` FROM `grade`;

模糊查询

like 结合 %(代表任意字符) _(代表一个字符)

-- 查询名称包含'一'的数据
SELECT * FROM `grade` WHERE `grade_name` LIKE '%一%';
​
-- 查询名称以&