mysql · 編程開發筆記

```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`; ```