DDL 數(shù)據(jù)庫定義語言

查看當前數(shù)據(jù)庫

SELECT DATABASE();

退出數(shù)據(jù)庫

QUIT[;]

顯示數(shù)據(jù)庫列表

SHOW DATABASES;

SHOW SCHEMAS;

顯示數(shù)據(jù)庫版本

SELECT VERSION();

當前登錄數(shù)據(jù)庫的用戶

SELECT USER();

顯示當前時間

SELECT NOW();

數(shù)據(jù)庫幫助

HELP CREATE DATABASE

? CREATE

\H CREATE

取消執(zhí)行SQL

SELECT USER()\c

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE test1;

CREATE SCHEMA test1;

轉(zhuǎn)義反引號關(guān)鍵字

CREATE DATABASE `database`;

創(chuàng)建檢查數(shù)據(jù)庫是否存在

CREATE DATABASE IF NOT EXISTS test1;

查看警告

SHOW WARNINGS;

創(chuàng)建數(shù)據(jù)庫指定編碼

CREATE DATABASE test IF NOT EXISTS DEFAULTER CHARACTER SET = 'UTF8';

查看創(chuàng)建的編碼方式

SHOW CREATE DATABASE test;

查看表

SHOW TABLES;

SHOW FULL TABLES FROM mysql;

查看鍵表信息

SHOW CREATE TABLE imooc_user;

查看表結(jié)構(gòu)

DESC test;

DESCRIBE test;

SHOW COLUMNS FROM test;

打開數(shù)據(jù)庫

USE test;

ALTER

改變編碼方式

ALTER DATABASE test DEFAULTER CHARACTER SET = 'UTF8';

添加字段

ALTER TABLE user1 ADD username VARCHAR(20);

添加字段在某個字段的后面

ALTER TABLE user?

ADD email VARCHAR(50) NOT NULL UNIQUE KEY AFTER username;

添加字段在首位

ALTER TABLE user1

ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;

刪除字段

ALTER TABLE user1

?DROP test;

添加刪除組合使用

ALTER TABLE user1

?ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,

?ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',

?DROP email;

存在基礎(chǔ)上修改字段添加默認值SET

ALTER TABLE user2?

?ALTER email SET DEFAULT 'imooc@qq.com';

存在基礎(chǔ)上修改字段刪除默認值

ALTER TABLE user2

?ALTER age DROP DEFAULT;

修改字段名稱

?ALTER TABLE user3

?CHANGE username user VARCHAR(20) NOT NULL;

修改字段的類型 NOT NULL要加上

?ALTER TABLE user3

?MODIFY username VARCHAR(20) NOT NULL ;

修改字段的類型 并放在首位

?ALTER? TABLE user3

?MODIFY email VARCHAR(50)? NOT NULL FIRST;

去掉AUTO_INCREMENT

ALTER TABLE user5

?MODIFY id INT UNSIGNED;

添加唯一性索引

?ALTER TABLE user6

?ADD UNIQUE KEY(username);

刪除唯一性索引

ALTER TABLE user6

?DROP INDEX username;

添加唯一性索引并指定索引名稱

ALTER TABLE user6 ADD UNIQUE INDEX uni_email(email);

添加主鍵

ALTER TABLE user4

?ADD PRIMARY KEY(id);

修改表名稱

ALTER TABLE user6?

?RENAME TO user666;

ALTER TABLE user666

?RENAME AS user6;

RENAME TABLE user6 TO user666;

刪除外鍵

ALTER TABLE news

?DROP FOREIGN KEY cateId_fk_newsCate;

添加外鍵

?ALTER TABLE news

?ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId)?

?REFERENCES news_cate(id); ALTER TABLE news

?ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId)?

?REFERENCES news_cate(id);

DROP

刪除數(shù)據(jù)庫

DROP DATABASE? IF EXISTS? test;

刪除表

DROP TABLE IF EXISTS imooc_user;

注釋

#

--

插入數(shù)據(jù)

INSERT INTO imooc_user()VALUES(); INTO可以省略

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

VALUES(1,'king','king','382771946@qq.com',24,'230112197809871234',18635579617,888888.68,0,'北京','男');

function

CONCAT()

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

NOW()

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

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

當前日期時間

SELECT NOW()

當前時間

SELECT CURRENT_TIME();

當前時間

SELECT CURTIME();

CURRENT_DATE()

INSERT INTO test_date(a) VALUES(CURRENT_DATE());

當前日期

CURRENT_DATE()

CURRENT_TIMEStamP

INSERT test_timestamp(a) VALUES(CURRENT_TIMEStamP);

當前日期和時間

CURRENT_TIMEStamP

進一取整

SELECT CEIL(1.2);

去掉小數(shù)

SELECT FLOOR(2.9);

四舍五入 取兩位小數(shù)

SELECT ROUND(3.567,2);

取小數(shù)3位部分

SELECT TRUNCATE(3.456789,3);

取余數(shù)

SELECT MOD(5,2);

取絕對值

SELECT ABS(-12);

冪運算

SELECT POWER(2,3);

得到PI

SELECT PI();

隨機數(shù)0-1之間

SELECT RAND();

定給參數(shù)會變成一個標識

比如SELECT RAND(1);

取符號 0為0 -n(取n)>0為-1? ?+n>0為+1

SELECT SIGN(-23);

e的幾次方

SELECT EXP(2);

得到字符長度以占位符來區(qū)分

SELECT CHAR_LENGTH('你好啊');

真正字符串長度

SELECT LENGTH('abc');

合并字符串 如果有null最終結(jié)果是null

SELECT CONCAT('a','b','c',null);

以指定的分隔符拼接字符串，分隔符是null就是null，其他地方是null沒影響

CONCAT_WS('-','A','B','C')

CONCAT_WS('-','A','B','C',null)

CONCAT_WS(null,'A','B','C')

大小寫轉(zhuǎn)換 UPPER('hello king') UCASE('hello imooc') LOWER('HELLO ADMIN') LCASE('HELLO EVERYBODY');

SELECT UPPER('hello king'),UCASE('hello imooc'),LOWER('HELLO ADMIN'),LCASE('HELLO EVVERYBODY');

字符串反轉(zhuǎn)函數(shù)

SELECT REVERSE('abc');

得到前2個或者后2個字符

SELECT LEFT('hello',2),RIGHT('hello',2);

用字符串填充到指定長度

SELECT LPAD('abc',10,'?');

SELECT RPAD('abc',10,'!');

去掉空格

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

字符串替換

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

重復(fù)字符數(shù)

SELECT REPEAT('hello',3);

截取字符串

SELECT SUBSTRING('abcdef',1,3);

比較字符串 相等為0 第一個字符串大于第二個字符串返回是正1? 第一個字符串小于第二個字符串返回是-1

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

返回當前日期

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());

返回一周內(nèi)的第幾天

SELECT DAYOFWEEK(NOW());

返回第幾個星期了

SELECT WEEK(NOW());

返回年份 月份 號 小時 分鐘 秒

SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());

計算兩個日期相差的天數(shù)

SELECT DATEDIFF('2017-03-06','2017-03-02');

其他常用函數(shù)

獲得版本

SELECT VERSION();

當前連接數(shù)

SELECT CONNECTION_ID();

獲得當前的數(shù)據(jù)庫名

SELECT DATABASE(),SCHEMA();

當前登錄的用戶

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

得到上一次auto_increment的值

SELECT LAST_INSERT_ID();

MD5加密

SELECT MD5('king');

密碼加密算法

SELECT PASSWORD('root');

查詢root密碼

SELECT * FROM mysql.user WHERE user='root';

查看表狀態(tài)

SHOW TABLE STATUS LIKE 'myisam_1';

設(shè)置定長格式

CREATE TABLE myisam_2(

?a VARCHAR(30),

?id INT

?)ENGINE=MYISAM ROW_FORMAT=FIXED;

檢查表

CHECK TABLE test_innodb;

修復(fù)表

REPAIR TABLE test_innodb;

插入數(shù)據(jù)省略

INSERT user VALUE(1,'king',24,'382771946@qq.com');

INSERT user VALUES(1,'king',24,'382771946@qq.com');

一次插入多條記錄

INSERT user VALUES(NULL,'A',DEFAULT,DEFAULT),

(NULL,'b',56,'b@qq.com'),

(NULL,'c',14,'c@qq.com');

INSERT SET形式

INSERT user SET username='d',age=45,email='d@qq.com';

UPDATE SET

UPDATE user SET username='socialpg',age=18 ,email='qq@com' WHERE id=3;

重置AUTO_INCREMENT

ALTER TABLE user AUTO_INCREMENT=1;

徹底刪除數(shù)據(jù)表

TRUNCATE user;

查詢指定庫中的表

SELECT * FROM imooc.user1;

字段起別名

SELECT id AS '編號',username AS '用戶名' , sex AS '性別'FROM user1;

數(shù)據(jù)庫起別名

SELECT id,username FROM user1 AS u;

表名.字段名

SELECT user1.id,user1.username FROM user1;

<=>檢測NULL值

SELECT id,username,age,userDesc FROM user1?

WHERE userDesc <=> NULL;

IS NULL

SELECT id,username,age,userDesc FROM user1?

WHERE userDesc IS NULL ;

IS NOT NULL 取反

SELECT id,username,age,userDesc FROM user1?

WHERE userDesc IS NOT NULL ;

BETWEEN AND

SELECT id,username,age,userDesc FROM user1?

WHERE age BETWEEN 18 AND 30;

NOT BETWEEN AND?

SELECT id,username,age,salary FROM user1

?WHERE salary NOT BETWEEN 18 AND 30;?

IN指定集合范圍

SELECT id,username,age FROM user1 WHERE id IN(1,3,5,7,9,29,45,78);

NOT IN 不在指定集合范圍

SELECT id,username,age FROM user1 WHERE id NOT IN(1,3,5,7,9,29,45,78);

BETWEEN AND AND AND

SELECT id,username,age,sex,salary addr FROM user1

?WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';

LIKE 模糊查詢 字符串? LIKE不區(qū)分大小寫的

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

LIKE %% 任意一個字符

?SELECT id,username,age,sex FROM user1 WHERE username LIKE '%三%';

LIKE ___ 一個字符 限定位數(shù)

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

GROUP BY hash算法 過濾掉相同的，只保留一條第一次檢索出來的數(shù)據(jù)作為代表，不同的記錄成為不同的一個組,多的數(shù)據(jù)會第一顯示出來，這是默認情況下，但可以通過函數(shù)顯示出這個組中的詳細的各個記錄的信息，一組查該組的所有記錄,會派出一個sex代表出來

SELECT id,username,age,sex FROM user1 GROUP BY sex;

GROUP BY 和 GROUP_CONCAT函數(shù)

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

COUNT(*) 統(tǒng)計有多少條記錄數(shù) 就是每條每條記錄

SELECT COUNT(*) AS total_users FROM user1;

COUNT(字段)在統(tǒng)計字段，如果字段值為空就不記錄出來

SELECT COUNT(userDesc) FROM user1;

CONUT(*)配合GROUP BY 使用的時候 COUNT(*) 統(tǒng)計的是 GROUP BY 分組中的記錄數(shù)

SELECT? sex,GROUP_CONCAT(username) AS usersDetail ,COUNT(*) AS toalUsers FROM user1 GROUP BY sex;

GROUP BY 是配合聚合函數(shù)使用的 聚合函數(shù)受GROUP BY 影響

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;

WITH ROLLUP 會在最后一條記錄加上各個分組合和聚合函數(shù)統(tǒng)計的結(jié)果相加到一起

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

POSITION 按照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

?GROUP BY 1;

Having是二次 是對分組的結(jié)果 受分組影響 可以加 聚合函數(shù) select查詢的列

SELECT addr,

?GROUP_CONCAT(username) AS userDetail,

?COUNT(*) AS totalUsers

?FROM user1

?GROUP BY addr HAVING COUNT(*);

Having可通過別名

SELECT addr,

?GROUP_CONCAT(username) AS userDetail,

?COUNT(*) AS totalUsers

?FROM user1

?GROUP BY addr HAVING totalUsers

>=3;

ORDER BY DESC 降序 ASC升序

SELECT id,username,age FROM user1 ORDER BY id DESC;

多個ORDER BY,一個ORDER BY 不能滿足可通過多個

SELECT id,username,age FROM user1 ORDER BY age ASC,id ASC;

RAND() 返回[0-1) 隨機排序

SELECT id,username,age FROM user1 ORDER BY RAND();

LIMIT顯示5條記錄

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

LIMIT從指定顯示記錄

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

?

LIMIT分頁實現(xiàn) 從第4條開始顯示3條也就是到6條 包括4本身 LIMIT受ORDER BY影響

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

綜合練習(xí)

SELECT addr,

?GROUP_CONCAT(username) AS usersDetail,

?COUNT(*) AS totalUsers,

?SUM(age) AS sum_age,

?MAX(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,1;

笛卡爾積 相等于二層循環(huán) 第一個記錄數(shù)*第二個表記錄數(shù)

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

一對一 內(nèi)連接 INNER JOIN ON 條件 交集共有

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

左連接 LEFT OUTER JOIN 顯示左表所有記錄 以O(shè)N為條件 右邊符合的顯示 不符合的右表以NULL形式顯示

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;

右連接 RIGHT OUTER JOIN 顯示右表所有記錄 以O(shè)N為條件 左表符合的顯示 不符合的右表以NULL形式顯示

SELECT e.id,e.username,e.age,d.depName,d.depDesc FROM? emp AS e RIGHT OUTER JOIN? dep AS d? ?ON e.depId=d.id;

三表復(fù)合多表連查單表關(guān)系? 缺點性能不好

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;

四表

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;

級聯(lián)操作

?ALTER TABLE news

?ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)

?ON DELETE CASCADE ON UPDATE CASCADE;

子查詢

IN 在集合中查詢

SELECT * FROM emp WHERE id IN (1,2,3,4);

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

NOT IN 不在集合中查詢

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

>= 子查詢

SELECT id,username,score FROM stu WHERE score>=

(SELECT score FROM level WHERE id=1);

EXISTS 子查詢 true || false

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

>= any最小值? all最大值

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

= ANY等于任意值 存在ANY中的任意值才返回

SELECT * FROM stu?

WHERE score = ANY(SELECT score FROM level);

!= <> ALL不等于任意值 不存在ALL中的任意值都會返回

SELECT * FROM stu

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

<= any最大值? all最小值

SELECT * FROM stu

?WHERE score < ALL(SELECT score FROM level);

CREATE根據(jù)select創(chuàng)建表?

CREATE TABLE user1(

id TINYINT AUTO_INCREMENT KEY,

? ? username VARCHAR(20)

)SELECT id,username FROM user;

INSERT根據(jù)select創(chuàng)建表

INSERT INTO user1(username) SELECT username FROM user;

INSERT不指定字段直接通過select創(chuàng)建表

INSERT user2 SELECT * FROM user1;

INSERT SET 形式通過子查詢插入數(shù)據(jù)

通過like創(chuàng)建一模一樣的表 但是沒數(shù)據(jù)

CREATE TABLE user2 LIKE user1;

DISTINCT去掉重復(fù)的列select查詢

SELECT DISTINCT(username) FROM user2;

聯(lián)合查詢 UNION 合并重復(fù)數(shù)據(jù)? 留下不重復(fù)針對多個表的，單個表就不用

SELECT * FROM user1?

UNION SELECT * FROM user2;

UNION ALL 簡單的合并 兩個表并在一起

SELECT * FROM user1?

UNION ALL SELECT * FROM user2;

自連接 無限極分類 虛擬出兩種相同的表，只不過在創(chuàng)建表時的條件不一樣 查詢所有分類信息，并且得到父類

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;

自連接 無限極分類 虛擬出兩種相同的表，只不過在創(chuàng)建表時的條件不一樣 查詢所有分類信息及其子分類

?SELECT? p.id,p.cateName AS pCateName,s.cateName AS sCateName

?FROM cate AS s RIGHT JOIN cate AS p ON p.id=s.pid;

自連接 分組 查詢所有的分類及其子分類的數(shù)目

SELECT? p.id,p.cateName AS pCateName,COUNT(s.cateName) AS count

?FROM cate AS s?

?RIGHT JOIN cate AS p?

?ON p.id=s.pid?

?GROUP BY p.cateName ORDER BY id ASC;

全連接

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

WHERE Table.id IS NULL OR TableB.id IS NULL