|
Web-第六天 MySQL回顧學(xué)習(xí) 數(shù)據(jù)庫就是存儲(chǔ)數(shù)據(jù)的倉庫,，其本質(zhì)是一個(gè)文件系統(tǒng)，數(shù)據(jù)按照特定的格式將數(shù)據(jù)存儲(chǔ)起來,，用戶可以通過sql語句對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行增加,，修改，刪除及查詢操作 關(guān)系數(shù)據(jù)庫(Relationship DataBase Management System 簡寫:RDBMS) ,，描述是建立在關(guān)系模型基礎(chǔ)上的數(shù)據(jù)庫,，借助于集合代數(shù)等數(shù)學(xué)概念和方法來處理數(shù)據(jù)庫中的數(shù)據(jù)。說白了就是描述實(shí)體與實(shí)體之間的關(guān)系的數(shù)據(jù)庫.例如用戶購物下訂單,訂單包含商品.他們之間的關(guān)系可以通過E-R圖表示. 數(shù)據(jù)庫發(fā)展歷程如下所示： 沒有數(shù)據(jù)庫,，使用磁盤文件存儲(chǔ)數(shù)據(jù),； 層次結(jié)構(gòu)模型數(shù)據(jù)庫； 網(wǎng)狀結(jié)構(gòu)模型數(shù)據(jù)庫,； 關(guān)系結(jié)構(gòu)模型數(shù)據(jù)庫,，使用二維表格來存儲(chǔ)數(shù)據(jù)； 關(guān)系-對(duì)象模型數(shù)據(jù)庫,； Oracle數(shù)據(jù)庫：Oracle是殷墟（yīn Xu）出土的甲骨文（oracle bone inscriptions）的英文翻譯的第一個(gè)單詞,，在英語里是“神諭”的意思,，由Larry Ellison 和另兩個(gè)編程人員在1977創(chuàng)辦,，他們開發(fā)了自己的拳頭產(chǎn)品,，在市場(chǎng)上大量銷售，1979 年,，Oracle公司引入了第一個(gè)商用SQL 關(guān)系數(shù)據(jù)庫管理系統(tǒng),。Oracle公司是最早開發(fā)關(guān)系數(shù)據(jù)庫的廠商之一，其產(chǎn)品支持最廣泛的操作系統(tǒng)平臺(tái),。目前Oracle關(guān)系數(shù)據(jù)庫產(chǎn)品的市場(chǎng)占有率名列前茅,。 SQL Server數(shù)據(jù)庫：1987 年，微軟和 IBM合作開發(fā)完成OS/2,，IBM 在其銷售的OS/2 ExtendedEdition 系統(tǒng)中綁定了OS/2Database Manager,，而微軟產(chǎn)品線中尚缺少數(shù)據(jù)庫產(chǎn)品。為此,，微軟將目光投向Sybase,，同Sybase 簽訂了合作協(xié)議，使用Sybase的技術(shù)開發(fā)基于OS/2平臺(tái)的關(guān)系型數(shù)據(jù)庫,。1989年,，微軟發(fā)布了SQL Server 1.0 版。 DB2數(shù)據(jù)庫： 作為關(guān)系數(shù)據(jù)庫領(lǐng)域的開拓者和領(lǐng)航人,，IBM在1997年完成了System R系統(tǒng)的原型,，1980年開始提供集成的數(shù)據(jù)庫服務(wù)器—— System/38,，隨后是SQL/DSforVSE和VM,，其初始版本與SystemR研究原型密切相關(guān),。DB2 forMVSV1 在1983年推出。該版本的目標(biāo)是提供這一新方案所承諾的簡單性,，數(shù)據(jù)不相關(guān)性和用戶生產(chǎn)率,。1988年DB2 for MVS 提供了強(qiáng)大的在線事務(wù)處理（OLTP）支持，1989 年和1993 年分別以遠(yuǎn)程工作單元和分布式工作單元實(shí)現(xiàn)了分布式數(shù)據(jù)庫支持,。最近推出的DB2 Universal Database 6.1則是通用數(shù)據(jù)庫的典范,，是第一個(gè)具備網(wǎng)上功能的多媒體關(guān)系數(shù)據(jù)庫管理系統(tǒng)，支持包括Linux在內(nèi)的一系列平臺(tái),。 Sybase數(shù)據(jù)庫：Sybase公司成立于1984年,，公司名稱“Sybase”取自“system”和 “database” 相結(jié)合的含義。Sybase公司的創(chuàng)始人之一Bob Epstein 是Ingres 大學(xué)版（與System/R同時(shí)期的關(guān)系數(shù)據(jù)庫模型產(chǎn)品）的主要設(shè)計(jì)人員,。公司的第一個(gè)關(guān)系數(shù)據(jù)庫產(chǎn)品是1987年5月推出的Sybase SQLServer1.0,。Sybase首先提出Client/Server 數(shù)據(jù)庫體系結(jié)構(gòu)的思想，并率先在Sybase SQLServer 中實(shí)現(xiàn),。 MySQL數(shù)據(jù)庫： mySQL是一個(gè)小型關(guān)系型數(shù)據(jù)庫管理系統(tǒng),，開發(fā)者為瑞典MySQL AB公司。在2008年1月16號(hào)被Sun公司收購,。而2009年,，Sun公司又被Oracle公司收購。目前MySQL被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中,。由于其體積小,、速度快、總體擁有成本低,，尤其是開放源碼這一特點(diǎn),，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站數(shù)據(jù)庫 我們通常將數(shù)據(jù)庫管理系統(tǒng)(DataBase Management System,簡寫dbms)稱為數(shù)據(jù)庫,大白話就是我們安裝的軟件，當(dāng)我們安裝了數(shù)據(jù)庫之后（數(shù)據(jù)庫服務(wù)器）,，就可以在數(shù)據(jù)庫服務(wù)器中創(chuàng)建數(shù)據(jù)庫,，每個(gè)數(shù)據(jù)庫中還可以包含多張表. 通過上述圖我們知道了數(shù)據(jù)庫與表之間的關(guān)系，那么我們的數(shù)據(jù)又是怎樣存儲(chǔ)在數(shù)據(jù)庫中,。 數(shù)據(jù)庫中的表就是一個(gè)多行多列的表格,。在創(chuàng)建表時(shí)，需要指定表的列數(shù),，以及列名稱,，列類型等信息。而不用指定表格的行數(shù),，行數(shù)是沒有上限的,。 表中的列我們稱之為字段,，表中的行我們稱之為記錄。 用我們熟悉的java程序來與關(guān)系型數(shù)據(jù)對(duì)比,，就會(huì)發(fā)現(xiàn)以下對(duì)應(yīng)關(guān)系,。 類-------表 類中屬性-------表中的字段 對(duì)象--------記錄。 mysql的安裝與配置詳見附件:MySQL的安裝.doc http://www.下載 進(jìn)入命令窗口 格式:mysql [-h主機(jī)地址] -u用戶名 -p[密碼] 啟動(dòng)mysql服務(wù)命令 net start mysql 關(guān)閉mysql服務(wù)命令 net stop mysql a. 停止mysql服務(wù)器 運(yùn)行輸入services.msc 停止mysql服務(wù) b. 在cmd下,輸入mysqld --console --skip-grant-tables 啟動(dòng)服務(wù)器,出現(xiàn)一下頁面,不要關(guān)閉該窗口 c. 新打開cmd,輸入mysql -uroot 不需要密碼 use mysql; update user set password=password('abc') WHERE User='root'; d. 關(guān)閉兩個(gè)cmd窗口 SQL:結(jié)構(gòu)化查詢語言(Structured Query Language),是一種數(shù)據(jù)庫查詢和程序設(shè)計(jì)語言,，用于存取,、查詢、更新數(shù)據(jù)以及管理關(guān)系數(shù)據(jù)庫系統(tǒng). 用來定義數(shù)據(jù)庫對(duì)象：數(shù)據(jù)庫,，表,，列等，例如創(chuàng)建,、刪除,、修改數(shù)據(jù)庫和表結(jié)構(gòu)等； 用來對(duì)數(shù)據(jù)庫中表的記錄進(jìn)行更新,，例如：增,、刪、改表記錄,； 用來查詢數(shù)據(jù)庫中表的記錄,。 用來定義數(shù)據(jù)庫的訪問權(quán)限和安全級(jí)別，及創(chuàng)建用戶,； DDL是對(duì)數(shù)據(jù)庫或表的結(jié)構(gòu)進(jìn)行操作,，而DML是對(duì)表的記錄進(jìn)行操作(增、刪,、改),。我們以后最常用的就是DDL、DML,、DQL,。 SQL語句可以單行或多行書寫，以分號(hào)結(jié)尾 MySQL數(shù)據(jù)庫的SQL語句不區(qū)分大小寫,，建議使用大寫,，例如：SELECT * FROM user 例如:create database day04;-- 使用數(shù)據(jù)庫默認(rèn)字符集. 擴(kuò)展: 格式: create database [if not exists] 數(shù)據(jù)庫名稱 [character set 字符集] [collate 校對(duì)規(guī)則]; 例如: create database day0401 character set utf8;-- 使用指定的編碼創(chuàng)建數(shù)據(jù)庫 create database day0402 character set utf8 collate utf8_general_ci;-- 使用指定的編碼和校對(duì)規(guī)則創(chuàng)建數(shù)據(jù)庫 例如:drop database day0402; 修改數(shù)據(jù)庫編碼或者校對(duì)規(guī)則 例如:alter database day0401 character set gbk;-- 修改其字符集為gbk 例如:show create database day04; 例如:use day04; java中的數(shù)據(jù)類型 mysql中的數(shù)據(jù)類型 備注 byte tinyint short smallint int int long bigint float float double double double(m,d) m數(shù)字長度，d精度及小數(shù)位,double(5,2)表示它的最大值是：999.99 String char varchar() char固定長度的字符串.默認(rèn)255,如果存儲(chǔ)的字符沒有達(dá)到指定長度,，mysql將會(huì)在其后面用空格補(bǔ)足到指定長度,； varchar可變長度的字符串,長度可以由我們自己指定，它能保存數(shù)據(jù)長度的最大值是65535,，如果存儲(chǔ)的字符沒有達(dá)到指定的長度,，不會(huì)補(bǔ)足到指定長度； java.sql.Date date 日期,格式為yyyy-MM-dd java.sql.Time time 時(shí)間,格式為hh:mm:ss java.sql.Timestamp timestamp 時(shí)間戳,格式'YYYY-MM-DD HH:MM:SS'.若設(shè)置為空,將該列設(shè)置為當(dāng)前的日期和時(shí)間；特點(diǎn)：當(dāng)更新一條數(shù)據(jù)時(shí) 這條數(shù)據(jù)中有一個(gè)字段是時(shí)間戳 那么這個(gè)時(shí)間戳的值會(huì)自動(dòng)更新 datetime 時(shí)間,日期,格式'YYYY-MM-DD HH:MM:SS' 大文本 Clob tinytext 255B text 64kb longtext 4gb 大數(shù)據(jù) Blob tinyblob 255B blob 64kb longblob 4gb 字段描述格式: 字段名稱 字段類型 [字段約束] 例如: create table user( id int, username varchar(50), password varchar(20) ); 練習(xí): 創(chuàng)建一個(gè)張表user,該表具有以下字段: id 整型 主鍵 自動(dòng)增長 username 字符串 長度20 非空 password 字符串 長度20 非空 gender 字符串 長度10 email 字符串 長度50 唯一 非空 role 字符串 長度10 默認(rèn)值 “admin” registTime 時(shí)間戳 create table user( id int primary key auto_increment, username varchar(20), password varchar(20), gender varchar(10), email varchar(50), role varchar(10), registTime timestamp ); 例如： #1,，為分類表添加一個(gè)新的字段為 分類描述 varchar(20) ALTER TABLE category ADD `desc` VARCHAR(20); 例如： #2, 為分類表的描述字段進(jìn)行修改,，類型varchar(50) 添加約束 not null ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL; 例如： #3, 為分類表的分類名稱字段進(jìn)行更換 更換為 snamesname varchar(30) ALTER TABLE category CHANGE `desc` description VARCHAR(30); 例如： #4, 刪除分類表中snamename這列 ALTER TABLE category DROP description; 例如： #5, 為分類表category 改名成 category2 RENAME TABLE category TO category2; 例如： #6, 為分類表 category 的編碼表進(jìn)行修改，修改成 gbk ALTER TABLE category CHARACTER SET gbk; 例如:drop table user; 例如:desc user; 例如: show create table user01; PRIMARY KEY 約束唯一標(biāo)識(shí)數(shù)據(jù)庫表中的每條記錄,。 主鍵必須包含唯一的值,。 主鍵列不能包含 NULL 值。 每個(gè)表都應(yīng)該有一個(gè)主鍵,，并且每個(gè)表只能有一個(gè)主鍵,。 CREATE TABLE Persons ( Id_P int PRIMARY KEY, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) 格式：[constraint 名稱] primary key (字段列表) 關(guān)鍵字constraint可以省略,，如果需要為主鍵命名，constraint不能省略,，主鍵名稱一般沒用,。 字段列表需要使用小括號(hào)括住，如果有多字段需要使用逗號(hào)分隔,。聲明兩個(gè)以上字段為主鍵,，我們稱為聯(lián)合主鍵。 CREATE TABLE Persons ( FirstName varchar(255), LastName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (FirstName,LastName) ) 或 CREATE TABLE Persons ( FirstName varchar(255), LastName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (FirstName,LastName) ) ALTER TABLE Persons ADD [CONSTRAINT 名稱] PRIMARY KEY (字段列表) CREATE TABLE Persons ( FirstName varchar(255), LastName varchar(255), Address varchar(255), City varchar(255) ) ALTER TABLE Persons ADD PRIMARY KEY (FirstName,LastName) 如需撤銷 PRIMARY KEY 約束，請(qǐng)使用下面的 SQL： ALTER TABLE Persons DROP PRIMARY KEY 我們通常希望在每次插入新記錄時(shí),，數(shù)據(jù)庫自動(dòng)生成字段的值,。 我們可以在表中使用 auto-increment（自動(dòng)增長列）關(guān)鍵字，自動(dòng)增長列類型必須是整形,，自動(dòng)增長列必須為鍵(一般是主鍵),。 下列 SQL 語句把 "Persons" 表中的 "P_Id" 列定義為 auto-increment 主鍵 CREATE TABLE Persons ( P_Id int PRIMARY KEY AUTO_INCREMENT, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) 向persons添加數(shù)據(jù)時(shí)，可以不為P_Id字段設(shè)置值,，也可以設(shè)置成null,，數(shù)據(jù)庫將自動(dòng)維護(hù)主鍵值： INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates') INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (NULL,'Bill','Gates') 面試：delete和truncate的區(qū)別 Delete刪除表中的數(shù)據(jù)，但不重置auto-increment記錄數(shù),。 Truncate刪除表中的數(shù)據(jù),，auto-increment記錄數(shù)將重置。Truncate其實(shí)先刪除表然后再創(chuàng)建表,。 擴(kuò)展：默認(rèn)地,，AUTO_INCREMENT 的開始值是 1，如果希望修改起始值，請(qǐng)使用下列 SQL 語法： ALTER TABLE Persons AUTO_INCREMENT=100 NOT NULL 約束強(qiáng)制列不接受 NULL 值,。 NOT NULL 約束強(qiáng)制字段始終包含值,。這意味著，如果不向字段添加值,，就無法插入新記錄或者更新記錄,。 下面的 SQL 語句強(qiáng)制 "Id_P" 列和 "LastName" 列不接受 NULL 值： CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) UNIQUE 約束唯一標(biāo)識(shí)數(shù)據(jù)庫表中的每條記錄。 UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證,。 PRIMARY KEY 擁有自動(dòng)定義的 UNIQUE 約束,。 請(qǐng)注意，每個(gè)表可以有多個(gè) UNIQUE 約束,，但是每個(gè)表只能有一個(gè) PRIMARY KEY 約束,。 CREATE TABLE Persons ( Id_P int UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) CREATE TABLE Persons ( Id_P int, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT 名稱UNIQUE (Id_P) ) ALTER TABLE Persons ADD [CONSTRAINT 名稱] UNIQUE (Id_P) 如需撤銷 UNIQUE 約束，請(qǐng)使用下面的 SQL： ALTER TABLE Persons DROP INDEX 名稱 如果添加唯一約束時(shí),，沒有設(shè)置約束名稱,，默認(rèn)是當(dāng)前字段的字段名。 還可以使用default,給定一個(gè)默認(rèn)值. 注意：不插入該字段時(shí) 使用默認(rèn)值 FOREIGN KEY 表示外鍵約束,，將在多表中學(xué)習(xí) id 主鍵自增長 username 字符串 長度20 不能為空 password 字符串 長度20 不能為空 gender 字符串10 age 整型 默認(rèn)25 email 字符串 長度50 非空 唯一 salary 浮點(diǎn)型 長度8其中2位小數(shù) state 整型 默認(rèn)值0 dept 字符串長度10 默認(rèn)咨詢部 registTime 日期 create table user( id int primary key auto_increment, username varchar(20) not null, password varchar(20) not null, gender varchar(10), age int default 25, email varchar(50) not null unique, salary double(8,2), state int default 0, dept varchar(10) default '咨詢部', registTime date ); 格式:insert into 表名 values(字段值1,字段值2,...); 例如:insert into user values(null,'laobai','1234','male','[email protected]',null,null); 注意: 沒有給出要插入的列,，表示插入所有列； 值的個(gè)數(shù)必須是該表的列的個(gè)數(shù),、插入的值與列名相對(duì)應(yīng),； 值的順序，必須與表創(chuàng)建時(shí)給出的列的順序相同,。 格式:insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...); 例如:insert into user(username,password,email,registTime) values('laoqi','1234','[email protected]',null); 注意:表名后面是當(dāng)前表中部分字段名稱 格式: insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...); 例如: insert into user(id,username,password,gender,email,role,registTime) values(1,'shijin','1234','male','[email protected]',null,null); 注意: 表名后面是當(dāng)前表中所有字段(列) 多個(gè)字段之間使用逗號(hào)分隔 字段值必須使用引號(hào)（建議單引號(hào)）,如果是整型數(shù)據(jù)引號(hào)可以省略,。 插入中文的時(shí)候會(huì)報(bào)錯(cuò),如下圖: 因?yàn)閙ysql的客戶端編碼的問題我們的是utf8,而系統(tǒng)的cmd窗口編碼是gbk. 查看所有的mysql編碼 show variables like 'character%'; 解決方案: 1.臨時(shí)修改 set 變量名稱=gbk; 例如: set character_set_client=gbk,character_set_connection=gbk,character_set_results=gbk; 重啟mysql服務(wù)器后,就回復(fù)原樣了. 2.永久修改 在mysql安裝目錄下, update 表名 set 字段名1=字段值1, 字段名2=字段值2, ..... ; update 表名 set 字段名1=字段值1, 字段名2=字段值2, ..... [where 條件]; 例如: 1.update user set gender='male' ;## 修改所有人的性別為’male’ 2.update user set username='xusanduo',email='[email protected]' where id=3;## 修改id為3的人username和email. 列名的類型與修改的值要一致. 修改值得時(shí)候不能超過最大長度. 除了數(shù)值類型外，其它的字段類型的值必須使用引號(hào)引起 格式:delete from 表名 [where 條件]; 例如: 1.delete from user;## 刪除所有 2.delete from user where id=6; ##刪除id為6的記錄. 1.delete from 表名; 2.truncate [table] 表名; 區(qū)別: 1.delete屬于DML語句 ,truncate屬于DDL語句 2.delete是一行一行刪除,truncate是將表結(jié)構(gòu)銷毀,再重新創(chuàng)建表結(jié)構(gòu),數(shù)據(jù)多的時(shí)候,效率高. 刪除方式：delete 一條一條刪除,，不清空auto_increment記錄數(shù),。受事務(wù)控制。 truncate 直接將表刪除,，重新建表,，auto_increment將置為零，從新開始,。不受事務(wù)控制 #創(chuàng)建商品表： create table product( pid int primary key auto_increment, pname varchar(20), price double, category_id varchar(32) ); INSERT INTO product(pid,pname,price,category_id) VALUES(1,'聯(lián)想',5000,'c001'); INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海爾',3000,'c001'); INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001'); INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002'); INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真維斯',200,'c002'); INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002'); INSERT INTO product(pid,pname,price,category_id) VALUES(7,'勁霸',2000,'c002'); INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈兒',800,'c003'); INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003'); INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003'); INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你棗',56,'c004'); INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飄飄奶茶',1,'c005'); INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL); select [distinct] * | 列名,列名 from 表 where 條件 例如：查詢商品名和商品價(jià)格. select pname,price from product; 例如： 查詢所有的商品. select * from product; 注意:使用"*"在練習(xí),，學(xué)習(xí)過程中可以使用,，在實(shí)際開發(fā)中，不建議使用,。 例如：去掉重復(fù)值. select distinct price from product; 代表當(dāng)pname與price都重復(fù)時(shí)才去重兩個(gè)條件 select distinct pname,price from product; 注意:distinct它的作用是去除重復(fù). 別名查詢.使用的關(guān)鍵字是as（as可以省略的）. 1.表別名: select * from product as p; 2.列別名：select pname as pn from product; 我們?cè)?/span>sql操作中,，可以直接對(duì)列進(jìn)行運(yùn)算。 例如：查詢結(jié)果是表達(dá)式（運(yùn)算查詢）：將所有商品的價(jià)格+10元進(jìn)行顯示. select pname,price+10 from product; 注意：null進(jìn)行運(yùn)算結(jié)果還是null,，想要null運(yùn)算有值,，則使用ifnull函數(shù)即可； 在對(duì)數(shù)值類型的列做運(yùn)算的時(shí)候,，如果做運(yùn)算的列的值為null的時(shí)，運(yùn)算結(jié)果都為null,，為了解決這個(gè)問題可以使用ifnull函數(shù),使用方法 ifnull(字段,0) 比較運(yùn)算符 > < <= >= = <> 大于,、小于、大于(小于)等于,、不等于 BETWEEN ...AND... 顯示在某一區(qū)間的值(含頭含尾) IN(set) 顯示在in列表中的值,，例：in(100,200) LIKE ‘張pattern’ 模糊查詢，Like語句中,， % 代表零個(gè)或多個(gè)任意字符， _ 代表一個(gè)字符,， 例如：first_name like ‘_a%’; IS NULL 判斷是否為空 邏輯運(yùn)算符 and 多個(gè)條件同時(shí)成立 or 多個(gè)條件任一成立 not 不成立,，例：where not(salary>100); null值操作 is null; 判斷為空 is not null; 判斷不為空 單條件查詢 #查詢商品名稱為“花花公子”的商品所有信息： SELECT * FROM product WHERE pname = '花花公子' #查詢價(jià)格為800商品 SELECT * FROM product WHERE price = 800 #查詢價(jià)格不是800的所有商品 SELECT * FROM product WHERE price != 800 SELECT * FROM product WHERE price <> 800 SELECT * FROM product WHERE NOT(price = 800) #查詢商品價(jià)格大于60元的所有商品信息 SELECT * FROM product WHERE price > 60; 范圍查詢和BETWEEN AND #查詢商品價(jià)格在200到1000之間所有商品 SELECT * FROM product WHERE price >= 200 AND price <=1000; SELECT * FROM product WHERE price BETWEEN 200 AND 1000; and or和枚舉in查詢 #查詢商品價(jià)格是200或800的所有商品 SELECT * FROM product WHERE price = 200 OR price = 800; SELECT * FROM product WHERE price IN (200,800); 模糊查詢 #查詢含有'霸'字的所有商品 SELECT * FROM product WHERE pname LIKE '%霸%'; #查詢以'香'開頭的所有商品 SELECT * FROM product WHERE pname LIKE '香%'; #查詢第二個(gè)字為'想'的所有商品 SELECT * FROM product WHERE pname LIKE '_想%'; 空查詢 #商品沒有分類的商品 SELECT * FROM product WHERE category_id IS NULL #查詢有分析的商品 SELECT * FROM product WHERE category_id IS NOT NULL 通過order by語句,，可以將查詢出的結(jié)果進(jìn)行排序,。放置在select語句的最后。 格式:可以單字段和多字段排序 SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC; ASC 升序 (默認(rèn)) DESC 降序 #1.使用價(jià)格排序(降序) SELECT * FROM product ORDER BY price DESC; #2.在價(jià)格排序(降序)的基礎(chǔ)上,，以分類排序(降序) SELECT * FROM product ORDER BY price DESC,category_id DESC; #3.顯示商品的價(jià)格(去重復(fù)),，并排序(降序) SELECT DISTINCT price FROM product ORDER BY price DESC; 之前我們做的查詢都是橫向查詢，它們都是根據(jù)條件一行一行的進(jìn)行判斷,，而使用聚合函數(shù)查詢是縱向查詢,，它是對(duì)一列的值進(jìn)行計(jì)算，然后返回一個(gè)單一的值,；另外聚合函數(shù)會(huì)忽略空值,。 今天我們學(xué)習(xí)如下五個(gè)聚合函數(shù)： count：統(tǒng)計(jì)指定列不為NULL的記錄行數(shù)； sum：計(jì)算指定列的數(shù)值和,，如果指定列類型不是數(shù)值類型,，那么計(jì)算結(jié)果為0； max：計(jì)算指定列的最大值,，如果指定列是字符串類型,，那么使用字符串排序運(yùn)算； min：計(jì)算指定列的最小值，如果指定列是字符串類型,，那么使用字符串排序運(yùn)算,； avg：計(jì)算指定列的平均值，如果指定列類型不是數(shù)值類型,，那么計(jì)算結(jié)果為0,； 注意：聚合函數(shù)忽略null的存在； 注意：聚合函數(shù)一般情況下（除了分組的字段外）不與其他不字段一起查詢,； #1 查詢商品的總條數(shù) SELECT COUNT(*) FROM product; #2 查詢價(jià)格大于200商品的總條數(shù) SELECT COUNT(*) FROM product WHERE price > 200; #3 查詢分類為'c001'的所有商品的總和 SELECT SUM(price) FROM product WHERE category_id = 'c001'; #4 查詢分類為'c002'所有商品的平均價(jià)格 SELECT AVG(price) FROM product WHERE category_id = 'c002'; #5 查詢商品的最大價(jià)格和最小價(jià)格 SELECT MAX(price),MIN(price) FROM product; 分組查詢是指使用group by字句對(duì)查詢信息進(jìn)行分組,。 格式： SELECT 字段1,字段2… FROM 表名 GROUP BY分組字段 HAVING 分組條件; 分組操作中的having子語句，是用于在分組后對(duì)數(shù)據(jù)進(jìn)行過濾的,，作用類似于where條件,。 having與where的區(qū)別: having是在分組后對(duì)數(shù)據(jù)進(jìn)行過濾. where是在分組前對(duì)數(shù)據(jù)進(jìn)行過濾 having后面可以使用分組函數(shù)(統(tǒng)計(jì)函數(shù)) where后面不可以使用分組函數(shù)。 #1 統(tǒng)計(jì)各個(gè)分類商品的個(gè)數(shù) SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ; #2 統(tǒng)計(jì)各個(gè)分類商品的個(gè)數(shù),且只顯示個(gè)數(shù)大于1的信息 SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1; 分頁查詢相應(yīng)的sql是方言(不同的數(shù)據(jù)庫特有的語法sql) mysql：limit index,length oracle: rownum sqlServer: top select * from user limit 0,3; 推斷：頁面與起始索引的關(guān)系 頁數(shù) 每頁條數(shù) 起始索引 關(guān)系 1 3 0 (1-1)*3 2 3 3 (2-1)*3 3 3 6 (3-1)*3 4 3 9 (4-1)*3 公式： 起始索引 = (頁數(shù)-1)*每頁顯示條數(shù) 綜合我們學(xué)習(xí)的查詢相關(guān)關(guān)鍵字：select,，from,，where，group by,，having,，order by；它們的執(zhí)行順序是如下： lfrom：首先執(zhí)行from,，找到要查詢的表,； lwhere：判斷條件，篩選符合條件所有記錄,； lgroup by：根據(jù)之前操作對(duì)記錄按照指定列進(jìn)行分組 lhaving：對(duì)分組后的信息進(jìn)行篩選,； lselect：選擇所需要的列信息； lorder by：對(duì)查詢信息進(jìn)行排序,。 在SQL語言中,，第一個(gè)被處理的子句是from字句，盡管select字句最先出現(xiàn),，但是幾乎總是最后被處理,。 id 主鍵自增長 username 字符串 長度20 不能為空 password 字符串 長度20 不能為空 gender 字符串10 age 整型 默認(rèn)25 email 字符串 長度50 非空 唯一 salary 浮點(diǎn)型 長度8其中2位小數(shù) state 整型 默認(rèn)值0 dept 字符串長度10 默認(rèn)咨詢部 registTime 日期 create table user( id int primary key auto_increment, username varchar(20) not null, password varchar(20) not null, gender varchar(10), age int default 25, email varchar(50) not null unique, salary double(8,2), state int default 0, dept varchar(10) default '咨詢部', registTime date ); insert into user values(null,'張青青','zs123','男',18,'zs@',5000,1,'財(cái)務(wù)部','2015-09-10 12:18:38'); insert into user values(null,'邊海鵬','bhp','男',32,'bhp@',4500,0,'咨詢部','2015-09-12 12:18:38'); insert into user values(null,'張淑敏','753951','女',42,'zsm@',5200,1,'技術(shù)部','2015-09-18 12:18:38'); insert into user values(null,'楊志勇','pangzi','男',29,'yzy@',null,0,'技術(shù)部','2015-08-10 12:18:38'); insert into user values(null,'范瑾','fj123','女',22,'fj@',3000,1,'咨詢部','2014-09-19 12:18:38'); insert into user values(null,'多博','db123','女',25,'db@',8000,0,'技術(shù)部','2014-09-10 12:18:38'); insert into user values(null,'張章婉航','zsf123','女',58,'zsff@',12000,0,'技術(shù)部','2015-03-28 12:18:38'); insert into user values(null,'楊秀清','yxq123','女',42,'yxq@',5900,1,'財(cái)務(wù)部','2015-09-10 12:18:38'); insert into user values(null,'孫志剛','szg123','男',18,'szg@',8000,0,'財(cái)務(wù)部','2015-09-10 12:18:38'); insert into user values(null,'張英','wj123','男',33,'zyj@',8500,1,'技術(shù)部','2014-12-12 12:18:38'); 代表當(dāng)gender與username都重復(fù)時(shí)才去重 select age+50 as '年齡' from user; select salary+1000 '薪資' from user; 注意：null進(jìn)行運(yùn)算結(jié)果還是null 注意：函數(shù) ifnull(字段,值) --- 當(dāng)字段為null時(shí) 賦值為多少 select ifnull(salary,0)+1000 '薪資' from user; count():計(jì)數(shù) sum():求和 avg():平均值 max():最大值 min():最小值 注意：聚合函數(shù)忽略null的存在 注意：聚合函數(shù)一般情況下(除了分組的字段外)不與其他字段一起查詢 select * from user order by salary; select * from user order by salary,age desc;多字段排序 select * from user where age<35; = != <> >= <= > < 查詢年齡小于35并且性別是女的 select * from user where age<35 and gender='女'; 查詢年齡小于35并且性別是女的,，或者性別是男工資大于8000 select * from user where age<35 and gender='女' or gender='男' and salary>8000; 注意：and的優(yōu)先級(jí)大于or select * from user where salary is null; select * from user where salary is not null; 查詢工資大于等于3000并且小于等于8000的 select * from user where salary>=3000 and salary<=8000; select * from user where salary between 3000 and 8000; 查詢14年注冊(cè)的人 select * from user where registTime>='2014-01-01 00:00:00' and registTime<='2014-12-31 23:59:59'; select * from user where registTime between '2014-01-01 00:00:00' and '2014-12-31 23:59:59'; select * from user where age=18 or age=22 or age=25; select * from user where age in(18,22,25); %:代表任意多個(gè)字符 _:代表任意一個(gè)字符 select * from user where username like '張%'; select * from user where username like '張__'; select * from user where username like '___'; 查詢每個(gè)部門的總薪資 select sum(salary) from user group by dept; select dept,sum(salary) from user group by dept; select dept,sum(salary) from user group by dept having sum(salary)>10000; having 與 where的區(qū)別？ where對(duì)分組前的篩選 having對(duì)分組后的數(shù)據(jù)篩選 select dept,sum(salary) from user where salary>5000 group by dept having sum(salary)>10000; 分頁查詢相應(yīng)的sql是方言(不同的數(shù)據(jù)庫特有的語法sql) mysql：limit index,length oracle: rownum sqlServer: top select * from user limit 0,3; 推斷：頁面與起始索引的關(guān)系 頁數(shù) 每頁條數(shù) 起始索引 關(guān)系 1 3 0 (1-1)*3 2 3 3 (2-1)*3 3 3 6 (3-1)*3 4 3 9 (4-1)*3 公式： 起始索引 = (頁數(shù)-1)*每頁顯示條數(shù) 數(shù)據(jù)庫的備份是指將數(shù)據(jù)庫轉(zhuǎn)換成對(duì)應(yīng)的sql文件,。 數(shù)據(jù)庫導(dǎo)出sql腳本的格式： mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫名>生成的腳本文件路徑 例如: mysqldump -uroot -p1234 day0401>d:\day04.sql 以上備份數(shù)據(jù)庫的命令中需要用戶名和密碼,，即表明該命令要在用戶沒有登錄的情況下使用 數(shù)據(jù)庫的恢復(fù)指的是使用備份產(chǎn)生的sql文件恢復(fù)數(shù)據(jù)庫，即將sql文件中的sql語句執(zhí)行就可以恢復(fù)數(shù)據(jù)庫內(nèi)容,。因?yàn)閿?shù)據(jù)庫備份只是備份了數(shù)據(jù)庫內(nèi)容,，所以備份產(chǎn)生的sql文件中沒有創(chuàng)建數(shù)據(jù)庫的sql語句,，在恢復(fù)數(shù)據(jù)庫之前需要自己動(dòng)手創(chuàng)建數(shù)據(jù)庫。 在數(shù)據(jù)庫外恢復(fù) 格式:mysql -uroot -p密碼 數(shù)據(jù)庫名 < 文件路徑 例如:mysql -uroot -p1234 day0401<d:\day04.sql 注意:要求數(shù)據(jù)庫必須先創(chuàng)建出來. 在數(shù)據(jù)庫內(nèi)恢復(fù) 格式:source SQL腳本路徑 例如:source d:\day0401.sql 注意:使用這種方式恢復(fù)數(shù)據(jù),，首先要登錄數(shù)據(jù)庫. 選中數(shù)據(jù)庫,，右鍵 ”備份/導(dǎo)出” ， 指定導(dǎo)出路徑,，保存成.sql文件即可,。 數(shù)據(jù)庫列表區(qū)域右鍵“從SQL轉(zhuǎn)儲(chǔ)文件導(dǎo)入數(shù)據(jù)庫”,， 指定要執(zhí)行的SQL文件，執(zhí)行即可,。 實(shí)際開發(fā)中，一個(gè)項(xiàng)目通常需要很多張表才能完成,。例如：一個(gè)商城項(xiàng)目就需要分類表(category),、商品表(products),、訂單表(orders)等多張表。且這些表的數(shù)據(jù)之間存在一定的關(guān)系,，接下來我們將在單表的基礎(chǔ)上，一起學(xué)習(xí)多表方面的知識(shí),。 現(xiàn)在我們有兩張表“分類表”和“商品表”,，為了表明商品屬于哪個(gè)分類，通常情況下,，我們將在商品表上添加一列,，用于存放分類cid的信息,，此列稱為：外鍵 此時(shí)“分類表category”稱為：主表,，“cid”我們稱為主鍵?！吧唐繁韕roducts”稱為：從表,，category_id稱為外鍵。我們通過主表的主鍵和從表的外鍵來描述主外鍵關(guān)系,，呈現(xiàn)就是一對(duì)多關(guān)系,。 外鍵特點(diǎn)： 從表外鍵的值是對(duì)主表主鍵的引用,。 從表外鍵類型,，必須與主表主鍵類型一致,。 聲明外鍵約束 語法：alter table 從表 add [constraint] [外鍵名稱] foreign key (從表外鍵字段名) references 主表 (主表的主鍵); [外鍵名稱] 用于刪除外鍵約束的,，一般建議“_fk”結(jié)尾 alter table 從表 drop foreign key 外鍵名稱 使用外鍵目的: 保證數(shù)據(jù)的一致性和完整性 常見實(shí)例：客戶和訂單,，分類和商品,，部門和員工. 一對(duì)多建表原則：在從表(多方)創(chuàng)建一個(gè)字段，字段作為外鍵指向主表(一方)的主鍵. 常見實(shí)例：學(xué)生和課程,、用戶和角色 多對(duì)多關(guān)系建表原則：需要?jiǎng)?chuàng)建第三張表,中間表中至少兩個(gè)字段,，這兩個(gè)字段分別作為外鍵指向各自一方的主鍵. 在實(shí)際的開發(fā)中應(yīng)用不多.因?yàn)橐粚?duì)一可以創(chuàng)建成一張表. 兩種建表原則： 外鍵唯一：主表的主鍵和從表的外鍵（唯一），形成主外鍵關(guān)系,，外鍵唯一unique。 外鍵是主鍵：主表的主鍵和從表的主鍵,，形成主外鍵關(guān)系,。 實(shí)際開發(fā)中表與表的關(guān)系包括如下三種： 一對(duì)一： 一對(duì)一的兩張表一般都可以合并成為一張表,，但基于如下兩個(gè)原因可能會(huì)將一張表拆分張兩張一對(duì)一關(guān)系的表 原因一：要使兩張表的語義更加明確,，例如用戶表和用戶帳戶表 原因二：將經(jīng)常查詢的字段放到一張表中,，不經(jīng)常查詢的數(shù)據(jù)放到一張表中 一對(duì)多： 如：類別表和商品表、用戶表和訂單表 一對(duì)多建表原則：在多的一方（從表）中創(chuàng)建外鍵與一的一方（主表）的主鍵進(jìn)行關(guān)聯(lián),。 父子關(guān)系,。 多對(duì)多： 如：訂單表與商品表、用戶表和角色表,、角色表和功能表 多對(duì)多的建表原則：在兩張表的中間建立一個(gè)關(guān)系表維護(hù)兩張表的多余多的關(guān)系 注意：怎么看兩張表的關(guān)系,？ 看一條分方向 category分類表，為一方,，也就是主表，必須提供主鍵cid products商品表，為多方,，也就是從表,，必須提供外鍵category_id ###創(chuàng)建分類表 create table category( cid varchar(32) PRIMARY KEY , cname varchar(100) #分類名稱 ); # 商品表 CREATE TABLE `products` ( `pid` varchar(32) PRIMARY KEY , `name` VARCHAR(40) , `price` DOUBLE ); #添加外鍵字段 alter table products add column category_id varchar(32); #添加約束 alter table products add constraint product_fk foreign key (category_id) references category (cid); #1 向分類表中添加數(shù)據(jù) INSERT INTO category (cid ,cname) VALUES('c001','服裝'); #2 向商品表添加普通數(shù)據(jù),沒有外鍵數(shù)據(jù)，默認(rèn)為null INSERT INTO products (pid,pname) VALUES('p001','商品名稱'); #3 向商品表添加普通數(shù)據(jù),，含有外鍵信息(數(shù)據(jù)存放在) INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名稱2','c001'); #4 向商品表添加普通數(shù)據(jù),，含有外鍵信息(數(shù)據(jù)不存在) -- 不能異常 INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名稱2','c999'); #5 刪除指定分類(分類被商品使用) -- 執(zhí)行異常 DELETE FROM category WHERE cid = 'c001'; 商品和訂單多對(duì)多關(guān)系,，將拆分成兩個(gè)一對(duì)多。 products商品表,，為其中一個(gè)一對(duì)多的主表,，需要提供主鍵pid orders 訂單表，為另一個(gè)一對(duì)多的主表,，需要提供主鍵oid orderitem中間表,，為另外添加的第三張表，需要提供兩個(gè)外鍵oid和pid ### 商品表[已存在] ### 訂單表 create table `orders`( `oid` varchar(32) PRIMARY KEY , `totalprice` double #總計(jì) ); ### 訂單項(xiàng)表 create table orderitem( oid varchar(50),-- 訂單id pid varchar(50)-- 商品id ); ###---- 訂單表和訂單項(xiàng)表的主外鍵關(guān)系 alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid); ###---- 商品表和訂單項(xiàng)表的主外鍵關(guān)系 alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid); ### 聯(lián)合主鍵（可省略） alter table `orderitem` add primary key (oid,pid); #1 向商品表中添加數(shù)據(jù) INSERT INTO products (pid,pname) VALUES('p003','商品名稱'); #2 向訂單表中添加數(shù)據(jù) INSERT INTO orders (oid ,totalprice) VALUES('x001','998'); INSERT INTO orders (oid ,totalprice) VALUES('x002','100'); #3向中間表添加數(shù)據(jù)(數(shù)據(jù)存在) INSERT INTO orderitem(pid,oid) VALUES('p001','x001'); INSERT INTO orderitem(pid,oid) VALUES('p001','x002'); INSERT INTO orderitem(pid,oid) VALUES('p002','x002'); #4刪除中間表的數(shù)據(jù) DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002'; #5向中間表添加數(shù)據(jù)(數(shù)據(jù)不存在) -- 執(zhí)行異常 INSERT INTO orderitem(pid,oid) VALUES('p002','x003'); #6刪除商品表的數(shù)據(jù) -- 執(zhí)行異常 DELETE FROM products WHERE pid = 'p001'; 方案1：多張表,，一對(duì)多 方案2：一張表，自關(guān)聯(lián)一對(duì)多 多對(duì)多關(guān)系 多對(duì)多關(guān)系 一對(duì)多：一個(gè)客戶服務(wù)于多個(gè)聯(lián)系人 CREATE TABLE category ( cid VARCHAR(32) PRIMARY KEY , cname VARCHAR(50) ); CREATE TABLE products( pid VARCHAR(32) PRIMARY KEY , pname VARCHAR(50), price INT, flag VARCHAR(2), #是否上架標(biāo)記為：1表示上架,、0表示下架 category_id VARCHAR(32), CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid) ); #分類 INSERT INTO category(cid,cname) VALUES('c001','家電'); INSERT INTO category(cid,cname) VALUES('c002','服飾'); INSERT INTO category(cid,cname) VALUES('c003','化妝品'); #商品 INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','聯(lián)想',5000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海爾',3000,'1','c001'); INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真維斯',200,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','勁霸',2000,'1','c002'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈兒',800,'1','c003'); INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003'); 語法：select * from A,B; select * from A left outer join B on 條件; select * from A right outer join B on 條件; #1.查詢哪些分類的商品已經(jīng)上架 #隱式內(nèi)連接 SELECT DISTINCT c.cname FROM category c , products p WHERE c.cid = p.category_id AND p.flag = '1'; #內(nèi)連接 SELECT DISTINCT c.cname FROM category c INNER JOIN products p ON c.cid = p.category_id WHERE p.flag = '1'; #2.查詢所有分類商品的個(gè)數(shù) #左外連接 INSERT INTO category(cid,cname) VALUES('c004','奢侈品'); SELECT cname,COUNT(category_id) FROM category c LEFT OUTER JOIN products p ON c.cid = p.category_id GROUP BY cname; select ....查詢字段 ... from ... 表.. where ... 查詢條件 #3 子查詢, 查詢“化妝品”分類上架商品詳情 #隱式內(nèi)連接 SELECT p.* FROM products p , category c WHERE p.category_id=c.cid AND c.cname = '化妝品'; #子查詢 ##作為查詢條件 SELECT * FROM products p WHERE p.category_id = ( SELECT c.cid FROM category c WHERE c.cname='化妝品' ); ##作為另一張表 SELECT * FROM products p , (SELECT * FROM category WHERE cname='化妝品') c WHERE p.category_id = c.cid; #查詢“化妝品”和“家電”兩個(gè)分類上架商品詳情 SELECT * FROM products p WHERE p.category_id in ( SELECT c.cid FROM category c WHERE c.cname='化妝品' or c.name='家電' ); 數(shù)據(jù)準(zhǔn)備: -- 用戶表(user) create table `user` ( `id` int auto_increment primary key, `username` varchar(50) -- 用戶姓名 ); -- 訂單表(orders) create table `orders` ( `id` int auto_increment primary key, `price` double, `user_id` int ); -- 給訂單表添加外鍵約束 alter table orders add constraint user_fk foreign key (user_id) references user(id); -- 向user表中添加數(shù)據(jù) insert into user values(1,'張三'); insert into user values(2,'李四'); insert into user values(3,'王五'); insert into user values(4,'趙六'); -- 向orders 表中插入數(shù)據(jù) insert into orders values(1,1314,1); insert into orders values(2,1314,1); insert into orders values(3,15,2); insert into orders values(4,315,4); insert into orders values(5,1014,null); 練習(xí): 1.查詢用戶的訂單,沒有訂單的用戶不顯示 2.查詢所有用戶的訂單詳情 3.查詢所有訂單的用戶詳情 內(nèi)連接(inner join)是應(yīng)用程序中用的普遍的"連接"操作,，它一般都是默認(rèn)連接類型,。內(nèi)連接基于連接謂詞將兩張表(如 A 和 B)的列組合在一起，產(chǎn)生新的結(jié)果表,。查詢會(huì)將 A 表的每一行和 B 表的每一行進(jìn)行比較,，并找出滿足連接謂詞的組合。當(dāng)連接謂詞被滿足,，A 和 B 中匹配的行會(huì)按列組合(并排組合)成結(jié)果集中的一行,。連接產(chǎn)生的結(jié)果集，可以定義為首先對(duì)兩張表做笛卡爾積(交叉連接) -- 將 A 中的每一行和 B 中的每一行組合,，然后返回滿足連接謂詞的記錄,。實(shí)際上 SQL 產(chǎn)品會(huì)盡可能用其他方式去實(shí)現(xiàn)連接，笛卡爾積運(yùn)算是非常沒效率的. SQL 定義了兩種不同語法方式去表示"連接",。首先是"顯式連接符號(hào)"，它顯式地使用關(guān)鍵字 JOIN,，其次是"隱式連接符號(hào)",，它使用所謂的"隱式連接符號(hào)",。隱式連接符號(hào)把需要連接的表放到 SELECT 語句的 FROM 部分，并用逗號(hào)隔開,。這樣就構(gòu)成了一個(gè)"交叉連接",，WHERE 語句可能放置一些過濾謂詞(過濾條件)。那些過濾謂詞在功能上等價(jià)于顯式連接符號(hào). 常見的內(nèi)連接有相等連接和交叉連接. 又稱笛卡爾連接(cartesian join)或叉乘(Product),，它是所有類型的內(nèi)連接的基礎(chǔ),。把表視為行記錄的集合，交叉連接即返回這兩個(gè)集合的笛卡爾積,。這其實(shí)等價(jià)于內(nèi)連接的鏈接條件為"永真",，或連接條件不存在.笛卡爾積引用自數(shù)學(xué)，在數(shù)學(xué)中,，兩個(gè)集合X和Y的笛卡爾積（Cartesian product）,，又稱直積。假設(shè)集合A={a,b},，集合B={0,1,2},，則兩個(gè)集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}?？梢詳U(kuò)展到多個(gè)集合的情況,。 例如: select * from user inner join orders on user.id =orders.user_id; 通過on后面的條件，將兩張表中的數(shù)據(jù)過濾,。這種寫法可以將inner省略,，因?yàn)閖oin默認(rèn)就是內(nèi)連接(inner join),，省略inner后的語句變?yōu)?/span> select * from user join orders on user.id=orders.user_id; 效果與寫上inner是一樣的,。 執(zhí)行的結(jié)果如下: 我們?cè)趯?shí)際開發(fā)中,它的使用頻率是最高的,，其實(shí)就是將inner join省略，也不在使用on進(jìn)行條件過濾,，而是直接使用where進(jìn)行過濾,。如下面 select * from user ,orders where user.id=orders.user_id; 結(jié)果與inner join on一樣。 總結(jié): inner join on 它可以省略inner,在on后面直接書寫比較條件,，通過條件來消除迪卡爾積,。 隱式內(nèi)連接，它在開發(fā)應(yīng)用比較多,，我們可以省略inner join 表與表之間使用逗號(hào)分開,，通過where條件來消除迪卡爾積 如果我們?cè)陂_發(fā)中需要將所有用戶及其訂單查詢出來，如果用戶沒有訂單,，也要查詢出用戶信息,。那么使用內(nèi)連接是做不到了，這時(shí)就需要使用外連接。 外連接并不要求連接的兩表的每一條記錄在對(duì)方表中都一條匹配的記錄. 連接表保留所有記錄,甚至這條記錄沒有匹配的記錄也要保留. 外連接可依據(jù)連接表保留左表, 右表或全部表的行而進(jìn)一步分為左外連接, 右外連接和全連接. 注意:mysql只支持左外連接與右外連接,，而不支持全連接,。 例如:select * from user left outer join orders on user.id=orders.user_id; 上面這條sql我們使用的是左外連接，它的意思就是說以u(píng)ser表為準(zhǔn),，保留user表中所有數(shù)據(jù),，右表orders中沒有關(guān)聯(lián)的數(shù)據(jù)，那么就以null關(guān)聯(lián)顯示出來, 執(zhí)行的結(jié)果如下: 例如:select * from user right outer join orders on user.id=orders.user_id; 這條sql我們使用的是右外連接,，它的意思就是說以orders表為準(zhǔn),，保留orders表中所有數(shù)據(jù)，左表user中沒有關(guān)聯(lián)的數(shù)據(jù),，那么就以null關(guān)聯(lián)顯示出來 練習(xí): 1.查看用戶為張三的訂單詳情 2.查詢出訂單的價(jià)格大于300的所有用戶信息,。 3.查詢訂單價(jià)格大于300的訂單信息及相關(guān)用戶的信息。 在sql語言中,，當(dāng)一個(gè)查詢是另一個(gè)查詢的條件時(shí),，稱之為子查詢。 可以使用關(guān)系運(yùn)算符操作 例如: 查看用戶為張三的訂單詳情,。 i.先根據(jù)用戶名查詢出張三的id select id from user where username='張三';----得到了一個(gè)結(jié)果 1 ii.根據(jù)用戶的id查詢?cè)撚脩舻挠唵?/span> select * from orders where user_id=(select id from user where username='張三'); 可以使用in,any或all操作 >any：大于子查詢中的最小值,。 >all： 大于子查詢中的最大值。 <any：小于子查詢中的最大值,。 <all： 小于子查詢中的最小值,。 !=any或<>any：不等于子查詢中的任意值。 !=all或<>all：不等于子查詢中的所有值,。 =any：等于子查詢中任意值,。 例如: 查詢出訂單的價(jià)格>300的所有用戶信息。 i.先查詢出所有訂單價(jià)格>300的用戶的id. select distinct user_id from orders where price >=300; ii.根據(jù)上面查詢出的結(jié)果,，在用戶表中查詢出用戶信息 select * from user where id in(select distinct user_id from orders where price >300); 子查詢返回的就是一張表,，我們直接對(duì)這張表操作即可。 例如: 查詢訂單價(jià)格大于300的訂單信息及相關(guān)用戶的信息,。 i.先得到訂單價(jià)格大于300的訂單信息 select * from orders where price>300; ii.在查詢出用戶信息,，與上面的多行多列關(guān)聯(lián) select * from user u ,(select * from orders where price>300) o where o.user_id=u.id; 上面的操作，不使用子查詢也可以完成: select * from user u,orders o where o.price>300 and o.user_id =u.id;
 |
|
|
