SQL快速参考

Structured Query Language，结构化查询语言

基础

SHOW DATABASES;
USE database_name;
SHOW COLUMNS FROM table_name; 

# 创建用户
CREATE USER 'chen'@'%' IDENTIFIED [WITH mysql_native_password] BY 'chen123';
# 检查用户
SELECT user, host, plugin, authentication_string FROM mysql.user\G;
# 授权
GRANT ALL PRIVILEGES ON *.* TO 'chen'@'%';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO 'chen'@'%';
# 查看权限
SHOW GRANTS for 'chen'@'%';

SELECT

SELECT column_name(s)
FROM table_name;

SELECT [DISTINCT] column_name(s)
FROM table_name
WHERE column_name operator value
[AND|OR column_name operator value]
ORDER BY column_name(s) ASC|DESC
[LIMIT number];

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN 'A' AND 'H';

LIKE

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
# pattern
'%a'  # 以a结尾的数据
'a%'  # 以a开头的数据
'%a%' # 含有a的数据
'_a_' # 三位且中间字母是a的
'_a'  # 两位且结尾字母是a的
'a_'  # 两位且开头字母是a的

# REGEXP
SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

INSERT

INSERT INTO table_name
VALUES (value1, value2, ...);

INSERT INTO table_name 
(column_name1, column_name2, ...)
VALUES
(value1, value2, ...);

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

UPDATE

UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE some_column=some_value;

DELECT

DELECT FROM table_name
WHERE some_column=some_value;

DELECT * FROM table_name;

TRUNCATE table_name;

DROP table_name;

JOIN

SELECT column_name(s)
FROM
table1 INNER|LEFT|RIGHT|FULL JOIN table2
ON table1.column_name=table2.column_name;\

UNION

SELECT column_name(s) FROM table1
UNION [ALL]
SELECT column)name(s) FROM table2;

CREATE TABLE

CREATE TABLE persons
(
    PersonID int NOT NULL AUTO_INCREMENT,
    StudentID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255) DEFAULT 'GuangZhou',
    CHECK (PersonID>0),
    UNIQUE (StudentID),
    PRIMARY KEY (PersonID),
    FOREIGN KEY (StudentID) REFERENCES student(StudentID)
    );

ALTER TABLE

ALTER TABLE persons
ADD|DROP UNIQUE (PersonID);

ALTER TABLE persons
DROP PRIMARY KEY;

ALTER TABLE persons
ADD CHECK (PersonID>0);

ALTER TABLE persons
ALTER Address SET DEFAULT 'GuangZhou';

VIEW

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

函数

SELECT AVG(column_name) FROM table_name;

SELECT COUNT(column_name) FROM table_name;

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

PyMySQL

• fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
• fetchall(): 接收全部的返回结果行.
• rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
• 重要函数
  • cursor(cursor = None): 创建一个游标
  • commit(): 事务提交，如果没有设为自动提交，则每次操作后必须提交事务，否则操作无效。
  • rollback(): 操作出错时，可以用这个函数回滚到执行事务之前
  • close(): 关闭连接

# pymysql不支持caching_sha2_password加密，需指定mysql_native_password
# create user 'wang'@'localhost' identified with mysql_native_password by 'wang123';
# user需要权限
# grant all privileges on *.* to 'wang'@'localhost';
import pymysql
db = pymysql.connect(host='localhost', user='wang', password='wang123', db='world')
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
# db.commit()  # 像数据库提交
data = cursor.fetchone()
print ('Database version : %s ' % data)
db.close()

备忘

where add_time >= UNIX_TIMESTAMP(to_date('20180101', 'yyyymmdd'))

更多函数

• 日期时间

  • to_date, 将字符类型按一定格式转化为日期类型, to_date(‘2004-11-27’, ‘yyyy-mm-dd’)

  • to_char, 将日期类型转化为字符类型, to_char(sysdate, ‘yyyymmdd’)

    # 星期几
    to_char(to_date('2002-08-26','yyyy-mm-dd'),'day')

  • date_format(date, format)

  • from_unixtime(inux_timestamp, format)
  • unix_timestamp(date)
• 数据类型转化，CAST (expression AS data_type)，SELECT CAST(‘12.5’ AS decimal(9,2)) # 精度9，小数点位数2
• TRIM()，移除头尾空白
• 正则，select regexp_substr(a, ‘[0-9]+’)