mysql------基礎(chǔ)及常見(jiàn)SQL技巧

時(shí)間：2019-01-24 14:24:01

關(guān)鍵字： mysql sql

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]基礎(chǔ) 1.1 mysql表復(fù)制復(fù)制表結(jié)構(gòu)+復(fù)制表數(shù)據(jù) mysql>create table t3 like t1; mysql>insert into t3 select * from t1

基礎(chǔ) 1.1 mysql表復(fù)制

復(fù)制表結(jié)構(gòu)+復(fù)制表數(shù)據(jù)
mysql>create table t3 like t1;
mysql>insert into t3 select * from t1;

1.2 mysql 索引 1.ALTER TABLE用來(lái)創(chuàng)建普通索引，UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEX index_name(column_list)
ALTER TABLE table_name ADD UNIQUE(column_list)
ALTER TABLE table_name ADD PRIMARY KEY(column_list)

2.create index

CREATE INDEX index_name ON table_name(column_list)
CREATE UNIQUE INDEX index_name ON table_name(column_list)

3.drop index

DROP INDEX index_name ON table_name

4.alter table table drop

ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

1.3mysql視圖

mysql>create view v_t1 as select * from t1 where id>4 and id<11;
mysql> ? view   //view視圖的幫助信息
ALTER VIEW
CREATE VIEW
DROP VIEW
mysql>show tables;  //查看視圖
mysql>drop view v_t1;   //刪除視圖v_t1

1.4mysql內(nèi)置函數(shù) 字符串函數(shù)：

CONCAT(string2[,...])   //連接字符串
LCASE(string2)  //轉(zhuǎn)換成小寫(xiě)
UCASE(string2)  //轉(zhuǎn)換成大寫(xiě)
LENGTH(string)  //string長(zhǎng)度
LTRIM(string2)  //去除前端空格
RTRIM(string2)  //去除后端空格
REPEAT(string2,count)   //重復(fù)count次
REPLACE(str,search_str,replace_str) //在str中用replace_str替換search_str
SUBSTRING(str,position[,length])    //從str的position開(kāi)始，取length個(gè)字符
SPACE(count)    //生成count個(gè)空格

數(shù)學(xué)函數(shù)：

BIN(decimal_number) //十進(jìn)制轉(zhuǎn)二進(jìn)制
CEILING(number2)    //向上取整
FLOOR(number2)  //向下取整
MAX(col)    //取最大值,聚合時(shí)使用
MIN(col)    //取最小值,聚合時(shí)使用
SORT(number2)   //開(kāi)平方
RAND()  //返回0-1內(nèi)的隨機(jī)值

日期函數(shù)：

CURDATE()   //返回當(dāng)前日期
CURTIME()   //返回當(dāng)前時(shí)間
NOW()   //返回當(dāng)前的日期時(shí)間
UNIX_TIMESTAMP(date)    //返回當(dāng)前date的UNIX時(shí)間戳
FROM_UNIXTIME() //返回UNIX時(shí)間戳的日期值
WEEK(date)  //返回日期date為一年中的第幾周
YEAR(date)  //返回日期date的年份
DATEDIFF(expr,expr2)    //返回起始時(shí)間expr和結(jié)束時(shí)間expr2間天數(shù)

1.5mysql預(yù)處理語(yǔ)句

設(shè)置stmt1預(yù)處理，傳遞一個(gè)數(shù)據(jù)作為一個(gè)where判斷條件
mysql>prepare stmt1 from 'select * from t1 where id>?';
mysql>set @i=1; //設(shè)置一個(gè)變量
mysql>execute stmt1 using @i;   //執(zhí)行stmt1預(yù)處理
mysql>set@i=5;  //設(shè)置@i為5
mysql>execute stmt1 using @i;   //再次去執(zhí)行stmt1
mysql>drop prepare stmt1:   //如何刪除預(yù)處理stmt1

1.6mysql事務(wù)處理

mysql>set autocommit=0; //關(guān)閉自動(dòng)提交功能
mysql>delete from t1 where id=11;   //從表t1中刪除了一條記錄
mysql>savepoint p1; //此時(shí)做一個(gè)p1還原點(diǎn)
mysql>delete from t1 where id=10;   //再次從表t1中刪除一條記錄
mysql>savepoint p2; //再次做一個(gè)p2還原點(diǎn)
mysql>rollback to p1;   //此時(shí)恢復(fù)到p1還原點(diǎn)，即p2自動(dòng)失效
mysql>rollback; //退回到最原始的還原點(diǎn)

1.7mysql存儲(chǔ) 創(chuàng)建一個(gè)存儲(chǔ)p1();
mysql>d //改變語(yǔ)句界定符
mysql>create procedure p1()
->begin
->set @i=0;
->while @i<10 do
->select @i;
->set @i=@i+1;
->end while;
->end;
->//
mysql>d;
mysql>call p1(); //執(zhí)行存儲(chǔ)p1();
mysql>show procedurestatusG //查看procedure p1()的status信息
mysql>show create procedure p1G //查看procedure p1()的具體信息 1.8mysql觸發(fā)器 (用于增刪改)

創(chuàng)建一個(gè)名字為tg1的觸發(fā)器，當(dāng)向t1表中插入數(shù)據(jù)時(shí)，就向t2表中插入一條數(shù)據(jù)

修改delimiter為//
mysql>d //
mysql>create trigger tg1 before insert on t1 for each row
>begin
>insert into t2(id) values(new.id);
>end//
準(zhǔn)備兩個(gè)空表t1和t2
mysql>select * from t1;
mysql>select * from t2;
向t1表中插入多條數(shù)據(jù)：
mysql>insert into t1 values(1),(2),(3),(4);

mysql>create trigger t2 before delete on t1 for each row
-> begin
-> delete from t2 where id=old.id;
-> end//

如何制作刪除表t1后t2表中的記錄也會(huì)跟著刪除呢？
mysql>create trigger tg3 before delete on t1 for each row
>begin delete from t2 where id=old.id;
>end//

如何制作更改表t1后t2表也跟著修改呢？
mysql>create trigger tg4 before update on t1 for each row
>begin update t2 set id=new.id where id=old.id;
>end//
改回語(yǔ)句界定符為分號(hào)
mysql>d;
查看觸發(fā)器：
mysql>show triggers;

1.9重排auto_increment值

MYSQL數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)的ID如何恢復(fù)
清空表的時(shí)候，不能用delete from tablename;
而是要用：truncate table tablename;
這樣auto_increment就恢復(fù)成1了
或者
清空內(nèi)容后直接ALTER命令修改表：
alter table tablename auto_increment=1;

常見(jiàn)SQL技巧 2.1 正則表達(dá)式的使用（？ regexp）

使用正則表達(dá)式“$”和“[…]”進(jìn)行匹配：

mysql>select name,email from t where email REGEXP “@163[.,]com$”

使用like方式查詢：
mysql>select name,email from t where email like “%@163.com” or email like “%@163,com”

2.2 巧用RAND()提取隨機(jī)行

MYSQL數(shù)據(jù)庫(kù)中有一個(gè)隨機(jī)函數(shù)rand()是獲取一個(gè)0-1之間的數(shù)，利用這個(gè)函數(shù)一起和order by能夠把數(shù)據(jù)隨機(jī)排序。

mysql>select * from stu order by rand();

下面是通過(guò)limit隨機(jī)抽取了3條數(shù)據(jù)樣本

mysql>select * from stu order by rand() limit3;

2.3 利用GROUP BY的WITH ROLLUP子句統(tǒng)計(jì) 使用group by 的 with rollup子句可以檢索出更多的分組聚合信息
對(duì)demo表按照cname、pname列分組對(duì)pname列進(jìn)行聚合計(jì)算如下
同樣使用with rollup 關(guān)鍵字后，統(tǒng)計(jì)出更多的信息，如下（注意：with rollup不可以和order by同時(shí)使用）
2.4 利用BIT GROUP FUNCTIONS做統(tǒng)計(jì) 在使用group by語(yǔ)句時(shí)可以同時(shí)用使用bit_and、bit_or函數(shù)來(lái)完成統(tǒng)計(jì)工作。這兩個(gè)函數(shù)的作用主要是做數(shù)值之間的邏輯位運(yùn)算
mysql>select id,bit_or(kind) from order_rab group by id; 對(duì)order_rab表中id分組時(shí)對(duì)kind做位與和或計(jì)算。
mysql>select id,bit_and(kind) from order_rab group by id; 2.5 使用外鍵需要注意的問(wèn)題

創(chuàng)建外鍵的方式如下
mysql>create table temp(id int,name char(20),foreign key(id) references outTable(id) on delete cascade on update cascade);
注意：Innodb類型的表支持外鍵，myisam類型的表，雖然創(chuàng)建外鍵可以成功，但是不起作用，主要原因是不支持外鍵。

2.6 mysql中help的使用在mysql中那么多的命令如何才能記得住是個(gè)問(wèn)題，這里有一個(gè)特別好的獲得幫助的好方法，當(dāng)然是在mysql》的提示下的操作：

？ %
可以獲得所有的mysql>里的命令，這個(gè)是最多的，那么這里的東西如何去進(jìn)一步獲得幫助呢？

？create

? opti%
因?yàn)橛洸蛔ptimize的全稱，這個(gè)時(shí)候可以用%來(lái)替代

? reg%
獲得了記不住了的regexp用法

查看所有用？ contents可以得到所有的幫助大綱，通過(guò)這個(gè)目錄再用？繼續(xù)往下細(xì)查。