Mysql 基础知识 常用的SQL

DQL

连接和组合

连接

内连接（INNER JOIN）

内连接又称等值连接，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

自连接（=）

自连接可以看成内连接的一种，只是连接的表是自身而已。自然连接是把同名列通过 = 连接起来的，同名列可以有多个。

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

自然连接（NATURAL JOIN）

内连接提供连接的列，而自然连接自动连接所有同名列。自然连接使用 NATURAL JOIN 关键字。

SELECT *
FROM Products
NATURAL JOIN Customers;

外连接（OUTER JOIN）

外连接返回一个表中的所有行，并且仅返回来自此表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接（Mysql 不支持）。

左连接（LEFT JOIN）

左外连接就是保留左表没有关联的行。

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

右连接（RIGHT JOIN）

右外连接就是保留右表没有关联的行。

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

组合（UNION）

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：

• 所有查询的列数和列顺序必须相同。
• 每个查询中涉及表的列的数据类型必须相同或兼容。
• 通常返回的列名取自第一个查询。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

应用场景：

• 在一个查询中从不同的表返回结构数据。
• 对一个表执行多个查询，按一个查询返回数据。

组合查询示例：

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';

五、函数

文本处理

函数	说明
LEFT()、RIGHT()	左边或者右边的字符
LOWER()、UPPER()	转换为小写或者大写
LTRIM()、RTIM()	去除左边或者右边的空格
LENGTH()	长度
SUBSTRING_INDEX(str, split, index)	截取 第 index 个 split 之前、之后(index为负数)的所有字符，如果不存在，则返回整个字符串
SUBSTRING(str, index, len)	截取指定长度的字符串

日期和时间处理

• 日期格式：YYYY-MM-DD
• 时间格式：HH:MM:SS

函 数	说 明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

字符串格式化

• 格式化时间戳：select from_unixtime(1655012478, '%Y-%m-%d');
• 格式化时间格式：select date_format(now(), '%Y-%m-%d')

format	描述
%S,%s	两位数字形式的秒（ 00,01, …, 59）
%I,%i	两位数字形式的分（ 00,01, …, 59）
%H	两位数字形式的小时，24 小时（00,01, …, 23）
%h	两位数字形式的小时，12 小时（01,02, …, 12）
%k	数字形式的小时，24 小时（0,1, …, 23）
%l	数字形式的小时，12 小时（1, 2, …, 12）
%T	24 小时的时间形式（hh:mm:ss）
%r	12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
%p	AM或PM
%W	一周中每一天的名称（Sunday, Monday, …, Saturday）
%a	一周中每一天名称的缩写（Sun, Mon, …, Sat）
%d	两位数字表示月中的天数（00, 01,…, 31）
%e	数字形式表示月中的天数（1, 2， …, 31）
%D	英文后缀表示月中的天数（1st, 2nd, 3rd,…）
%w	以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, …, 6=Saturday）
%j	以三位数字表示年中的天数（ 001, 002, …, 366）
%U	周（0, 1, 52），其中Sunday 为周中的第一天
%u	周（0, 1, 52），其中Monday 为周中的第一天
%M	月名（January, February, …, December）
%b	缩写的月名（ January, February,…., December）
%m	两位数字表示的月份（01, 02, …, 12）
%c	数字表示的月份（1, 2, …., 12）
%Y	四位数字表示的年份
%y	两位数字表示的年份
%%	直接值%

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

汇总

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以让汇总函数值汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable

约束

SQL 约束用于规定表中的数据规则。

• 如果存在违反约束的数据行为，行为会被约束终止。
• 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
• 约束类型
  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。
  • DEFAULT - 规定没有给列赋值时的默认值。

创建表时使用约束条件：

CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用户名',
  Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '邮箱地址',
  Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',
  PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

DML

索引

添加索引

alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

添加唯一限制条件的索引

alter table 表名 add unique 索引名 (字段名);

删除某个索引

alter table 表名 drop index 索引名;

表

添加主键

alter table 表名 add primary key (字段名);

添加字段

ALTER TABLE table_name ADD field_name field_type;

修改字段名称和类型

ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

删除字段

ALTER TABLE table_name DROP field_name;

修改数据库的字符集

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

修改表默认字符集和所有字符列

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

修改表的默认字符集

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

DCL

用户

创建用户

CREATE USER 'ifan'@'%' IDENTIFIED BY '12345';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, REFERENCES, INDEX, DROP  ON database.table TO 'ifan'@'%';
FLUSH PRIVILEGES;

修改密码

set password for 'ifan'@'%' = password('123456'); 

删除用户

DROP USER 'ifan'@'%';

删除用户权限

REVOKE DELETE, DROP ON database.table FROM 'ifan'@'%'
FLUSH PRIVILEGES;