本文使用的 MySQL 版本：8.2.0
1.使用mysql
管理员权限下开启和关闭mysql服务
net stop mysql
net start mysqlcmd命令行进入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 子句操作符
| 操作符 | 描述 |
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
|
BETWEEN
| 在指定的两个值之间 |
范围值查询
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 // 排序
LIMIT11.子查询
子查询，即嵌套在其他查询中的查询。
子查询过滤
// 问题: 已知书籍名称, 查询购买此书籍的用户信息
// 注: 书籍表(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;14.全文本搜索
启用全文搜索
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()指定要使用的搜索表达式
// 返回结果不区分大小写
// 注: 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果15.插入数据
插入单行
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;
// 这里的列名不一定要相同, 只要值是对应的就行16.更新和删除数据
更新数据
UPDATE users SET username='Dell',age=18 WHERE id=8;
// 若不加 where 子句将修改所有行删除数据
DELETE FROM users WHERE id=8;
// 若不加 where 子句将删除所有行17.创建和操作表
创建表
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;18.视图
视图概念
// 视图是虚拟的表, 不存储数据
// 一个联结查询
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;19.存储过程
使用存储过程
// 存储过程为一条或多条语句的集合
// 创建存储过程
// 创建一个名为 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;20.游标
使用游标
// 游标只能用于存储过程21.触发器
基础概念
// 以下语句支持触发器
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;22.事务处理
基础概念
// 事务处理是一种机制，用来管理必须成批执行的MySQL操作，
// 以保证数据库不包含不完整的操作结果。相关术语：
- 事务（transaction），指一组SQL语句；
- 回退（rollback），指撤销指定SQL语句；
- 提交（commit），指将未存储的SQL语句结果写入数据库表；
- 保留点（savepoint），指事务处理中设置的临时占位符，可以对它回退。
使用回退
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;
// 第二条删除语句将会回退
扫一扫
791
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
