SQL
增删改查
对数据库有修改的操作是：增删改
增 insert into 表名 values();
删 delete from...where();
改 update 表名 set...新值 where(旧值);
对数据库没有修改的操作是：查（最主要的SQL）
*----------------------------------------------------------------------*
1.查询SQL的结构：
select...（输出） from...（获取数据）
where...（过滤）group by...（分组）
having....（过滤）order by...（排序）
limit...（限定个数）;
2.执行（写）SQL顺序
1）from 2）where 3）group by 4)select 5)having 6)order by 7)limit
3.二维数据，一般以表格形式呈现：行：同一个事物的内容 列：事务的属性。
*----------------------------------------------------------------------*
4.SQL分组group by
1）数据分组时按照group by的属性同类别数据为一组
select * 的结果为每一组的第一行（即以每一组的第一行作为每组的标识）
2）select (聚集函数) from...group by...
聚集函数：count、sum、max、min、avg、group-concat
count(1)是指增加新的一列计数，每一行赋值为1，然后计数有多少行。
sum(1)是指增加新的一列计数，每一行赋值为1，然后计数加和
所以count(1)=sum(1),sum(2)=2count(2)。
区别：count(1)和count(name)的区别：
如果name中有NULL，那么count不计入null值。count(1)计入全部行数
但上述两种都不去重，若要记录不重复值，用count(distinct name)
*----------------------------------------------------------------------*
5.having....（过滤）order by...（排序）limit...（限定个数）
having为select之后的数据做过滤（groupby分组之后），where为from中获取的数据做过滤。
where后面不能用聚集函数作为筛选，因为聚集函数在groupby之后。
order by可以给聚集函数排序，因为其在select后面操作。asc正序，desc反序。
limit a,b;从a开始，取b个数.注意0代表第1行。
select * from yaya where 1=1;因为1=1永远为真，所以相当于取全表数据。
*----------------------------------------------------------------------*
6.case when:
case when 表达式
then 输出
when 表达式
then 输出
else 输出
end
select id,case when grade=98 then 99 when grade=99 then 100 end from yaya;
+----+-------------------------------------------------------+
| id | case when grade=98 then 99 when grade=99 then 100 end |
+----+-------------------------------------------------------+
| 2 | 99 |
| 4 | 100 |
+----+-------------------------------------------------------+
*case when与聚集函数结合 实战：行列转换*
*建表*
CREATE TABLE scores(name char(3),stage char(3),score int);
INSERT INTO scores VALUES('A','基础',90),('B','基础',91),('C','基础',92),
('A','爬虫',80),('B','爬虫',81),('C','爬虫',82),
('A','SQL',70),('B','SQL',71),('C','SQL',72);
mysql> select * from scores;
+------+--------+-------+
| name | stage | score |
+------+--------+-------+
| A | 基础 | 90 |
| B | 基础 | 91 |
| C | 基础 | 92 |
| A | 爬虫 | 80 |
| B | 爬虫 | 81 |
| C | 爬虫 | 82 |
| A | SQL | 70 |
| B | SQL | 71 |
| C | SQL | 72 |
+------+--------+-------+
9 rows in set (0.00 sec)
*思路为在后面增加新列，即为要展现的基础、爬虫、SQL三列，
有值赋值，无值赋NULL，然后按姓名分组选取每列每组中的最大值。*
select name,
max(CASE WHEN stage='基础' THEN score ELSE NULL END) as ‘基础’,
max(CASE WHEN stage='爬虫' THEN score ELSE NULL END) as '爬虫',
max(CASE WHEN stage='SQL' THEN score ELSE NULL END) as 'SQL'
FROM scores
GROUP BY name;
+------+--------------+--------+------+
| name | ‘基础’ | 爬虫 | SQL |
+------+--------------+--------+------+
| A | 90 | 80 | 70 |
| B | 91 | 81 | 71 |
| C | 92 | 82 | 72 |
+------+--------------+--------+------+
3 rows in set (0.00 sec)
*-------------------------------------------------------------------------*
7. join...on..
1)select...from 表1 join 表2 on...
只有JOIN时，on和where条件无区别，可以相互替代。
2)LEFT JOIN／RIGHT JOIN 必须有on条件
A RIGHT JOIN B = B LEFT JOIN A
多表链接:A JOIN B ON...JOIN C ON...JOIN D ON...
两行之间的对比需要自己JOIN自己放到同一行里
3)join当中有from子查询必须用as作别名，再做join
*-------------------------------------------------------------------------*
8.半连接
子查询中有引用副查询的表信息：
select * from a1 where cc=(select cc from b2 where b2.xx=b1.xx)
create table stu(id int,name varchar(10),classid int);
insert into stu values(1,'A',1),(2,'B',1),(3,'C',2),(4,'D',2),(5,'E',3);
mysql> SELECT * FROM stu;
+------+------+---------+
| id | name | classid |
+------+------+---------+
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | C | 2 |
| 4 | D | 2 |
| 5 | E | 3 |
+------+------+---------+
5 rows in set (0.00 sec)
找出每个class当中id最大的行。
1）使用groupby方法
select max(id),classid from stu group by classid;
mysql> select max(id),classid from stu group by classid;
+---------+---------+
| max(id) | classid |
+---------+---------+
| 2 | 1 |
| 4 | 2 |
| 5 | 3 |
+---------+---------+
3 rows in set (0.00 sec)
如果想选name，直接写就会出错
select max(id),name,classid from stu group by classid;
mysql> select max(id),name from stu group by classid;
ERROR 1055 (42000): Expression
使用以下方法：
mysql> select * from stu where id in
(select max(id) from stu group by classid);
+------+------+---------+
| id | name | classid |
+------+------+---------+
| 2 | B | 1 |
| 4 | D | 2 |
| 5 | E | 3 |
+------+------+---------+
3 rows in set (0.00 sec)
2）使用半连接的方法
select *
from stu s1 where id=
(select max(id) from stu s2 where classid=s1.classid)
+------+------+---------+
| id | name | classid |
+------+------+---------+
| 2 | B | 1 |
| 4 | D | 2 |
| 5 | E | 3 |
+------+------+---------+
3 rows in set (0.00 sec)
思路：判断主查询与副查询条件下的子查询的结果。
例如主查询依次取行，id=1时，classid变为1，
子查询变为select max(id) from stu s2 where classid=1，
子查询结果为max(id)=2，并不等于主查询中的id=1,所以不输出。
半连接例题：选择每个部门工资最高的员工
create table employee(id int primary key,name varchar(10),salary int,departmendid int);
insert into employee values(1,'Joe',70000,1),(2,'Henry',80000,2),(3,'Sam',60000,2),(4,'Max',90000,1);
mysql> select * from employee;
+----+-------+--------+--------------+
| id | name | salary | departmendid |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
4 rows in set (0.00 sec)
create table department(id int,name varchar(10));
insert into department VALUES(1,'IT'),(2,'Sales');
mysql> select * from department;
+------+-------+
| id | name |
+------+-------+
| 1 | IT |
| 2 | Sales |
+------+-------+
2 rows in set (0.00 sec)
select department.name as department,employee.name as employee,employee.salary as salary
from department,employee where
employee.salary in (select max(salary) from employee where departmendID=department.id);
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
2 rows in set (0.00 sec)
半连接的方法
select department.name as department,employee.name as employee,employee.salary as salary
from department join employee
on department.id=employee.departmendid
where employee.salary in
(select max(salary) from employee
group by departmendid);
+------------+----------+--------+
| department | employee | salary |
+------------+----------+--------+
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
2 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------
9.查询属性中含有某个字符x的方法
like 'x%':以x字符开头 like '%x':以x字符结尾 like '%x%':含有x字符
以上述为例，查询employ.name中带a字符的
select name from employee where name like '%a%';
+------+
| name |
+------+
| Sam |
| Max |
+------+
2 rows in set (0.00 sec)
网友评论