MySql-菜鸟学习笔记

最新推荐文章于 2021-01-27 09:00:00 发布

傅小大人

最新推荐文章于 2021-01-27 09:00:00 发布

阅读量226

点赞数

分类专栏：工具

本文链接：https://blog.csdn.net/qq_37746623/article/details/106884935

版权

工具专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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 之后）对一个具体的数字取模，得到的余数就是需要存放到的新表的位置。（时间/地域进行分表）

傅小大人

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySql-菜鸟学习笔记

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_i
复制链接

扫一扫