一.基本操作语句
SQL语句优先级:
SELECT column_name
FROM table_name
WHERE where_condition
GROUP BY colunm_name
HAVING having_condition
ORDER BY column_name
LIMIT limit_condition
一些基本语句:
CREATE DATABASE database_name
DROP DATABASE database_name
CREATE TABLE table_name
(col1
type [NOT NULL] [PRIMARY KEY], col2
type[NOT NULL],...)
CREATE TABLE table_new
LIKE table_old
CREATE TABLE table_new
as select col1
,col2
,.. FROM table_old DEFINITION ONLY
DROP TABLE table_name
ALTER TABLE table_name
ADD COLUMN col
type
ALTER TABLE table_name
ADD PRIMARY KEY(col
)
ALTER TABLE table_name
DROP PRIMARY KEY(col
)
CREATE [UNIQUE] INDEX index_name
ON table_name
(col
)
DROP INDEX index_name
CREATE VIEW view_name
AS SELECT statement
DROP VIEW view_name
SELECT * FROM table_name
WHERE where_condition
INSERT INTO table_name
(field1
,field2
) VALUES (value1
,value2
)
DELETE FROM table_name
WHERE where_condition
UPDATE table_name
SET field1
=value1
WHERE where_condition
SELECT * FROM table_name
WHERE field1
LIKE '%value1%'
SELECT * FROM table_name
ORDER BY field1
,field2
[DESC]
SELECT COUNT
AS TOTALCOUNT
FROM table_name
SELECT SUM(field
) AS SUMVALUE
FROM table_name
SP_RENAMEDB
'old_name','new_name'
SELECT A
.A
,A
.B
,B
.C
FROM
(SELECT A
,B
FROM A
)C
(SELECT A
,C
FROM B
)D
ON A
.A
=B
.A
SELECT * FROM
(SELECT column1
FROM A
UNION ALL
SELECT column1
FROM B
)T
SELECT DISTINCT column_name
FROM table_name
SELECT COUNT(DISTINCT COLUMN_name
) FROM table_name
二.文本和时间处理函数
LEFT()
LENGTH
()
LOCATE
()
LOWER
()
LTRIM
()
RIGHT()
RTRIM
()
SOUNDEX
()
SUBSTRING
()
UPPER
()
ADDDATE
()
ADDTIME
()
CURDATE
()
CURTIME
()
DATE()
DATE_ADD
()
DATE_FORMAT
()
DAY()
DAYOFWEEK
()
HOUR()
MINUTE()
MONTH()
NOW()
SECOND()
TIME()
YEAR()
ABS
()
COS
()
EXP
()
MOD()
PI
()
RAND
()
SIN
()
SQRT
()
TAN
()
三.进阶语句
SELECT * INTO B
FROM A
WHERE 1<>1
SELECT TP
0 * INTO
INSERT INTO B
(A
,B
,C
)SELECT D
,E
,F
FROM A
SELECT A
,B
,C
FROM A
WHERE A
IN(SELECT D
FROM B
)`
SELECT *,ROW_NUMBER
() OVER (condition
) AS ROW_num
FROM table_name
DATEDIFFER
(enddate
,startdate
)
DATE_SUB
(startdate
,days
)
DATE_ADD
(startdate
,days
)
常用函数暂时想到这么多,有用到的再随时补充。
转载请注明原文地址:https://tech.qufami.com/read-456.html