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