什么是数据库
初识SELECT
SELECT *FROM employess;
SELECT first_name,last_name
FROM employees;
#查询员工表一共有那些部门
#有重复
SELECT department_id
FROM employees;
#无重复
SELECT DISTINCT department_id
FROM employees;
#空只参与运算
/*
空值:null
null不等同0,'','null'
*/
#有null的还是null
SELECT employee_id,salary "月工资",salary*(1+commission_pct)*12 "年工资",commission_pct
FROM employees;
#用控制语句 IFNULL
SELECT employee_id,salary "月工资",salary*(1+IFNULL(commission_pct,0))*12 "年工资",commission_pct
FROM employees;
#着重号``
SELECT *FROM ORDER;
SELECT *FROM `order`;
#查询常数
SELECT '尚硅谷',123,employee_id,last_name
FROM employees;
#显示表结构
DESCRIBE employees;
#也可以
DESC employees;
#过滤信息(查询想要的数据)
#WHERE 需要放在FROM后面
SELECT *
FROM employees
WHERE department_id = 90;
运算符
SELECT 100+'1'
FROM DUAL;
#结果是101 ,在sql语言中+不代表拼接,java可以代表。+就表示加法,会将字符串转换数值(隐式转换)
SELECT 100+'a'
FROM DUAL;
#结果是100,'a'看做0处理
SELECT 100+'ab'
FROM DUAL;
#结果是100,'ab'看做0处理
SELECT 100+NULL
FROM DUAL;
#结果是null,null值参与运算,结果为null
SELECT 100/5
FROM DUAL;
#结果是50.0000,就算是5.0也是50.0000,/也可以写DIV,分母为0结果为null
SELECT -101%5
FROM DUAL;
#结果是-1,正负与被模数有关,就算是-5结果也为-1
#比较运算符
#结果为真返回为1,假为0,其他为null
SELECT 1=2,1!=2,1='1',1='a',0='a'
FROM DUAL;
#结果为 0, 1 1 0 1
# = 是判断两边是否相等,字符串存在隐式转换,转换不成功则看为0
SELECT 'a'='a','ab'='ab','a'='b'
FROM DUAL;
#结果是 1 1 0
#两边都为字符串会按Ascll码值比较
SELECT 1=NULL ,NULL =1,NULL =NULL
FROM DUAL;
#结果是 null null null
#只要NULL参与结果为null
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct=NULL;
#什么结果也没有,WHERE 返回值为1才保存输出
# <=> 安全等于
#与= 一样只是可以判断null,避免上面的情况
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct<=>NULL;
#有结果 ,commission_pct为null的输出
#IS NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;
#和<=>一样
#IS NOT NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#commission_pct不为null的输出
#ISNULL()
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);
#和<=>一样和IS NULL一样
#LEAST()最小 、GREATEST()最大
SELECT LEAST('A','C','a'),GREATEST('a','c','C')
FROM DUAL;
#按ascll比较 A C
#BETWEEN ......AND包含边界
#查询工资6000到8000
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
#查询工资不在6000到8000
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#或者
#可以用>配合&&,&&可以用and代替
#也可以用<配合||当然||可以用or代替
#XOR 表示异或
#In()查询某些个值(不是范围) ,NOT IN()相反
#查询工资6000或者8000或者5000
SELECT employee_id,last_name,salary
FROM employees
WHERE salary IN(6000,8000,5000);
#LIKE 模糊查询
#查询last_name中字符包含'a'的信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#不区分大小写
#% 代表不确定个数(0个 ,1,个,多个)
# _ 带表一个字符不确定
SELECT last_name
FROM employees
WHERE last_name LIKE '__a';
#第三个字符是a的信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a';
#第二个字符为_第三个字符为a, \转意字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a' ESCAPE '$';
#若用其他方法表示转意字符用ESCAPE 表示
#REGEXP\RLIKE(正则表达式)
SELECT 'shklls' REGEXP '^sh','rctyvuhjb' REGEXP '$jb',
'efsvgd'REGEXP 'sv','efsvgd'REGEXP 'sv.d','efsvgd'REGEXP '[fd]'
FROM DUAL;
#结果是 1 1 1
#'^'是否以什么字符开头,'$'是否以什么字符结尾''是否包含这个字符串
#'.'有一个不知道svgd 这里.就相当于g了
#'[]'包含a或者b
注意列的别名只能在ORDER BY里面使用,不能在WHERE使用原因
排序
#排序
SELECT *FROM employees;
#使用ORDER BY 对查询的数据进行排序操作(默认是升序)
#升序:ASC (ascend)
#降序:DESC (descend)
#注意列的别名只能在ORDER BY里面使用,不能在WHERE使用
#where 在from后,order by 前
SELECT employee_id,last_name,salary,,salary*12 "年工资"
FROM employees
WHERE salary>1000
ORDER BY salary ASC;
#二次排序
#显示员工信息,先department_id升序然后,salary降序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id ASC,salary DESC;
分页
SELECT *
FROM employees;
#分页
#LIMIT 偏移量,每页显示多少条记录
SELECT employee_id,last_name
FROM employees
LIMIT 0,20; #第一页,共20条记录
SELECT employee_id,last_name
FROM employees
LIMIT 20,20; #第二页,共20条记录
#WHERE, ORDER BY, LIMIT 三个的先后循序
SELECT last_name,salary
FROM employees
WHERE salary>6000
ORDER BY salary DESC
LIMIT 0,20;
#Mysql 8.0新特性
#LIMIT 每页的个数 OFFSET 偏移量
SELECT employee_id,last_name
FROM employees
LIMIT 20 OFFSET 0; #第一页,共20条记录
多表查询
优点:减少数据冗余,减少与内存IO交换效率,线程并发,利于维护
分类:
等值连接、非等值连接
自连接、非自连接
内连接、外连接
等值连接
SELECT employees.`employee_id`,departments.`department_name`,employees.`department_id`
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#注意:多表查询中有共有的需要指明在那张表操作
#优化,表特有的指明是那张表,可以提高效率
#继续优化,表名太长可以起别名
SELECT t1.`employee_id`,t2.`department_name`,t1.`department_id`
FROM employees t1,departments t2
WHERE t1.`department_id`=t2.`department_id`;
#注意:表被起别名之后需要用别名不能用源有的表名,覆盖
非等值连接
SELECT e.`first_name`,e.`salary`,j.`grade_level`
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal`AND j.`highest_sal`;
自连接
#查询员工ID,员工姓名以及管理者的ID的姓名
SELECT emp.`employee_id` ,emp.`last_name`,mgr.`department_id`,mgr.`last_name`
FROM employees emp,employees mgr
WHERE emp.`manager_id`=mgr.`employee_id`;
非自连接
不在同一个表中
内连接
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的
SELECT t1.`employee_id`,t2.`department_name`,t1.`department_id`
FROM employees t1,departments t2
WHERE t1.`department_id`=t2.`department_id`;
外连接
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的 行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
#查询“所有”员工的last_name,department_name
#SQL92语法为连接用+,但是Mysql不支持(ORECL支持)
SELECT e.`last_name`,d.`department_name`
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`(+);
#SQL95语法使用JOIN...ON(ORECL支持)
#内连接
SELECT e.`last_name`,d.`department_name` ,city
FROM employees e INNER JOIN departments d#INNER可以省略
ON e.`department_id`=d.`department_id`
JOIN locations l
ON d.`location_id`=l.`location_id`;
#左外连接
SELECT e.`last_name`,d.`department_name`
FROM employees e LEFT OUTER JOIN departments d#OUTER可以省略
ON e.`department_id`=d.`department_id`;
#右外连接
SELECT e.`last_name`,d.`department_name`
FROM employees e RIGHT OUTER JOIN departments d#OUTER可以省略
ON e.`department_id`=d.`department_id`;
#满外连接(mysql不支持)(ORECL支持)
SELECT e.`last_name`,d.`department_name`
FROM employees e FULL OUTER JOIN departments d#OUTER可以省略
ON e.`department_id`=d.`department_id`;
#中图
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;
#右中
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#左下
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#右下
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
SQL语法新特性:自然连接和USING连接
自然连接
它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行 等值 连接
#未使用新特性
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id`=d.`manager_id`;
#使用新特性
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e NATURAL JOIN departments d;
USING连接
指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用
#未使用新特性
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
#使用新特性
SELECT e.`first_name`,e.`employee_id`,d.`department_name`
FROM employees e JOIN departments d
USING(department_id);
sql99语法执行的步骤
sql92和99语法的简单区别