mysql基础大全、字典

最新推荐文章于 2023-02-10 09:50:20 发布

小邓9907

最新推荐文章于 2023-02-10 09:50:20 发布

阅读量264

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_45848775/article/details/127058147

版权

1.sql规范

在window下大小写无所谓，linux下要严格区分
数据库名、表名、表的别名、字段名、别名小写
关键字、函数名、绑定变量名大写
2.三种注释
多行注释： /* */
单行注释：–
单行注释：#
3.导入数据（sql语句）
1.source 文件路劲名
2.图形化界面按钮导入
4.基础查询
SELCCT * FROM 表名；
SELCET * FROM DUAL; #dual为伪表
起别名： AS+别名可以中文如：SELECT username AS 'rootname’FROM user; SELECT username AS '账号’FROM user;
注：没有as也可以但单词相隔不行如 root nameSELECT username rootname’FROM user;
*去重 DISTINCT：：SELECT DISTINCT username AS 'rootname’FROM user; 错误去重列数不匹配需要把去重的放在第一位，但是没有意义，一般就是单独执行去重查询
空值参与运算：即null null参与运算参与运算一定等于null 需要函数控制 ifnull（commission，0）即null转化为0‘ ’、0不等于null需要强调下如
着重号：如SELECT * FROM ORDER; 与关键字冲突需要用 非单引号
查询常数：加一个常数如SELECT ‘dyh’,id,username,password FROM user;
显示表结构：desc 表名；
范围查询：between and 如SELECT *FROM user WHERE id BETWEEN 11 AND 15;
in用于少量查询如：SELECT *FROM user WHERE id IN (11,12);
过滤条件where：SELECT *FROM user WHERE id=1; 注意如username=‘kij’严格需要单引号
模糊查询：只要有就行如：SELECT *FROM user WHERE username LIKE ‘%w%’; %表示0到个字符。
包含多个的可以用and SELECT *FROM user WHERE username LIKE ‘%w%‘AND username LIKE’%s%’;或者OR SELECT *FROM user WHERE username LIKE ‘%w%s%‘OR username LIKE’%s%w%’;
模糊第二个子字符用_ _代表一个不确定的字符如 SELECT *FROM user WHERE username LIKE ‘%、_s%’; 规定第三个也是如此 __ SELECT *FROM user WHERE username LIKE ‘__x’;
如果需要查询_需要转义_
不为空写法 IS NOT NULL 如：SELECT employee_id,employees.department_id,departmemt_name ,commision_pct FROM employees JOIN departments ON employees.department_id=departments.department_id WHERE employees.commision_pct IS NOT NULL;
基础运算符：
* null 只要有null的都会是null 如SELECT * FROM user WHERE ;
* <=>安全等于与=主要区分是有没有null <=>有null会显示0 如select 1<=>null from dual;
逻辑运算符：AND 、OR、!、XOR(异或)
位运算符（很少用）一般优化不会从位运算符入手数组扩容会用到
```
  * &与		
  * |或
  * ^异或
  * ~取反
  * >>右移
  * <<左移
```
排序 order by
* order by 默认低到高 ASC升序，DESC低到高如：SELECT * FROM user ORDER BY score DESC;
* 也可以去别名进行排序：如 SELECT id,score AS ‘成绩’ FROM user ORDER BY score DESC;
* 注意别名只能在order by中使用，不能再where使用，需要理解sql执行过程，先from，再where得到数据，再取select的字段，最后order by排序
* 二级排序/duo==多级排序先按第一个相同才按第二个（当一级排序一样数值的情况下）
```
  * SELECT id,score AS'成绩'  FROM `user` ORDER BY score  DESC ,id ASC ;  先按score降序后按id升序。
```
分页操作 limit
* 如 SELECT id,score AS’成绩’ FROM user LIMIT 0,20; 显示1到20条。 0表示第0条开始 20是一页20条不是0到20 20到40条则是：SELECT id,score AS’成绩’ FROM user LIMIT 20,20;
* 公式 limit （pagenumber-1）*pagesize ，pagesize；
* 如：where order by limit排序如：SELECT id,score AS’成绩’ FROM userWHERE score>50 ORDER BY score LIMIT 20,20;
* mysql8.0写法：limit offfset SELECT id,score AS’成绩’ FROM user LIMIT 20 OFFSET 20;
* limit分页可以用在mysql、但不能用在sql server、db、eracle
* 比较字符数的函数（与值无关）：SELECT id,score AS’成绩’ FROM user ORDER BY LENGTH(score) LIMIT 20,400 ;
多表查询
* 原则上不能超过多个表联表因为全部遍历三个以上表太慢了
* 多表查询需要多连接条件如：SELECT employees.department_id,departments.departmemt_name FROM employees,departments WHERE employees.department_id=departments.departments_id;
* 如果查询中出现多个表都共同有的字段需要写好表名：SELECT employee_id , employees.department_id,departments.departmemt_name FROM employees,departments WHERE employees.department_id=departments.department_id;
* 为了优化多表查询最好每个字段名都加上表名：如 SELECT employees.employee_id , employees.department_id,departments.departmemt_name FROM employees,departments WHERE employees.department_id=departments.department_id;
* 表名出现太多，不好读可以给表起别名： t1、t2 SELECT t1.employee_id , t1.department_id,t2.departmemt_name FROM employees AS t1,departments AS t2 WHERE t1.department_id=t2.department_id; 注：AS可不加，但是起了别名一定要再select、where用别名，不能再用表名
* 三表以上连表：SELECT t1.employee_id , t1.department_id,t2.departmemt_name,t3.city FROM employees AS t1,departments AS t2,locations AS t3 WHERE t1.department_id=t2.department_id AND t2.location_id=t3.location_id; 用and连接起来
多表连接关系
- 等值连接与非等值连接
  * 非等值范围连接：SELECT job_grade.grade_level,employees.slary ,lowest_sal,highest_sal FROM employees,job_grade WHERE employees.slary BETWEEN job_grade.lowest_sal AND job_grade.highest_sal;
- 自连接与非自连接
  * 自连接：即自引用表还是那个表但是逻辑上面分成两个表如员工id 与管理者id 因为管理者也是员工就可以利用这种包含关系建立自连接如：SELECT emp.employee_id,emp.last_name,mag.employee_id,mag.last_name FROM employees AS emp,employees AS mag WHERE emp.manager_id=mag.employee_id;
- 内连接与外连接
  - 内连接：合并具有同一列的两个以上的表的行，结果不包含另一个表与另一个不匹配的行
  - join on格式的内连接（省略了inner）:SELECT emp.employee_id,emp.last_name,dep.department_id,dep.departmemt_name FROM employees AS emp JOIN departments AS dep ON emp.department_id =dep.department_id;
  - 多个表也是 join on 连接：SELECT emp.employee_id,emp.last_name,dep.department_id,dep.departmemt_name,loc.city FROM employees AS emp JOIN departments AS dep ON emp.department_id =dep.department_id JOIN locations AS loc ON dep.location_id=loc.location_id;
- 外连接：包含内连接及没有匹配到的：分别是左外连接、右外连接、全连接
  - 左外连接也就是内连接加上左边没有匹配的 left (outer) join：SELECT emp.employee_id,emp.last_name,dep.department_id,dep.departmemt_name FROM employees AS emp left JOIN departments AS dep ON emp.department_id =dep.department_id ;
  - 右外连接即 right join on ：SELECT emp.employee_id,emp.last_name,dep.department_id,dep.departmemt_name FROM employees AS emp RIGHT JOIN departments AS dep ON emp.department_id =dep.department_id ;
  - 满外连接：也就是左外联合右外用union 去重效率较低 \union all 不去重但是多一个内连接但是不建议因为有重一般是左外+右外去内连接
union使用：
* 左外连接去掉内连接写法：选出null 也就是内连接不存在的部分：SELECTemp.employee_id,emp.last_name,dep.department_id,dep.departmemt_name FROM employees AS emp LEFT JOIN departments AS dep ON emp.department_id =dep.department_id WHERE dep.department_id IS NULL;
* 注意union 的列数和类型必须一样，也就是选择的字段要一样：SELECT emp.employee_id,emp.last_name,dep.department_id,dep.departmemt_name FROM employees AS emp LEFT JOIN departments AS dep ON emp.department_id =dep.department_id WHERE dep.department_id IS nullUNION all SSELECT emp.employee_id,emp.last_name,dep.department_id,dep.departmemt_name FROM employees AS emp RIGHT JOIN departments AS dep ON emp.department_id =dep.department_id WHERE dep.department_id ;
sql99新特性：
* 自然连接：NATURAL JOIN 会自动查询两个表中相同的字段如： SELECT employee_id,department_id,departmemt_name FROM employees NATURAL JOIN departments;
* 自然连接比where 简洁但是不够灵活比如两个表有2个相同字段但是当你只想要一个的时候就不行
* 新特性using：using替换的就是jion on 就是on后面的连接条件：SELECT employee_id,department_id,departmemt_name FROM employees JOIN departments USING(department_id);注：名字必须一样不适用于自连接