mysql基礎

SQL陳述句

SQL陳述句分類

• SQL分類：
  • 資料定義語言：簡稱DDL(Data Definition Language)，用來定義資料庫物件：資料庫，表，列等，關鍵字：create，alter，drop等
  • 資料操作語言：簡稱DML(Data Manipulation Language)，用來對資料庫中表的記錄進行更新，關鍵字：insert，delete，update等
  • 資料控制語言：簡稱DCL(Data Control Language)，用來定義資料庫的訪問權限和安全級別，及創建用戶，DBA
  • 資料查詢語言：簡稱DQL(Data Query Language)，用來查詢資料庫中表的記錄，關鍵字：select，from，where等

SQL通用語法

• SQL陳述句可以單行或多行書寫，以分號結尾
• 可使用空格和縮進來增強陳述句的可讀性
• MySQL資料庫的SQL陳述句不區分大小寫，關鍵字建議使用大寫
  • 例如：SELECT * FROM user，
• 同樣可以使用/**/的方式完成注釋
• MySQL中的我們常使用的資料型別如下

DDL之資料庫操作：database

創建資料庫

格式：

create database 資料庫名;
create database 資料庫名 character set 字符集;

例如：

#創建資料庫 資料庫中資料的編碼采用的是安裝資料庫時指定的默認編碼 utf8
CREATE DATABASE webdb_1; 
#創建資料庫 并指定資料庫中資料的編碼
CREATE DATABASE webdb_2 CHARACTER SET utf8;

查看資料庫

查看資料庫MySQL服務器中的所有的資料庫:

  show databases;

查看某個資料庫的定義的資訊:

show create database 資料庫名;

例如：

show create database webdb_1;

洗掉資料庫

drop database 資料庫名稱;

例如：

drop database webdb_2;

使用資料庫

• 查看正在使用的資料庫:

select database();

• 其他的資料庫操作命令

  切換資料庫：

use 資料庫名;

例如：

use webdb_1;

DDL之表操作：table

創建表

• 格式：

create table 表名(
   欄位名 型別(長度) [約束],
   欄位名 型別(長度) [約束],
   ...
);

型別：
	varchar(n)	字串
	int			整形
	double		浮點
	date		時間
	timestamp	時間戳
約束：(詳情查看第四章)
	primary key	主鍵，被主鍵修飾欄位中的資料，不能重復、不能為null，

• 例如：創建分類表

CREATE TABLE category (
  cid INT primary key, #分類ID 
  cname VARCHAR(100) #分類名稱
);

查看表

• 查看資料庫中的所有表：

格式：show tables;

• 查看表結構：

格式：desc 表名;
例如：desc category;

洗掉表

• 格式：drop table 表名;

例如：drop table category;

修改表結構格式

• 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, 為分類表的分類名稱欄位進行更換 更換為 description varchar(30)
ALTER TABLE category CHANGE `desc` description VARCHAR(30);

• alter table 表名 drop 列名;

  作用：修改表洗掉列.

例如：#4, 洗掉分類表中description這列
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;

DML資料操作語言

插入表記錄：insert

• 語法：

-- 向表中插入某些欄位
insert into 表 (欄位1,欄位2,欄位3..) values  (值1,值2,值3..); 
-- 向表中插入所有欄位,欄位的順序為創建表時的順序
insert into 表 values (值1,值2,值3..);

• 注意：
  • 值與欄位必須對應，個數相同，型別相同
  • 值的資料大小必須在欄位的長度范圍內
  • 除了數值型別外，其它的欄位型別的值必須使用引號引起，（建議單引號）
  • 如果要插入空值，可以不寫欄位，或者插入null，
• 例如：

INSERT INTO category(cid,cname) VALUES('c001','電器');
INSERT INTO category(cid,cname) VALUES('c002','服飾');
INSERT INTO category(cid,cname) VALUES('c003','化妝品');
INSERT INTO category(cid,cname) VALUES('c004','書籍');

INSERT INTO category(cid) VALUES('c005');
INSERT INTO category(cname,cid) VALUES('耗材','c006');

更新表記錄：update

用來修改指定條件的資料，將滿足條件的記錄指定列修改為指定值

• 語法：

  • 更新所有記錄的指定欄位

  update 表名 set 欄位名=值,欄位名=值,...;
  

  • 更新符合條件記錄的指定欄位

  update 表名 set 欄位名=值,欄位名=值,... where 條件;
  

• 注意：

  • 列名的型別與修改的值要一致.
  • 修改值得時候不能超過最大長度.
  • 除了數值型別外，其它的欄位型別的值必須使用引號引起

洗掉記錄：delete

• 語法：

delete from 表名 [where 條件];

SQL約束

主鍵約束

PRIMARY KEY 約束唯一標識資料庫表中的每條記錄，

• 主鍵必須包含唯一的值，
• 主鍵列不能包含 NULL 值，
• 每個表都應該有一個主鍵，并且每個表只能有一個主鍵，

添加主鍵約束

• 方式一：創建表時，在欄位描述處，宣告指定欄位為主鍵：

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

• 方式三：創建表之后，通過修改表結構，宣告指定欄位為主鍵：
  • 格式：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 約束，請使用下面的 SQL：

ALTER TABLE persons DROP PRIMARY KEY

自動增長列

我們通常希望在每次插入新記錄時，資料庫自動生成欄位的值，

我們可以在表中使用 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')

• 擴展：默認AUTO_INCREMENT 的開始值是 1，如果希望修改起始值，請使用下列 SQL 語法：

ALTER TABLE persons AUTO_INCREMENT=100

• 面試題

問：針對auto_increment ，洗掉表中所有記錄使用 delete from 表名 或使用 truncate table 表名，二者有什么區別？

洗掉方式：
	delete 一條一條洗掉，不清空auto_increment記錄數，
	truncate 直接將表洗掉，重新建表，auto_increment將置為零，從新開始，

非空約束

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

唯一約束

UNIQUE 約束唯一標識資料庫表中的每條記錄，
UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證，
PRIMARY KEY 擁有自動定義的 UNIQUE 約束，
請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束，

添加唯一約束

與主鍵添加方式相同，共有3種

• 方式一：創建表時，在欄位描述處，宣告唯一：

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 約束，請使用下面的 SQL：

ALTER TABLE persons DROP INDEX 名稱

• 如果添加唯一約束時，沒有設定約束名稱，默認是當前欄位的欄位名，

默認約束

default 約束 用于指定欄位默認值，當向表中插入記錄時，如果沒有明確的為欄位賦值，則自動賦予默認值，

• 添加默認約束，在創建表時候添加

CREATE TABLE t_user(user_id INT(10) DEFAULT 3);

• 洗掉默認約束

ALTER TABLE t_user MODIFY user_id INT(10);

SQL陳述句(DQL)

準備作業

#創建商品表：
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);

語法

select [distinct] 
* | 列名,列名 
from 表 
where 條件

簡單查詢

• 練習

 #.查詢常量值
 SELECT 100;
 SELECT 'hello';
 #.查詢運算式
 SELECT 100%98;
 #.查詢函式
 SELECT NOW();
 SELECT VERSION();
 #兩個運算元都為數值型，則做加法運算
 SELECT 100+90; 
 #只要其中一方為字符型，試圖將字符型數值轉換成數值型 如果轉換成功，則繼續做加  法運算
 SELECT '123'+90;
 #如果轉換失敗，則將字符型數值轉換成0
 SELECT 'hello'+90;	
 #只要其中一方為NULL，則結果肯定為NULL
 SELECT NULL+10; 
 #連接字串
 SELECT CONCAT('a','b','c')

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

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

#別名查詢.使用的關鍵字是as（as可以省略的）.表別名:
select * from product as p;

#別名查詢.使用的關鍵字是as（as可以省略的）.列別名:
select pname as pn from product;

#去掉重復值.
select distinct price from product;

#查詢結果是運算式（運算查詢）：將所有商品的價格+10元進行顯示. 
select pname,price+10 from product;

條件查詢

• 練習

#查詢商品名稱為“花花公子”的商品所有資訊：
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;

#查詢商品價格在200到1000之間所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

#查詢商品價格是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 '香%';

#查詢第二個字為'想'的所有商品
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陳述句，可以將查詢出的結果進行排序，暫時放置在select陳述句的最后，

• 格式:

SELECT * FROM 表名 ORDER BY 排序欄位 ASC|DESC;
#ASC 升序 (默認)
#DESC 降序

• 練習：

#使用價格排序(降序)
SELECT * FROM product ORDER BY price DESC;

#在價格排序(降序)的基礎上，以分類排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;

#顯示商品的價格(去重復)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

聚合查詢

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

今天我們學習如下五個聚合函式：

• count：統計指定列不為NULL的記錄行數；

• sum：計算指定列的數值和，如果指定列型別不是數值型別，那么計算結果為0；

• max：計算指定列的最大值，如果指定列是字串型別，那么使用字串排序運算；

• min：計算指定列的最小值，如果指定列是字串型別，那么使用字串排序運算；

• avg：計算指定列的平均值，如果指定列型別不是數值型別，那么計算結果為0；

練習：

#查詢商品的總條數
SELECT COUNT(*) FROM product; 

#查詢價格大于200商品的總條數
SELECT COUNT(*) FROM product WHERE price > 200;

#查詢分類為'c001'的所有商品的總和
SELECT SUM(price) FROM product WHERE category_id = 'c001';

#查詢分類為'c002'所有商品的平均價格
SELECT AVG(price) FROM product WHERE category_id = 'c002';

#查詢商品的最大價格和最小價格
SELECT MAX(price),MIN(price) FROM product;

分組查詢

分組查詢是指使用group by字句對查詢資訊進行分組，

• 格式：

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

分組操作中的having子陳述句，是用于在分組后對資料進行過濾的，作用類似于where條件，

• having與where的區別:

  • having是在分組后對資料進行過濾.

    where是在分組前對資料進行過濾

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

    where后面不可以使用分組函式，

練習：

#統計各個分類商品的個數
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;

#統計各個分類商品的個數,且只顯示個數大于1的資訊
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

分頁查詢

select 列名 from 表名 limit page,pageSize;

0   10  
10  10 
20  10 
30  10 
(當前頁-1)*pageSize

標籤：其他

上一篇：輕松存盤千億級資料，知憾訓于Doris的DMP系統架構實踐

下一篇：返回列表