【內置】總結持續更新--mysql經常使用指令

時間  2020-02-02 標籤 內置 總結 持續 更新 mysql 經常 使用 指令

建議看這以前先看我寫的mysql從入門到精通

mysql經常使用函數參考php

https://blog.csdn.net/xfg0218/article/details/53415374  #*win安裝推薦配置（heavy）的中文配置詳解html

根據系統的內存大小，能夠參考下面的配置文件，進行配置mysql

my-small.ini （內存 <= 64M）
my-medium.ini （內存 128M ）
my-large.ini （內存 512M）
my-huge.ini （內存 1G-2G）
my-innodb-heavy-4G.ini （內存 4GB）sql

 

https://www.cnblogs.com/nxblog/p/4359861.html  #*經常使用錯誤碼數據庫

 

Mysql 查詢表死鎖 和結束死鎖的表步驟

詳情參考：https://blog.csdn.net/enweitech/article/details/52447006segmentfault

指令詳解：https://blog.csdn.net/sunqingzhong44/article/details/70570728php框架

狀態值的官方文檔：https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.htmlapp

一、查詢是否鎖表 show OPEN TABLES where In_use > 0;框架

二、查詢進程yii

show full processlist; #列出當前的全部process

顯示的大體以下

+------+-------------+---------------------+--------------------------+-------------+--------+---------------------------------------------------------------+------------------------------------------------------------------------------------------+
| Id   | User        | Host                | db                       | Command     | Time   | State                                                         | Info                                                                                     |
+------+-------------+---------------------+--------------------------+-------------+--------+---------------------------------------------------------------+------------------------------------------------------------------------------------------+
 
| 9934 | root        | Master.Hadoop:52139 | my_db                    | Sleep       |    176 |                                                               | NULL                                                                                     |
| 9936 | root        | 10.92.36.90:58919   | NULL                     | Query       |     12 | Waiting for table metadata lock                               | Alter table `ans_pms_db_test`.`system_organization_info`   
  drop column `organize_id` |

看到此時9936的進程的狀態爲 Waiting xxxx  能夠將其kill掉

三、殺掉進程

kill 9936;

補充：

查看正在鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

 

 

DDL語言

 

一、進入mysql的指令：

mysql -h 127.0.0.1 -P 3306 -u root -p敲回車    //注意：端口是大寫的P，密碼是小寫的p

這時候輸入密碼

二、mysql> set password for root@localhost = password('新密碼'); //修改密碼。root@localhost是用戶名和主機

mysqladmin -uroot -p'123456' password 'root'  //新密碼要和password中間空個格。這種是不進入mysql執行的

三、退出用exit或quit

操做庫

一、show databases; //顯示數據庫
二、create database 數據庫名; //建立數據庫
三、use 數據庫名; //選擇要打開的數據庫
四、drop database 數據庫名  //直接刪除數據庫，不提醒

五、mysqladmin drop database name //刪除數據庫前，有提示？？。

六、flush privileges //刷新數據庫

七、show engines\G;    //數據庫支持的全部引擎

*八、設置mysql數據庫爲只讀

詳情參考：https://blog.csdn.net/xieyunc/article/details/80527465

mysql> set global read_only=1;    #1是隻讀，0是讀寫

mysql> show global variables like "%read_only%";

操做表

一、建立表

CREATE TABLE 表名(

    字段名 數據類型 其餘關鍵詞,

    .....

);

CREATE UNIQUE INDEX 索引名 ON 表名 (要加索引的列名);   //*建立惟一索引

*若是您但願以降序索引某個列中的值，您能夠在列名稱以後添加保留字 DESC：

CREATE INDEX PersonIndex ON Person (LastName DESC) #在列名中加一個desc

*假如您但願索引不止一個列，您能夠在括號中列出這些列的名稱，用逗號隔開：不知道這是否是組合索引？？

CREATE INDEX PersonIndex ON Person (LastName, FirstName) #多個列用逗號隔開

二、show tables;   //顯示錶

三、show tables like'%tablename%'  //模糊查詢表名

四、desc 表名;  //查看錶結構

rename命令格式：rename table 原表名 to 新表名;    //修改表名

六、 show table status\G;    //查看錶的狀態

五、show create table 表名;  //查看錶的引擎

五、【修改表結構：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 字段名;    //刪除索引

六、truncate table 表名  //這樣不但將數據所有刪除，並且從新定位自增的字段

七、DROP TABLE 表名 //刪除表

8、複製表結構及數據到新表

CREATE TABLE 新表名 
SELECT * FROM 舊錶名

九、只複製表結構到新表

CREATE TABLE 新表名
LIKE 舊錶名

十、複製舊錶的數據到新表(假設兩個表結構同樣)

INSERT INTO 新表名
SELECT * FROM 舊錶名

十一、複製舊錶的數據到新表(假設兩個表結構不同)

INSERT INTO 新表(字段1,字段2,…….)
SELECT 字段1,字段2,…… FROM 舊錶

十二、備份表

-- 備份sql
-- bi_contract_product_backup 新表名
create table bi_contract_product_backup as select * FROM bi_contract_product;

1三、如何查看哪些ip鏈接着MYSQL呢

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

 

DML和DQL語言

 

sql語句的增刪改查

INSERT INTO 表名 (字段1,字段2) VALUES (值1,值2);    //【增】

DELETE FROM 表名 WHERE條件;     //【刪】

UPDATE 表名 SET 字段=新值,字段=新值 WHERE條件;     //*【改】

SELECT * FROM 表名;    //【查】

點擊查看經常使用函數

點擊查看經常使用關鍵字

 

使用關鍵字

一、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;

二、去重

  以name去除重複的行，注意：只能寫一個字段，不然去重會失效（因此這個demo去重不成功）

SELECT distinct name,age from yii_user

三、鏈接查詢

語法：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條件的區別以下：

一、 on條件是在生成臨時表時使用的條件，它無論on中的條件是否爲真，都會返回左邊表中的記錄。

二、where條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不爲真的就所有過濾掉。 

三、全連和內連，必定要寫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條件都是笛卡爾乘積

四、聯合查詢：倆條sql語句經過UNION ALL鏈接在一塊兒。從上到下依次執行

語法：Union [union選項]

All: 保留全部(無論重複)；Distinct: 去重(整個重複): 默認的

場景：

  1. 從多個表中查詢出類似結構的數據，而且返回一個結果集

  2. 從單個表中屢次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不去重

五、子查詢：括號裏sql的結果，是括號外sql的where條件。從裏到外依次執行（連表比子查詢效率高）

語法：.帶in關鍵字查詢：select 字段1，字段2 frome 表名 where 字段 [not]in(元素1，元素2)；

  • 按位置分類: 子查詢(select語句)在外部查詢(select語句)中出現的位置

  1. From子查詢: 子查詢跟在from以後

SELECT people.name,people.chinese,people.math,people.english FROM (SELECT name,chinese,math,english FROM tb_demo071) AS people

  1. 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);    //查學生表裏有成績的

  1. Exists子查詢:

子查詢查詢到記錄（存在），則進行外層查詢，不然，不執行外層查詢。還有個not exists

-- EXISTS:若是存在身高大於2米的人，列出表中全部數據。不然 不輸出。由於exists返回的是bool值
select * from student where EXISTS (select * from student where height >= '200')

 

  • 按結果分類: 根據子查詢獲得的數據進行分類(理論上講任何一個查詢獲得的結果均可以理解爲二維表)

  1. 標量子查詢: 子查詢獲得的結果是一行一行的數據

  2. 列子查詢: 子查詢獲得的結果是一列多行

-- 列子查詢：找出語文和數學都及格人的名字（只輸出名字，因此叫一列多行）
-- 換種思路，先找出小於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以後)

注意：括號裏的不能有；不然會報語法錯誤，由於系統認爲到score desc這就結束了。這要最外層的select就會觸發語法錯誤

-- 表子查詢：找出每一個人最好的成績
-- 思路：先以人分類，相同名字的湊成一組，在根據分數分組後倒序，這樣張三的最高分就在上面了-》在拿名字分組，此時相同名字他取第一個
select * from (select * from score GROUP BY name,score desc) as a GROUP BY name

 

例子1：any 或 some
any 或 some關鍵字是同義詞，表示知足其中任何一個條件便可，它們容許建立一個表達式對子查詢的返回值列表進行比較，只要知足內層子查詢中的任何一個比較條件，就返回一個結果做爲外層查詢的條件

 

例子四、帶any關鍵字的子查詢（any關鍵字表示知足其中任一條件）

select * from t_book where price>= any(select price from t_priceLevel);

例子五、帶all關鍵字的子查詢（all關鍵字表示知足全部條件）

select * from t_book where price>= all(select price from t_priceLevel);

六、limit 從第幾個開始取，取多少條

七、帶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 '張三_''

八、null查詢：select 字段1，字段2...frome 表名 where 字段  is[not] null;   //null比較特殊，詳情點擊查看

-- 統計id字段爲null的條數
select count(1) from bi_contract where id is null;

九、帶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 <= ''   //或用 >= <=

十、HAVING 能夠單獨使用

-- HAVING 要放在最下面，緣由1：having是對查出來的結果在篩選，而where是查以前進行篩選；
-- 緣由2：having支持經過字段別名（mini_contract就是別名），進行篩選，where只支持原來的字段名
HAVING 1 = 1 

十一、正則替換字符串

-- yearquarter中的夏替換成Q2
update bi_datedict_time SET yearquarter=replace(yearquarter,"夏",'Q2') where yearquarter REGEXP '夏$'

十二、字段爲null的更新爲0

-- 給class_id是null的，更新成0
update bi_contract_product set class_id = ifnull(class_id,0)

1三、

注意：「!=」左右不能有空格，不然報語法錯誤

select INSERT(name,1,4,'abcd') as new from xg_test where id=3;	-- 字符串類型 替換 前4位
select insert(SUBSTRING(name, -4),1,4,'efgh') as new from xg_test where id=3;	-- 字符串類型 替換 後4位
select SUBSTRING(name, -4) as new from xg_test where id=3;	-- 截取後4位
SELECT name FROM bi_student_copy WHERE length(name)!=char_length(name); -- 輸出某個字段全是漢字的，使用前提是當字符集爲UTF-8，而且字符爲中文時。
update bi_student set NAME = ifnull(NAME,'');	-- null替換成''
update bi_student set name=trim(NAME);		-- 去掉空格
update bi_customer_copy set name=concat('ms',name);	-- 加前綴,原理ms和name拼接在一塊兒

1四、更新時where條件in中寫select

-- 批量更新之in中帶select
update bi_customer_copy set name='楊西瓜' where id in(
	select * from(					-- 若是沒外面這個select會報錯
		SELECT
			id
		FROM
			bi_customer_copy
		WHERE
			-- NAME='楊西瓜'
			NAME LIKE '%,%'
			OR
			NAME LIKE '%?%'
			OR
			NAME LIKE '%<%'
			OR
			NAME LIKE '%。%'
			OR
			NAME LIKE '%、%'
			OR
			NAME LIKE '%·%'
			OR
			NAME LIKE '%/%'
			OR
			NAME LIKE '%\'%'	-- 想檢測單引
			OR
			NAME LIKE '%（%'
	) as tmp
);

1四、經過當前等級 查 上級

SELECT
 		o.id as o_id,
		o.NAME AS o_name,		-- 當前名稱
 		o.parentID o_pid,
		oo.name o_pname			-- 上級名稱
	FROM organization AS o 
	left join organization oo on o.parentID=oo.id		-- 全部的上級都在oo中（從當前等級 找 上級）	
	where o.institution_id=2 and o.name not like '%停用%'

1五、查詢表時，建立（生成）一個自增字段

set @rownum=0;    -- 定義變量
SELECT
  @rownum:=@rownum+1 AS id, -- 生成 自增字段（表自己沒有的字段）
  name    -- 表自己就有的字段
FROM a    -- 表名
limit 10; -- 限制表只輸出10條數據

 

 

DCL語言

 

受權

注意：和權限相關的，每次作完都要刷新權限 flush privileges;

 

一、建立用戶

create user '用戶名' @'指定ip訪問' identified by '密碼';

create user 'test' @'127.0.0.1' identified by '123456';

二、給新用戶賦予權限

grant 權限（增刪改查關鍵字） on 數據庫名.* to '用戶名' @'指定ip訪問';

grant update,insert,delete,select on test.* to 'test' @'127.0.0.1';

三、撤銷權限

revoke 權限 on 數據庫名.* from '用戶名' @'指定訪問ip';

revoke select on test.* from 'test' @'127.0.0.1';

注意：撤銷後刷新完權限後，要先quit退出在從新登陸才生效

四、給全部的數據庫用*.*，給全部的權限用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 後面的是登陸用的密碼，能夠省略，即缺省密碼或者叫空密碼。

五、查看權限

show grants for '用戶名'@'指定訪問ip'\G;

show grants for 'test'@'127.0.0.1'\G;

六、刪除用戶

drop user '用戶名'@'指定訪問ip';  //注意：要和表中的對應上

drop user 'test'@'127.0.0.1';

 

DTL事務控制語句

 

start transaction;    //開啓事物

執行sql....    

commit;    //沒問題，提交

rollback;    //有問題，回滾

 

存儲過程

 

一、定義存儲過程

mysql> create procedure test(a int)
    -> begin
    -> select * from tp5_user where id=a;
    -> end///
Query OK, 0 rows affected (0.01 sec)

二、調用存儲過程（使用call關鍵字調用 存儲過程的名字）

call test(1)///

三、刪除存儲過程

drop procedure test///

四、查看存儲過程

show procedure status///

 

視圖

總結：至關因而as後面得sql結果存到了test_view表中（show tables;就能看出來），而後使用視圖的時候查test_view表

一、建立

create view 視圖名(視圖名中定義的字段別名) as SQL語句;
mysql> create view test_view(view_name,view_fee) as select name,fee from tp5_user where id=6;

二、使用

select 視圖名中定義的字段別名 from 視圖名;

select view_name from test_view;

 

三、查看

show create view 視圖名\G;

show create view test_view\G;

四、刪除

drop view 視圖名;

drop view test_view;

觸發器

總結：至關於php框架中的鉤子

一、建立

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///

二、查看

show create trigger 觸發器名稱\G;

show create trigger delete_total_num\G;

三、刪除

 drop trigger 觸發器名稱;

 drop trigger delete_total_num;

 

其餘

一、select version(),current_date;    //顯示當前mysql版本和當前日期

二、SELECT User, Host, Password FROM mysql.user;    //在Mysql中顯示全部用戶（mysql庫中的user表）
三、SELECT DISTINCT User FROM mysql.user;  //顯示去重後的用戶（其實只是加了個關鍵字修飾sql語句）
注意：在Mysql中其實有一個內置且名爲mysql的數據庫，這個數據庫中存儲的是Mysql的一些數據，好比用戶（user）、權限信息、存儲過程等，因此呢，咱們能夠經過以下簡單的查詢語句來顯示全部的用戶呢
四、 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=utf8 數據庫名 > 導出的
文件名(數據庫默認編碼是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.一、先登陸後-》導入數據庫
mysql -u root -p    //進入mysql數據庫控制檯
mysql>use 數據庫    //選擇數據庫
mysql>source wcnc_db.sql    //使用source命令，後面參數爲腳本文件(如這裏用到的.sql)
4.二、不登陸-》使用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);						-- 調用
联系我们 沪ICP备2021010478号-7