測試人員必會SQL命令

静水流深0801發表於2024-03-29
SQL

一、資料庫相關的SQL

1.建立資料庫 test

CREATE DATABASE test;
CREATE DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2.檢視資料庫建立是否成功 

SHOW DATABASES;

3.刪除庫 test 

DROP DATABASE test;

4.進入testdb6 庫

USE testdb6 ;

二、表相關的SQL

1.建立test和weisi表

CREATE TABLE test (

id INT(10) NOT NULL UNIQUE PRIMARY KEY ,

uname VARCHAR(20) NOT NULL ,

sex VARCHAR(4) ,

birth YEAR,

department VARCHAR(20) ,

address VARCHAR(50) ,

weisi VARCHAR(20)

);

weisi表

CREATE TABLE weisi (

id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,

stu_id INT(10) NOT NULL ,

c_name VARCHAR(20) ,

test VARCHAR(50) ,

grade INT(10)

);

2.顯示所有表

SHOW TABLES;

3.複製test表結構，建立新表test2

注：可以有兩種方式

CREATE TABLE test2 LIKE test ;

或

CREATE TABLE weisi2 AS SELECT * FROM weisi WHERE 2=1;

4.複製weisi表結構和資料，建立新表weisi3

CREATE TABLE weisi3 AS SELECT * FROM weisi

5.複製weisi表結構的 id,stu_id,test三個欄位，建立新表weisi3

CREATE TABLE weisi3 AS SELECT id,stu_id,test FROM weisi WHERE 1<>1;

6.刪除表 test2

DROP TABLE test2 ;

7.同時刪除表weisi2和weisi3

DROP TABLE weisi2,weisi3 ;

8.修改test表，新增一個欄位test6 （字元型別VARCHAR，長度100，不允許為空）

ALTER TABLE test ADD COLUMN test6 VARCHAR(100) NOT NULL;

9.檢視錶中的所有欄位

DESC test

10.修改test表，刪除欄位test6

ALTER TABLE test DROP test6

11.把表weisi6，改名為weisi 

RENAME TABLE weisi6 TO weisi;

或

ALTER TABLE weisi6 RENAME weisi;

12.把weisi表，改名為 weisi6

ALTER TABLE 舊錶名 RENAME TO 新表名 ;

ALTER TABLE weisi RENAME TO weisi681 ;

13.修改表weisi，把欄位test ，改為test6（字元型別varchar，長度160 ）

ALTER TABLE weisi CHANGE test test6 VARCHAR(160);

14.在資料庫 testdb2 ，建立weisi表，直接複製 test庫weisi表的資料和結構 ；

CREATE TABLE testdb2.weisi AS SELECT * FROM test.weisi;

三、表中資料相關的SQL

1.向表中插入資料

向表test插入資料

id = 1 ,uname = weisi ,weisi = 2020

INSERT INTO test(id,uname,sex) VALUES(14,"weisi6",2);

向表weisi插入資料

id=4,stu_id=11,c_name=weisi,grade=90

id=5,stu_id=12,c_name=lin,grade=100

id=6,stu_id=33,c_name=test,grade=20

INSERT INTO weisi(id,stu_id,c_name,grade) VALUES(4,11,"weisi",90),(5,12,"lin",100),(6,33,"test",20);

SELECT * FROM weisi;

造資料 ，把test表的所有資料，插入到 weisi表

欄位關係

id 取id

stu_id 取id

c_name 取 uname

test 和 grade欄位，給預設值 60

INSERT INTO weisi(id,stu_id,c_name,test,grade) SELECT id,id,uname,60,60 FROM test ;

2.查詢表中的資料

#查詢test表 id = 1的內容

SELECT * FROM test WHERE id = 1;

#查詢weisi表，名稱（c_name）包含 “i” 的資料

SELECT * FROM weisi WHERE c_name LIKE '%i%' ;

#查詢test表，id 包含 “1” 的資料，按id降序

SELECT * FROM test WHERE id LIKE '%1%' ORDER BY id DESC ;

#查詢test表，id 包含 “1” 的資料 ，取id最大的三個

SELECT * FROM test WHERE id LIKE '%1%' ORDER BY id DESC LIMIT 3 ;

#找出weisi表中，分數最高的同學和分數；

SELECT c_name,grade AS "maxvalue" FROM weisi WHERE grade IN (SELECT MAX(grade) FROM weisi ) ;

#找出weisi表中，分數最低的同學和分數；

SELECT c_name,grade AS "minvalue" FROM weisi WHERE grade IN (SELECT MIN(grade) FROM weisi );

#找出test表，sex為空的的資料；

SELECT * FROM test WHERE sex IS NULL ;

#查詢test表，有多少行資料

SELECT COUNT(sex) FROM test;

#查詢test表，有性別型別數量（sex欄位，去重）

SELECT COUNT(DISTINCT sex) FROM test;

#查詢weisi表，成績在80 - 100區間的學生 ；

SELECT * FROM weisi WHERE grade BETWEEN 80 AND 100;

#查詢test表，id 為 2，11，12 的資料 ；

SELECT * FROM test WHERE id IN (2,11,12) ;

limit是mysql的語法

select * from table limit m,n

其中m是指記錄開始的index，從0開始，表示第一條記錄

n是指從第m+1條開始，取n條。

select * from tablename limit 2,4

即取出第3條至第6條，4條記錄

#排名3 - 6名的學生 和分數 ； 

SELECT c_name,grade FROM weisi ORDER BY grade DESC LIMIT 2,4;

#左連線 

SELECT * FROM weisi a LEFT JOIN test b ON a.stu_id=b.id;

#內連線 

SELECT * FROM weisi a INNER JOIN test b ON a.stu_id=b.id;

#右連線 

SELECT * FROM weisi a RIGHT JOIN test b ON a.stu_id=b.id;

3.修改表中的資料

#更新test表，sex為空的，設定為0（性別未知）

UPDATE test SET sex = 0 WHERE sex IS NULL ;

#關聯更新

UPDATE 表1

INNER JOIN 表2  ON 表1.`id_` = 表2.`order_id`

INNER JOIN 表3 ON 表1.`id_` = 表3.`order_id`

SET 表1.`update_time_`= 1650617212846000,

    表2.`update_time_` = 1650617212846000,

    表3.`update_time_` = 1650617212846000

WHERE 表1.user LIKE 'zs%';

4.刪除表中的資料

#刪除 test表，id大於12的資料 ；

DELETE FROM test WHERE id > 12;

#關聯刪除

DELETE 表1,表2,表3 FROM 表1

INNER JOIN 表2 ON 表1.id_ = 表2.order_id

INNER JOIN 表3 ON 表1.id_ = 表3.order_id WHERE 表1.user LIKE 'zs%';

#清空weisi表的資料 

TRUNCATE TABLE weisi;

或

DELETE FROM weisi;

四、檢視資料庫版本、字符集

1.檢視Mysql版本

SELECT VERSION();

2.修改資料庫的字符集

ALTER DATABASE 資料庫名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3.修改表的字符集

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

五、日期時間相關

1.獲取當前系統的日期和時間

select now();

2.日期轉時間戳

SELECT UNIX_TIMESTAMP('2022-01-01 00:00:00');

如果想得到13位的時間戳，需要再乘以1000

SELECT 1000*UNIX_TIMESTAMP('2022-01-01 00:00:00');

3.sql中實現一個日期減去幾天，幾個月，幾年

SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);#1天前的日期
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);#1月前的日期
SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR);#1年前的日期
sql中實現一個日期加上幾天，幾個月，幾年
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);#1天后的日期
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);#1月後的日期
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);#1年後的日期

六、使用變數

1. 定義變數：

2. 使用變數

SET @starttime =1000*UNIX_TIMESTAMP('2022-01-01 00:00:00');
SELECT @starttime;

七、常見問題彙總

1.插入insert into 語句中欄位包含中文時報錯

解決：

插入中文時報錯的原因可能是字符集設定不正確。請確保資料庫、表和列的字符集設定為支援中文的字符集，如utf8或utf8mb4。

（持續更新中...）

相關文章