```sql
數據庫的種類(實時數據庫)
關系型數據庫:MySQL、Oracle、DB2、SqlServer
非關系型數據庫:MongoDB(學習成本低)、Redis、Memcache
sql語句
# 新增
INSERT INTO student (`stu_num`,`name`,`age`,`class`)
VALUES (001,'熊貓',23,2);
# 修改
UPDATE student SET age=22,class=3 WHERE NAME = '熊貓';
# 查詢
SELECT * FROM student; - 查詢所有的
SELECT name,age FROM student; - 只顯示name、age
select * from student where age=18 - 只查詢age為18的
SELECT * FROM student WHERE age=22 and name='熊貓'; - 查符合條件的 并且
SELECT * FROM student WHERE age=22 or name='熊貓'; - 查符合條件的 或
SELECT COUNT(*) FROM student; - 查總行數
SELECT COUNT(*) FROM student WHERE age=22; - 查符合條件的總行數
select sum(age) from student; - 合計:查詢所有年齡加起來的數
select sum(age)/COUNT(1) from student; - 平均:查詢平均年齡
select avg(age) from student; - 平均(簡易):查詢平均年齡
select avg(age) as 平均年齡 from student; - 平均(簡易):查詢平均年齡
select coun(1) from student group by age;
SELECT age,COUNT(1) FROM student GROUP BY age; - 分組查詢
select * from student limit 30,1; - 偏移
select * from student limit order by; - 倒序
```
:-: mySql 筆記 2020-10-27
```sql
-- mySql -- 關聯型數據庫
-- 可視化數據庫管理工具 Navicat
-- 單表查詢
select id, loginid from `user`;
-- 給isMale列取別名
select isMale as '性別' from `employee`;
-- 查詢單個表(employee)的所有數據
select * from `employee`;
-- 定別名,轉化輸出的結果
select id as 'ID', `name` as '姓名',
-- case isMale when 1 then '男' else '女' end '性別',
-- isMale列進行判斷,為1是輸出男,否則為女
case when isMale = 1 then '男' else '女' end '性別',
case
when salary>=10000 then '高薪資'
when salary>=5000 then '中薪資'
else '低薪資'
end '薪資等級',
salary as '薪資',
-- xxx -- 一個不存在的列,輸出時它是一個常量
xxx
from employee;
-- 查詢特定條件的多條數據
select * from employee where `isMale` = 1;
-- 查詢公司(companyId)為1或2的所有部門(department)數據。
select * from department where `companyId` in (1, 2);
-- 查詢員工表(employee)所有地址(location)為空字符串的數據。
select * from employee where `location`="";
-- 查詢員工表(employee)所有地址(location)為空的數據。
select * from employee where `location` is null;
-- 查詢員工表(employee)所有地址(location)不為空的數據。
select * from employee where `location` is not null;
-- 查詢員工表(employee)所有薪資大于等于10000的數據。
select * from employee where `salary`>=10000;
-- 查詢員工表(employee)所有薪資在10000~12000之間的數據。
select * from employee where `salary` between 10000 and 1200;
-- 模糊匹配查詢, 查詢員工表(employee)所有名字(name)包含'陳'關鍵字的數據。
-- '%'表示匹配任意字符,'_' - 一個字符的任意字符
select * from employee where `name` like '%陳%';
-- 組合條件搜索
select * from employee
where `name` like '%張%' and `isMale`=0 and `salary`>=12000
or `birthday`>='1996-1-1';
-- 將搜索出來的結果進行升降序 order by
-- asc limit
-- desc - 降序
select *,
case when isMale=1 then '男' else '女' end '性別'
from employee
where `name` like '%張%' and (`isMale`=0 and `salary`>=12000 or `birthday`>='1996-1-1')
order by `性別` delimit
-- 在原有的基礎上進一步進行排序。
select *,
case when isMale=1 then '男' else '女' end '性別'
from employee
order by '性別' asc, `salary` delimit
-- 查詢時跳過n條數據取出n條數據,一般用于分頁。limit [(第n頁-1)*頁容量],[頁容量]
select * from employee limit 0,30;
-- 查詢后去除重復項、distinct
select distinct `location` from employee;
-- 聯表查詢
-- 笛卡爾積
select team.name '主', team.name '客'
from team as t1, team as t2
when ti.id != t2.id;
-- 左表查詢
select * from department as d left join employee as e
on d.id = e.deptId;
-- 右表查詢
select * from employee as e right join department as d
on d.id = e.deptId;
-- 類連接
select * from employee as e inner join department as d
on d.id = e.deptId;
-- 連接更多的表進行查詢
select e.name 'empname', d.name 'dptname', c.name 'companyname'
from employee as e
inner join department as d on d.id=e.deptId
inner join company as c on d.id=c.id;
select e.name '員工姓名',
case isMale when 1 then '男' else '女' end '性別',
e.joinDate '入職時間',
e.salary '薪資',
d.name '所屬部門',
c.name '所屬公司'
from employee e
inner join department d on e.deptId = d.id
inner join company c on d.companyId = c.id;
select e.name '員工姓名',
case isMale when 1 then '男' else '女' end '性別',
e.joinDate '入職時間',
e.salary '薪資',
d.name '所屬部門',
c.name '所屬公司'
from employee e
inner join department d on e.deptId = d.id
inner join company c on d.companyId = c.id
-- when c.name = in ('騰訊科技','螞蟻金服');
when c.name = like '%渡一%' and d.name = '教學部';
-- 查詢id有值的總數
select count(`id`,`name`) from employee;
-- 聚合查詢
select count('id') as '員工數量',
avg('salary') as '平均薪資',
sum('salary') as '總薪資',
min('salary') as '最小薪資',
from employee;
-- 將查詢到列的字符串拼接起來
select concat(`name`, `salary`) from employee;
select concat_ws('->拼接符<-', `name`, `salary`) from employee;
-- 得到當前時間
select current_date(); select curdate();
select timestampdiff(year,'2010-4-1 11:11:11','2010-1-2 11:11:12');
-- 查詢員工表中,根據出生日期計算出年齡。并進行排序
select *, timestampdiff(year, `birthday`, curdate()) as age
from employee order by age;
-- 查詢員工表中limit所對應的員工數量
select `location`, count(id) as '員工數量'
from employee group by `location`;
-- having 運行時間在 select 之后
select `location`, count(id) as '員工數'
from employee
group by `location`
having '員工數'>=40;
-- 查詢時語句的運行順序
-- 1、from
-- 2、join .. on ..
-- 3、where
-- 4、group by
-- 5、select
-- 6、having
-- 7、order by
-- 8、limit
-- 查詢所有薪水在10000以上員工,分布的居住地
select `location`, count(`id`) as '員工數'
from employee
when `salary`>=10000
group by `location`
having count(`id`)>=30;
-- 最后得到 部門名 跟 員工數量
select d.`name`, count(e.id) as '員工數量'
-- 公司表
from company as c
-- 連表 - 部門表
inner join department as d on c.id = d.companyId
-- 連表 - 員工表
inner join employee as e on d.id = e.deptId
-- 模糊搜索條件為渡一關鍵字的公司
where c.`name` like '%度一%'
-- 將部門名字作為分組
group by d.id, d.`name`;
-- 最后得到 公司名 跟 員工數量
select c.`name`, count(e.id) as '員工數量'
-- 公司表
from company as c
-- 連表 - 部門表
inner join department as d on c.id = d.companyId
-- 連表 - 員工表
inner join employee as e on d.id = e.deptId
-- 查詢五年內入職的員工,并且居住地在萬家灣的。
where timestampdiff(year, e.joinDate, curdate())<=5 and e.`location` like '%萬家灣%'
-- 將部門名字作為分組
group by d.id, d.`name`;
```
- 前端工具庫
- HTML
- CSS
- 實用樣式
- JavaScript
- 模擬運動
- 深入數組擴展
- JavaScript_補充
- jQuery
- 自定義插件
- 網絡 · 后端請求
- css3.0 - 2019-2-28
- 選擇器
- 邊界樣式
- text 字體系列
- 盒子模型
- 動圖效果
- 其他
- less - 用法
- scss - 用法 2019-9-26
- HTML5 - 2019-3-21
- canvas - 畫布
- SVG - 矢量圖
- 多媒體類
- H5 - 其他
- webpack - 自動化構建
- webpack - 起步
- webpack -- 環境配置
- gulp
- ES6 - 2019-4-21
- HTML5補充 - 2019-6-30
- 微信小程序 2019-7-8
- 全局配置
- 頁面配置
- 組件生命周期
- 自定義組件 - 2019-7-14
- Git 基本操作 - 2019-7-16
- vue框架 - 2019-7-17
- 基本使用 - 2019-7-18
- 自定義功能 - 2019-7-20
- 自定義組件 - 2019-7-22
- 腳手架的使用 - 2019-7-25
- vue - 終端常用命令
- Vue Router - 路由 (基礎)
- Vue Router - 路由 (高級)
- 路由插件配置 - 2019-7-29
- 路由 - 一個實例
- VUEX_數據倉庫 - 2019-8-2
- Vue CLI 項目配置 - 2019-8-5
- 單元測試 - 2019-8-6
- 掛載全局組件 - 2019-11-14
- React框架
- React基本使用
- React - 組件化 2019-8-25
- React - 組件間交互 2019-8-26
- React - setState 2019-11-19
- React - slot 2019-11-19
- React - 生命周期 2019-8-26
- props屬性校驗 2019-11-26
- React - 路由 2019-8-28
- React - ref 2019-11-26
- React - Context 2019-11-27
- PureComponent - 性能優化 2019-11-27
- Render Props VS HOC 2019-11-27
- Portals - 插槽 2019-11-28
- React - Event 2019-11-29
- React - 渲染原理 2019-11-29
- Node.js
- 模塊收納
- dome
- nodejs - tsconfig.json
- TypeScript - 2020-3-5
- TypeScript - 基礎 2020-3-6
- TypeScript - 進階 2020-3-9
- Ordinary小助手
- uni-app
- 高德地圖api
- mysql
- EVENTS
- 筆記
- 關于小程序工具方法封裝
- Tool/basics
- Tool/web
- parsedUrl
- request