MySQL数据库概述
1 SQL
SQL语句大小写不敏感。
SQL语句末尾应该使用分号结束。
1.1 SQL语句及相关操作示例
- DDL:数据定义语言,负责数据库定义、数据库对象定义,由CREATE、ALTER与DROP三个语法所组成
- DML:数据操作语言,负责对数据库对象的操作,CRUD:create、read、update、delete增查、改、删
- DCL:数据控制语言,负责数据库权限访问控制,由GRANT和REVOKE两个指令组成
- TCL:事务控制语言,负责处理ACID事务,支持commit、rollback指令
# GRANT授权、REVOKE撤销:
grant all on crashsource.* to 'test_user'@'%' identified by 'cli*963.'; -- 在所有主机上,对test_user用户,授权操作数据库crashsource中的所有表。 .*表示所有表, '%'通配符
revoke all on *.* from test_use; -- 撤销test_user用户对所有表的配置权限。
# 删除用户
drop user test_user; -- 删除用户test_user,慎用。
# 创建数据库
create database if not exists gogs character set utf8mb4 collate utf8mb4_general_ci; -- 创建数据库
# 删除数据库
drop database if exists gogs; -- 如果数据库gogs存在,则删除。
# 创建表
CREATE TABLE `reg` (
`loginname` varchar(48) NOT NULL,
`name` varchar(64) NOT NULL,
`password` varchar(255) NOT NULL,
KEY `ln` (`loginname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 创建表,反引号标注的名称,被认为是非关键字。
# DESC查看列信息
desc products; -- 查看列信息
desc products '%name'; -- 查看以name结尾的列信息
1.2 主键PRIMARY KEY *
主键PRIMARY KEY,表中一列或多列(一个字段或多个字段)组成组成唯一的key,通过这个key能唯一的标识一条记录。主键的列不能使用空值NULL。主键往往设置为整型、长整型,(字符串、日期型都可以,但是开发中一般不这么做,因为存在性能上的问题);通过自增AUTO_INCREMENT来保证主键的唯一性。通常实践结果表明,也不怎么使用多列组合来组成主键。
自增AUTO_INCREMENT:自己管理一个数字,每增加一行,数字+1,删除一行这个数字不变。数字绝不回头。
表中也可以没有主键,但不符合惯例。
1.3 索引INDEX *
索引可以看做是一本大字典的目录,为了快速检索用的。空间换时间,显著提高查询效率(同时会带来插入、删除、修改的效率问题,比如要修改某一个字段,可能需要同时去更新索引)。
可以对一列或多列设定索引。
**主键索引:**主键会自动建立主键索引,主键本身就是为了快速定位唯一记录的。
**唯一索引:**表中的索引列组成的索引必须唯一,但可以为空,非空值必须唯一。 主键索引和唯一索引的显著差异,就是主键不可以为NULL,唯一键可以为NULL。
**普通索引:**没有唯一性的要求,就是建立了一个字典的目录而已。
1.4 约束Constraint *
unique约束(唯一键约束):定义了唯一键索引,就定义了唯一键约束。唯一约束就是不允许重复,但是可以为NULL,为NULL只允许某一行的一个字段为NULL。
primary key约束:定义了主键,就定义了主键约束。主键约束就是不允许为NULL,而且不允许重复。
外键约束Foreign Key
**外键:**在表B中的列,关联表A中的主键,表B中的列就是外键。
1、如果在表B插入一条数据,B的外键列插入了一个值,这个值必须是表A中存在的主键值。
2、修改表B的外键值,同样要在表A中存在。
3、如果表A要删除一条记录,那么就等于删除了一个主键,那么如果表B中引用了这个主键,就必须先删除表B中引用这个主键的记录,然后才能删除表A的记录,否则删除失败。
4、修改表A的主键,由于主键的唯一性,修改的主键相当于插入新的主键,那么表B引用过这个主键,将组织表A的主键修改,必须删除表B的相关记录后,才能修改表A的主键。
外键约束,为了保证数据完整性、一致性,杜绝数据冗余、数据讹误。
1.5 视图
视图,也称续表,看起来像表。它是由查询语句生成的。可以通过视图进行CRUD增删改查操作。
视图的作用:
- 简化操作,将复杂的SQL查询语句定义为视图,可以简化查询。
- 数据安全,视图可以只显示真实表的部分列,或计算后的结果,隐藏真实表的数据。
# 创建视图示例
create view ProductCustomers AS
select cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num;
2 MySQL数据类型 *
下列常用的数据类型必须牢记:
类型 | 含义 |
---|---|
tinyint | 1字节,带符号的范围是128到127。无符号的范围是0到255。bool或boolean,就是tinyint,0表示假,非0表示真 |
smallint | 2字节,带符号的范围是-32768到32767。无符号的范围是0到65535 |
int | 整型,4字节,同Integer,带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295 |
bigint | 长整型,8字节,带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615 |
float | 单精度浮点数精确到大约7位小数位 |
double | 双精度浮点数精确到大约15位小数位 |
DATE | 日期。支持的范围为1000-01-01 到9999-12-31 |
DATETIME | 支持的范围是1000-01-0100:00:00 到9999-12-3123:59:59 |
TIMESTAMP | 时间戳。范围是1970-01-0100:00:00 到2037年 |
char(M) | 固定长度,右边填充空格以达到长度要求。M为长度,范围为0~255。M指的是字符个数 |
varchar(M) | 变长字符串。M表示最大列长度。M的范围是0到65535。但不能突破行最大字节数65535 |
text | 大文本。最大长度为65535(2^16-1)个字符 |
BLOB | 大字节。最大长度为65535(2^16-1)字节的BLOB列 |
LENGTH函数:返回字节数。而char和varchar定义的M是字符数限制。
char可以将字符串变成等长的,空间换时间,效率略高(查询、存储效率)
varchar变长,省了空间。但是会带来效率问题。因为存的数据参差不齐,带来读取的问题,比如存了一百万条记录,单要对前面的数据进行修改,而这个数据恰恰引起了长度的变化,导致整个IO开始变动,上百万条整体挪动。。。
所以到底用什么,要根据实际项目评估。比如数据库经常修改,还不如用char呢。但是用char,一个主从、多从的数据库架构,那要浪费多少磁盘空间啊。。。
2.1 使用Navicat设计表示例
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uAF2gcKc-1692374636464)(设计表示例.jpg)]
保存后生成的SQL语句:
CREATE TABLE `reg` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`loginname` varchar(48) NOT NULL,
`name` varchar(64) NOT NULL,
`password` varchar(255) NOT NULL,
`reserved1` varchar(255) DEFAULT NULL,
`reserved2` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ln` (`loginname`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2.2 使用Navicat创建视图示例
-
点击视图-> 新建视图
-
点击视图创建工具 -> 拖拽需要创建视图的表
salaries
、employees
到窗口,勾选需要查看的字段first_name、last_name、salary、emp_no -> 构建:
-
预览:
-
点击保存,然后双击打开保存的视图,新建视图查询语句-> 点击运行:
3 关系操作
关系:在关系型数据库中,关系就是二维表。关系操作就是对表的操作。
- 选择:selection,又称为限制,是从关系中选择出满足给定条件的元组。
- 投影:projection,在关系上投影就是从选择出若干属性列组成新的关系。
- 连接,join,将不同的两个关系连接成一个关系。
3.1 DML
CRUD:增create、查read、改update、删delete。
3.1.1 Insert语句
INSERT INTO
customers (cust_name, cust_address, cust_city, cust_country, cust_contact, cust_email)
VALUES ('Li Ming', 'JIN NIU', 'ChengDU', 'China', '02811111', 'test@email.com'); -- 基本的insert语句。主键自动递增可以不指定。
INSERT INTO
orders (order_date, cust_id)
VALUES (database(), 123)
ON DUPLICATE KEY
UPDATE order_date=database(); -- 如果主键冲突、唯一键冲突就执行Upgdate后的设置。主键不在新增记录,主键存在更新部分字段。
INSERT IGNORE INTO
vendors (vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES ('Huawei', 'PIDU', 'CHENGDU', 'SICHUAN', 123454, 'CHINA'); -- 如果主键冲突、唯一建冲突就忽略错误,返回一个告警。
3.1.2 Update语句
UPDATE IGNORE vendors SET vend_address='Pi DU', vend_city='CHENG DU' where vend_name='Huawei'; -- 要有where语句,否则就会更新所有行。ignore同上
3.1.3 DELETE语句
DELETE IGNORE FROM vendors WHERE vend_name='HUAWEI'; -- 删除符合条件的记录; 没有where子句将删除所有行,可以通过日志恢复。delete不会删除表本身。
truncate语句:
作用是清空表或者说是截断表,只能作用于表。truncate的语法很简单,后面直接跟表名即可 truncate是DDL语句。
TRUNCATE vendors -- 不记录数据的变动,可以通过日志恢复
truncate与drop,delete的对比
truncate与delete,drop很相似,其实这三者还是与很大的不同的,下面简单对比下三者的异同。
- truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,可回滚。
- truncate只能作用于表;delete,drop可作用于表、视图等。
- truncate会清空表中的所有行,但表结构及其约束、索引等保持不变;drop会删除表的结构及其所依赖的约束、索引等。
- truncate会重置表的自增值;delete不会。
- truncate不会激活与表有关的删除触发器;delete可以。
- truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减少表或索引所占用的空间,drop语句将表所占用的空间全释放掉。
3.1.4 select语句
查询的结果称为结果集record set。
/*SELECT
[DISTINCT]
select_expr,...
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC],... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name I expr I position]
[ASC I DESC],..]
[LIMIT ([offset,] row count I row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]]
*/
for update 会把进行写锁定,这是排他锁。
SELECT
DISTINCT
vend_name, vend_address
FROM vendors
WHERE vend_country IN
('USA', 'ENGLISH', 'CHINA')
GROUP BY vend_country DESC
HAVING vend_country IN
('USA', 'ENGLISH', 'CHINA')
ORDER BY vend_country
LIMIT 5 OFFSET 2
FOR UPDATE;
SELECT 1;
-- 最简单的查询
SELECT * FROM vendors;
-- 字符串合并
SELECT vend_id, vend_name + vend_address FROM vendors;
SELECT vend_id, COUNT(vend_name, ' ', vend_address, ' ', vend_city) FROM vendors;
-- as 定义别名
SELECT vend_id, COUNT(vend_name, ' ', vend_address, ' ', vend_city) as vend_info FROM vendors;
-- limit子句
SELECT * FROM orders LIMIT 5; -- 提取前5行
SELECT * FROM orders LIMIT 5 OFFSET 4; -- 提取5条记录,偏移4条。偏移可以用作分页读取;比如每次偏移20,读取20条记录,每读取一次偏移增加20.
SELECT * FROM orders LIMIT 4, 5; -- 上条语句的另外一种写法。
– where子句
where子句涉及的运算符:
运算符 | 描述 |
---|---|
= | 等于 |
<> | 不等于 |
>,<,>=,<= | 大于、小于、大于等于、小于等于 |
BETWEEN | 在某个范围之内,between a andb等价于[a,b] |
LIKE | 字符串模式匹配,%表示任意多个字符,表示一个字符 |
IN | 指定针对某个列的多个可能值 |
AND | 与 |
OR | 或 |
**注意:**如果很多表达式需要使用AND、OR计算逻辑表示式的值的时候,由于结合律问题,可以使用小括号来避免错误。
-- 条件查询
SELECT * FROM vendors WHERE vend_id<1006 and vend_city in ('New York', 'Pairs', 'London') and vend_name like 'J%';
SELECT * FROM vendors WHERE vend_id BETWEEN 1000 AND 1005 AND vend_name LIKE 'J%';
SELECT * FROM vendors WHERE vend_id IN (1003, 1004, 1005);
-- order by子句
# 对查询结果进行排序,可以升序ASC、降序DESC
-- 降序
SELECT * FROM vendors WHERE vend_id IN (1003, 1004, 1005) ORDER BY vend_id DESC;
-- DISTINCT
# 不返回重复记录
SELECT DISTINCT vend_id FROM vendors;
SELECT DISTINCT vend_id, vend_city FROM vendors;
聚合函数
函数 | 描述 |
---|---|
COUNT(expr) | 返回记录中记录的数目,如果指定列,则返回非NULL值的行数 |
COUNT(DISTINCT expr,[expr..) | 返回不重复的非NULL值的行数 |
AVG(IDISTINCT]expr) | 返回平均值,返回不同值的平均值 |
MIN(expr), MAX(expr) | 最小值,最大值 |
SUM(IDISTINCT]expr) | 求和,Distinct返回不同值求和 |
-- 聚合函数
SELECT COUNT(*), AVG(quantity), SUM(quantity), MIN(quantity), MAX(quantity) FROM orderitems;
分组查询
使用group by
子句,如果有条件,使用HAVING子句过滤分组、聚合过滤后的结果。
-- 聚合所有
SELECT SUM(item_price), AVG(item_price), COUNT(order_item) FROM orderitems;
-- 聚合被选择的记录
SELECT order_item, SUM(quantity), AVG(item_price), COUNT(order_item) FROM orderitems WHERE order_item=1;
-- 按照不同的order_item分组,分组分别聚合
SELECT order_item, SUM(quantity), AVG(item_price), COUNT(order_item) FROM orderitems WHERE item_price<10 GROUP BY order_item;
-- HAVING子句对分组结果过滤
SELECT order_item, SUM(quantity), AVG(item_price), COUNT(order_item) FROM orderitems GROUP BY order_item HAVING AVG(quantity)>3;
-- 使用别名
SELECT order_item, SUM(quantity), AVG(item_price) as avg_q FROM orderitems GROUP BY order_item HAVING AVG(quantity)>3;
-- 最后对分组过滤后的结果进行排序
SELECT order_item, SUM(quantity), AVG(item_price) as avg_q FROM orderitems GROUP BY order_item HAVING AVG(quantity)>3 ORDER BY avg_q;
子查询
查询语句可以嵌套,内部查询就是子查询。子查询必须在一组小括号中。子查询不能使用order by
。
SELECT *
FROM orderitems
where item_price IN (SELECT item_price FROM orderitems WHERE item_price<10)
ORDER BY item_price
DESC;
SELECT order_item
FROM (SELECT * FROM orderitems WHERE item_price<10) as ord_items
where item_price<9
ORDER BY order_item
DESC;
子查询存在性能问题,建议少用,改用join
。
连接join
在联结两个表时,实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件,只包含那些匹配给定条件(这里是联结条件)的行。
没有WHERE子句,第一个表中的每一行将与第二个表中的每一行配对,而不管它们逻辑上是否能配在一起,这种联结就是笛卡尔联结。
内联结:
- 没有where限定的内联结,就是笛卡尔联结,又称叉联结。有的说法,叉联结是叉联结,使用CROSS JOIN;内联结是内联结,使用INNER JOIN;只是两种联结的语法相同。
SELECT vend_name, prod_name, prod_price FROM vendors, products;
SELECT vend_name, prod_name, prod_price FROM vendors CROSS JOIN products; -- 叉联结,使用CROSS JOIN
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products; -- 内联结,使用INNER JOIN,因为没有where限定,得出的结果也是叉联结的结果。
- 等值联结,有where限定的内联结
在联结两个表时,实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件,只包含那些匹配给定条件(这里是联结条件)的行。
没有 WHERE子句,第一个表中的每一行将与第二个表中的每一行配对,而不管它们逻辑上是否能配在一起。
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id;
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id; -- 更推荐使用inner join... on... 语法
-- 自联结,特殊的等值联结
-- 找出与Mr Wang在同一个国家的所有顾客,然后给他们发邮件。先使用子查询
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_country = (SELECT cust_country
FROM Customers
WHERE cust_contact = 'Mr Wang');
-- 使用自联结
SELECT a.cust_id, a.cust_name, a.cust_email
FROM customers AS a INNER JOIN customers AS b ON a.cust_country = b.cust_country AND b.cust_name='Mr Wang';
-- 自然联结:等值连接中,若干个联结的表,会产生重复的列。自然联结就是没有重复的列,是一种特殊的等值连接。
-- 没有内置的DBMS语法支持自然联结,需要用户自己实现。一般通过对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成
SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O,
OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
- 外联结:分为左外联结,右外联结
许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行。
例如,对每个顾客下的订单进行计数,包括那些至今尚未下订单的顾客;列出所有产品以及订购数量,包括没有人订购的产品。
-- 使用 LEFT OUTER JOIN 从 FROM 子句左边的表(Customers 表)中选择所有行.检索包括没有订单顾客在内的所有顾客
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
-- 使用 RIGHT OUTER JOIN 从 FROM 子句右边的表(Customers 表)中选择所有行.检索包括没有订单顾客在内的所有顾客
SELECT customers.cust_id, orders.order_num FROM orders RIGHT OUTER JOIN customers ON customers.cust_id = orders.cust_id;
-- 带聚集函数的联结
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id; -- 先执行INNER JOIN... ON...,将两张表关联;然后按顾客ID分组;最后通过聚集函数,统计出每个顾客的订单数。
-- 联结多个表
-- 使用子查询
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (
SELECT cust_id
FROM Orders WHERE order_num IN (
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01')
);
-- 使用联结
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';