SQL的一些常用语法

SQL的一些常用语法

link:https://blog.csdn.net/weixin_39609623/article/details/95043370

数据空间（数据文件夹） : database
表（数据文件） : table
在数据库中数据空间和表均不可以重名

进入mysql
mysql -u root -p 回车
输入密码

显示数据空间(显示数据文件夹)
show databases;

选择数据空间
use database名称;

显示数据文件
show tables;

创建数据空间
create database 名称;

创建表
create table 名称(列名1 类型1,列名2 类型2,…列名N 类型N);

查看表的结构
desc 表名;

删除表
drop table 名称;

删除数据空间
drop database 名称;

插入数据
1.insert into 表名 values(列值1,列值2,…列值N);
必须与表内数据类型对其

2.insert into 表名(列名1,列名2,…列名N) values (列值1,列值2,…列值N);

删除数据
delete from 表名; 清空表内所有数据

条件删除语句
delete from 表名 where 列名 = 列值;

更改数据
update 表名 set 列名 = 列值; 修改表内所有数据

条件修改语句
update 表名 set 列名 = 列值 where 列名x = 列值x;

查询数据
select 列名1,列名2,…列名N from 表名;

练习用查询语句:select * from 表名;

条件查询语句
select 列名1，列名2，…列名N from 表名 where 列名X = 列值X;

常用比较运算符 :

< >= <= = <>(mysql中可用!=)
and(mysql中可用&&) or(mysql中可用||)

特殊比较运算符
is is not 用于判断是否为NULL
列名 between 区间的开始 and 区间的结束
用于判断该列是否在区间内 返回一个[]的区间

in (值1，值2，…值N) 用于判断是否在该括号的集合内
一般不推荐使用，因为在简单的数据表结构中
特别的浪费效率，因为遍历次数过多

注：所有的比较运算符都在where语句后使用

常用的算数运算符

• • • / %

查询出每个员工的编号，姓名和年薪(不算奖金，15月工资起算)
<select empno,ename,salary*15 from emp;/>

查询出每个员工的编号，姓名和最大月工资
select empno,ename,salary + ifnull(bonus,0) from emp;

数据库中NULL有四种特性 :
1.凡是与NULL值做运算的结果 都为NULL
2.NULL值 不会与普通的比较运算符运算 判断列值是否为NULL
通过 is 判断等于空 is not判断不等于空
3.被组函数默认忽略 不会参与函数计算
4.在mysql中默认比所有的值都小，oracle中默认比所有值都大

普通函数
ifnull(列名，new值) 将该列中的所有NULL值转换为 new值
注：oracle下没有ifnull 但是有使用方法完全一致的 nvl(列名,new值)

lower(列名) 将该列中的值转换为小写
upper(列名) 将该列中的值转换为大写
注：在mysql下条件查询时，会默认将所有值转换为lower
但在oracle下不会被转换，所以oracle下必须手动添加lower或upper

别名机制
可以在任意列名或者表名重新定义一个别名 用于方便使用
as 新名字
注：as大部分时间略写

查询出每个员工的编号，姓名，基本工资，最大月薪，最大年薪
select e.empno no,e.ename name,e.salary sal,
e.salary+ifnull(e.bonus,0) month_sal,
(e.salary + ifnull(e.bonus,0))*15 year_sal from emp e;

查询出工资在5000到10000之间的员工的编号，姓名和工资
select empno,ename,salary from emp
where salary between 5000 and 10000;

查询出工资是5000，8000,15000的员工的编号，姓名和工资
select empno,ename,salary from emp
where salary in (5000,8000,15000);

组函数 :
直接与普通列一起查询 会出现数据显示有误的情况
如果组函数和普通列一起被查询，则必须分组
如果只有组函数的查询，则不需要分组
count(列名) : 求该列非NULL值的个数
max(列名) : 求该列最大值
min(列名) : 求该列最小值
sum(列名) : 求该列总和
avg(列名) : 求该列平均值
group by 列名 :
被分组的列，一般都是有重复数据的列
组函数和普通列一起被查询时，一般都直接按照该普通列分组

查询出公司有多少个员工
select count(empno) count_emp from emp;

查询出每个部门的员工个数
select count(empno) count_emp,deptno from emp group by deptno;

查询出每个职位的员工个数
select count(empno) count_emp,job from emp group by job;

查询出公司的最高工资、最低工资、公司每个月工资的总和和平均工资
select max(salary) max_sal,min(salary) min_sal,
sum(salary) sum_sal,avg(salary) avg_sal from emp;

查询出公司的每个月实际平均工资
select avg(salary+ifnull(bonus,0)) avg_sal from emp;

查询每个部门的最高工资和最低工资
select max(salary) max_sal,min(salary) min_sal,deptno from emp
group by deptno;

having字句 用于在分组后的判断条件
如果在没有分组的情况下使用，会默认按照第一列分组
极大的占用系统内存，所以只有在分组后需要的情况下使用

分组查询之后 默认语法不可以使用where语句
select avg(salary) avg_sal,deptno from emp
group by deptno having avg_sal > 5000;

查询公司的所有员工的编号，名称和工资 按照工资从小到大的顺序排序
select empno,ename,salary from emp order by salary;
注：order by 列名 排序后默认按照从小到大 关键字 asc
逆序 排序后添加desc

distinct 去重
写在select之后 列名之前

limit
可以选定显示哪部分的查询结果
在limit后面添加行数和显示的个数

模糊查询 like
需要两个通配符配合使用
_ 每一个_都代表一个字符 直到匹配到类似的内容
% 放在字符前 用于表示以该字符为结尾
放在字符后 用于表示以该字符为开始
字符两边都添加 用于表示显示全部相关内容

复制一个表
create table 表名1 select * from 表名2 完全复制一个表结构和其内容

子查询语句:
查询出比Galen工资高的员工的编号，姓名和工资
select empno,ename,salary from emp where salary >
(select salary from emp where ename = ‘Galen’);

查询出比平均工资低的员工的编号，姓名和工资
select empno,ename,salary from emp where salary <
(select avg(salary) from emp) order by salary;

每个部门拿最高薪水的是谁？
select empno,ename,salary,deptno from emp where (deptno,salary) in
(select deptno,max(salary) from emp group by deptno);

哪个部门的人数比部门30的人数多？
select deptno,count(empno) count_emp from emp group by deptno having count_emp >
(select count(empno) from emp where deptno = 30);
注：在mysql下，使用having时可以使用from之前声明的别名

哪个部门的平均薪水比部门20的平均薪水高？
select deptno,avg(salary) avg_sal from emp group by deptno having avg_sal >
(select avg(salary) from emp where deptno = 20);

查询出比所有的Teemo工资高的员工编号，姓名和工资
select empno,ename,salary from emp where salary >
all(select salary from emp where ename = ‘Teemo’);

查询出比任意的Teemo工资高的员工编号，姓名和工资
select empno,ename,salary from emp where salary >
any(select salary from emp where ename = ‘Teemo’);

谁和’Lacus’同部门？列出除了Lacus的人
select empno,ename from emp where deptno =
any(select deptno from emp where ename = ‘Lacus’)
and ename <> ‘Lacus’;

谁是’Lacus’的下属？
select empno,ename from emp where mgr =
any(select empno from emp where ename = ‘Lacus’);

关联子查询 :
查询出比自己部门平均工资低的员工编号和姓名
select e1.empno,e1.ename,e1.deptno from emp e1
where e1.salary <
(select avg(e2.salary) from emp e2
where e1.deptno = e2.deptno) order by deptno desc;

exists :
一般情况下用于替换in而使用的关键字，必定是关联子查询的方式
谁是’Lacus’的下属？
select empno,ename from emp where mgr in
(select empno from emp where ename = ‘Lacus’);

select empno,ename from emp e1 where exists
(select 1 from emp e2 where e1.mgr = e2.empno and ename = ‘Lacus’);
谁和’Lacus’同部门？列出除了Lacus的人
select empno,ename,deptno from emp where deptno in
(select deptno from emp where ename = ‘Lacus’) and ename <> ‘Lacus’;

select empno,ename,deptno from emp e1 where exists
(select 1 from emp e2 where e1.deptno = e2.deptno
and ename = ‘Lacus’) and ename <> ‘Lacus’;

哪些人是其他人的经理？
select empno,ename from emp where empno in
(select mgr from emp);

select empno,ename from emp where empno =
any(select mgr from emp);

select empno,ename from emp e1 where exists
(select 1 from emp e2 where e1.empno = e2.mgr);

exists存在 not exists 不存在
哪些人不是其他人的经理
select empno,ename from emp e1 where not exists
(select 1 from emp e2 where e1.empno = e2.mgr);

哪些部门没有员工？
select deptno,dname from dept where deptno not in
(select deptno from emp where deptno is not null);

select deptno,dname from dept d where not exists
(select 1 from emp e where d.deptno = e.deptno);

每个部门拿最高薪水的是谁？
select empno,ename,salary,deptno from emp where (deptno,salary) in
(select deptno,max(salary) from emp group by deptno);

select empno,ename,salary,deptno from emp e1 where exists
(select 1 from emp e2 group by deptno
having max(salary) = e1.salary and e1.deptno = e2.deptno);

查询出每个职位的最高工资的员工信息
select empno,ename,salary,job from emp
where (salary,job) in
(select max(salary),job from emp group by job
having job is not null);

select empno,ename,salary,job from emp e1 where exists
(select 1 from emp e2 group by job
having e1.salary = max(salary) and e1.job = e2.job);

dual 虚表
用于存储数据库中的基本常量，与基本函数
可以通过select Value from dual的方式访问任意Value
在mysql中可以略写from dual 但大部分数据库不支持该功能
mysql中的dual 不可以查看结构 也不可以通过select * 的方式访问

时间函数 :
在mysql下
now() : 年月日 时分秒 yyyy-mm-dd hh:mm:ss
curdate() : 年月日 yyyy-mm-dd
curtime() : 时分秒 hh:mm:ss
to_days(列名) : 将时间转换为天数
datediff(列名1，列名2) : 将两个时间转换为天数，返回它们的差
在oracle下
没有对应的时间函数 全部通过一个列实现 该列名称：sysdate

查询出每个员公的工作天数
select empno,ename,datediff(curdate(),hiredate) work_day from emp;

查询出今年工作超过10个月的员工
select empno,ename,work_day from (select empno,ename,
datediff(curdate(),hiredate)%365 work_day
from emp) e where e.work_day > 300;

表间关联查询 ：（多表查询）
查询出所有员工的编号，姓名，部门名称，工作地点
表1 join 表2 on 表1.列名 = 表2.列名
默认情况下 在join之前添加了inner关键字
on后的条件 是两个表公有的相同意义不同数据的列

inner : 内联结

查询出公司所有在编的员工编号，姓名，部门名，工作地点

select empno,ename,dname,location from emp e
join dept d on e.deptno = d.deptno;

left outer : 左外连接

查询出公司所有员工编号，姓名，部门名，工作地点
select empno,ename,dname,location from emp e
left outer join dept d on e.deptno = d.deptno;

right outer : 右外连接

查询出公司所有部门的员工编号，姓名，部门名，工作地点
select empno,ename,dname,location from emp e
right outer join dept d on e.deptno = d.deptno;

full outer : 全外连接
注：oracle下可用 mysql不可用
只可以通过union的方式实现类似于full outer的功能

查询出公司的所有员工信息与部门信息
select empno,ename,dname,location from emp e
left outer join dept d on e.deptno = d.deptno
union
select empno,ename,dname,location from emp e
right outer join dept d on e.deptno = d.deptno;

超过两个表以上的关联查询 只需要在on的条件后 继续join即可
A join B on A.id = B.id join C on A(B).id = C.id join …

自链接
查询出每个员工的姓名和其上司的姓名
select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;

mysql 分段统计数据
一个简单的分段统计的问题：student 表{id,name,score} 字段，统计各个分数段的人数。规则：60以下不及格，60-80良，80-100优。

SELECT
sum(CASE when score<60 then 1 else 0 end) AS ‘不及格’,
sum(CASE when score>=60 and score<=80 then 1 else 0 end) AS ‘良’,
sum(CASE when score>80 then 1 else 0 end) AS ‘优’
FROM student;

SELECT
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score<60) 不及格,
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score BETWEEN 60 AND 80) 良,
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score>80) 优
FROM stuscore a GROUP BY a.subject;

修改的用户都以root为列。
一、拥有原来的myql的root的密码；

方法一：
在mysql系统外，使用mysqladmin

mysqladmin -u root -p password “test123”

Enter password: 【输入原来的密码】

方法二：
通过登录mysql系统，

mysql -uroot -p

Enter password: 【输入原来的密码】
mysql>use mysql;
mysql> update user set password=passworD(“test”) where user=‘root’;
mysql> flush privileges;
mysql> exit;

二、忘记原来的myql的root的密码；

首先，你必须要有操作系统的root权限了。要是连系统的root权限都没有的话，先考虑root系统再走下面的步骤。
类似于安全模式登录系统，有人建议说是pkill mysql，但是我不建议哈。因为当你执行了这个命令后，会导致这样的状况：
/etc/init.d/mysqld status
mysqld dead but subsys locked
这样即使你是在安全模式下启动mysql都未必会有用的，所以一般是这样/etc/init.d/mysqld stop，如果你不幸先用了pkill，那么就start一下再stop咯。

mysqld_safe --skip-grant-tables &

&，表示在后台运行，不再后台运行的话，就再打开一个终端咯。

mysql

mysql> use mysql;
mysql> UPDATE user SET password=password(“test123”) WHERE user=‘root’;
mysql> flush privileges;
mysql> exit;
##本来mysql是不分大小写的，但是这个是修改的mysql中的mysql数据库的具体的值，要注意到。

构建连续日期

link.