mysql資料記錄

準備作業

創建庫、表

#創建資料庫
create database mysql02;

#使用資料庫
use mysql02;

#創建表
create table student(id int ,name varchar(10),age int);

查看student表結構

資料增刪改操作

知識點

插入資料:insert into 表名(欄位名1,欄位名2,...) values(值1,值2,...),(...),...;
	注意:可以不指定欄位，那么默認指定所有欄位，插入values時，要和欄位一一對應，
	注意:如果插入多條資料，中間用','分隔
洗掉資料:delete from 表名 where 條件;
修改資料:update 表名 set 欄位名 = 值 where 條件;
注意:條件中，判斷為空是is null,非空是is not null

示例

# 1.演示資料記錄的插入操作
# 指定欄位中插入資料
insert into student(name) values ('熊大');
insert into student(name,age) values('熊大',8);
insert into student(id,name,age) values(1,'熊大',2);
#指定欄位插入多條資料
INSERT INTO student(id, name, age) VALUES (2,'熊二',2),(3,'熊三',2);

# 不指定欄位(默認所有欄位)插入資料
INSERT INTO student VALUES (4,'李四',23),(5,'王五',32);

查看此表:

# 2.演示資料記錄的洗掉操作
# 空判斷 : is null 為空 is not null:不為空
# 根據條件洗掉部分資料
delete from student where name = '王五';
delete from student where id is null;

洗掉之后的表:

注意！如果不加where后面的條件，即delete from 表名;就默認是洗掉整個表，一般會出現警告！不建議這樣洗掉表，可以使用truncate 表名;來清空整個表，

truncate student;

使用truncate命令后的表:

# 3.演示資料記錄的更新操作
# 根據條件修改部分資料
# 需求: 把'熊三'改成'張三'
update student set name = '張三' where name = '熊三';

改名后的表:

注意，update沒有加where條件就是改所有資料，慎用！

資料約束

主鍵約束

主鍵約束關鍵字: primary key
特點: 修飾的欄位,值不能為空且不能重復,一般主鍵名為xxid
注意: 主鍵約束建議建表的時候添加
個數: 主鍵約束一個表有且只有一個

# 主鍵約束: primary key 特點: 修飾的欄位xid,不能為空,不能重復
# 創建表
create table stu1(id int primary key,name varchar(10),age int);

查看表結構:

# 添加主鍵方式除了創建表的時候能添加,建表后依然可以添加,但是必須保證需要添加主鍵約束的每個欄位都滿足非空不重復，否則添加失敗，
# 需求: 給上述student表添加
alter table student add primary key (id);

主鍵自增

主鍵自增: primary key auto_increment 特點: 主鍵需要是int型別,不指定具體值,從1開始每次自動加1
注意1: 主鍵自增建議建表的時候添加
注意2: 給主鍵添加了自增后,在插入資料的時候,不指定id默認自增,也可以使用null和0占位默認代表使用自增

# 創建表
create table stu2(id int primary key auto_increment,name varchar(10),age int);

查看表結構:

# 添加自增方式除了創建表的時候能添加,建表后依然可以添加,如果要添加主鍵自增，那么表必須沒有主鍵，如果僅添加自增，則需要此表有主鍵，在這個主鍵上進而添加自增
#首先洗掉student表的主鍵
alter table student drop PRIMARY KEY ;
#之后在student表中添加主鍵自增
alter table student change id id int PRIMARY KEY AUTO_INCREMENT;

#在stu1表中添加自增
alter table stu1 CHANGE id id int AUTO_INCREMENT;

非空約束

非空約束關鍵字: not null
特點: 修飾的欄位,值不能為空
注意: 建議建表的時候添加
個數: 非空約束一個表有且可以只有多個

# 特點: 修飾的欄位,值不能為空
# 個數: 非空約束一個表有且可以只有多個
# 創建表
create table stu3(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(100) not null,
age int
);
# 查看表結構
desc stu3;
# 演示非空約束特點: 不能為空
insert into stu3 (name,age) values (null,18); # 插入失敗,因為name值不能為空
insert into stu3 (name,age) values ('張三',18);# 插入成功
# 當然建表后也可以添加非空約束的
alter table stu3 CHANGE age age int not null;
# 也可以手動洗掉非空約束
alter table stu3 CHANGE age age int;

唯一約束

唯一約束關鍵字: unique
特點: 修飾的欄位,值不能重復
注意: 建議建表的時候添加
個數: 唯一約束一個表有且可以只有多個

# 創建表
create table stu4(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(100) UNIQUE ,
age int
);
# 查看表結構
desc stu4;
insert into stu4 (name,age) values (null,18); # 可以插入空值
insert into stu4 (name,age) values (null,28); # 可以插入空值(注意:空值沒有重復之說)
# 演示唯一約束特點: 不能重復
insert into stu4 (name,age) values ('張三',18);# 插入成功
insert into stu4 (name,age) values ('張三',28);# 插入失敗,唯一約束特點: 不能重復
# 當然建表后也可以添加唯一約束的
alter table stu4 CHANGE age age int UNIQUE ;
# 也可以手動洗掉唯一約束
alter table stu4 drop INDEX age;

默認約束

默認約束關鍵字: default
特點: 修飾的欄位,值可以設定默認值
注意: 建議建表的時候添加 欄位名 欄位型別 default 默認值
個數: 默認約束一個表有且可以只有多個

# 創建表
create table stu5(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(100) DEFAULT 'admin',
age int
);
# 查看表結構
desc stu5;
# 注意: 如果你指定姓名,以你指定的為主
insert into stu5 (name,age) values ('張三',18);# 插入成功,但是沒有使用默認值
# 演示默認約束特點: 不指定對應欄位插資料,自動使用默認值
insert into stu5 (age) values (28);# 使用默認值,默認約束特點:你不指定就使用默認
# 當然建表后也可以添加唯一約束的
alter table stu5 CHANGE age age int DEFAULT 18 ;
# 也可以手動洗掉唯一約束
alter table stu5 CHANGE age age int;

資料查詢操作

1.準備資料

# 創建商品表：
CREATE TABLE product
(
pid INT PRIMARY KEY,
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,'杰克瓊斯',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,'海瀾之家',1,'c002');

2.簡單查詢

知識點

基礎查詢關鍵字: select from
基礎格式: select [distinct] 列名,列名 | * from 表名;
distinct關鍵字: 根據指定欄位去除重復值
as關鍵字: 可以給表或者欄位起別名
[]: 代表可以省略
| : 代表或者的意思
* : 代表所有欄位

示例

# 1.簡單查詢
# 需求: 查詢所有商品資訊
select * from product;

# 需求: 查詢商品名稱和商品價格
select pname,price from product;

# 需求: 查詢分類編號要求去重
select DISTINCT category_id from product;

# 需求: 給商品名稱,商品價格欄位起別名
select pname as '商品名稱' ,price as '商品價格' from product; # 實際不建議用中文別名

# 給表起別名: 雙擊左上角庫名查看資料底層執行的代碼就使用了別名
SELECT t.* FROM mysql02.product t; # 默認省略了as

3.條件查詢

知識點

條件查詢關鍵字: where
基礎格式: select 列名 from 表名 where 條件;
比較查詢: > < >= <= != 和 <>
邏輯查詢: and(并且) or(或者) not(取反)
范圍查詢: between x and y: 連續范圍x到y in(x,y): 非連續范圍x或者y
模糊查詢: 關鍵字like % : 0個或者多個字符 _ : 1個字符
空判斷 : is null:判斷為空 is not null: 判斷不為空

示例

# 基礎格式: select 列名 from 表名 where 條件;
# 需求: 查詢價格大于2000的商品資訊
SELECT *
FROM
    product
WHERE
    price > 2000;

# 需求: 查詢價格大于等于3000的商品資訊
SELECT *
FROM
    product
WHERE
    price >= 3000;

# 需求: 查詢價格在200(含)到3000(含)區間范圍內的商品資訊
SELECT *
FROM
    product
WHERE
    price BETWEEN 200 AND 3000;

# 需求: 查詢價格不是200的商品資訊
SELECT *
FROM
    product
WHERE
    price != 200;

# 需求: 查詢價格是3000或者5000的商品資訊
SELECT *
FROM
    product
WHERE
    price IN (3000, 5000);

# 需求: 查詢價格 不在 200(含)到3000(含)區間范圍內的商品資訊
SELECT *
FROM
    product
WHERE
    price NOT BETWEEN 200 AND 3000;

# 需求: 查詢商品名稱以'香'開頭的商品資訊
SELECT *
FROM
    product
WHERE
    pname LIKE '香%';

# 需求: 查詢商品名稱以'斯'結尾的商品資訊
SELECT *
FROM
    product
WHERE
    pname LIKE '%斯';

# 需求: 查詢商品名稱是3個字,并且以'斯'結尾的商品資訊
SELECT *
FROM
    product
WHERE
    pname LIKE '__斯';

# 需求: 查詢第二個字是'想'的商品資訊
SELECT *
FROM
    product
WHERE
    pname LIKE '_想%';

# 注意: mysql中的null不等于空字串,或者字串'null'
# 需求: 查詢商品分類不為空的商品資訊
SELECT *
FROM
    product
WHERE
    category_id IS NOT NULL;
# 需求: 查詢商品分類為空的商品資訊
SELECT *
FROM
    product
WHERE
    category_id IS NULL;

4.排序查詢

知識點

排序查詢關鍵字: order by
基礎格式: select 列名 from 表名 order by 排序列名 asc|desc;
| : 代表或者的意思
asc : 升序(默認)
desc: 降序
特殊格式: select 列名 from 表名 order by 排序列名1 asc|desc , 排序列名2 asc|desc;
特殊格式注意: 先按照第一個欄位排序,如果第一個欄位值相同,再按照第二個欄位排序

示例

# 基礎格式: select 列名 from 表名 order by 排序列名 asc|desc;
# 需求: 查詢商品所有資訊,要求價格升序排序
select * from product ORDER BY price asc; # 報黃,提示多余寫asc,因為默認就是升序
select * from product ORDER BY price; # 以后如果是升序,就不用寫asc了
# 需求: 查詢商品所有資訊,要求價格降序排序
select * from product ORDER BY price desc;
# 特殊格式: select 列名 from 表名 order by 排序列名1 asc|desc , 排序列名2 asc|desc;
# 需求: 查詢商品所有資訊,要求先按照價格降序排序,如果價格相同再按照分類id降序排序
select * from product ORDER BY price desc,category_id desc;

5.聚合查詢

知識點

聚合函式:sum() avg() count() max() min()
基礎格式:select 聚合函式(列名) from 表名;
注意1: 聚合函式又叫分組函式或者統計函式
注意2: 聚合函式(列名)這種方式會自動忽略null值

示例

# 需求: 查詢所有商品價格總和
SELECT sum(price) from product;

# 需求: 查詢所有商品總個數
SELECT count(*) FROM product;

# 需求: 查詢所有商品平均價格
SELECT avg(price) FROM product;

# 需求: 查詢所有商品最大價格是多少
SELECT max(price) FROM product;

# 需求: 查詢所有商品最小價格是多少
SELECT min(price) FROM product;

# 需求: 查詢分類id為c001的所有商品價格的總和
SELECT sum(price) FROM product WHERE category_id = 'c001';

6.分組查詢

知識點

分組關鍵字: group by
基礎格式: select 列名 from 表名 group by 分組列名;
特殊格式: select 列名 from 表名 where 非聚合條件 group by 分組列名 having 聚合條件;
having與where的區別:
1).where是在分組前對資料進行過濾, having是在分組后對資料進行過濾
2).where后面不可以使用分組函式(聚合函式),having后面可以使用分組函式(聚合函式),

示例

# 需求: 查詢各個分類的單獨的商品價格總和
SELECT sum(price) FROM product GROUP BY category_id;

# 特殊需求: 查詢各個分類的單獨的商品價格總和,并且篩選總和小于5000的分類
SELECT category_id FROM product GROUP BY category_id HAVING sum(price) < 5000;

# 特殊需求: 查詢所有商品價格小于3000的,并且分組統計每個分類的總和,最后只顯示總和小于5000
SELECT category_id FROM product WHERE price < 3000 GROUP BY category_id HAVING sum(price) < 5000;

7.分頁查詢

知識點

分頁查詢關鍵字: limit
基礎格式: select 列名 from 表名 limit x,y;
x: 整數 起始索引(從0開始數) 如果從0開始那么0可以省略
y: 整數 本次查詢多少條資料

示例

# 經常用于資料庫優化 查資料建議加limit關鍵字
# 需求: 查詢前3條資料
# 需求: 查詢前3條資料
SELECT * FROM product limit 0,3;  #0可以省略

# 需求: 查詢最高商品價格的前5個商品資訊
SELECT * FROM product ORDER BY price desc limit 5;

# 分頁需求:一共16條資料,每頁4條
# 需求: 查詢每頁資料
SELECT * FROM product limit 0,4;
SELECT * FROM product limit 4,4;
SELECT * FROM product limit 8,4;
SELECT * FROM product limit 12,4;
# 推出計算起始索引結論: x=(頁數-1)*y

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/552172.html

標籤：其他

上一篇：從快取的本質說起，說服技術大佬用Redis

下一篇：返回列表