mysql基礎（附具體操作代碼）-有解無憂

# 注釋內容
-- 注釋內容
-- 創建資料庫 king
CREATE DATABASE king;
-- 查看當前服務器下有哪些資料庫
SHOW DATABASES;
SHOW SCHEMAS;
-- 查看king資料庫的詳細資訊
SHOW CREATE DATABASE king;

-- 創建資料庫queen 并且指定編碼方式為gbk
CREATE DATABASE IF NOT EXISTS queen DEFAULT CHARACTER SET 'GBK';
-- 查看當前服務器下全部的資料庫
SHOW DATABASES;
-- 查看queen指定資料庫的詳細資訊
SHOW CREATE DATABASE queen;
-- 將queen的資料庫的編碼方式改為utf8
ALTER DATABASE queen DEFAULT CHARACTER SET 'UTF8';
-- 打開queen的資料庫
USE queen;
-- 得到當前打開的資料庫
SELECT DATABASE();
SELECT SCHEMA();
-- 洗掉king資料庫
DROP DATABASE IF EXISTS king;
DROP DATABASE IF EXISTS queen;

-- 創建測驗用資料庫 test
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET 'UTF8';

-- 打開test

USE test;

-- 用戶表 test_user

CREATE TABLE IF NOT EXISTS test_user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;

-- 查看test_user表的表結構
DESC test_user;

DESCRIBE test_user;

SHOW COLUMNS FROM test_user;

-- 洗掉test_user表

DROP TABLE IF EXISTS test_user;

-- 查看當前資料庫下的資料表

-- 查看test_user表的詳細資訊

-- 通過三種方式查看指定資料表的表結構

-- 最后洗掉test_user資料表

資料庫增加資料與查詢資料

-- 用戶表 test_user

CREATE TABLE IF NOT EXISTS test_user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM('男','女','保密')
)ENGINE=INNODB CHARSET=UTF8;

-- INSERT [INTO] tbl_name(id,username,...) VALUES(1,'KING',...);

-- 向test_user表插入一條記錄
INSERT test_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(1,'king','king','[email protected]',24,'xxxxxxxxxxxxxxxxxx','132xxxxxxxx',88888.68,0,'寧波','男');

-- 查詢表中所有記錄 SELECT * FROM tbl_name;

SELECT * FROM test_user;

INSERT test_user(id,username,password,email,age,card,tel,salary,married,addr,sex)

VALUES(-5,'king','123456','[email protected]',190,'xxxxxxxxxxxxxxxxxx','132xxxxxxxx',2345.68,1,'寧波','女');

資料型別測驗-測驗整型+無符號+零填充

-- 測驗整型
CREATE TABLE test_int(
    a tinyint,
    b smallint,
    c mediumint,
    d int,
    e bigint
);
INSERT test_int(a) VALUES(-128);
-- 如果超出資料的范圍，會產生截斷現象
INSERT test_int(a) VALUES(-129);
INSERT test_int(a) VALUES(1270);

-- 測驗無符號

CREATE TABLE test_unsigned(
a tinyint,
b tinyint UNSIGNED
);

INSERT test_unsigned(a,b) VALUES(-12,-12);
INSERT test_unsigned(a,b) VALUES(0,0);
INSERT test_unsigned(a,b) VALUES(0,256);

-- 測驗零填充 ZEROFILL
CREATE TABLE test_int1(
    a tinyint ZEROFILL,
    b smallint ZEROFILL,
    c mediumint ZEROFILL,
    d int ZEROFILL,
    e bigint ZEROFILL
);

INSERT test_int1(a,b,c,d,e) VALUES(1,1,1,1,1);

CREATE TABLE test_int2(
    a tinyint(2),
    b smallint(2)
);
INSERT test_int2(a,b) VALUES(123,45678);

資料型別測驗-測驗浮點型

-- 測驗浮點型
CREATE TABLE test_float(
a FLOAT(6,2),
b DOUBLE(6,2),
c DECIMAL(6,2)
);

INSERT test_float(a,b,c) VALUES(4.143,4.146,4.149);

CREATE TABLE test_float1(
a FLOAT,
b DOUBLE,
c DECIMAL
);
INSERT test_float1(a,b,c) VALUES(4.143,4.146,4.649);
INSERT test_float(a,b,c) VALUES(1234567.89,2345678.9,567890.89);

資料型別測驗-測驗char和varchar+text+ENUM+set

-- 測驗char 和 varchar
CREATE TABLE test_str(
    a CHAR(5),
    b VARCHAR(5)
);
INSERT test_str(a,b) VALUES('','');
INSERT test_str(a,b) VALUES('a','a');
INSERT test_str(a,b) VALUES('ab','ab');
INSERT test_str(a,b) VALUES('abc','abc');
INSERT test_str(a,b) VALUES('abcd','abcd');
INSERT test_str(a,b) VALUES('abcde','abcde');
INSERT test_str(a,b) VALUES('abcdef','abcdef');

INSERT test_str(a,b) VALUES(' 123 ',' 123 ');

SELECT CONCAT('*',a,'*'),CONCAT('*',b,'*') FROM test_str;

-- 測驗TEXT不能有默認值
CREATE TABLE test_str1(
 content TEXT DEFAULT 'THIS IS A TEST'
);


-- 測驗ENUM
CREATE TABLE test_enum1(
sex ENUM('男','女 ','保密 ')
);
INSERT test_enum(sex) VALUES('男');
INSERT test_enum(sex) VALUES('男1');
INSERT test_enum(sex) VALUES(NULL);
INSERT test_enum(sex) VALUES(1);
INSERT test_enum(sex) VALUES(3);
INSERT test_enum(sex) VALUES(5);

-- 測驗SET
CREATE TABLE test_set(
    a SET('A','B','C','D','E','F')
);

INSERT test_set(a) VALUES('A');
INSERT test_set(a) VALUES('C');
INSERT test_set(a) VALUES('C,D,E');
INSERT test_set(a) VALUES('C,F,A');
INSERT test_set(a) VALUES('C,F,A,H');
INSERT test_set(a) VALUES(2);

資料型別測驗-測驗時間型別

-- HH:MM:SS [D HH:MM:SS] D表示天數 0~34
-- 測驗TIME型別
CREATE TABLE test_time(
a TIME
);
INSERT test_time(a) VALUES('12:23:45');
INSERT test_time(a) VALUES('2 12:23:45');
INSERT test_time(a) VALUES('22:22');
INSERT test_time(a) VALUES('22');
INSERT test_time(a) VALUES('2 22');
-- HHMMSS
INSERT test_time(a) VALUES('121212');
INSERT test_time(a) VALUES('0');
INSERT test_time(a) VALUES(0);
INSERT test_time(a) VALUES('787878');

INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME);

-- 測驗DATE型別 YYYY-MM-DD YYYYMMDD
CREATE TABLE test_date(
    a DATE
);
INSERT test_date(a) VALUES('2017-03-04');
INSERT test_date(a) VALUES('2017-2-13');
INSERT test_date(a) VALUES('4007-03-23');
INSERT test_date(a) VALUES('40071212');
INSERT test_date(a) VALUES('4007@12@12');
INSERT test_date(a) VALUES('4008#13#13');
INSERT test_date(a) VALUES('4009.8.14');


-- YY-MM-DD YYMMDD
-- 70~99 轉換成1970~1999 00~69 2000~2069
INSERT test_date(a) VALUES('780902');
INSERT test_date(a) VALUES('650902');
INSERT test_date(a) VALUES('880902');
INSERT test_date(a) VALUES(NOW());
INSERT test_date(a) VALUES(CURRENT_DATE);

-- 測驗DATETIME

CREATE TABLE test_datetime(
a DATETIME
);
INSERT test_datetime(a) VALUES('1004-09-12 13:24:56');
INSERT test_datetime(a) VALUES('720305121212');
INSERT test_datetime(a) VALUES(NOW());
INSERT test_datetime(a) VALUES(DATETIME);

-- 測驗TIMESTAMP
CREATE TABLE test_timestamp(
    a TIMESTAMP
);
INSERT test_timestamp(a) VALUES('1978-10-23 12:12:12');

-- 插入CURRENT_TIMESTAMP
INSERT test_timestamp VALUES(CURRENT_TIMESTAMP);

-- 插入NULL
INSERT test_timestamp VALUES(NULL);

-- 什么也不寫 得到當前系統日期和時間
INSERT test_timestamp VALUES();


-- 測驗YEAR

CREATE TABLE test_year(
    a YEAR
);

INSERT test_year(a) VALUES(1901);

-- 00~69 2000~2069 70~99 1970~1999
-- 0 插入的結果是0000
-- '0' 插入的結果是2000

資料型別測驗-測驗主鍵+復合主鍵+自增

-- 測驗主鍵
CREATE TABLE test_primary_key(
    id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(20)
);
INSERT test_primary_key(id,username) VALUES(1,'king');
INSERT test_primary_key(id,username) VALUES(1,'king123');
INSERT test_primary_key(username) VALUES('QUEEN');

CREATE TABLE test_primary_key1(
    id INT UNSIGNED KEY,
    username VARCHAR(20)
);

CREATE TABLE test_primary_key2(
    id INT UNSIGNED,
    username VARCHAR(20),
    PRIMARY KEY(id)
);

-- CREATE TABLE test_primary_key3(
--     id INT UNSIGNED PRIMARY KEY,
--     courseId INT UNSIGNED PRIMARY KEY,
--     username VARCHAR(20),
--     email VARCHAR(50)
-- );

-- 復合主鍵
CREATE TABLE test_primary_key3(
    id INT UNSIGNED,
    courseId VARCHAR(20),
    username VARCHAR(20),
    email VARCHAR(50),
    PRIMARY KEY(id,courseId)
);
-- 1-a
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'a','king','[email protected]');

INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'b','king','[email protected]');

INSERT test_primary_key3(id,courseId,username,email)
VALUES(2,'a','king','[email protected]');

INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,'a','1king','[email protected]');

-- 測驗AUTO_INCREMENT

CREATE TABLE test_auto_increment(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20)
);

INSERT test_auto_increment(username) VALUES('A');
INSERT test_auto_increment(username) VALUES('B');
INSERT test_auto_increment(username) VALUES('C');

INSERT test_auto_increment(id,username) VALUES(NULL,'E');
INSERT test_auto_increment(id,username) VALUES(DEFAULT,'F');
INSERT test_auto_increment(id,username) VALUES(15,'G');

資料型別測驗-測驗非空+默認值+唯一鍵

-- 測驗非空
CREATE TABLE test_not_null(
a varchar(20),
b varchar(20) not null
);

INSERT test_not_null(a,b) VALUES('','');
INSERT test_not_null(a,b) VALUES(NULL,NULL);
INSERT test_not_null(a,b) VALUES(NULL,'abc');
INSERT test_not_null(a) VALUES('TEST');

-- 測驗默認值
CREATE TABLE test_default(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
email VARCHAR(50) NOT NULL DEFAULT '[email protected]'
);
INSERT test_default(username) VALUES('A');
INSERT test_default(username,age,email) VALUES('B',30,'[email protected]');
INSERT test_default(username,age,email) VALUES('C',NULL,'[email protected]');
INSERT test_default(username,age,email) VALUES('D',NULL,NULL);
INSERT test_default(username,age,email) VALUES('D',NULL,DEFAULT);

CREATE TABLE test_default1(
id INT UNSIGNED AUTO_INCREMENT KEY,
sex ENUM('a','b','c') NOT NULL DEFAULT 'a'
);

-- 測驗UNIQUE KEY
CREATE TABLE test_unique(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
email VARCHAR(50) UNIQUE,
card CHAR(18) UNIQUE
);

INSERT test_unique(username,email,card) VALUES('A','[email protected]','1');
INSERT test_unique(username,email,card) VALUES('A','[email protected]','12');
INSERT test_unique(username,email,card) VALUES('B',NULL,NULL);
INSERT test_unique(username,email,card) VALUES('C',NULL,NULL);

資料型別測驗-測驗布爾型別

-- 用戶表test_user
CREATE TABLE IF NOT EXISTS `test_user`(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用戶編號',
`username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用戶名',
`password` CHAR(32) NOT NULL COMMENT '密碼',
`email` VARCHAR(50) NOT NULL UNIQUE COMMENT '郵箱',
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年齡',
`sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性別',
`tel` CHAR(11) NOT NULL UNIQUE COMMENT '電話',
`addr` VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
`card` CHAR(18) NOT NULL UNIQUE COMMENT '身份證號',
`married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未結婚，1代表已結婚',
`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 測驗布爾型別
CREATE TABLE test_bool(
id int,
married BOOL
);
CREATE TABLE test_bool1(
id int,
married BOOLEAN
);

對資料表欄位的增刪改

-- 測驗添加和洗掉欄位
CREATE TABLE IF NOT EXISTS user1(
    id INT UNSIGNED AUTO_INCREMENT KEY
);

-- 添加用戶名欄位 username VARCHAR(20)
ALTER TABLE user1
ADD username VARCHAR(20);

-- 添加密碼欄位 password CHAR(32) NOT NULL
ALTER TABLE user1
ADD password CHAR(32) NOT NULL;

-- 添加郵箱欄位 email VARCHAR(50) NOT NULL UNIQUE 加到username之后
ALTER TABLE user1
ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;

-- 添加測驗欄位 test TINYINT(1) NOT NULL DEFAULT 0; 加到首位
ALTER TABLE user1
ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;

-- 洗掉test欄位
ALTER TABLE user1
DROP test;


-- 添加age、addr欄位，洗掉email欄位
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP email;

-- 創建資料表 id / proName
-- 添加price FLOAT(8,2) UNSIGNED NOT NULL DEFAULT 0
-- 添加num INT UNSIGNED NOT NULL DEFAULT 100
-- 添加測驗欄位test VARCHAR(50) NOT NULL 加到首位
-- 添加測驗欄位test1 CHAR(23) NOT NULL 添加到price之后
-- 洗掉test欄位

-- 選中一次資料表完成多次操作，添加一個desc TEXT, 添加isSale 布爾型別 0下架 1代表在賣
-- 洗掉test1欄位

-- 測驗添加洗掉默認值操作
CREATE TABLE user2(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 18,
email VARCHAR(50) NOT NULL
);
-- 給email欄位添加默認值 [email protected]
ALTER TABLE user2
ALTER email SET DEFAULT '[email protected]';

-- 洗掉age欄位的默認值
ALTER TABLE user2
ALTER age DROP DEFAULT;

-- 添加地址欄位
-- 給地址欄位添加默認值為北京
-- 洗掉商品數量的默認值


-- 測驗修改欄位型別和欄位屬性、欄位名稱
CREATE TABLE user3(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(5) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(10) NOT NULL
);

-- 將用戶名欄位的型別改為20
ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL;

-- 將密碼的長度改為40
ALTER TABLE user3
MODIFY password CHAR(40) NOT NULL;

-- 將email欄位改為VARCHAR(50) NOT NULL FIRST

ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;

-- 將username 名稱改為user
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL;

-- 將password 名稱改為pwd
ALTER TABLE user3
CHANGE password pwd CHAR(40) NOT NULL;

-- 將email改成userEmail 型別改成VARCHAR(100) DEFAULT '[email protected]'

ALTER TABLE user3
CHANGE email userEmail VARCHAR(100) DEFAULT '[email protected]';


-- 測驗添加和洗掉主鍵
CREATE TABLE user4(
id INT UNSIGNED,
username VARCHAR(20) NOT NULL
);

-- 添加主鍵
ALTER TABLE user4
ADD PRIMARY KEY(id);

-- 洗掉主鍵
ALTER TABLE user4
DROP PRIMARY KEY;

CREATE TABLE user5(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL
);

-- 將id的AUTO_INCREMENT去掉
ALTER TABLE user5
MODIFY id INT UNSIGNED;

-- 測驗添加和洗掉唯一
CREATE TABLE user6(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE
);

-- 洗掉唯一索引 username 和email
ALTER TABLE user6
DROP INDEX username;


ALTER TABLE user6
DROP INDEX email;

-- 添加唯一索引
ALTER TABLE user6
ADD UNIQUE KEY(username);

ALTER TABLE user6
ADD UNIQUE INDEX uni_email(email);

-- 將user6改為user666
ALTER TABLE user6
RENAME TO user666;


ALTER TABLE user666
RENAME AS user6;

ALTER TABLE user6
RENAME user666;

RENAME TABLE user666 TO user6;

存盤引擎

-- 測驗MEMORY存盤引擎
CREATE TABLE m1(
a VARCHAR(100)
)ENGINE=MEMORY;

-- 測驗CSV存盤引擎
CREATE TABLE c1(
a VARCHAR(20) NOT NULL,
b VARCHAR(20) NOT NULL
)ENGINE=CSV;
INSERT c1(a,b) VALUES('AAA','BBB');
INSERT c1(a,b) VALUES('CCC','DDD');

-- 測驗ARCHIVE存盤引擎
CREATE TABLE a1 AS SELECT * FROM INFORMATION_SCHEMA.columns;

INSERT INTO a1 SELECT * FROM a1;

-- 11026432 57177

CREATE TABLE a2 ENGINE=ARCHIVE AS SELECT * FROM a1;

-- 742354 55392

-- 測驗MyISAM存盤引擎
CREATE TABLE test_myisam(
    a INT UNSIGNED,
    b VARCHAR(20),
    c CHAR(32)
)ENGINE=MyISAM;


CREATE TABLE myisam_1(
a char(30),
id int
)ENGINE=MyISAM;

CREATE TABLE myisam_2(
a VARCHAR(30),
id int
)ENGINE=MyISAM;

CREATE TABLE myisam_3(
a VARCHAR(30),
id int
)ENGINE=MyISAM ROW_FORMAT=FIXED;


CREATE TABLE test_innodb(
 id INT UNSIGNED AUTO_INCREMENT KEY,
 username VARCHAR(20) NOT NULL     
);

添加資料

-- 測驗添加記錄
CREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET 'UTF8';
USE king;
CREATE TABLE IF NOT EXISTS user(
    id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '編號',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用戶名',
    age TINYINT UNSIGNED DEFAULT 18 COMMENT '年齡',
    email VARCHAR(50) NOT NULL DEFAULT '[email protected]' COMMENT '郵箱'
)ENGINE=INNODB CHARSET=UTF8;

-- 不指定欄位名稱
INSERT user VALUE(1,'king',24,'[email protected]');
INSERT user VALUES(NULL,'queen',25,'[email protected]');
INSERT user VALUES(DEFAULT,'lily',26,'[email protected]');

-- 列出指定欄位的形式
INSERT user(username,email) VALUES('rose','[email protected]');
INSERT user(age,email,id,username) VALUES(34,'[email protected]',5,'test');

-- 一次插入3條記錄
INSERT user VALUES(NULL,'a',DEFAULT,DEFAULT),
(NULL,'b',56,'[email protected]'),
(NULL,'c',14,'[email protected]');

-- INSERT ...SET 的形式
INSERT user SET username='d',age=45,email='[email protected]';

-- INSERT SELECT
INSERT user(username) SELECT a FROM test;

修改洗掉資料

-- 測驗更新陳述句
-- 修改第一個用戶的資訊 id=1
UPDATE user SET age=29 WHERE id=1;

-- 修改id=3的用戶，username age email
UPDATE user SET age=47,email='[email protected]',username='lilys' WHERE id=3;

-- 所有用戶年齡+10
UPDATE user SET age=age+10;

-- 將id<=5的用戶年齡改為-20，將郵箱改為默認值
UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;

-- 測驗洗掉陳述句
-- 洗掉用戶名為king
DELETE FROM user WHERE username='king';

-- 洗掉年齡為24的用戶
DELETE FROM user WHERE age=24;

-- 洗掉表中所有記錄
DELETE FROM user;

INSERT user VALUES(NULL,'queen',25,'[email protected]');
INSERT user VALUES(DEFAULT,'lily',26,'[email protected]');

查詢操作

-- 測驗查詢操作
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '編號',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年齡',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性別',
addr VARCHAR(20) NOT NULL DEFAULT '寧波',
married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未結婚，1代表已婚',
salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
INSERT user1 VALUES(1,'king',23,'男','寧波',1,50000);
INSERT user1(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);
INSERT user1 SET username='test',age=31,sex='女',addr='寧波',salary=40000;
INSERT user1 VALUES(NULL,'張三',38,'男','上海',0,15000),
(NULL,'張三風',38,'男','上海',0,15000),
(NULL,'張子怡',39,'女','寧波',1,85000),
(NULL,'汪峰',42,'男','深圳',1,95000),
(NULL,'劉德華',58,'男','廣州',0,115000),
(NULL,'吳亦凡',28,'男','寧波',0,75000),
(NULL,'奶茶妹',18,'女','寧波',1,65000),
(NULL,'劉嘉玲',36,'女','廣州',0,15000);

-- 查詢表中所有記錄
SELECT * FROM user1;

-- username,addr,age
SELECT username,addr,age FROM user1;

-- 查詢king資料庫下user1表中的所有記錄
SELECT * FROM king.user1;

-- 查詢user1表中的id 編號 username 用戶名 sex 性別
SELECT id AS '編號',username AS '用戶名', sex AS '性別'
FROM user1;

SELECT id age,age id,username FROM user1;

-- 給表起別名
SELECT id,username FROM user1 AS u;

-- 測驗表名.欄位名
SELECT user1.id,user1.username,user1.age FROM user1 ;

SELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;

-- 測驗WHERE 條件的比較運算子
-- 查詢id,username,age id=5的用戶
SELECT id,username,age FROM user1
WHERE id=5;

SELECT id,username,age FROM user1
WHERE id=50;

-- 添加desc欄位 VARCHAR(100)
ALTER TABLE user1
ADD userDesc VARCHAR(100);

-- 更新id<=9的用戶 userDesc='this is a test'

UPDATE user1 SET userDesc='this is a test'
WHERE id<=9;

-- 查詢用戶userDesc 為NULL的用戶
SELECT id,username,age,userDesc FROM user1
WHERE userDesc=NULL;

-- 檢測NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc<=>NULL;

-- IS [NOT] NULL檢測NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NULL;

-- 測驗范圍BETWEEN AND
-- 查詢年齡在18~30之間的用戶
SELECT id,username,age,sex FROM user1
WHERE age BETWEEN 18 AND 30;

-- 查詢薪水在10000~50000之間的用戶
SELECT id,username,age,salary FROM user1
WHERE salary BETWEEN 10000 AND 50000;

SELECT id,username,age,salary FROM user1
WHERE salary NOT BETWEEN 10000 AND 50000;

-- 測驗指定集合 IN

-- 查詢編號為1,3,5,7,9
SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9,29,45,78);

SELECT id,username,age FROM user1
WHERE username IN('king','queen','lily','rose');

-- 測驗邏輯運算子
-- 查詢性別為男并且年齡>=20的用戶
SELECT id,username,age,sex FROM user1
WHERE sex='男' AND age>=20;

-- id>=5 && age<=30
SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30;

SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30 AND sex='男';

-- 要求sex='女' 并且 addr='寧波'
SELECT id,username,age,sex,addr FROM user1
WHERE sex='女' AND addr='寧波';

-- 查詢薪水范圍在60000~10000并且性別為男 addr='寧波'
SELECT id,username,age,sex,salary,addr FROM user1
WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='寧波';

-- 查詢id=1 或者 用戶名為queen

SELECT id,username,age FROM user1
WHERE id=1 OR username='queen';

-- 測驗模糊查詢
SELECT id,username,age FROM user1
WHERE username='king';

SELECT id,username,age FROM user1
WHERE username LIKE 'king';

-- 要求用戶名中包含三
SELECT id,username,age,sex FROM user1
WHERE username LIKE '%三%';

-- 用戶名中包含n
SELECT id,username,age FROM user1
WHERE username LIKE '%in%';

-- 要求查詢出姓張的用戶
SELECT id,username,age FROM user1
WHERE username LIKE '張%';

-- 查詢以風結尾的用戶
SELECT id,username,age FROM user1
WHERE username LIKE '%風';


-- 用戶名長度為三位的用戶
SELECT id,username,age,sex FROM user1
WHERE username LIKE '___';

SELECT id,username,age,sex FROM user1
WHERE username LIKE '張_';

SELECT id,username,age,sex FROM user1
WHERE username LIKE '張_%';

-- 測驗分組
-- 按照性別分組sex
SELECT id,username,age,sex FROM user1
GROUP BY sex;

-- 按照addr分組
SELECT username,age,sex,addr FROM user1
GROUP BY addr;

-- 按照性別分組，查詢組中的用戶名有哪些
SELECT GROUP_CONCAT(username),age,sex,addr FROM user1
GROUP BY sex;

SELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1
GROUP BY sex;

-- 測驗COUNT()
SELECT COUNT(*) FROM user1;

SELECT COUNT(id) FROM user1;

-- 按照sex分組，得到用戶名詳情，并且分別組中的總人數
SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1
GROUP BY sex;

-- 按照addr分組，得到用戶名的詳情，總人數，得到組中年齡的總和，年齡的最大值、最小值、平均值和
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
GROUP BY addr;

-- 按照sex分組，統計組中總人數、用戶名詳情，得到薪水總和，薪水最大值、最小值、平均值
SELECT sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY sex;

SELECT GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
WITH ROLLUP;

-- 按照欄位的位置來分組
SELECT id,sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY 2;

-- 查詢age>=30的用戶并且按照sex分組
SELECT sex,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
WHERE age>=30
GROUP BY sex;

-- 按照addr分組，統計總人數
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr;

-- 對于分組結果進行二次篩選，條件是組中總人數>=3
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING COUNT(*)>=3;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING totalUsers>=3;

-- 按照addr分組，
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr;

-- 要求平均薪水>=40000
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr
HAVING avg_salary>=40000;

-- 測驗排序
-- 按照id降序排列
SELECT id,username,age
FROM user1
ORDER BY id DESC;

-- 按照age升序
SELECT id,username,age
FROM user1
ORDER BY age ;

-- 按照多個欄位排序
SELECT id,username,age
FROM user1
ORDER BY age ASC,id ASC;

-- 測驗條件+排序
SELECT id,username,age
FROM user1
WHERE age>=30;

SELECT id,username,age
FROM user1
WHERE age>=30
ORDER BY age DESC;

-- 實作隨機記錄
SELECT id,username,age
FROM user1
ORDER BY RAND();

-- 測驗LIMIT陳述句
-- 顯示結果集的前5條記錄
SELECT id,username,age,sex
FROM user1
LIMIT 5;

SELECT id,username,age,sex
FROM user1
LIMIT 0,5;

-- 顯示前3條記錄
SELECT id,username,age,sex
FROM user1
LIMIT 0,3;

SELECT id,username,age,sex
FROM user1
LIMIT 3,3;

-- 更新前3條記錄，將age+5
UPDATE user1 SET age=age+5 LIMIT 3;

-- 按照id降序排列，更新前三條記錄，將age-10
UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;

-- 洗掉前三條記錄

DELETE FROM user1
LIMIT 3;

DELETE FROM user1
ORDER BY id DESC
LIMIT 3;

-- 測驗完整SELECT 陳述句的形式
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2;


SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC
LIMIT 0,2;

多表查詢

CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8';
USE test2;
CREATE TABLE emp(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '編號',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年齡',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性別',
addr VARCHAR(20) NOT NULL DEFAULT '寧波',
depId TINYINT UNSIGNED NOT NULL COMMENT '部門對應的編號'
)ENGINE=INNODB CHARSET=UTF8;

INSERT emp(username,age,depId) VALUES('king',24,1),
('queen',25,2),
('test',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);

INSERT emp(username,age,depId)  VALUES('測驗用戶',39,6);

CREATE TABLE dep(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(50) NOT NULL UNIQUE,
depDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB CHARSET=UTF8;
INSERT dep(depName,depDesc) VALUES('PHP教學部','研發PHP課件'),
('JAVA教學部','研發JAVA課件'),
('WEB前端教學部','研發WEB前端課件'),
('IOS教學部','研發IOS課件');


-- 查詢 emp id username age  部門名稱 dep depName

SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;

SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;

-- 查詢emp id username age addr dep id depName depDesc
SELECT e.id,e.username,e.age,e.addr,
d.id,d.depName,d.depDesc
FROM dep AS d
JOIN emp AS e
ON d.id=e.depId;

-- 測驗左外連接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;

-- 測驗右外連接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;

實體

-- 創建管理員表
CREATE TABLE user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '[email protected]',
proName VARCHAR(10) NOT NULL DEFAULT '北京'
);
INSERT user(username,proName) VALUES('a','北京'),
('b','哈爾濱'),
('c','上海'),
('d','深圳'),
('e','廣州'),
('f','重啟');

-- 創建省份表
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳');
--
CREATE TABLE user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '[email protected]',
proId TINYINT UNSIGNED NOT NULL
);
INSERT user(username,proId) VALUES('a',1);
INSERT user(username,proId) VALUES('b',1);
INSERT user(username,proId) VALUES('c',1);
INSERT user(username,proId) VALUES('d',2);
INSERT user(username,proId) VALUES('e',3);
INSERT user(username,proId) VALUES('f',1);
INSERT user(username,proId) VALUES('g',1);

-- 查詢user id ,username provinces proName
SELECT u.id,u.username,p.proName
FROM user AS u
JOIN provinces AS p
ON u.proId=p.id;

-- 創建省份表
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);

-- 管理員admin id username email proId
CREATE TABLE admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '[email protected]',
proId TINYINT UNSIGNED NOT NULL
);
INSERT admin(username,proId) VALUES('king',1);
INSERT admin(username,proId) VALUES('queen',2);
-- 商品分類cate id cateName cateDesc
CREATE TABLE cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);
INSERT cate(cateName) VALUES('母嬰');
INSERT cate(cateName) VALUES('服裝');
INSERT cate(cateName) VALUES('電子');

-- 商品表products id productName, price,cateId
CREATE TABLE products(
id INT UNSIGNED AUTO_INCREMENT KEY,
productName VARCHAR(50) NOT NULL UNIQUE,
price FLOAT(8,2) NOT NULL DEFAULT 0,
cateId TINYINT UNSIGNED NOT NULL,
adminId TINYINT UNSIGNED NOT NULL
);
INSERT products(productName,price,cateId,adminId)
VALUES('iphone9',9888,3,1),
('adidas',388,2,2),
('nike',888,2,2),
('奶瓶',288,1,1);

-- 查詢products id productName price --- cate cateName
SELECT p.id,p.productName,p.price,c.cateName
FROM products AS p
JOIN cate AS c
ON p.cateId=c.id;

-- 查詢管理員 id username email -- provinces proName

SELECT a.id,a.username,a.email,p.proName
FROM admin AS a
JOIN provinces AS p
ON a.proId=p.id;
-- 查詢 products id productName price
-- cate cateName
-- admin username email

SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
JOIN admin AS a
ON p.adminId=a.id
JOIN cate AS c
ON p.cateId=c.id
WHERE p.price<1000
ORDER BY p.price DESC
LIMIT 0,2;


-- products id productName price
-- cate cateName
-- admin username email
-- provinces proName

測驗外鍵

-- 測驗外鍵
-- 新聞分類表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);

-- 新聞表 news
CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL
);

INSERT news_cate(cateName) VALUES('國內新聞'),
('國際新聞'),
('娛樂新聞'),
('體育新聞');

INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);

-- 查詢news id title content
-- news_cate cateName
SELECT n.id,n.title,n.content,c.cateName
FROM news AS n
JOIN news_cate AS c
ON n.cateId=c.id;

INSERT news(title,content,cateId) VALUES('a6','aaaa6',45);

-- 添加外鍵

-- 新聞分類表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB;

-- 新聞表 news
CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL,
FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;

INSERT news_cate(cateName) VALUES('國內新聞'),
('國際新聞'),
('娛樂新聞'),
('體育新聞');

INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);

-- 測驗非法記錄
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);

-- 測驗洗掉父表中的記錄 和洗掉父表
DELETE FROM news_cate WHERE id=1;

UPDATE news_cate SET id=10 WHERE id=1;

INSERT news_cate(cateName) VALUES('教育新聞');

-- 將教育新聞 改成教育
UPDATE news_cate SET cateName='教育' WHERE id=5;
UPDATE news_cate SET id=50 WHERE cateName='教育';

-- 添加外鍵名稱

-- 新聞分類表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB;

-- 新聞表 news
CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL,
CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;

-- 洗掉外鍵
ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;

-- 添加外鍵
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);

-- 洗掉外鍵
ALTER TABLE news
DROP FOREIGN KEY news_ibfk_1;

ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

INSERT news_cate(cateName) VALUES('國內新聞'),
('國際新聞'),
('娛樂新聞'),
('體育新聞');

INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',8);

ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);

-- 指定級聯操作 DELETE CASCADE UPDATE CASCADE
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;

測驗子查詢

-- 測驗子查詢
-- 測驗由IN引發的子查詢

SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);

SELECT * FROM emp
WHERE depId NOT IN (SELECT id FROM dep);

-- 學員stu
CREATE TABLE stu(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED NOT NULL
);
INSERT stu(username,score) VALUES('king',95),
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',98),
('ceshi',99),
('tiancai',50);


-- 分數級別level
CREATE TABLE level(
id tinyint UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED COMMENT '分數'
);
INSERT level(score) VALUES(90),(80),(70);

-- 查詢出成績優秀的學員

SELECT score FROM level WHERE id=1;

SELECT id,username,score FROM stu
WHERE score>=(SELECT score FROM level WHERE id=1);

-- 查詢出沒有得到評級的學員

SELECT id,username,score FROM stu
WHERE score<=(SELECT score FROM level WHERE id=3);

-- 由EXISTS 引發的子查詢

SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);
SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=1);


-- 帶有ANY SOME ALL關鍵字的子查詢
SELECT * FROM stu
WHERE score>= ANY(SELECT score FROM level);

SELECT * FROM stu
WHERE score>= SOME(SELECT score FROM level);

SELECT * FROM stu
WHERE score>= ALL(SELECT score FROM level);

SELECT * FROM stu
WHERE score< ALL(SELECT score FROM level);

SELECT * FROM stu
WHERE score=ANY(SELECT score FROM level);

SELECT * FROM stu
WHERE score!=ALL(SELECT score FROM level);

-- 創建一個user1表,id username
CREATE TABLE user1(
    id int UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
)SELECT id,username FROM emp;

-- 將user表中的用戶名寫入到user1表中
INSERT user1(username) SELECT username FROM user;

-- 將stu表中的tiancai用戶名添加到user2表中
INSERT user2 SET username=(SELECT username FROM stu WHERE id=9);

-- 去掉欄位的重復值
SELECT DISTINCT(username) FROM user2;

-- 將user1和user2資料合并到一起
SELECT * FROM user1
UNION
SELECT * FROM user2;

測驗自身連接

-- 測驗自身連接
CREATE TABLE cate(
    id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    cateName VARCHAR(100) NOT NULL UNIQUE,
    pId SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT cate(cateName,pId) VALUES('服裝',0);
INSERT cate(cateName,pId) VALUES('數碼',0);
INSERT cate(cateName,pId) VALUES('箱包',0);

INSERT cate(cateName,pId) VALUES('男裝',1);
INSERT cate(cateName,pId) VALUES('女裝',1);
INSERT cate(cateName,pId) VALUES('內衣',1);

INSERT cate(cateName,pId) VALUES('電視',2);
INSERT cate(cateName,pId) VALUES('冰箱',2);
INSERT cate(cateName,pId) VALUES('洗衣機',2);

INSERT cate(cateName,pId) VALUES('愛馬仕',3);
INSERT cate(cateName,pId) VALUES('LV',3);
INSERT cate(cateName,pId) VALUES('GUCCI',3);

INSERT cate(cateName,pId) VALUES('夾克',4);
INSERT cate(cateName,pId) VALUES('襯衫',4);
INSERT cate(cateName,pId) VALUES('褲子',4);

INSERT cate(cateName,pId) VALUES('液晶電視',10);
INSERT cate(cateName,pId) VALUES('等離子電視',10);
INSERT cate(cateName,pId) VALUES('背投電視',10);

-- 查詢所有的分類資訊，并且得到其父分類
SELECT s.id,s.cateName AS sCateName,p.cateName  AS pCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId=p.id;

-- 查詢所有的分類及其子分類
SELECT p.id,p.cateName  AS pCateName,s.cateName AS sCateName
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId=p.id;

-- 查詢所有的分類并且得到子分類的數目
SELECT p.id,p.cateName  AS pCateName,COUNT(s.cateName) AS count
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId=p.id
GROUP BY p.cateName
ORDER BY id ASC;


sCate 表
id | cateName        | pId |
+----+-----------------+-----+
|  1 | 服裝            |   0 |
|  2 | 數碼            |   0 |
|  3 | 玩具            |   0 |
|  4 | 男裝            |   1 |
|  5 | 女裝            |   1 |
|  6 | 內衣            |   1 |
| 10 | 電視            |   2 |
| 11 | 冰箱            |   2 |
| 12 | 洗衣機          |   2 |
| 13 | 愛馬仕          |   3 |
| 14 | LV              |   3 |
| 15 | GUCCI           |   3 |
| 16 | 夾克            |   4 |
| 17 | 襯衫            |   4 |
| 18 | 褲子            |   4 |
| 19 | 液晶電視        |  10 |
| 20 | 等離子電視      |  10 |
| 21 | 背投電視        |  10

pCate
id | cateName        | pId |
+----+-----------------+-----+
|  1 | 服裝            |   0 |
|  2 | 數碼            |   0 |
|  3 | 玩具            |   0 |
|  4 | 男裝            |   1 |
|  5 | 女裝            |   1 |
|  6 | 內衣            |   1 |
| 10 | 電視            |   2 |
| 11 | 冰箱            |   2 |
| 12 | 洗衣機          |   2 |
| 13 | 愛馬仕          |   3 |
| 14 | LV              |   3 |
| 15 | GUCCI           |   3 |
| 16 | 夾克            |   4 |
| 17 | 襯衫            |   4 |
| 18 | 褲子            |   4 |
| 19 | 液晶電視        |  10 |
| 20 | 等離子電視      |  10 |
| 21 | 背投電視        |  10

測驗字串函式

-- 測驗字串函式
-- CHAR_LENGTH():得到字串的字符數
SELECT CHAR_LENGTH('abc');

-- LENGTH():回傳字串的長度
SELECT LENGTH('abc');

-- CONCAT(s1,s2,....):將字串合并成一個字串

SELECT CONCAT('a','b','c');
-- 如果字串中包含NULL，回傳拼接結果就是NULL
SELECT CONCAT('a','b','c',null);

-- CONCAT_WS(x,s1,s2,s2....)：以指定分隔符拼接字串
SELECT CONCAT_WS('-','a','b','c');
-- 如果null在拼接的內容中，則轉化成空字串
SELECT CONCAT_WS('-','a','b','c',null);
-- 如果分隔符為null，拼接的結果為null
SELECT CONCAT_WS(null,'a','b','c');
-- 將字串轉換成大寫或者小寫 UPPER()| UCASE() LOWER()|LCASE()
SELECT UPPER('hello king'),UCASE('hello imooc'),LOWER('HELLO ADMIN'),LCASE('HELLO EVERYBODY');

-- 字串的反轉REVERSE()
SELECT REVERSE('abc');

-- LEFT()|RIGHT():回傳字串的前幾個字符或者后幾個字符
SELECT LEFT('hello',2),RIGHT('hello',2);

-- LPAD()|RPAD():用字串填充到指定長度
SELECT LPAD('abc',10,'?');
SELECT RPAD('abc',10,'!');

-- 去掉字串兩端的空格TRIM()|LTRIM()|RTRIM():

SELECT CONCAT('*',TRIM(' abc '),'*'),CONCAT('*',LTRIM(' abc '),'*'),CONCAT('*',RTRIM(' abc '),'*');

-- REPEAT():重復指定的次數
SELECT REPEAT('hello',3);

-- REPLACE():字串

SELECT REPLACE('hello king','king','queen');

-- 截取字串SUBSTRING
SELECT SUBSTRING('abcdef',1,3);

-- 比較字串

SELECT STRCMP('a','b');

測驗日期時間函式

-- 測驗日期時間函式
-- 回傳當前日期
SELECT CURDATE(),CURRENT_DATE();
-- 回傳當前時間
SELECT CURTIME(),CURRENT_TIME();
-- 回傳當前的日期時間
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();
-- 回傳日期中的月份和月份的名稱
SELECT MONTH('2017-02-19');
SELECT MONTH(CURDATE()),MONTHNAME(CURDATE());
-- 回傳星期幾
SELECT DAYNAME(NOW());
-- 回傳一周內的第幾天,0代表星期一
SELECT DAYOFWEEK(NOW());
SELECT WEEK(NOW());
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());

-- DATEDIFF()計算兩個日期相差的天數
SELECT DATEDIFF('2019-03-06','2019-03-02');

其他函式測驗

-- 測驗其它常用函式
SELECT VERSION(),CONNECTION_ID();

SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();

-- 得到上一步插入操作產生AUTO_INCREMENT的值
SELECT LAST_INSERT_ID();

SELECT MD5('king');
-- PASSWORD():密碼加密演算法
SELECT PASSWORD('root');

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

標籤：MySQL

上一篇：MySQL資料庫(三)簡介

下一篇：mysql常用函式