Mysql的查询总结

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for jointest
-- ----------------------------
DROP TABLE IF EXISTS `jointest`;
CREATE TABLE `jointest` (
  `id` int(10) DEFAULT NULL,
  `Cus_num` varchar(10) DEFAULT NULL,
  `num_id` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `O_Id` int(10) NOT NULL AUTO_INCREMENT,
  `OrderDate` date DEFAULT NULL,
  `OrderPrice` decimal(10,2) DEFAULT NULL,
  `Customer` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`O_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

Mysql不支持的：first，last，format，top，full join
/*-----Mysql高级查询知识点*/
/*1.查询条数限制*/

SELECT * FROM test LIMIT 0,2

/*2.查询的通配符,%替代一个或多个字符*/

SELECT * FROM test WHERE Customer NOT LIKE '%h%'

/*3._表示仅代替一个字符*/

SELECT * FROM test WHERE Customer LIKE '_ush'

/*4.包含A或B的，但是不管用好像*/

SELECT * FROM test WHERE Customer LIKE '[AB]%'

/*5.表示这一列中包含Bush或Adams*/

SELECT * FROM test WHERE Customer IN ('Bush','Adams')

/*6.介于两个值之间，可以是数值，文本或日期*/

SELECT * FROM test WHERE OrderPrice BETWEEN 1000 AND 2100

/*7.AS指定列的别名*/

SELECT Customer AS C From test

/*8.JOIN表之间的关联*/
SELECT Customer,Cus_num 
FROM test AS t,JoinTest AS j 

WHERE t.O_id=j.num_id

/*9.InnerJoin内连接查询,双方都有值才显示*/
SELECT Customer,j.Cus_num as Cus_num
FROM test AS t

INNER JOIN jointest AS j ON t.O_id=j.num_id

/*10.LeftJoin左连接查询，查询左表所有的信息*/
SELECT O_id,Customer,j.Cus_num as Cus_num
FROM test AS t

LEFT JOIN jointest AS j ON t.O_id=j.num_id

/*11.RightJoin右连接查询，右表所有信息*/
SELECT O_id,Customer,j.Cus_num as Cus_num
FROM test AS t

RIGHT JOIN jointest AS j ON t.O_id=j.num_id

/*12.Mysql不支持full，所以用这种方法代替*/
SELECT O_id,Customer,j.Cus_num as Cus_num
FROM test AS t

LEFT JOIN jointest AS j ON t.O_id=j.num_id

/*13.合并相同的重复的列*/
UNION
SELECT O_id,Customer,j.Cus_num as Cus_num
FROM test AS t

RIGHT JOIN jointest AS j ON t.O_id=j.num_id

/*14.select into复制数据，create database table
     创建数据库，create table name创建表名

     drop删除表库，atler修改表内容*/

/*15.约束条件：NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,DEFAULT*/
NOT NULL:非空约束，字段不能为空
UNIQUE:唯一约束，字段不能有重复的值‘
PRIMARY KEY:主键约束，必须唯一值，不能为空，只有一个
FOREIGN KEY:与其他表做关联的
CHECK (O_id>0):表明O_id的值必须大于0
DEFAULT:在创建insert语句可能需要输入默认值
UNIQUE INDEX:唯一索引，用户无法查看索引，用来提交查询速度
AUTO-INCERMENT:设置id自增


/*-----Mysql函数查询知识点-----*/
/*1.求平均值*/

SELECT AVG(OrderPrice) FROM test

/*2.求某一列的总数*/
DISTINCT:去掉重复的数值
SELECT COUNT(OrderPrice) FROM test

SELECT COUNT(DISTINCT OrderPrice) FROM test

/*3.Mysql没有first和last函数，用limit代替*/

SELECT Customer FROM test LIMIT 0,1

/*4.Max和Min函数,获取某列的最大最小值*/
SELECT Max(OrderPrice) FROM test

SELECT Min(OrderPrice) FROM test

/*5.GROUP BY根据什么分组查询*/

SELECT OrderPrice,Customer FROM test GROUP BY Customer

/*6.HAVING跟where差不多，因为sum()函数与where不能一起使用*/
SELECT OrderPrice,Customer FROM test
WHERE  Customer='Bush' OR Customer='Adams'
GROUP BY Customer

HAVING SUM(OrderPrice)>1000

/*7.UCASE把字段的值转为大写,LCASE把字段转为小写*/
SELECT UCASE(Customer) FROM test

SELECT LCASE(Customer) FROM test

/*8.MID提取字段的字符,起始值>=1,后面是截取的长度*/

SELECT MID(Customer,2,2) FROM test

/*9.获取字段的长度*/

SELECT LENGTH(Customer) FROM test

/*10.ROUND设置数值类保留几位小数*/

SELECT OrderPrice, ROUND(SUM(OrderPrice),1) as OrderPrice FROM test

/*11.获取当前的时间*/
SELECT Customer, NOW() FROM test
/*日期格式化*/
SELECT Customer,DATE_FORMAT(Now(),'%m-%d-%Y') as date FROM test
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')：Dec 29 2008 11:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y')：12-29-2008
DATE_FORMAT(NOW(),'%d %b %y')：29 Dec 08
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')：29 Dec 2008 16:25:46.635