MySQL學習（二）SQL語句的總結

1、連接配接查詢和關聯查詢

連接配接查詢：把兩個表中相同的元素的連接配接就可以查詢，

使用：where裡，select table1.*,table2.* from table1,table2 where table1.id=table2.id;

關聯查詢：把兩個表關聯，之後的操作對兩個表都有影響（left join，right join，full join，

inner join）

使用:不用where，select table1.*,table2.* from table1 （inner）join table2 on

table1.id=table2.id;

（1）SELECT * FROM score, student WHERE score.id = student.id ...

（2）SELECT * FROM score JOIN student ON (score.id = student.id) WHERE ...

（3）SELECT * FROM score JOIN student USING (id) WHERE ...

前兩種寫法，隻是寫法不同而已，其功能完全相同；最後一種，會将同名的列，合并起來。

第一種，是傳統寫法，SQL89标準裡，就是這種；後面兩種，是在SQL92标準才有的！

2、多表查詢（連接配接查詢）關系資料庫中最主要特征

無論哪種連接配接都不能對text、ntext和image資料類型列進行直接連接配接，但可以對這三種列進行間接連接配接。

連接配接：

（1）自連接配接；一個表自己與自己建立連接配接稱為自連接配接或自身連接配接。可以把一個表的某一行與同一表中的

另一行連接配接起來。

(2）内連接配接；

文法：select 字段名清單 from 表名 [inner] join 表名 on 連接配接條件 [where 條件表達式]

分類：

（1）等值連接配接：是指表之間通過“等于”關系連接配接起來，産生一個臨時表。

（2）不等值連接配接：是指表之間的連接配接關系不是“等于”，而是其它關系

這些運算符包括>、>=、<=、<、!>、!<和<>。

（3）自然連接配接：在等值連接配接中消除重複列就是自然連接配接。表A natural join B where 連接配接條件

(3)外連接配接；外連接配接是對内連接配接的擴充,除了将兩個資料集合中重疊部分以内的那些資料行連接配接起來之外

,

(4)交叉連接配接；交叉連接配接不使用任何連接配接條件來限制結果集合,将各表的記錄以“笛卡爾”積的方式組合

起來。

(5)聯合查詢：UNION運算符可以将兩個或兩個以上上SELECT語句的查詢結果集合合并成一個結果集合顯

示。

(6)複雜查詢：---複合連接配接，三個表以上的連接配接。

3、查詢平均成績

select class_name as 班級名,avg(stu_score1)  as 平均成績 from stu_table,class_table where stu_table.class_id=class_table.class_id;

4、查詢成績排名的前五名

select stu_name,stu_score1 from stu_table order by stu_score1 desc limit 5 ;

5、在college_id字段後邊加入tel字段

alter table stu_table add tel varchar(15) after college_id;

6、-- 把tel字段修改成tel1 --

alter table stu_table change tel tel1 varchar(30);

7、建立表（SQL語句建表）（需要重新整理才能見到）

create table test1(

test_id int not null auto_increment,

test_title varchar(100) not null,

test_author varchar(40) not null,

primary key ( test_id )

);

字段使用not null屬性，不希望這個字段值為null。

字段使用auto_increment屬性告訴我們自動帶有增加id字段下一個可用編号

字段primary key用于定義此列作為主鍵

8、建立表（通過指令行提示符來建立表）使用資料庫TUTORIALS，建立表tutorials_tbl

root@host# mysql -u root -p

Enter password:

mysql> use TUTORIALS;

Database changed

mysql> CREATE TABLE tutorials_tbl(

-> tutorial_id INT NOT NULL AUTO_INCREMENT,

-> tutorial_title VARCHAR(100) NOT NULL,

-> tutorial_author VARCHAR(40) NOT NULL,

-> submission_date DATE,

-> PRIMARY KEY ( tutorial_id )

-> );

Query OK, 0 rows affected (0.16 sec)

mysql>

9、-- 删除表 --

drop table tutorials_tbl; key用于定義此列作為主鍵。

10、my sql限制條件

（1）主鍵(PRIMARY KEY)

辨別該屬性為該表的主鍵，可以唯一的辨別對應的記錄。

（2）外鍵(FOREIGN KEY)

辨別該屬性為該表的外鍵，與某個表的主鍵關聯。

（3）唯一性(UNIQUE)

辨別該屬性的值是唯一的。

（4）非空(NOT NULL)

辨別該屬性不能為空。

（5）預設值(DEFAULT)

為該屬性設定預設值。

* MySQL不支援CHECK限制，但可以使用CHECK限制而沒有任何效果。

#建表(班級表)

create table t_class(

id int primary key auto_increment, #班級id為主鍵，id設定自增長

className varchar(20) unique not null #班級名稱，不能為空且唯一

#建立學生表

create table t_student(

id int primary key auto_increment, #學生id為主鍵，id自增長

name varchar(20) unique not null, #學生姓名，不能為空且唯一

sex varchar(1) not null, #學生性别，不能為空

birthday datetime not null default '1992-10-01 00:00:00', #學生出生日期，不能為空，預設

值為1992-10-01 00:00:00

classId int, #學生所屬的班級id為外鍵，關聯班級表't_class'的主鍵id

constraint test_class foreign key(classId) references t_class(id) #建立外鍵限制，限制名

為test_class

11、mysql視圖

理由（原因）：

（1）安全性，用于權限與視圖進行綁定，使用特性:grant語句針對視圖進行授予權限

（2）查詢性能提高

（3）靈活性的功能需求，改動表的結構工作量大，使用虛表達到少修改效果

（4）複雜的查詢需求

視圖的内容沒有存儲，而是在視圖被引用的時候才派生出資料。這樣不會占用空間，由于是即時引

用，視圖的内容總是與真實表的内容是一緻的。

視圖這樣設計有什麼好處？節省空間，内容是總是一緻的話，那麼我們不需要維護視圖的内容，維

護好真實表的内容，就可以保證視圖的完整性了。

針對每個表建立一個視圖（注：視圖也是一種表，是虛拟表。不能與已有的表(視圖)出現重名）

create view teams(字段，字段，字段) as select distinct(字段，字段) from 表名

例如：#建立視圖

create view test3(stu_id,stu_name,stu_score1,stu_sex) as

select distinct stu_id,stu_name,stu_score1,stu_sex from stu_table;

12、資料庫優化1

應盡量避免在 where 子句中使用 or 來連接配接條件，否則将導緻引擎放棄使用索引而進行全表掃描，

Sql 代碼 : select id from t where num=10 or num=20;

可以這樣查詢：

Sql 代碼 : select id from t where num=10 union all select id from t where num=20;

13、資料庫優化2

in 和 not in 也要慎用，否則會導緻全表掃描，如：

Sql 代碼 : select id from t where num in(1,2,3);

對于連續的數值，能用 between 就不要用 in 了：

Sql 代碼 : select id from t where num between 1 and 3;

14、資料庫優化3

應盡量避免在 where 子句中對字段進行表達式操作， 這将導緻引擎放棄使用索引而進行全表掃描。

Sql 代碼 : select id from t where num/2=100;

Sql 代碼 : select id from t where num=100*2;

文中有錯誤的地方希望指出，共同進步