sql中的join和inner join的区别

join等同于inner join等同于where

SQL ROUND() 函数

ROUND 函数用于把数值字段舍入为指定的小数位数。

语法

1	SELECT ROUND(column_name,decimals) FROM table_name

实例

我们拥有下面这个 “Products” 表：

Prod_Id ProductName Unit UnitPrice

1 gold 1000 g 32.35

2 silver 1000 g 11.56

3 copper 1000 g 6.85
现在，我们希望把名称和价格舍入为最接近的整数。

我们使用如下 SQL 语句：
1
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
结果集类似这样：

ProductName UnitPrice

gold 32

silver 12

copper 7

Prod_Id	ProductName	Unit	UnitPrice
1	gold	1000 g	32.35
2	silver	1000 g	11.56
3	copper	1000 g	6.85

ProductName	UnitPrice
gold	32
silver	12
copper	7

MySQL IFNULL函数简介

它接受两个参数，如果不是NULL，则返回第一个参数。否则，IFNULL函数返回第二个参数。

语法

1	IFNULL(expression_1,expression_2);

实例

1	SELECT IFNULL(1,0);

笛卡尔积

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。

SQL ORDER BY 关键字

ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

语法

1
2
3

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

演示数据库

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
+----+--------------+---------------------------+-------+---------+

实例

1 2	SELECT * FROM Websites ORDER BY alexa;

asc和desc的英文全称

asc：英文全称是ascend，含义如下：
desc：英文全称是descend，含义如下：

SQL之CASE WHEN用法详解

简单CASE WHEN函数：

1
2
3

CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END

等同于，使用CASE WHEN条件表达式函数实现：

1
2
3

CASE WHEN SCORE = 'A' THEN '优'
     WHEN SCORE = 'B' THEN '良'
     WHEN SCORE = 'C' THEN '中' ELSE '不及格' END

**场景：**有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀


SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE

注意：如果你想判断score是否null的情况写法应为：

1	CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END

SQL CONCAT()函数

SQL CONCAT函数用于连接两个字符串，形成一个字符串。

例子：

SQL> SELECT CONCAT('FIRST ', 'SECOND');
+----------------------------+
| CONCAT('FIRST ', 'SECOND') |
+----------------------------+
| FIRST SECOND               |
+----------------------------+

SQL BETWEEN 操作符

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

原始的表 (在实例中使用：)

Persons 表:

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing
4	Gates	Bill	Xuanwumen 10	Beijing

实例
1
2
3
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'
结果集：

Id LastName FirstName Address City

1 Adams John Oxford Street London

2 Bush George Fifth Avenue New York
实例
1
2
3
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Adams' AND 'Carter'
结果集：

Id LastName FirstName Address City

3 Carter Thomas Changan Street Beijing

4 Gates Bill Xuanwumen 10 Beijing

Id	LastName	FirstName	Address	City
3	Carter	Thomas	Changan Street	Beijing
4	Gates	Bill	Xuanwumen 10	Beijing

MYSQL 分组 group by 多个字段

1
2
3

SELECT Subject, Semester, Count(*)
FROM Subject_Selection
GROUP BY Subject, Semester

GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里

MySQL应用之CROSS JOIN用法简介教程

cross join简介

在MySQL中，当CROSS JOIN不使用WHERE子句时，CROSS JOIN产生了一个结果集，该结果集是两个关联表的行的乘积。

cross join用法

1 2	SELECT * FROM t1 CROSS JOIN t2;

注意：cross join的时候是不需要on或者using关键字的，这个是区别于inner join和join的

MySQL YEAR函数：获取年份

MySQL YEAR() 函数可以从指定日期值中来获取年份值。

语法格式如下：

1	YEAR(date);

实例:

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2019 |
+-------------+
1 row in set (0.00 sec)

MySQL IF函数：判断

语法结构如下：

1	IF(expr,v1,v2)

其中：表达式 expr 得到不同的结果，当 expr 为真是返回 v1 的值，否则返回 v2.

实例:

mysql> SELECT IF(1<2,1,0) c1,IF(1>5,'√','×') c2,IF(STRCMP('abc','ab'),'yes','no') c3;
+----+----+-----+
| c1 | c2 | c3  |
+----+----+-----+
|  1 | × | yes |
+----+----+-----+
1 row in set, 2 warnings (0.00 sec)

MySQL CURDATE和CURRENT_DATE函数：获取系统当前日期

MySQL 中 CURDATE() 和 CURRENT_DATE() 函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回，具体格式根据函数用在字符串或数字语境中而定。

实例:

mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;
+------------+----------------+------------------+
| CURDATE()  | CURRENT_DATE() | CURRENT_DATE()+0 |
+------------+----------------+------------------+
| 2017-04-01 | 2017-04-01     |         20170401 |
+------------+----------------+------------------+
1 row in set (0.03 sec)

难题分析

19、按各科成绩进行排序，并显示排名

select
	(@i := case when @pre_group_id = c_id then @i + 1 else 1 end) as rank,
	(@pre_group_id := c_id) as c_id,
	c_name,s_id,s_name,s_score
from (select @i := 0, @pre_group_id := 1) var
cross join (
	select c.c_id,c.c_name,s.s_id,s.s_name,s_score
	from score sc
	inner join student s on sc.s_id = s.s_id
	inner join course c on sc.c_id = c.c_id
	group by c.c_id,s.s_id
	order by c.c_id,s_score desc
) t1;

User-Defined Variables

User variables are written as @*var_name*, where the variable name var_name consists of alphanumeric characters, ., _, and $. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @my-var``).

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 |
+------+------+------+--------------------+

It is also possible to assign a value to a user variable in statements other than SET. (This functionality is deprecated in MySQL 8.0 and subject to removal in a subsequent release.) When making an assignment in this way, the assignment operator must be := and not =because the latter is treated as the comparison operator = in statements other than SET:

从mysql官方文档中找到的解释，@定义用户变量，定义方法有两种：

set运算符定义
在sql语句中用:=定义

定义用户变量能让用户在不同sql语句中传递数据。

25、查询各科成绩前三名的记录

内外关联

select c.c_id,c.c_name,s.s_id,s.s_name,s_score
from (
	select *
	from score sc
	where (
		select count(*)
		from score sc1
		where sc.c_id = sc1.c_id
		and sc.s_score < sc1.s_score
	) < 3
)
t1
inner join student s on t1.s_id = s.s_id 
inner join course c on t1.c_id = c.c_id 
order by c.c_id,s_score desc;

+------+--------+------+--------+---------+
| c_id | c_name | s_id | s_name | s_score |
+------+--------+------+--------+---------+
|    1 | 语文   |    1 | 赵雷   |      80 |
|    1 | 语文   |    3 | 孙风   |      80 |
|    1 | 语文   |    5 | 周梅   |      76 |
|    2 | 数学   |    1 | 赵雷   |      90 |
|    2 | 数学   |    7 | 郑竹   |      89 |
|    2 | 数学   |    5 | 周梅   |      87 |
|    3 | 英语   |    1 | 赵雷   |      99 |
|    3 | 英语   |    7 | 郑竹   |      98 |
|    3 | 英语   |    3 | 孙风   |      80 |
+------+--------+------+--------+---------+

47、查询本周过生日的学生

select *
from student
where
	datediff(concat(year(current_date()), date_format(s_birth, '-%m-%d')), current_date())
	between 0 and 7
or
	datediff(concat(year(current_date()) + 1, date_format(s_birth, '-%m-%d')), current_date())
	between 0 and 7;

MySQL DATEDIFF() 函数

DATEDIFF() 函数返回两个日期之间的天数。

语法

1	DATEDIFF(date1,date2)

实例

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
1
2
3
4
5
6
7
8
9

   结果：

   | DiffDate |
   | :------- |
   | 1        |

2. ```sql
   SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate


结果：

| DiffDate |
| :------- |
| -1       |

MySQL DATE_FORMAT() 函数

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法

1	DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。

格式	描述
%m	月，数值(00-12)
%d	月的天，数值(00-31)