mysql 基础操作

最新推荐文章于 2024-04-03 19:01:16 发布

8秒记忆的鱼丶

最新推荐文章于 2024-04-03 19:01:16 发布

阅读量101

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/ckx12138/article/details/107875880

版权

mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

基础查询

显示常量


select 100;

select 'john';

显示表达式

select 70*20;

显示函数

SELECT length('john');

SELECT VERSION();

显示表中字段

select last_name from employees;

#显示多个字段＋ 该别名

SELECT last_name'名字',email'youxiang' from employees;

SELECT last_name as '名字',email as 'youxiang' from employees;

如何去重

SELECT DISTINCT email from employees;

条件查询

/**

条件拆查询

**/

#案例1：查询月薪>5000的员工信息

select * from employees where salary > 5000

#案例  查询月薪不等于12000的员工信息

SELECT *FROM employees WHERE salary <> 12000

#案例1：查询月薪在5000到12000的员工工资和姓名

SELECT first_name,employee_id from employees WHERE salary between 5000 and 12000

#案例3：查询部门编号=90 或 月薪>10000并且月薪<15000的员工信息

select first_name from employees where employee_id = 90 or salary between 10000 and 15000

基础查询练习


#4.	显示表departments的结构，并查询其中的全部数据 desc 表名;

DESC departments;

#显示出表employees中的全部job_id（不能重复）distinct

SELECT DISTINCT job_id from employees

#6.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT

SELECT CONCAT(last_name,'||',email) as OUT_PUT from employees

#7.显示出表employees部门编号在80-100之间 的姓名、职位

SELECT * from employees where department_id BETWEEN 80 and 100

SELECT last_name,job_id,department_id FROM employees

WHERE department_id>=80 AND department_id<=100;

#8.显示出表employees的manager_id 是 100,101,110 的员工姓名、职位

SELECT last_name FROM employees where manager_id in(100,101)

/**

模糊查询

**/

#2.查询员工号为176的员工的姓名和部门号和年薪

SELECT first_name,job_id,salary*12*(1+IFNULL(commission_pct,0))

from employees where employee_id = 176

#3.选择工资不在5000到12000的员工的姓名和工资

SELECT first_name,salary from employees where salary not BETWEEN 5000 and 12000

#4.选择在20或50号部门工作的员工姓名和部门号

SELECT first_name,department_id from employees

WHERE department_id = 20 or department_id =50

#5.选择公司中没有管理者的员工姓名及job_id

SELECT first_name,job_id from employees where manager_id is null

#6.选择公司中有奖金的员工姓名，工资和奖金级别

SELECT first_name,salary,commission_pct

from employees WHERE commission_pct is not null

#7.选择员工姓名的第三个字母是a的员工姓名

SELECT first_name from employees where first_name LIKE '__a%'

#8.选择姓名中有字母a和e的员工姓名

SELECT first_name from employees

WHERE first_name like '%a&e%' or first_name like '%e%a%';

#9.显示出表employees表中 first_name 以 'e'结尾的员工信息

SELECT first_name from employees WHERE first_name like '%e'

/**

排序查询

**/

#一、按单个字段进行排序

SELECT * FROM employees ORDER BY salary DESC

#二、按多个字段进行排序

SELECT * from employees ORDER BY salary,employee_id DESC

#三、按表达式排序

#案例：按年薪降序

SELECT *,salary*12*(1+IFNULL(commissiom_pct,0)) 年薪

FROM employees

ORDER BY salary*12*(1+IFNULL(commissiom_pct,0)) DESC

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪

FROM employees

ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;