数据库语言的个人笔记

想试试自己写ORM，总用别人的，多少感觉有点不爽。

. use Database; 命令用于选择数据库。
. set names utf8; 命令用于设置使用的字符集。
. SELECT * FROM Websites; 读取数据表的信息。

SELECT - 从数据库中提取数据UPDATE - 更新数据库中的数据DELETE - 从数据库中删除数据INSERT INTO - 向数据库中插入新数据CREATE DATABASE - 创建新数据库ALTER DATABASE - 修改数据库CREATE TABLE - 创建新表ALTER TABLE - 变更（改变）数据库表DROP TABLE - 删除表CREATE INDEX - 创建索引（搜索键）DROP INDEX - 删除索引

SELECT DISTINCT 语句用于返回唯一不同的值。

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

ASC | DESC

INSERT INTO table_name
VALUES (value1,value2,value3,…);

INSERT INTO table_name (column1,column2,column3,…)
VALUES (value1,value2,value3,…);

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

DELETE FROM table_name
WHERE some_column=some_value;

mysql SELECT column_name(s)
FROM table_name
LIMIT number;

sql ServerSELECT TOP number|percent column_name(s)
FROM table_name;

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

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

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

下面的 SQL 语句选取 name 以 “G”、”F” 或 “s” 开始的所有网站;

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

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

函数

>SELECT AVG(column_name) FROM table_name>SELECT COUNT(column_name) FROM table_name;>SELECT FIRST(column_name) FROM table_name;>SELECT LAST(column_name) FROM table_name;>SELECT MAX(column_name) FROM table_name;>SELECT MIN(column_name) FROM table_name;>SELECT SUM(column_name) FROM table_name;>SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name; >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; >SELECT UCASE(column_name) FROM table_name;>SELECT LCASE(column_name) FROM table_name;>SELECT LCASE(column_name) FROM table_name;>SELECT LEN(column_name) FROM table_name;>SELECT ROUND(column_name,decimals) FROM table_name;>SELECT NOW() FROM table_name;>SELECT FORMAT(column_name,format) FROM table_name;