MySQL学习总结

于 2019-11-20 13:26:22 发布
阅读量217 收藏
点赞数
分类专栏： SQL 文章标签： MySQL 单表查询 多表查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lixin122100/article/details/102525477
版权

数据库基本概念：数据库就是存储数据的仓库

SQL基本概念：Structured Query Language: 结构化查询语言。

​ 即操作所有关系型数据库的规则。

SQL分类

  • DDL(Data Definition Language): 数据定义语言

    用来定义数据库对象：数据库，表，字段等，常见操作：

    ​ create，drop，alter等

  • DML(Data manipulation Language): 数据操作语言

    用来对数据库中的表进行操作，常见操作如下：

    ​ insert，delete，update等

  • DQL(Data Query Language)：数据查询语言

    用于查询数据库中表中的数据（记录），常用操作如下：

    ​ select，where，group by等

  • DCL(Data Control Language)：数据控制语言

    用来定义数据库的访问权限和安全级别，以及创建用户，常用操作如下：

    ​ GRANT, REVOKE 等

二、数据库操作

1.创建数据库

CREATE DATABASE myDatabase;
-- 进入数据库myDatabase
USE myDatabase;

 

2.查看数据库

1、查看所有数据库

show databases; 

-- 查看所有数据库
show databases;

2、查看指定部分的数据库：模糊查询

show databases like ‘pattern’ ; – pattern是匹配模式

%：表示匹配多个字符

_: 表示匹配多个字符

-- 查看以my开头的数据库
  SHOW DATABASES LIKE 'my%';

3.更新数据库

数据库名字是不可以修改的，想要修改数据库名只能先删除再创建

数据库的修改仅限库选项：字符集和校对集（且校对集依赖字符集）

Alter database 数据库名 [库选项];

Charset/character set [=] 字符集

Collate 校对集

三、数据表操作

1.创建数据表

Create table [if not exists] 表名（
字段名 数据类型，
字段名 数据类型  -- 最后一个不需要逗号
）[表选项];

If not exists: 如果表名不存在，那么就创建，否则不执行创建代码 --->检查的功能
表选项：控制表的表现
    字符集：charset/character set 具体字符集；   -- 保证表中数据与存储的字符集
    校对集：collate 具体校对集；
    存储引擎：engine 具体的存储引擎（innodb 和 myisam）


-- 创建表stydent
CREATE TABLE stydent(
  name VARCHAR(30) COMMENT = '姓名',
  age INT COMMENT = '年龄',
  gender CHAR COMMENT = '性别',
  address VARCHAR(30) COMMENT = '住址'
);

2.查看数据表

  1. 查看数据表

    -- 查看所有表
SHOW TABLES;
-- 模糊匹配，查看以's'结尾的表
SHOW TABLES LIKE '%s';

  2. 查看表创建语句

    -- 查看表创建语句
  SHOW CREATE TABLE 表名\g； -- \g代表旋转90度显示查询结果

  3. 查看表结构

    -- 三种方法
Desc  stydent;
Describe stydent;
Show columns from stydent;

3.修改数据表

a.修改表选项

Alter table 表名 表选项[=]值；

-- 将student表的字符集修改为GBK
ALTER TABLE stydent CHARSET = GBK;

b.修改表名

alter table 旧表名 rename to 新表名;

rename table 旧表名 to 新表名;

-- 将上面创建的表stydent修改为student
-- 修改表名
  ALTER TABLE stydent RENAME TO student;

c.新增字段

Alter table 表名 add 字段名 数据类型 [列属性] [位置] ;

位置：字段名可以存放在表中的任意位置

First：放在第一位

After：在哪个字段名之后 字段名 . after 字段名 ，默认是最后一位。

-- 添加字段 ' id '到字段name后面
ALTER TABLE student ADD id INT AFTER name;

d.修改字段

-- 修改字段数据类型
alter table 表名 modify 字段名 数据类型 [位置];

-- 修改字段名
alter table 表名 change 旧字段名 新字段名 数据类型;

e.删除字段

删除字段(一列数据)

Alter table 表名 drop 字段名;

-- 删除student表中的age字段
ALTER TABLE student DROP age;

4.删除数据表

Drop table 表名；

DROP TABLE student;

四、数据操作

1.添加数据

给数据表添加数据

-- 创建表student
CREATE TABLE student(
  name VARCHAR(30) COMMENT = '姓名',
  age INT COMMENT = '年龄',
  gender ENUM('男','女') COMMENT = '性别',
  address VARCHAR(30) COMMENT = '住址'
);

-- 插入数据
INSERT INTO student VALUES ("张三",21,'男',"南丰城");
INSERT INTO student VALUES ("叶凡",19,'男',"磐石镇");
INSERT INTO student VALUES ("魍魉",12,'女',"酆都");
INSERT INTO student VALUES ("伍六七",19,'男',"小鸡岛");
INSERT INTO student VALUES ("秦昊天",21,'男',"黑暗天");

2.删除数据

– 根据指定条件删除表中的一行或多行数据
delete from 表名 where 条件 [limit n];

-- 删除student表中的id为1002的记录
delete from student where id = 1002;

3.修改数据

Update 表名 set 字段名 = 值 [where 条件] ; – 建议都要有where子句，要不然就是更新全部

-- 将student表中name为魍魉的age修改为10
update student set age=10 where name"魍魉";

五、单表查询

1.基础查询

语法：select 字段列表 from 表名列表 ;

-- 查询所有字段的记录
select * from student；
-- 查询所有name和gender字段
select name,gender from student;

2.条件查询

语法：select 字段列表 from 表名列表 where 条件;

-- 查询name为张三的记录
select * from student where name="张三";

3.排序查询

通过ORDER BY 子句，可以将查询出的结果进行排序（排序只是显示方式，不会影响数据库中数据的顺序

SELETE 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 字段名 [ ASC | DESC ];

  • order by 字段1 排序方式1，字段2 排序方式2, … ;（如果有多个字段需要排序，那么先按字段1排序，如果字段1相同，则再按字段2排序）

  • 排序方式：

    ASC: 升序，默认

    DESC: 降序

3.1 单列排序

  • 什么是单列查询？

    只是按某一个字段进行排序，单列排序。

    -- 查询所有数据，使用年龄降序排列
SELECT * FROM student ORDER BY age DESC;

3.2 组合排序

  • 什么是组合排序？

    同时对多个字段进行排序，如果第1个字段相等，则按照第2个字段排序，依次类推。

    语法：SELECT 字段名 FROM 表名 WHERE 字段=值ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

    -- 先按第一条件math降序排列，当math相同时，再按chinese降序排列
SELECT * FROM student ORDER BY math DESC,chinese DESC ;

4.聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值NULL。

SQL中的聚合函数作用
max（列名）求这一列的最大值
min（）求这一列的最小值
sum（）对这一列求总和
avg（）求这一列的平均值
count（）统计这一列有多少条记录
-- count函数:计算学生人数（id）唯一
SELECT COUNT(id) FROM student;

-- max函数
SELECT MAX(math) FROM student;

-- min函数
SELECT MIN(math) FROM student;

-- sum函数
SELECT SUM(math) FROM student;

-- avg函数
SELECT AVG(math) FROM student;

-- 如果有字段含有null，那么可以使用IFNULL函数
SELECT COUNT(IFNULL(math,0)) FROM student;

*注意：聚合函数的计算排除null值。

解决方案：1、选择不能为空的列 2、IFNULL函数

IFNULL (列名，默认值) 如果列名不为空，返回这列的值。如果为null，则返回默认值。

-- 查询id字段，如果为null，则使用0代替
SELECT IFNULL(id，0) FROM student；

我们可以使用IFNULL （）函数，如果记录为null，给个默认值，这样统计的数据就不会遗漏

SELECT COUNT(IFNULL(id,0)) FROM student；

5.分组查询

分组查询是指使用 group by 语句对查询信息进行分组，相同数据作为一组

语法：SELECT 字段1，字段2 …FROM 表名 GROUP BY 分组字段 [ HAVING 条件 ]；
GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

-- 查询math平均成绩和人数，按性别分组
SELECT gender,COUNT(ID),AVG(math) FROM student GROUP BY gender;

-- 查询数学成绩大于80的平均成绩，按照性别分组
SELECT gender,COUNT(ID),AVG(math) FROM student WHERE math>80 GROUP BY gender;

-- 按照性别分组,分别查询男，女同学的数学平均成绩，人数。要求：数学成绩低于80的人，不参与分组，分组之后人数要大于2
SELECT gender,COUNT(ID),AVG(math) FROM student WHERE math>80 GROUP BY gender HAVING COUNT(ID)>2;

注意：

  • 分组之后查询的字段只能是：分组字段，聚合函数
  • where 和 having 的区别？
    1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如果不满足结果，则不会被查询出来。
    2. where 后不可以跟聚合函数，having 可以进行聚合函数的判断

6.分页查询

语法：limit index, n; index：开始的索引，每页显示的条数；

公式：开始的索引 = （当前页码 - 1） 每页显示的条数。*

-- 分页查询，限定每页三条记录
SELECT * FROM student LIMIT 0,3;	-- 第1页
SELECT * FROM student LIMIT 3,3;	-- 第2页
SELECT * FROM student LIMIT 6,3;	-- 第3页

六、多表查询

查询语法：

select
	字段列表
from
	表名列表
where
	...

准备数据，创建表

-- 创建dept表（id，dept_name，dept_loc）
create table dept(
  id int primary key,
  dept_name varchar(20),
  dept_loc varchar(20)
);

INSERT INTO dept VALUES(20,'开发部',"南荒古地"),(30,'财务部',"佛地"),(40,'市场部',"天庭");
-- 创建emp表（id，emp_name，join_date，salary，dept_id）
create table emp(
  id int primary key ,
  emp_name varchar(20),
  join_date date,
  salary decimal(7,2),
  dept_id int(11),
  foreign key (dept_id) references dept(id)
);

-- 给表emp信息
insert into emp values (10002,"孙悟空",'2000-12-17',8800.00,20);
insert into emp values (10001,"金蝉",'2001-09-27',29750.00,30);
insert into emp values (10220,"白骨精",'2001-11-17',28500.00,20);
insert into emp values (10123,"嫦娥",'2001-12-03',24500.00,40);
insert into emp values (10124,"红孩儿",'2002-01-23',15000.00,30);
insert into emp values (10005,"八戒",'2007-04-19',30000.00,20);
insert into emp values (10006,"悟净",'2007-05-23',11000.00,40);
insert into emp values (10026,"杨戬",'2002-08-3',33000.00,40);
  • 笛卡尔积：有两个集合A,B .取这两个集合的所有组成情况。

笛卡尔积就是当直接查询两张表中的数据时没有条件限制,每一条A表的数据都会和B表的数据生成一条新的数据,产生了大量的重复无意义多余数据,即为笛卡尔积.

1.内连查询

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

  • 隐式内连查询: 使用where条件消除无用数据

    • 语法格式：

      • select 字段列表 from 表列表 where 条件；

      -- 隐式内连查询，查询所有的员工信息和部门信息
select * from emp e,dept d where e.dept_id=d.id;

    在这里插入图片描述

  • 显示内连查询：使用INNER JOIN … ON语句, 可以省略INNER

    • 语法格式：

      • SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 条件;

      -- 确定查询字段，查询唐僧的信息，显示员工id，姓名，工资和所在的部门名称
  select e.id,e.emp_name,e.salary,d.dept_name from emp e inner join dept d on e.dept_id=d.id where e.emp_name = "金蝉";

在这里插入图片描述

```sql
-- 显示内连接, 查询所有的员工名，入职时间和对应部门名
select e.emp_name,e.join_date,d.dept_name from emp e inner join dept d on e.dept_id = d.id;
```

在这里插入图片描述

  • 总结内连接查询步骤：
    1. 确定查询哪些表
    2. 确定表连接的条件
    3. 确定查询的条件
    4. 确定查询的字段

2.外连查询

  • 左外连接: 使用LEFT OUTER JOIN … ON，OUTER可以省略

    • 语法格式

      • SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;

      用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL
      可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

    • -- 查询所有员工的姓名,编号以及部门信息,如果员工有部门则显示部门,如果员工没有部门则不显示.
select e.id,e.emp_name,d.dept_name from emp e left join dept d on e.dept_id = d.id;

在这里插入图片描述

​ 左外连接查询的是左表的所有数据以及交集的部分.

  • 右外连接: 使用RIGHT OUTER JOIN … ON，OUTER可以省略

    • 语法格式

      • SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件;

        用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL
        可以理解为：在内连接的基础上保证右表的数据全部显示

        -- 查询所有部门的信息以及部门对应的员工,如果员工没有部门,则不显示
select d.dept_name,d.dept_loc,e.emp_name from emp e right join dept d on e.dept_id = d.id;

    在这里插入图片描述

    可以看出没有部门的小旋风没有

    • 右外连接查询的是右表的所有数据以及交集的部分.

3.子查询

-- 需求：查询开发部中有哪些员工
select * from emp;

-- 通过两条语句查询
select id from dept where name='开发部' ;
select * from emp where dept_id = 1;
-- 使用子查询
select * from emp where dept_id = (select id from dept where name='市场部');

  • 子查询的概念

    1. 一个查询的结果做为另一个查询的条件
    2. 有查询的嵌套，内部的查询称为子查询
    3. 子查询要使用括号

  • 子查询的三种情况

    1. 子查询的结果是单行单列

      • 子查询结果只要是单行单列，肯定在WHERE后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等

      • SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

      -- -- 1) 查询最高工资是多少
select max(salary) from emp;

-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);

-- 3) 查询平均工资是多少
select avg(salary) from emp;

-- 4) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);

    2. 子查询的结果是多行单列

      • 子查询结果是单例多行，结果集类似于一个数组，父查询使用IN运算符

      • SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

      范例1：查询工资大于15000的员工，来自于哪些部门的名字

      -- 查询工资大于5000的员工，来自于哪些部门的名字
-- 1.先查询大于5000的员工所在的部门id
select dept_id from emp where salary>15000;
-- 2.再查询在这些部门id中部门的名字
select dept_name from dept d where id in (select dept_id from emp  where salary>15000);

      范例2：查询开发部与财务部所有的员工信息

      -- 先查询开发部与财务部的id
select id from dept where dept_name in('开发部','财务部');
-- 再查询在这些部门id中有哪些员工
select * from emp where dept_id in (select id from dept where dept_name in('开发部','财务部'));

    3. 子查询的结果是多行多列

      • 子查询结果只要是多列，肯定在FROM后面作为表

      • SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

        子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

      范例：查询出2011年以后入职的员工信息，包括部门名称

      -- 查询出2011年以后入职的员工信息，包括部门名称
-- 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date >='2011-1-1';

-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.id = e.dept_id ;

-- 也可以使用表连接：
select * from emp inner join dept on emp.dept_id = dept.id where join_date >='2011-1-1';
select * from emp inner join dept on emp.dept_id = dept.id and join_date >='2011-1-1';

  • 子查询结果只要是单列，则在WHERE后面作为条件

  • 子查询结果只要是多列，则在FROM后面作为表进行二次查询

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

渡劫得得得
关注
专栏目录
mysql学习总结
01-04
中继日志与二进制日志的格式相同，并且可以用mysqlbinlog读取 如果你没有备份主服务器，这里是一个创建备份的快速程序。所有步骤都应该在主服务器主机上执行。 10 操作加解锁 1. 发出该语句： mysql> FLUSH TABLES WITH READ LOCK； 2. 仍然加锁时，执行该命令（或它的变体）： shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql 3. 发出该语句并且确保记录了以后用到的输出： mysql>SHOW MASTER STATUS；
数据库MySQL学习总结
最新发布
05-30
数据库MySQL学习总结
mysql学习总结.docx
03-11
MySQL数据库学习总结 MySQL 是一种关系数据库管理系统（RDBMS），它被广泛应用于 Web 应用程序中。学习 MySQL 是一个非常重要的任务，以下是 MySQL 学习笔记中的重要知识点总结。 一、备份和恢复 mysqldump 命令...
关系型数据库管理系统之MySql学习总结
12-14
本篇文章将围绕MySQL的学习进行深入探讨。 数据库是存储数据的场所，用户可以通过执行增删改查等操作来管理数据。数据库系统采用一定的数据模型，如关系型模型，以确保数据的结构化和有序性。关系型数据库中，数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值