sql常用语句

 1、数据库的常用操作

（1）创建数据库
CREATE DATABASE Nuctech
 
（2）删除数据库
DROP DATABASE Nuctech
 
（3）创建表
CREATE TABLE Zhaocl(name VARCHAR(50),gender VARCHAR(50),age INT)
 
（4）删除表
DELETE dbo.Zhaocl			--删除表数据，不删除表结构，不释放空间
TRUNCATE TABLE dbo.Zhaocl		--删除表数据，不删除表结构，释放空间
DROP TABLE dbo.Zhaocl			--删除表所有，删除表结构，释放空间
 
（5）增加字段（列）
ALTER TABLE dbo.Zhaocl ADD addr VARCHAR(50)
 
（6）删除字段（列）
ALTER TABLE dbo.Zhaocl DROP COLUMN age
 
（7）插入数据
INSERT dbo.Zhaocl
        ( NAME, gender, addr )
VALUES  ( 'zhaocl', -- NAME - varchar(50)
          'men', -- gender - varchar(50)
          'beijing'  -- addr - varchar(50)
          )
 
（8）删除数据
DELETE FROM dbo.Zhaocl WHERE NAME = 'zhaocl'  --（注意where条件，否则就删除所有数据了）
 
（9）更新数据
UPDATE dbo.Zhaocl SET addr = 'zhengzhou' WHERE NAME = 'zhaocl' --（注意where条件，否则更新所有字段值）
 
（10）查询表所有数据
SELECT * FROM dbo.Websites
 
（11）查询某列数据
SELECT name FROM dbo.Websites
 
（12）返回唯一不同的值
SELECT DISTINCT name FROM dbo.Websites
 
（13）升序排列（默认ASC）
SELECT * FROM dbo.Websites ORDER BY alexa
 
（14）降序排列
SELECT * FROM dbo.Websites ORDER BY alexa DESC
 
（15）获取第一条数据
SELECT TOP 1 NAME,url FROM dbo.Websites
 
（16）获取最后一条数据
SELECT TOP 1 name FROM dbo.Websites ORDER BY name DESC
 
（17）获取某列最大值
SELECT MAX(alexa) AS maxAlexa FROM dbo.Websites
 
（18）获取某列最小值
SELECT MIN(alexa) AS minAlexa FROM dbo.Websites
 
（19）某列求和(varchar类型的不可)
SELECT SUM(name) AS sumAlexa FROM dbo.Websites
 
（20）字母转大写
SELECT UPPER(country) FROM dbo.Websites
 
（21）字母转小写
UPDATE dbo.Websites SET country = LOWER(country)
 
（22）字符串截取
SELECT SUBSTRING(country,1,3) FROM dbo.Websites
 
（23）获取字符串长度
SELECT LEN(country) AS lenCountry FROM dbo.Websites WHERE alexa=13
 
（24）四舍五入
SELECT ROUND(1.56,1)
 
（25）第三个参数非0时，表示舍弃位数，不再进行四舍五入
SELECT ROUND(2.56,1,1)
 
（26）获取当前时间
SELECT GETDATE()
 
（27）获取当前年月日
SELECT DATENAME(YEAR,GETDATE())
SELECT DATENAME(MONTH,GETDATE())
SELECT DATENAME(DAY,GETDATE())
SELECT YEAR(GETDATE())
 
（28）FORMAT格式化
SELECT FORMAT(GETDATE(),'yyyy-MM-dd')
 
（29）对结果集分组，并对组进行操作（SUM）
SELECT country,SUM(alexa) AS sumAlexa FROM dbo.Websites GROUP BY country
 
（30）where无法和聚合函数一起使用，所以使用HAVING跟条件
SELECT country,AVG(alexa) AS avgAlexa FROM dbo.Websites GROUP BY country HAVING country = 'china'
 
（31）LIKE
SELECT country FROM dbo.Websites WHERE country LIKE '%a%'	--包含a（SQL不区分大小写）
SELECT country FROM dbo.Websites WHERE country LIKE '%a'	--a结尾
SELECT country FROM dbo.Websites WHERE country LIKE 'a%'	--a开头
SELECT country FROM dbo.Websites WHERE country LIKE '_a_'	--三位并且中间是a
SELECT country FROM dbo.Websites WHERE country LIKE '_a'	--两位并且末尾是a
SELECT country FROM dbo.Websites WHERE country LIKE 'a_'	--两位并且a开头
 
（32）返回多个条件中符合的条目
SELECT * FROM dbo.Websites WHERE alexa IN ('1','0','100')
 
（33）返回范围内的数据
SELECT * FROM dbo.Websites WHERE alexa BETWEEN 1 AND 26 --[]两边都包括
 
（34）列的别名（单个以及多个）
SELECT name AS wName,CONCAT(url,'-',alexa,'-',country) AS allName FROM dbo.Websites
 
（35）表的别名
SELECT * FROM dbo.Websites a,dbo.Zhaocl b WHERE a.alexa = b.gender

（36）select 1 as test from 表A;
假设表A有4条数据
那么得出的结果集就是相当于建了一个临时表，也有4条数据，只有1列，列名为test，并且每行的值都是1，同理select 2的话，其余一样，每行的值为2

（37）select COUNT（1）from 表A;
返回表A的行数，即有多少条数据，count(10)也是也一样，依旧返回行数。

（38）select SUM（1） from 表A;
假设表A有4条数据，SUM(1)得出4，SUM(10)得出40

2、多表查询的方法

现有两张表A和B

简单的多表查询语句一般可以这么写：

select * from A a，B b where a.id = b.id 

若想查询某些字段：

select a.name, b.address from A a, B b where a.id = b.id

3、内连接  join 或 inner join

SQL语句：

select * from A a inner join B b on a.ID = b.ID

innerjoin 是比较运算符，只返回符合条件的行。

上面就相当于：

select * from A a, B b where a.ID = b.ID

4、外连接

外连接分为：左外连接，右外连接，全外连接

    a.  左外连接  left join或 left outer join

    左外连接是以左表为主，如果左表中某行在右表没有匹配的，则结果中对应行右表的部分显示NULL

   SQL语句示例：

select * from A a left join B n on a.ID = b.ID

    b.  右外连接  right join或 right outer join

    右外连接是以右表为主，如果右表中某行在左表没有匹配的，则结果中对应行左表的部分显示NULL

SQL语句示例：

select * from A a right join B n on a.ID = b.ID

    c.  完全外连接  full join或 full outer join

    完全外连接包含full join左右两表中所有的行，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分显示NULL，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分显示为NULL

    SQL语句示例：

select * from A a full join B n on a.ID = b.ID

5、交叉连接  cross join （又叫笛卡尔积）

两个表做交叉连接，得到的结果集的行数是两个表的行数的乘积。 cross join后加条件只能用where,不能用on

select * from A a cross join B b where a.ID=b.ID

注意：带有where条件的子句，往往会先生成两个表行数乘积的数据表，然后从根据where条件从中选择。 当数据量比价大的时候，笛卡尔积操作会很消耗数据库的性能

6、子查询

（1）带 In 关键字的子查询

一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。

（2）带比较运算符的子查询

子查询可以使用比较运算符。

（3）带 （not）Exists 关键字的子查询

假如子查询查询到记录，则进行外层查询，否则，不执行外层查询；not exists表示内层没有内容时才查询外层。

如下，表B中返回的有内容时，才执行外层表A的查询

SELECT * from A where EXISTS(select * from B);

（4）带 Any 关键字的子查询

ANY 关键字表示满足其中任一条件

如下，A表中筛选价格大于任意B表中选出的价格的结果，即比最小的大的价格就满足条件

SELECT * FROM A WHERE price>= ANY (SELECT price FROM B);

（5）带 All 关键字的子查询

ALL 关键字表示满足所有条件。

如下，表A中筛选价格大于所有表B中选出的价格的结果，即比最大的价格大的才满足条件

SELECT * FROM A WHERE price>= ALL (SELECT price FROM B);

7、合并查询

    UNION 和 UNION ALL

    UNION 是，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录

   UNION ALL 不去重

8、分组查询 group by

select sex ,count(*)  as 人数  from student group by sex

上述的语句会出错，原因是count为聚合函数，返回集只有一条数据，而普通的查询结果可能有多条，是不能整合到一起。

所以为了保证完整性，系统约定俗成，在使用了聚合函数的查询语句中，除了聚合函数，可以在查询列表上，如果要出现其他字段，那么该字段就必须为分组字段，而且该字段一定要跟随在GROUP BY关键字后面。

即与聚合函数一起出现在select后面进行查询的列，只有两种可能性：被聚合 、被分组

例：对表里，查询每一个班级的男女生的人数

分析：第一轮先对班级分组，得到班级个数，再对每个班级的男女分组

select GradeId,sex,COUNT(*) from student group by GradeId,Sex order by GradeId

HAVING子句的使用：

where里面不能出现聚合函数作为条件。

where和having的不同：where是对原始数据进行筛选，having是对分组时候的数据进行筛选

select  GradeId,sex,COUNT(*) as renshu from student group by GradeId, Sex having count(*)>=3 order by GradeId

9、count函数

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计