杭州 网站开发,企业网站建设方案策划书,专业做化妆品的网站,ios开发网站app本人SQL新手#xff0c;五一期间自学了《SQL必知必会》一书#xff0c;在此简要分享学习心得#xff0c;若有差错#xff0c;请各位大佬们多多指教呀#xff01;本人的SQL学习计划是先根据《SQL必知必会》一书入门#xff0c;了解SQL的整体框架#xff0c;语法等#x…本人SQL新手五一期间自学了《SQL必知必会》一书在此简要分享学习心得若有差错请各位大佬们多多指教呀本人的SQL学习计划是先根据《SQL必知必会》一书入门了解SQL的整体框架语法等在闲暇时间刷题巩固。首先奉上本人整理的SQL涉及的功能及语法的思维导图思维导图中整理了各个SQL功能以及实现功能的代码和函数等还在不断完善更新中~本人新手可能有部分功能理解有误请大家不吝赐教(●◡●)另外本着学以致用的心态本人在SQL学习过程中坚持码代码实现相关功能文末也将奉上本人编写的SQL代码以及相关功能说明~工具是SQLyog思路依据的是《SQL必知必会》一书老规矩同学们觉得有问题或有优化空间的话请多多留言指教哇/* 检索固定位置和固定行数SELECT *FROM employees;Select distinct employee_idfrom employees;select employee_id from employeeslimit 4,4order by employee_id; *//* 多列排序 第一个排序列的值不唯一需要多列区分select employee_id, first_name, last_namefrom employeesorder by first_name,last_name;*//* 升序排序和降序排序select first_name, last_name, salaryfrom employeesorder by salary desc;*//* 找到工资3000的员工where 过滤指定的是行。select first_name,last_name,salaryfrom employeeswhere salary 3000;*//* 不匹配查询--字符串要用单引号引用select first_name,last_name,salary from employees where job_id AD_VP ;*//* 范围查询select first_name,last_name,salaryfrom employeeswhere salary between 3000 and 5000order by salary desc;*//* 查询空值select first_name,last_name,emailfrom employeeswhere email is null;*//*逻辑操作符*//* AND 操作符select first_name,last_name,salary,job_idfrom employeeswhere job_id IT_PROG and salary 5000order by salary;*//* OR 操作符select salary,first_name,last_name,job_idfrom employeeswhere job_id IT_PROGor salary 10000order by salary;*//* 求值顺序--AND的优先级更高会优先和其它语句组合类似数学运算的乘除OR类似加减可用圆括号优先级更高强制改变运算顺序select job_id, first_name,last_name,salaryfrom employeeswhere (job_id IT_PROGor job_id AD_VP)and salary 6000;*//* IN 的用法————类似“集合”概念完成类似 ”OR“语句的操作学会使用“IN”替代“OR”select first_name,last_name,salary,job_idfrom employeeswhere job_id in (AD_VP,IT_PROG)order by salary;*//*select first_name,last_name,salary,job_idfrom employeeswhere not job_id in (AD_VP,IT_PROG)and salary 10000order by salary;*//* 通配符% 匹配多个字符 搜百分号放字符前面表示以百分号后的字符结尾的量放字符后面表示搜索以字符开头的量select *from jobswhere job_title like %manager%order by min_salary;*/ /*通配符 下划线 一个下划线只匹配单个字符而不是多个字符select *from jobswhere job_title like __________ Manager;*//*组合使用select *from employeeswhere first_name NOT like N%or first_name Not like L%order by salary desc;*//* 使用concat拼接两列,并指定别名select concat(first_name,(,last_name,))from employeesas nameorder by salary;*//* 返回当前日期select now()*//* 文本处理改变字符串首字母大小写select first_name,lower(first_name)as first_name_locasefrom employeesorder by first_name_locase;*//*搜索发音相同的字符select first_namefrom employeeswhere soundex(first_name)soundex(vali);*//* 提取时间 年select first_name,last_name,salaryfrom employeeswhere year(hiredate)2004order by salary;/* 提取时间 月select first_name,last_name,salaryfrom employeeswhere month(hiredate)3order by salary;*//* 提取时间 日select first_name,last_name,salaryfrom employeeswhere day(hiredate)3order by salary;*//* 求平均值列名不是字符串select avg(salary)as avg_salaryfrom employees;*//* 计数函数的使用是否将NULL计算在内select count(*)from employees;select count(manager_id)from employees;*//*最大值 最小值函数使用select max(first_name)from employees;select min(first_name)from employees;*//*分组数据Group by和Having*//* group by 使用select job_id,count(*)as num_jobfrom employeesgroup by job_id*//* having函数使用对“列”过滤,再排序select job_id,count(*)as num_jobfrom employeesgroup by job_idhaving num_job10order by num_job;*//* 子查询select first_name,last_name,salaryfrom employeeswhere department_id in (select department_id from departmentswhere location_id 1700);*//*作为计算字段使用子查询select department_name,manager_id,(select count(*)from employeeswhere employees.department_iddepartments.department_id) as deparfrom departmentsorder by manager_id;*//* 内联结 选定的列位于不同的表如何选取如何使用表名的缩写-表别名select d.department_name,e.first_name,e.last_namefrom departments as d,employees as ewhere d.department_id e.department_id;*//* 自联结 同一张表中寻找拥有同种属性的对象--问题相同列会出现多次,下面代码结果同一个对象重复出现select e1.employee_id,e1.first_name,e1.last_name,e1.department_idfrom employees as e1,employees as e2where e1.department_ide2.department_idand e2.department_id100;*//*自然联结--避免自联结结果项重复出现重点在于采用另一张明确的表来联结select e.department_id,e.first_name,e.last_name,e.salaryfrom employees as e,departments as dwhere e.department_idd.department_idand d.department_id100;*//* 外联结-关键可以显示没有关联行的行注意left outer join指出语句左边表默认按左边表排序right outer join指出语句右边表默认按右边表排序select e.employee_id,e.first_name,e.last_name,d.department_name,e.department_idfrom employees as e left outer join departments as don e.department_idd.department_id;*//*使用带聚集函数的联结。select d.department_name,e.employee_id,count(e.department_id) as num_empfrom employees as e inner join departments as don e.department_idd.department_idgroup by e.department_id;*//* 组合查询多个查询条件类似OR的功能在面对多个表调用时采用这个语句结构会清晰一些。select e.employee_id,e.first_name,e.last_name,e.email,e.department_idfrom employees as ewhere e.department_id in (100,110,120)union select e.employee_id,e.first_name,e.last_name,e.email,e.department_idfrom employees as ewhere e.first_nameStevenorder by e.department_id;*//* 用OR语句替代上述组合查询语句实现相同功能select e.employee_id,e.first_name,e.last_name,e.email,e.department_idfrom employees as ewhere e.department_id in (100,110,120)or e.first_nameStevenorder by e.department_id;*//* 插入insert into employeess(*)values(01,Lynn,Zheng,lingling66zh,1888,AD_VP,17000,null,null,100,2020-08-25);*//*插入检索的数据insert into employees(*)select(*)from another_tbl;select *into custcopyfrom customer*//*更新数据update employeesset employee_id 250where employee_id 1;*//*删除数据-注意这里删除的是整行部分删除要使用update delete from employeeswhere employee_id 250;*//*创建表必须给出三个项 列名数据类型是否允许值为null如何给定默认值create table test.product250(prod_id char(10) not null,department_id int(4) default 1,prod_name char(254) default 1);*//*更改设定表,添加用add删除用drop column 列名alter table test.product250add prod_price int(10) default 1;*/
