java核心技術第二篇之資料庫SQL語法

来源:https://www.cnblogs.com/haizai/archive/2019/07/28/11260038.html
-Advertisement-
Play Games

#查詢products表記錄SELECT * FROM products WHERE price > 2000;-- 單行註釋/* 多行註釋*/#創建資料庫CREATE DATABASE hei66_day19_db;hei66_day19_db#查看資料庫SHOW DATABASES;#查看某個數 ...


#查詢products表記錄
SELECT * FROM products WHERE price > 2000;
-- 單行註釋
/*
多行註釋
*/
#創建資料庫
CREATE DATABASE hei66_day19_db;hei66_day19_db
#查看資料庫
SHOW DATABASES;
#查看某個資料庫的定義信息
SHOW CREATE DATABASE hei66_day19_db;
#刪除資料庫
DROP DATABASE hei66_day19_db;

#切換資料庫
USE day21_267;
SELECT * FROM products;
#查看當前工作的資料庫
SELECT DATABASE();
#創建學員信息表：
CREATE TABLE student(
stuNo INT,
stuName VARCHAR(200),
stuSex CHAR(5),
stuAge INT
);
#查看資料庫中有哪些表：
SHOW TABLES;

#查看表結構：
DESC account;

#刪除表
DROP TABLE student;

#為student表添加一個address列
ALTER TABLE student ADD address VARCHAR(200);

#將student表的address欄位修改為500個長度
ALTER TABLE student MODIFY address VARCHAR(500);

#刪除address列
ALTER TABLE student DROP address;



11.表記錄操作相關的語句：
1.添加數據：insert into
兩種格式：
1.insert into 表名 values(值1，值2，.....，值n)--全欄位添加
註意：
1).後面值列表中的數量必須跟表中列的數量匹配，而且順序也要匹配。
2).值：數值類型，可以不用單引號(用也可以)
字元串類型，一定要使用單引號。
2.insert into 表名(欄位1,欄位2,.....,欄位n) values(值1,值2,....,值n)--部分欄位添加，剩餘欄位添加：NULL


#把sc表的查詢出來的數據,再添加到sc1表中(重難點)
INSERT INTO sc1 (SELECT * FROM sc);
INSERT INTO sc1 (SELECT * FROM sc);
添加數據的時候,可以直接添加查詢出來的所有數據
INSERT INTO sc SELECT sno, 3, (SELECT AVG(score) FROM sc WHERE cno = 2) FROM student WHERE sno NOT IN (SELECT sno FROM sc WHERE cno = 3);

註意：
1).欄位列表：可以是表的部分欄位，也可以不按照定義順序；
2).值列表：必須跟欄位列表的數量和順序要匹配。
3).未指定的欄位，添加：NULL值。(前提是：欄位允許NULL值)
2.修改數據：update(在有外鍵的表中不能查詢的同時,再進行修改)
格式：update 表名 set 欄位名1 = 值1 , 欄位名2 = 值2 , .... , 欄位名n = 值n where 條件；


3.刪除數據：delete from
格式：delete from 表名 where 條件;
清空表：
1).delete from 表名;逐行刪除，效率低；不清空auto_increment記錄數
2).truncate 表名;先摧毀表，然後按照原結構再創建一個新表，效率高；auto_increment將置為零，從新開始
3).TRUNCATE TABLE sc;//恢復表

#1.添加記錄--全欄位
#1.insert into 表名 values(值1，值2，.....，值n)--全欄位添加
INSERT INTO student VALUES(2,章子怡,女,20);

#2.添加記錄--部分欄位
#2.insert into 表名(欄位1,欄位2,.....,欄位n) values(值1,值2,....,值n)--部分欄位添加，剩餘欄位添加：NULL
INSERT INTO student(stuName,stuNo) VALUES('汪峰',22);

#3.修改記錄
UPDATE student SET stuAge = 30 WHERE stuNo = 22;

#4.修改記錄：修改汪峰：年齡為：23，性別：男
UPDATE student SET stuage = 23,stuSex = '男' WHERE stuNo = 22;
UPDATE student SET stuAge = 30 WHERE stuSex = '女'

#5.刪除數據
day21_267

DELETE FROM products WHERE price > 2000;

/*
練習：
1.添加一個學員信息：1,'黃渤','男',22
2.添加一個需要信息：2,'孫紅雷',(空),23
3.修改：stuNo為3的，姓名改為：小沈陽，性別：男，年齡：20
4.刪除：刪除stuName='小沈陽'的記錄

*/
INSERT INTO student VALUES(1,'黃渤','男',22);
INSERT INTO student VALUES(2,'孫紅雷',NULL,23);
INSERT INTO student(stuNo,stuName,stuAge) VALUES(3,'孫紅雷',23);
UPDATE student SET stuName = '小沈陽',stuSex='男',stuAge = 20 WHERE stuNo = 3;
DELETE FROM student WHERE stuName = '小沈陽';



4.查詢數據：select 欄位名 from 表名 where 欄位的篩選條件
1.簡單查詢：
1).查詢所有欄位的所有記錄：
select pid,pname,price,categoryName from product;
或者
select * from product;

查詢條件是大寫的單詞
select * from 表名 where binary name = 'TOM';

2).查詢部分欄位的所有記錄：
select pname,price from product;
3).使用別名：
a).列別名：
SELECT pname AS '商品名稱' , price AS '價格' FROM product;
b).表別名：
SELECT p.pname,p.price FROM product p;//一般在多表中使用別名
4).去掉重覆值
SELECT DISTINCT price FROM product;
5).對查詢結果進行運算：
例如：將所有查詢結果的商品的價格加100顯示：
select pname,price + 100 from product;
註意：只對查詢結果進行更改，原數據沒有更改。
2.條件查詢：
1).比較運算符：
1).">":大於。例如：查詢價格大於2000元的商品 --針對數值類型查詢
select * from product where price > 2000;
2)."<":小於。例如：查詢價格小於2000元的商品 --針對數值類型查詢
select * from product where price < 2000;
3).">=":大於等於。例如：查詢價格大於等於2000元的商品 --針對數值類型查詢
select * from product where price >= 2000;
4)."<=":小於等於。例如：查詢價格小於等於2000元的商品 --針對數值類型查詢
select * from product where price <= 2000;
5)."<>":不等於。例如：查詢價格不等於2000元的商品 --針對各種類型
select * from product where price <> 2000;
"!=":不等於 --針對各種類型
select * from product where price != 2000;
6)."=" :等於.例如：查詢價格等於2000元的商品 --針對各種類型
select * from product where price = 2000;
2).邏輯運算符：
1).and : 語義：並且
例如：查詢所有商品價格大於2000元的電腦類商品
select * from product where price > 2000 and categoryName = '電腦';
2).or : 語義：或者
例如：查詢所有商品價格大於2000元，或者價格低於1000元的所有商品
select * from product where price > 2000 or price < 1000;
3).not : 語義：非
例如：查詢商品價格不大於2000元的所有商品
select * from product where not(price > 2000);

註意：如果多個and和or運算，中間不要加逗號，可以使用()改變運算順序。
例如：查詢所有價格大於2000元的電腦類商品或者服裝類商品
select * from product where price > 2000 and (categoryName = '電腦' or categoryName = '服裝');
3).範圍查詢：between ... and ...(可以用於數值類型，也可以用於日期類型)
1).用於查詢數值範圍：between(包含)....and(包含)...
例如：查詢價格在1000元(包含)到2000元(包含)之間的所有商品
select * from product where price >= 1000 and price <= 2000;
或者
select * from product where price between 1000 and 2000;
2).用於查詢日期範圍：
例如：查詢生產日期在2017年1月份的所有商品
select * from product where proDate between '2017-01-01' and '2017-01-31';
4).多個值的判斷：in(值列表)
例如：查詢商品價格為200元，500元，1000元，2000元的商品信息
select * from product where price = 200 or price = 500 or price = 1000 or price = 2000;
或者使用in查詢
select * from product where price in (200,500,1000,2000);

擴展:all和any用法
create table #A(id int)
Go
insert into #A values(1)
insert into #A values(2)
insert into #A values(3)
insert into #A values(4)
go
--All：對所有數據都滿足條件，整個條件才成立，例如：5大於所有返回的id
select *
from #A
where 5>All(select id from #A)
go
--Any：只要有一條數據滿足條件，整個條件成立，例如：3大於1，2
select *
from #A
where 3>any(select id from #A)
go
--Some和Any一樣
備註：Any的用法中，在作數字比對時，也可以改用先select subquery的min/max value的方法，某些情況下效率更高



5).模糊查詢：like 兩個通配符：1)"%" : 任意的0到多個字元；2)"_":任意的1個字元
例如：查詢商品名稱中包含"花"的商品信息
select * from product where pname like '%花%';
查詢商品名稱中以"花"字開頭的商品：
select * from product where pname like '花%';
商品名稱以"花花"開頭，全名是四個字的商品：
select * from product where pname like '花花__';
6).查詢空欄位：
1).添加一條記錄時，不添加的欄位可以指定為NULL值，例如：
insert into product values(14,'果10',200,'食品',NULL);
要查詢所有"生產日期"沒有添加的所有商品：
select * from product where proDate IS NULL;
3).添加一條記錄：
insert into product values(15,'果11',300,'',null);
要查詢出來這條記錄：
select * from product where proDate = '';

#1.查詢所有欄位的所有記錄
SELECT pid,pname,price,categoryName FROM product;
#或者
SELECT * FROM product;
#2.查詢部分欄位：
SELECT pname,price FROM product;

#3.使用別名
SELECT pname AS '商品名稱' , price AS '價格' FROM product;
#表別名
SELECT p.pname,p.price FROM product p;
#4.去掉重覆值
SELECT DISTINCT price FROM product;
#5.對查詢結果進行運算
SELECT pname,price + 100 FROM product;
#6.查詢價格大於2000元的商品
SELECT * FROM product WHERE price > 2000;
#7.查詢價格小於2000元的商品
SELECT * FROM product WHERE price < 2000;
#8.查詢價格小於等於2000元的商品
SELECT * FROM product WHERE price <= 2000;
#9.查詢價格不等於2000元的商品
SELECT * FROM product WHERE price <> 2000;
#10.查詢價格等於2000元的商品
SELECT pname,price FROM product WHERE price = 2000;

#11.查詢商品類別不等於'電腦'
SELECT * FROM product WHERE categoryName <> '電腦';

#12.查詢所有商品價格大於2000元的電腦類商品
SELECT * FROM product WHERE price > 2000 AND categoryName = '電腦';
#13.查詢所有商品價格大於2000元，或者價格低於1000元的所有商品
SELECT * FROM product WHERE price > 2000 OR price < 1000;
#14.查詢商品價格不大於2000元的所有商品
SELECT * FROM product WHERE NOT(price > 2000);
#15.查詢所有價格大於2000元的電腦類商品或者服裝類商品
SELECT * FROM product WHERE price > 2000 AND (categoryName = '電腦' OR categoryName = '服裝');
#16.查詢生產日期在2017年1月份的所有商品
SELECT * FROM product WHERE proDate BETWEEN '2017-01-01' AND '2017-01-31';
#17.查詢商品名稱中包含"花"的商品信息
SELECT * FROM product WHERE pname LIKE '%花%';

INSERT INTO product VALUES(14,'果10',200,'食品',NULL);
SELECT * FROM product WHERE proDate IS NULL;
INSERT INTO product VALUES(15,'果11',300,'',NULL);
SELECT * FROM product WHERE categoryName = '';
=================================================================================================================================
學習目標總結：
1，能夠理解資料庫的概念
說出資料庫的概念
1).資料庫就是存儲數據的倉庫，其本質是一個文件系統，
資料庫按照特定的格式將數據存儲起來，用戶可以對資料庫中的數據進行增加，修改，刪除及查詢操作。
說出資料庫的作用
2).存儲、管理大量的數據。
說出資料庫與表的關係
一個邏輯資料庫包含多個表
說出常見的資料庫
MySQL
Oracle
SQLServer

2，能夠安裝MySQL資料庫
參考安裝文檔。
3，能夠啟動,關閉及登錄MySQL
1.啟動和關閉MySQL服務：
1).我的電腦-->右鍵-->管理-->服務和應用程式-->服務-->MySQL-->啟動/停止
2.登錄MySQL
1).命令行：
C:\>mysql -uroot -p密碼 (回車)
2).SQLYog:
啟動，填寫連接信息，登錄。

4，能夠使用SQL語句操作資料庫
寫出創建資料庫的SQL語句
create database 資料庫名;
或者
create database 資料庫名 character set 字元集;
寫出刪除資料庫的SQL語句
drop database 資料庫名;
寫出查看所有資料庫的SQL語句
show databases;
寫出切換資料庫的SQL語句
ues 資料庫名;
5，能夠使用SQL語句操作表結構
寫出創建表的SQL語句
create table 表名(
欄位名1 數據類型[長度] [約束],
欄位名2 數據類型[長度] [約束],
....
欄位名n 數據類型[長度] [約束]
);
寫出刪除表的SQL語句
drop table 表名;
寫出添加一列的SQL語句
alter table 表名 add 列名 類型(長度) [約束];
寫出刪除一列的SQL語句
alter table 表名 drop 列名;
寫出查看當前資料庫下所有表的SQL語句
show tables;
寫出查看表結構的SQL語句
desc 表名;
6，能夠使用SQL語句進行數據的添加修改和刪除的操作
寫出添加數據的SQL語句
insert into 表名 values(值1，值2，....，值n);
或者
insert into 表名(欄位1，欄位2，... ，欄位n) values(值1，值2，... ， 值n)
寫出修改數據的SQL語句
update 表名 set 欄位1 = 值1 ， 欄位2 = 值2 ， ... ，欄位n = 值n where 條件;
寫出刪除數據的SQL語句
delete from 表名 where 條件；


在dos中怎麼解決亂碼？

win7系統中使用DOS命令是出現亂碼的解決方法
方法一：設置cmd顯示字體
1、win+R打開運行視窗->輸入cmd->回車，打開命令行提示符視窗
win7系統運行視窗
win7系統DOS命令行提示視窗

2、在命令行標題欄上點擊右鍵，選擇”屬性”->”字體”，將字體修改為True Type字體”Lucida Console”，然後點擊確定將屬性應用到當前視窗，確定
DOS命令視窗右鍵菜單
DOS命令行，設置字體

方法二：修改註冊表
1、快捷鍵win+R，打開運行視窗，輸入“regedit”，打開註冊表編輯器
運行視窗打開註冊表編輯器
2、修改註冊表的參數值，
正常顯示路徑：HKEY_CURRENT_USER\Console\%"SystemRoot"%_system32_cmd.exe
健名：CodePage
修改前的值為：437(十進位)，改為：936(十進位)；
溫馨提示：涉及註冊表修改，請做好註冊表備份



01.SQL高級查詢_排序：
1.使用的關鍵字：order by 欄位名 ASC(升序--預設) / DESC(降序)
例如：查詢所有商品，要求結果按價格從小到大排序
SELECT * FROM product ORDER BY proDate ASC;
2.註意：升序可以不寫asc關鍵字，例如：
select * from product order by proData;//升序
3.排序：
升序(ASC):從小到大；
降序(DESC):從大到小；
4.對多列進行排序：
例如：對多列進行排序：按金額排序，如果金額相同，按生產日期升序排序
SELECT * FROM product ORDER BY price ASC,proDate ASC;
先按第一個欄位排序，在第一個欄位值相同的情況下，再按第二個欄位排。
5.如果有查詢條件，寫法：
select * from 表名 where 條件 order by 欄位 ... ;
02.SQL高級查詢_聚合函數：
1.我經常會有需求，對某列進行彙總，這就需要使用"聚合函數"；
2.今天我們掌握的五個聚合函數：
a).count(*/欄位名)：統計指定列不為NULL的記錄行數--任何數據類型
例如：查詢電腦類別的商品，共有多少種
SELECT COUNT(*) FROM product WHERE categoryName = '電腦';
b).sum(列名)：計算指定列的數值和，如果指定列類型不是數值類型，那麼計算結果為0--數值類型的列
例如：查詢電腦類商品的價格總數是多少？
select sum(price) from product where categoryName = '電腦';
c).max(列名)：計算指定列的最大值，如果指定列是字元串類型，那麼使用字元串排序運算--數值類型、日期類型
例如：查詢電腦類商品的最高價格？
select max(price) from product where categoryName = '電腦';
d).min(列名)：計算指定列的最小值，如果指定列是字元串類型，那麼使用字元串排序運算
例如：查詢電腦類商品的最低價格？
select min(price) from product where categoryName = '電腦';
e).avg(列名)：計算指定列的平均值，如果指定列類型不是數值類型，那麼計算結果為0
例如：查詢電腦類商品的平均價格？
select avg(price) from product where categoryName = '電腦';
註意：計算記錄的總數量時，不包含NULL的記錄。
所以如果計算的列中有NULL值，則結果不准確。
3.註意：聚合查詢的結果，只能包含"聚合結果列"，不要包含其他列，要包含，其結果是無意義的。
聚合的結果是"計算的結果"，跟某行數據無關，所以不能關聯顯示其它欄位。

03.SQL高級查詢_分組：
1.分組：對某列中"相同的值"作為一組，進行分組。分組只是手段，後續經常需要進行彙總：
2.例如：一條語句查詢出每種商品的最高價格是多少？
SELECT categoryName,MAX(price) FROM product GROUP BY categoryName;
練習：查詢每種商品的價格的總和
SELECT categoryName,SUM(price) FROM product GROUP BY categoryName;
練習：查詢每種商品的商品數量是多少
SELECT categoryName,COUNT(*) FROM product GROUP BY categoryname;
3.註意：
1).分組查詢的結果欄位中，只能包含"分組欄位"，"聚合結果欄位"。不能再包含其他欄位，如果包含，其結果也是無意義的。
4.having子句：
1).由於where不能對聚合後的結果進行篩選。所以要對聚合後的結果進行篩選，需要使用having子句。
例如：查詢每種商品的價格總額，結果保留大於1000元的。
select categoryName,sum(price) from product group by categoryName having sum(price) > 1000;
5.對多列進行分組：
收支流水錶：trans
id 收支項 賬戶 金額
1 工資收入 工商銀行 1000
2 紅包收入 工商銀行 500
3 收入 交通銀行 3000
4 支出 工商銀行 300
5 支出 交通銀行 770

需求：查詢出每個賬戶的收支總額，分別是多少？
賬戶 收支項 總金額
工商銀行 收入 1500
工商銀行 支出 300
交通銀行 收入 3000
交通銀行 支出 770

select 賬戶,收支項,sum(金額) from trans group by 賬戶,收支項;//先按賬戶分，再按收支項分。

04.SQL語句的執行順序：
1).from
2).where
3).group by
4).having
5).select
6).distinct
7).order by

SQL語句的編寫順序：
select ... from ... where ... group by ... having ... order by ...;
05.分頁查詢：
1).基本語句：select * from 表名 limit M,N;
M值：從第幾條(第一條記錄為0)記錄開始取。
N值：取幾條記錄
2).例如：查詢所有的商品，每頁顯示5條：
第一頁：
select * from product limit 0,5;
第二頁：
select * from product limit 5,5;
第三頁：
select * from product limit 10,5

固定演算法：
select * from product limit (當前的頁數 - 1) * 每頁顯示的條數

例題:SELECT * FROM products LIMIT 2; //表示從0頁開始,取2條記錄.


3).註意：M值和N值，只要是正數，不會拋異常，可能會返回空結果集。
但如果是負數，會拋異常。
06.備份和恢複數據庫：
1).備份：在要備份的資料庫上右鍵-->備份/導出-->以SQL轉儲文件備份資料庫
2).恢復：在SQLYog左側右鍵-->導入-->從SQL轉儲文件導入資料庫
07.SQL的約束：
1).主鍵約束：
1).主鍵的作用：唯一標識表中一條記錄。用於作為條件，方便的進行增刪改查操作。
2).定義主鍵：
create table product(
pid int primary key,
..其它欄位..
..
)

?方式二：創建表時，在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)
)

先來看SQL Serve的ISNULL函數：

 

ISNULL(check_expression,replacement_value)

1、check_expression與replacement_value的數據類型必須一致。
2、如果check_expression為NULL,則返回replacement_value。
3、如果check_expression為NULL,則返回check_expression。

再來看Mysql的IFNULL函數：

IFNULL(expr1,expr2)
如果expr1不是NULL,IFNULL()返回expr1,否則返回expr2。

下麵來看下SQLServer中ISNULL與NULLIF的具體用法。
SQL Server里的 ISNULL 與 ASP 中的 IsNull不同，SQL Server 中有兩個參數。

一，ISNULL 語法：
ISNULL(check_expression, replacement_value)

1，check_expression 與 replacement_value 數據類型必須一致
2，如果 check_expression 為 NULL，則返回 replacement_value
3，如果 check_expression 不為 NULL，則返回 check_expression

二，NULLIF 用於檢查兩個表達式。
語法：
NULLIF(expression, expression)

說明：
如果兩個 expression 相等，則返回 NULL，該 NULL 為第一個 expression 的數據類型。
如果兩個 expression 不相等，則返回第一個 expressio。



?方式三：創建表之後，通過修改表結構，聲明指定欄位為主鍵：
ALTER TABLE Persons ADD [CONSTRAINT 名稱] PRIMARY KEY (欄位列表)

3).一個表中只能有一個主鍵；
4).一個主鍵，可以由一個或多個欄位組成[很少用]；複合主鍵，聯合主鍵
客戶信息表：將"客戶姓名" + "工作單位" 同時作為一個主鍵
客戶姓名 工作單位 性別 年齡
張三 人事部 男 20
李四 人事部 女 22
張三 業務部 男 23
張三 人事部 男 18 //錯誤的數據
5).任何類型的欄位都可以做主鍵。當前使用int類型。後期varchar
6).為某個欄位添加了"主鍵約束"，也同時自動添加：唯一約束、非空約束,主鍵也不能為NULL.
7).刪除主鍵約束：
ALTER TABLE 表名 DROP PRIMARY KEY;
2).自動增長：
1).自動增長：讓某列的值根據某個基數，進行自增。這種約束通常用於"主鍵".
2).添加自動增長約束：
create table product(
pid int primary key auto_increment,
....
)
3).清空表對自動增長列的基數的變化：
1).delete from 表名:逐行刪除。不改變自動增長的基數。
2).truncate 表名【效率高】：摧毀表，重建表。將自動增長的基數重新設置為1.

?擴展：預設地，AUTO_INCREMENT 的開始值是 1，如果希望修改起始值，請使用下列 SQL 語法：
ALTER TABLE Persons AUTO_INCREMENT=100

3).非空約束：NOT NULL
1).作用：強制某列的數據不能包含NULL值；
2).添加非空約束：
create table product(
pid int primary key,
pname varchar(200) not null,
....
)
如下添加，會拋出異常：
insert into product values(null,null,...);//第二個null是錯誤，pname欄位不允許null值
3).刪除非空約束
ALTER TABLE 表名 MODIFY 列名 數據類型[長度] (後面不出現not null約束即可，就表示刪除了not NULL約束)
4).唯一約束：unique
1).作用：表示本列的值是唯一的
2).添加唯一約束：
create table product(
pid int primary key,
pname varchar(200) unique,
...
)

?方式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)

如果向pname欄位添加重覆的值，資料庫會拋出異常。
3).如果欄位設置了唯一約束，可以寫入"空字元串"，但只能有一條。
也可以寫入NULL值，可以寫入多條。
4).刪除唯一約束：
ALTER TABLE 表名 DROP INDEX 名稱;
如果添加唯一約束時，沒有設置約束名稱，預設是當前欄位的欄位名
5).主鍵與唯一約束的區別：
主鍵：代表：唯一、非空；一個表只能有一個主鍵；
唯一：只代表：唯一；可以有多個NULL值；一個表可以有多個欄位被設置為唯一約束；
5).預設約束：default 值;
1).作用：可以設置某列的預設值，在添加數據時，可以不指定這列的數據，而使用預設值。
2).設置預設約束：
create table student(
id int primary key auto_increment,
stuName varchar(20) not null,
sex char(5) default '男'
)
在添加時，如果要使用預設值：
INSERT INTO student VALUES(NULL,'bbb',DEFAULT);
3).刪除預設約束：
ALTER TABLE 表名 MODIFY 列名 數據類型[長度](後面不要出現default關鍵字即可)
-----------------------------------------------------------------------------------------------------------------------------
08.多表_分表的作用：
1.在製作表時要註意：一個表只描述一件事情。如果需要描述多件事情，可以創建多表，然後通過某個欄位去引用
另一個表的數據。這樣可以使每個表的數據單獨管理，互不影響。
2.分表後：
主表：被其它表引用的表；
從表：引用其它表的表；
09.多表_表和表之間的關係：
1.一對多關係【最常用】：
1).應用場景：客戶和訂單；分類和商品；部門和員工
客戶表:主表 訂單表：從表 外鍵
----------------------------------------------------------------------------------------
客戶ID 登錄名 支付寶 訂單ID 訂單時間 總金額 客戶ID
001 zhangsan xxx 001 xxx xxx 001
002 xxx xxx 001

2).建表原則：在從表(多方)創建一個欄位，欄位作為外鍵指向主表(一方)的主鍵.

2.多對多關係【較常用】：
1).應用場景：訂單和商品、用戶和角色
訂單表 商品表：
-----------------------------------------------------------------------------------------
訂單ID 訂單日期 總金額 商品ID 名稱 單價
d001 2017-07-04 100 p001 奧利奧 5.5
d002 2017-07-05 200 p002 紅牛 4
p003 啤酒 2.00

訂單_商品_關係表
訂單Id 商品ID 數量 總價
d001 p001 2 11
d001 p002 3 12
d001 p003
d002 p001
2).建表原則：需要創建第三張表,中間表中至少兩個欄位，這兩個欄位分別作為外鍵指向各自一方的主鍵。
3.一對一關係【不建議用】：
1).客戶信息表： 地址表
姓名 性別 年齡 地址ID id 省 市 區 街道門牌
張三 男 22 01 01 北京 北京 順義 99號
李四 女 23 02 02 河北 廊坊 安次 88號
------------------------------------------------------------------------------------------------
合併為一個客戶表：
姓名 性別 年齡 省 市 區 街道門牌
張三 男 22 北京 北京 順義 99號
李四 女 23 河北 廊坊 安次 88號
10.外鍵約束：
1).作用：設置在"從表"的外鍵欄位上，可以強制外鍵欄位的值必須參考主表中的主鍵欄位的值。
2).設置外鍵約束：
alter table 從表 add [constraint] [外鍵名稱] foreign key (從表外鍵欄位名) references 主表 (主表的主鍵);
例如:ALTER TABLE product1 addconstraint fk_product1 FOREIGN KEY(categoryId) REFERENCES category(cid);
3).使用外鍵目的：
保證數據完整性(在有外鍵的表中不能查詢的同時,再進行修改表中數據)


如何刪除外鍵？
sp_help 表名 可以看到外鍵名
alter table xxx drop constraint fk_xxx
===============================================================================================================================
學習目標總結：
3，能夠使用SQL語句進行排序
a， 說出排序語句中的升序和降序關鍵字
order by 欄位名 ASC(升序-預設) / DESC(降序)
b， 寫出排序語句
select * from product order by price desc;
4，能夠使用聚合函數
a， 寫出獲取總記錄數的SQL語句
select count(*) from product;
b， 寫出獲取某一列數據總和的SQL語句
select sum(price) from product;
c， 寫出獲取某一列數據平均值的SQL語句
select avg(price) ...
d， 寫出獲取某一列數據的最大值的SQL語句
select max(price) ...
e， 寫出獲取某一列數據的最小值的SQL語句
select min(price) ...
5，能夠使用SQL語句進行分組查詢
a， 寫出分組的SQL語句
group by 欄位名
b， 寫出分組後條件過濾器的SQL語句
gruup by 欄位名 having 聚合函數 條件;
6，能夠完成數據的備份和恢復
1.備份：在要備份的資料庫上右鍵-->備份/導出-->以SQL轉儲文件備份資料庫
2.恢復：在SQLYog左邊右鍵-->導入-->以SQL轉儲文件導入資料庫。
7，能夠使用可視化工具連接資料庫,操作資料庫
使用SQLYog連接資料庫。操作資料庫

8，能夠說出多表之間的關係及其建表原則
a， 說出一對多的應用場景及其建表原則
1).應用場景：客戶和訂單，分類和商品，部門和員工.
2).在從表(多方)創建一個欄位，欄位作為外鍵指向主表(一方)的主鍵.
b， 說出多對多的應用場景及其建表原則
1).應用場景：學生和課程、用戶和角色
2).需要創建第三張表,中間表中至少兩個欄位，這兩個欄位分別作為外鍵指向各自一方的主鍵.
9，能夠理解外鍵約束
a， 說出外鍵約束的作用
作用：強制外鍵欄位的值必須參考主表中主鍵欄位的值。
b， 寫出創建外鍵的SQL語句
alter table product add constraint fk_fkname foreign key (categoryid) references category (cid);
c， 通過sql語句能夠建立多表及其關係
創建表的外鍵，並且創建外鍵約束。

能夠使用dos視窗的方式進行數據的備份和恢復（不要求記憶，但是要求保留一份最標準的操作步驟）


資料庫備份
資料庫的備份是指將資料庫轉換成對應的sql文件。資料庫導出sql腳本的命令如下：
?mysqldump –u用戶名 –p密碼 資料庫名>生成的腳本文件路徑;
以上備份資料庫的命令中需要用戶名和密碼，即表明該命令要在用戶沒有登錄的情況下使用
資料庫恢復
資料庫的恢復指的是使用備份產生的sql文件恢複數據庫，即將sql文件中的sql語句執行就可以恢複數據庫內容。因為資料庫備份只是備份了資料庫內容，所以備份產生的sql文件中沒有創建資料庫的sql語句，在恢複數據庫之前需要自己動手創建資料庫。
?在資料庫外恢復
mysql -u root -p 資料庫名 < 文件路徑
註意:要求資料庫必須先創建出來.
?.在資料庫內恢復
source SQL腳本路徑：使用這種方式恢複數據，首先要登錄資料庫
【筆試面試題】
無
【重點總結】
備份
Mysqldump –u root –p abc mydb>c:/mydb.sql
恢復
1.mysql –u root –p mydb<c:/mydb.sql
source c:/mydb.sql

*/
/*

在架構設計器上,把從表的外鍵拉向主表的主鍵

01.多表查詢_交叉查詢【瞭解】
1.查詢結果=左表的總記錄數 * 右表的總記錄數 -- 笛卡爾積
02.多表查詢_內連接查詢【重點掌握】
1.隱式內連接【常用】：
1).格式：select 欄位列表 from 表1,表2 where 表1和表2的等值關係;
2).例如：查詢商品信息，要顯示所對應類別信息
select * from products , category where products.category_id = catetory.cid;
只保留兩個表的部分欄位，使用表別名：
SELECT p.pname,p.price,c.cname FROM products p , category c WHERE p.category_id = c.cid;
3).練習：查詢"市"的所有信息，並且顯示對應的"省名"
SELECT c.cname AS '市',p.pname AS '省' FROM city c,province p WHERE c.pid = p.pid;

2.顯示內連接：
1).格式：select 欄位列表 from 表1 INNER JOIN 表2 ON 等值關係；
2).例如：查詢商品信息，要顯示所對應類別信息
select * from products p inner join category c on p.category_id = c.cid;
3).練習：查詢"市"的所有信息，並且顯示對應的"省名"
SELECT c.cname AS '市',p.pname AS '省' from city c inner join province p on c.pid = p.pid;
註意：
1.內連接的查詢結果：兩個表中的等值記錄；
2.兩種內連接都可以再添加其它where條件：
隱式內連接：select .. from 表1,表2 where 等值條件 and 其它條件...
顯示內連接：select .. from 表1 inner join 表2 on 等值條件 where 其它條件....
3.兩種查詢的格式說明：
隱式內連接：select .. from 表1,表2 on 等值條件//錯誤
顯示內連接：select .. from 表1 inner join 表2 where 等值條件//OK的
03.多表查詢_外連接查詢【重點掌握】
1.左外連接查詢：
1).格式：select 欄位列表 from 表1 left join 表2 on 等值關係;
2).查詢結果：左表的所有記錄，和右表的等值記錄;
3).例如：需求：查詢出所有商品(包括沒有類別的商品)，有類別的商品要顯示類別名稱。
SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid;

2.右外連接查詢：
1).格式：select 欄位列表 from 表1 right join 表2 on 等值關係；
2).查詢結果：右表的所有記錄，和左表中的等值記錄；
3).例如：需求：查詢出所有的商品類別，如果類別下有商品的，要同時顯示商品信息；
SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;
04.子查詢【重點掌握】
1.在一個查詢內部，可以再寫一個查詢，這個寫在內部的查詢就叫：子查詢；
2.子查詢的結果可以作為另一個查詢：判斷條件，表使用。
3.例子：查詢價格高於"勁霸"的商品信息；
SELECT * FROM products WHERE price > (SELECT price FROM products WHERE pname = '勁霸');
4.練習：
1).查詢化妝品類別的商品信息
a).使用多表連接查詢：
select * from products p , category c where p.category_id = c.cid and c.cname = '化妝品';
b).使用子查詢(單表查詢)
SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妝品');
c).使用子查詢作為第三張表：select * from (子查詢)
SELECT * FROM products p ,(SELECT * FROM category WHERE cname = '化妝品') c WHERE p.category_id = c.cid;
2).查詢所有"家電","服飾"類商品的信息：
select * from products where category_id = 1 or category_id = 2;
改進：
select * from products where category_id in (1,2);
改進：
select * from products where category_id in (select cid from category where cname in ('家電','服飾'));
============================================================================================================================================
學習目標總結：
1，能夠使用內連接進行多表查詢
a， 說出內連接的兩種查詢方式
1.隱式內連接
2.顯示內連接
b， 寫出顯式內連接的SQL語句
select * from products p inner join category c on p.category_id = c.cid;
c， 寫出隱式內連接的SQL語句
select * from products p , category c where p.category_id = c.cid;
2，能夠使用外連接進行多表查詢
a， 說出外連接的兩種查詢方式
1.左外查詢
2.右外查詢
b， 寫出左外連接的SQL語句
select * from products p left join category c on p.category_id = c.cid;//所有左表中的記錄，和右表的等值記錄
c， 寫出右外連接的SQL語句
select * from products p right join category c on p.category_id = c.cid;//所有右表中的記錄，和左表的等值記錄
3，能夠使用子查詢進行多表查詢
select * from products where category_id in (select cid from category where cname in ('家電','服飾'));
================================================================================================================================
擴展：三表聯查：
1.隱式內連接：使用user表，role表，user_role表進行測試
select * from users u , role r,user_role ur where u.uid = ur.uid and ur.rid = r.rid;
2.顯示內連接：
select * from users u inner join user_role ur on u.uid = ur.uid inner join role r on ur.rid = r.rid;

#1.多表交叉查詢
SELECT * FROM products,category;
#2.內連接--隱式內連接
SELECT * FROM category,products WHERE products.category_id = category.cid;
#3.只顯示部分欄位
SELECT p.pname,p.price,c.cname FROM products p , category c WHERE p.category_id = c.cid;
#4.查詢"市"的所有信息，並且顯示對應的"省名"
SELECT c.cname AS '市',p.pname AS '省' FROM city c,province p WHERE c.pid = p.pid;
#5.顯示內連接 查詢商品信息，要顯示所對應類別信息
SELECT p.pname,p.price,c.cname FROM products p INNER JOIN category c ON p.category_id = c.cid;

#6.左外連接查詢
#需求：查詢出所有商品(包括沒有類別的商品)，有類別的商品要顯示類別名稱。
SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid;
#7.右外連接查詢
#需求：查詢出所有的商品類別，如果類別下有商品的，要同時顯示商品信息；
SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

SELECT * FROM products WHERE price > (SELECT price FROM products WHERE pname = '勁霸');

SELECT * FROM products p , category c WHERE p.category_id = c.cid AND c.cname = '化妝品';
SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妝品');
SELECT * FROM products p ,(SELECT * FROM category WHERE cname = '化妝品') c WHERE p.category_id = c.cid;

SELECT * FROM users u , role r,user_role ur WHERE u.uid = ur.uid AND ur.rid = r.rid;
SELECT * FROM users u INNER JOIN user_role ur ON u.uid = ur.uid INNER JOIN role r ON ur.rid = r.rid;


單列多行子查詢
可以使用in,any或all操作
>any：大於子查詢中的最小值。
>all： 大於子查詢中的最大值。
<any：小於子查詢中的最大值。
<all： 小於子查詢中的最小值。
>=any：大於等於子查詢中的最小值。
>=all：大於等於子查詢中的最大值。
<=any：小於等於子查詢中最大值。
<=all：小於等於子查詢中最小值。
!=any或<>any：不等於子查詢中的任意值。
!=all或<>all：不等於子查詢中的所有值。
=any：等於子查詢中任意值。
=all：等於子查詢中所有值（無意義）


SQL語句中:
查詢:select * from 表名 where 0/1; 0表示查詢不到任何表信息,1表示可以查詢任何信息 例如:or '1=1 : 就是利用這個sql註入問題,返回一個true,表示登錄成功


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • linux中為什麼刪除文件比創建文件要快，讀取文件和刪除文件的過程是什麼？
    一.為什麼刪除文件比創建文件要快？ 因為刪除文件只是將bitmap點陣圖表中將文件所占據的inode 和dacablock的使用狀態從1變成0，相當於釋放了這些快的使用權。 二.讀取文件和刪除文件的過程？ 讀取文件的過程：cat /test/file1 1、進入 /test 目錄， 檢查 test目錄 ...
  • Apache啟動後出現You don't have permission to access on this server的解決辦法
    安裝好wampserver想在瀏覽器打開運行php的結果，發現： You don't have permission to access on this server 解決辦法是： 在Apache的根目錄打開httpd.conf文件下進行修改： 修改完之後： 然後再試試在瀏覽器輸入127.0.0.1 ...
  • linux中許可權對文件和目錄的影響？
    ######### rwx 許可權對文件和目錄的含義 ############ 代表字元 許可權 對文件的含義 對 目錄 的含義 r 讀許可權 可以看到文件的內容 可以看到目錄下的內容 cat tail more head ls w 寫許可權 可以修改文件的內容 可以在該目錄下，刪除，創建 vim echo ...
  • CentOS7最小化安裝後
    查看ip地址：ip addr/ifconfig 修改ip地址：/etc/sysconfig/network-script/網卡，修改onboot=yes BOOTPROTO=dhcp -->BOOTPROTO=static IP獲取方式改為靜態獲取 ZONE=public firewalld zon ...
  • java - redis學習
    在學習redis之前，我們首先需要瞭解一下NoSQL(非關係資料庫)。非關係型資料庫通常指數據以對象的形式存儲在資料庫中，而對象之間的關係通過每個對象自身的屬性來決定。 為什麼需要NoSQL? (1) High performance - 高併發讀寫 (2) Huge Storage - 海量數據的 ...
  • CentOS7-yum安裝MySQL5.7
    1. 下載並安裝MySQL官方repository 然後安裝 然後安裝mysql伺服器 2.配置mysql資料庫 首先啟動資料庫 登陸資料庫 配置mysql可以設置簡單密碼 查看密碼規則 卸載mysql Repository，不然每次yum會自動更新 ...
  • Spark Scala當中reduce的用法和例子
    [學習筆記] reduce將RDD中元素前兩個傳給輸入函數，產生一個新的return值，將新產生的return值與RDD中下一個元素（即第三個元素）組成兩個元素，再被傳給輸入函數，這樣遞歸運作，直到最後只有一個值為止。*/ val rdd07 = sc.parallelize(1 to 10) va ...
  • java核心技術第四篇之JDBC第二篇
    01.JDBC連接池_連接池的概念： 1).什麼是連接池：對於多用戶程式，為每個用戶單獨創建一個Connection，會使程式降低效率。這時我們可以創建一個"容器"， 這個容器中，先緩存一些Connection對象，有用戶請求，就從池中取出一個Connection對象，當用戶使用完畢， 再將這個Co ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類