MySql-菜鸟学习
文章目录
- MySql-菜鸟学习
- 支持数值类型
- 基本语法
- 1.创建表
- 1.1 SQL约束
- 2.删除表/修改表
- 2.1删除：
- 2.2修改/删除表字段：
- 2.3 修改表名
- 3.表插入数据
- 4.查询语句
- 4.1 WHERE 子句
- 4.2 UNION操作符
- 4.3 排序
- 4.4 GROUP BY
- 4.5 HAVING 语法
- 4.6 NULL 值处理
- 4.7 LIKE 语法
- 4.8 操作符
- 4.9 子查询
- 5.更新表内数据
- 6.删除表内数据
- 7.MySQL 连接
- 8.事务
- 8.1事务的特性
- 8.2事务控制语句
- 8.3事务处理的方式
- 9 索引
- 9.1 普通索引
- **创建索引的几种方式**
- 9.2 唯一索引
- 10.临时表
- 10.1创建临时表
- 10.2 删除临时表
- 11.函数
- 12.视图
- 13.**分库分表**
- 13.1 分库
- 13.2 分表
支持数值类型
基本语法
1.创建表
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;注：
- AUTO_INCREMENT定义列为自增的属性
- PRIMARY KEY关键字用于定义列为主键
- ENGINE 设置存储引擎，CHARSET 设置编码。
1.1 SQL约束
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
CREATE TABLE Persons
(
P_Id int NOT NULL,
UNIQUE (P_Id)
)- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CREATE TABLE Orders
(
P_Id int,
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)- CHECK - 保证列中的值符合指定的条件。
- DEFAULT - 规定没有给列赋值时的默认值。
2.删除表/修改表
2.1删除：
DROP TABLE table_name ;2.2修改/删除表字段：
删除字段
ALTER TABLE testalter_tbl DROP i;增加字段
FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句（指定插入顺序）
ALTER TABLE testalter_tbl ADD i INT;修改字段类型/名称:
MODIFY 或 CHANGE (CHANGE需指定修改字段名)
ALTER TABLE table_name
MODIFY COLUMN column_name datatypeALTER TABLE testalter_tbl CHANGE i j BIGINT;2.3 修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;3.表插入数据
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );- 如果数据是字符型，必须使用单引号或者双引号，如：“value”。
4.查询语句
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]- LIMIT 限制返回数量
-
limit y分句表示: 读取 y 条数据 -
limit x, y分句表示: 跳过 x 条数据，读取 y 条数据 -
limit y offset x分句表示: 跳过 x 条数据，读取 y 条数据
- OFFSET 查询数据偏移量，默认为0
4.1 WHERE 子句
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....- 限制查询条件
- 使用 AND 或者 OR 指定一个或多个条件
- 适用于SELECT/DELETE/UPDATE
- WHERE子句可用操作符
操作符 | 描述 |
= | 是否相等 |
<>，！= | 是够不相等 |
>，>= | 是都大于 |
<,<= | 是否小于 |
4.2 UNION操作符
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];- 连接两个以上的 SELECT 语句的结果组合到一个结果集合中
- ALL: 可选，返回所有结果集，包含重复数据。
- DISTINCT: 可选，删除结果集中重复的数据。(默认情况UNION已去除重复数据)
4.3 排序
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]- 可以设置多个字段排序
- DESC 降序/ASC 升序（默认）
4.4 GROUP BY
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;- 根据一个/多个列对结果进行分组
- function(column_name)：COUNT, SUM, AVG
- SUM总数 COUNT计数 AVG 平均值
4.5 HAVING 语法
- 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
- HAVING 子句可以让我们筛选分组后的各组数据。
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;/*做聚合函数的筛选条件*/4.6 NULL 值处理
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
4.7 LIKE 语法
SELECT *column_name(s)*
FROM *table_name*
WHERE *column_name* LIKE 'G%';- "%" 符号:替代0个或多个字符 _：替代一个字符
- [charlist]:字符列中的任何单一字符 **[ ！charlist ]😗*不在字符列中的任意单一字符
- 通过使用 NOT 关键字,选取不匹配模式的记录
- RLIKE/REGEXP 操作正则表达式
4.8 操作符
**IN：**IN 操作符允许在 WHERE 子句中规定多个值。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);BETWEEN:选取介于两个值之间的数据范围内的值,这些值可以是数值、文本或者日期。(NOT BETWEEN)
**别名：**可以为表名称或列名称指定别名。
SELECT *column_name* AS *alias_name*
FROM *table_name;*SELECT *column_name(s)*
FROM *table_name* AS *alias_name;***UNION:**UNION 操作符用于合并两个或多个 SELECT 语句的结果集。（允许重复则使用UNION ALL）
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;4.9 子查询
- 独立子查询
- 条件使用子查询，利用子查询过滤
- 单行子查询/多行子查询（多行子比较符有 IN(等于列中任意一个)、ANY(和子查询返回的某个值比较)，ALL(和子查询返回的所有值比较)。）
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='RGAN01');SELECT cust_name, cust_state, ( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;5.更新表内数据
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]6.删除表内数据
DELETE FROM table_name [WHERE Clause]- 没有where将删除表内所有记录
7.MySQL 连接
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- **LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
- OUTER JOIN （外连接）：只要其中一个表中存在匹配，则返回行
1.INNER JOIN（join on 默认内连接）
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;- 在a表和b表的author字段一样时，才取出a/b表的这些字段
2.LEFT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;3.RIGHT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;4.Full outer join
Full outer join 产生A和B的并集,对于没有匹配的记录，则会以null做为值。
8.事务
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
8.1事务的特性
- **原子性：**不可分割，一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏
- **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力
- **持久性：**事务处理结束后，对数据的修改就是永久的
8.2事务控制语句
8.3事务处理的方式
- 用 BEGIN, ROLLBACK, COMMIT,SAVEPOINT,RELEASE SAVEPOINT,ROLLBACK TO,来实现
- 直接用 SET 来改变 MySQL 的自动提交模式
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
9 索引
- 索引分单列索引和组合索引
- 索引增加了查询的速度，但是会影响更新表的速度，更新表的时候不仅要保存数据，还要保存一下索引文件
9.1 普通索引
创建索引的几种方式
CREATE INDEX indexName ON mytable(username(length)); /*如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length*/ALTER table tableName ADD INDEX indexName(columnName)CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);删除索引
DROP INDEX [indexName] ON mytable;9.2 唯一索引
- 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引的几种方式（UNIQUE）
CREATE UNIQUE INDEX indexName ON mytable(username(length))ALTER table mytable ADD UNIQUE [indexName] (username(length))CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);ALTER命令添加的四种方法：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)/* 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。/增加唯一主键有唯一索引，索引名PRIMARY/*/ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)/*这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）*/ALTER TABLE tbl_name ADD INDEX index_name (column_list)/*添加普通索引，索引值可出现多次。*/ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)/*该语句指定了索引为 FULLTEXT ，用于全文索引*/显示索引信息
SHOW INDEX FROM table_name; \G /*\G格式化输出*/10.临时表
- 临时表只在当前连接可见
10.1创建临时表
CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);CREATE TEMPORARY TABLE 临时表名 AS
(
SELECT * FROM 旧的表名
LIMIT 0,10000
);/*用查询直接创建临时表*/10.2 删除临时表
DROP TABLE SalesSummary;11.函数
https://www.runoob.com/mysql/mysql-functions.html
函数 | 注释 | 使用 |
AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 | SELECT AVG(Price) AS AveragePrice(别名) FROM Products |
COUNT(expression) | 返回查询的记录总数（NULL值不算）COUNT（1）查满足条件的结果 | SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
BIN(x) | 返回 x 的二进制编码 | |
BINARY(s) | 将字符串 s 转换为二进制字符串 |
12.视图
创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition13.分库分表
DRDS 在后端将数据量较大的数据表水平拆分到后端的每个 RDS 数据库中，这些拆分到RDS中的数据库被称为分库，分库中的表称为分表。DRDS 由每个分库负责每一份数据的读写操作，从而有效的分散了整体访问压力。
13.1 分库
通常数据分库之后，每一个数据库包含多个数据表，多个数据库会组成一个 Cluster/Group，提高了数据库的可用性，并且可以把读写做分离。
Master 库主要负责写操作，Slave 库主要负责读操作。在应用访问数据库的时候会通过一个负载均衡代理，通过判断读写操作把请求路由到对应的数据库。
13.2 分表
垂直分表
根据业务把一个表中的字段（Field）分到不同的表中。这些被分出去的数据通常根据业务需要，例如分出去一些不是经常使用的字段，一些长度较长的字段。（字段数较多的表可选择）
水平分表
将一个表中的数据，按照关键字（例如：ID）（或取 Hash 之后）对一个具体的数字取模，得到的余数就是需要存放到的新表的位置。（时间/地域进行分表）
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
