SQL 学习笔记

文档中心

-- AND 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

SELECT * FROM b2b_menu_info WHERE MENUID =1002 AND MENUTYPE=1;

-- OR 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
SELECT * FROM b2b_menu_info WHERE MENUID=1002 OR MENUTYPE=7;

-- ORDER BY 语句用于根据指定的列对结果集进行排序默认是升序 , DESC 是降 ASC是升。
SELECT * FROM b2b_menu_info WHERE SUPERID=1002 ORDER BY MENUID DESC

-- INSERT INTO 插入语句
INSERT INTO b2b_menu_info (MENUID,MENUNAME)VALUES(1002,'订单管理');

-- Update SET 语句用于修改表中的数据。
UPDATE b2b_menu_info SET MENUNAME='订单管理' WHERE MENUID=1002;

-- DELETE 语句用于删除表中的行。
DELETE FROM b2b_menu_info WHERE MENUID=1002;

-- like 模糊查询语句
SELECT * FROM b2b_menu_info WHERE MENUID LIKE '%4%';

-- IN 操作符允许我们在 WHERE 子句中规定多个值。
SELECT * FROM b2b_menu_info WHERE MENUID IN(1001,1002);

-- count 统计数量
SELECT COUNT(MENUID) FROM b2b_menu_info ;

-- BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SELECT * FROM b2b_menu_info WHERE MENUID BETWEEN 1001001 AND 1002004;

-- 内连接 INNER JOIN 连接的2张表都存在关联数据则显示
SELECT COUNT(A.MENUID) FROM b2b_menu_info A INNER JOIN b2b_role_menu_info B ON B.MENUID=A.MENUID;

-- 左连接 LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
SELECT COUNT(A.MENUID) FROM b2b_menu_info A LEFT JOIN b2b_role_menu_info B ON B.MENUID=A.MENUID;

-- 右连接 RIGHT JOIN 即使左表中没有匹配，也从左表返回所有的行
SELECT COUNT(A.MENUID) FROM b2b_menu_info A RIGHT JOIN b2b_role_menu_info B ON B.MENUID=A.MENUID;

-- is not null 语句 is not 查等于空值 is not null 查不等于null值
SELECT * FROM test.test where address is not null;

-- max 最大值
SELECT MAX(CITY) FROM TEST ;

-- avg平均值
SELECT AVG(CITY) FROM TEST;

-- MIN最小值
SELECT MIN (CITY) FROM TEST;

-- GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
SELECT * FROM TEST GROUP BY CITY;

-- UCASE 函数把字段的值转换为大写。
SELECT ucase(CITY) FROM TEST ;

-- LCASE 函数把字段的值转换为小写。
SELECT lcase(CITY) FROM TEST;

alter table drop column ;

select，当字段为NULL是返回0否则返回1

IF(ISNULL(f.USER_NAME),0,1) AS NAME FROM users f

mysql 索引 es_area_info表名 AREANAME 字段名 BkBookNameIdx 索引名

CREATE INDEX BkBookNameIdx ON es_area_info(AREANAME);

行转列

select a.downloadDate,a.flight_date,a.flight_no,a.deptime,a.depAirport,a.arrAirport,'-' carrier,max(case when a.downloadTime=b.downloadTime then a.tatol_cap end)as cap,'' as clazz,
                    max(case when a.downloadTime='0830' then a.tatol_booked end) as tatol_booked_0830,max(case when a.downloadTime='0830' then a.tatol_bookrate end) as tatol_bookrate_0830,'' as clazz_status_0830,
                   max(case when a.downloadTime='0900' then a.tatol_booked end) as tatol_booked_0900,max(case when a.downloadTime='0900' then a.tatol_bookrate end) as tatol_bookrate_0900,'' as clazz_status_0900,
                   max(case when a.downloadTime='0930' then a.tatol_booked end) as tatol_booked_0930,max(case when a.downloadTime='0930' then a.tatol_bookrate end) as tatol_bookrate_0930,'' as clazz_status_0930,
                   max(case when a.downloadTime='1000' then a.tatol_booked end) as tatol_booked_1000,max(case when a.downloadTime='1000' then a.tatol_bookrate end) as tatol_bookrate_1000,'' as clazz_status_1000,
                   max(case when a.downloadTime='1030' then a.tatol_booked end) as tatol_booked_1030,max(case when a.downloadTime='1030' then a.tatol_bookrate end) as tatol_bookrate_1030,'' as clazz_status_1030,
                   max(case when a.downloadTime='1100' then a.tatol_booked end) as tatol_booked_1100,max(case when a.downloadTime='1100' then a.tatol_bookrate end) as tatol_bookrate_1100,'' as clazz_status_1100,
                   max(case when a.downloadTime='1130' then a.tatol_booked end) as tatol_booked_1130,max(case when a.downloadTime='1130' then a.tatol_bookrate end) as tatol_bookrate_1130,'' as clazz_status_1300,
                   max(case when a.downloadTime='1200' then a.tatol_booked end) as tatol_booked_1200,max(case when a.downloadTime='1200' then a.tatol_bookrate end) as tatol_bookrate_1200,'' as clazz_status_1200,
                   max(case when a.downloadTime='1230' then a.tatol_booked end) as tatol_booked_1230,max(case when a.downloadTime='1230' then a.tatol_bookrate end) as tatol_bookrate_1230,'' as clazz_status_1230,
                   max(case when a.downloadTime='1300' then a.tatol_booked end) as tatol_booked_1300,max(case when a.downloadTime='1300' then a.tatol_bookrate end) as tatol_bookrate_1300,'' as clazz_status_1300,
                   max(case when a.downloadTime='1330' then a.tatol_booked end) as tatol_booked_1330,max(case when a.downloadTime='1330' then a.tatol_bookrate end) as tatol_bookrate_1330,'' as clazz_status_1330,
                   max(case when a.downloadTime='1400' then a.tatol_booked end) as tatol_booked_1400,max(case when a.downloadTime='1400' then a.tatol_bookrate end) as tatol_bookrate_1400,'' as clazz_status_1400,
                   max(case when a.downloadTime='1430' then a.tatol_booked end) as tatol_booked_1430,max(case when a.downloadTime='1430' then a.tatol_bookrate end) as tatol_bookrate_1430,'' as clazz_status_1430,
                   max(case when a.downloadTime='1500' then a.tatol_booked end) as tatol_booked_1500,max(case when a.downloadTime='1500' then a.tatol_bookrate end) as tatol_bookrate_1500,'' as clazz_status_1500,
                   max(case when a.downloadTime='1530' then a.tatol_booked end) as tatol_booked_1530,max(case when a.downloadTime='1530' then a.tatol_bookrate end) as tatol_bookrate_1530,'' as clazz_status_1530,
                   max(case when a.downloadTime='1600' then a.tatol_booked end) as tatol_booked_1600,max(case when a.downloadTime='1600' then a.tatol_bookrate end) as tatol_bookrate_1600,'' as clazz_status_1600,
                   max(case when a.downloadTime='1630' then a.tatol_booked end) as tatol_booked_1630,max(case when a.downloadTime='1630' then a.tatol_bookrate end) as tatol_bookrate_1630,'' as clazz_status_1630,
                   max(case when a.downloadTime='1700' then a.tatol_booked end) as tatol_booked_1700,max(case when a.downloadTime='1700' then a.tatol_bookrate end) as tatol_bookrate_1700,'' as clazz_status_1700,
                   max(case when a.downloadTime='1730' then a.tatol_booked end) as tatol_booked_1730,max(case when a.downloadTime='1730' then a.tatol_bookrate end) as tatol_bookrate_1730,'' as clazz_status_1730,
                   max(case when a.downloadTime='1800' then a.tatol_booked end) as tatol_booked_1800,max(case when a.downloadTime='1800' then a.tatol_bookrate end) as tatol_bookrate_1800,'' as clazz_status_1800,
                   max(case when a.downloadTime='1830' then a.tatol_booked end) as tatol_booked_1830,max(case when a.downloadTime='1830' then a.tatol_bookrate end) as tatol_bookrate_1830,'' as clazz_status_1830,3 from #flightbooked a inner join (select DISTINCT depAirport,arrAirport ,flight_date,downloadDate,MAX(downloadTime) as downloadTime,flight_no from #flightbooked group by depAirport,arrAirport ,
               flight_date,downloadDate,downloadTime,flight_no) b on b.depAirport=a.depAirport and b.arrAirport=a.arrAirport and b.flight_date=a.flight_date and b.downloadDate=a.downloadDate and b.flight_no=a.flight_no where a.depAirport =SUBSTRING('PVG-CGQ',1,3) and a.arrAirport =SUBSTRING('PVG-CGQ',5,7) group by a.downloadDate,a.flight_date,a.flight_no,a.depAirport,a.arrAirport,a.deptime

mysql rownum用法

SELECT @rowno:=@rowno+1 as rowno,r.* from
(select * from course_item where courseId=47 order by sort asc,id asc) r ,(select @rowno:=0) t

update course_item a inner join
(
SELECT @rowno:=@rowno+1 as rowno,r.id from
(select * from course_item where courseId=47 order by sort asc,id asc) r ,(select @rowno:=0) t
) c
on a.id=c.id
set a.sort=c.rowno
where a.courseId=47

关联修改

UPDATE works wo
INNER JOIN works_teacher_bind wt on wt.worksId = wo.id
SET wo.createDate = wt.createDate

SQL 学习笔记

公告

标签

SQL 学习笔记

相关问题

公告

标签