sql

insert into
INSERT INTO 表名称 VALUES (值1, 值2,....);
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....);
INSERT INTO Persons VALUES (‘Gates‘, ‘Bill‘, ‘Xuanwumen 10‘, ‘Beijing‘);
INSERT INTO Persons (LastName, Address) VALUES (‘Wilson‘, ‘Champs-Elysees‘);

update
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;
UPDATE Person SET FirstName = ‘Fred‘ WHERE LastName = ‘Wilson‘ ;
UPDATE Person SET Address = ‘Zhongshan 23‘, City = ‘Nanjing‘
WHERE LastName = ‘Wilson‘;

delete
DELETE FROM 表名称 WHERE 列名称 = 值;
DELETE FROM Person WHERE LastName = ‘Wilson‘;
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM table_name;
DELETE * FROM table_name;

top mysql 
SELECT * FROM Persons LIMIT 5;

like
SELECT * FROM Persons WHERE City LIKE ‘N%‘;
提示："%" 可用于定义通配符（模式中缺少的字母）。
以g结尾的city
SELECT * FROM Persons WHERE City LIKE ‘%g‘;
包含lon的city
SELECT * FROM Persons WHERE City LIKE ‘%lon%‘;
不包含
SELECT * FROM Persons WHERE City NOT LIKE ‘%lon%‘;

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

---------------------------------
SELECT * FROM Persons  WHERE FirstName LIKE ‘_eorge‘;

SELECT * FROM Persons  WHERE LastName LIKE ‘C_r_er‘;

我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM Persons  WHERE City LIKE ‘[ALN]%‘;
上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM Persons  WHERE City LIKE ‘[!ALN]%‘;

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

---------------
SELECT * FROM Persons
WHERE LastName IN (‘Adams‘,‘Carter‘);

----------------------------------------

between .....and
以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人，
SELECT * FROM Persons
WHERE LastName
BETWEEN ‘Adams‘ AND ‘Carter‘;

SELECT * FROM Persons
WHERE LastName
NOT BETWEEN ‘Adams‘ AND ‘Carter‘;

Alias
SELECT column_name AS alias_name
FROM table_name;

join
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P ;

-------------或者
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName;

JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

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

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

希望列出所有的人，以及他们的定单，以及所有的定单，以及定购它们的人
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;

union
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA;

select into
SQL SELECT INTO 语法
您可以把所有的列插入新表：
SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename
或者只把希望的列插入新表：
SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename
------------
下面的例子会制作 "Persons" 表的备份复件：
SELECT *
INTO Persons_backup
FROM Persons;

IN 子句可用于向另一个数据库中拷贝表：
SELECT *
INTO Persons IN ‘Backup.mdb‘
FROM Persons;

SELECT LastName,FirstName
INTO Persons_backup
FROM Persons;

下面的例子通过从 "Persons" 表中提取居住在 "Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表：
SELECT LastName,Firstname
INTO Persons_backup
FROM Persons
WHERE City=‘Beijing‘;

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P;

sql

标签：