MySQL基础知识丨青训营笔记

lastHertz
42 阅读9分钟

前置要求

  • mysql8.0+
  • 数据库管理工具：navicat或者其他

数据库，表基础

连接MySQL

  1. 首先Win + R打开运行对话框，输入cmd
  2. 在命令行输入mysql -u 用户名（默认是root） -p，出现错误先检查是否是环境变量没配置好
  3. 输入密码
  4. 登录成功会出现

  1. 退出exit

数据库的基本命令

show databases; //查询所有数据库

create database 数据库名; //创建数据库

use 数据库名； //切换数据库

drop database 数据库名； //删除数据库

drop table；//删除表名

show tables；//查看当前库下的所有表

crud

查询

  • 用法
SELECT 列名，列名 FROM 表名;
  • 示例
SELECT name, age FROM user;
//从user表查询用户姓名、年龄

新增

  • 用法
INSERT INTO 表名（字段名1， 字段名2...）
VALUES（值1，值2...）;

//批量添加数据
INSERT INTO 表名（字段名1， 字段名2...）
VALUES（值1， 值2...）,（值1，值2...）

//注意事项：字符型插入必须使用单引号'或者双引号"
如果某个字段不需要值，需要填NULL
  • 示例
INSERT INTO user (name, age)
VALUES ('user1', 18);
//往user表插入一条数据，name='user1',age=18

修改

  • 用法
UPDATE 表名 
SET 字段名1 = 值1， 字段名2 = 值2；
//注意事项：如果没有指定WHERE条件，则表示整表数据都会进行修改
  • 示例
UPDATE user
SET age = 20 
WHERE user_id = 1;
//将用户表user_id为1的用户年龄更新成20

删除

  • 用法
DELETE FROM 表名；
  • 示例
DELETE FROM user WHERE user_id = 1;
//将用户id为1的数据删除

关键字

WHERE

  • WHERE 用于过滤查询结果，只返回满足特定条件的行。
WHERE 条件
符号描述
=等号
<>, !=不等于
大于
<小于
>=大于等于
<=小于等于
  • 示例
SELECT id, name, pwd FROM user WHERE id = 1;
//查询id为1的用户信息，id、name和pwd

AND, OR

  • AND：前后条件需都为真
  • OR：只要一边为真即可
SELECT * FROM user WHERE college = "计信" AND class = "软件2103" 
//查询学院是计信学院并且班级是软件2103的用户

SELECT * FROM user WHERE college = "计信" OR class = "软件2103"
//查询学院是计信学院或者班级是软件2103的用户

LIKE

  • LIKE子句表示模糊搜索符合条件的字符串
  • % 表示零个或多个字符
  • _表示一个字符
SELECT * FROM user WHERE name LIKE "user%"
匹配用户名以"user"开头的任意字符串，例如：user，user1，user2，user111

SELECT * FROM user WHERE name LIKE "user_"
匹配用户名以"user"开头的额外一个字符的字符串，例如：user1，user2，

IN

  • IN用于规定多个值
IN (value1, value2)

SELECT * FROM user WHERE name IN ("user1", "user2")
查询user1和user2用户的信息

BETWEEN

  • BETWEEN用于选取两个值之间的数据
column BETWEEN value1 AND value2;

SELECT * FROM user WHERE create_time BETWEEN '2024-01-1' AND '2024-06-1';
//查询创建时间在2024.1.1到2024.6.1的用户

NOT

  • NOT用于反转接着的条件
SELECT * FROM user WHERE create_time NOT BETWEEN '2024-01-1' AND '2024-06-1';
//查询创建不在2024.1.1到2024.6.1的用户

IS NULL

  • IS NULL用于判断值是否为NULL
SELECT * FROM user WHERE class IS NULL;
//查询未填写班级的用户

DISTINCT

  • DISTINCT用于消除重复记录
DISTINCT column
  • 示例
SELECT DISTINCT author_id FROM article;
//查询发布过文章的用户id，如果有用户发表多篇，但是没用DISTINCT就会出现id重复

ORDER BY

  • ORDER BY用于按字段排序数据
  • ASC（升序），DESC（降序）
  • 默认升序
ORDER BY column1 ASC | DESC

SELECT * FROM user
ORDER BY create_time ASC;
//按创建时间对用户进行排序

GROUP BY

  • GROUP BY对结果集进行分组
  • SELECT 的列通常要么是分组列，要么是聚合函数的参数。
GROUP BY column

SELECT author_id, COUNT(*) FROM article
GROUP BY author_id
//根据作者id进行分组，查询每个作者的文章数

HAVING

  • WHERE无法和聚合函数一起使用，HAVING用于分组后筛选数据
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

UNION

  • UNION 用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行
  • 每个 SELECT 语句的列数和对应位置的数据类型必须相同
  • UNION ALL不去重
SELECT column1, column2, ...
FROM table1

UNION

SELECT column1, column2, ...
FROM table2

AS

  • AS 用于别名
column AS ''

SELECT real_name AS name FROM user;

连接

  • 如果两个表使用的字段相同需要指定某个表

LEFT JOIN

  • LEFT JOIN（左连接） 用于获取左表所有记录，即使右表没有对应匹配的记录。
  • ON后面跟连接条件
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
  • 示例
//查询用户信息，有的用户可能没有头像，所以需要用左连接
SELECT name, age, filePath 
FROM user 
LEFT JOIN file 
ON user.avatar = file.id;

RIGHT JOIN

  • RIGHT JOIN（右连接） 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
  • ON后面跟连接条件
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

INNER JOIN

  • INNER JOIN（全连接）获取两个表中字段匹配关系的记录。
  • ON后面跟连接条件
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

函数

COUNT（x）

  • COUNT（x）用于返回查询的记录总数
SELECT COUNT(*) FROM user WHERE college = "计信学院"；
//查询计信学院的学生数
  • 统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。
SELECT COUNT(class) FROM `user`;
//查询班级不为NULL的学生数

MAX（x）

  • MAX（x）用于查询列最大值
SELECT MAX(follow) FROM user;
//查询关注数最多

MIN（x）

  • MIN（x）用于查询列最小值
SELECT MIN(follow) FROM user;
//查询关注数最小

AVG（x）

  • AVG（x）用于计算平均数
SELECT avg(salary) FROM emp
//查询员工的平均薪资

练习

  • 建表、数据sql
create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age  int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';

 create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
            
            (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
            (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
            (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),

            (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
            (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
            (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),

            (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
            (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
            (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
            (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),

            (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
            (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
            (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
            (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
          
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);
  1. 查询员工的姓名、年龄、职位、部门信息

  1. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息

  1. 查询没有直属领导的员工

  1. 查询拥有员工的部门id，部门名称

  1. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来

  1. 查询所有员工的工资等级

  1. 查询 "研发部" 所有员工的信息及 工资等级

  1. 查询 "研发部" 员工的平均工资

  1. 查询工资比 "灭绝" 高的员工信息

  1. 查询比平均薪资高的员工信息

  1. 查询每个部门平均薪资

  • sql参考
#1. 查询员工的姓名、年龄、职位、部门信息
SELECT emp.name as 姓名, age As 年龄, job as 职位, dept.name as 部门 
FROM emp 
INNER JOIN dept 
ON emp.dept_id = dept.id;

#2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息
SELECT emp.name as 姓名, age as 年龄, job as 职位, dept.name as 部门 
FROM emp 
INNER JOIN dept 
ON emp.dept_id = dept.id
WHERE age < 30;


#3. 查询没有直属领导的员工
SELECT e.name AS 员工姓名, d.name AS 部门名称
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id
WHERE e.managerid IS NULL;

#4. 查询拥有员工的部门id，部门名称
SELECT DISTINCT dept.id, dept.name as 部门
FROM emp 
INNER JOIN dept 
ON emp.dept_id = dept.id;

#5. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
SELECT emp.name as 姓名, age as 年龄, dept.name as 部门 
FROM emp 
LEFT JOIN dept 
ON emp.dept_id = dept.id
WHERE age > 40;

#6. 查询所有员工的工资等级
SELECT e.name as 姓名, s.grade AS 工资等级 
FROM emp e 
INNER JOIN salgrade s 
ON e.salary BETWEEN s.losal and s.hisal;

#7. 查询 "研发部" 所有员工的信息及 工资等级
SELECT e.*, s.grade
FROM emp e
LEFT JOIN dept d 
ON e.dept_id = d.id
LEFT JOIN salgrade s 
ON e.salary BETWEEN s.losal AND s.hisal
WHERE d.name = '研发部';

#8. 查询 "研发部" 员工的平均工资
SELECT avg(e.salary) as '平均工资' 
FROM emp e 
INNER JOIN dept d 
ON e.dept_id = d.id 
WHERE d.name = '研发部';

#9. 查询工资比 "灭绝" 高的员工信息
SELECT * 
FROM emp 
WHERE salary > ( 
    SELECT salary 
    FROM emp 
    WHERE name = '灭绝' 
);

#10. 查询比平均薪资高的员工信息
SELECT * 
FROM emp 
WHERE salary > ( 
    SELECT avg(salary) 
    FROM emp 
);

#11. 查询每个部门平均薪资
SELECT d.name AS 部门, AVG(e.salary) AS 平均薪资
FROM dept d
JOIN emp e ON d.id = e.dept_id
GROUP BY d.name;

Tip

  1. 力扣有数据库的题目，leetcode.cn/problemset/…
  2. 牛客也有，

www.nowcoder.com/exam/intell…

avatar
文章
阅读
粉丝