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