本人SQL新手，五一期間自學(xué)了《SQL必知必會(huì)》一書，在此簡(jiǎn)要分享學(xué)習(xí)心得，若有差錯(cuò)，請(qǐng)各位大佬們多多指教呀！

本人的SQL學(xué)習(xí)計(jì)劃是先根據(jù)《SQL必知必會(huì)》一書入門，了解SQL的整體框架，語(yǔ)法等，在閑暇時(shí)間刷題鞏固。

首先奉上本人整理的SQL涉及的功能及語(yǔ)法的思維導(dǎo)圖，思維導(dǎo)圖中整理了各個(gè)SQL功能以及實(shí)現(xiàn)功能的代碼和函數(shù)等，還在不斷完善更新中~（本人新手，可能有部分功能理解有誤，請(qǐng)大家不吝賜教，(●'?'●)）

另外，本著學(xué)以致用的心態(tài)，本人在SQL學(xué)習(xí)過(guò)程中，堅(jiān)持碼代碼實(shí)現(xiàn)相關(guān)功能，文末也將奉上本人編寫的SQL代碼以及相關(guān)功能說(shuō)明~（工具是SQLyog，思路依據(jù)的是《SQL必知必會(huì)》一書），老規(guī)矩，同學(xué)們覺(jué)得有問(wèn)題或有優(yōu)化空間的話，請(qǐng)多多留言指教哇！

=======================================================================

/* 檢索固定位置和固定行數(shù)

SELECT *

FROM employees;

Select distinct employee_id

from employees;

select employee_id

from employees

limit 4,4

order by employee_id; */

/* 多列排序 第一個(gè)排序列的值不唯一，需要多列區(qū)分

select employee_id, first_name, last_name

from employees

order by first_name,last_name;

*/

/* 升序排序和降序排序

select first_name, last_name, salary

from employees

order by salary desc;

*/

/* 找到工資3000的員工；where 過(guò)濾指定的是行。

select first_name,last_name,salary

from employees

where salary >= 3000;

*/

/* 不匹配查詢--字符串要用單引號(hào)引用

select

first_name,last_name,salary

from

employees

where job_id = 'AD_VP' ;

*/

/* 范圍查詢

select first_name,last_name,salary

from employees

where salary between 3000 and 5000

order by salary desc;

*/

/* 查詢空值

select first_name,last_name,email

from employees

where email is null;

*/

/*邏輯操作符*/

/* AND 操作符

select first_name,last_name,salary,job_id

from employees

where job_id = 'IT_PROG' and salary >5000

order by salary;

*/

/* OR 操作符

select salary,first_name,last_name,job_id

from employees

where job_id ='IT_PROG'

or salary >=10000

order by salary;

*/

/* 求值順序--AND的優(yōu)先級(jí)更高，會(huì)優(yōu)先和其它語(yǔ)句組合，類似數(shù)學(xué)運(yùn)算的乘除；

OR類似加減，

可用圓括號(hào)（優(yōu)先級(jí)更高）強(qiáng)制改變運(yùn)算順序

select job_id, first_name,last_name,salary

from employees

where (job_id = 'IT_PROG'

or job_id = 'AD_VP')

and salary > 6000;

*/

/* IN 的用法————類似“集合”概念；完成類似 ”O(jiān)R“語(yǔ)句的操作，學(xué)會(huì)使用“IN”替代“OR”

select first_name,last_name,salary,job_id

from employees

where job_id in ('AD_VP','IT_PROG')

order by salary;

*/

/*

select first_name,last_name,salary,job_id

from employees

where not job_id in ('AD_VP','IT_PROG')

and salary > 10000

order by salary;

*/

/* 通配符% 匹配多個(gè)字符 搜百分號(hào)放字符前面表示以百分號(hào)后的字符結(jié)尾的量，放字符后面表示搜索以字符開頭的量

select *

from jobs

where job_title like '%manager%'

order by min_salary;

*/

/*通配符 下劃線 一個(gè)下劃線只匹配單個(gè)字符而不是多個(gè)字符

select *

from jobs

where job_title like '__________ Manager';

*/

/*組合使用

select *

from employees

where first_name NOT like 'N%'

or first_name Not like 'L%'

order by salary desc;

*/

/* 使用concat拼接兩列,并指定別名

select concat(first_name,'(',last_name,')')

from employees

as name

order by salary;

*/

/* 返回當(dāng)前日期

select now()

*/

/* 文本處理，改變字符串首字母大小寫

select first_name,lower(first_name)as first_name_locase

from employees

order by first_name_locase;

*/

/*搜索發(fā)音相同的字符

select first_name

from employees

where soundex(first_name)=soundex('vali');

*/

/* 提取時(shí)間 年

select first_name,last_name,salary

from employees

where year(hiredate)=2004

order by salary;

/* 提取時(shí)間 月

select first_name,last_name,salary

from employees

where month(hiredate)=3

order by salary;

*/

/* 提取時(shí)間 日

select first_name,last_name,salary

from employees

where day(hiredate)=3

order by salary;

*/

/* 求平均值，列名不是字符串

select avg(salary)

as avg_salary

from employees;

*/

/* 計(jì)數(shù)函數(shù)的使用，是否將NULL計(jì)算在內(nèi)

select count(*)

from employees;

select count(manager_id)

from employees;

*/

/*最大值 最小值函數(shù)使用

select max(first_name)

from employees;

select min(first_name)

from employees;

*/

/*分組數(shù)據(jù)，Group by和Having*/

/* group by 使用

select job_id,count(*)as num_job

from employees

group by job_id

*/

/* having函數(shù)使用，對(duì)“列”過(guò)濾,再排序

select job_id,count(*)as num_job

from employees

group by job_id

having num_job>=10

order by num_job;

*/

/* 子查詢

select first_name,last_name,salary

from employees

where department_id in (select department_id

from departments

where location_id = 1700);

*/

/*作為計(jì)算字段使用子查詢

select department_name,

manager_id,

(select count(*)

from employees

where employees.department_id=departments.department_id) as depar

from departments

order by manager_id;

*/

/* 內(nèi)聯(lián)結(jié) 選定的列位于不同的表，如何選取？如何使用表名的縮寫-表別名？

select d.`department_name`,e.first_name,e.last_name

from `departments` as d,employees as e

where d.`department_id` = e.`department_id`;

*/

/* 自聯(lián)結(jié) 同一張表中尋找擁有同種屬性的對(duì)象--問(wèn)題：相同列會(huì)出現(xiàn)多次,下面代碼結(jié)果同一個(gè)對(duì)象重復(fù)出現(xiàn)

select e1.`employee_id`,e1.first_name,e1.last_name,e1.department_id

from employees as e1,employees as e2

where e1.`department_id`=e2.`department_id`

and e2.`department_id`=100;

*/

/*自然聯(lián)結(jié)--避免自聯(lián)結(jié)結(jié)果項(xiàng)重復(fù)出現(xiàn)，重點(diǎn)在于采用另一張明確的表來(lái)聯(lián)結(jié)

select e.`department_id`,e.`first_name`,e.`last_name`,e.`salary`

from employees as e,departments as d

where e.`department_id`=d.`department_id`

and d.`department_id`=100;

*/

/* 外聯(lián)結(jié)-關(guān)鍵：可以顯示沒(méi)有關(guān)聯(lián)行的行；注意：left outer join指出語(yǔ)句左邊表（默認(rèn)按左邊表排序），right outer join指出語(yǔ)句右邊表（默認(rèn)按右邊表排序）

select e.`employee_id`,e.`first_name`,e.`last_name`,d.`department_name`,e.`department_id`

from employees as e left outer join departments as d

on e.`department_id`=d.`department_id`;

*/

/*使用帶聚集函數(shù)的聯(lián)結(jié)。

select d.`department_name`,e.`employee_id`,

count(e.`department_id`) as num_emp

from employees as e inner join departments as d

on e.`department_id`=d.`department_id`

group by e.department_id;

*/

/* 組合查詢，多個(gè)查詢條件，類似OR的功能，在面對(duì)多個(gè)表調(diào)用時(shí)，采用這個(gè)語(yǔ)句，結(jié)構(gòu)會(huì)清晰一些。

select e.`employee_id`,e.`first_name`,e.`last_name`,e.`email`,e.`department_id`

from employees as e

where e.`department_id` in (100,110,120)

union

select e.`employee_id`,e.`first_name`,e.`last_name`,e.`email`,e.`department_id`

from employees as e

where e.`first_name`='Steven'

order by e.`department_id`;

*/

/* 用OR語(yǔ)句替代上述組合查詢語(yǔ)句，實(shí)現(xiàn)相同功能

select e.`employee_id`,e.`first_name`,e.`last_name`,e.`email`,e.`department_id`

from employees as e

where e.`department_id` in (100,110,120)

or e.`first_name`='Steven'

order by e.`department_id`;

*/

/* 插入

insert into employeess(*)

values(01,

'Lynn',

'Zheng',

'lingling66zh',

'1888',

'AD_VP',

17000,

null,

null,

100,

2020-08-25);

*/

/*插入檢索的數(shù)據(jù)

insert into employees(*)

select(*)

from another_tbl;

select *

into custcopy

from customer

*/

/*更新數(shù)據(jù)

update employees

set employee_id =250

where employee_id =1;

*/

/*刪除數(shù)據(jù)-注意：這里刪除的是整行，部分刪除要使用update

delete from employees

where employee_id = 250;

*/

/*創(chuàng)建表，必須給出三個(gè)項(xiàng)： 列名，數(shù)據(jù)類型，是否允許值為null；如何給定默認(rèn)值

create table test.product250

(prod_id char(10) not null,

department_id int(4) default 1,

prod_name char(254) default 1);

*/

/*更改設(shè)定表,添加用add，刪除用drop column 列名

alter table test.`product250`

add prod_price int(10) default 1;

*/

總結(jié)

以上是生活随笔為你收集整理的sql 百分号_SQL思维导图和代码分享的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。