1、DML核心CRUD增删改查
缩写全称和对应 SQL:
-
* DML 数据操纵语言:Data Manipulation Language
-
* Create 增加:insert
-
* Retrieve 查询:select
-
* Update 更新:update
-
* Delete 删除:delete
2、SQL基本查询
2.1 常用SQL总结
-
* 基本查询:select * from table_name
-
* 字段查询:select fileds from table_name
-
* 条件查询:select * from table_name where a=1
-
* 排序:select * from table_name order by b desc
-
* 分页:select * from table_name order limit 10 offset 0
-
* 去重:select distinct fileds from table_name
2.2 实操演示
现在有这样一个公司部门人员各个信息的数据库,包含了如下几个表:
departments 部门表字段:
dept_emp 雇员部门表字段:
dept_manager领导部门表字段:
employees雇员表字段:
salaries薪资表字段:
titles岗位表字段:
基本查询-查询departments表的所有数据
select * from departments;
字段查询-查询employees表里所有的雇佣日期hire_date
select hire_date from employees;
条件查询-查询employees表里所有男性员工M
select * from employees where gender='M';
排序-查询departments表里的所有部门并按部门序号进行从小到大排序展示
select * from departments order by dept_no;
若是想要按部门序号从大到小进行排序的话就可以使用DESC:
select * from departments order by dept_no desc;
分页-将departments表按部门序号进行从小到大排序后取前4个
select * from departments order by dept_no limit 4;
再取偏移量offset为3后的前4个
select * from departments order by dept_no limit 4 offset 3;
去重-现在想知道titles表中的岗位头衔有多少种,就需要对title进行去重处理
select distinct title from titles;
3、其他SQL条件查询Where
基本条件查询在上述已经说明:
select * from table_name where a=1
其余条件查询SQL:
-
* and or not
-
* 相等: =
-
* 数字比较:等于= 大于> 小于< 不等<>
-
* LIKE通配:% _
-
* BETWEEN AND
-
* IN
实操演示:
LIKE通配-现在要取出employees里所有名字为C开头的人
select * from employees where first_name like 'C%';
再取employees里所有名字为C开头,第3个字母为y的人
select * from employees where first_name like 'C_y%';
BETWEEN AND-查询employees中字母顺序显示名字在“Anneke”(包括)和“Chirstian”(包括)的人
select * from employees where first_name between 'Anneke' and 'Chirstian';
IN-现在,要从employees表中选取姓氏为 ‘Simmel’和’Peir’ 的人
select * from employees where last_name in ('Simmel','Peir');
4、聚合查询
4.1 常用聚合查询SQL
-
* GROUP BY、 HAVING
-
* COUNT,MAX,MIN,SUM,AVG
-
* select count(gender),gender from employees group by gender;
-
* select count(gender),gender from employees group by gender having gender='F';
4.2 实操演示
GROUP BY、SUM-现取salaries表中各个员工emp_no的薪资总和
select emp_no,sum(salary) from salaries group by emp_no;
HAVING-现在接着上一步,取员工总薪资大于1000000的员工
select emp_no,sum(salary) from salaries group by emp_no having sum(salary)>1000000;
COUNT、AVG-取salaries表中薪资排名前100名的平均薪资(需要利用子查询)
select avg(salary) from (select salary from salaries order by salary desc limit 100) as s;
参考链接
SQLW3C: https://www.w3school.com.cn/sql/sql_having.asp
感谢每一个认真阅读我文章的人,礼尚往来总是要有的,虽然不是什么很值钱的东西,如果你用得到的话可以直接拿走:
这些资料,对于【软件测试】的朋友来说应该是最全面最完整的备战仓库,这个仓库也陪伴上万个测试工程师们走过最艰难的路程,希望也能帮助到你!有需要的小伙伴可以点击下方小卡片领取