注冊用戶即可下載全站資源 關注Java幫幫微信公眾號
 

Web-第六天 MySQL回顧學習【悟空教程】

53
發表時間:2018-11-07 15:22來源:Java幫幫-微信公眾號

第1章 數據庫

1.1 數據庫介紹

數據庫就是存儲數據的倉庫,其本質是一個文件系統,數據按照特定的格式將數據存儲起來,用戶可以通過sql語句對數據庫中的數據進行增加,修改,刪除及查詢操作


1.2 關系型數據庫

關系數據庫(Relationship DataBase Management System 簡寫:RDBMS) ,描述是建立在關系模型基礎上的數據庫,借助于集合代數等數學概念和方法來處理數據庫中的數據。說白了就是描述實體與實體之間的關系的數據庫.例如用戶購物下訂單,訂單包含商品.他們之間的關系可以通過E-R圖表示.

數據庫發展歷程如下所示:

  • 沒有數據庫,使用磁盤文件存儲數據;

  • 層次結構模型數據庫;

  • 網狀結構模型數據庫;

  • 關系結構模型數據庫,使用二維表格來存儲數據;

  • 關系-對象模型數據庫;


1.2.1 常見的關系型數據庫

  • Oracle數據庫:Oracle是殷墟(yīn Xu)出土的甲骨文(oracle bone inscriptions)的英文翻譯的第一個單詞,在英語里是“神諭”的意思,由Larry Ellison 和另兩個編程人員在1977創辦,他們開發了自己的拳頭產品,在市場上大量銷售,1979 年,Oracle公司引入了第一個商用SQL 關系數據庫管理系統。Oracle公司是最早開發關系數據庫的廠商之一,其產品支持最廣泛的操作系統平臺。目前Oracle關系數據庫產品的市場占有率名列前茅。

  • SQL Server數據庫:1987 年,微軟和 IBM合作開發完成OS/2,IBM 在其銷售的OS/2 ExtendedEdition 系統中綁定了OS/2Database Manager,而微軟產品線中尚缺少數據庫產品。為此,微軟將目光投向Sybase,同Sybase 簽訂了合作協議,使用Sybase的技術開發基于OS/2平臺的關系型數據庫。1989年,微軟發布了SQL Server 1.0 版。

  • DB2數據庫:  作為關系數據庫領域的開拓者和領航人,IBM在1997年完成了System R系統的原型,1980年開始提供集成的數據庫服務器—— System/38,隨后是SQL/DSforVSE和VM,其初始版本與SystemR研究原型密切相關。DB2 forMVSV1 在1983年推出。該版本的目標是提供這一新方案所承諾的簡單性,數據不相關性和用戶生產率。1988年DB2 for MVS 提供了強大的在線事務處理(OLTP)支持,1989 年和1993 年分別以遠程工作單元和分布式工作單元實現了分布式數據庫支持。最近推出的DB2 Universal Database 6.1則是通用數據庫的典范,是第一個具備網上功能的多媒體關系數據庫管理系統,支持包括Linux在內的一系列平臺。

  • Sybase數據庫:Sybase公司成立于1984年,公司名稱“Sybase”取自“system”和 “database” 相結合的含義。Sybase公司的創始人之一Bob Epstein 是Ingres 大學版(與System/R同時期的關系數據庫模型產品)的主要設計人員。公司的第一個關系數據庫產品是1987年5月推出的Sybase SQLServer1.0。Sybase首先提出Client/Server 數據庫體系結構的思想,并率先在Sybase SQLServer 中實現。

  • MySQL數據庫: mySQL是一個小型關系型數據庫管理系統,開發者為瑞典MySQL AB公司。在2008年1月16號被Sun公司收購。而2009年,Sun公司又被Oracle公司收購。目前MySQL被廣泛地應用在Internet上的中小型網站中。由于其體積小、速度快、總體擁有成本低,尤其是開放源碼這一特點,許多中小型網站為了降低網站總體擁有成本而選擇了MySQL作為網站數據庫


1.3 數據庫管理系統

我們通常將數據庫管理系統(DataBase Management System,簡寫dbms)稱為數據庫,大白話就是我們安裝的軟件,當我們安裝了數據庫之后(數據庫服務器),就可以在數據庫服務器中創建數據庫,每個數據庫中還可以包含多張表.

通過上述圖我們知道了數據庫與表之間的關系,那么我們的數據又是怎樣存儲在數據庫中。

數據庫中的表就是一個多行多列的表格。在創建表時,需要指定表的列數,以及列名稱,列類型等信息。而不用指定表格的行數,行數是沒有上限的。

表中的列我們稱之為字段,表中的行我們稱之為記錄。

用我們熟悉的java程序來與關系型數據對比,就會發現以下對應關系。

類-------表

類中屬性-------表中的字段

對象--------記錄。


1.4 mysql數據庫安裝,卸載與配置

mysql的安裝與配置詳見附件:MySQL的安裝.doc   http://polkscan.com下載


1.5 mysql登錄

進入命令窗口

格式:mysql [-h主機地址] -u用戶名 -p[密碼]

啟動mysql服務命令 net start mysql

關閉mysql服務命令 net stop mysql


1.6 mysql數據庫密碼重置

a. 停止mysql服務器 運行輸入services.msc 停止mysql服務

b. 在cmd下,輸入mysqld   --console  --skip-grant-tables 啟動服務器,出現一下頁面,不要關閉該窗口

c. 新打開cmd,輸入mysql -uroot  不需要密碼

use mysql;

update user set password=password('abc') WHERE User='root';

d. 關閉兩個cmd窗口


第2章 SQL語句

2.1 SQL介紹

SQL:結構化查詢語言(Structured Query Language),是一種數據庫查詢和程序設計語言,用于存取、查詢、更新數據以及管理關系數據庫系統.


2.2 SQL分類

2.2.1 數據定義語言:簡稱DDL(Data Definition Language)

用來定義數據庫對象:數據庫,表,列等,例如創建、刪除、修改數據庫和表結構等;


2.2.2 數據操作語言:簡稱DML(Data Manipulation Language)

用來對數據庫中表的記錄進行更新,例如:增、刪、改表記錄;


2.2.3 數據查詢語言:簡稱DQL(Data Query Language)

用來查詢數據庫中表的記錄。


2.2.4 數據控制語言:簡稱DCL(Data Control Language)

用來定義數據庫的訪問權限和安全級別,及創建用戶;


DDL是對數據庫或表的結構進行操作,而DML是對表的記錄進行操作(增、刪、改)。我們以后最常用的就是DDLDMLDQL


2.3 書寫注意事項

SQL語句可以單行或多行書寫,以分號結尾

MySQL數據庫的SQL語句不區分大小寫,建議使用大寫,例如:SELECT * FROM user


2.4 DDL數據定義語言

2.4.1 對數據庫操作

2.4.1.1 創建數據庫create database 數據庫名稱;

例如:create database day04;-- 使用數據庫默認字符集.

擴展:

格式:

create database [if not exists] 數據庫名稱 [character set 字符集] [collate 校對規則];

例如:

create database day0401 character set utf8;-- 使用指定的編碼創建數據庫

create database day0402 character set utf8 collate utf8_general_ci;-- 使用指定的編碼和校對規則創建數據庫


2.4.1.2 刪除數據庫drop database數據庫名稱;

例如:drop database day0402;


2.4.1.3 修改數據庫alter database 數據庫名稱  character set 編碼collate 校對規則

修改數據庫編碼或者校對規則

例如:alter database day0401 character set gbk;-- 修改其字符集為gbk


2.4.2 操作數據庫常見命令

2.4.2.1 查看所有數據庫show databases;


2.4.2.2 查看數據庫定義信息show create database 數據庫名稱

例如:show create database day04;

2.4.2.3 查看字符集和校對規則show character set;


2.4.2.4 切換數據庫:use 數據庫名稱;

例如:use day04;


2.4.2.5 查看當前使用的數據庫select database();


2.4.2.6 查看當前數據庫下所有的表show tables;


2.4.3 mysql的數據類型

java中的數據類型

mysql中的數據類型

備注

byte

tinyint


short

smallint


int

int


long

bigint


float

float


double

double

double(m,d) m數字長度,d精度及小數位,double(5,2)表示它的最大值是:999.99

String

char

varchar()

char固定長度的字符串.默認255,如果存儲的字符沒有達到指定長度,mysql將會在其后面用空格補足到指定長度;

varchar可變長度的字符串,長度可以由我們自己指定,它能保存數據長度的最大值是65535,如果存儲的字符沒有達到指定的長度,不會補足到指定長度;

java.sql.Date

date

日期,格式為yyyy-MM-dd

java.sql.Time

time

時間,格式為hh:mm:ss

java.sql.Timestamp

timestamp

時間戳,格式'YYYY-MM-DD HH:MM:SS'.若設置為空,將該列設置為當前的日期和時間;特點:當更新一條數據時 這條數據中有一個字段是時間戳 那么這個時間戳的值會自動更新


datetime

時間,日期,格式'YYYY-MM-DD HH:MM:SS'

大文本 Clob

tinytext 255B

text  64kb

longtext  4gb


大數據 Blob

tinyblob 255B

blob   64kb

longblob  4gb


2.4.4 對數據表的操作(建表前要切換到數據庫use 數據庫名;


2.4.4.1 創建表create table 表名(字段描述 ,字段描述 ,....);-- 最后一個字段沒有逗號

字段描述格式:

字段名稱  字段類型  [字段約束]

例如:

create table user(

id int,

username varchar(50),

password varchar(20)

);

練習:

創建一個張表user,該表具有以下字段:

id 整型 主鍵 自動增長

username 字符串 長度20 非空

password 字符串 長度20 非空

gender 字符串 長度10

email 字符串 長度50 唯一 非空

role 字符串 長度10 默認值 “admin”

registTime  時間戳


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

);

2.4.4.2 修改表

修改表添加列alter table 表名 add 列名 類型(長度) [約束];

例如:

#1,為分類表添加一個新的字段為 分類描述 varchar(20)

ALTER TABLE category ADD `desc` VARCHAR(20);

修改表修改列的類型長度及約束alter table 表名 modify 列名 類型(長度)約束;

例如:

#2, 為分類表的描述字段進行修改,類型varchar(50) 添加約束 not null

ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;


修改表修改列名alter table 表名 change 舊列名 新列名 類型(長度) 約束;

例如:

#3, 為分類表的分類名稱字段進行更換 更換為 snamesname varchar(30)

ALTER TABLE category CHANGE `desc` description VARCHAR(30);


修改表刪除列alter table 表名 drop 列名;

例如:

#4, 刪除分類表中snamename這列

ALTER TABLE category DROP description;


修改表名rename table 表名 to 新表名;

例如:

#5, 為分類表category 改名成 category2

RENAME TABLE category TO category2;


修改表的字符集alter table 表名 character set 字符集;

例如:

#6, 為分類表 category 的編碼表進行修改,修改成 gbk

ALTER TABLE category CHARACTER SET gbk;


2.4.4.3 刪除表drop table 表名;

例如:drop table user;


2.4.5 常見的命令

2.4.5.1 查看表結構desc 表名稱;

例如:desc user;


2.4.5.2 查看建表語句show create table 表名稱;

例如: show create table user01;


2.5 約束

2.5.1 主鍵約束PRIMARY KEY

PRIMARY KEY 約束唯一標識數據庫表中的每條記錄。

主鍵必須包含唯一的值。

主鍵列不能包含 NULL 值。

每個表都應該有一個主鍵,并且每個表只能有一個主鍵。


2.5.1.1 添加主鍵約束

方式一:創建表時,在字段描述處,聲明指定字段為主鍵:

CREATE TABLE Persons

(

Id_P int PRIMARY KEY,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)


方式二:創建表時,在constraint約束區域,聲明指定字段為主鍵
  • 格式:[constraint 名稱] primary key (字段列表)

  • 關鍵字constraint可以省略,如果需要為主鍵命名,constraint不能省略,主鍵名稱一般沒用。

  • 字段列表需要使用小括號括住,如果有多字段需要使用逗號分隔。聲明兩個以上字段為主鍵,我們稱為聯合主鍵。

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)

2.5.1.2 刪除主鍵約束

如需撤銷 PRIMARY KEY 約束,請使用下面的 SQL

ALTER TABLE Persons DROP PRIMARY KEY


2.5.2 自動增長列auto-increment

我們通常希望在每次插入新記錄時,數據庫自動生成字段的值。

我們可以在表中使用 auto-increment(自動增長列)關鍵字,自動增長列類型必須是整形,自動增長列必須為鍵(一般是主鍵)。

  • 下列 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添加數據時,可以不為P_Id字段設置值,也可以設置成null,數據庫將自動維護主鍵值

INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')

INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (NULL,'Bill','Gates')


  • 面試:delete和truncate的區別

    • Delete刪除表中的數據,但不重置auto-increment記錄數。

    • 日本韩国三级aⅴ在线观看Truncate刪除表中的數據,auto-increment記錄數將重置。Truncate其實先刪除表然后再創建表。


  • 日本韩国三级aⅴ在线观看擴展:默認地,AUTO_INCREMENT 的開始值是 1,如果希望修改起始值,請使用下列 SQL 語法:

ALTER TABLE Persons AUTO_INCREMENT=100

2.5.3 非空約束NOT NULL

NOT NULL 約束強制列不接受 NULL 值。

NOT NULL 約束強制字段始終包含值。這意味著,如果不向字段添加值,就無法插入新記錄或者更新記錄。

  • 下面的 SQL 語句強制 "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)

)


2.5.4 唯一約束UNIQUE

UNIQUE 約束唯一標識數據庫表中的每條記錄。

UNIQUE PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。

PRIMARY KEY 擁有自動定義的 UNIQUE 約束。

請注意,每個表可以有多個 UNIQUE 約束,但是每個表只能有一個 PRIMARY KEY 約束。


2.5.4.1 添加唯一約束

方式1:創建表時,在字段描述處,聲明唯一:

CREATE TABLE Persons

(

Id_P int UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)


方式2:創建表時,在約束區域,聲明唯一:

CREATE TABLE Persons

(

Id_P int,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT 名稱UNIQUE (Id_P)

)


方式3:創建表后,修改表結構,聲明字段唯一

ALTER TABLE Persons ADD [CONSTRAINT 名稱] UNIQUE (Id_P)

2.5.4.2 刪除唯一約束

  • 如需撤銷 UNIQUE 約束,請使用下面的 SQL:

ALTER TABLE Persons DROP INDEX 名稱

  • 如果添加唯一約束時,沒有設置約束名稱,默認是當前字段的字段名。

2.5.5 默認約束(default

還可以使用default,給定一個默認值.

注意:不插入該字段時 使用默認值


2.5.6 外鍵約束FOREIGN KEY

FOREIGN KEY 表示外鍵約束,將在多表中學習


2.6 創建表加入約束示例:

id 主鍵自增長

username 字符串 長度20 不能為空

password 字符串 長度20 不能為空

gender 字符串10

age 整型 默認25

email 字符串 長度50 非空 唯一

salary 浮點型 長度8其中2位小數

state 整型 默認值0

dept 字符串長度10 默認咨詢部

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

);


2.7 字段類型



2.8 DML數據操作語言

2.8.1 插入數據insert

2.8.1.1 不指定列插入

格式:insert into 表名 values(字段值1,字段值2,...);

例如:insert into user values(null,'laobai','1234','male','laobai@126.com',null,null);

注意:

  • 沒有給出要插入的列,表示插入所有列;

值的個數必須是該表的列的個數、插入的值與列名相對應;

值的順序,必須與表創建時給出的列的順序相同。


2.8.1.2 插入指定列值

格式:insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...);

例如:insert into user(username,password,email,registTime)

values('laoqi','1234','laoqi@126.com',null);

注意:表名后面是當前表中部分字段名稱


2.8.1.3 插入全部列值

格式:

insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...);

例如:

insert into user(id,username,password,gender,email,role,registTime)

   values(1,'shijin','1234','male','shijin@126.com',null,null);

注意:

表名后面是當前表中所有字段(列)

多個字段之間使用逗號分隔

字段值必須使用引號(建議單引號),如果是整型數據引號可以省略。

2.8.1.4 注意(編碼問題):

插入中文的時候會報錯,如下圖:

因為mysql的客戶端編碼的問題我們的是utf8,而系統的cmd窗口編碼是gbk.

查看所有的mysql編碼

show variables like 'character%';


解決方案:

1.臨時修改

set 變量名稱=gbk;

例如:

set character_set_client=gbk,character_set_connection=gbk,character_set_results=gbk;

重啟mysql服務器后,就回復原樣了.


2.永久修改

在mysql安裝目錄下,

2.8.2 更新數據update

2.8.2.1 更新所有記錄的指定字段

update 表名 set 字段名1=字段值1, 字段名2=字段值2, ..... ;


2.8.2.2 更新符合條件記錄的指定字段

update 表名 set 字段名1=字段值1, 字段名2=字段值2, ..... [where 條件];

例如:

1.update user set gender='male' ;## 修改所有人的性別為’male’

2.update user set username='xusanduo',email='xusanduo@qq.com' where id=3;## 修改id為3的人username和email.


2.8.2.3 注意:

  • 列名的類型與修改的值要一致.

  • 修改值得時候不能超過最大長度.

  • 除了數值類型外,其它的字段類型的值必須使用引號引起


2.8.3 刪除數據delete

格式:delete from 表名 [where 條件];

例如:

1.delete from user;## 刪除所有

2.delete from user where id=6; ##刪除id為6的記錄.


2.8.3.1 刪除整張表的內容,兩種實現方式:

1.delete from 表名;

2.truncate [table] 表名;

區別:

1.delete屬于DML語句 ,truncate屬于DDL語句

2.delete是一行一行刪除,truncate是將表結構銷毀,再重新創建表結構,數據多的時候,效率高.


2.8.3.2 面試題刪除表中所有記錄使用delete from 表名; 還是用truncate table 表名;

刪除方式:delete 一條一條刪除,不清空auto_increment記錄數。受事務控制。

truncate 直接將表刪除,重新建表,auto_increment將置為零,從新開始。不受事務控制


2.9 DQL數據查詢語言

2.9.1 準備工作

#創建商品表:

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,'聯想',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);


2.9.2 語法:

select [distinct]

* | 列名,列名

from

where 條件


2.9.3 基本查詢select

2.9.3.1 查詢指定列select 字段 from 表名;

例如:查詢商品名和商品價格. select pname,price from product;

2.9.3.2 查詢指定字段信息,如果要查詢多個字段select 字段1,字段2,...from表名;

2.9.3.3 查詢所有列select * from 表名;

例如:

查詢所有的商品.  select * from product;

注意:使用"*"在練習,學習過程中可以使用,在實際開發中,不建議使用。

2.9.3.4 去掉重復記錄select distinct 字段 from 表名;

例如:去掉重復值. select distinct price from product;

代表當pnameprice都重復時才去重兩個條件

select distinct pname,price from product;  

注意:distinct它的作用是去除重復.


2.9.3.5 使用別名

別名查詢.使用的關鍵字是asas可以省略的).

1.表別名: select * from product as p;

2.列別名:select pname as pn from product;


2.9.3.6 在查詢中可以直接對列進行運算

我們在sql操作中,可以直接對列進行運算。

例如:查詢結果是表達式(運算查詢):將所有商品的價格+10元進行顯示.

 select pname,price+10 from product;

注意:null進行運算結果還是null,想要null運算有值,則使用ifnull函數即可;


2.9.3.7 ifnull函數使用,給null賦值

在對數值類型的列做運算的時候,如果做運算的列的值為null的時,運算結果都為null,為了解決這個問題可以使用ifnull函數,使用方法 ifnull(字段,0)


2.9.4 條件查詢

>/</<=/>=/=/<>/!=/BETWEEN..AND.../IN()/LINK/IS NULL/IS NOT NULL/AND/OR/NOT

比較運算符

>  <  <=   >=   =  <>

大于、小于、大于(小于)等于、不等于

BETWEEN  ...AND...

顯示在某一區間的值(含頭含尾)

IN(set)

顯示在in列表中的值,例:in(100,200)

LIKE ‘張pattern’

模糊查詢,Like語句中,

% 代表零個或多個任意字符,

_ 代表一個字符,

例如:first_name like ‘_a%’;

IS NULL

判斷是否為空

邏輯運算符

and

多個條件同時成立

or

多個條件任一成立

not

不成立,例:where not(salary>100);

null值操作

is null; 判斷為空

is not null; 判斷不為空

單條件查詢

#查詢商品名稱為“花花公子”的商品所有信息:

SELECT * FROM product WHERE pname = '花花公子'

#查詢價格為800商品

SELECT * FROM product WHERE price = 800

#查詢價格不是800的所有商品

SELECT * FROM product WHERE price != 800

SELECT * FROM product WHERE price <> 800

SELECT * FROM product WHERE NOT(price = 800)


#查詢商品價格大于60元的所有商品信息

SELECT * FROM product WHERE price > 60;

范圍查詢和BETWEEN AND

#查詢商品價格在2001000之間所有商品

SELECT * FROM product WHERE price >= 200 AND price <=1000;

SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

and or和枚舉in查詢

#查詢商品價格是200800的所有商品

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 '%';


#查詢第二個字為''的所有商品

SELECT * FROM product WHERE pname LIKE '_%';

空查詢

#商品沒有分類的商品

SELECT * FROM product WHERE category_id IS NULL


#查詢有分析的商品

SELECT * FROM product WHERE category_id IS NOT NULL


2.9.5 排序升序ASC、降序DESC

通過order by語句,可以將查詢出的結果進行排序。放置在select語句的最后。

格式:可以單字段和多字段排序

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

ASC 升序 (默認)

DESC 降序

#1.使用價格排序(降序)

SELECT * FROM product ORDER BY price DESC;

#2.在價格排序(降序)的基礎上,以分類排序(降序)

SELECT * FROM product ORDER BY price DESC,category_id DESC;

#3.顯示商品的價格(去重復),并排序(降序)

SELECT DISTINCT price FROM product ORDER BY price DESC;


2.9.6 聚合COUNT/SUM/MAX/MIN/AVG

之前我們做的查詢都是橫向查詢,它們都是根據條件一行一行的進行判斷,而使用聚合函數查詢是縱向查詢,它是對一列的值進行計算,然后返回一個單一的值;另外聚合函數會忽略空值。

今天我們學習如下五個聚合函數:

  • count:統計指定列不為NULL的記錄行數;

  • sum:計算指定列的數值和,如果指定列類型不是數值類型,那么計算結果為0;

  • max:計算指定列的最大值,如果指定列是字符串類型,那么使用字符串排序運算;

  • min:計算指定列的最小值,如果指定列是字符串類型,那么使用字符串排序運算;

  • avg:計算指定列的平均值,如果指定列類型不是數值類型,那么計算結果為0;

注意:聚合函數忽略null的存在;

注意:聚合函數一般情況下(除了分組的字段外)不與其他不字段一起查詢;

#1 查詢商品的總條數

SELECT COUNT(*) FROM product;

#2 查詢價格大于200商品的總條數

SELECT COUNT(*) FROM product WHERE price > 200;

#3 查詢分類為'c001'的所有商品的總和

SELECT SUM(price) FROM product WHERE category_id = 'c001';

#4 查詢分類為'c002'所有商品的平均價格

SELECT AVG(price) FROM product WHERE category_id = 'c002';

#5 查詢商品的最大價格和最小價格

SELECT MAX(price),MIN(price) FROM product;


2.9.7 分組GROUP BY--having&where區別

分組查詢是指使用group by字句對查詢信息進行分組。

  • 格式:

SELECT 字段1,字段2… FROM 表名 GROUP BY分組字段 HAVING 分組條件;

分組操作中的having子語句,是用于在分組后對數據進行過濾的,作用類似于where條件。


  • having與where的區別:

    • having是在分組后對數據進行過濾.

    • where是在分組前對數據進行過濾

    • having后面可以使用分組函數(統計函數)

    • where后面不可以使用分組函數。

#1 統計各個分類商品的個數

SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;

#2 統計各個分類商品的個數,且只顯示個數大于1的信息

SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;


2.9.8 分頁查詢limit index,length

分頁查詢相應的sql是方言(不同的數據庫特有的語法sql)

mysqllimit index,length

oracle: rownum

sqlServer: top

select * from user limit 0,3;


推斷:頁面與起始索引的關系


頁數 每頁條數 起始索引 關系

1   3   0  (1-1)*3

2   3   3  (2-1)*3

3   3   6  (3-1)*3

4   3   9  (4-1)*3


公式: 起始索引 = (頁數-1)*每頁顯示條數

2.9.9 DQL語句操作總結

綜合我們學習的查詢相關關鍵字:select,from,where,group by,having,order by;它們的執行順序是如下:

from:首先執行from,找到要查詢的表;

where:判斷條件,篩選符合條件所有記錄;

group by:根據之前操作對記錄按照指定列進行分組

having:對分組后的信息進行篩選;

select:選擇所需要的列信息;

order by:對查詢信息進行排序。

在SQL語言中,第一個被處理的子句是from字句,盡管select字句最先出現,但是幾乎總是最后被處理。


2.10 DQL練習

2.10.1 數據準備

id 主鍵自增長

username 字符串 長度20 不能為空

password 字符串 長度20 不能為空

gender 字符串10

age 整型 默認25

email 字符串 長度50 非空 唯一

salary 浮點型 長度8其中2位小數

state 整型 默認值0

dept 字符串長度10 默認咨詢部

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@polkscan.com',5000,1,'財務部','2015-09-10 12:18:38');

insert into user values(null,'邊海鵬','bhp','',32,'bhp@polkscan.com',4500,0,'咨詢部','2015-09-12 12:18:38');

insert into user values(null,'張淑敏','753951','',42,'zsm@polkscan.com',5200,1,'技術部','2015-09-18 12:18:38');

insert into user values(null,'楊志勇','pangzi','',29,'yzy@polkscan.com',null,0,'技術部','2015-08-10 12:18:38');

insert into user values(null,'范瑾','fj123','',22,'fj@polkscan.com',3000,1,'咨詢部','2014-09-19 12:18:38');

insert into user values(null,'多博','db123','',25,'db@polkscan.com',8000,0,'技術部','2014-09-10 12:18:38');

insert into user values(null,'張章婉航','zsf123','',58,'zsff@polkscan.com',12000,0,'技術部','2015-03-28 12:18:38');

insert into user values(null,'楊秀清','yxq123','',42,'yxq@polkscan.com',5900,1,'財務部','2015-09-10 12:18:38');

insert into user values(null,'孫志剛','szg123','',18,'szg@polkscan.com',8000,0,'財務部','2015-09-10 12:18:38');

insert into user values(null,'張英','wj123','',33,'zyj@polkscan.com',8500,1,'技術部','2014-12-12 12:18:38');


2.10.2 數據的查詢操作

2.10.2.1 查詢全部select * from user;


2.10.2.2 查詢部分select 字段,字段 from user;


2.10.2.3 去重查詢----distinct select distinct gender,username from user;

代表當genderusername都重復時才去重


2.10.2.4 運算查詢

select age+50 as '年齡' from user;

select salary+1000 '薪資' from user;

注意:null進行運算結果還是null

注意:函數 ifnull(字段,) --- 當字段為null時 賦值為多少

select ifnull(salary,0)+1000 '薪資' from user;


2.10.2.5 聚合函數

count():計數

sum():求和

avg():平均值

max():最大值

min():最小值


注意:聚合函數忽略null的存在

注意:聚合函數一般情況下(除了分組的字段外)不與其他字段一起查詢


2.10.2.6 排序查詢----order by 升序asc、降序desc

select * from user order by salary;

select * from user order by salary,age desc;多字段排序


2.10.2.7 條件查詢

1) 單條件 =/!=/<>/>=/<=/>/<
查詢年齡小于35的

select * from user where age<35;  = != <> >= <= > <


2) 多條件 --- and or

查詢年齡小于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的優先級大于or


3) 空查詢is nullis not null

select * from user where salary is null;

select * from user where salary is not null;


4) 范圍查詢 ---- between and

查詢工資大于等于3000并且小于等于8000

select * from user where salary>=3000 and salary<=8000;

select * from user where salary between 3000 and 8000;


查詢14年注冊的人

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';


5) 枚舉查詢 --- or關鍵字的增強 in

select * from user where age=18 or age=22 or age=25;

select * from user where age in(18,22,25);


6) 模糊查詢 like  %  _

%:代表任意多個字符

_:代表任意一個字符


select * from user where username like '%';

select * from user where username like '__';

select * from user where username like '___';


2.10.2.8 分組查詢 ---- group by ... having

查詢每個部門的總薪資

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的區別?

where對分組前的篩選

having對分組后的數據篩選

select dept,sum(salary) from user where salary>5000 group by dept having sum(salary)>10000;


2.10.2.9 分頁查詢 limit index,length

分頁查詢相應的sql是方言(不同的數據庫特有的語法sql)

mysqllimit index,length

oracle: rownum

sqlServer: top

select * from user limit 0,3;


推斷:頁面與起始索引的關系


頁數 每頁條數 起始索引 關系

1   3   0  (1-1)*3

2   3   3  (2-1)*3

3   3   6  (3-1)*3

4   3   9  (4-1)*3

公式: 起始索引 = (頁數-1)*每頁顯示條數


第3章 數據庫備份與恢復

3.1 命令方式

3.1.1 備份

數據庫的備份是指將數據庫轉換成對應的sql文件。

數據庫導出sql腳本的格式:

mysqldump  -u用戶名 -p密碼 數據庫名>生成的腳本文件路徑

例如:

mysqldump  -uroot  -p1234  day0401>d:\day04.sql

以上備份數據庫的命令中需要用戶名和密碼,即表明該命令要在用戶沒有登錄的情況下使用


3.1.2 恢復

數據庫的恢復指的是使用備份產生的sql文件恢復數據庫,即將sql文件中的sql語句執行就可以恢復數據庫內容。因為數據庫備份只是備份了數據庫內容,所以備份產生的sql文件中沒有創建數據庫的sql語句,在恢復數據庫之前需要自己動手創建數據庫。

  • 在數據庫外恢復  

格式:mysql  -uroot  -p密碼 數據庫名 < 文件路徑

例如:mysql  -uroot  -p1234 day0401<d:\day04.sql

注意:要求數據庫必須先創建出來.    

  • 在數據庫內恢復  

格式:source SQL腳本路徑

例如:source d:\day0401.sql

注意:使用這種方式恢復數據,首先要登錄數據庫.


3.2 圖形化SQL yog

3.2.1 SQL備份

選中數據庫,右鍵 ”備份/導出” , 指定導出路徑,保存成.sql文件即可。


3.2.2 SQL恢復

數據庫列表區域右鍵“從SQL轉儲文件導入數據庫”, 指定要執行的SQL文件,執行即可。

第4章 多表操作

實際開發中,一個項目通常需要很多張表才能完成。例如:一個商城項目就需要分類表(category)、商品表(products)、訂單表(orders)等多張表。且這些表的數據之間存在一定的關系,接下來我們將在單表的基礎上,一起學習多表方面的知識。


4.1 外鍵約束

現在我們有兩張表“分類表”和“商品表”,為了表明商品屬于哪個分類,通常情況下,我們將在商品表上添加一列,用于存放分類cid的信息,此列稱為:外鍵

此時“分類表category”稱為:主表,“cid”我們稱為主鍵。“商品表products”稱為:從表,category_id稱為外鍵。我們通過主表的主鍵和從表的外鍵來描述主外鍵關系,呈現就是一對多關系。

外鍵特點:

  • 從表外鍵的值是對主表主鍵的引用。

  • 從表外鍵類型,必須與主表主鍵類型一致。

  • 聲明外鍵約束

語法:alter table 從表 add [constraint] [外鍵名稱] foreign key (從表外鍵字段名) references 主表 (主表的主鍵);

[外鍵名稱] 用于刪除外鍵約束的,一般建議“_fk”結尾

alter table 從表 drop foreign key 外鍵名稱

  • 使用外鍵目的:

保證數據的一致性和完整性


4.2 表與表之間的關系

4.2.1 一對多關系:

  • 常見實例:客戶和訂單,分類和商品,部門和員工.

  • 一對多建表原則:在從表(多方)創建一個字段,字段作為外鍵指向主表(一方)的主鍵.

4.2.2 多對多關系:

  • 常見實例:學生和課程、用戶和角色

  • 多對多關系建表原則:需要創建第三張表,中間表中至少兩個字段,這兩個字段分別作為外鍵指向各自一方的主鍵.


4.2.3 一對一關系:(了解)

  • 在實際的開發中應用不多.因為一對一可以創建成一張表.

  • 兩種建表原則:

    • 外鍵唯一:主表的主鍵和從表的外鍵(唯一),形成主外鍵關系,外鍵唯一unique。

    • 外鍵是主鍵:主表的主鍵和從表的主鍵,形成主外鍵關系。


4.3 多表設計原則

實際開發中表與表的關系包括如下三種:

一對一:

一對一的兩張表一般都可以合并成為一張表,但基于如下兩個原因可能會將一張表拆分張兩張一對一關系的表

原因一:要使兩張表的語義更加明確,例如用戶表和用戶帳戶表

原因二:將經常查詢的字段放到一張表中,不經常查詢的數據放到一張表中

一對多:

如:類別表和商品表、用戶表和訂單表

一對多建表原則:在多的一方(從表)中創建外鍵與一的一方(主表)的主鍵進行關聯。 父子關系。

多對多:

如:訂單表與商品表、用戶表和角色表、角色表和功能表

多對多的建表原則:在兩張表的中間建立一個關系表維護兩張表的多余多的關系

注意:怎么看兩張表的關系?

看一條分方向


4.4 一對多操作

4.4.1 分析

  • category分類表,為一方,也就是主表,必須提供主鍵cid

  • products商品表,為多方,也就是從表,必須提供外鍵category_id

4.4.2 實現:分類和商品

###創建分類表

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);


4.4.3 操作

#1 向分類表中添加數據

INSERT INTO category (cid ,cname) VALUES('c001','服裝');


#2 向商品表添加普通數據,沒有外鍵數據,默認為null

INSERT INTO products (pid,pname) VALUES('p001','商品名稱');


#3 向商品表添加普通數據,含有外鍵信息(數據存放在)

INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名稱2','c001');


#4 向商品表添加普通數據,含有外鍵信息(數據不存在) -- 不能異常

INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名稱2','c999');


#5 刪除指定分類(分類被商品使用) -- 執行異常

DELETE FROM category WHERE cid = 'c001';


4.5 多對多

4.5.1 分析

  • 商品和訂單多對多關系,將拆分成兩個一對多。

  • products商品表,為其中一個一對多的主表,需要提供主鍵pid

  • orders 訂單表,為另一個一對多的主表,需要提供主鍵oid

  • orderitem中間表,為另外添加的第三張表,需要提供兩個外鍵oid和pid


4.5.2 實現:訂單和商品

### 商品表[已存在]


### 訂單表

create table `orders`(

 `oid` varchar(32) PRIMARY KEY ,

 `totalprice` double #總計

);


### 訂單項表

create table orderitem(

 oid varchar(50),-- 訂單id

 pid varchar(50)-- 商品id

);


###---- 訂單表和訂單項表的主外鍵關系

alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);


###---- 商品表和訂單項表的主外鍵關系

alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);


### 聯合主鍵(可省略)

alter table `orderitem` add primary key (oid,pid);


4.5.3 操作

#1 向商品表中添加數據

INSERT INTO products (pid,pname) VALUES('p003','商品名稱');


#2 向訂單表中添加數據

INSERT INTO orders (oid ,totalprice) VALUES('x001','998');

INSERT INTO orders (oid ,totalprice) VALUES('x002','100');


#3向中間表添加數據(數據存在)

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刪除中間表的數據

DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';


#5向中間表添加數據(數據不存在) -- 執行異常

INSERT INTO orderitem(pid,oid) VALUES('p002','x003');


#6刪除商品表的數據 -- 執行異常

DELETE FROM products WHERE pid = 'p001';


第5章 多表關系實戰

5.1 實戰1:省和市

  • 方案1:多張表,一對多

  • 方案2:一張表,自關聯一對多

5.2 實戰2:用戶和角色

  • 多對多關系


5.3 實戰3:角色和權限

  • 多對多關系


5.4 實戰4:客戶和聯系人(可選)

  • 一對多:一個客戶服務于多個聯系人

第6章 多表查詢

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),    #是否上架標記為:1表示上架、0表示下架

 category_id VARCHAR(32),

 CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)

);

6.1 初始化數據

#分類

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','聯想',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');


6.2 多表查詢

6.2.1 交叉連接查詢(基本不會使用-得到的是兩個表的乘積) [了解]

  • 語法:select * from A,B;

6.2.2 內連接查詢(使用的關鍵字 inner join  -- inner可以省略)


6.2.2.1 隱式內連接:select * from A,B where 條件;


6.2.2.2 顯示內連接:select * from A inner join B on 條件;


6.2.3 外連接查詢(使用的關鍵字 outer join -- outer可以省略)


6.2.3.1 左外連接:left outer join

  • select * from A left outer join B on 條件;


6.2.3.2 右外連接:right outer join

  • select * from A right outer join B on 條件;

#1.查詢哪些分類的商品已經上架

#隱式內連接

SELECT DISTINCT c.cname FROM category c , products p

WHERE c.cid = p.category_id AND p.flag = '1';


#內連接

SELECT DISTINCT c.cname FROM category c

INNER JOIN products p ON c.cid = p.category_id

WHERE p.flag = '1';


#2.查詢所有分類商品的個數

#左外連接

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;



6.3 子查詢一條select語句結果作為另一條select語法一部分(查詢條件,查詢結果,表等)。

select ....查詢字段 ... from ... .. where ... 查詢條件

#3 子查詢, 查詢“化妝品”分類上架商品詳情

#隱式內連接

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;


#查詢“化妝品”和“家電”兩個分類上架商品詳情

SELECT * FROM products p

WHERE p.category_id in

(

SELECT c.cid FROM category c

WHERE c.cname='化妝品' or c.name='家電'

);


第7章 多表查詢練習

7.1 多表查詢-內連接

數據準備:

-- 用戶表(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表中添加數據

insert into user