SQL資料庫DML和DQL基本指令(圖文結合)

首先示範DML,對資料庫進行增删改

1.建立一個表

– AUTO_INCREMENT:自增

– PRIMARY KEY:主鍵

– UNIQUE:不能重複

CREATE TABLE IF NOT EXISTS `users`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	phoneNum VARCHAR(20) UNIQUE DEFAULT '',
	createTime TIMESTAMP，
	updateTime TIMESTAMP
);
           

2.插入資料

INSERT INTO `user` VALUES (110,'lsh','020-10101','2020-10-10','2020-1-1');
或者
INSERT INTO `user` (name,telPhone,createTime,updateTime)
             VALUES ('james','020-10101010','2020-1-1','2021-1-1') ;
           

需求1,createTime和upadteTime可以自動設定值

createTime是建立時間，upadteTime是更新時間

ALTER TABLE `user` MODIFY `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP;
           

再添加一個

INSERT INTO `user` (name,telPhone)
             VALUES ('gogoing','020-1010101') ;
           

需求2,删除資料

1.删除所有資料

DELETE FROM `user`;
           

2.删除符合條件的資料

DELETE FROM `user` WHERE id = 110;
           

需求3,更新資料

1.更新所有的資料

UPDATE `user` SET `name` = '詹姆斯',telPhone = '110-110';
           

2.更新符合條件的資料

UPDATE `user` SET `name` = '詹姆斯',telPhone = '110-110' WHERE id = 117;
           

再示範DQL,對資料庫進行查詢

1.建立一個表

CREATE TABLE IF NOT EXISTS `products`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	brand VARCHAR(20),
  title VARCHAR(100) NOT NULL,
	price DOUBLE NOT NULL,
	score DECIMAL(2,1),
	voteCnt INT,
	url VARCHAR(100),
	pid INT
);
           

2.導入我們的資料，打開我們的vscode編譯器

先安裝插件

cnpm i mysql2
           

編寫導入資料庫的代碼

const mysql = require('mysql2');
 
const connection = mysql.createConnection({
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: '',//你設定的密碼
  database: '' //你的database名字
});

const statement = `INSERT INTO products SET ?;`
const phoneJson = require('./phone.json');
//phone.json是我們寫的json假資料

for (let phone of phoneJson) {
  connection.query(statement, phone);
}
           

然後運作

node insertSQL.js
           

然後資料庫顯示如下:

需求1,基本查詢

1.查詢表中所有的字段和所有的資料

SELECT * FROM `products`;
           

2.查詢指定的字段

SELECT title,price FROM `products`;
           

3.對字段結果起一個别名

SELECT title as phoneTitle,price as currentPrice FROM `products`;
           

需求2,where條件

1.查詢價格小于1000手機

SELECT * FROM `products` WHERE price < 1000;
           

2.價格不等于999的手機

SELECT * FROM `products` WHERE price != 999;
或者
SELECT * FROM `products` WHERE price <> 999;
           

3.查詢品牌是華為的手機

SELECT * FROM `products` WHERE brand = '華為';
           

需求3,邏輯運算語句

1.價格1000-2000之間手機

SELECT * FROM `products` WHERE price > 1000 AND price < 2000;
或者
SELECT * FROM `products` WHERE price > 1000 && price < 2000;

另外，如果包含等于這種寫法

SELECT * FROM `products` WHERE price BETWEEN 1000 AND 2000;
           

2.價格在5000以上或者是華為手機

SELECT * FROM `products` WHERE price > 5000 OR brand = '華為';
或者
SELECT * FROM `products` WHERE price > 5000 || brand = '華為';
           

3.将某些值設定為null

UPDATE `products` SET url = NULL WHERE id > 85 AND id < 90;
           

4.查詢某一個值是否為null

SELECT * FROM `products` WHERE url IS NULL;
SELECT * FROM `products` WHERE url IS NOT NULL;
           

需求4,模糊查詢

字段裡包含M都會被查詢出來

SELECT * FROM `products` WHERE title LIKE '%M%';
           

第二個字元包含P

SELECT * FROM `products` WHERE title LIKE '_P%';
           

6.IN表示取多個值中的其中一個即可

SELECT * FROM `products` WHERE brand IN('華為','小米','蘋果');
           

需求5,對查詢結果進行排序

價格升序

SELECT * FROM `products` WHERE brand IN('華為','小米','蘋果') ORDER BY price ASC;
           

價格相同的話，按評分高低

SELECT * FROM `products` WHERE brand IN('華為','小米','蘋果') ORDER BY price ASC , score DESC;
           

需求6,分頁查詢

limit條數，offset偏移量

SELECT * FROM `products` LIMIT 20 OFFSET 0 ;
SELECT * FROM `products` LIMIT 20 OFFSET 20 ;
           

簡約寫法,偏移數在前

SELECT * FROM `products` LIMIT 0 , 20 ;