sql基本语法和一些实例

基本知识点：

mysql简介：

mysql优势：

mysql启动与停止：

net start mysql数据名称

net stop mysql数据名称

连接mysql数据库

mysql -u 用户名 -p

退出mysql

exit 或 quit

mysql关键词

1，创建数据库并且设置所以字符集和排序规则

CREATE DATABASE test CHARACTER SET 'utf8' COLLATE 'utf8_bin';

2, 修改数据库

ALTER DATABASE test CHARACTER SET 'utf8' COLLATE 'utf8_bin';

3, 删除数据库

DROP DATABASE test

表的简介：

4，创建表并且写两个字段

CREATE TABLE employee  (
  id int(0) NOT NULL,
  Salary decimal(10, 2) NULL,
  PRIMARY KEY (id)
);

5，查询有那些数据库

show databases;

6，查询数据库中有哪些表

show tables;

7，显示表结构

DESC 表名

DESC 表名 列名

8，删除表

DROP TABLE 表名

9,修改表

10，重命名表

11，复制表

mysql约束条件

字段的约束

• 主键约束
  • 
  • 
  • 表的完整性约束
    
  • 列的完整性约束
    
    
    
• 外键约束
  • 
  • 
  • 

12, 表的数据插入

13, 表的数据修改

14, 表的数据删除

15, 表的数据查询

参考：https://www.cnblogs.com/chenduzizhong/p/9590741.html

基本操作点：

1, 组合两个表

select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId

知识点： 表A left join 表B on 表A=表B的相同字段(表A的主键=表B的外键) —多表查询，连接查询
关键词：无论person是否有地址

2， 第二高的薪水

select ifnull((select Distinct Salary from Employee order by Salary desc limit 1, 1),null) as secondHighestSalary

知识点：
ifnull((‘查询语句‘),null) —如果查询语句没有找到合适的结果就返回null
distinct — 查询结果去除重复
order by 字段 desc —查询到的结果按‘字段’降序排序
limit 1,1 —(限制输出为从1开始输出1个，查询结果从0开始)

关键词：第二高

3， 第N高的薪水

create function getNthHighestSalary(n int) returns int
begin
    set n = n-1;
    return(
        select distinct Salary from Employee group by Salary order by Salary desc limit n,1
    );
end

知识点：
存储函数的创建

create function 函数名(参数 参数类型) returns 返回的参数类型
begin
	set n = n-1;
	return(
		函数语句(这个函数语句返回函数要输出的结果并且类型在上面指定了)
	)；
end

4，sql语句实现分数排名

select a.Score as 'Score',(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'Rank'
 from Scores a order by a.Score desc

知识点：
1，分表查询
from Scores a
from Scores b
出现这样的就是有分表查询了，一般在select出现的表为当前表，在条件中出现的表为对照表
当前表是当前对象，对照表是整个全表对象，同一个个比对当前对象和全表对象来得出结果

2， count函数(统计)的使用

select count(distinct b.Score) from Scores b where b.Score >= a.Score

count函数是局部 --> 全局
首先找到b表的第一条数据的Score, 然后让这个数据的Score对照相同参照表a全部数据的Score ，得出这个数据Score大于等于全表数据Score的个数(b表当前用的那个数据，b表相同对应a表全部数据)

3，排名一般用统计函数count来做

5，至少连续出现三次的数字

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

知识点：多表查询（但是表是同一个的多表查询）
建立自己的对照表：
Logs l1,
Logs l2,
Logs l3
相互连续且相等：
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num

6，超过经理收入的员工

select a.Name as Employee 
from Employee a, Employee b 
where a.ManagerId = b.Id and a.Salary >b.Salary;

知识点：多表查询（但是表是同一个的多表查询）
建立以自己为目标的对照表：
from Employee a, Employee b
where a.ManagerId = b.Id and a.Salary >b.Salary;

7，查找重复的电子邮箱

以此作为临时表，我们可以得到下面的解决方案。

select Email from (select Email, count(Email) as num from Person group by Email) as static where num >1

select Email
from Person
group by Email
having count(Email) > 1;

知识点：
1，count()的使用
2，group by 的使用
3， count()搭配group by()的使用

8， 部门工资最高的员工

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	);

知识点：
结果要在两个表中得到
1， 用join on 连接表
2， 用from Employee e， Department d(这个可能不信，因为会分开了结果)
符合条件可以用 = ,也可以用 in

思路：
先把所有的结果都查询到，然后用其中的几个字段选出符合条件的

错误的示范：

select d.Name as 'Department',e.Name as 'Employee',e.Salary as 'Salary' 
from Employee e, Department d
where (e.DepartmentId ,e.Salary) in (select DepartmentId, max(e.Salary) as'Salary' from Employee e group by DepartmentId); 

因为员工表和部门表分开查询，会出现在员工表查到A员工，但是因为没有指定部门，所以会在部门1中有A员工
在部门2中同样有A员工这样的结果

解决方法：增加条件让员工表和部门表对接起来(类似于正确答案中的join on条件)

select d.Name as 'Department',e.Name as 'Employee',e.Salary as 'Salary' 
from Employee e, Department d
where (e.DepartmentId ,e.Salary) in (select DepartmentId, max(e.Salary) as'Salary' from Employee e group by DepartmentId) and e.DepartmentId=d.Id; 

增加了添加 e.DepartmentId=d.Id避免一个员工在两个部门出现

9，部门工资前三高的所有员工

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        );

理解这个判断条件：

WHERE 3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        );

同一部门内 ，第一高工资 比他高的工资 个数为0 ,第二高工资 ,比他的高的 工资个数为1，同理 第三高为2，因此 只要查出 在同一部门内 其他的工资>该员工的薪资 的个数小于3 那么他就为同一部门的前三高工资 ，即为子查询里面的条件

10， 有趣的电影

select * from cinema
where description != 'boring' and mod(id,2)=1 
order by rating desc

知识点：
mod(N,M)函数的使用：此函数返回N除以M余值
mod(id,2)=1 --判断是奇数