Mysql常用基础知识

最新推荐文章于 2024-07-24 16:18:52 发布

云深不知处丿

最新推荐文章于 2024-07-24 16:18:52 发布

阅读量125

点赞数

文章标签： mysql 数据库 sql

原文链接：https://www.bilibili.com/video/BV1iq4y1u7vj/?spm_id_from=333.1007.top_right_bar_window_custom_collection.content.click&vd_source=eee2a07150b6fae114fa796966a1eae4

版权

一、查询语句

1.SELECT 字段 FROM 表
2.取别名
SELECT last_name AS name FROM employees;
3.去重
SELECT DISTINCT department_id FROM employees;
4.查看表结构
DESC employees;

二、运算符

1.加减乘除
SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 FROM dual;
2.求模,取余
SELECT 12 % 3, 12 MOD 5 FROM dual; 结果为：0,2
3.比较运算符在这里插入图片描述
4.空运算符
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
5.非空运算符
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
6.最小值运算符
SELECT LEAST (1,0,2), LEAST(‘b’,‘a’,‘c’), LEAST(1,NULL,2);
7.最大值运算符
SELECT GREATEST(1,0,2), GREATEST(‘b’,‘a’,‘c’), GREATEST(1,NULL,2);
8.BETWEEN AND运算符
SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;
9.IN运算符
WHERE manager_id IN (100, 101, 201);
10.NOT IN运算符
WHERE** manager_id NOT IN (100, 101, 201);
11. LIKE运算符注：“%”：匹配0个或多个字符。“_”：只能匹配一个字符
SELECT last_name FROM employees WHERE last_name LIKE ‘_o%’;
12.与运算符
SELECT job_id,salary FROM employees WHERE salary >=10000 AND job_id LIKE ‘%MAN%’;
13.或运算符
SELECT job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE ‘%MAN%’;
注意：
OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先
对AND两边的操作数进行操作，再与OR中的操作数结合。

三、排序与分页

注：1.ASC（ascend）: 升序 2.DESC（descend）:降序 3.ORDER BY 子句在SELECT语句的结尾
1.单列排序
SELECT hire_date FROM employees ORDER BY hire_date DESC
2.多列排序
SELECT department_id, salary FROM employees ORDER BY department_id, salary DESC;
3.分页（LIMIT [位置偏移量,] 行数）
–前10条记录：SELECT * FROM 表名 LIMIT 0,10;

四、多表查询

1.SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; #连接条件(可在表后起别名)
2.等值连接
SELECT employees.last_name, departments.department_name,employees.department_id FROM employees, departments WHERE employees.department_id = departments.department_id;
注意：连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件
3.非等值连接
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
4.自连接
SELECT CONCAT(worker.last_name ,’ works for ', manager.last_name) FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;（使用同一张表）
补充：
①内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
②外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的
行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
③左外连接:则连接条件中左边的表也称为主表，右边的表称为从表。
④右外连接:则连接条件中右边的表也称为主表，左边的表称为从表。
5.内连接
SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件 WHERE 等其他子句;
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);
6.左外连接
SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句
SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
7.右外连接
FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;
SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
8.UNION合并查询
SELECT column,… FROM table1 UNION [ALL] SELECT column,… FROM table2
EG:
①SELECT * FROM employees WHERE email LIKE ‘%a%’ UNION SELECT * FROM employees WHERE department_id>90;
②SELECT id,cname FROM t_chinamale WHERE csex=‘男’ UNION ALL SELECT id,tname FROM t_usmale WHERE tGender=‘male’;
①补充：UNION 操作符返回两个查询的结果集的并集，去除重复记录。UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重
②注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效
率。

五、单行函数(只对一行进行变换,每行返回一个结果,可以嵌套,参数可以是一列或一个值)

1.数值函数
在这里插入图片描述
2.三角函数

3.字符串函数

4.日期函数

六、聚合函数(非法使用聚合函数：不能在 WHERE 子句中使用聚合函数)

1.AVG和SUM函数和MIN和MAX函数
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE ‘%REP%’;
2.COUNT函数(计算一共有多少行数据)
SELECT COUNT(*) FROM employees WHERE department_id = 50;
3.HAVING函数（使用了聚合函数，满足HAVING 子句中条件的分组将被显示，HAVING 不能单独使用，必须要跟 GROUP BY 一起使用，必须在where后面）
关键字顺序：SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…
SELECT执行顺序:FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

七、创建与管理

1.创建库
CREATE DATABASE IF NOT EXISTS 数据库名;
2.查看当前所有的数据库
SHOW DATABASES; #有一个S，代表多个数据库
3.查看当前正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数
4.查看指定库下所有的表
SHOW TABLES FROM 数据库名;
5.查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
6.使用/切换数据库
USE 数据库名;
7.修改库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
8.删除指定的数据库
DROP DATABASE IF EXISTS 数据库名;
9.创建表
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
10.追加表一列
ALTER TABLE dept80 ADD job_id varchar(15);
11.修改一列
ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
12.重命名一列
ALTER TABLE dept RENAME [TO] detail_dept; – [TO]可以省略
13.删除一个列
ALTER TABLE 表名 DROP 【COLUMN】字段名
14.删除表
DROP TABLE [IF EXISTS] depot;
15.清空表（不能回滚）
TRUNCATE TABLE detail_dept;

八、数据的增删改

1.插入数据
INSERT INTO departments VALUES (70, ‘Pub’, 100, 1700) (70, ‘Pub’, 100, 1700) (70, ‘Pub’, 100, 1700);（可以插入多条）
补充：INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行
INSERT INTO emp2 SELECT * FROM employees WHERE department_id = 90;
2.修改数据
UPDATE employees SET department_id = 70 WHERE employee_id = 113;
3.删除数据
DELETE FROM departments WHERE department_name = ‘Finance’;

九、约束

1.非空约束
字段名数据类型 NOT NULL
修改为非空：alter table 表名称 modify 字段名数据类型 not null;
删除非空:alter table 表名称 modify 字段名数据类型 NULL;
2.唯一约束
字段名数据类型 unique
修改为唯一：ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE;
3.主键
字段名数据类型 primary key
删除主键：alter table 表名称 drop primary key;
4.自增
字段名数据类型 auto_increment（必须为整型）

十、视图

创建单表视图
CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80;
2.创建多表联合视图
CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

十一、流程控制

1.分支结构之 IF
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
2.分支结构之 CASE
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
…
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
eg:
CASE
WHEN val IS NULL THEN SELECT ‘val is null’;
WHEN val < 0 THEN SELECT ‘val is less than 0’;
WHEN val > 0 THEN SELECT ‘val is greater than 0’;
ELSE SELECT ‘val is 0’;
END CASE;
3.循环结构之LOOP
问题：使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;
4.循环结构之WHILE
问题：WHILE语句示例，i值小于10时，将重复执行循环过程，代码如下
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
SELECT i;
END //
DELIMITER ;
#调用
CALL test_while();
5. 循环结构之REPEAT（REPEAT语句内的语句或语句群被重复，直至expr_condition为真）
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
UNTIL i >= 10
END REPEAT;
SELECT i;
END //
DELIMITER ;
6.跳转语句之LEAVE语句（break）
问题：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN…END加标记名，并在
BEGIN…END中使用IF语句判断num参数的值。
如果num<=0，则使用LEAVE语句退出BEGIN…END；
如果num=1，则查询“employees”表的平均薪资；
如果num=2，则查询“employees”表的最低薪资；
如果num>2，则查询“employees”表的最高薪资
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num<=0
THEN LEAVE begin_label;
ELSEIF num=1
THEN SELECT AVG(salary) FROM employees;
ELSEIF num=2
THEN SELECT MIN(salary) FROM employees;
ELSE
SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END //
DELIMITER
7.跳转语句之ITERATE语句（ continue）
问题：定义局部变量num，初始值为0。循环结构中执行num + 1操作。
如果num < 10，则继续执行循环；
如果num > 15，则退出循环结构；
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
SELECT ‘尚硅谷：让天下没有难学的技术’;
END LOOP my_loop;
END //
DELIMITER ;