mysql的複習與進階

標籤：des   使用   os   io   檔案   資料   for   art   

--  mysql的複習與進階
--  建立一個結構完整的表
create table tab1(
id int(5) not null auto_increment primary key comment "這是ID",
name varchar(20) not null default "ran",
age int(2) not null
);
--  在id不能加default




--  複製表
create  table tab2 select * from tab1;


--  只複製結構，不複製內榮 
create  table tab2 like tab1; 


--  增加欄位
alter table tab1 add sex varchar(2) not null;


--  刪除欄位
alter table tab1 drop sex;


--  修改欄位
alter table tab1 change name myname varchar(20) not null;


--  增加主鍵
alter table tab1 add primary key (id);


--  修改表明
alter table tab1 rename to tab11;


--  插入資料
insert into tab1 values(null,"zhangran","23");


--  查詢資料
--  limit 
select * from tab1 limit 1;


--  從第一個開始顯示，顯示一個
select * from tab1 limit 1,1;


--  排序
--order by asc|desc
select * from tab1 order by name asc;


--  更新資料
update tab1 set name="zhangranran",age="111" where name="zhangran" [order by][limit]


--  刪除資料
delete from tab1 where name="zhangran";
--  此處要注意一點，truncated也可以當delete使用，而且比delete要快，是一種資料定義語言 (Data Definition Language)，但是不能擺正資料的安全性
--  truncated之後的自增長是重新開始的。


--  串連
--  交叉串連
select * from tab1,tab2;
--  所有的都能查出來，冗餘的部分特別多


--  內串連
select tab1.name,tab2.myname from tab1 join tab2 on tab1.id=tab2.id;
--  加一個別名select * from tab1 a join tab2 b where tab1.id=tab2.id;




--  左串連跟右串連此處略了啊


--  自串連
--  需要加一個別名
select a.name,b.name from tab1 a,tab1 b where a.id=b.id;


--  子查詢
select * from tab1 where name=(select name from tab2 where name="zhangran");


--  子查詢做派生表
select * e.name from (select name from tab2) as e;


--  查詢中的IN應用
select * from tab1 where name in(select name from tab2);


--  exists的使用,此時子查詢作為條件，如果有傳回值說明是真，無傳回值是假
select * from tab1 where exists (select name from tab1 where id=1);


--  交易處理
--  把幾條語句作為一個整體
--  每一個都執行成功了才算是事務的成功處理
--  否則返回最初狀態
start transaction 
insert into tab1 values(null,"zhangdayi",22);
update tab1 set age=11 where name="zhangdayi";
commit




--  想要撤銷這個事務
rollback




--  MySQL管理
--  資料備份
--  使用者權限
--  存取控制
--  效能最佳化




--  資料備份
--  備份某個資料庫的一個表
mysqldump -u root -p111111 wangluo tab1 >D:/mysql.sql


--  備份某個資料庫裡面的所有的表
mysqldump -u root -p111111 wangluo >D:/mysql.sql


--  一次性備份多個資料庫
mysqldump -u root -p111111 --database db1 db2 >D:/mysql1.sql
--  需要特別注意的是database是單數而且它的前面是雙橫杠


--  將資料庫中的所有的表備份

mysqldump -u root -p111111 --all --database >D:/mysql2.sql

 

-- 將SQL檔案匯入到資料庫

source d:/aaa/bbb/ccc.sql;

--  使用者的許可權及存取控制
--  簡單的舉兩個例子
grant all privileges on *.* to ‘newuser‘ identified by ‘111111‘ with grant option;



grant update,select on *.* to ‘newuser‘ identified by ‘111111‘;


--  效能最佳化
--  加索引，在查詢操作時最佳化
--  最佳化資料庫服務
mysql -verbose -help
--  記住，該指令不是在mysql環境下輸入的是在cmd下輸入的


--  MySQL的基礎知識大概就這麼多，關鍵是勤練，多琢磨，以後記得常回過頭來看看！！！！！！！！！！！！！！！！！！！！

 

 

 

--  MySQL的預存程序
--  declare生命局部變數
delimiter //
CREATE PROCEDURE pro(IN myid INT);
BEGIN 
DECLARE name VARCHAR(20);
SELECT id INTO myid FROM tab1 WHERE name="zhangran";
SELECT myid;
END
// 


CALL pro(1);
//




--  用set命令賦值
delimiter //
CREATE PROCEDURE pro(IN num1 INT,IN num2 INT);
DECLARE num INT;
SET num=num1+num2;
SELECT num;
END
// 


--  流程式控制制語句 IF
delimiter //
CREATE PROCEDURE pro(IN type VARCHAR(20));
IF type="a" THEN SELECT "a等級";
ELSEIF type="b" THEN SELECT "b等級";
ELSE SELECT "c等級";
END IF;
END
//



--  流程式控制制語句 CASE
delimiter //
CREATE PROCEDURE pro(IN num INT);
BEGIN 
CASE num
WHEN 1 THEN SELECT "1等級";
WHEN 2 THEN SELECT "2等級";
WHEN 3 THEN SELECT "3等級";
END CASE;
END
//


 
--  迴圈語句之LOOP
delimiter //
CREATE PROCEDURE pro(IN num INT);
BEGIN 
lable : LOOP
SET num=num+1;
IF num>10 THEN LEAVE lable;
END IF;
END LOOP;
SET @a=num;
END
//


CALL pro(2);
//
SELECT @a;
//


--  迴圈之WHILE
delimiter //
CREATE PROCEDURE pro();
BEGIN 
DECLARE num INT DEFAULT 0;
WHILE num<10 DO;
SET　num=num+1;
END WHILE;
SELECT i;
END
//




--  迴圈之REPEAT
--  類似於do while迴圈，無論如何都是先執行一步的
delimiter //
CREATE PROCEDURE pro();
BEGIN 
SET @num=0;
REPEAT @[email protected]+1 UNTIL @num>10;
END REPEAT;
END
//


--  查看預存程序的相關資訊
--  查看建立文法資訊
show create procedure pro\G


--查看預存程序的特徵
show procedure status;




--  修改預存程序 用alter，這一部分掌握的不太全面！！！！！！！！！！！！！
alter procedure mypro
sql security invoker


--  刪除預存程序 用drop
DROP PROCEDURE IF EXISTS pro;


--  觸發器-------------------
--  MySQL5 開始計入了觸發器技術
--  觸發器無參數
--  同一個動作不能觸發兩次
delimiter //
CREATE TRIGGER myter
BEFORE INSERT
ON tab1
FOR EACH ROW
BEGIN
INSERT INTO tab2 SET name=NEW.name;
UPDATE tab3 SET name="newname" WHERE name=NEW.name;
END
//
--  上面的觸發器經過驗證無任何錯誤！！！


--  查看觸發器
show triggers\G


--  刪除觸發器
drop trigger myter;


--  另外當前尚無法修改觸發器，故只能刪除重建的方式來更新




--  遊標--------------
--  遊標部分比較複雜，而且並不常用，以後再逐漸補充吧






--  視圖---
--  視圖是根據已經存在的表，經過一定的篩選所組成的，這樣可以有選擇的讓使用者看到或是修改部分資料不至於讓資料完全曝光
--  示範執行個體
create table user(id int not null,name varchar(20) not null,address varchar(50) not null,age int not null,tel varchar(11) not null);
--  該表中的個人資訊，名稱，住址可以公開，但是年齡跟電話不願意公開，可以建立一個視圖
create view myview as select name,address from user;
select * from myview;
--  這樣就達到了目的


--  查看視圖的建立文法
show create view myview\G


-- 刪除視圖
drop view myview;


--  差不多就這些吧，以後會慢慢補充的，還是記得有時間多回過頭來看看