mysql语句进阶

陈洪奇

于 2023-06-23 19:40:48 发布

阅读量90

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_36933261/article/details/131353582

版权

数据库大全

操作数据库

-- 创建库db_Test
create database db_Test;

-- 判断创建库db_Test是否存在，不存在则创建
create database if not exists db_Test;

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

-- 查看数据库db_Test的定义情况
show create database db_Test;

-- 修改数据库db_Test中文字符
alter database db_Testcharacter set utf8;

-- 删除数据库db_Test
drop database  db_Test;

操作表

– 创建学生表 tb_stu 并且设置主键非空，自增长还有字符一次性设置完

CREATE TABLE tb_stu(
    
id int PRIMARY KEY AUTO_INCREMENT NOT NULL,--设置主键非空自增长
    
stuname VARCHAR(32)  NOT NULL
    
)default charset=utf8;--设置字符

– 普通创建学生表tb_stu

create table tb_stu(
    
id int,
    
name varchar(32),
    
age int ,
    
score double(4,1),
    
birthday date,
    
insert_time timestamp
    
);

– 创建普通之后在单独设置主键，非空以及字符还有自增长语句

-- 设置主键
ALTER TABLE tb_stu ADD PRIMARY KEY(id);

-- 删除主键，注意：我们在删除主键的时候必须要确保表里面没有任何一条数据才可以执行
ALTER TABLE tb_stu DROP PRIMARY KEY;

-- 设置自增长
ALTER TABLE tb_stu modify id int AUTO_INCREMENT;

-- 删除自增长
ALTER TABLE tb_stu DROP COLUMN id;

-- 设置非空约束里面的id int()是字段和类型
ALTER TABLE tb_stu MODIFY id INT(11) NOT NULL;

-- 取消设置的非空约束stuname varchar(32)是字段名和类型
ALTER TABLE tb_stu MODIFY stuname varchar(32) NULL;

– 表tb_stu相关操作

-- 查看表tb_stu结构
desc tb_stu;

-- 查看创建表tb_stu的SQL语句
show create table tb_stu;

-- 修改表tb_stu名为t_stu
alter table tb_stu rename to t_stu;

-- tb_stu添加一列adder
alter table tb_stu add adder varchar(32);

-- tb_stu删除列 adder
alter table tb_stu drop adder;

-- 删除表tb_stu
drop table tb_stu;
drop table if exists tb_stu ;

– 增加 tb_stu 数据 insert into

-- 插入部分数据
insert into tb_stu(id,name,adder) values("001","zhangsan","北京");

-- 不写列名（所有列全部添加）
insert into tb_stu values("001","zhangsan","北京";

-- 插入部分数据
insert into tb_stu(adder) values("北京");

– tb_stu删除语句 delete

-- 删除表中数据
delete from tb_stu where id = 1;

-- 删除表中所有数据
delete from tb_stu;

-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table tb_stu;

– 更新表tb_stu数据

-- 不带条件的修改(会修改所有行)
update tb_stu set adder = "深圳";

-- 带条件的修改
update tb_stu set adder = "深圳" where id=1;

查询

– 查询概括

主要介绍排序查询、聚合函数、模糊查询、分组查询、分页查询、内连接、外连接、子查询

– 基础关键字

BETWEEN…AND （在什么之间）和 IN( 集合)

-- 查询年龄大于等于20 小于等于30 三种方式
SELECT * FROM tb_stu WHERE age >= 20 && age <=30;
SELECT * FROM tb_stu WHERE age >= 20 AND age <=30;
SELECT * FROM tb_stu WHERE age BETWEEN 20 AND 30;

-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM tb_stu WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM tb_stu WHERE age IN (22,18,25);

is null(不为null值) 与 like（模糊查询）、distinct（去除重复值）

-- 查询英语成绩不为null
SELECT * FROM tb_stu WHERE english IS NOT NULL;

-- 查询姓马的有哪些？ like  _:单个任意字符  %：多个任意字符
SELECT * FROM tb_stu WHERE NAME LIKE '马%';

-- 查询姓名第二个字是化的人
SELECT * FROM tb_stu WHERE NAME LIKE "_化%";

-- 查询姓名是3个字的人
SELECT * FROM tb_stu WHERE NAME LIKE '___';

-- 查询姓名中包含德的人
SELECT * FROM tb_stu WHERE NAME LIKE '%德%';

-- 关键词 DISTINCT 用于返回唯一不同的值，语法：SELECT DISTINCT 列名称 FROM 表名称
SELECT DISTINCT NAME FROM tb_stu ;

– 排序查询 order by

/**
* 语法：order by 子句
* order by 排序字段1 排序方式1 ， 排序字段2 排序方式2...
* 注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
*/
SELECT * FROM tb_person ORDER BY math; -- 默认升序
SELECT * FROM tb_person ORDER BY math desc; -- 降序

– 聚合函数：将一列数据作为一个整体，进行纵向的计算函数（列名）

-- count：计算个数
SELECT count(name) FROM tb_stu

-- max：计算最大值
SELECT max(age) FROM tb_stu

-- min：计算最小值
SELECT min(age) FROM tb_stu

-- sum：计算和
SELECT sum(age) FROM tb_stu

-- avg：计算平均数
SELECT avg(age) FROM tb_stu

– 分组查询 grout by

/**
* 语法：group by 分组字段;
* 注意：分组之后查询的字段：分组字段、聚合函数
*/

-- 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM tb_stu GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数
SELECT sex , AVG(math),COUNT(id) FROM tb_stu GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math),COUNT(id) FROM tb_stu WHERE math > 70 GROUP BY sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人 num是人数
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) num FROM student WHERE math > 70 GROUP BY sex HAVING num > 2;

– 分页查询

语法：limit 开始的索引,每页查询的条数;

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

limit 是一个MySQL"方言"。

-- 每页显示3条记录
SELECT * FROM tb_stu LIMIT 0,3; -- 第1页
SELECT * FROM tb_stu LIMIT 3,3; -- 第2页
SELECT * FROM tb_stu LIMIT 6,3; -- 第3页

– 内连接查询

从哪些表中查询数据，条件是什么，查询哪些字段

隐式内连接：使用where条件消除无用数据

-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

SELECT
t1.name, -- 员工表的姓名
t1.gender,-- 员工表的性别
t2.name -- 部门表的名称
FROM
emp t1,
dept t2
WHERE
t1.`dept_id` = t2.`id`;

显式内连接

语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件

SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;

– 外连接查询

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;

右外连接 – 查询的是右表所有数据以及其交集部分。

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；

SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;

– 子查询：查询中嵌套查询

查询工资最高的员工信息

-- 1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;

-- 2 查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.salary = 9000;

-- 一条sql就完成这个操作。这就是子查询
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

子查询的结果是单行单列的,子查询可以作为条件，使用运算符去判断。运算符： > >= < <= =

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

子查询的结果是多行单列的：子查询可以作为条件，使用运算符in来判断

-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

子查询的结果是多行多列的：子查询可以作为一张虚拟表参与查询

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11'

管理用户

-- 添加用户
-- 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 删除用户
-- 语法：DROP USER '用户名'@'主机名';
-- 权限管理
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';

-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

-- 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.account FROM 'lisi'@'%';

存储过程

– 参数分类
存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

没有参数（无参数无返回）
仅仅带 IN 类型（有参数无返回）
仅仅带 OUT 类型（无参数有返回）
既带 IN 又带 OUT（有参数有返回）
带 INOUT（有参数有返回）

注意： IN、OUT、INOUT 都可以在一个存储过程中带多个。

– 创建存储过程(语法)

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END

说明：

– IN：当前参数为输入参数，也就是表示入参；

存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

– OUT：当前参数为输出参数，也就是表示出参；

执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

– INOUT：当前参数既可以为输入参数，也可以为输出参数。

– BEGIN…END：
BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。

– DECLARE：
DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。

– SET：赋值语句，用于对变量进行赋值。

– SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

– DELIMITER 新的结束标记

因为MySQL默认的语句结束符号为分号是 ; 。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

-- 建议新的结束标记不要太花里胡哨了
DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
[characteristics ...]
BEGIN
	sql语句1;
	sql语句2;
END $

– 调用存储过程(语法)

-- 语法
CALL 存储过程名(实参列表)

-- 调用in模式的参数：
CALL sp1('值');

-- 调用out模式的参数
SET @name;
CALL sp1(@name);
SELECT @name;

-- 调用inout模式的参数
SET @name=值;
CALL sp1(@name);
SELECT @name;

– 删除存储过程

-- 语法
DROP PROCEDURE [IF EXISTS] 存储过程名

– 查看存储过程

--  使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE  存储过程名

-- 使用SHOW STATUS语句查看存储过程和函数的状态信息

SHOW PROCEDURE  STATUS [LIKE 'pattern']

/**
* 这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。
* [LIKE 'pattern']匹配存储过程的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程的信息。
*/
-- 查询名称以select开头的存储过程
mysql> SHOW PROCEDURE STATUS LIKE 'SELECT%';
*************************** 1. row ***************************
                  Db: test_db
                Name: SelectAllData
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-10-16 15:55:07
             Created: 2021-10-16 15:55:07
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
  
-- 从information_schema.Routines表中查看存储过程的信息
-- MySQL中存储过程的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存储过程的名' [AND ROUTINE_TYPE = 'PROCEDURE'];

-- 如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

– 修改存储过程

-- 修改存储过程，不影响存储过程功能，只是修改相关特性。使用ALTER语句实现。
ALTER PROCEDURE  存储过程名;

– 练习

-- 准备工作
CREATE TABLE admin (
          id INT PRIMARY KEY AUTO_INCREMENT,
          user_name VARCHAR (15) NOT NULL,
          pwd VARCHAR (25) NOT NULL
) ;

 CREATE TABLE beauty (
          id INT PRIMARY KEY AUTO_INCREMENT,
          NAME VARCHAR (15) NOT NULL,
          phone VARCHAR (15) UNIQUE,
          birth DATE
) ;

INSERT INTO beauty(NAME,phone,birth) VALUES 
('朱茵','13201233453','1982-02-12'), 
('孙燕姿','13501233653','1980-12-09'), 
('田馥甄','13651238755','1983-08-21'), 
('邓紫棋','17843283452','1991-11-12'), 
('刘若英','18635575464','1989-05-18'), 
('杨超越','13761238755','1994-05-11');

-- 1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中 
DELIMITER $  -- ‘$’代表着语句的结束
CREATE PROCEDURE insert_user(IN username VARCHAR(15),IN `password` VARCHAR(25))
BEGIN 
	INSERT INTO admin VALUES (username,`password`);
END $  -- 存储过程结束
DELIMITER ;  -- 修改语句结束的标识符为‘;’

-- 2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话 
DELIMITER $
CREATE PROCEDURE get_phone(IN id INT,OUT bname VARCHAR(15),OUT bphone VARCHAR(15))
BEGIN
	SELECT beauty.name , beauty.phone 
	       INTO bname,bphone
	FROM beauty WHERE beauty.id = id;
END $
DELIMITER ;

-- 调用
CALL get_phone(1,@name,@phone);
SELECT @name , @phone;

MYSQL其他

虚拟表

-- 创建虚拟表
CREATE TEMPORARY TABLE u (
    new_assignment VARCHAR ( 66 ),
    key_id VARCHAR ( 64 ),
    is_del VARCHAR ( 1 ),
PRIMARY KEY ( key_id ));

-- 将数据插入临时表
INSERT INTO u ( new_assignment, key_id, is_del )
SELECT new_assignment, key_id, is_del FROM user_assign WHERE new_assignment = 1 AND is_del = 0 GROUP BY key_id;

– 临时表创建与删除

# 创建临时表
CREATE TEMPORARY TABLE TempAsiaCountries (
	Code VARCHAR (10),
	name VARCHAR (30),
	SufaceArea NUMERIC,
	Population NUMERIC
);

# 第一种
# 写数据到临时表
INSERT INTO TempAsiaCountries
SELECT
	Code,
	Name,
	SurfaceArea,
	Population
FROM
	country
WHERE
	Continent = 'Asia';


# 查询临时表
SELECT * FROM TempAsiaCountries;

# 第二种
# 查询数据并写到临时表中
CREATE TEMPORARY TABLE TempAsiaCountries 
SELECT
	`Code`,
	`Name`,
	`SurfaceArea`,
	`Population`
FROM
	country
WHERE
	`Continent` = 'Asia';

# 删除临时表
DROP TABLE TempAsiaCountries;

更多学习资料，请参考我的博客https://mp.csdn.net/mp_blog/manage/article?spm=3001.5298