MySQL經典語法炒雞詳細
- MySQL引言
- 1.登錄MySQL資料庫
- 一、資料庫操作相關
- 二、表相關
- 三、資料相關
- 四、約束
- 五、主鍵約束+自增
- 六、注釋
- 七、事務
- 八、資料庫資料型別
- 九、資料庫匯入匯出
- 十、IFNULL() 函式
- 十一、聚合函式（不能寫在where條件后面）
- 十二、字串相關函式(從1開始)
- 十三、數學相關的函式
- 十四、分類
- 十五、視圖
- 十六、索引
- 十七、事務
MySQL引言
引言：為了學習方便，本文章搭配資料庫檔案學習，方便學習者結合例子進行深入的掌握，更加深刻印象
鏈接：https://pan.baidu.com/s/1dQfXgCiyC2fs2MV9iXutjg
提取碼：20c5
1.登錄MySQL資料庫
mysql -u root -p
1.1 exit; //退出關閉資料庫
一、資料庫操作相關
2.查詢所有資料庫
show databases；
3.創建資料庫
create database 資料庫名；
4.查看資料庫的字符集
show create database 資料庫名；
5.創建資料庫且給定字符集
create database 資料庫名 character set utf8/gbk；
6.洗掉資料庫
drop database 資料庫名；
7.使用某一個資料庫
use 資料庫名；
8.資料庫匯入匯出
匯入：source e:/MySQL/lianxi/tables.sql (匯入資料庫前先創建個資料庫，再use，進行匯入這個資料庫)
匯出：mysqldump -u root -p 資料庫名 表名 > C:\path\path\dump.sql ----路徑
Windows 的斜桿是 \ ，Linux 的斜桿是 /
二、表相關
8.創建資料表（先use 資料庫名，再添加資料表）
create table 資料表名（欄位名 型別，欄位名 型別）；
9.查詢當前資料表
show create table 資料表名；
10.創建資料表并且給定表引擎和字符集
create table 資料表名（欄位名 型別） engine=myisam/innodb charset=utf8/gbk;
11.查看資料表所有欄位
desc 資料表名
12.洗掉資料表
drop table 資料表名;
13.查看當前資料庫的所有資料表（先use 資料庫名，再查看）
show tables；
13.1清空資料表所有資料 (洗掉表并創新表 自增數值清零)
truncate table 資料表名;
14.修改資料表名
rename table 資料表原名 to 新資料表名；
15.修改資料表的表引擎和字符集
alter table 資料表名 engine=myisam/innodb charset=utf8/gbk；
修改當前資料庫字符集（先use 當前資料庫）
alter database 資料庫名 character set 'utf8';
16.資料表添加欄位
16-1.alter table 表名 add 欄位名 資料型別； //資料表欄位末尾添加
16-2.alter table 表名 add 欄位名 資料型別 first； //資料表字符首位添加
16-3.alter table 表名 add 欄位名 資料型別 after 欄位名； //資料表某欄位后面添加
17.洗掉表欄位
alter table 表名 drop 欄位名；
18.修改欄位名和型別
alter table 表名 change 原名 新名 新型別；
19.修改欄位型別和位置
alter table 表名 modify 欄位名 新型別 first/after xxx；
三、資料相關
20.全表插入資料
insert into 表名 values (值1，值2);
21.指定欄位格式
insert into 表名 (欄位1,欄位2) values(值1,值2);
批量插入
21-1：insert into 表名 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
insert into 表名 (欄位1,欄位2) values(值1,值2),(值1,值2),(值1,值2);
22.查詢資料表的資料（先use 資料庫名，再查看）
select 欄位 from 表名;
select 欄位1,欄位2 from 表名;
select * from 表名 where 條件;
22_1. is null 和 is not null, 查詢為空(null)，和不為空的，
select ename from emp where mgr is not null;//查詢所有名字，且mgr不能為null的資料表emp
22_2. 加別名
select ename as '姓名',sal as '工資' from emp;
select ename '姓名',sal '工資' from emp;
select ename 姓名,sal 工資 from emp;
22_3. 去重復 distinct
select distinct job from emp;
22_4. 比較運算子
> < = >= <= !=和<>
注：資料量大時，加\G就行了，
22_5.
1.and，&& 需要同時滿足多個條件時使用
2.or，|| 需要滿足多個條件中某一個條件時使用
多個or用in ，例子：select sal,comm from emp where ename in('james','king','ford');
in ,not in
3.between x and y (包括xy)
例子：select * from emp where sal>=1000 and sal<=2000;
select * from emp where sal between 1000 and 2000;
4.模糊陳述句
like , not like _ 表示單個未知， % 表示0個或多個未知
SELECT 欄位 FROM 表 WHERE 某欄位 Like '條件'
4.1 [ ] ：表示括號內所列字符中的一個（類似正則運算式），指定一個字符、字串或范圍，要求所匹配物件為它們中的任一個
比如 SELECT * FROM [user] WHERE u_name LIKE '[張李王]三'
將找出“張三”、“李三”、“王三”（而不是“張李王三”）；
如 [ ] 內有一系列字符（01234、abcde之類的）則可略寫為“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
將找出“老1”、“老2”、……、“老9”；
4.2 [^ ] ：表示不在括號所列之內的單個字符，其取值和 [] 相同，但它要求所匹配物件為指定字符以外的任一個字符，
比如 SELECT * FROM [user] WHERE u_name LIKE '[^張李王]三'
將找出不姓“張”、“李”、“王”的“趙三”、“孫三”等；
22_6. 排序
order by 欄位名 asc/desc; ----asc升序，desc降序
例子：查詢所有員工資訊按照部門編號升序排序，工資降序，
SELECT * FROM emp ORDER BY deptno,sal DESC;
22_7. 分頁查詢
limit 跳過的條數，請求的條數（每頁的條數）
例子：1.查詢員工表工資最高的前五條資料
select * from emp order by sal desc limit 0,5;
第二頁資料
select * from emp order by sal desc limit 5,5;
limit (頁數-1)*每條的條數，每頁的條數
22_8.concat()函式 -----可以將字串進行拼接
1.1例子：
查詢商品表，顯示商品名稱，單價（價格：xx元）
select title,concat('價格:',price,'元') from t_item;
22_9.計算 + - * / %,mod()
2.1例子：
查詢員工的姓名，工資，年終獎（年終獎=工資*5）
select ename,sal,sal*5 年終獎 from emp;
22_10.日期相關函式
1.獲取系統當前日期+時間
select now();
2.獲取當前年月日 和 時分秒
select curdate(),curtime();
3.從完整的年月日時分秒中 提取年月日 和 提取時分秒
select date(now());
select time(now());
4.從完整的年月日時分秒中提取時間分量 extract
select extract(year/month/day/hour/minute/second from now());
5.日期格式化 date_format()
date_format(時間,格式);
%Y 四位年 %y 兩位年 %m 兩位月 %c 一位月 %d 日
%H 24小時 %h 12小時 %i 分 %s 秒
例子：把默認的時間格式轉為 年月日時分秒
select date_format(now(),'%Y年%m月%d日 %H時%i分%s秒');
6.把非標準時間格式轉出標準格式 str_to_date();
str_to_date(字串時間，格式);
select str_to_date('14.08.2019 08:00:00','%d.%m.%Y %H:%i:%s');
22_11.分組查詢
1.group by 欄位名,欄位名 ----（要對誰進行分組就寫誰的欄位名）
1-1例子：
1.查詢emp表中每個部門的編號，人數，工資總和，最后根據部門分組且根據人數進行升序排列，如果人數一致，根據工資總和降序排列，
select deptno,count(*),sum(sal) from emp group by deptno order by count(*),sum(sal) desc;
加別名:select deptno,count(*) c ,sum(sal) s from emp group by deptno order by c,s desc;
2.查詢工資在1000~3000之間的員工資訊，每個部門的編號，平均工資，最低工資，最高工資，根據平均工資進行升序排列，
select deptno,avg(sal),min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by avg(sal);
3.查詢含有上級領導的員工，每個職業的人數，工資的總和，平均工資，最低工資，最后根據人數進行降序排序，如果人數一致，根據平均工資進行升序排序，
select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null group by job order by c desc,a;
4.查詢每個部門每個主管的手下人數，
select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;
5.查詢每個部門的平均工資，要平均工資大于2000，
select deptno,avg(sal) a from emp group by deptno having a>2000;
(having后面可以寫普通字符條件，但是不建議這么做，having一般要和分組查詢結合使用，后面寫聚合函式的條件，having寫在分組查詢的后面)
6.查詢每個分類category_id的平均單價，要求平均單價低于100
select category_id,avg(price) a from t_item group by category_id having a<100;
7.查詢分類category_id為238和917的平均單價
select category_id,avg(price) a from t_item where category_id in(238,917) group by category_id;
8.查詢emp表中每個部門的平均工資高于2000的部門變你好，部門人數，平均工資，最后根據平均工資降序排序
select deptno,avg(sal),count(*) a from emp group by deptno having a>2000 order by a desc;
9.查詢emp表中工資1000~3000之間的員工，每個部門的編號，工資總和，平均工資，過濾掉平均工資低于2000的部門，按照平均工資進行升序排序
select deptno,sum(sal),avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a;
10.查詢emp表中職位不是以s開頭，每個職位的名字，人數，工資總和，最后作業，過濾掉平均工資3000的職位，根據人數升序排序，如果人數一致則工資總和降序排序，
select ename,count(*) c,sum(sal) s,max(sal) a from emp where job not like 'S%' group by job having avg(sal)!=3000 order by c,s desc;
11.查詢emp表中每年入職的人數
SELECT EXTRACT(YEAR FROM HIREdate) YEAR,COUNT(*) FROM emp GROUP BY YEAR;
12.查詢每個部門的最高平均工資
select deptno,avg(sal) a from emp group by deptno order by a desc limit 0,1;
22_12.子嵌套陳述句
1.1例子：
1.查詢emp中工資最高的員工資訊
select * from emp where sal=(select max(sal) from emp);
2.查詢emp表中工資大于平均工資的所有員工的資訊
select * from emp where sal>(select avg(sal) from emp);
3.查詢工資高于20號部門的最高工資的員工資訊，
select * from emp where sal>(select max(sal) from emp where deptno=20);
4.查詢和Jones相同作業（job）的其他員工資訊
select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
5.查詢工資最低的員工的同事們的資訊（同事=相同job，即：相同作業的員工資訊）
select min(sal) from emp
select job form emp where sal=(select min(sal) from emp)
select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
6.查詢最后入職的員工資訊
select max(hiredate) from emp;
select * from emp where hiredate=(select max(hiredate) from emp);
7.查詢名字為King的部門編號和部門名稱
select deptno,dname from dept where deptno=(select deptno from emp where ename='King');
8.查詢有員工的部門資訊
select distinct deptno from emp;
select * from dept where deptno in(select distinct deptno from emp);
9.查詢平均工資最高的部門資訊
9.1先找到最高平均工資
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
9.2通過最高平均工資得到部門編號
select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
9.3通過部門編號得到部門資訊
select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));
22_13.子查詢創建表
1.例子：
1.1：create table emp_10 as (select * from emp where deptno=10); -----把查詢到的資料存到一個新創建的資料表
22_14.子查詢虛擬表(當成虛擬包必須有別名)
1.例子：
1.1：select ename from (select * from emp where deptno=10) newtable;
22_15.關聯查詢：等值連接和內連接，外連接(同時查詢多張表的資料的查詢方式)
1.等值連接
格式：select * from A,B where A.x=B.x and A.age=10;
1.1例子：
1.查詢每個員工的姓名和所屬部門的名字
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
2.查詢部門地點在new york的 部門名稱以及該部門下所有的員工姓名
select d.dname,e.ename from emp e,dept d where e.deptno=d.deptno and d.loc='new york';
2.內連接
格式：select * from A join B on A.x=B.x where A.age=10;
2.1例子：
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
3.外連接(左外，右外)
格式：select * from A left/right join B on A.x=B.x where A.age=18;
3.1例子：查詢所有的部門名稱和對應的員工姓名（外連接）
select d.dname,e.ename
from emp e right join dept d on e.deptno=d.deptno;
總結：如果查詢的資料時兩張表的交集資料
22_16.組連接 group_concat
1).例子一：查詢每個部門的員工姓名和對應的工資，要求一個部門的資訊顯示到一行
select deptno,group_concat(ename,':',sal) from emp group by deptno;
2).例子二：
create table student(id int primary key auto_increment, name varchar(10),subject varchar(10),score varchar(10));
insert into student values(null,'張三','語文','66'),(null,'張三','數學','77'),(null,'張三','英語','55'),(null,'張三','計算機','81'),(null,'李四','語文','59'),(null,'李四','數學','88'),(null,'李四','英語','78'),(null,'張三','計算機','95'),(null,'投訓','語文','75'),(null,'投訓','數學','54'),(null,'投訓','英語','89'),(null,'投訓','計算機','75');
-- 查詢每個人的平均分，從大到小排序
select name '姓名',avg(score)'平均分' from student group by name order by '平均分';
-- 每個人的名字，科目，成績 一行顯示出來
select name,group_concat(subject,':',score) 各科成績 from student group by `name`;
-- 查詢每個人的最高分和最低分
select name,max(score)最高分,min(score)最低分 from student group by name;
-- 查詢每個人不及格的科目以及分數，不及格的科目數量
select name 姓名,group_concat(subject,':',score)不及格科目,count(*)門數 from student where score<60 group by name;
23.修改資料
update 表名 set 欄位名=值 where 條件;
例子：update person set name='bbb' where name='xxx';
24.洗掉資料
delete from 表名 where 條件;
25.解決中文欄位出現亂碼
set 欄位名(中文varchar) gbk;
四、約束
1.主鍵約束
主鍵：用于表示資料唯一性的欄位稱為主鍵，-----create table t1(id int primary key,name varchar(10));
約束：就是創建表的時候給欄位添加的限制條件
2.主鍵約束：插入資料必須是唯一且非空的
create table t1(id int primary key,name varchar(10));
insert into t1 values(1,'劉備');
insert into t1 values(1,'劉備’); //報錯 不能重復
insert into t1 values(null,'劉備'); //報錯 不能為null
3.洗掉主鍵(若有自增auto_increment,先洗掉自增auto_increment，再洗掉主鍵)
alter table 表名 change 欄位 欄位 型別;
alter table 表名 drop primary key;
添加主鍵(在添加主鍵之前,必須先把重復的id洗掉掉，)
alter table 表名 add primary key(欄位);
alter table 表名 modify 欄位 型別 auto_increment primary key; ------修改欄位為自增，并設定為主鍵
4.非空約束：
欄位的值不能為null
create table 表名 (id int, name varchar(10)not null);
5.唯一約束 unique
欄位的值不能重復
create table 表名 (id int, age int unique);
6.默認約束 default
給欄位設定默認值
create table 表名 (id int, age int default 20);
insert into 表名 values(1,10);
insert into 表名 (id) values(2); --------默認值生效
7.外鍵約束
外鍵：用于建立關系的欄位
外鍵約束：為了保證兩張表之間建立正確的關系，外鍵欄位的值可以為null，可以重復，
不能是另外一張表中不存在的資料，建立好關系后被依賴的資料不能先洗掉，
被依賴的表不能先洗掉，
格式：constraint 約束名稱 foreign key(外鍵欄位名) references 被依賴的表名(被依賴的欄位名);
五、主鍵約束+自增
自增數只增不減
從歷史最大值基礎上+1
1.create table t2(id int primary key auto_increment,name varchar(10));
insert into t2 values(null,'豬八戒');//1
insert into t2 values(null,'豬八戒');//2
insert into t2 values(10,'豬八戒');//10
insert into t2 values(null,'豬八戒');//11
六、注釋
create table t3(id int primary key auto_increment comment '主鍵欄位',name varchar(10) comment '這是姓名');
七、事務
事務是資料庫中執行同一業務多條sql陳述句的作業單位，可以保證多條SQL陳述句全部執行成功或者全部執行失敗，不會出現部分成功部分失敗的執行，
事務相關指令：
1.開始事務 begin;
2.提交事務 commit;
3.回滾事務 rollback;
4.保存回滾點 savepoint;
八、資料庫資料型別
1.整數型
int(m) ----m表示顯示長度，需要結合zerofill關鍵字使用，
bigint(m) ----- create table t_int(id int(5) zerofill);
insert into t_int values(18);
2.浮點數
double(m,d) ---m代表總長度 ---d代表小數長度 25.321 m=5 d=3
decimal超高度精度浮點數，當涉及超高精度運算時使用
3.字串
char(m) 固定長度 執行效率高 最大長度255
varchar(m)可變長度 節省資源 最大65535 超高255位建議使用text， text可變長度 最大65535
4.日期
date 只能保存年月日
time只能保存時分秒
datetime最大值9999-12-31 默認值為null
timestamp 最大值2038-1-19 默認值 當前系統時間
九、資料庫匯入匯出
匯入：source e:/MySQL/data/tables.sql -----------（windows系統）
十、IFNULL() 函式
10.1. IFNULL() 函式用于判斷第一個運算式是否為 NULL，
如果為 NULL 則回傳第二個引數的值，如果不為 NULL 則回傳第一個引數的值，
ifnull();
age = ifnull(x,y); 如果x值為null則age=y 如果x不為null則age=x
例子：把emp資料表中的獎金為null的改為0
update emp set comm = ifnull(comm,0);
十一、聚合函式（不能寫在where條件后面）
求和 平均數 最大值 最小值 計數
1.求和：sum
sum(求和的欄位)
2.平均值：avg
avg(欄位)
3.最大值：max
max(欄位)
4.最小值：min
min(欄位)
5.計數：count
count(欄位)
一般寫count(*)，只有涉及null值時才使用欄位名
十二、字串相關函式(從1開始)
1.char_length(str)獲取字串的長度
select ename,char_length(ename) from emp;
2.instr(str,substr) 獲取substr在str中出現的位置 從1開始
select instr('abcdefg','d');
3.insert(str,start,length,newstr); 插入
select insert('abcdefg',3,2,'m');
4.lower(str) upper(str) 轉小寫 轉大寫
select lower('NBa'),upper('NBa');
5.trim(str) 去兩端空白
select trim(' a b ');
6.left(str,index) 從左邊截取
7.right(str,index) 從右邊截取
8.substring(str,index,?length) 從指定位置截取
select left('abcdef',2);
select right('abcdef',2);
select substring('abcdef',3,2);
select substring('abcdef',3);
9.repeat(str,count) 重復
select repeat('ab',3); //ababab
10.replace(str,old,new) 替換
select replace('This is mysql','my','your'); //This is yoursql
11.reverse() 反轉
select reverse('abc'); //cba
十三、數學相關的函式
1.floor(num) 向下取整
select floor(3.84); //3
2.round(num) 四舍五入
select round(3.8); //4
3.round(num,m) 四舍五入并保留幾位小數 （m代表保留幾位小數）
select round(3.8679,2); //3.87
4.truncate(num,m) 非四舍五入并保留幾位小數 （m代表保留幾位小數）
select truncate(3.8679,2); //3.86
5.rand() 亂數0~1
例子1：獲取0~5的隨機整數
select floor(rand()*6);
例子2：獲取0~10的隨機整數
select floor(rand()*11);
十四、分類
1.DDL資料定義語言： create drop alter truncate 不支持事務
2.DML資料操作語言： insert delete update select 支持事務
3.DQL資料查詢語言： select
4.TCL事務控制語言： begin commit rollbakc savepoint
5.DCL資料控制語言： 分配用戶權限相關SQL
十五、視圖
一、試圖和表都是資料庫中的物件，視圖可以理解為一張虛擬的表，視圖本質就是取代了一段SQL查詢陳述句，
作用：可以起到SQL陳述句重用的作用，提高開發效率，還可以隱藏敏感資訊，
1.創建視圖
create view 視圖名 as (子查詢);
2.洗掉視圖
drop view 視圖名
3.修改視圖
create or replace view 視圖名 as (子查詢);
4.視圖別名
如果創建視圖的子查詢中使用了別名，那么對視圖進行操作時只會使用別名
3.例子：
create view v_emp_30 as (select ename name,sal from emp where deptno=30);
二、視圖的分類：
1.簡單視圖：創建視圖時的子查詢不包含：去重、函式、分組、關聯查詢創建的視圖稱為簡單視圖，可以對簡單視圖進行增刪改查操作
2.復雜視圖：和簡單視圖相反，只能對復雜視圖進行查詢操作，
三、資料污染：往視圖中插入一條視圖中不可見但是在原表中可見的資料稱為資料污染
通過with check option 關鍵字避免出現資料污染現象，
十六、索引
一、索引是資料庫中用于提高查詢效率的技術，作業原理類似目錄
有利也有弊，如果資料量比較小，有索引反而會降低查詢效率
1.創建索引
格式：create index 資料庫名 on 表名(欄位名(?欄位長度));
create index i_item_title on item2(title);
select * from item2 where title='100';
索引越多，不好，因為索引會占磁盤空間，
2.查看索引
格式：show index from 表名;
show index from item2;
3.洗掉索引
格式：drop index 索引名 on 表名;
drop index i_item_title on item2;
4.復合索引
通過多個欄位創建的索引稱為復合索引
create index i_item_title_price on item2(title,price);
十七、事務
一、資料庫中執行同一業務多條sql陳述句的作業單元，可以保證多條sql全部執行成功或全部執行失敗
1）事務的ACID特性，此特性是保證事務正確執行的四大基本元素
Atomicity： 原子性：最小不可拆分，保證全部成功或全部失敗
Consistency： 一致性：從一個一致性狀態到另外一個一致性狀態
Isolation： 隔離性：多個事務之間互不影響
Durability： 持久性：提交事務后資料持久保存在磁盤中
2）事務相關指令：
1.開始事務 begin;
2.提交事務 commit;
3.回滾事務 rollback;
4.保存回滾點 savepoint;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/192574.html
標籤：其他