常用命令 · mysql從入門到精通

[TOC] ## DDL語言 1、進入mysql的指令： mysql -h 127.0.0.1 -P 3306 -u root -p敲回車 //注意：端口是大寫的P，密碼是小寫的p 這時候輸入密碼 2、mysql> set password for root@localhost = password('新密碼'); //修改密碼。root@localhost是用戶名和主機 mysqladmin -uroot -p'123456' password 'root' //新密碼要和password中間空個格。這種是不進入mysql執行的 3、退出用exit或quit ### 操作庫 1、show databases; //顯示數據庫 2、create database name; //創建數據庫 3、use databasename; //選擇要打開的數據庫 4、drop database name //直接刪除數據庫，不提醒 5、mysqladmin drop databasename //刪除數據庫前，有提示。 6、flush privileges //刷新數據庫 7、show engines\G; //數據庫支持的所有引擎 ### 操作表 1、創建表 CREATE TABLE 表名( 字段名數據類型其他關鍵詞, ..... ); 2、show tables; //顯示表 3、show tables like'%tablename%' //模糊查詢表名 4、desc 表名; //查看表結構 rename命令格式：rename table 原表名 to 新表名; //修改表名 5、 show table status\G; //查看表的狀態 5、show create table 表名; //查看表的引擎 5、【修改表結構：ALTER TABLE 表名】 ALTER TABLE 表名 MODIFY 字段名 VARCHAR(255); //修改列的數據類型如果同時需要修改列名和數據類型，則可以先修改數據類型再來修改列名，即執行上述sql語句，倆遍。增加列： ALTER TABLE 表名 ADD 新字段名數據類型關鍵詞位置（放到哪個列后面） mysql> alter table tp5_user add sex tinyint unsigned not null default 0 comment '0男1女' after age; //將新字段，放到age后面 ALTER TABLE 表名 DROP 字段名 //刪除列 alter table 表名 engine=innodb; //修改表引擎 alter table 表名 add primary key(字段名); //添加主鍵 alter table 表名 drop 字段名; //刪除索引 6、truncate table 表名 //這樣不但將數據全部刪除，而且重新定位自增的字段 7、DROP TABLE 表名 //刪除表 8、復制表結構及數據到新表 CREATE TABLE 新表 SELECT * FROM 舊表 9、只復制表結構到新表 CREATE TABLE 新表 LIKE 舊表 10、復制舊表的數據到新表(假設兩個表結構一樣) INSERT INTO 新表 SELECT * FROM 舊表 11、復制舊表的數據到新表(假設兩個表結構不一樣) INSERT INTO 新表(字段1,字段2,…….) SELECT 字段1,字段2,…… FROM 舊表 ## DML和DQL語言 ### sql語句的增刪改查 INSERT INTO 表名 (字段1,字段2) VALUES (值1,值2); //【增】 DELETE FROM 表名 WHERE條件; //【刪】 UPDATE 表名 SET 字段=新值 WHERE條件; //【改】 SELECT * FROM 表名; //【查】點擊查看常用函數點擊查看常用關鍵字使用關鍵字 1、group by 字段名 [having 條件表達式][with rollup] //分組例子1：與聚合函數一起使用先以性別進行分組，在聚合統計男、女各有多少人 select count(*) as totel,sex from tp5_user group by sex; 例子2：與group_concat()函數一起使用 select gradeName,group_concat(stuName) from t_student group by gradeName; 例子3：與HAVING一起使用 select sex from tp5_user group by sex having count(*) > 100; //在group中執行搜索，大于100的。having 有多個條件，用and 或 or 相連例子4：與with rollup 一起使用（最后加入一個總和行）注意：使用with rollup 后，不能再使用 ORDER BY 語句對結果集進行排序 select gradeName,group_concat(stuName) from t_student group by gradeName with rollup; 2、去重以name去除重復的行，注意：只能寫一個字段，否則去重會失效（所以這個demo去重不成功） SELECT distinct name,age from yii_user 3、連接查詢語法：SELECT 哪些字段 FROM 表1 用哪種連表2 ON 連表條件 select * from score,student where score.id=student.id -- 相當于inner join select * from score join student on (score.id=student.id) -- 默認的join是inner join select * from score join student using(id) where student.id=2 -- using函數等同于on，參數傳關聯字段(可以帶表名也可以不帶) -- A表中有，B表中沒有 select * from score as a left JOIN student as b on a.id=b.id where b.id is null select * from score as a where a.id not in (SELECT id from student) select * from score as a where not exists (select 1 from student as b where a.id=b.id) -- 用not EXISTS的話，要加關聯字段注意：在使用left jion時，on和where條件的區別如下： 1、 on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。 2、where條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。 3、全連和內連，一定要寫on或where，否則會出現笛卡爾積（a表的總行數*b表的總條數）如這種 select * from score INNER JOIN student -- 或不寫inner join 或寫 full join都會出現這種情況內連（推薦）內連是查條件全相等的: SELECT `a`.`id`,`a`.`title`,`a`.`content`,`c`.`cat_name` FROM `tp5_article` `a` INNER JOIN `tp5_cat` `c` ON `a`.`cat_id`=`c`.`id` ; //5表相連，模糊查詢默認是為空。from前只寫你要輸出的字段即可，不用寫字段的關聯條件 SELECT a.CREATE_TIME,a.remark,a.nextFollowupTime, b.id,b.name as resource_name, c.name as input_name, d.name as grade_name, e.name as school_name FROM customer_folowup AS a INNER JOIN customer AS b ON a.CUSTOMER_ID = b.id INNER JOIN user as c on a.CREATE_USER_ID=c.USER_ID INNER JOIN data_dict as d on b.pointial_student_grade_dict=d.id INNER JOIN organization as e on b.BL_SCHOOL=e.id where d.institution_id=2 and d.STATE=0 and c.name like '%%' order by a.id asc limit 100 補充：MySQL STRAIGHT_JOIN 與 NATURAL JOIN的使用長話短說：straight_join實現強制多表的載入順序，從左到右，如： ...A straight_join B on A.name = B.name straight_join完全等同于inner join 只不過，join語法是根據“哪個表的結果集小，就以哪個表為驅動表”來決定誰先載入的，而straight_join 會強制選擇其左邊的表先載入。往往我們在分析mysql處理性能時，如(Explain),如果發現mysql在載入順序不合理的情況下，可以使用這個語句，但往往mysql能夠自動的分析并處理好。左連：左表里有多少條就打印出來多少條，匹配不上的，右表字段顯示null select a.*,b.* from score as a LEFT JOIN student as b on a.id=b.id 右連： select score.*,student.* from score right join student on score.id=student.id 全連：完整外部聯接返回左表和右表中的所有行（有的版本不支持，可以使用left join union right join）交叉連接：倆表的行數相乘 SELECT a.*,b.* FROM score as a CROSS JOIN student as b on a.id=b.id -- 不論寫不寫cross join,如果不加on條件都是笛卡爾乘積 4、聯合查詢：倆條sql語句通過UNION ALL連接在一起。從上到下依次執行語法：Union [union選項] All: 保留所有(不管重復)；Distinct: 去重(整個重復): 默認的場景：從多個表中查詢出相似結構的數據，并且返回一個結果集從單個表中多次SELECT查詢，將結果合并成一個結果集返回。 SELECT title,content FROM `tp5_article` UNION ALL SELECT * FROM `tp5_cat`; 注意：列的數量必須相等 where和Union的區別：這里Union All可以返回重復的數據，就是where子句完成不了的工作 order by 字段，必須是2個sql中都有的 union 去重；union all不去重 5、子查詢：括號里sql的結果，是括號外sql的where條件。從里到外依次執行（連表比子查詢效率高）語法：.帶in關鍵字查詢：select 字段1，字段2 frome 表名 where 字段 [not]in(元素1，元素2)；按位置分類: 子查詢(select語句)在外部查詢(select語句)中出現的位置 From子查詢: 子查詢跟在from之后 SELECT people.name,people.chinese,people.math,people.english FROM (SELECT name,chinese,math,english FROM tb_demo071) AS people Where子查詢: 子查詢出現where條件中 SELECT * FROM mark where id in(2,5); //查id等于2或5的 SELECT id FROM `student` where id in(select stu_id from mark); //查學生表里有成績的 Exists子查詢: 子查詢查詢到記錄（存在），則進行外層查詢，否則，不執行外層查詢。還有個not exists -- EXISTS:如果存在身高大于2米的人，列出表中所有數據。否則不輸出。因為exists返回的是bool值 select * from student where EXISTS (select * from student where height >= '200') 按結果分類: 根據子查詢得到的數據進行分類(理論上講任何一個查詢得到的結果都可以理解為二維表) 標量子查詢: 子查詢得到的結果是一行一行的數據列子查詢: 子查詢得到的結果是一列多行 -- 列子查詢：找出語文和數學都及格人的名字（只輸出名字，所以叫一列多行） -- 換種思路，先找出小于60分的，在pass掉小于60分的人（只要這個人有一科低于60分，那他另一科的成績也就掛了）就是語文和數學都及格的了 select name from score where name not in (select name from score where score<60) -- 前提是語文和數學是一個字段 3.行子查詢: 子查詢得到的結果是多列一行帶比較運算符的子查詢（子查詢可以使用比較運算符） -- 行子查詢：找出年齡最大且身高最高的人 -- 換種思路：一個條件一個條件的取最大數 select * from student where age = (select max(age) from student) and height = (select max(height) from student) select * from student where (age,height)=(select max(age),max(height) from student) 4.表子查詢: 子查詢得到的結果是多行多列(出現的位置是在from之后) -- 表子查詢：找出每個人最好的成績 -- 思路：先以人分類，相同名字的湊成一組，在根據分數分組后倒序，這樣張三的最高分就在上面了-》在拿名字分組，此時相同名字他取第一個 select * from (select * from score GROUP BY name,score desc) as a GROUP BY name 例子1：any 或 some any 或 some關鍵字是同義詞，表示滿足其中任何一個條件即可，它們允許創建一個表達式對子查詢的返回值列表進行比較，只要滿足內層子查詢中的任何一個比較條件，就返回一個結果作為外層查詢的條件例子4、帶any關鍵字的子查詢（any關鍵字表示滿足其中任一條件） select * from t_book where price>= any(select price from t_priceLevel); 例子5、帶all關鍵字的子查詢（all關鍵字表示滿足所有條件） select * from t_book where price>= all(select price from t_priceLevel); 6、limit 從第幾個開始取，取多少條 7、帶like的模糊查詢：select 字段1，字段2... frome 表名 where 字段 [not] like '字符串'； “%”代表任意字符； “_"代表單個字符； select * frome t_student where stuName like '張三''； //完全等于張三的 select * frome t_student where stuName like '張三%''； //右匹配，找以張開頭的 select * frome t_student where stuName like '%張三%''； //含有張三的任意字符 select * frome t_student where stuName like '張三_'' 8、空值查詢：select 字段1，字段2...frome 表名 where 字段 is[not] null; //null比較特殊，詳情點擊查看 9、帶between and的范圍查詢：select 字段1，字段2 frome 表名 where 字段 [not]between 取值1 and 取值2； select * frome t_student where age between 21 and 29; select * frome t_student where age not between 21 and 29; SELECT * FROM `yii_msg` where speak_time >= '' and speak_time <= '' //或用 >= <= 10、HAVING 可以單獨使用 -- HAVING 要放在最下面，原因1：having是對查出來的結果在篩選，而where是查之前進行篩選； -- 原因2：having支持通過字段別名（mini_contract就是別名），進行篩選，where只支持原來的字段名 HAVING 1 = 1 ## DCL語言 ### 授權注意：和權限相關的，每次做完都要刷新權限 flush privileges; 1、創建用戶 create user '用戶名' @'指定ip訪問' identified by '密碼'; create user 'test' @'127.0.0.1' identified by '123456'; 2、給新用戶賦予權限 grant 權限（增刪改查關鍵字） on 數據庫名.* to '用戶名' @'指定ip訪問'; grant update,insert,delete,select on test.* to 'test' @'127.0.0.1'; 3、撤銷權限 revoke 權限 on 數據庫名.* from '用戶名' @'指定訪問ip'; revoke select on test.* from 'test' @'127.0.0.1'; 注意：撤銷后刷新完權限后，要先quit退出在重新登錄才生效 4、給所有的數據庫用*.*，給所有的權限用ALL 案例：創建用戶firstdb(密碼firstdb)和數據庫，并賦予權限于firstdb數據庫 mysql> create database firstdb; //創建數據庫 mysql> grant all on firstdb.* to firstdb identified by 'firstdb'; //會自動創建用戶firstdb。mysql默認的是本地主機是localhost,對應的IP地址就是127.0.0.1，所以你用你的IP地址登錄會出錯，如果你想用你的IP地址登錄就要先進行授權用grant命令。 mysql>grant all on *.* to firstdb@localhost identified by '123456'; //grant 與on 之間是各種權限，例如:insert,select,update等 on 之后是數據庫名和表名,第一個*表示所有的數據庫，第二個*表示所有的表 firstdb可以改成你的用戶名，@后可以跟域名或IP地址，identified by 后面的是登錄用的密碼，可以省略，即缺省密碼或者叫空密碼。 5、查看權限 show grants for '用戶名'@'指定訪問ip'\G; show grants for 'test'@'127.0.0.1'\G; 6、刪除用戶 drop user '用戶名'@'指定訪問ip'; //注意：要和表中的對應上 drop user 'test'@'127.0.0.1'; ## DTL事務控制語句 start transaction; //開啟事物執行sql.... commit; //沒問題，提交 rollback; //有問題，回滾 ### 存儲過程 1、定義存儲過程 mysql> create procedure test(a int) -> begin -> select * from tp5_user where id=a; -> end/// Query OK, 0 rows affected (0.01 sec) 2、調用存儲過程（使用call關鍵字調用存儲過程的名字） call test(1)/// 3、刪除存儲過程 drop procedure test/// 4、查看存儲過程 show procedure status/// ### 視圖總結：相當于是as后面得sql結果存到了test_view表中（show tables;就能看出來），然后使用視圖的時候查test_view表 1、創建 create view 視圖名(視圖名中定義的字段別名) as SQL語句; mysql> create view test_view(view_name,view_fee) as select name,fee from tp5_user where id=6; 2、使用 select 視圖名中定義的字段別名 from 視圖名; select view_name from test_view; 3、查看 show create view 視圖名\G; show create view test_view\G; 4、刪除 drop view 視圖名; drop view test_view; ### 觸發器總結：相當于php框架中的鉤子 1、創建 mysql> create trigger 觸發器名稱關鍵字1 關鍵字2 on 表名 for each row -> begin -> update total_num set num=num-1 where type=1; -> end/// mysql> delimiter /// mysql> create trigger delete_total_num after delete on article for each row -> begin -> update total_num set num=num-1 where type=1; -> end/// 2、查看 show create trigger 觸發器名稱\G; show create trigger delete_total_num\G; 3、刪除 drop trigger 觸發器名稱; drop trigger delete_total_num; 其他 1、select version(),current_date; //顯示當前mysql版本和當前日期 2、SELECT User, Host, Password FROM mysql.user; //在Mysql中顯示所有用戶（mysql庫中的user表） 3、SELECT DISTINCT User FROM mysql.user; //顯示去重后的用戶（其實只是加了個關鍵字修飾sql語句）注意：在Mysql中其實有一個內置且名為mysql的數據庫，這個數據庫中存儲的是Mysql的一些數據，比如用戶（user）、權限信息、存儲過程等，所以呢，我們可以通過如下簡單的查詢語句來顯示所有的用戶呢 4、 delimiter 結束符 //修改sql語句的結束符編碼相關亂碼：只有win的cmd才會出現，是因為cmd的編碼是gbk（改不了的）改mysql的.... 1.查看數據庫編碼格式 1 mysql> show variables like 'character_set_database'; 2.查看數據表的編碼格式 1 mysql> show create table <表名>; 3.創建數據庫時指定數據庫的字符集 mysql>create database <數據庫名> character set utf8; 4.創建數據表時指定數據表的編碼格式 create table tb_books ( name varchar(45) not null, price double not null, bookCount int not null, author varchar(45) not null ) default charset = utf8; 5.修改數據庫的編碼格式 mysql>alter database <數據庫名> character set utf8; 6.修改數據表格編碼格式 mysql>alter table <表名> character set utf8; 7.修改字段編碼格式 mysql>alter table <表名> change <字段名> <字段名> <類型> character set utf8; mysql>alter table user change username username varchar(20) character set utf8 not null; 導入導出數據庫注意：mysqldump和mysql指令是在未登錄未進入的情況下使用的 1.導出整個數據庫 mysqldump -u 用戶名 -p --default-character-set=latin1 數據庫名 > 導出的文件名(數據庫默認編碼是latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql #>后面不加目錄名，是導出到你當前所在的目錄中 2.導出一個表 mysqldump -u 用戶名 -p 密碼數據庫名表名> 導出的文件名 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql 3.導出一個數據庫結構 mysqldump -u wcnc -p -d -add-drop-table smgp_apps_wcnc >d:wcnc_db.sql -d 沒有數據 -add-drop-table 在每個create語句之前增加一個drop table 4.1、先登錄后-》導入數據庫 mysql -u root -p //進入mysql數據庫控制臺 mysql>use 數據庫 //選擇數據庫 mysql>source wcnc_db.sql //使用source命令，后面參數為腳本文件(如這里用到的.sql) 4.2、不登錄-》使用mysql命令，導入數據庫 mysql -u 用戶名 -p 數據庫名< 要導入的sql文件 mysql -u root -p test < /user.sql #然后敲回車，輸入密碼自定義變量 -- 自定義變量，計算行號 SELECT @row := @row + 1 as 行號, -- 迭代器+1 fenxiao.* FROM fenxiao, (SELECT @row := 0) t -- 虛個表，將變量置位0 WHERE @row < 8 -- 輸出行號<8的數據自定義函數 -- 自定義函數之字符串替換（函數體中必須以分號結尾） DELIMITER $$ -- 修改結束符 DROP FUNCTION IF EXISTS `test`.`getdate`$$ -- 如果test庫有getdate函數，就先將該函數刪除 CREATE FUNCTION `test`.`getdate`(gdate datetime) RETURNS varchar(255) -- gdate是形參，datetime是數據類型；返回字符串 BEGIN -- 函數體開始,相當于{ DECLARE x VARCHAR(255) DEFAULT ''; -- 定義變量，默認是空 SET x= date_format(gdate,'%Y年%m月%d日%h時%i分%s秒'); -- 給變量賦值 RETURN x; -- 返回結果，x的類型要和 returns 時的一樣 END $$ -- 函數體開始,相當于} DELIMITER; select getdate('2018-07-23 15:47:56'); -- 調用函數 -- 自定義函數之 if判斷（將字符串s保留前n位） DELIMITER $$ DROP FUNCTION IF EXISTS `sp_test`.`cutString` $$ CREATE FUNCTION `sp_test`.`cutString`(s VARCHAR(255),n INT) RETURNS varchar(255) BEGIN -- 函數體開始 IF(ISNULL(s)) THEN RETURN ''; -- 判斷開始 ELSEIF CHAR_LENGTH(s)<n THEN RETURN s; -- 小于n位直接返回 ELSEIF CHAR_LENGTH(S)=n THEN RETURN '相等'; ELSE RETURN CONCAT(LEFT(s,n),'...'); -- 大于n位，先截取，在拼接上... END IF; -- 判斷結束 END $$ -- 函數體結束 DELIMITER ; -- 自定義函數之循環 DELIMITER $$ DROP FUNCTION IF EXISTS `test`.`morestar`$$ CREATE FUNCTION `test`.`morestar`(n INT) RETURNS text -- 返回長文本 BEGIN DECLARE i INT DEFAULT 0; -- 定義變量，默認值是0 DECLARE s TEXT DEFAULT ''; -- 定義變量 myloop:LOOP -- 循環開始 SET i=i+1; -- 每次循環+1 SET s = CONCAT(s,'*'); - IF i > n THEN LEAVE myloop; -- 如果i 大于傳進來的n 就結束循環 END IF; END LOOP myloop; -- 循環結束 RETURN s; END $$ DELIMITER ; SELECT morestar(5); -- 調用