Fork me on GitHub

sql基础常用

发表于 | 分类于 | | 本文总阅读量 次
字数统计: 926 | 阅读时长 ≈ 5

日期格式化(DATE_FORMAT() 函数)

1
2
3
4
5
6
7
DATE_FORMAT(date,format)

DATE_FORMAT(NOW(),'%Y%m%d')//20180704
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

设置自增日期

类型：timestamp

默认：CURRENT_TIMESTAMP

CREATE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `tel` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_info
-- ----------------------------
INSERT INTO `user_info` VALUES ('1', 'yinxs', '24', '18811428452', '北京', '2018-07-04 16:21:09');
INSERT INTO `user_info` VALUES ('2', 'yinxs', '24', '18811428452', '北京', '2018-07-04 16:21:56');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for option_info
-- ----------------------------
DROP TABLE IF EXISTS `option_info`;
CREATE TABLE `option_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `option` varchar(255) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of option_info
-- ----------------------------
INSERT INTO `option_info` VALUES ('1', '1', '选项一', '2018-07-04 16:29:03');

INSERT

1
INSERT INTO user_info (name,age,tel,address) VALUES ('yinxs','24','18811428452','北京')

left join

1
SELECT * FROM user_info AS a LEFT JOIN option_info AS b ON(b.user_id = a.id)

HAVING

1
SELECT * FROM user_info HAVING SUM(age) > 1000

inner join

同join

1
SELECT * FROM user_info AS a INNER JOIN option_info AS b ON (b.user_id = a.id)

right join

1
SELECT * FROM user_info AS a RIGHT JOIN option_info AS b ON(b.user_id = a.id)

update

1
UPDATE user_info SET tel = '18811428453' WHERE id = 1

DELETE

1
DELETE FROM user_info WHERE id = 2

DISTINCT

去重

1
SELECT DISTINCT age FROM user_info

LIKE

1
2
3
4
5
6
7
以y开始
SELECT * FROM user_info WHERE name LIKE 'y%'

SELECT * FROM user_info WHERE name LIKE '%x%'

以s结束
SELECT * FROM user_info WHERE name LIKE '%s'

ORDER BY

1
2
3
4
正序
SELECT * FROM user_info AS a LEFT JOIN option_info AS b ON(b.user_id = a.id) ORDER BY a.id 
逆序
SELECT * FROM user_info AS a LEFT JOIN option_info AS b ON(b.user_id = a.id) ORDER BY a.id DESC

function(SQL Server)

1
2
3
4
5
6
7
8
9
10

AVG(column)	返回某列的平均值
COUNT(column)	返回某列的行数（不包括NULL值）
COUNT(*)	返回被选行数
COUNT(DISTINCT column)	返回相异结果的数目
FIRST(column)	返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column)	返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
SUM(column)	返回某列的总和

GROUP BY

1
SELECT * FROM user_info AS a LEFT JOIN option_info AS b ON (b.user_id = a.id) ORDER BY age

UNION && UNION ALL

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

1
2
3
SELECT * FROM user_info UNION SELECT * FROM user_info

SELECT * FROM user_info UNION ALL SELECT * FROM user_info

AND & OR

1
2
3
SELECT * FROM user_info WHERE id = 1 AND age = 24

SELECT * FROM user_info WHERE id = 1 OR age = 24

in

1
2
3
SELECT * from user_info WHERE id in (1,2,3)

SELECT * from user_info WHERE id not in (1,2,3)

case when zhen

1
2
3
4
5
6
7
8
9
--简单case函数
case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' end
--case搜索函数
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' end

实例：

1
SELECT (CASE when uid = 1 THEN '男' WHEN uid = 2 THEN '女' ELSE '其他' END)性别  FROM `session`

Round

1
2
3
4
5
SELECT ROUND(column_name,decimals) FROM table_name


column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

舍入为最接近的整数

1
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

标准函数中的日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1、day(date_expression)

返回date_expression中的日期值

 

2、month(date_expression)

返回date_expression中的月份值

 

3、year(date_expression)

返回date_expression中的年份值

BETWEEN … AND

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

1
select * from tj_md WHERE date BETWEEN 20180705 AND 20180706
显示 Gitment 评论