Mysql常見動作陳述式

標籤：

一、資料完整性
    1、實體完整性
    2、值域完整性（列完整性條件約束）：
        指資料庫表的列（即欄位）必須符合某種特定的資料類型或約束。
        
        約束：
            類型約束：  id int
            長度約束：    id int(3)      
            非空約束：    username varchar(10) NOT NULL 必須有值
            唯一約束：    idcardnum varchar(18) UNIQUE 可以為null，有的話必須唯一
            
            使用者名稱：一般網站使用者名稱必須有，且唯一（不讓他作為主鍵）
                username varchar(100) NOT NULL UNIQUE
            
        
        樣本：
            CREATE TABLE user(
                id int PRIMARY KEY,
                username varchar(20) NOT NULL UNIQUE,
                idcardnum varchar(18) UNIQUE,
                gender varchar(10) NOT NULL
            );
            
        專題：整數類型的主鍵自動成長。由資料庫自己自動插入主鍵的值
            CREATE TABLE t1(
                id int PRIMARY KEY auto_increment,#主鍵自動成長
                name varchar(100)
            );
            INSERT INTO t1 (id,name) VALUES(1,‘A‘);
            INSERT INTO t1 (name) VALUES(‘B‘);  (推薦)
            INSERT INTO t1 VALUES (null,‘C‘);
            
        建議：盡量不要讓資料庫維護主鍵。（不是所有的資料庫都有自動成長這個功能）
        由應用來維護主鍵
            
    3、參照完整性（重點：多表.外鍵）
        3.1多表的設計
            a、一對多（出現頻率最高）
                CREATE TABLE department(
                    id int PRIMARY KEY,
                    name varchar(100),
                    addr varchar(100)
                );
                CREATE TABLE employee(
                    id int PRIMARY KEY,
                    name varchar(100),
                    gender varchar(10),
                    salary float(8,2),
                    depart_id int,
                    CONSTRAINT depart_id FOREIGN KEY(depart_id) REFERENCES department(id)
                );
                
                外鍵定義文法：
                CONSTRAINT 外鍵名稱 FOREIGN KEY(外鍵欄位) REFERENCES 主表名稱(主鍵欄位);
                外鍵名稱：隨便定義，當前庫中必須唯一
                外鍵欄位：當前表中那個欄位是外鍵

　　

                
            b、多對多（出現頻率其次）
                CREATE TABLE teacher(
                    id int PRIMARY KEY,
                    name varchar(100),
                    salary float(8,2)
                );
                CREATE TABLE student(
                    id int PRIMARY KEY,
                    name varchar(100),
                    grade varchar(10)
                );
                #定義關係表
                CREATE TABLE teacher_student(
                    t_id int,
                    s_id int,
                    PRIMARY KEY(t_id,s_id),
                    CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
                    CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id)
                );
                
                INSERT INTO teacher VALUES(1,‘WYJ‘,10000);
                INSERT INTO teacher VALUES(2,‘DH‘,10001);
                INSERT INTO student VALUES(1,‘WF‘,‘A‘);
                INSERT INTO student VALUES(2,‘QHS‘,‘A‘);
                INSERT INTO teacher_student VALUES(1,1);
                INSERT INTO teacher_student VALUES(1,2);
                INSERT INTO teacher_student VALUES(2,1);
                INSERT INTO teacher_student VALUES(2,2);

 

            c、一對一（出現頻率幾乎沒有）
                CREATE TABLE person(
                    id int PRIMARY KEY,
                    name varchar(100)
                );
                CREATE TABLE id_card(
                    id int PRIMARY KEY,
                    num varchar(18),
                    CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES person(id)
                );

二、多表的查詢
    
    1、串連查詢
        基本文法：select XXX from t1 連線類型 t2 [on 串連條件][where 篩選條件]
            約定：t1在連線類型的左邊，稱之為左表 t2就是右表
                  連線類型：
                    cross join:交叉串連
                    inner join:顯式內串連
                    
                    left outer join:左外串連
                    right outer join:右外串連
            
        1.1交叉串連(瞭解)：cross join
            SELECT * FROM customer CROSS JOIN orders;
            返回的結果是：返回的是兩張表結果的笛卡爾積。即表1有5條，表2有7條，返回的5*7=35條
        1.2內串連：inner join（自然串連）
            a、隱式內串連：不使用on關鍵字（即不明確指定串連條件），使用的是where
                查詢有訂單的客戶的資訊和訂單資訊
                SELECT * FROM customer c,orders AS o WHERE c.id=o.customer_id;
            b、顯式內串連：使用on關鍵字
                查詢有訂單的客戶的資訊和訂單資訊
                SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id;
                查詢訂單金額在200元以上的客戶的資訊和訂單資訊
                SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id WHERE o.price>=200;
        1.3外串連：outer join
            a、左外串連:返回滿足串連條件的結果，同時返回左表中剩餘的其他記錄
                查詢客戶資訊，同時顯示他的訂單
                SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;
                查詢所有的員工，列印所在的部門名稱
                SELECT * FROM employee e LEFT OUTER JOIN department d ON e.depart_id=d.id;
            b、右外串連:返回滿足串連條件的結果，同時返回右表中剩餘的其他記錄
                查詢客戶資訊，同時顯示他的訂單
                SELECT * FROM orders o RIGHT OUTER JOIN customer c ON c.id=o.customer_id;
                查詢所有訂單，同時顯示他的客戶資訊
                SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id;
    2、子查詢（簡單）：
        子查詢：巢狀查詢（內部語句）。一個查詢語句是另外一個查詢語句的條件。子查詢的語句必須放在小括弧之內
            select * from t1 where id=(select id from t2);
            
            查詢id為2的老師教過的學生姓名
            方式一：多條語句
                select s_id from teacher_student where t_id=2;
                select * from student where id in (1,2);
            方式二：串連查詢
                select s.* from teacher_student ts,student s where ts.s_id=s.id and ts.t_id=2;
            方式三：子查詢（一個單列結果）
                select * from student where id in (select s_id from teacher_student where t_id=2);
        
            查詢名字為‘陳冠希‘的所有訂單資訊（子查詢）
                select * from orders where customer_id=(select id from customer where name=‘陳冠希‘);
        
    3、聯集查詢：UNION
        聯集查詢能夠合并兩條查詢語句的查詢結果，去掉其中的重複資料行，然後返回沒有重複資料行的查詢結果。
        
        取多條語句的並集（沒有重複記錄）
        查詢客戶id=1並且訂單金額>=200的訂單資訊
        
        SELECT * from orders where price>=200 and customer_id=1;
        
        查詢客戶id=1或者訂單金額>=200的訂單資訊
        SELECT * from orders where price>=200 or customer_id=1;
        使用聯集查詢：查詢客戶id=1或者訂單金額>=200的訂單資訊
        SELECT * FROM orders WHERE price>=200 UNION SELECT * FROM orders WHERE customer_id=1;
        
    4、報表查詢（資料庫的內建函數）
        報表查詢對資料行進行分組統計
        [select …] from … [where…] [ group by … [having… ]] [ order by … ]
        group by:按照那些欄位進行分組
        having:對分組後的內容進行過濾（不能使用where）
        
        
        統計一個班級共有多少學生？
        mysql>SELECT count(*) FROM student;
        統計數學成績大於90的學生有多少個？
        mysql>SELECT COUNT(*) FROM student where math>90;
        統計總分大於250的人數有多少？
        mysql>SELECT COUNT(*) FROM student where (math+chinese+english)>250;
        統計一個班級數學總成績？
        mysql>SELECT SUM(math) FROM student;
        統計一個班級語文、英語、數學各科的總成績
        mysql>SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
        統計一個班級語文、英語、數學的成績總和
        mysql>SELECT SUM(math+chinese+english) FROM student;
        統計一個班級語文成績平均分
        mysql>SELECT SUM(chinese)/COUNT(*) FROM student;
        
        求一個班級數學平均分？
        mysql>SELECT AVG(math) FROM student;
        求一個班級總分平均分
        msyql>SELECT AVG(math+chinese+english) FROM student;
        
        求班級語文最高分和最低分
        mysql>SELECT MAX(chinese) FROM student;
        mysql>SELECT MIN(chinese) FROM student;
        
        注意：不能使用關鍵字作為表名或列名，如果必須使用，請使用反引號`（ESC按鍵下面）引起來
        
        對訂單表中商品歸類後，顯示每一類商品的總價
        mysql>SELECT product,sum(price) FROM orders GROUP BY product;
        查詢購買了幾類商品，並且每類總價大於100的商品
        mysql>SELECT product,sum(price) FROM orders GROUP BY product HAVING sum(price)>100;
        
        
三、資料庫的備份與恢複（MySQL）
    1、備份資料庫（表結構和表中資料）
        c:/>mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql
        
    2、恢複資料庫：資料庫名必須手工建立，並選擇
        方式一：在MySQL內部匯入資料
            mysql>create database mydb1;
            mysql>use mydb1;
            mysql>source d:/mydb1.sql;
        方式二：不進入mysql進行資料的恢複
            c:/>mysql -u root -p mydb1<d:/mydb1.sql

       

Mysql常見動作陳述式