SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE DCType =1;
查询 groupcallinfo 表中 DCUserID 为 13003 或 13004
1
SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID IN (13003, 13004);
BETWEEN AND
查询 groupcallinfo 表中 DCUserID 介于 13003 到 13005
1 2
SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID BETWEEN 13003 AND 13005; SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID >= 13003 AND DCUserID <= 13005;
模糊查询
查询 dcuser 表中所有以 Name 字段以 dis 开头的内容。 % 代替任意数量字符。
1
SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE Name LIKE'dis%'
查询 dcuser 表中所有以 Name 字段以 dis0 + 任意一个字符的内容。 _ 代替一个任意字符。
1
SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE Name LIKE'dis0_'
SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID IN (SELECT UserID FROM dcuser WHERE DCType =1); SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID =ANY(SELECT UserID FROM dcuser WHERE DCType =1); SELECT A.DCUserID, A.GroupID, A.Time, A.CallType FROM groupcallinfo A WHERE A.DCUserID IN (SELECT B.UserID FROM dcuser B WHERE B.DCType =1);
1
SELECT*FROM contacts WHERE (surname, firstname) IN (SELECT surname, firstname FROM customer);
ALL 与 ANY 关键字:
找出 class1 中比 class2 所有 source 都高的学生信息。
1
SELECT*FROM class1 WHERE source >ALL(SELECT source FROM class2);
找出 class1 中 second_name 与 class2 中的重名的学生信息。
1
SELECT*FROM class1 WHERE second_name =ANY(SELECT second_name FROM class2);
所以聚合语句(sum,min,max,avg,count)要比having子句优先执行,而where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)。 where子句: select sum(num) as rmb from order where id>10 //只有先查询出id大于10的记录才能进行聚合语句
UPDATE runoob_tbl SET runoob_title='学习 C++'WHERE runoob_id=3;
过程、函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
DELIMITER //
DROPPROCEDURE IF EXISTS Create10K//
CREATEPROCEDURE `Create10K`(IN `for_time` INT, IN `type_int` INT, IN `info_text` VARCHAR(255)) BEGIN DECLARE i INTDEFAULT0; WHILE i < for_time *10000 DO INSERT INTO test_table(Type, Info) VALUES(type_int, info_text); SET i = i +1; END WHILE; END//