0%

《Mysql》Mysql手册

distinct

如果要从 “Company” 列中选取所有的值,我们需要使用 SELECT 语句:
SELECT Company FROM Orders
“Orders”表:
Company OrderNumber
IBM 3532
W3School 2356
Apple 4698
W3School 6953
结果:
Company
IBM
W3School
Apple
W3School
请注意,在结果集中,W3School 被列出了两次。
如需从 Company” 列中仅选取唯一不同的值,我们需要使用 SELECT DISTINCT 语句:
SELECT DISTINCT Company FROM Orders
结果:
Company
IBM
W3School
Apple

LIKE ‘A%’

“%” 可用于定义通配符(模式中缺少的字母)

例如:从 “Persons” 表中选取居住在以 “g” 结尾的城市里的人
SELECT * FROM Persons
WHERE City LIKE ‘%g’

通配符:

  • %:替代一个或多个字符
  • _:仅替代一个字符
  • [charlist]:字符列中的任何单一字符
  • [^charlist]或者[!charlist]:不在字符列中的任何单一字符

(not) between ‘..’ and ‘..’

例如:以字母顺序显示介于 “Adams”(包括)和 “Carter”(不包括)之间的人
SELECT * FROM Persons
WHERE LastName
BETWEEN ‘Adams’ AND ‘Carter’

inner join (交集)

例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

left join (会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行)

例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

right join (会从右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行)

例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

full join (只要其中某个表存在匹配,FULL JOIN 关键字就会返回)

例如:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

union

用于合并两个或多个 SELECT 语句的结果集
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同

UNION 操作符选取不同的值, 不允许重复

例如:
SELECT E_Name, a FROM Employees_China
UNION
SELECT E_Name, a FROM Employees_USA

union all

允许重复

例如:
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

CREATE INDEX / CREATE UNIQUE INDEX

例如:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

DROP INDEX/TABLE/DATABASE/VIEW

TRUNCATE TABLE

ALTER TABLE/COLUMN

例如:添加字段
ALTER TABLE Persons
ADD Birthday date

例如:改变 “Persons” 表中 “Birthday” 列的数据类型
ALTER TABLE Persons
ALTER COLUMN Birthday year

例如:删除 “Person” 表中的 “Birthday” 列
ALTER TABLE Person
DROP COLUMN Birthday

日期函数

now() 返回当前的日期和时间

curdate() 返回当前的日期

curtime() 返回当前的时间

date(date) 返回日期或日期/时间表达式的日期部分

extract(year from {..}) 返回日期/时间的单独部分

例如:
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1

date_add({..}, interval 2 year) 向日期添加指定的时间间隔

例如:
SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

date_sub({..}, interval 2 year) 向日期减指定的时间间隔

datediff(date1, date2) 返回两个日期之间的天数

例如:
SELECT DATEDIFF(‘2008-12-30’,’2008-12-29’) AS DiffDate

date_format(date, format) 以不同的格式显示日期/时间数据

%a    缩写星期名
%b    缩写月名
%c    月,数值
%D    带有英文前缀的月中的天
%d    月的天,数值(00-31)
%e    月的天,数值(0-31)
%f    微秒
%H    小时 (00-23)
%h    小时 (01-12)
%I    小时 (01-12)
%i    分钟,数值(00-59)
%j    年的天 (001-366)
%k    小时 (0-23)
%l    小时 (1-12)
%M    月名
%m    月,数值(00-12)
%p    AM 或 PM
%r    时间,12-小时(hh:mm:ss AM 或 PM)
%S    秒(00-59)
%s    秒(00-59)
%T    时间, 24-小时 (hh:mm:ss)
%U    周 (00-53) 星期日是一周的第一天
%u    周 (00-53) 星期一是一周的第一天
%V    周 (01-53) 星期日是一周的第一天,与 %X 使用
%v    周 (01-53) 星期一是一周的第一天,与 %x 使用
%W    星期名
%w    周的天 (0=星期日, 6=星期六)
%X    年,其中的星期日是周的第一天,4 位,与 %V 使用
%x    年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y    年,4 位
%y    年,2 位

例如:
DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)

mysql时间比较

and created_at < NOW() - INTERVAL 30 MINUTE

两个datetime类型时间相减得到s

UNIX_TIMESTAMP(endDateTime) - UNIX_TIMESTAMP(beginDateTime)

IS (NOT) NULL (判断是否是null)

计算函数

avg (返回数值列的平均值)

first (返回指定的字段中第一个记录的值)

last

ucase() 把字段的值转换为大写

ucase() 把字段的值转换为大写

lcase() 把字段的值转换为小写

mid({..}, start[, length]) 从文本字段中提取字符

例如:
SELECT MID(City,1,3) as SmallCity FROM Persons

len({..}) 返回文本字段中值的长度

round(column_name,decimals) 把数值字段舍入为指定的小数位数

format(column_name,format) 用于对字段的显示进行格式化

字符串分割

substring_index ( substring_index ( address,’:’,1 ), ‘:’, -1) 第一个
substring_index ( substring_index ( address,’:’,2 ), ‘:’, -1) 第二个
从字符串的第 4 个字符位置开始取,直到结束。

mysql> select substring(‘example.com’, 4);
+——————————+
| substring(‘example.com’, 4) |
+——————————+
| mple.com |
+——————————+

having (与where类似, 增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用)

例如:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

IF表达式

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。

CASE表达式

CASE
WHEN MIN(value) <= 0 THEN 0
WHEN MAX(1/value) >= 100 THEN 1
ELSE -1
END

insert into (table) [fields] (select …) 将查询结果插入新表

insert into wallet.deposit (user_id,created_at,updated_at,tx_id,currency,confirmations,amount,status,address) (
select
a.user_id,
a.created_at,
a.updated_at,
tx_hash as tx_id,
a.asset as currency,
confirmation as confirmations,
amount,
case when status=1 then 3 when status=2 then 1 when status=3 then 4 end as status,
if(b.id is null, “”, b.address) as address
from old_system_wallet.user_recharge_logs a
left join old_system_wallet.user_recharge_addresses b on b.user_id = a.user_id and b.asset = a.asset
);

insert ignore 存在则忽略,不存在就插入

insert ignore into balance (currency,address,chain) values (‘GHP’,’0xac411e38a6a777e0b54c75e06fb1195b513189d8’,’Eth’);
如果表中已经存在触发唯一索引的数据,就不会插入,如果没有就会插入一条新数据

REPLACE INTO

如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复,则先删除旧数据再插入最新的数据
REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山’, 50);
REPLACE INTO users SET id = 123, name = ‘赵本山’, age = 50;
REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录

limit

limit x,y [x+1, x+1+y)
limit y offset x [x+1, x+1+y)

联表更新字段

update user.user a, wallet.withdraw b set b.unique_uid = a.uuid where b.user_id = a.id;

修改auto_increment

alter table deposit auto_increment=1001;

查看配置

最大连接数

show variables like ‘%max_connections%’

慢查询日志功能开启状态

show variables like ‘%slow_query_log%’;

慢查询阈值

show variables like ‘%long_query_time%’;

sql日志功能开启状态

show variables like “general_log%”;

查看Mysql设置的时区

show variables like “%time_zone%”;

time_zone: SYSTEM 说明mysql使用system的时区
system_time_zone: CST 说明system使用CST时区
system_time_zone: UTC 说明system使用UTC时区

on duplicate key update

如果插入的数据会导致UNIQUE索引发生冲突/重复,则执行UPDATE语句
INSERT INTO student(name, age) VALUES(‘Jack’, 19)
ON DUPLICATE KEY
UPDATE age=19;

in和exists

select * from A where deptId in (select deptId from B);

先查询部门表B select deptId from B 再由部门deptId,查询A的员工 select * from A where A.deptId = B.deptId

select * from A where exists (select 1 from B where A.deptId = B.deptId);

select * from A,先从A表做循环 select * from B where A.deptId = B.deptId,再从B表做循环.

我们要选择最外层循环小的,也就是,如果B的数据量小于A,适合使用in,如果B的数据量大于A,即适合选择exists

用户管理

删除账户

DROP USER IF EXISTS bridge;

创建账户

create user bridge IDENTIFIED by ‘bridge’; 创建账户. 默认host是%

修改账户密码

alter user ‘root’@’%’ identified by ‘123’;FLUSH PRIVILEGES; 修改账户密码

给账户授权

grant select,update,insert on bridge.* to bridge; 给账户授权

查看所有账户

select * from mysql.user;

查看赋予的权限

show grants for third_party_rest; 查看权限

撤销权限

revoke update,insert on engine.* from lcq; 撤销权限

count(*)、count(1)、count(列名)

count(1)和count(*)基本没有差别,统计出总行数

count(列名) 会忽略指定列名是NULL的行,返回总行数




微信关注我,及时接收最新技术文章