常用sql大全袖珍版

根据给定文件的信息，我们可以总结出以下SQL知识点： ### 常用SQL大全：Mssql与Access中的区别 #### 1. 数据库操作 (DDL - Data Definition Language) - **创建数据库**: ```sql CREATE DATABASE database-name; ``` - **删除数据库**: ```sql DROP DATABASE dbname; ``` #### 2. 数据表操作 - **创建数据表**: ```sql CREATE TABLE tabname ( col1 type1 [NOT NULL] [PRIMARY KEY], col2 type2 [NOT NULL], ... ); ``` - **注释**: 可以通过 `CREATE TABLE tab_new LIKE tab_old` 来复制已有的表结构。 - **注释**: 也可以通过 `CREATE TABLE tab_new AS SELECT col1, col2 FROM tab_old DEFINITION ONLY` 来仅复制表的定义。 - **删除数据表**: ```sql DROP TABLE tabname; ``` - **修改数据表**: ```sql ALTER TABLE tabname ADD COLUMN col type; ``` - **添加主键**: ```sql ALTER TABLE tabname ADD PRIMARY KEY (col); ``` - **删除主键**: ```sql ALTER TABLE tabname DROP PRIMARY KEY (col); ``` - **创建索引**: ```sql CREATE [UNIQUE] INDEX idxname ON tabname (col); ``` - **删除索引**: ```sql DROP INDEX idxname; ``` - **创建视图**: ```sql CREATE VIEW viewname AS SELECT statement; ``` - **删除视图**: ```sql DROP VIEW viewname; ``` #### 3. 数据操作 (DML - Data Manipulation Language) - **查询数据**: ```sql SELECT * FROM table1 WHERE condition; ``` - **模糊查询**: ```sql SELECT * FROM table1 WHERE field1 LIKE '%value1%'; ``` - **排序查询**: ```sql SELECT * FROM table1 ORDER BY field1, field2 DESC; ``` - **统计记录数量**: ```sql SELECT COUNT(*) AS total_count FROM table1; ``` - **求和**: ```sql SELECT SUM(field1) AS sum_value FROM table1; ``` - **平均值**: ```sql SELECT AVG(field1) AS avg_value FROM table1; ``` - **最大值**: ```sql SELECT MAX(field1) AS max_value FROM table1; ``` - **最小值**: ```sql SELECT MIN(field1) AS min_value FROM table1; ``` - **插入数据**: ```sql INSERT INTO table1 (field1, field2) VALUES (value1, value2); ``` - **更新数据**: ```sql UPDATE table1 SET field1 = value1 WHERE condition; ``` - **删除数据**: ```sql DELETE FROM table1 WHERE condition; ``` #### 4. 数据集操作 (DCL - Data Control Language) - **权限控制**: - **授予权限**: ```sql GRANT permission TO username; ``` - **撤销权限**: ```sql REVOKE permission FROM username; ``` - **事务控制**: - **提交事务**: ```sql COMMIT; ``` - **回滚事务**: ```sql ROLLBACK; ``` #### 5. 集合操作 - **联合查询 UNION**: ```sql SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; ``` - **注释**: UNION会自动去除重复行，如果需要保留所有行，则使用 `UNION ALL`。 - **差集查询 EXCEPT**: ```sql SELECT column_name(s) FROM table1 EXCEPT SELECT column_name(s) FROM table2; ``` - **注释**: EXCEPT返回在第一个表中但不在第二个表中的行，如果需要保留所有行，则使用 `EXCEPT ALL`。 - **交集查询 INTERSECT**: ```sql SELECT column_name(s) FROM table1 INTERSECT SELECT column_name(s) FROM table2; ``` - **注释**: INTERSECT返回两个表中的共同行，如果需要保留所有行，则使用 `INTERSECT ALL`。 #### 6. JOIN操作 - **左连接 LEFT OUTER JOIN**: ```sql SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a LEFT OUTER JOIN b ON a.a = b.c; ``` - **右连接 RIGHT OUTER JOIN**: ```sql SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a RIGHT OUTER JOIN b ON a.a = b.c; ``` - **全连接 FULL OUTER JOIN**: ```sql SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a FULL OUTER JOIN b ON a.a = b.c; ``` #### 7. 特殊操作 - **Access 中的数据导出**: ```sql -- 创建新表并插入数据 SELECT * INTO new_table FROM old_table WHERE 1<>1; -- 插入一个空表 INSERT INTO new_table (column1, column2) SELECT column1, column2 FROM old_table; ``` - **跨数据库查询**: ```sql INSERT INTO target_table (column1, column2) SELECT source_column1, source_column2 FROM source_table IN 'database_path' WHERE condition; ``` 以上是对Mssql中常用的SQL命令进行了详细的介绍，并指出了这些语句在Access中可能存在的差异。需要注意的是，不同的数据库系统可能会有不同的语法和功能支持，因此在实际使用时应根据所使用的具体数据库系统来调整相应的SQL语句。