MYSQL基本動作陳述式

標籤：

0、修改密碼:mysqladmin -u root -p password 123456

     匯出資料庫：mysqldump -u root -p yunpay>yunpay.sql

     匯入資料庫：mysql –u root -p yunpay < yunpay.sql

1、為mysql增加一個名為admin，主機名稱任意的網路使用者，其通過密碼‘123‘訪問資料庫，這個使用者擁有對資料庫的所有操作許可權（ALL PRIVILEGES）

CREATE USER ‘admin‘@‘%‘ IDENTIFIED BY ‘123‘;GRANT ALL PRIVILEGES ON * . * TO ‘admin‘@‘%‘ IDENTIFIED BY ‘123‘ WITH GRANT OPTION;

 2、刪除該使用者

DROP USER ‘wbhuang‘@‘%‘;

3、建立資料庫

CREATE DATABASE school;USE school;SHOW TABLES;

 4、刪除、建立資料表

刪除資料表時，有如下文法

DROP TABLE <表名> [RESTRICT | CASCADE];

當選擇RESTRICT：則該表的刪除是有限制條件的。欲刪除的基本表不能被其他表的約束所引用（如CHECK，FOREIGN KEY等約束），不能有視圖，觸發器，預存程序和函數，否則不能刪除。如果選擇CASCADE：則刪除基本表的同事，相關的依賴對象，例如視圖，都將被一起刪除。下面語句的"SET FOREGIN_KEY_CHECKS = 0;"為取消CHECKS依賴。可見MYSQL預設的刪除表方式為RESTRICT（受約束的）。

SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS Student;DROP TABLE IF EXISTS Course;DROP TABLE IF EXISTS SC; CREATE TABLE Student     (Sno CHAR(9) PRIMARY KEY,     Sname CHAR(20) UNIQUE,         Ssex CHAR(2),         Sage SMALLINT,         Sdept CHAR(20)        );         CREATE TABLE Course    (Cno CHAR(4) PRIMARY KEY,         Cname CHAR(40),         Cpno CHAR(4),/*先修課*/         Ccredit SMALLINT,         FOREIGN KEY (Cpno) REFERENCES Course(Cno)         /*表級完整性條件約束條件，Cpno是外碼，被參照表是Course，被參照列是Cno*/         ); CREATE TABLE SC    (Sno CHAR(9),         Cno CHAR(4),         Grade SMALLINT,         PRIMARY KEY (Sno,Cno),         /*主碼由兩個屬性構成，必須作為表級完整性進行定義*/         FOREIGN KEY (Sno) REFERENCES Student(Sno),         FOREIGN KEY (Cno) REFERENCES Course(Cno)        );

 5、修改資料表

ALTER TABLE Student DROP COLUMN Sentry;ALTER TABLE Student ADD Sentrance DATE;ALTER TABLE Student CHANGE Sentrance Sentry DATE;/*為Student增加"入學時間"列*/ ALTER TABLE Student MODIFY COLUMN Sage INT;/*將年齡的資料類型由字元型改為整形*/ ALTER TABLE Course ADD UNIQUE(Cname);/*增加課程名稱必須取唯一值的約束條件*/

 6、刪除、建立索引表

DROP INDEX Stusno ON Student;DROP INDEX Coucno ON Course;DROP INDEX SCno ON SC; CREATE UNIQUE INDEX Stusno ON Student(Sno);/*按課程號升序建唯一索引*/CREATE UNIQUE INDEX Coucno ON Course(Cno);/*SC表按學號升序和課程號降序建唯一索引*/CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);<br>SELECT * FROM INDEX Stusno;

7、插入資料

INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES(‘20071025‘,‘wbhuang‘,‘male‘,23,‘math‘,‘2007-09-01‘);INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES(‘20071026‘,‘dkluo‘,‘male‘,24,‘math‘,‘2007-09-01‘);INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES(‘20071005‘,‘hlyang‘,‘male‘,24,‘math‘,‘2007-09-01‘);INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES(‘20071007‘,‘ljhu‘,‘male‘,24,‘math‘,‘2007-09-01‘);INSERT INTO Student (Sno,Sname,Ssex,Sage,Sdept,Sentry) VALUES(‘20071024‘,‘yluo‘,‘male‘,24,‘math‘,‘2007-09-01‘);

INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES(‘100‘,‘Chinese‘,‘100‘,4);INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES(‘101‘,‘English‘,‘100‘,3);INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES(‘102‘,‘Science‘,‘100‘,2);INSERT INTO Course (Cno,Cname,Cpno,Ccredit) VALUES(‘103‘,‘Math‘,‘100‘,5);

  8、修改、刪除資料

UPDATE  `school`.`student` SET  `Sname` =  ‘wbhuang‘,`Sdept` =  ‘xinji‘ WHERE  `student`.`Sno` =  ‘20071025‘;DELETE FROM Student WHERE Sno=‘20071025‘;

 9、普通查詢和聚集合函式

SELECT Sno,Sname,Ssex FROM Student WHERE Sno=‘20071004‘;SELECT COUNT(DISTINCT Sname) FROM Student;SELECT AVG(Sage) FROM Student;SELECT Cno FROM Student ORDER BY Sage DESC; ORDER BY <列名|,列名> [ASC | DESC];       ASC升序，DESC降序COUNT([DISTINCT | ALL] * )          統計元素個數COUNT([DISTINCT | ALL] <列名> ) 統計一列中元素個數SUM([DISTINCT | ALL] <列名> )       計算一列值的總和（數值型）AVG([DISTINCT | ALL] <列名> )       計算一列值的平均值（數值型）MAX([DISTINCT | ALL] <列名> )       計算一列值的最大值MIX([DISTINCT | ALL] <列名> )       計算一列值的最小值 GROUP BY 子句將查詢結果按某一列或多列的值分組，值相等的為一組。SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;/*Cno值相等的為一組，計算各組的COUNT(Sno)*/

 10、串連查詢

/*等值，非等值串連：比較的串連謂詞有=、<、>、>=、<=、!=(或<>)等*/SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno;/*若在等值串連中把目標列的重複的屬性去掉則為自然串連*/SELECT Student.*,SC.Cno,SC.Grade FROM Student,SC WHERE Student.Sno=SC.Sno;/*自身串連：一個表與自己進行串連*/SELECT FIRST.*,SECOND.* FROM Course FIRST, Course SECOND WHERE FIRST.Cpno=SECOND.Cno;/*外串連：若某個Student沒有選課，仍把捨棄的Student元組儲存在結果中，其SC的屬性全填NULL*/SELECT * FROM Student LEFT JOIN SC ON (Student.Sno=SC.Sno);

 11、巢狀查詢

/*去掉Student.Cname的UNIQUE*/ALTER TABLE Student DROP INDEX Sname; /*帶有比較子的子查詢*/SELECT Sno,Sname,Sdept FROM Student WHERE Sdept = (    SELECT Sdept    FROM Student    WHERE Sno=‘20071004‘);                         SELECT Sno,Sname,Sdept FROM Student WHERE Sno IN (    SELECT Sno    FROM SC    WHERE Grade>=5);                         /*帶有ANY(SOME)或ALL謂詞的子查詢*/                          SELECT Sno,Sname,Sage,SsexFROM StudentWHERE Sage>ANY (    SELECT Sage    FROM Student    WHERE Ssex=‘fe‘); /*帶有[NOT] EXISTS謂詞的子查詢*//*EXISTS謂詞的子查詢不反悔任何資料，只產生邏輯真與假*//*拿外層的元組逐個放在內層中判斷是否EXIST，如果為真則將元組放入結果集*/SELECT Sname FROM Student WHERE EXISTS (    SELECT *    FROM SC    WHERE Sno=Student.Sno     AND Cno=‘100‘);                             /*集合查詢：UNION並集，INTERSECT交集，EXCEPT差集*/SELECT Sno FROM StudentWHERE Sdept=‘hwx‘UNIONSELECT *FROM StudentWHERE Sage>=20;

 12、視圖操作

/*建立視圖*/CREATE VIEW V_StudentASSELECT Sno,Sname,SageFROM StudentWHERE Sdept=‘xj‘; /*刪除視圖*/DROP VIEW V_Student; /*查詢檢視*/SELECT Sno,SnameFROM V_StudentWHERE Sage>50; /*更新視圖*/UPDATE V_StudentSET Sname=‘vname‘WHERE Sno=‘20071089‘; INSERT INTO V_StudentVALUES (‘20081010‘,‘vnew‘,36);

 

MYSQL基本動作陳述式