国产精品天干天干,亚洲毛片在线,日韩gay小鲜肉啪啪18禁,女同Gay自慰喷水

歡迎光臨散文網(wǎng) 會(huì)員登陸 & 注冊(cè)

MySQL的常見SQL語(yǔ)句

2020-10-08 02:33 作者:開源開發(fā)者  | 我要投稿

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


查看當(dāng)前數(shù)據(jù)庫(kù)

SELECT DATABASE();


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

QUIT[;]


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

SHOW DATABASES;

SHOW SCHEMAS;


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

SELECT VERSION();


當(dāng)前登錄數(shù)據(jù)庫(kù)的用戶

SELECT USER();


顯示當(dāng)前時(shí)間

SELECT NOW();


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

HELP CREATE DATABASE

? CREATE

\H CREATE


取消執(zhí)行SQL

SELECT USER()\c


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

CREATE DATABASE test1;

CREATE SCHEMA test1;


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

CREATE DATABASE `database`;



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

CREATE DATABASE IF NOT EXISTS test1;


查看警告

SHOW WARNINGS;


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

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ù)庫(kù)

USE test;


ALTER

改變編碼方式

ALTER DATABASE test DEFAULTER CHARACTER SET = 'UTF8';


添加字段

ALTER TABLE user1 ADD username VARCHAR(20);


添加字段在某個(gè)字段的后面

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ǔ)上修改字段添加默認(rèn)值SET

ALTER TABLE user2?

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


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

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ù)庫(kù)

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

當(dāng)前日期時(shí)間

SELECT NOW()

當(dāng)前時(shí)間

SELECT CURRENT_TIME();

當(dāng)前時(shí)間

SELECT CURTIME();


CURRENT_DATE()

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

當(dāng)前日期

CURRENT_DATE()


CURRENT_TIMEStamP

INSERT test_timestamp(a) VALUES(CURRENT_TIMEStamP);



當(dāng)前日期和時(shí)間

CURRENT_TIMEStamP


進(jìn)一取整

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


取絕對(duì)值

SELECT ABS(-12);


冪運(yùn)算

SELECT POWER(2,3);


得到PI

SELECT PI();


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

SELECT RAND();

定給參數(shù)會(huì)變成一個(gè)標(biāo)識(shí)

比如SELECT RAND(1);



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

SELECT SIGN(-23);



e的幾次方

SELECT EXP(2);


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

SELECT CHAR_LENGTH('你好啊');



真正字符串長(zhǎng)度

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個(gè)或者后2個(gè)字符

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


用字符串填充到指定長(zhǎng)度

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 第一個(gè)字符串大于第二個(gè)字符串返回是正1? 第一個(gè)字符串小于第二個(gè)字符串返回是-1

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



返回當(dāng)前日期

SELECT CURDATE(),CURRENT_DATE();


返回當(dāng)前時(shí)間

SELECT CURTIME(),CURRENT_TIME();


返回當(dāng)前的日期時(shí)間

SELECT NOW(),CURRENT_TIMEStamP(),SYSDATE();


返回日期中的月和月份名稱

SELECT MONTH('2017-02-19');

SELECT MONTH(CURDATE()),MONTHNAME(CURDATE());


返回星期幾的名稱

SELECT DAYNAME(NOW());


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

SELECT DAYOFWEEK(NOW());


返回第幾個(gè)星期了

SELECT WEEK(NOW());


返回年份 月份 號(hào) 小時(shí) 分鐘 秒

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


計(jì)算兩個(gè)日期相差的天數(shù)

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



其他常用函數(shù)


獲得版本

SELECT VERSION();


當(dāng)前連接數(shù)

SELECT CONNECTION_ID();


獲得當(dāng)前的數(shù)據(jù)庫(kù)名

SELECT DATABASE(),SCHEMA();



當(dāng)前登錄的用戶

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



得到上一次auto_increment的值

SELECT LAST_INSERT_ID();



MD5加密

SELECT MD5('king');


密碼加密算法

SELECT PASSWORD('root');


查詢r(jià)oot密碼

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


查看表狀態(tài)

SHOW TABLE STATUS LIKE 'myisam_1';



設(shè)置定長(zhǎng)格式

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;



查詢指定庫(kù)中的表

SELECT * FROM imooc.user1;



字段起別名

SELECT id AS '編號(hào)',username AS '用戶名' , sex AS '性別'FROM user1;



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

SELECT id,username FROM user1 AS u;



表名.字段名

SELECT user1.id,user1.username FROM user1;



<=>檢測(cè)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 %% 任意一個(gè)字符

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


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

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


GROUP BY hash算法 過濾掉相同的,只保留一條第一次檢索出來的數(shù)據(jù)作為代表,不同的記錄成為不同的一個(gè)組,多的數(shù)據(jù)會(huì)第一顯示出來,這是默認(rèn)情況下,但可以通過函數(shù)顯示出這個(gè)組中的詳細(xì)的各個(gè)記錄的信息,一組查該組的所有記錄,會(huì)派出一個(gè)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)計(jì)有多少條記錄數(shù) 就是每條每條記錄

SELECT COUNT(*) AS total_users FROM user1;


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

SELECT COUNT(userDesc) FROM user1;



CONUT(*)配合GROUP BY 使用的時(shí)候 COUNT(*) 統(tǒng)計(jì)的是 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 會(huì)在最后一條記錄加上各個(gè)分組合和聚合函數(shù)統(tǒng)計(jì)的結(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是二次 是對(duì)分組的結(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;


多個(gè)ORDER BY,一個(gè)ORDER BY 不能滿足可通過多個(gè)

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


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

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分頁(yè)實(shí)現(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) 第一個(gè)記錄數(shù)*第二個(gè)表記錄數(shù)

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


一對(duì)一 內(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)系? 缺點(diǎ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;



級(jí)聯(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中的任意值都會(huì)返回

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ù)針對(duì)多個(gè)表的,單個(gè)表就不用

SELECT * FROM user1?

UNION SELECT * FROM user2;



UNION ALL 簡(jiǎn)單的合并 兩個(gè)表并在一起

SELECT * FROM user1?

UNION ALL SELECT * FROM user2;




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

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)建表時(shí)的條件不一樣 查詢所有分類信息及其子分類

?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


MySQL的常見SQL語(yǔ)句的評(píng)論 (共 條)

分享到微博請(qǐng)遵守國(guó)家法律
临沭县| 玉门市| 阿克苏市| 肇源县| 萝北县| 湘潭县| 高陵县| 台州市| 宁阳县| 城固县| 宿州市| 汤原县| 东光县| 遂溪县| 德格县| 锦屏县| 恩平市| 大余县| 沙洋县| 伊春市| 合阳县| 乡城县| 长泰县| 商丘市| 关岭| 天长市| 丰原市| 稻城县| 宜川县| 延长县| 牟定县| 青冈县| 正阳县| 公主岭市| 临潭县| 通海县| 丰顺县| 三河市| 独山县| 米林县| 屏山县|