navicat初学者笔记（1）

navicat初学者笔记（1）

#1、查询多个
SELECT
	last_name,
	first_name,
	email,
	phone_number 
FROM
	employees;
	
#2、查询常量值
SELECT 'Join' FROM employees;

#3、查询表达式
SELECT 100%98 ；

#4、起别名的三种方式
SELECT 100%98 AS 结果;
SELECT
	last_name AS 姓,	first_name AS 名,	email AS 邮箱,phone_number AS 电话 FROM 	employees;
SELECT
	last_name 姓,	first_name 名,	email 邮箱,phone_number 电话 FROM 	employees;
	
#5、去重
SELECT DISTINCT department_id FROM employees;

#6、+的作用仅仅是数字相加，如果一方是字符型，试图转换为数字型，如果转换成功，做加法运算，如果失败，将数值型字符转换为零如果任意一方是NULL，结果为NULL。
SELECT 100%98 AS 结果;
SELECT 'Join' + 90;   #转换失败，结果为90
SELECT NULL +90;

#7、连接字符串
SELECT
	CONCAT ( last_name,' ', first_name)  
FROM
	employees;

#8、IFNULL()替换NULL值，例如是NULL则替换为0
SELECT IFNULL (commission_pct ,0) FROM employees;

#9、逻辑运算符
#与或非  AND OR NOT  例如查询部门编号不为90-100之间，或者工资大于15000的所以员工信息。
SELECT * FROM employees WHERE department_id < 90 OR department_id > 100 OR salary > 15000;
#或者
SELECT * FROM employees WHERE NOT(department_id >= 90 AND department_id <= 100)	OR salary > 15000;

#10、模糊查询
#名字里带a的
SELECT * FROM employees
WHERE last_name LIKE '%a%';
#名字里第三个字符是e
SELECT * FROM employees
WHERE last_name LIKE '___e%';
#名字里第二个字符是_(转义)
SELECT * FROM employees
WHERE last_name LIKE '_\_%';
#指定转义$
SELECT * FROM employees
WHERE last_name LIKE '__$%' ESCAPE '$';
#11、不等于  IS NOT

#12、排序ASC DESC
SELECT * FROM employees
ORDER BY salary ASC,;
SELECT * FROM employees
ORDER BY salary ASC,IFNULL(commission_pct,0) DESC;