MySQL笔记

MySQL数据库

#数据库类型

*分类

• 关系型数据库：MySQL（开源）、Oracle（收费）、SQLServer、DB2

  数据以行和列存在磁盘中。

• 非关系性数据库：Redis、MongoDB

  数据以键值对存在内存中，存储经常用到的数据。

#数据类型

数值型

数据类型	范围	用途
tinyint	（-128，127）	微小整数型
smallint	（-32768，32767）	小整数型
mediumint	(-8 388 608，8 388 607)	中等整数型
int	(-2 147 483 648，2 147 483 647)	整数型
bigint	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	大整数型
float	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	单精度浮点型
double	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点型
decimal		小数值

decimal类型和double区别：

decimal所能储存的数比double大，decimal精确度更高；

字符串型

字符串类型	字节大小	描述	备注
char	0-255字节	定长字符串，char(10),定长10占位，不足补空格	char(4)存储4个字符，根据编码方式的不同占用不同的字节数
varchar	0-255字节	边长字符串，varchar(10)，最长10个字节，存储长度按照实际长度输入长度为准	varchar保存可变长度的字符串，使用额外的一个或两个字节存储字符串长度，varchar(10),除了需要存储10个字符，还需要1个字节存储长度信息(10),超过255的长度需要2个字节来存储
tinyblob	0-255字节	不超过255个字符的二进制字符串
tinytext	0-255字节	短文本字符串
blob	0-65535字节	二进制形式的长文本数据
text	0-65535字节	长文本数据
mediumblob	0-16777215字节	二进制形式的中等长度文本数据	存放图片等
mediumtext	0-16 777 215字节	中等长度文本数据	存放论坛发言等
longblob	0-4 294 967 295字节	二进制形式的极大文本数据	存放音视频
longtext	0-4 294 967 295字节	极大文本数据	存放文章等
varbinary(M)	M	允许长度0-M个字节的定长字节符串，值的长度+1个字节	varbinary保存变长的二进制字符串
binary(M)	M	允许长度0-M个字节的定长字节符串	binary保存二进制字符串，它保存的是字节而不是字符，没有字符集限制；binary(8)可以保存8个字符，每个字符占1个字节，共占8个字节

char、varchar、text比较：

1. 经常变化的字段用varchar
2. 知道固定长度的用char
3. 尽量用varchar
4. 超过255字符的只能用varchar或者text
5. 能用varchar的地方不用text
6. 按照查询速度：char最快，varchar次之，text最慢

日期时间型

类型	字节大小	格式	用途
date	4字节	YYYY-MM-DD	日期值
time	3字节	HH:MM:SS	时间值
year	1字节	YYYY	年份值
datetime	8字节	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4字节	YYYYMMDD HHMMSS	混合日期和时间值，即时间戳，用于自动存储INSERT或UPDATE操作时记录日期和时间

datetime与timestamp区别：

datetime与timestamp都是日期和时间的混合类型，区别在于：

1. 表示的取值范围不同，datetime的取值范围远远大于timestamp的取值范围
2. 将null插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间
3. 同一个timestamp类型的日期或时间，不同的时区，显示结果不同

混合型

数据类型	说明	举例
enum类型	enum类型是一个允许你输入可能值的列表，在处理相互排斥的数据时容易让人理解，比如人类的性别；enum类型在系统内部可以存储为数字，一个enum类型最多可以包含65536个元素，多选一；	ENUM(x,y,z)、ENUM(‘X’,‘Y’,‘Z’)
set类型	set数据是一个字符串对象，可以有零或多个值；多个值之间用逗号隔开，最多可以有64个不同的值；	SET(‘a’,‘b’,‘c’,‘d’)

enum和set异同点：

都是string类型，而且只能在指定的集合里取值，不同的是，应用程序从数据库表中读取数据时，set一次可以取多个值，enum每次只能取一个值；

#表的约束

*主键（primary key）约束

主键是用于约束表中的一行，作为这一行的标识符（这一行的唯一代表）,在一张表中通过主键就能准确定位到一行

定义主键的作用：避免表中的数据重复

主键特点：唯一（unique），非空（not null）

字段名 primary key,
或者最后
primary key(字段名)

一个表可以设置多个主键，复合主键primary key（字段名1,字段名2）

*外键（foreign key）约束

外键既能确保数据完整性，也能展现一个表和其他表之间的关系；一个表可以有一个或者多个外键，每个外键必须REFERENCES（参考）另一个表的主键或者唯一键，被外键约束的列，取值必须在它参考的列（另一个表的主键或者唯一键）中有对应值。

对应的字段名可以不一样，参考字段必须是主键或者是唯一键

添加外键

创建表以前

foreign key(外键字段名) REFERENCES 参考表(关联字段名)

*唯一性（unique）约束

表中的每个字段都可以称之为一个关键字（key），将表中的某个字段定义为唯一键，意味着该字段中的数据不允许重复。

字段名 数据类型 unique,

*默认值（default）约束

为字段设置默认值

字段名 数据类型 default '100',

*非空（not null）约束

指定某列不能为空。SQL中的null不区分大小写，所有数值类型都可以为null。空字符串不等于null，0也不等于null

字段名 数据类型 not null,

*检查（check）约束

check约束用于限制列中的值的范围

store INT,
check(store>=0)

#SQL使用

*cmd连接

连接命令：mysql -u 用户名 -p

*SQL语句编写事项

• 单词小写
• 以；结尾
• 引号可以用单引号，也可以用双引号

*关键字

create 创建
add  添加
insert  插入
drop  删除
alter 改动
modify 修改
change 更换
modify可以修改数据类型，可以添加约束，不能添加外键约束，不能修改名字；change可以修改名字
update  更新

*数据库操作

–创建库

create database 数据库名;

–删除库

drop database 库名;

–使用库

use 数据库名;

–查看所有库

show databases;

–查看库中所有表

show tables;

*表操作

–创建表&添加联合主键

create table 库名.表名(
字段名1 字段类型1(字符长度),
字段名2 字段类型2(字符长度),
字段名3 字段类型3(字符长度),
primary key(字段名1,字段名2)
... ...
);

–显示表结构

desc 库名.表名

–删除表结构

drop table 库名.表名;

（考点）当一个表引用了另一个表的数据时，如果需要将两个表都删除，需要先删除引用了其他表的那个表，再删除被引用的表，否则将删除失败。

–表约束操作

—添加主键

alter table 库名.表名 add primary key(添加主键字段名);
或者
ALTER TABLE 库名.表名 MODIFY 添加主键字段名 数据类型 PRIMARY KEY;

—删除主键

alter table 库名.表名 drop primary key;

—添加外键

alter table 库名.表名 add CONSTRAINT 外键约束名 foreign key(字段名) REFERENCES 库名.参考表(关联字段名);

外键约束名称为自己命名，格式可以设为表名_ 字段名_ fk

—删除外键

步骤一：删除外键
alter table 库名.表名 drop foreign key 外键约束名;
步骤二：删除外键索引
drop index 库名.索引名 on 库名.表名;

索引名（外键名）和外键约束名称相同。

—添加非空约束

alter table 库名.表名 modify 字段名 数据类型（数据长度） not null;

字段名 数据类型（数据长度）就是要设置非空的字段

—添加默认值约束

alter table 库名.表名 modify 字段名 数据类型（数据长度） default('默认值');

—添加唯一键约束

alter table 库名.表名 modify 字段名 数据类型（数据长度） unique;

—添加检查约束

ALTER TABLE 库名.表名 MODIFY 字段名 数据类型（数据长度） CHECK(字段名 约束范围);

—添加自增约束

当字段设置为主键时，可以设置自增auto_increment,建表时直接在字段名、主键后面添加

修改表,为表中已有的字段增加自增约束

alter table 库名.表名 modify 字段名 数据类型（数据长度） auto_increment primary key;

修改表，为表中新增字段并同时增加自增约束

alter table 库名.表名 add 字段名 数据类型（长度） primary key auto_increment;

• 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
• AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。
• AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

—删除自增约束

步骤一：修改该字段的数据类型，去除自增约束属性
alter table 库名.表名 modify 字段名 数据类型;
步骤二：删除该字段的主键约束
alter table 库名.表名 drop primary key;

—查看一个表的键值

show keys from 库名.表名;

–修改表结构

—修改表名

alter table 库名.表名 rename to 新表名;
rename table 库名.表名 to 新表名;

—为表添加一个字段（重点）

alter table 库名.表名 add column 字段名 数据类型（数据长度）;

为表添加一个字段相当于给表添加了一列，必须加column关键字

—修改表中指定字段名称&数据类型

alter table 库名.表名 change 原字段名 新字段名 数据类型（数据长度）;

修改字段名时必须用change

修改数据类型时字段名两个都用的原字段名

—修改表中指定字段的数据类型

alter table 库名.表名 modify column 字段名 新数据类型（新数据长度）;

column可以不加

—删除表中指定的字段

alter table 库名.表名 drop colunm 字段名;

删除字段不需要字段数据类型

–修改表数据

—插入表数据（重点）

insert into 库名.表名(字段名1,字段名2) values(字段值1，字段值2);

values后面可以跟多个括号插入多行数据，用逗号隔开；

如果后面字段值依次写全，前面表名后字段名可以省略

—修改表数据（重点）

update 库名.表名 set 字段名='修改后的数据' where 字段1=字段值1 and 字段2=字段值2;

and关键字，与的意思，后面可以跟n个and，直到确定唯一行

同时修改两个，用逗号连接

不加where条件时，修改数据会对整个表中所有记录起作用

—删除表数据（重点）

delete from 库名.表名;  ————删除整个表数据
delete from 库名.表名 where 字段1=字段值1;  ————删除字段1所在的整行数据
truncate 库名.表名;   ————删除整个表数据

重点说明：删除表中数据时，如果不加where默认删除表中所有数据(清空表)。

重点说明：使用delete删除表中数据时，delete后不能跟 * 号，delete不能和*号搭配使用。

*（面试题）delete、drop和truncate有何区别？

1. delete：可以删除指定数据，不会删除表结构，删除后可以恢复，可以和where关键字配合使用；
2. truncate：可以删除表中的数据，不会删除表结构，删除后的数据不可恢复，删除速度比delete快，不会删除表；
3. drop：可以删除数据库、表（及其中数据）、字段、约束等。

—复制表&复制表数据（考点）

创建一个表，只复制另一个表的表结构

create table 新表名 like 被复制的表名;

创建一个表，复制另一个表的表结构和数据

create table 新表名 as select * from 被复制的表名;

as可以省略

*表查询（重中之重）

查询语句都是select开头

select语句顺序：

1. select 字段列表 ----多个字段用逗号隔开
2. from 数据源表 ----多个字段用逗号隔开
3. [where 条件表达式（条件）]
4. [group by 分组字段]
5. [having 条件表达式]
6. [order by 排序字段[asc | desc]]
7. [limit [start],length];

注意：select语句中，各子句出现的顺序不能乱，否则报错

例子：查询书名为“Java编程思想”的库存

分析：

1. 要显示什么 --图书名字book_name和库存store
2. 来源于哪些表 --bookinfo
3. 表和表之间的关系
4. 条件 --book_name=‘Java编程思想’

SELECT book_name,store FROM bookinfo WHERE book_name='Java编程思想';

–条件运算符

等于号=

大于号>

小于号<

不等于号!=或<>

大于等于>=

小于等于<=

–逻辑运算符

not 非；and 与；or或

not表达式：取反

NOT 条件1 AND 条件2 ——不取1，取2

NOT (条件1 AND 条件2) ——12都不取

条件 1 AND 条件 2：查询结果返回同时符合条件1和条件2的数据

条件 1 OR 条件 2：返回符合任意一个条件的数据

or可以用in(3,4,5,…)代替

not in(3,4,5,…) ----取反

–查询关键字

in

当查询条件给出的为多个类似的值时，可以使用关键字IN，不同值之间使用逗号隔开。IN后跟数据列表，匹配其中任何一条数据的记录都将显示在查询结果中

SELECT book_name,store FROM bookinfo WHERE store IN(3,4,5);

between and

当查询在两个数之间时，可以用between and

SELECT book_name,store FROM bookinfo WHERE store BETWEEN 3 AND 5; 

null

is null 代表为空，is not null为非空，不能使用=null

SELECT book_name,store FROM bookinfo WHERE store IS NULL;  #查询为空的数据

SELECT book_name,store FROM bookinfo WHERE store IS NOT NULL;  #查询不为空的数据

distinct

distinct和具体字段名配合使用，可以过滤掉该字段的重复数据

select distinct 字段名 from...

与count()函数配合使用

select count(distinct 字段名) from...

分组查询关键字group by用法

group by … 通过…进行分组

先通过给指定字段名进行分组，在对分组的数据进行分别处理

SELECT AVG(sal) FROM emp GROUP BY deptno;  #没有特定条件，不用写where

having对分组后的数据进行过滤

having关键字，可以对分组后的数据进行过滤，类似于where的作用，having后的过滤条件可以跟字段，也可以跟聚合函数

having和where的区别：（重点）

1. where能对分组前的数据进行过滤，不能对分组后的数据进行过滤；
2. having是对通过group by分组后的数据进行过滤；
3. having后面可以跟字段名、函数；
4. where后不能跟函数。

order by对查询结果进行排序

mysql 中查询，查询结果默认按升序（从小到大）排列

order by 字段名 asc/desc

通过给指定的字段名将查询结果进行排序（asc升序或者desc降序）

order by 还可以多字段排序，可以同时使用多个字段对查询结果数据先后进行排序，先满足第一个字段，然后满足后面一个字段，依次类推；字段之间用逗号隔开

Order by 字段1 [[desc]|[asc]] ,字段2 [[desc]|[asc]];

查询关键字limit的用法

在MySQL中，limit m,n m从0开始，连续取n条数据，显示在查询结果中。

limit 0,4;（取前1-4条）

limit 3,1;（取第4条）

limit 4;（取前4条）

模糊查询like

模糊查询，使用查询关键字like

select...from...where 字段名 like '需查询的内容与字符';

模糊查询支持两种字符匹配符号：

• 下划线_:下划线可以代替单个字符
• 百分号%：可以代替任意个字符（0、1或者多个字符）

–聚合函数（重点)

常用分组函数主要包括：

求最大值max() 函数、最小值min() 函数、平均值avg() 函数、求和sum() 函数、统计条数count() 函数、round() 函数

时间函数

DATE_FORMAT(NOW(),‘%Y-%m-%d %H:%M:%S’) 函数–获取系统当前的年月日时分秒

YEAR(NOW()) --获取系统当前的年

MONTH(NOW())–获取系统当前的月

DAY(NOW())–获取系统当前的日

HOUR(NOW())–获取系统当前的时
MINUTE(NOW())–获取系统当前的分
SECOND(NOW())–获取系统当前的秒

函数不要写到where条件后面，写到查询字段的位置

SELECT book_name,MAX(price) FROM bookinfo;

SELECT book_name AS '图书名',MIN(price) AS '最低价格' FROM bookinfo;

as关键字可以修改查询结果显示的字段名

count()函数：

count(字段名) ----统计指定字段名的数据，不会统计null

count(*) ----统计整个表的数据，会统计null

count(n) ----统计第n列的数据

round(a,b) ----四舍五入，a，字段名；b，保留小数位数

–查询多个表中的数据（*重中之重）

例子：借了图书没有归还的人的名字和电话

分析：

1. 要显示什么 --name 、tel

2. 来源于哪些表 --reader、 borrowinfo

3. 表和表之间的关系 --reader.card_id=borrowinfo.card_id

   • 直接关系

     两个表之间是主外键关系，有字段内容相同的

   • 间接关系

     两个表之间间接有关系，没有内容相等

4. 条件 --没有归还：status=‘否’

表和表之间的关系也是条件，写在where后面

SELECT name,tel FROM borrowinfo,reader 
WHERE borrowinfo.card_id=reader.card_id AND status= '否';

间接关系同理，and后面写表之间的联系

例：查询借了图书没有归还的人姓名、电话、借的书名和类别

SELECT NAME,tel,book_name,category 
FROM bookinfo,borrowinfo,bookcategory,reader
WHERE borrowinfo.book_id=bookinfo.book_id 
AND bookinfo.book_category_id=bookcategory.`category_id`
AND reader.`card_id`=borrowinfo.`card_id`
AND `status` = '否';

–子查询

就是嵌套在另一个查询内部中的查询， 子查询可以达到的查询目的，使用其他多表连接查询同样可以使用多表连接实现，但是子查询更加易读

可以写在from后面，当作一个临时表，供外层sql再次查询

• 整个子查询用括号括起来，起一个别名
• 如果字段里面有聚合函数，函数也要起一个别名才能引用

也可以写在where后面，把内层查询的结果作为外层查询的比较条件

子查询操作符ALL

• “<>ALL”：等价于not in
• “>ALL”：比子查询中最大的值还要大
• “<ALL”：比子查询中最小的值还要小

特别说明：“=ALL” 不存在

子查询操作符ANY

• “=ANY”：与任何一个相等
• “>ANY”：比最低的高
• “<ANY”：比最高的低

–内连接&外连接查询（重点***）

内连接

在进行跨表连接查询数据时，查询结果只返回符合连接条件的数据

select 字段名1，字段名2... 
from 表名1 
inner join 表名2    -- inner join 代替逗号
on 表名1.字段名1=表名2.字段名2  -- on代替where
[where 条件]

inner可以省略

外连接

外连接分为左连接和右连接

左外连接

使用左连接进行多表查询时，如果某个表中的数据需要全部显示，查询时，将这个表作为左表，右表中符合条件的数据显示在查询结果中，右表不符合条件的数据，也显示在结果中，不符合条件的相关查询结果字段的值以null填充

-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 NULL )
  SELECT student.sid,sname,COUNT(cid), SUM(score)
  FROM student 
  LEFT JOIN score ON student.sid=score.`Sid` 
  GROUP BY sid 

左连接与内连接的区别（面试）：

• 内连接只显示表之间的公共数据
• 左连接会显示左表的所有数据，右表没有的数据会以null显示

右外连接

与左连接同样用法，只是把left改成了right

–联合查询

UNION，把多个SQL查询出来的结果合并到一起

注意：

• 两个结果显示的字段个数并且字段的数据类型必须是一样的
• 会去除重复数据

UNION与UNION ALL的区别：

• UNION ALL不会去重
• UNION ALL效率更快