数据库创建和查询语句总结

来源：互联网发布：d3.js 力导向图编辑：程序博客网时间：2024/05/21 19:26

例子：

1、SELECT Profile.UserId,CouponCode,UserName,FROM_UNIXTIME(ValidEndTime/1000) FROM UserCoupon,Profile
WHERE Profile.UserName = "**********"
ANDUserCoupon.UserId = Profile.UserId;

username条件在前，因为username可以更快定位，效率高
FROM_UNIXTIME(ValidEndTime/1000) 数据库中时间存储的是long值，利用此函数进行转换，函数传入参数是秒为单位，long值以毫秒为单位，故除以1000

2、表中包含学号，语文，英语，数学，求总成绩前三，降序排序

SELECT Sno,(Chinese + English + Math) AS sumScore FROM TableName1
       GROUP BY Sno
       ORDER BY sumScore DESC
       LIMIT 3;

3、利用存储过程向数据库中添加多条数据

DELIMITER $$

CREATE PROCEDURE `sss`.`ddd`()
    BEGIN

   DECLARE i INT;
       SET i = 0;
       WHILE i<4000 DO

           INSERT INTO TB_a(Id,Accno,Bankid,Certno,Firmid,Name,Phone,UserId,Type)
               VALUES(20000+i,'2222','1000','3333','10000','aaa',12000000000 + i,'5000','1');
           SET i = i + 1;
       END WHILE;
    END$$

DELIMITER ;

CALL ddd();   //创建存储过程之后，调用存储过程才会向表中插入数据

本方法是比较土的方法，因为id不能一样，所以下次重新执行的时候需要修改前边的常量，而且在创建存储过程的时候并没有考虑是否存在相同名字的存储过程，所以下次创建的时候又需要将本存储过程的名字进行修改，这样子每次都需要变化，很麻烦。

改进：

DELIMITER $$
USE `sss` $$
DROP PROCEDURE IF EXISTS insertNum $$

CREATE PROCEDURE insertNum(IN num BIGINT(20),OUT newId BIGINT(20))
    BEGIN

   DECLARE i BIGINT(20);
       SET i = 1;
       SELECT MAX(Id) FROM TB_a INTO newId;
       WHILE i <= num DO
           INSERT INTO TB_a(Id,Accno,Bankid,Certno,Firmid,Name,Phone,UserId,Type)
               VALUES(newId+i,'2222','1000','3333','10000','aaa',10000000000 + i,'5000','1');
           SET i = i + 1;
       END WHILE;
    END$$

DELIMITER ;

CALL insertNum(2,@id);

本方法使用了带参数的存储过程，并且创建存储过程之前先检查是否有与此名字相同的存储过程，有的话则删除，然后查询出已有的最大id,在此id的基础上继续增加，并且将重复次数设置成了变量，这样直接传参数给存储过程调用即可。

4、查询不重复的数据

//只是查询重复记录的单个字段
SELECT DISTINCT Phone FROM TB_a;

//查询重复记录的任意字段
SELECT MIN(Phone),Email FROM TB_a
   GROUP BY Phone;
//打开重复任意次数的所有记录
SELECT * FROM TB_a
   WHERE Phone IN
   (SELECT Phone FROM TB_a
       GROUP BY Phone
       HAVING COUNT(Phone) = 4);

注：where只能用在group by前边，group by 后边只能使用 having进行限定。

5、表中有三个字段A,B,C，先要求更新A，若B满足一定条件，则A更新为'111',否则更新为'222'

update 表 set A = (case when B=满足一定的条件 then '111' else '222' end)

例：

UPDATE student SET invalid = (
   CASE WHEN Sno IN (SELECT Sno FROM student WHERE Sage=25 AND Ssex='M')
       THEN 0
   ELSE 1
   END
);

这种情况报错：1093 因为子查询表和更新表一样，这样子不行

UPDATE student SET invalid = (
   CASE
       WHEN Sage=25 AND Ssex='M'
       THEN 0
       ELSE 1
   END
);

UPDATE student SET invalid = (
   CASE
        WHEN Sage=25 AND Ssex='W'
       THEN 0
        WHEN Sage!=25 AND Ssex!='M'
       THEN 2
        ELSE 1
   END
);

直接写条件

sql case when的使用

http://www.cnblogs.com/qiantuwuliang/archive/2009/06/03/1495770.html

总结：

1、创建带有外键的表：不同的数据库支持的写法不一样，要注意：

第一种：MySql

create table SC(

Sno varchar(7),

Cno varchar(7),

grade int,

primary key(Sno,Cno),

foreign key(Sno) references Student(Sno),

foreign key(Cno) references Course(Cno)

);

第二种：SQL Server/Oracle/MS Access

create table SC(

Sno varchar(7) foreign key references Student(Sno),

Cno varchar(7) foreign key references Course(Cno),

grade int,

primary key(Sno,Cno)

);

2、查询：在销售记录表中，查询总销量前8的商品Id和总销量,降序排列

表结构：Id，Count(个数)，订单Id，ProductId，销售时间，skuId, 用户Id, Username,

select ProductId,Sum(Count) from SalesRecord

group by ProdctId

Order by sum(Count) desc

limit 8;

3、查询总体语句：

0 0