SQL语句参考
大写
SQL语句 | 语法 |
---|---|
AND / OR | SELECT column_name(s) FROM table_name WHERE condition AND|OR condition |
ALTER TABLE | ALTER TABLE table_name ADD column_name datatype or ALTER TABLE table_name DROP COLUMN column_name |
AS (alias) | SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name AS table_alias |
BETWEEN | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 |
CREATE DATABASE | CREATE DATABASE database_name |
CREATE TABLE | CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, … ) |
CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name) |
CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
DELETE | DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name (Note: Deletes the entire table!!) DELETE * FROM table_name (Note: Deletes the entire table!!) |
DROP DATABASE | DROP DATABASE database_name |
DROP INDEX | DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL) |
DROP TABLE | DROP TABLE table_name |
GROUP BY | SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name |
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 |
IN | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,…) |
INSERT INTO | INSERT INTO table_name VALUES (value1, value2, value3,…) *or *INSERT INTO table_name (column1, column2, column3,…) VALUES (value1, value2, value3,…) |
INNER JOIN | SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
LEFT JOIN | SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
RIGHT JOIN | SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
FULL JOIN | SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
LIKE | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] |
SELECT | SELECT column_name(s) FROM table_name |
SELECT * | SELECT * FROM table_name |
SELECT DISTINCT | SELECT DISTINCT column_name(s) FROM table_name |
SELECT INTO | SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name *or *SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name |
SELECT TOP | SELECT TOP number|percent column_name(s) FROM table_name |
TRUNCATE TABLE | TRUNCATE TABLE table_name |
UNION | SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 |
UNION ALL | SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 |
UPDATE | UPDATE table_name SET column1=value, column2=value,… WHERE some_column=some_value |
WHERE | SELECT column_name(s) FROM table_name WHERE column_name operator value |
小写版
SQL语句 | 语法 |
---|---|
and / or | select column_name(s) from table_name where condition and|or condition |
alter table | alter table table_name add column_name datatype or alter table table_name drop column column_name |
as (alias) | select column_name as column_alias from table_name or select column_name from table_name as table_alias |
between | select column_name(s) from table_name where column_name between value1 and value2 |
create database | create database database_name |
create table | create table table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, … ) |
create index | create index index_name on table_name (column_name) or create unique index index_name on table_name (column_name) |
create view | create view view_name as select column_name(s) from table_name where condition |
delete | delete from table_name where some_column=some_value or delete from table_name (note: deletes the entire table!!) delete * from table_name (note: deletes the entire table!!) |
drop database | drop database database_name |
drop index | drop index table_name.index_name (sql server) drop index index_name on table_name (ms access) drop index index_name (db2/oracle) alter table table_name drop index index_name (mysql) |
drop table | drop table table_name |
group by | select column_name, aggregate_function(column_name) from table_name where column_name operator value group by column_name |
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 |
in | select column_name(s) from table_name where column_name in (value1,value2,…) |
insert into | insert into table_name values (value1, value2, value3,…) *or *insert into table_name (column1, column2, column3,…) values (value1, value2, value3,…) |
inner join | select column_name(s) from table_name1 inner join table_name2 on table_name1.column_name=table_name2.column_name |
left join | select column_name(s) from table_name1 left join table_name2 on table_name1.column_name=table_name2.column_name |
right join | select column_name(s) from table_name1 right join table_name2 on table_name1.column_name=table_name2.column_name |
full join | select column_name(s) from table_name1 full join table_name2 on table_name1.column_name=table_name2.column_name |
like | select column_name(s) from table_name where column_name like pattern |
order by | select column_name(s) from table_name order by column_name [asc|desc] |
select | select column_name(s) from table_name |
select * | select * from table_name |
select distinct | select distinct column_name(s) from table_name |
select into | select * into new_table_name [in externaldatabase] from old_table_name *or *select column_name(s) into new_table_name [in externaldatabase] from old_table_name |
select top | select top number|percent column_name(s) from table_name |
truncate table | truncate table table_name |
union | select column_name(s) from table_name1 union select column_name(s) from table_name2 |
union all | select column_name(s) from table_name1 union all select column_name(s) from table_name2 |
update | update table_name set column1=value, column2=value,… where some_column=some_value |
where | select column_name(s) from table_name where column_name operator value |
MySQL 数据类型
在 MySQL 中,有三种主要的类型:Text(文本)、Number(数字)和 Date/Time(日期/时间)类型。
Text 类型:
数据类型 | 描述 |
---|---|
CHAR(size) | 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。 |
VARCHAR(size) | 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。**注释:**如果值的长度大于 255,则被转换为 TEXT 类型。 |
TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
TEXT | 存放最大长度为 65,535 个字符的字符串。 |
BLOB | 用于 BLOBs(Binary Large OBjects)。存放最多 65,535 字节的数据。 |
MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
MEDIUMBLOB | 用于 BLOBs(Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
ENUM(x,y,z,etc.) | 允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。**注释:**这些值是按照您输入的顺序排序的。可以按照此格式输入可能的值: ENUM(‘X’,‘Y’,‘Z’) |
SET | 与 ENUM 类似,不同的是,SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。 |
Number 类型:
数据类型 | 描述 |
---|---|
TINYINT(size) | 带符号-128到127 ,无符号0到255。 |
SMALLINT(size) | 带符号范围-32768到32767,无符号0到65535, size 默认为 6。 |
MEDIUMINT(size) | 带符号范围-8388608到8388607,无符号的范围是0到16777215。 size 默认为9 |
INT(size) | 带符号范围-2147483648到2147483647,无符号的范围是0到4294967295。 size 默认为 11 |
BIGINT(size) | 带符号的范围是-9223372036854775808到9223372036854775807,无符号的范围是0到18446744073709551615。size 默认为 20 |
FLOAT(size,d) | 带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DOUBLE(size,d) | 带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型,允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。 |
**注意:**以上的 size 代表的并不是存储在数据库中的具体的长度,如 int(4) 并不是只能存储4个长度的数字。
实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外,int(M) 跟 int 数据类型是相同的。
例如:
1、int的值为10 (指定zerofill)
int(9)显示结果为000000010 int(3)显示结果为010
就是显示的长度不一样而已 都是占用四个字节的空间
Date 类型:
数据类型 | 描述 |
---|---|
DATE() | 日期。格式:YYYY-MM-DD**注释:**支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’ |
DATETIME() | *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS**注释:**支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ |
TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的秒数来存储。格式:YYYY-MM-DD HH:MM:SS**注释:**支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC |
TIME() | 时间。格式:HH:MM:SS**注释:**支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’ |
YEAR() | 2 位或 4 位格式的年。**注释:**4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 1970 到 2069。 |
*即便 DATETIME 和 TIMESTAMP 返回相同的格式,它们的工作方式很不同。在 INSERT 或 UPDATE 查询中,TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式,比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。