假设
- 表1:Persons
- 表2:Behaviors
SELECT
-** SELECT 查询 **
SELECT * FROM Persons
-** 从Persons表里查询姓名和电话 **
SELECT name,phone
FROM Persons
-** DISTINCT 返回唯一不同的值 **
SELECT DISTINCT name,phone
FROM Persons
-** WHERE 找出地域为北京的用户姓名和电话 **
SELECT name,phone FROM Persons
WHERE city =’beijing’
-** AND/OR 找出来源是微信且(或)地域是北京的用户姓名和电话 **
SELECT name,phone FROM Persons
WHERE city =’beijing’ and(/or) source = ‘wechat’
-** ORDER BY 找出北京地区用户,按公司字母(和名字)排序 **
SELECT * FROM Persons
WHERE city =’beijing’
ORDER BY company (,name)
-** DESC/ASC 找出北京地区用户,按公司字母顺序和名字逆序排序 **
SELECT * FROM Persons
WHERE city =’beijing’
ORDER BY company ASC (,name DESC)
-** 找出前n名 **
SELECT city(列名)
FROM Persons
LIMIT number
-** 找出名字带有‘二’的人 **
SELECT * FROM Persons
WHERE city = ‘%二%’
-** 找出在上海和北京的人,用in **
SELECT * FROM Persons
WHERE city in (‘beijing’,’shanghai’)
-** 值在什么之间 **
SELECT * FROM Persons
WHERE birth
BETWEEN ‘2017/02/03’ AND ‘2018/04/03’
-两表间查询
** 对应相同的名字 **
方式1
SELECT * FROM Persons, Behaviors
WHERE Persons.name = Behaviors.name方式2
** JOIN **
SELECT * FROM Persons
INNER JOIN Behaviors
ON Persons.name = Behaviors.name
-** Left Join 想象左对齐,心里只有左表,不管它怎样,都保留它 **
SELECT * FROM Persons
LEFT JOIN Behaviors
ON Persons.name = Behaviors.name
-** RIGHT JOIN 全为右右右 **
-** FULL JOIN 全部都要,左表右表全在一块儿 **
-** UNION 汇总:两个表表头一样,合并在一起,只取唯一值 **
SELECT name FROM Persons1
UNION
SELECT name FROM Persons2
-** UNION ALL 汇总所有值(用法如上) **
-** SELECT INTO 从前表选取数据插入后表中 **
SELECT */name(列名)
INTO new_Persons (IN Externaldb.mdb)
FROM old_Persons
-** GROUP BY 感觉某字段,分类统计 **
-** INSERT 插入新的行 **
INSERT INTO table_name(列1, 列2)
VALUES (VALUE 1, VALUE 2, VALUE 3)
-** 插入指定行的值,在value里加入限定的值 **
-** UPDATE 更新值 **
UPDATE Persons
SET city(列值)= ‘beijing’
WHERE phone=’12122222222’
-** 删除表(表的行) **
DELETE FROM Persons
(WEHRE city=’beijing’)
-** AS 指定命名 **
-** CREATE DATABASE 创建数据库 **
CREATE DATABASE new_db
-** CREATE TABLE **
CREATE TABLE new_Persons{
name varchar,
city varchar,
…
}
-** 字段约束 **
NOT NULL 不为空
ALERT TABLE Persons
DROP INDEX PersonIDUNIQUE 唯一标识
PRIMARY KEY 主键
FOREIGN KEY
CHECK
DEFAULT