mysql常用操作语句（详细）_命令行退出mysql

本文使用的 MySQL 版本：8.2.0

1.使用mysql

管理员权限下开启和关闭mysql服务

net stop mysql
net start mysql

cmd命令行进入mysql

mysql -uroot -p123456 //root用户名，123456密码，没密码则不输入

 cmd命令行退出mysql

exit // 或者 quit

创建数据库

create database cAuth;
 
// 更加规范的写法是关键词全大写, 以增强可读性
 
CREATE DATABASE cAuth;

显示有哪些数据库

SHOW DATABASES;

进入某个数据库

USE cAuth;

创建表

CREATE TABLE books(
    id int NOT NULL AUTO_INCREMENT,
    book_name varchar(50) NOT NULL,
    year int NOT NULL,
    introduction text NULL,
    PRIMARY KEY(id)
);
 
// 创建 books 表,
// id 整型 不为空 自动递增
// book_name 字符类型限制50字符 不为空
// year 整型 不为空
// introduction 文本类型 不为空
// 主键为id

查看有那些表

SHOW TABLES;

查看某个表详情（或列信息）

SHOW COLUMN FROM books;
// 或者简写语法
DESCRIBE books;
// 或者
DESC books;

2.select 查询

查询表的所有数据

SELECT * FROM books;

查询指定列数据

SELECT id,book_name FROM books;

列数据去重

SELECT DISTINCT book_name FROM books;
// 如果有两个重复的名称, 则只返回一条数据
// DISTINCT 作用于所有指定的列, 而不是前置它的列, 只有指定的所有列数据都不同时才会过滤
// 比如
SELECT DISTINCT book_name,year FROM books;
// 只有 book_name 和 year 都相同时才算重复数据

limit 限制返回数据数量

SELECT * FROM books LIMIT 3; // 只返回 3 条数据
// 等价于
SELECT * FROM books LIMIT 0,3;
// 等价于
SELECT * FROM books LIMIT 3 OFFSET 0; // 表示从第1行开始返回3行数据
 
// 返回下一个 3 条数据
SELECT * FROM books LIMIT 3,3; // 代表从第4行开始返回3条数据
// 第一个3代表开始位置, 第二个3代表行数

使用完全限定的表名

SELECT books.book_name FROM books;
 
// 除了列能限定外, 表名也可以限定
SELECT books.book_name FROM cAuth.books;

3.数据排序

基础排序

SELECT * FROM books ORDER BY year; // 默认升序排列
// 除了按单列排序外, 也可以指定多列进行排序
SELECT * FROM books ORDER BY year,book_name;
// 这里会先按year进行排序, 只有year相同的, 其内部才会按book_name排序

指定排序方向

// 升序
SELECT * FROM books ORDER BY year ASC; // 默认就是升序, ASC 可不写
 
// 降序
SELECT * FROM books ORDER BY year DESC;
 
// 多列字段
SELECT * FROM books ORDER BY year DESC,book_name; // year降序,book_name升序

获取最大/最小值

SELECT year FROM books ORDER BY year DESC LIMIT 1;
// 返回最大year, 注意: LIMIT 子句应在 ORDER BY 之后

4.where 条件过滤

使用 where

SELECT * FROM books WHERE book_name='book1';
// 注意: mysql 在执行匹配时是不区分大小写的

where 子句操作符

范围值查询

SELECT * FROM books WHERE year BETWEEN 2020 AND 2022;
// 返回year为2020至2022的书籍, 包括开始值和结束值

空值查询

SELECT * FROM books WHERE book_name IS NULL;
// 返回名称为null的数据

and 与 or 与 in

// and 所有条件都要满足
SELECT * FROM books WHERE book_name='book1' AND year=2020;
 
// or 至少满足条件之一
SELECT * FROM books WHERE book_name='book1' OR year=2020;
 
// and 的优先级高于 or, 查询时注意是否需要加括号
SELECT * FROM books WHERE (year=2020 OR year=2021) AND book_name='book1';
 
// 上面的语句用 in 改写, 其效果是等价的
SELECT * FROM books WHERE year IN (2020,2021) AND book_name='book1';

not 操作符

SELECT * FROM books WHERE year NOT IN (2020,2021); // 返回除2020,2021之外的数据
 
// mysql 支持使用NOT对IN、BETWEEN和EXISTS子句取反

5.通配符过滤

百分号(%)通配符

// %通配符可以匹配0个、1个或多个字符
 
// 返回以book开头的数据
SELECT * FROM books WHERE book_name LIKE 'book%';
 
// %通配符可以写在任意位置, 也可以多个使用, 以下语句代表包含 book 的数据
SELECT * FROM books WHERE book_name LIKE '%book%';

下划线(_)通配符

// _通配符与%通配符作用一样, 但只能匹配一个字符而不是多个, 也不是0个
 
// 返回以book开头且其后跟随任意一个字符的数据, 如 book1
SELECT * FROM books WHERE book_name LIKE 'book_';

注意：不要过度使用通配符，若其他操作符能达到相同目的，则优先使用其他操作符。

6.正则表达式过滤

基本字符匹配

// 匹配包含book的数据, 不区分大小写
SELECT * FROM books WHERE book_name REGEXP 'book';
 
// MySQL中正则表达式匹配(从版本3.23.4后)不区分大小写, 这里可以用BINARY进行区分
SELECT * FROM books WHERE book_name REGEXP BINARY 'book1';
 
// BINARY 在 8.0.0版本后会报错
 
// .匹配任意一个字符
SELECT * FROM books WHERE book_name REGEXP 'book.';
// 返回book开头且后面跟任意一个字符的数据

or匹配

SELECT * FROM books WHERE book_name REGEXP 'book1|book2';

匹配多个字符之一

// 返回 book1,book2
SELECT * FROM books WHERE book_name REGEXP 'book[12]';
 
// 返回 不包括book1,book2
SELECT * FROM books WHRER book_name REGEXP 'book[^12]';

匹配范围

// [0-9]匹配任意数字, [a-z]匹配任意字符
SELECT * FROM books WHERE book_name REGEXP 'book[0-9]';

匹配特殊字符

// 匹配.  使用 \\ 进行转义
SELECT * FROM books WHERE book_name REGEXP '\\.';

匹配多个实列

// 返回 book 后跟1到2个数字的数据
SELECT * FROM books WHERE book_name REGEXP 'book[0-9]{1,2}';

 定位符

// 返回以book开始的数据
SELECT * FROM books WHERE book_name REGEXP '^book';

 7.创建计算字段

拼接字段 Concat()

// 将年份和书名拼接, 如: 《book1》--2020
SELECT Concat('《',book_name,'》','--',year) FROM books;
 
// 以上查询结果没得列名, 可以使用 AS 关键字声明别名
SELECT Concat('《',book_name,'》','--',year) AS book_detail FROM books;

执行算数计算

// 将数量和单价相乘得到每本书总价
SELECT book_name,quantity*price AS total_price FROM books;
 
// mysql 支持加减乘除

8.使用数据处理函数

文本处理函数

// RTrim()处理右侧空格, LTrim()处理左侧空格, Trim()处理两侧空格
SELECT Trim(book_name) FROM books;

日期和时间处理函数

// 匹配日期
SELECT * FROM books WHERE Date(create_date)='2020-10-10';
 
// 查询2020-09月的数据
SELECT * FROM books WHERE Date(create_date)
    BETWEEN '2020-09-01' AND '2020-09-30';
// 或者
SELECT * FROM books WHERE Year(create_date)='2020' AND Month(create_date)=9;

数值处理函数

9.数据汇总

聚集函数

// 返回平均价格
SELECT AVG(price) AS avg_price FROM books;
 
// 返回平均价格, 过滤掉相同的价格再汇总进行平均
SELECT AVG(DISTINCT price) AS avg_price FROM books;
 
// 返回数据总条数
SELECT COUNT(*) AS num_books FROM books;
 
// 返回指定列的总条数, 会忽略 Null
SELECT COUNT(book_name) AS num FROM books;

10.分组数据

创建分组

// 查看每个作者有几本书
SELECT author,COUNT(*) AS num_books FROM books GROUP BY author;

过滤分组

// where 过滤行, having 过滤分组
// having 支持所有 where 操作符
 
// 返回有两本及以上书的作者数据
SELECT author,COUNT(*) AS num_books FROM books
    GROUP BY author HAVING COUNT(*)>=2;
 
// 返回2020年出版且作者出版了2本及以上的数据
SELECT author,COUNT(*) AS num_books FROM books WHERE year=2020
    GROUP BY author HAVING COUNT(*)>=2;

分组与排序

// 返回作者书籍的平均售价, 且按平均价格倒序排序
SELECT author,AVG(price) AS avg_price FROM books
    GROUP BY author ORDER BY avg_price DESC;

select 子句顺序

// 顺序如下
SELECT
FROM
WHERE // 过滤行
GROUP BY // 分组
HAVING // 过滤组
ORDER BY // 排序
LIMIT

11.子查询

子查询，即嵌套在其他查询中的查询。

子查询过滤

// 问题: 已知书籍名称, 查询购买此书籍的用户信息
 
// 注: 书籍表(books), 用户表(users), 订单表(orders)。
// 订单表中表中包含user_id和book_id
 
// 问题可以通过以下3步进行拆分
// 1.通过书籍名称查询书籍id, 查询结果为 1
SELECT id FROM books WHERE book_name='book1';
 
// 2.通过书籍id=1, 查询用户id, 查询结果为 1,3
SELECT user_id FROM orders WHERE book_id IN(1);
 
// 3.通过用户id=1,3, 查询用户详情
SELECT * FROM users WHERE id IN(1,3);
 
// 使用子查询
SELECT * FROM users WHERE id IN(
    SELECT user_id FROM orders WHERE book_id IN(
        SELECT id FROM books WHERE book_name='book1'
    )
);

作为计算字段使用子查询

// 显示用户表中每个用户的订单总数
SELECT name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id=users.id) AS orders_num
FROM users;

12.联结表

内部联结或等值联结

// 将出版商表(publishers)和书籍表(books)进行联结查询
SELECT book_name,publisher_name FROM books,publishers
    WHERE books.publisher_id=publishers.id;
 
// 以上联结为等值联结或内部联结, 可以用以下语法明确指定其类型, 其返回结果是相同的
SELECT book_name,publisher_name FROM books INNER JOIN publishers
    ON books.publisher_id=publishers.id;

自联结

// 查询书籍 book1 对应出版商出版的所有书籍. 这里使用 AS 给表设置别名
SELECT b2.book_name FROM books AS b1,books AS b2
    WHERE b1.publisher_id=b2.publisher_id
    AND b1.book_name='book1';

外部联结

// 问题: 查询所有书籍的出版商信息, 没有出版商的返回null
 
// 使用内部联结是没法返回书籍出版商为null的数据
SELECT book_name,publisher_name FROM books INNER JOIN publishers
    ON books.publisher_id=publishers.id;
 
// 使用外部联结则可以返回书籍出版商为null的数据
SELECT book_name,publisher_name FROM books LEFT OUTER JOIN publishers
    ON books.publisher_id=publishers.id;
 
// 使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表
// RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表

带聚集函数的联结

// 问题: 查询所有用户的订单数量
SELECT username,COUNT(orders.id) AS order_num
    FROM users LEFT OUTER JOIN orders
    ON users.id=orders.user_id
    GROUP BY users.id;

13.组合查询（也称并查询或复合查询）

使用UNION

// 问题: 返回价格小于10, 或者出版商id为2的书籍数据
 
// 首先想到使用 where 和 or 进行条件查询
SELECT * FROM books WHERE price<10 OR publisher_id=2;
 
// 使用 UNION 关键字将两个查询语句组合, 其返回结果是相同的
SELECT * FROM books WHERE price<10
UNION
SELECT * FROM books WHERE publisher_id=2;
// UNION前一句语句单独执行会返回3条数据, 后一句语句会返回2条数据,
// 最终返回了4条数据而不是5条数据, 是因为过滤了重复的行,
// 使用UNION ALL可以取消过滤, 使其返回所有数据, 这是WHERE所子句不能实现的功能
 
// 在这个简单例子中, 使用UNION比使用WHERE子句更为复杂, 但对于更复杂的过滤条件,
// 或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单

UNION规则

• UNION 必须由两条或两条以上的 SELECT语句组成，语句之间用关键字 UNION 分隔；
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）；
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）

组合查询结果排序

// 用UNION组合查询时，只能使用一条ORDER BY子句，
// 它必须出现在最后一条SELECT语句之后
SELECT * FROM books WHERE price<10
UNION
SELECT * FROM books WHERE publisher_id=2
ORDER BY id DESC;

CREATE TABLE books(
    id int NOT NULL AUTO_INCREMENT,
    book_name varchar(50) NOT NULL,
    year int NOT NULL,
    introduction text NULL,
    PRIMARY KEY(id),
    FULLTEXT(introduction)
);
// 使用 FULLTEXT(introduction) 来设置索引

SELECT * FROM books WHERE Match(introduction) Against('mysql');
 
// Match()指定被搜索的列，Against()指定要使用的搜索表达式
// 返回结果不区分大小写
// 注: 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果

INSERT INTO users VALUES(null, 'Bob'); // 对应字段 id, username
 
// 以上语句依赖特定的字段顺序, 并不安全, 更安全的写法如下
 
INSERT INTO users(username) VALUES('Bob');
// 这里可以省略不必要的字段, 比如id

INSET INTO users(username) VALUES('Tom'),('Jerry');

INSERT INTO users(username) SELECT username FROM users2;
// 这里的列名不一定要相同, 只要值是对应的就行

UPDATE users SET username='Dell',age=18 WHERE id=8;
 
// 若不加 where 子句将修改所有行

DELETE FROM users WHERE id=8;
 
// 若不加 where 子句将删除所有行

CREATE TABLE books(
    id int NOT NULL AUTO_INCREMENT,
    book_name varchar(50) NOT NULL,
    year int NOT NULL,
    quantity int NOT NULL DEFAULT 100,
    introduction text NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;
 
// AUTO_INCREMENT 代表自动增加, 每个表只能有一个字段可以设置 AUTO_INCREMENT
// DEFAULT 设置默认值
// PRIMARY KEY 定义主键, 主键不能为 NULL 其是唯一值。主键可以定义多个
// ENGINE 设置引擎类型

// 添加 age 列
ALTER TABLE users
    ADD age int;
 
// 删除 age 列
ALTER TABLE users
    DROP COLUMN age;
 
// 定义外键, 给 orders 表的 user_id 字段定义一个名为 fk_orders_users 的外键,
// 其引用 users 表的 id 字段
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY(user_id) REFERENCES users(id);

DROP TABLE test;

// 将 test 重命名为 test2;
RENAME TABLE test TO test2;

// 视图是虚拟的表, 不存储数据
 
// 一个联结查询
SELECT order_number,username,book_name
	FROM orders,users,books
	WHERE orders.user_id=users.id AND orders.book_id=books.id;
 
// 如果将以上查询包装成虚拟的表 ordersdetail, 则可以简化查询为
SELECT * FROM ordersdetail;
 
// 视图的一大作用就是简化复杂的 sql,
// 视图名称不能与其他视图和表重名

// 使用 CREATE VIEW 创建视图;
// 使用 SHOW CREATE VIEW viewname 查看创建视图的语句;
// 使用 DROP VIEW viewname 删除视图;
// 使用 CREATE OR REPLACE VIEW 更新视图。
 
// 创建视图
CREATE VIEW ordersdetail AS
SELECT order_number,username,book_name
	FROM orders,users,books
	WHERE orders.user_id=users.id AND orders.book_id=books.id;
 
// 使用视图
SELECT * FROM ordersdetail;

// 存储过程为一条或多条语句的集合
 
// 创建存储过程
// 创建一个名为 booksPriceAvg 的存储过程, BEGIN END 之间为存储过程体
CREATE PROCEDURE booksPriceAvg()
BEGIN
    SELECT AVG(price) AS price_avg FROM books;
END;
 
// 使用存储过程
CALL booksPriceAvg();
 
// 删除存储过程, 存储过程不存在将报错
DROP PROCEDURE booksPriceAvg;
 
// 删除存储过程且不报错
DROP PROCEDURE IF EXISTS booksPriceAvg;

CREATE PROCEDURE booksPrice(
	OUT pMin DECIMAL(10,2),
	OUT pMax DECIMAL(10,2),
	OUT pAvg DECIMAL(10,2)
)
BEGIN
	SELECT MIN(price) INTO pMin FROM books;
	SELECT MAX(price) INTO pMax FROM books;
	SELECT AVG(price) INTO pAvg FROM books;
END;
 
// 这里 OUT 关键字指出相应的参数从存储过程中返回的值,
// OUT（从存储过程中传出）, IN（传递给存储过程）,INOUT（对存储过程传入传出）
// DECIMAL(10,2) 代表数值长度10位, 2位小数。这里根据 price 字段来定义的
// INTO 指定保存到相应变量
 
 
// 使用3个变量调用存储过程
CALL booksPrice(@priceMin,@priceMax,@priceAvg);
 
// 变量用@定义. 变量为内存中的特定位置, 用来存储临时数据
 
// 查询变量值
SELECT @priceMin,@priceMax,@priceAvg;
 
 
// ----- 使用 IN 参数 -----
CREATE PROCEDURE booksSum(
	IN bookId INT,
	OUT bookSumPrice DECIMAL(10,2)
)
BEGIN
    --注释
	SELECT SUM(price*quantity) FROM books
		WHERE id=bookId INTO bookSumPrice;
END;
 
CALL booksSum(1,@bookSumPrice);
 
SELECT @bookSumPrice;

// 游标只能用于存储过程

// 以下语句支持触发器
DELETE;
INSERT;
UPDATE;

创建触发器

// 问: 创建一个触发器, 添加一行书籍信息时返回添加行的id
 
// 创建一个名为 addbook 的触发器, 在 books 表插入数据之后执行,
// FOR EACH ROW 对每个插入行都执行之后的语句
// NEW 为虚拟表, 可以访问被插入的行（对应的 OLD 虚拟表, 可以访问被删除的行）
// 将 id 保存到变量 lastInsertId 中
CREATE TRIGGER addbook AFTER INSERT ON books
FOR EACH ROW SELECT NEW.id INTO @lastInsertId;
 
// 插入数据
INSERT INTO books(book_name,`year`,price,quantity)
VALUES('哈哈cccdd',2020,12,100);
 
// 拿取新增行的id
SELECT @lastInsertId;

DROP TRIGGER addbook;

// 事务处理是一种机制，用来管理必须成批执行的MySQL操作，
// 以保证数据库不包含不完整的操作结果。

使用回退

SELECT * FROM test;
START TRANSACTION;
DELETE FROM test;
SELECT * FROM test;
ROLLBACK;
SELECT * FROM test;
 
// ROLLBACK 可以回退 START TRANSACTION 之后的语句
 
// 可以回退的语句包括 INSERT、UPDATE、DELETE

START TRANSACTION;
DELETE FROM users WHERE id=10;
DELETE FROM books WHERE id=9;
COMMIT;
// 只有两条删除语句都成功执行时, 才能提交
// 如果第一条执行成功, 第二条执行失败, 则都不会删除

// 在复杂的事务中, 可能需要部分回退, 这时需要用到保留点
 
START TRANSACTION;
DELETE FROM users WHERE id=9;
SAVEPOINT delete1;
DELETE FROM books WHERE id=8;
ROLLBACK TO SAVEPOINT delete1;
COMMIT;
 
// 第二条删除语句将会回退