MySQL笔记

关注

原创最新推荐文章于 2026-03-20 01:36:26 发布 380 阅读 AI 写同款· GEO 优化 ›

《MySQL》超详细笔记关系型与非关系型的区别：显示数据库 (1)显示所有已经存在的数据库 (2)显示创建某个特定数据库的SQL语句运行结果解读:编辑删除数据库将已存在的数据库删除，并同时删除数据库中的数据使用数据库查询当前数据库信息显示数据库中的表导入数据库脚本【注】表查看表的结构查看创建某个表的SQL语句数据库的查询命令查询字段运算符操作 1、数值比较/字符比较2、逻辑操作3、范围内比较4、匹配空、阅读详情

MySQL数据类型

四种基本数据类型：数值，字符，时间，二进制

常用数值数据类型

整数：int,int(num) 括号中的数字为显示宽度，与取值范围无关，如果数值为一位则显示的时候会在前面补num-1个空格，不影响存储。
小数：decimal(M,N) M代表总共的位数，N代表小数位的位数。

常用字符数据类型

定长字符：char(N) 如果只存储了一个字符，则右边会填充N-1个空格，保证一共N个字符，但是最多只能存储N个字符。
变长字符：varchar(N) 最多存储N个字符，不足N个字符不会自动填充空格。

常用时间数据类型

date：用于仅仅存储日期数值时，不包括时间（时、分、秒）信息。
默认格式：‘YYYY-MM-DD’，YYYY表示年，MM表示月，DD表示日。
datetime ：用于既需要存储日期也需要存储时间信息时。
默认格式：‘YYYY-MM-DD HH:mm:SS’，YYYY表示年，MM表示月，DD表示日，HH表示小时，mm表示分钟，SS表示秒。

MySQL函数

常用字符串函数

char_length(str)：计算参数str中有多少个字符，str可以是具体的一个字符串，也可以是表中的列。
例1：查看字符串“你好”有几个字符select char_length(str);
例2：查看student表中学生名字的长度select name,char_length(str) 名字长度 from student ;
concat(str1,str2,…)：把括号内填入的多个参数拼接成一个字符串。
例1：把“你好”和“我叫小明”拼接成一句话select concat('你好','我叫小明');
例2：把student表的学生姓名和性别拼接起来，以 XXX是X生的形式打印结果select concat('name','是','sex','生') from student;
substring(str,pos,len)：把参数str字符串从第pos位起截取len位。
例：查看student表中每个学生的姓氏（暂且忽略复姓）select substring(name,1,1) from student;

常用数值函数

round(num,n)：对数字num进行四舍五入运算，从第n位小数算起。

常用日期函数

year(date)：获取日期date的年份。
例：根据生日获取student表中学生的出生年份select name,birthday,year(birthday) 年份 from student;
month(date)：获取日期date的月份。
例：根据生日获取student表中学生的出生月份select name,birthday,month(birthday) 月份 from student;
curdate()：获取当前日期。
curtime()：获取当前时间。
now()：获取当前的日期和时间。
datediff(date1,date2)：返回date1和date2两个日期间隔的天数。date1晚于date2返回正值，否则返回负值。
例1：计算当前距2018年元旦节过去了多少天select datediff(now(),'2018-1-1');
例2：根据student表中学生的生日计算学生现在的年龄select name,birthday,round(datediff(now(),birthday)/365,0) 年龄 from student;

常用条件判断函数

if(expr,v1,v2)：如果表达式expr成立返回v1值，否则返回v2值。
case运算符：case运算符增加了多重条件判断，比if函数更加灵活。
用法：case when 条件1 then 结果1 when 条件2 then 结果2 ... else 结果N end
例：根据学生分数显示学生成绩等级
select name,score,(case when score>=90 then '优秀' when score>=80 then '良好' when score>=70 then '一般' when score>=60 then '及格' else '不及格' end) 等级 from student;

常用空值处理函数

ifnull(v1,v2)：如果v1为空返回v2，否则返回v1。
isnull(expr)：如果表达式为空值返回1，表达式非空则返回0。
空值问题：使用in时如果值列表中有null值，不会返回null相关结果；使用not in时如果值列表中有空值，无查询结果返回。

常用聚合函数

max(列名)：统计出列中所有行中的最大值，列的数据类型可以是数值型、字符型、日期型。
min(列名)：统计出列中所有行中的最小值，列的数据类型可以是数值型、字符型、日期型。
sum(列名)：统计出列中所有行中数值的总和，列的数据类型不能是字符型。
avg(列名)：统计出列中所有行中数值的平均值，列的数据类型不能是字符型。
count(列名)：统计出列中所有行的数量，列中如果有空值不会被统计计数。

数据库

以root用户本地连接数据库：mysql -uroot -p
查看所有数据库：show databases;
使用数据库：use 数据库名;
查看当前使用的数据库：select database();
创建数据库（不带编码格式）：create database 数据库名;
创建数据库（带编码格式）：create database 数据库名 charset=utf8;
删除数据库：drop database 数据库名;

数据表

查看当前数据库中所有的表：show tables;
查看表结构：desc 表名;
列的别名：给列起个额外的名称用来替代原来的名称。
例如：select name as 姓名 from student;as可写可不写
表的别名：给表起个额外的名称用来替代原来的名称。
例如：select s.name,s.sex from student as s;as可写可不写

建表语句

创建表（无结构，无约束）：create table 表名;
基于已有数据表创建新表并复制所有数据：create table 新表名 as seletc * from 表名;
基于已有数据表创建新表并复制部分数据：create table 新表名 as seletc * from 表名 where 条件（如：sex='男'）;
基于已有数据表创建新表仅复制表结构不包括数据：create table 新表名 like 表名;或者create table 新表名 as seletc * from 表名 where 1=2;

insert语句

insert into 表名(列名1,列名2,...) values(列1对应值,列2对应值,...);
insert into 表名 values(列1对应值,列2对应值,...,列N对应值);——不指定列名需要在values中指定所有列对应的值，顺序不能错。
insert into 表名 values(列1对应值,列2对应值,...,列N对应值),(列1对应值,列2对应值,...,列N对应值),(列1对应值,列2对应值,...,列N对应值),...;——一次插入多行，每行用逗号隔开。

update语句

update 表名 set 列1=列1值,列2=列2值 where 列n=列n值;——一般情况下更新语句需要加上where子句以定位要修改的行，如果不加将会修改所有行相应的列；如果修改多个列对应的值，用逗号隔开各个列。
例如：把student表中小明的性别改为女 update student set sex='女' where name='小明';

delete语句

delete from 表名 where 列N=列N值;——一般删除语句需要加上where子句以定位要删除的行，如果不加会删除整个表的所有行。
例如：把student表中的小明那一行删除 delete from student where name='小明';

select查询语句

select * from 表名——查询表中所有列对应的行数据；
select 列名1,列名2,... from 表名——仅挑选表中对应的几列查询其对应的行数据；

select - where子句

常用算数运算符：(+，-，*，/)分别对应加，减，乘，除
常用逻辑运算符：and(与)，or(或)，not(非)
常用比较运算符：= 为等于；！= 或者 <> 为不等于；> 为大于；< 为小于；>= 为大于等于；<= 为小于等于；is null 为空值；is not null 为非空值；in (值列表) 为在值列表中；not in (值列表) 为不在值列表中；between 低值 and 高值 (包含低值和高值) 为在低值和高值之间；not between 低值 and 高值 (包含低值和高值) 为不在低值和高值范围内；like ‘通配符’ 按照通配符进行匹配；
常用通配符：%匹配0个或任意多个字符；_匹配任意一个字符；
运算符优先级：()优先级最高。
例如：在student表中查找老家是河南或者北京的男生
select * from student where (address='河南' or address='北京') and sex='男';
select * from student where address in ('河南','北京') and sex='男';
以上两种方法等效

select-distinct关键字

distinct关键字查询出指定的一个或多个字段的不重复记录。
例1：查询student表中有哪些班级select distinct class from student;
例2：列出学生表中不重复的班级和性别select distinct class,sex from student;
结果形如

class	sex
一班	男
一班	女
二班	男
二班	女

select-order by子句

对查询结果集按照order by后面指定的1列或多列排序。
分为增序和降序：

增序ASC，默认可以不写；
降序DESC（写到order by子句的最后，也就是指定列的后面）；
对于数值，增序是从小到大，降序是从大到小。
对于日期和时间，增序是从远到近，降序是从近到远。
对于英文字符，增序是从a到z，降序是从z到a。
对于中文字符，增序按照字符集编码从小到大，降序按照字符集编码从大到小，如果选择gbk编码可以按照拼音来排序。

对于排序列的名称可以使用列名、列别名、列的序号、函数、表达式等。
例1：按照拼音降序排列所有学生
select * from student order by convert(name using gbk) desc;——由于汉字在当前编码（utf8）中不是按照拼音顺序排序的，需要用到convert函数转换列name的编码到gbk。
例2：显示学生姓名、性别、生日3列信息，先按第2列再按第3列降序排序
select name,sex,birthday from student order by 2,3 desc;

select-limit子句

select语句中的limit子句最后执行。
作用：仅显示查询结果集中的部分内容。
例：显示student表中成绩最好的3名学生
select * from student order by score desc limit 3;
或者
select * from student order by score desc limit 0,3;——limit 0,3中的0为第一条记录，3为显示三条记录；如：limit 5,3意思为从结果集的第6条起，显示3条记录。

select-group by子句

group by子句按照指定的列对表数据进行分组。
group by后面跟的列也叫分组特性列。
使用group by后，能选择的列通常只能包括分组特性列和聚合函数。

select-having子句

having子句是对group by产生的结果集的过滤。
having子句可以对分组特性列进行过滤，也可以对聚合函数的值进行过滤。
例：统计student表中一班学生人数
select class 班号,count(*) 人数 from student group by class having class='一班';
或者
select class 班号,count(*) 人数 from student where class='一班';
两种方法产生的查询结果集相同，但是执行效率不同。
第一种方法先使用group by统计，再用having过滤统计结果，统计了和一班不相干的其他班的人数，浪费了系统资源，效率低。
第二种方法，先用where子句过滤掉不相干班级的人员，然后直接统计一班的人数，效率高。
注意：如果需求明确，where子句能用就用，先把结果集缩小，这样不会浪费额外系统资源，特别是成千上万用户使用相同SQL语句并发访问数据库的时候，效率高和效率低的SQL语句会有明显差别。

视图 VIEW

属于关系型数据库中的一种常用数据对象，保存一段select语句，可以把视图名称当成普通表来使用。
创建视图语法：create view 视图名称 as 查询语句;
视图主要作用：提高数据安全性（隐藏部分行和列）；简单化查询，可以将常用的查询语句写成视图的形式被其他查询调用，这样可以降低查询语句的复杂度，提高可读性。
视图仅仅保存select语句，其select语句中的表称为基表（或母表），视图对应的数据是放在基表中的，基表中数据产生变化，视图的结果集也会产生变化。即：视图里面的数据发生变化，其实是基表中的数据变化所致。
如果视图和其相关基表存在记录一一对应的关系，排除基表上存在约束的条件，通常可以对视图做DML操作，但是不要轻易对视图做DML操作，以防止对基表数据产生意想不到的影响。
查看有哪些视图：show table status\G其中comment值为view的表是视图
查看视图的定义：show create table 视图名称;

多表连接查询

特点：

将分散在多个表中的信息（列）横向合并在一起
通常需要指明连接条件
只要两个或多个表中列的数据类型类似（例如：都为数值型、都为字符型等）就可以连接在一起，但通常会根据列的实际业务含义进行连接，这样才有实际意义
多标连接查询和单表查询相比会更耗资源

语法：（现在有两个表，student表是学生姓名以及所在班级，teacher表中是班主任姓名及所带班级，现在将学生及其班主任进行匹配）

不使用表别名：select student.name,teacher.name from student join teacher on student.class=teacher.class
使用表别名：select s.name,t.name from student s join teacher t on s.class=t.class;

分类：

交叉连接：不指定连接条件，结果集被叫做笛卡尔积，记录数为a表记录数m乘以b表记录数n，两种写法select * from a cross join b;或者select * from a join b;均可。
内连接：指定连接条件，结果集包含符合连接条件的记录数，两种写法select * from a inner join b on a.id=b.id;或者select * from a join b on a.id=b.id;均可。
左连接（左外连接）：指定连接条件，结果集包含左表全部记录以及右表中符合连接条件的记录和右表中不符合连接条件的记录（用NULL值填充）；select * from a left join b on a.id=b.id;或者select * from a left outer join b on a.id=b.id;两种写法均可。
右连接（右外连接）：指定连接条件，结果集包含右表全部记录以及左表中符合连接条件的记录和左表中不符合连接条件的记录（用NULL值填充）；select * from a right join b on a.id=b.id;或者select * from a right outer join b on a.id=b.id;两种写法均可。

复合查询

符合查询指用集合运算符对多个查询结果集进行运算，产生新的查询结果集。MySQL常用集合运算符包括以下两种：

nuion：对两个结果集进行并集操作，重复行只取一次，同时进行默认规则的排序。
union all：对两个结果集进行并集操作，包括所有重复行，不进行排序。

用法：查询1 union 查询2，查询1 union all 查询2
提示：MySQL没有交集和差集运算符，可通过多表连接来实现。（例如差集：可以先让表一左连接表二，取表二内容为null的那部分，再只查询属于表一的列。）

子查询

select查询语句有些部分可以嵌套额外的select语句（子查询），与外部select语句（主查询）结合起来使用，用一个查询语句实现更为复杂的任务。那些嵌套的select语句往往被称为子查询，子查询需要用括号（）括起来。

分类：

非关联子查询：子查询可以单独于主查询执行，仅执行一次，效率高。
关联子查询：子查询不能单独于主查询执行，如果主查询有N行，子查询将执行N此，效率现对较低，但灵活度高。

约束

约束的作用

约束是数据库用来提高数据质量和保证数据完整性的一套机制
约束作用在列表上，是表定义（DDL语句）的一部分

约束分类

非空约束（not null）：insert，update数据时不允许空值（null）
唯一性约束（unique）：insert，update数据时不允许重值，可以允许空值，自动创建唯一性索引
主键约束（primary key）：非空约束+唯一约束，主键的列不允许有重值和空值，自动创建唯一性索引
外键约束（foreign key）：引用主键构成完整性约束，允许有空值，不允许存在对应主键约束的列所有数值以外的其它值

查看约束

desc 表名;
show create table 表名\G
show keys from 表名;——看不了非空约束
show index from 表名;——看不了非空约束

主键约束

约束的定义方式：

创建表时建立约束（建表之前就已经规划好了）：列级定义——create table 表名(id int primary key,name char(10))；表级定义——create table 表名(id int,name char(10),primary key(id));
修改表时追加约束（建表之后根据需要追加）：alter table 表名 add primary key(id);

一个表不能有多个主键，可以用非空+唯一约束代替

非空约束

非空约束用于确保其所在的列的值不能为空值null，只有列级定义和追加定义。
语法：

列级定义：create table 表名(id int not null,name char(10));（此时id不能为空，name可以为空）
追加定义：alter table 表名 modify name char(10) not null;（此时在id不能为空的基础上增加了name不能为空）

允许为空：create table 表名 modify id int null;（此时设置id可以为空）
设置默认值：如果没有指定值时用默认值填充
create table 表名 modify id int null default 100;（为id设置默认值为100）
create table 表名 modify name char(10) default 'abc';（为name设置默认值为abc）

唯一性约束

列表中不允许有重复值，但是可以有空值
语法：

列级定义：create table 表名(id int unique,name char(10));
标记定义：create table 表名(id int,name char(10),unique(id));
追加定义：alter table 表名 modify id int unique;

外键约束

引用主键构成完整性约束。外键允许有空值，不允许存在对应主键约束的列所有数值以外的其它值。MySQL自动创建非唯一性索引。
语法（一个表）：

表级定义：create table 表名(id int,name char(10),pid int,primary key(id),foreign key(pid) references 表名(id));
追加定义：create table 表名(id int,name char(10),pid int);-建表， alter table 表名 add primary key(id);-添加主键约束， alter table 表名 add foreign key(pid) references 表名(id)-添加外键约束

语法（两个表）：

表级定义：先建立外键要引用的主表——create table c(cid int primary key,cname char(10));，再建立包含外键的从表——create table s(sid int,sname char(10),cid int,foreign key(cid) references c(cid));
追加定义：create table s1(sid int,sname char(10),cid int);，alter table s1 add foreign key(cid) references c(cid);