sql数据命令Ⅱ「终于解决」

欢迎大家来到IT世界,在知识的湖畔探索吧!

各个关键字的书写顺序

select 查询的字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;

select[distinct]   //查询[去重]
from   //需要从哪个数据表检索数据
join（如left join）   //关联
on  //关联关系
where  //条件
group by  //分组
having   //聚合函数条件
union  //合并
order by   //排序
limit  //分页

欢迎大家来到IT世界,在知识的湖畔探索吧!

子查询(嵌套查询)

欢迎大家来到IT世界,在知识的湖畔探索吧!//查询工资大于2号部门平均工资的员工信息
select avg(sal) from emp where dept_id=2;select * from emp where sal>(select avg(sal) frziom emp where dept_id=2);
//查询工资高于程序员最高工资的员工信息
select max(sal) from emp where job="程序员";select * from emp where sal>(select max(sal) from emp where job="程序员");
//查询工资最高的员工信息
select max(sal) from emp;select * from emp where sal=(select max(sal) from emp);
//查询和孙悟空相同工作的员工信息
select job from emp where name="孙悟空";select * from emp where job=(select job from 
  emp where name="孙悟空") and name!="孙悟空";

关联关系 （指创建的表和表之间存在的业务关系）

有哪几种关系?

• 一对一: 有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据

一对一

• 一对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据

一对多

多对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据

多对多

表和表之间如何建立关系 ,通过外键字段建立关系

1. 一对一: 在任意表中添加一个建立关系的字段指向另外一张表的主键 .
2. 一对多: 在多的表中添加建立关系的字段(外键) 指向另外一张表的主键 .
3. 多对多: 需要创建一个单独的关系表,里面至少包含两个字段分别指向另外两个表的主键.

关联查询

1. 同时查询多张表数据的查询方式称为关联查询
2. 关联查询包括: 等值链接, 内连接和外连接

• 关联查询之等值链接

格式: select * from A,B where 关联关系

//查询工资高于2000的员工姓名和对应的部门名 
select e.name,d.name,salfrom emp e,dept d where e.dept_id=d.id and sal>2000;

• 关联查询之内连接

格式: select * from A join B on 关联关系

欢迎大家来到IT世界,在知识的湖畔探索吧!//查询工资高于2000的员工姓名和对应的部门名 
select e.name,d.name,sal from emp e join dept d on e.dept_id=d.id where sal>2000;

• 关联查询之外连接

1. 等值链接和内连接查询到的都是两张表的交集数据
2. 外连接查询的是一张表的全部和另外一张表的交集数据

格式: select * from A left/right join B on 关联关系

//查询所有员工姓名和对应的部门名
select e.name,d.name from emp e left join dept d on e.dept_id=d.id;
//查询所有部门的名称,地点和对应的员工姓名和工资 
select d.name,loc,e.name,salfrom emp e right join dept d on e.dept_id=d.id;

几种关联关系

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

INNER JOIN 关键字

在表中存在至少一个匹配时返回行【注】INNER JOIN 与 JOIN 是相同的。

内联关系-交集

LEFT JOIN 关键字

从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL【注】在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN

左连接

RIGHT JOIN 关键字

从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。【注】在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

右连接

FULL OUTER JOIN 关键字

1. FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
2. FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

并集关联

【注意】MySQL中不支持 FULL OUTER JOIN

如何查询多对多表中的数据

//查询每个老师对应的学生
select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid;
//查询苍老师的学生都有谁?
select s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where t.name="老师";
//查询小明的老师是谁?
select t.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where s.name="小明";

关联查询总结:

1. 如果需要同时查询多张表的数据使用关联查询
2. 关联查询包括:等值链接,内连接和外连接
3. 等值链接和内连接查询的是两个表的交集数据, 推荐使用内连接
4. 如果需要查询一张表的全部和另外一张表的交集时 使用外连接,只需要掌握左外即可,因为表的位置可以交换

合并结果集(UNION)

• UNION

下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）

• UNION ALL

下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的country（也有重复的值）

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2; 

约束(constraint)

1. SQL 约束用于规定表中的数据规则。
2. 如果存在违反约束的数据行为，行为会被约束终止。
3. 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

• NOT NULL – 指示某列不能存储 NULL 值。
• UNIQUE – 保证某列的每行必须有唯一的值。
• PRIMARY KEY – NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY – 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK – 保证列中的值符合指定的条件。
• DEFAULT – 规定没有给列赋值时的默认值。

CREATE TABLE Orders
(
Id int NOT NULL,//不能为空
OrderNo int NOT NULL,
P_Id int  DEFAULT '0', //默认值
PRIMARY KEY (O_Id), //主键
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) //外键
) ;

索引

CREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

1. 您可以在表中创建索引，以便更加快速高效地查询数据。
2. 用户无法看到索引，它们只能被用来加速搜索/查询。
3. 【注】更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建引。

CREATE INDEX index_name ON table_name (column_name) ;

CREATE UNIQUE INDEX 语法

1. 在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。

Creates a unique index on a table. Duplicate values are not allowed
//【注】用于创建索引的语法在不同的数据库中不一样。因此，检查您的数据库中创建索引的语法。

日期函数

Text类型

Number类型

sql语句

//1)按天统计：
select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;
//2)按周统计：
select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;
//3)按⽉统计:
select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths
//当年第一天：
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);
//当年最后一天：
SELECT concat(YEAR(now()),’-12-31’);
//当前week的第一天：
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);
//当前week的最后一天：
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY);
//前一week的第一天：
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY);
//前一week的最后一天：
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY);
//前两week的第一天：
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY);
//前两week的最后一天：
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY);
//当前month的第一天：
SELECT concat(date_format(LAST_DAY(now()),’%Y-%m-’),‘01’);
//当前month的最后一天：
SELECT LAST_DAY(now());
//前一month的第一天：
SELECT concat(date_format(LAST_DAY(now() - interval 1 month),’%Y-%m-’),‘01’);
//前一month的最后一天：
SELECT LAST_DAY(now() - interval 1 month);
//前两month的第一天：
SELECT concat(date_format(LAST_DAY(now() - interval 2 month),’%Y-%m-’),‘01’);
//前两month的最后一天：
SELECT LAST_DAY(now() - interval 2 month);
//当前quarter的第一天：
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),’%Y-%m-’),‘01’);
//当前quarter的最后一天：
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month);

学习记录，如有侵权请联系删除