MySql-菜鸟学习
文章目录
支持数值类型
基本语法
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 datatype
ALTER 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 condition
13.分库分表
DRDS 在后端将数据量较大的数据表水平拆分到后端的每个 RDS 数据库中,这些拆分到RDS中的数据库被称为分库,分库中的表称为分表。DRDS 由每个分库负责每一份数据的读写操作,从而有效的分散了整体访问压力。
13.1 分库
通常数据分库之后,每一个数据库包含多个数据表,多个数据库会组成一个 Cluster/Group,提高了数据库的可用性,并且可以把读写做分离。
Master 库主要负责写操作,Slave 库主要负责读操作。在应用访问数据库的时候会通过一个负载均衡代理,通过判断读写操作把请求路由到对应的数据库。
13.2 分表
垂直分表
根据业务把一个表中的字段(Field)分到不同的表中。这些被分出去的数据通常根据业务需要,例如分出去一些不是经常使用的字段,一些长度较长的字段。(字段数较多的表可选择)
水平分表
将一个表中的数据,按照关键字(例如:ID)(或取 Hash 之后)对一个具体的数字取模,得到的余数就是需要存放到的新表的位置。(时间/地域进行分表)