【MySQL】MySQL基础

MySQL数据类型

时间日期：

​ MySQL提供了两种日期类型，DateTime和TimeStamp,这两种类型是相似的。

​ DateTime能够保存从1001年到9999年的日期和时间，精度为秒，存储空间为8个字节

​ TimeStamp保存1970年1月1日午夜到2038年的秒数，存储空间为4个字节，和时区有关，不同时区下所代表的具体时间不同。默认情况下这个值会是当前时间。

字符串：

varchar:节省了存储空间，但在update的时候可能会使所在行变得更长，如果一个行占用的空间增长并且在页内没有更多的空间可以存储，就会被差分成不同的片段存储。

varchar(5)和varchar(100)存储相同长度的字符串的空间开销是一样的，但是更短的列会节省内存，所以最好分配刚好需要的空间大小

char:适合存储很短的字符串，或者所有值都接近同一个长度，经常需要变更的数据使用char比varchar更适合

varchar会保留字符串末尾的空格，而char会删除

存储引擎InnoDB和MyISAM

InnoDB是MySQL默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其他存储引擎

MyISAM对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

比较：

• InnoDB是事务型的，可以使用Commit和Rollbak语句，而MyISAM不支持事务
• MyISAM只支持表级锁，而InnoDB还支持行级锁
• InnoDB支持外键
• InnoDB是支持真正的在线热备份。而其他存储引擎不支持，要获取一致性视图需要停止对所有表的写入在读写混乱的情境下，停止写入可能会导致停止读取
• MyISAM崩溃后发生损坏的概率比InnoDB高很多，而且恢复的速度更慢
• MyISAM支持压缩表和空间数据索引

操作数据库

创建数据库

create DATABASE mysql_demo;

#加一个判断是否存在的条件 
create DATABASE IF Not EXISTS mysql_demo;

删除数据库

drop database mysql_demo;

drop database if EXISTS mysql_demo;

使用数据库

use `数据库名`

查看数据库

show DATABASES;

建表

create table if not EXISTS `student`(
id int not null AUTO_INCREMENT COMMENT '学号',
username varchar(30) not null DEFAULT '匿名' COMMENT '姓名',
password VARCHAR(20) not null COMMENT '密码',
sex varchar(2) not null DEFAULT'男' COMMENT'性别',
birthday datetime DEFAULT null COMMENT '出生日期',
primary key(`id`)
)ENGINE=INNODB  DEFAULT charset=utf8;

删表

drop table if exists student;

修改表

-- 修改表名
alter table student rename as user;

-- 增加字段
ALTER TABLE user add address varchar(100);
-- 修改字段(修改约束modify,重命名用change)
ALTER table user MODIFY sex int; 
alter table user change password pwd varchar(10);
-- 删除字段
alter table user drop address;

数据管理

外键

create table if not EXISTS `grade`(
gradeId INT(10) not null,
gradename varchar(50) not null,
primary key(`gradeId`)
)ENGINE=INNODB  DEFAULT charset=utf8;

-- 引用年级表的gradeId
-- 定义外键key
-- 给这个外键添加约束
create table if not EXISTS `student`(
id int not null AUTO_INCREMENT COMMENT '学号',
username varchar(30) not null DEFAULT '匿名' COMMENT '姓名',
password VARCHAR(20) not null COMMENT '密码',
sex varchar(2) not null DEFAULT'男' COMMENT'性别',
birthday datetime DEFAULT null COMMENT '出生日期',
`gradeId` int(10) not null,
primary key(`id`),
KEY `FK_gradeId` (gradeId),
CONSTRAINT `FK_gradeId`FOREIGN KEY(`gradeId`) REFERENCES `grade`(`gradeId`)
)ENGINE=INNODB  DEFAULT charset=utf8;

这都是物理外键，不推荐使用

DML语言(数据操作)

插入语句

insert into 表名([字段1,字段2,字段3]) values('值','值','值');
-- 主键自增可以省略，字段一一对应

修改语句

update `student`
set `name`='ks'
where id=1;
-- 不指定条件的情况下，会改动所有表！！！

删除语句

delete from `student` where id=1;

TRUNCATE

作用：完全清空一个数据库表，表的约束和索引约束不会变

TRUNCATE `student`;

与delete的区别

相同：都能删除数据且不会删除表结构

不同：TRUNCATE 重新设置自增列，delete不影响自增

DQL查询数据

查询指定字段

-- 起别名
select `id` as '学号',`name` as'姓名' from student as s

-- Concat(a,b)拼接字符串
select CONCAT('姓名',name) as 新名字 from student;

-- 去重 distict
-- 去除select查询出来的重复数据，只显示一条
select DISTINCT StudentNo from result;

-- 查看mysql系统版本
SELECT VERSION()

-- 用来计算
SELECT 100*3-1 as 计算结果;

Where条件子句

检索符合条件的值

SELECT studentNo,studentResult from result
where studentResult>80;

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为NULL，结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
LIKE	a like b	SQL匹配，如果a匹配b，则结果为真
IN	a in (a1,a2,a3…)	假设a在a1,或者a2…其中的某一个值中，结果为真

分页

limit 当前页,页面大小

limit 0,3 第一页的三个

limit 3,3 第二页的三个

limit 6,3 第三页的三个

连表查询

Join

(图片来自百度，侵删

操作	描述	例
left join	会从左表中返回所有的值，即使右表中没有匹配	from a left join b
inner join	如果表中至少有一个匹配就返回	from a inner join b
right join	会从右表中返回所有的值，即使左表没有匹配	from a right join b

SELECT s.studentNo,studentName,SubjectNo,StudentResult
from student as s
inner JOIN result as r
where s.studentNo=r.studentNo;


SELECT s.studentNo,studentName,SubjectNo,StudentResult
from student as s
RIGHT JOIN result as r
ON s.studentNo=r.studentNo

视图

创建视图

创建视图需要使用CREATE VIEW语句

create view 视图名称(<视图列名1>，<视图列名2>,......)
AS
<select语句>

select语句要写在as关键字之后，select语句中列的排列顺序和视图中列的排列顺序相同。

使用视图

select <视图列表1>，<视图列表2>
from 视图名称

创建出视图后，就可以通过非常简单的select语句，随时得到想要的汇总结果。并且当表更新之后，视图也会自动更新

之所以能实现上述功能，是因为视图就是保存好的select语句！

使用视图的查询

1. 首先执行定义的select语句
2. 根据得到的结果，再执行在From子句中使用视图的select语句

可以在视图的基础上创建视图，但是要尽量避免，多重视图会降低性能

视图的限制

• 不能使用Order By子句

因为视图和表一样，数据行是没有顺序的。

• 对视图进行更新有严格限制

在下列情况下可以进行更新：

1. select子句中未使用distinct
2. from子句中只有一张表
3. 未使用group by
4. 未使用having

删除视图

drop一下就可以啦

子查询

子查询就是一次性视图

子查询作为内层会首先执行

尽量避免使用多层嵌套的子查询，嵌套层数增加，sql语句会变得难以读懂，性能也会变差

标量子查询就是返回单一值的子查询

能够使用常数或者列名的地方，无论是select、group by、having还是order by

注意事项：决不能返回多行结果!

关联子查询

在对表中某一部分记录的集合进行比较时，就可以使用关联子查询 (在细分的组内进行比较时，需要使用关联子查询)

索引

索引是帮助mysql高效获取数据的数据结构

索引的分类

• 主键索引 primary key

  唯一的标识，主键不可重复

• 唯一索引 unique key

• 常规索引 key/index

• 全文索引 fullText