数据库笔记

 

数据库分为
关系型        ：Oracle，MySQL，SQLServer，Access

非关系型     ：MongoDB，Redis，Solr，ElasticSearch，Hive，HBase

关系型：建立在紧密关系基础之上（父子，师生）以行和列的形式存储数据，关系模型可以简单理解为二维表格模型
一个关系型数据库由二维表及其之间的关系组成一个数据集合

非关系型：建立在松散关系之上

安装数据库的时候需要记得开启MariaDB服务
在任务管理器最后一栏的服务中找到

DML：数据操纵语言，包括insert，delect，update，select，增删改查

DDL：数据库定义语言，包括create table，create database

DCL：数据库控制语言，包括grant，deny，revoke等，只有管理员才有相应的权限

DQL：数据库查询语言，包括select语法

查询表格属性：desc 表名

增加属性：alter table 表名 add column 属性  属性类型（x） 

查询语法：select * from 表名

插入语法：insert into 表名 valuse（属性值需要填很多，没有的用null，使用逗号隔开）

修改语法：update 表名 set 属性=新值（老值会被全部覆盖） ， 属性=新值

数字：
tinyint ,int 整数类型
float,double 小树类型
numeric(5,2) decimal(5,2)一般也表示小数，表示共有5位，其中可以有两位小数
decimal和numeric表示精确的整数数字

日期：
date 包含年月日
time 时分秒
datetime 包含年月日和时分秒
timestamp 时间戳，不是日期，从1970.1.1到指定日期的毫秒数

图片：
blob 二进制数据

约束：
唯一约束：unique 唯一约束
主键约束：primary key 主键（拥有非空约束和唯一约束的功能）
非空约束：not null
自增：auto_increment

create table 表名 （属性  属性类型（x） unique（唯一约束），属性  属性类型（x） not null（不能为空））；

dept表  

emp表

user表

#基本函数：
#upper（）,名字转为大写
#查询emp标所有员工的数据
SELECT * FROM emp;
#查询所有员工的名字
#select ename xxx from emp;//给字段设置别名
SELECT ename,job FROM emp;
SELECT ename,UPPER(ename) FROM emp;  #转大写
#lower()转小写
SELECT ename,LOWER('XYZ') FROM emp;
#length 查询长度
SELECT job,LENGTH(job) FROM emp;
#substr()截取SUBSTR(字段，从第几位开始截取，截几位)
SELECT ename,SUBSTR(ename,2,1) FROM emp;
#concat 拼接
SELECT ename, CONCAT(ename,'把','烈儿杀了') FROM emp;
#replace替换
SELECT ename,REPLACE(ename,'a','t') FROM emp;
#ifnull如果是null就替换
SELECT comm,IFNULL(comm,100) FROM emp;
#小数：round&ceil&floor
SELECT comm,ROUND(comm,1) FROM emp;#四舍五入，第二个参数是保留位数
SELECT comm,CEIL(comm) FROM emp;#向上取整
SELECT comm,FLOOR(comm) FROM emp;#向下取整
#now &year&month&day&hour&minute&second
SELECT NOW(),YEAR('2021-11-25'),YEAR(NOW()),MONTH(NOW());
#转义字符
SELECT 'xi\an';



#条件查询
#distinct去重
#查询部门地址
SELECT loc FROM dept;
SELECT DISTINCT(loc) FROM dept;
#where:条件满足查询
#select 字段名 from 表名 where 字段名=字段值
SELECT * FROM dept WHERE loc='一区';
#查询2号部门的所有数据
SELECT * FROM dept WHERE deptno=2;
#查询办公地址在二区的部门名称
SELECT dname FROM dept WHERE loc='二区';
#查询地址在二区的编号是3号的部门名称,多条查询用AND连接
SELECT dname FROM dept WHERE loc='二区'AND deptno=3;
#查询编号是1或者2的部门名称
SELECT dname FROM dept WHERE deptno=1 OR deptno=2;
SELECT dname FROM dept WHERE dootno= IN (1,2);#效果同上
#查询地址在一区，或者，编号是3的部门名称
SELECT dname FROM deuserpt WHERE loc='一区' OR deptno=3;
#修该3号用户的密码
UPDATE \user SET pwd=123456 WHERE id=3;
#修改密码是123，age=20的用户名
UPDATE \user SET \name='lier' WHERE pwd=123 AND age=20;
#删除5号用户
DELETE FROM \user WHERE id=5;
#like模糊查询
#练习查询名字里以t开头的员工姓名和岗位,%通配符字母和数字
SELECT ename,job FROM emp WHERE ename LIKE 't%';
#练习查询名字包含o的员工姓名和编号
SELECT ename,empno FROM emp WHERE ename LIKE '%o%';
#null 对null特殊处理
SELECT ename,job FROM emp WHERE comm IS NULL;#没有奖金
#查询有奖金的
SELECT ename,job FROM emp WHERE comm IS NOT NULL;#有奖金

#between and 区间范围两头都包
#查询工资在5000~10000员工姓名和入职日期
SELECT ename,hiredate FROM emp WHERE sal>5000 AND sal<10000;
SELECT ename,hiredate FROM emp WHERE sal BETWEEN 5000 AND 10000;
#查询在2019年入职的员工姓名
SELECT ename FROM emp WHERE YEAR(hiredate) BETWEEN 2019 AND 2019;
#limit分页
#展示前两个员工的信息
#select * from emp limit ?（从第n+1条数据开始）,?（取几条）
SELECT * FROM emp LIMIT 1,3;

#order by排序
#升序
SELECT * FROM emp ORDER BY sal;
#降序
SELECT * FROM emp ORDER BY sal DESC;
SELECT * FROM emp ORDER BY ename;
SELECT * FROM emp ORDER BY hiredate;
SELECT * FROM emp ORDER BY job;

#模拟用户登录过程
SELECT * FROM USER  WHERE NAME='jack' AND pwd=123;
SELECT * FROM USER  WHERE NAME='rose' AND pwd=123;

#模拟用户注册
INSERT INTO USER VALUES(NULL,'lier',123,20);

#统计部门每个月的工资支出
SELECT sal,comm,sal+IFNULL(comm,0) FROM emp;
SELECT sal,comm,(sal+IFNULL(comm,0))*12 FROM emp;


#聚合函数:max min sum avg coumt
#求最高薪水
SELECT MAX(sal) FROM emp;
#求最低薪水
SELECT MIN(sal) FROM emp;
#求薪水合
SELECT SUM(sal) FROM emp;
#求平均工资
SELECT AVG(sal) FROM emp;
#求个数
SELECT COUNT(*) FROM emp;
SELECT COUNT(comm) FROM emp;
#count(1) = count(*),但是1高效
SELECT COUNT(1) FROM emp;
#统计2019年前入职的员工信息
SELECT * FROM emp WHERE YEAR(hiredate)<2019;
#统计2019以前入职的平均工资
SELECT AVG(sal) FROM emp WHERE YEAR(hiredate)<2019;
#统计2号部门的最高薪水
SELECT MAX(sal) FROM emp WHERE deptno=2;
#统计岗位是员工的平均工资
SELECT AVG(sal) FROM emp WHERE job='员工';


#当查询结果中，出现了混合列的时候，必须分组！！！！
#聚合列使用了聚合函数，非聚合列，没有用聚合列
#group by 表示分组，having 子句类似where
#统计每个岗位的平均工资
SELECT job,AVG(sal) FROM emp GROUP BY job;#按岗位分组
#统计每个岗位的员工工人数并排序
SELECT job,COUNT(1) a FROM emp GROUP BY job ORDER BY a DESC;

#统计每个部门的最高薪
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;
#统计每个部门的总人数
SELECT deptno,COUNT(1) FROM emp GROUP BY deptno;
#统计每年入职的总人数
SELECT YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate);
#统计每年入职的平均工资
SELECT YEAR(hiredate),AVG(sal) FROM emp GROUP BY YEAR(hiredate) ORDER BY sal;



#分组后的过滤 使用having来进行筛选

#统计每年入职工资的总支出，2015年以后的
SELECT YEAR(hiredate) a,SUM(sal) FROM emp 
GROUP BY a
HAVING a>2015;

#统计每个部门的总人数，只要人数>2的数据
SELECT deptno,COUNT(1) a FROM emp GROUP BY deptno HAVING a>2