簡單的SQL語句學習

俠客小飛發表於2020-07-07
SQL
CREATE DATABASE db_test;
USE db_test;
CREATE TABLE USER(
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30) NOT NULL,
    PASSWORD VARCHAR(30) NOT NULL,
    uname VARCHAR(10) NOT NULL,
    phone VARCHAR(11) UNIQUE 
     
)
/*
資料庫增加
*/
INSERT INTO USER(uid,username,PASSWORD,uname,phone)
    VALUES(NULL,"馬文飛","123456","馬文飛","16637809505");
INSERT INTO USER(uid,username,PASSWORD,uname,phone)
    VALUES(NULL,"翟華兵","123456","翟華兵","16637809506");
    
/*
資料庫更改
*/
UPDATE USER SET phone = "16637801234" WHERE uid = 2;
/*
資料庫刪除
*/
DELETE FROM USER; 
DELETE FROM USER WHERE uname = "馬文飛"; 

#將這張表刪除，再建立一個新表，是新的主鍵索引
TRUNCATE TABLE USER;

/*
檢視資料庫
*/    
SELECT uname,phone FROM USER;
SELECT uname,phone FROM USER WHERE uid = "1";
SELECT MAX(uid) FROM USER;
SELECT MIN(uid) FROM USER;
SELECT SUM(uid) FROM USER;
SELECT AVG(uid) FROM USER;
SELECT COUNT(uid) FROM USER;

#按照uid降序排列
SELECT * FROM USER ORDER BY uid DESC;
SELECT * FROM USER ORDER BY uid ;

 

相關文章