使用Navicat訪問mysql

實操1：使用Navicat訪問mysql的查詢語法

#切換數(shù)據(jù)庫use test;#查看test庫下面都有哪些表格show tables;#查看score表的內(nèi)容select * from score;#查詢score表中的姓名列select name from score;#查詢score表中的name列和subject列的內(nèi)容。不同列中間用英文逗號隔開。select name,subject from score;#查詢score表中name為曹操的所有內(nèi)容select * from score where name='曹操';#查詢score表中姓名為曹操的人的數(shù)據(jù)并篩選出科目（subject）和分數(shù)（score）select subject,score from score where name='曹操';#匯總以上用法，查找曹操的語文成績select name,subject,score from score where name='曹操' and subject='語文';#查找score表中劉備和小喬的名字和成績select name,score from score where name='劉備' or name='小喬';

連接數(shù)據(jù)庫的必要條件：IP、端口、用戶名、密碼

1.數(shù)據(jù)庫語句查詢語法：

select結(jié)果1,結(jié)果2,結(jié)果3 from 表名 where 字段=’字段1值’ or/and 字段2=’字段2值’;

2.查詢內(nèi)容項中間使用英文逗號隔開，查找條件之間使用and/or隔開。

*號代表查詢所有內(nèi)容。

— 或者#代表注釋，快捷方式：快速注釋：ctrl+/取消注釋ctrl+shift+/

例2：數(shù)據(jù)庫的模糊查詢（%代表占位零位或多位；_代表占位一位）

use test;select name from score where name like '孫%' ;  -- 查詢姓名以孫為開頭的人的姓名select name from score where name like '孫_';  -- 查詢姓名以孫為開頭的兩個字人的姓名select name from score where name like '%亞%';  -- 查詢姓名中包含亞的人的姓名select name from score where name like '%喬' ;  -- 查詢姓名中以喬結(jié)尾的人的姓名

例3：篩選條件

select * from student where age>=10;  -- 查詢年齡大于等于10歲的人的所有信息select * from student where age>=10 and age<=60; --  查詢年齡在10-60歲之間人的所有信息select * from student where age <30  or  age>50;    -- 查詢年齡小于30歲或者年齡大于50歲的人的所有信息select * from student where age !=30;  -- 查詢年齡不等于30歲的人的所有信息select * from student where age<>30;  -- 查詢年齡不等于30歲的人的所有信息

例3：去除重復

select distinct name from score;  --去除重復查詢score表中人的姓名。

例4：嵌套

Use test;#查詢名字為曹操劉備孫權(quán)的姓名和分數(shù)。select ,name,score from score where name in('曹操','劉備','孫權(quán)'); #查詢名字不是曹操劉備孫權(quán)的姓名和分數(shù)。select name,score from score where name not in('曹操','劉備','孫權(quán)'); #查詢學生表中家庭地址為空的人的信息select * from student where home is null;#查詢學生表中家庭地址不為空的人的信息select * from student where home is not null;

例4：between .A and B的用法（包含A和B）。

-- 查詢做出學生表中年齡在20-50之間的人的信息（包含20和50）select*from student where age between 20 and 50;

例5：排序order by的用法(排序默認升序，兩個排序條件中字段間用英文逗號隔開)

select結(jié)果1,結(jié)果2,結(jié)果3 from 表名 where 條件 order by 字段1 asc,字段2 desc’;#按照年齡由大到小輸出性別為男的這些人的所有信息select*from student where sex='女' order by age desc;#按照年齡由小到大如果年齡一樣按照序號由大到小輸出性別為男的這些人信息select*from student where sex='男' order by age asc,id desc;

例6：取前幾行內(nèi)容limit的用法(limit后直接跟數(shù)字代表取前幾行，跟數(shù)字,數(shù)字代表去除前幾行向下取幾行內(nèi)容)

#取前四行內(nèi)容select * from student limit 4;#從第3行起取4行（取第四行到第七行）內(nèi)容select * from student limit 3,4;

例7：聚合函數(shù)（avg,sum,max,min,count）

select avg(age) from student;select sum(age) from student;select max(age) from student;select min(age) from student;select count(id)from student;#求男生中年齡最大的那個人select name,max(age) from student where sex='男';

例8：重命名as可以省略as用空格代替（臨時用的時候在查詢結(jié)果中顯示為新名字）

select name as 姓名,id as 序號,age 年齡 from student as st;#輸出學生表中的name并重命名為姓名,age并重命名為年齡，分數(shù)表中的name并重命名為大名select student.name as 姓名,student.age as 年齡,score.name as 大名 from student as st,score as sc;#可以簡寫為select st.name 姓名,st.age 年齡,sc.name 大名from student st,score sc ;

例9：多表聯(lián)查（查詢時多個表格中至少要有一種相同信息）

#查詢學生表與分數(shù)表中名字相同的人的所有信息select * from student st join score sc on st.name=sc.name;

例10：分組

USE test;SELECT *FROM score WHERE name='周瑜';#直接求周瑜的所有成績的分均分數(shù)（where用于分組之前的條件篩選）SELECT name 姓名,AVG(score) 平均分 FROM score WHERE  name='周瑜';#將同樣姓名的人分成一組進行分組，然后求平均分數(shù)(HAVING用在分組之后的條件篩選)SELECT name,avg(score) FROM score GROUP BY name HAVING name = '周瑜';#求各個科目的平均成績SELECT subject,AVG(score) FROM score GROUP BY subject;#求每個科目的總分，并按照總分由大到小進行排序SELECT subject,SUM(score) FROM score GROUP BY subject ORDER BY SUM(score) DESC;#求總分是前三名的人的姓名SELECT name FROM score GROUP BY name ORDER BY SUM(score) DESC LIMIT 3;#除了曹操和周瑜之外所有人總分的前三名SELECT name FROM score WHERE name NOT IN('曹操','周瑜') GROUP BY name ORDER BY SUM(score) DESC LIMIT 3;SELECT name FROM score GROUP BY name HAVING name NOT IN('曹操','周瑜') ORDER BY SUM(score) DESC LIMIT 3;Select最終查詢語法（查詢用到的最長語法）：select結(jié)果1,結(jié)果2,結(jié)果3 from 表名 where 條件 group by 字段 having 條件 order by 字段1 asc,字段2 desc’ limit 開始行數(shù),結(jié)束行數(shù);

增加、修改和刪除表格的語法

1. 創(chuàng)建表格：

Create table表名（字段一 字段類型（長度）,字段2字段類型（長度））；Primary key 主鍵：主鍵不能為空，且無法重復;

2.增加內(nèi)容：

Insert into表名（字段1,字段2，字段3）values('字段1的值’，’值2’，’值3’);（加內(nèi)容時，每個字段都寫上，并且每個字段的值對應(yīng)每個字段）

3.修改數(shù)據(jù)（如果不加where會改變所有行的數(shù)據(jù)，一定要加上這個條件）：

Update 表名 set 字段1=’字段1新值’,字段2=’字段2新值’where 修改行的某個字段=’修改行的某個字段的對應(yīng)值’;

4.刪除數(shù)據(jù)

（delete和from之間不帶,如果不加上條件會刪除所有數(shù)據(jù)；Delete一般與where一起使用刪除部分數(shù)據(jù)，truncate用來清空表內(nèi)數(shù)據(jù)、表格結(jié)構(gòu)還在，drop直接刪除表內(nèi)所有數(shù)據(jù)，連帶表格也會刪除。刪除速度：drop>truncate>delete）

Delete from 表名 where條件;Truncate table 表名;Drop table 表名;Alter table 表名 add column 字段名 字符類型（字符大?。?；#增加一列Alter table 表名 modify column 字段名 字符類型（字符大?。?；#修改一列的字符類型Alter table 表名 change column 原字段名 新字段名 字符類型（字符大?。?修改字段名稱Alter table 表名 drop column 字段名; #刪除一列內(nèi)容Alter table 原表名 rename to 新表名 #表格重命名DESC 表名; #查看表結(jié)構(gòu)Select*from 表名1,表名2 #將兩個表拼接起來（笛卡爾積）

創(chuàng)建表格Lee

CREATE table Lee(id INT PRIMARY KEY,yingxiong CHAR(100),pifu VARCHAR(100),changci INT,victory CHAR(10));SELECT*FROM Lee;#在表里添加內(nèi)容 INSERT INTO 用法INSERT INTO Lee(id,yingxiong,pifu,changci,victory) VALUES('1','亞瑟','死亡騎士','500','266');UPDATE Lee SET changci='10000'WHERE yingxiong='黃忠';#修改數(shù)據(jù)DELETE FROM Lee WHERE id='10';/TRUNCATE TABLE Lee;/DROP TABLE Lee;#刪除數(shù)據(jù)ALTER TABLE lee ADD 勝率 CHAR(6);#添加一列ALTER TABLE lee MODIFY COLUMN 勝率 VARCHAR(12);#修改字段的數(shù)據(jù)類型ALTER TABLE lee CHANGE 勝率 percent CHAR(10);#修改字段名稱ALTER TABLE lee DROP COLUMN percent;#刪除一列ALTER TABLE wzry RENAME to lee#重命名DESC lee;#查看表結(jié)構(gòu)#將兩個表拼接起來（笛卡爾積）Select*from student st,teacher  order by st.name desc;

子查詢，即嵌套，由內(nèi)層向外層查詢。(可以將一個很復雜的內(nèi)容來分步執(zhí)行，簡化問題）

1.把sql語句當成一個條件

#從student表和score表中查詢出共有的語文分數(shù)在50分以上的人的姓名、年齡、家庭住址。（使用兩個表中共有的字段名作為橋梁）SELECT name,age,home FROM student WHERE name IN(SELECT name FROM score WHERE subject='語文' AND score>50);

2.把sql語句當成一個表格處理（當成表格處理必須臨時給其命名）

#把分數(shù)表按人名分組，然后列出每個人的總分，再對總分按照降序排序，然后取出前三行數(shù)據(jù)。即求分數(shù)表中科目總分最高的人的前三名的人的所有信息。SELECT * from(SELECT * from (SELECT name,sum(score) 總分 from score GROUP BY name) st ORDER BY st.總分 desc) stt LIMIT 3;

多表聯(lián)查（查詢時多個表格中至少要有一種相同信息）

1.內(nèi)連接（內(nèi)聯(lián)查，過濾掉不共有信息/不顯示不共有的信息）inner可以省略

#查詢學生表與分數(shù)表中名字相同的人的所有信息select * from student st join score sc on st.name=sc.name;select * from student st inner join score sc on st.name=sc.name;

2. 外連接(外連查，分為左外連接（左連接）右外連接（右連接）)outer可以省略

#左連接（左邊的表（student表）不共有數(shù)據(jù)保留）SELECT*FROM student st LEFT JOIN score sc ON st.name=sc.name;SELECT*FROM student st LEFT OUTER JOIN score sc ON st.name=sc.name;#右連接（右邊的表（score表）不共有數(shù)據(jù)保留）SELECT*FROM student st RIGHT JOIN score sc ON st.name=sc.name;SELECT*FROM student st RIGHT OUTER JOIN score sc ON st.name=sc.name;#從student表和score表中查詢出語文分數(shù)在50分以上的人的姓名、年齡、家庭住址。SELECT st.name,st.age,st.home FROM student st JOIN score sc on st.name=sc.name WHERE sc.subject='語文' AND sc.score>50;

內(nèi)連接、左連接和右連接的區(qū)別：

內(nèi)連接就是把2個表里相同的內(nèi)容輸出，不同的內(nèi)容過濾掉。左連接就是以左表為基礎(chǔ)先把左表的信息全部輸出，再把右表里和左表里相匹配的內(nèi)容輸出，不匹配的以空值的形式出現(xiàn)。右連接就是以右表為基礎(chǔ)先把右表的信息全部輸出，再把左表里和右表里相匹配的內(nèi)容的輸出，不匹配的以空值的形式出現(xiàn)。

多表聯(lián)查中三個表以上的聯(lián)查。

#多表連查，需要一個表作為中間表，中間表有上表的字段名也有下表的字段名起承上啟下的作用。#查找student表和score表和teacher表中共同的信息。SELECT*FROM student st JOIN score sc ON st.name=sc.name JOIN teacher te ON sc.subject=te.subject;SELECT*FROM student st LEFT JOIN score sc ON st.name=sc.name LEFT JOIN teacher te ON sc.subject=te.subject;格式化時間Select *from 表名 where date_format(時間對應(yīng)的字段名, ’ %Y_%m_%d ’ )=’xxxx_xxx_xx’;#格式化時間: year年 month月 day日SELECT*FROM mt WHERE DATE_FORMAT(create_time,'%Y_%m_%d')='2018_03_30';

數(shù)據(jù)分頁

SELECT*FROM mt LIMIT(頁碼-1)*行數(shù),行數(shù)；#查看某一頁的數(shù)據(jù)#分頁SELECT*FROM zt_user;SELECT*FROM zt_user LIMIT 0,5;SELECT*FROM zt_user LIMIT 5,5;SELECT*FROM zt_user LIMIT 10,5;SELECT*FROM zt_user LIMIT 15,5;事務(wù)的語法：Start transaction;Update 表名set 字段名1=’字段1的值’ where 字段a=字段a1的值;Update 表名set 字段名2=’字段2的值’ where 字段a=字段a2的值;Commit;

使用Navicat訪問sqlserver

實操2：使用Navicat訪問sqlserver的查詢語法

Sqlserver 里的注釋使用 /* 注釋內(nèi)容*/  或者 - - 注釋內(nèi)容Seletct top 幾行 字段名 from 表名 where 條件;

Sqlserver 里的注釋使用 /* 注釋內(nèi)容*/  或者 - - 注釋內(nèi)容Seletct top 幾行 字段名 from 表名 where 條件;

使用plsqlt訪問oracle

更新數(shù)據(jù)：

select * from student;/*增加*/insert into student values('020','趙云','88','男','16652368965','蜀國');commit;/*刪除*/delete from student where id = '020';commit;/*修改*/update student set age = '55' where name = '曹操';commit;#將表格變?yōu)榭删庉嫚顟B(tài)第一步：#將表格變?yōu)榭删庉嫚顟B(tài),輸入Select*from student for update;Commit;第二步：點擊表格左上角小鎖，將鎖定解除，然后對數(shù)據(jù)進行編輯第三步：修改完畢數(shù)據(jù)后（增刪改），點擊對勾，再點擊小鎖鎖定表格。然后點擊齒輪執(zhí)行。

格式化時間：

select*from 表名to_char(時間對應(yīng)字段名,xxxx_xx_xx)=’1998_05_03’;--格式化時間 年：yyyy 月：mm 日：ddselect *from scott.emp;select *from scott.emp where to_char(hiredate,'yyyy')='1981';select *from scott.emp where to_char(hiredate,'yyyy_mm_dd')='1981_12_03';
 --取前幾行select *from student where rownum<=3;select *from student where rownum<=9;commit;--取第幾行到第幾行--取第三行到第八行的內(nèi)容select*from(select rownum r,st.*from student st) b where b.r >=3 and b.r<=8;

數(shù)據(jù)庫理論知識：

數(shù)據(jù)庫的概念：

SQL 結(jié)構(gòu)化查詢語言

數(shù)據(jù)庫的類型：

關(guān)系型數(shù)據(jù)庫：Mysql SQLserver Oracle DB2 access Informix VF非關(guān)系型數(shù)據(jù)庫：redis mongodb

LAMP：

即Linux(操作系統(tǒng)) apache（中間件） MySQL（數(shù)據(jù)庫） PHP（編程語言）

語法區(qū)別(會背 )

這三個數(shù)據(jù)庫都采用結(jié)構(gòu)化查詢語言取前幾行不同：Mysql用的是 limit, sqlserver 用的是 top, Oracle用的是rownumoracle數(shù)據(jù)在進行數(shù)據(jù)變更時需要進行 commit 操作（數(shù)據(jù)變更：增加 刪除 修改）

數(shù)據(jù)庫的存儲過程：

是一個預編譯的SQL語句，優(yōu)點是允許模塊化的設(shè)計，用戶只需創(chuàng)建一次，以后可以反復調(diào)用。

索引：加快數(shù)據(jù)的查詢，類似于一本書的目錄

索引是一個特殊的查詢表，可以利用索引加快數(shù)據(jù)的檢索速度。索引類似于一本書的目錄，在書籍中，通過目錄可以不必翻閱整本書就能快速從書中找到所需要的信息，而索引就是在數(shù)據(jù)庫中可以快速找到所需要表格中的數(shù)據(jù)而不必掃描所有的數(shù)據(jù)庫就可以找到所需要的數(shù)據(jù)。索引可以是唯一的，創(chuàng)建索引允許指定單個列或者多個列。缺點是他減慢了數(shù)據(jù)的錄入速度，同時也增加了數(shù)據(jù)庫的尺寸大小。

刪除速度drop > truncate >delete

主鍵與外鍵

主鍵:數(shù)據(jù)庫表中對存儲數(shù)據(jù)對象予以唯一和完整標識的數(shù)據(jù)列（主鍵不能為空不能重復） 外鍵：A表中有一個name（非A的主鍵），B表中也有一個name(B表的主鍵)，那么A里的name就是B表的外鍵

事務(wù)：

是數(shù)據(jù)庫中一組邏輯操作，這個操作的特點是要么全部成功，要么全部失敗。 Start transaction;

Start transaction;Update 表名set 字段名1=’字段1的值’ where 字段a=字段a1的值;Update 表名set 字段名2=’字段2的值’ where 字段a=字段a2的值;Commit;

Start transaction;

Update 表名set 字段名1=’字段1的值’ where 字段a=字段a1的值; Update 表名set 字段名2=’字段2的值’ where 字段a=字段a2的值; Commit;

什么是外連接：指左外連接與右外連接

內(nèi)連接就是把2個表里相同的內(nèi)容輸出，不同的內(nèi)容過濾掉。左連接就是以左表為基礎(chǔ)先把左表的信息全部輸出，再把右表里和左表里相匹配的內(nèi)容輸出，不匹配的以空值的形式出現(xiàn)。右連接與之相反。

用命令查詢表結(jié)構(gòu)：Desc 表名;

數(shù)據(jù)庫中去重復的關(guān)鍵字：distinct

多表聯(lián)查最多能聯(lián)查多少張表格：只要有相同的字段就可以進行多表聯(lián)查

多表（無關(guān)聯(lián)的）拼接：select*from 表a,表b;

數(shù)據(jù)庫的執(zhí)行計劃（查詢計劃）：

數(shù)據(jù)庫在執(zhí)行sql語句的時候，會準備幾套方案，最后會選擇資源消耗最小的那個方案執(zhí)行，就是執(zhí)行計劃。

三大數(shù)據(jù)庫的區(qū)別：

MYSQL

AB公司（甲骨文公司）開發(fā)的 默認端口號：3306 客戶端連接工具：Navicat，SQLyog 1.關(guān)系型數(shù)據(jù)庫 2.開源的3.快速可靠易于使用 4.可跨平臺 5.很多創(chuàng)業(yè)型的公司都在使用

SQLserver

微軟開發(fā)的 默認端口號：1433 客戶端連接工具：navicat 只能運行在windows上 電子商務(wù) 銀行 保險 電力這些行業(yè)也使用SQLserver

Oracle

甲骨文公司開發(fā) 默認端口號：1521 客戶端連接工具：plsql 1.支持多用戶，大事務(wù)量處理 2.數(shù)據(jù)安全性比較高 3.可跨平臺

三大數(shù)據(jù)庫語法的區(qū)別： 1.這三個數(shù)據(jù)庫都采用結(jié)構(gòu)化查詢語言 1.取前幾行不同：Mysql用的是limit, sqlserver用的是top, Oracle用的是rownum 3.oracle數(shù)據(jù)在進行數(shù)據(jù)變更時需要進行commit操作（數(shù)據(jù)變更：增加 刪除 修改）

倒序是哪里的關(guān)鍵詞：是數(shù)據(jù)庫里的（desc）。

Oracle里的for update是怎么使用的： 將oracle數(shù)據(jù)表變?yōu)榭删庉嫚顟B(tài) 1. 執(zhí)行for update 語句eg:select*from a for update; 2. 點開小鎖 3. 修改數(shù)據(jù) 4. 點擊對號 5. 關(guān)閉小鎖 6. 點擊提交

剛接觸工作應(yīng)該怎么辦：

1.先問公司用什么數(shù)據(jù)庫oracle、mysql、sqlserver哪種 2.然后下軟件或者給同事要安裝包安裝到工作的電腦上 3.然后問公司業(yè)務(wù)所需要的數(shù)據(jù)庫有哪些，賬號、端口號、IP是什么。需要的表格有哪些，表格里有哪些字段，每個字段代表什么意思。然后用筆記本或者記事本把這些信息記下來 4.編寫sql語句并添加注釋，寫完SQL之后把文件保存下來以后重復調(diào)用。