MySQL基础知识学习（二）

目录

一、连接查询

二、子查询

三、MySQL常用内置函数

四、存储过程

五、视图

六、事物

七、索引

八、基于命令行的MySQL

---

一、连接查询

1、基本概念

• 连接查询可以通过连接运算符（连接条件）可以实现多个表查询
• 内连接：查询的结果只显示两个表中满足连接条件的部分
• 左连接：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充
• 右连接：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充

2、内连接（inner join 表 on 连接）

• 语法1：

select 字段1,字段2,... from 表1
inner join 表2 on 表1.字段=表2.字段

• 语法2（隐式内连接）：

select 字段1,字段2,... from 表1,表2
where 表1.字段=表2.字段

3、左连接（left join 表 on 连接）

• 语法：

select 字段1,字段2,... from 表1
left join 表2 on 表1.字段=表2.字段

4、右连接（right join 表 on 连接）

• 语法：

select 字段1,字段2,... from 表1
right join 表2 on 表1.字段=表2.字段

---

二、子查询

1、定义

• 在一个select 语句中，嵌入了另外一个select 语句，那么被嵌入的select 语句称为子查询语句

2、主查询

• 外层的第一条select 语句为主查询

3、主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的，要么充当条件，要么充当数据源
• 子查询是可以独立存在的语句，是一条完整的select语句

（1） 标量子查询：子查询返回的结果只有一个值（一行一列），这种称为标量子查询

--例子：查询大于平均年龄的学生信息
select * form students 
where age > (select avg(age) from student);

（2）列子查询：子查询返回的结果是一列（一列多行），这种称之为列子查询

--例子：查询30岁的学生的成绩
select * from score 
where studentNo in (select studentNo from students where age=30);

（3）表级子查询：子查询返回的结果是多行多列（一个表），这种称之为表级子查询

--例子：查询所有女学生的信息和成绩
select * from (select * from students where sex='女') stu
inner join score sc on sc.studentNo=stu.studentNo;

---

三、MySQL常用内置函数

1、字符串函数

（1）拼接字符串 concat（str1，str2...）

• 参数可以是数字，也可以是字符串
• 把所有的参数连接成一个完整的字符串

（2）包含字符个数 length（str）

• 如果字符串包含utf8格式的汉字，一个汉字length返回3

（3）截取字符串

• left（str，len）返回字符串str的左端len个字符，中文与英文字母个数len一致
• right（str，len）返回字符串str的右端len个字符，中文与英文字母个数len一致
• substring（str，pos，len）返回字符串str的位置pos起len个字符，pos从1开始计数

（4）去除空格

• ltrim（str）返回删除左侧空格的字符串str
• rtrim（str）返回删除右侧空格的字符串str
• trim（str）返回删除左右两侧空格的字符串str

2、数字函数

（1）求四舍五入值round（n，d）

• n表示原数，d表示小数位置，默认为0

（2）随机数rand（）

• 值为0-1.0的浮点数

--小技巧：从学生表中随机抽出一名学生
select * from students order by rand() limit 1;

 3、日期时间函数

（1）当前日期 current_date（）

（2）当前时间 current_time（）

（3）当前日期和时间 now（）

---

四、存储过程

1、定义：存储过程procedure也翻译为存储程序，是一条或者多条SQL语句的集合

2、创建存储过程：

create procedure 存储过程名称(参数列表)
begin
    sql 语句
end

3、使用存储过程：

call 存储过程(参数列表);

4、删除存储过程：

drop procedure 存储过程;
drop procedure if exists 存储过程;

---

五、视图

1、定义：视图本质就是对查询的封装，可以当成一张表来使用（只能在select使用）

2、创建视图：

create view 视图名称
as
    select 语句;

3、删除视图：

drop view 视图名称;
drop view if exists 视图名称;

---

六、事物

1、定义：是一个操作系列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单位

2、事物命令：

• 开启事物：begin，开启事物后执行修改 update 或删除 delete记录语句，变更会写到缓存中，而不会立即生效
• 回滚事物：rollback，放弃修改
• 提交事物：commit，将修改的数据写入实际的表中

 当事物开启后，如果出现非人为因素导致语句不能执行下去，默认执行rollback

---

七、索引

1、定义：索引类似目录，索引是对表起作用，可以加快数据查询效率

2、创建索引：

create index 索引名称 on 表名(字段名称(长度));

• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
• 字段类型如果不是字符串，可以不填写长度部分

3、调用索引：只要where条件后面用到的字段建立了索引，那么系统会自动调用

4、查看索引：

show index from 表名;

注：对于主键，系统会自动建立索引

5、删除索引：

drop index 索引名称 on 表名;

6、索引优缺点

• 优点：索引大大提高了select 语句的查询速度
• 缺点：会降低更新表的速度，因为更新表时，不仅要保存数据，还要保存索引文件

当表要处理大量数据时，可以先删除索引处理数据，最后再添加索引，提高处理数据效率。

---

八、基于命令行的MySQL

1、windows cmd窗口连接mysql

（1）进入mysql.exe所在目录

（2）输入 mysql -h [主机名] -u [用户名] -p （本地连接可以不写 -h）

参数	说明
-h [主机名]	指定要连接mysql的ip地址或者主机名称； 如省略-h [主机名] 参数，mysql 会自动默认为本地连接；
-u [用户名]	指定连接的用户名
-p	执行命令后会提示输入密码

2、窗口常用命令（大部分与SQL命令相同）

（1）显示所有数据库：show databases;

（2）进入某个数据库：use 数据库名称;

（3）显示数据库所有表：show tables;

（4）告诉mysql数据库使用编码格式：set names gbk / utf8;（当汉字出现乱码时使用）

（5）查看表的结构：desc 表名;

（6）创建数据库：create database 数据库名 default charset [默认字符集];

• 默认字符集是utf8、jbk等字符编码格式