select 多查询-SQL增删改查补充和limit以及order by

​cnblogs.com/wupeiqi/articles/5713315.html

表内容操作

1、增

1 2 3

insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表 --insert into foo (bar,baz) select ArtistId,Column1 from Album; 复制表Albu到foo表

2、删

1 2	delete from 表 delete from 表 where id＝1 and name＝'alex'

3、改

1	update 表 set name ＝ 'alex' where id>1

4、查

1 2 3

select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id > 1

5、其他

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

a、条件     select * from 表 where id > 1 and name != 'alex' and num = 12;     select * from 表 where id between 5 and 16;     select * from 表 where id in (11,22,33)     select * from 表 where id not in (11,22,33)     select * from 表 where id in (select nid from 表) b、通配符     select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）     select * from 表 where name like 'ale_'  - ale开头的所有（一个字符） c、限制     select * from 表 limit 5;            - 前5行     select * from 表 limit 4,5;          - 从第4行开始的5行     select * from 表 limit 5 offset 4    - 从第4行开始的5行 d、排序     select * from 表 order by 列 asc              - 根据 “列” 从小到大排列     select * from 表 order by 列 desc             - 根据 “列” 从大到小排列     select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序 e、分组     select num from 表 group by num     select num,nid from 表 group by num,nid     select num,nid from 表  where nid > 10 group by num,nid order nid desc     select num,nid,count(*),sum(score),max(score),min(score),avg(score) from 表 group by num,nid                                 如果对于聚合函数结果进行二次筛选时?必须使用having     select num from 表 group by num having max(id) > 10     特别的：group by 必须在where之后，order by之前 f、连表     无对应关系则不显示     select A.num, A.name, B.name     from A,B     Where A.nid = B.nid     无对应关系则不显示     select A.num, A.name, B.name     from A inner join B     on A.nid = B.nid     A表所有显示，如果B中无对应关系，则值为null     select A.num, A.name, B.name     from A left join B     on A.nid = B.nid     B表所有显示，如果B中无对应关系，则值为null     select A.num, A.name, B.name     from A right join B     on A.nid = B.nid g、组合     组合，自动处理重合     select nickname     from A     union     select name     from B     组合，不处理重合     select nickname     from A     union all     select name     from B

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