SQL語句實例說明 方便學習mysql的朋友

熱門標簽：重慶防封電銷機器人供應商 西安青牛防封電銷卡 智能語音外呼系統(tǒng)哪個牌子好 溫州語音外呼系統(tǒng)代理 山西語音外呼系統(tǒng)價格 威海智能語音外呼系統(tǒng) 400電話申請需要開戶費嗎 北京辦理400電話多少 南京電銷外呼系統(tǒng)運營商

MySQL中模式就是數據庫

SHOW DATABASES;

show databases;
羅列所有數據庫名稱

CREATE DATABASE 數據庫名>

create database TEST;
創(chuàng)建名為TEST的數據庫

DROP DATABASE 數據庫名>

drop database TEST;
刪除名為TEST的數據庫

USE 數據庫名>

use TEST;
使用名為TEST的數據庫

SHOW TABLES

show tables;
顯示當前數據庫所有表格

SHOW [FULL] COLUMNS FROM 表名>

show [full] columns from 表名>
在MySQL數據庫中顯示表的結構,如果加上full則更加詳細

DESC 表名>

desc TEST;
查看表TEST的結構，同show columns from TEST

SHOW CREATE TABLE 表名>

show create table TEST;

查看表TEST的所有信息，包括建表語句

創(chuàng)建基本表:
CREATE TABLE 表名>
(列名> 數據類型>[列級完整性約束條件],
列名> 數據類型>[列級完整性約束條件],
………………
);

create table STUDENT
(
studentId int(30) primary key,
name varchar(255),
address varchar(255)
);
創(chuàng)建名為STUDENT的表格，有屬性studentId,name,address,其中studentId為主鍵

create table TEACHER
(
teacherId int(30),
name varchar(255),
age int(20),
studentId int(30),
primary key (teacherId),
foreign key (studentId) references STUDENT(studentId)
);
創(chuàng)建名為TEACHER的表格，其中teacherId為主鍵，studentId為外鍵，引用STUDENT表的主鍵studentId

修改基本表:

ALTER TABLE 表名> RENAME 修改后表名>

alter table TEACHER rename S;
將TEACHER表的表名改為S

ALTER TABLE 表名> ADD COLUMN 列名> 屬性類型>

alter table TEACHER add column ADDRESS varchar(255);
在表名為TEACHER的表中加入名為ADDRESS的列

ALTER TABLE 表名> CHANGE COLUMN 列名> 修改后列名> 屬性類型>

alter table TEACHER change column ADDRESS address varchar(230);
修改TEACHER表中的列，將ADDRESS的列名改為address

ALTER TABLE 表名> DROP [COLUMN] 列名>

alter table TEACHER drop [column] address;
刪除列名為address的列,column可有可無

刪除基本表：

DROP TABLE 表名> [RESTRICT|CASCADE]
刪除表格

drop table STUDENT restrict;
刪除STUDENT表。受限制的刪除，欲刪除的基本表不能被其他表的約束所引用(如check,foreign key等約束)，
不能有視圖，不能有觸發(fā)器，不能有存儲過程或函數等。

drop table STUDENT cascade;

若選擇cascade，則該表刪除沒有限制。在刪除基本表的同時，相關的依賴對象，例如視圖，都將被一起刪除。

但是我在MySQL測試的時候給錯誤提示Cannot delete or update a parent row: a foreign key constraint fails，不予以刪除，不知道是什么原因。

關于完整約束性：

 參考文章：完整性約束的SQL定義

ALTER TABLE 表名> ADD CONSTRAINT 約束名> 約束條件>

 alter table teacher add constraint pk_teacher_id primary key teacher(id);

 在teacher表中增加名為pk_teacher_id的主鍵約束。

ALTER TABLE 表名> DROP 約束條件>

alter table teacher drop primary key;

刪除teacher表的主鍵約束。

alter table student add constraint fk_student_teacher foreign key student(teacherId) references teacher(id);

在student表中增加名為fk_student_teacher的約束條件，約束條件為外鍵約束。

索引的建立與刪除:

索引的建立：

CREATE [UNIQUE]|[CLUSTER] INDEX 索引名> ON 表名>(列名> [次序][,列名> [次序]]……);

UNIQUE 表明此索引的每一個索引值只對應唯一的數據記錄。

CLUSTER 表示要建立的索引是聚簇索引。

create unique index id_index on teacher(id asc);
對teacher表的id列建立unique索引，索引名為id_index

索引的刪除：

DROP INDEX 索引名> ON 表名>

drop index id_index on teacher;
在teacher表中刪除索引，索引名為id_index

另外的方法：

新建索引：

ALTER TABLE 表名> ADD [UNIQUE]|[CLUSTER] INDEX [索引名>](列名> [次序>],[列名> [次序>]]……)

alter table teacher add unique index id_index(id asc);
在teacher表中對id列升序建立unique索引，索引的名字為id_index

刪除索引：

ALTER TABLE 表名> DROP INDEX 索引名>

alter table teacher drop index id_index;
刪除teacher表名為id_index的索引

數據庫索引的建立有利也有弊，參考文章：

數據庫索引的作用和優(yōu)點缺點（一）

數據庫索引的作用和優(yōu)點缺點（二）

數據庫建立索引的原則

數據查詢：

SELECT [ALL|DISTINCT] 目標列表達式> [,目標列表達式>]……

FROM 表名或視圖名> [表名或視圖名>]……

[WHERE 條件表達式>]

[GROUP BY 列名1> [HAVING 條件表達式>]]

[ORDER BY 列名2> [ASC|DESC] [,列名3> [ASC|DESC]]……]；

查詢經過計算的值：

select teacherId as id,salary - 100 as S from teacher;

查詢經過計算的值，從teacher表中查詢出teacherId字段，別名為id,并且查詢出salary字段減去100后的字段，別名為S

 

使用函數和字符串：

select teacherid as id,'birth',salary - 20 as SA, lower(name) from teacher;

目標表達式>可以是字符串常量和函數等,'birth' 為字符串常量，lower(name)為函數，將name字段以小寫字母形式輸出

 

消除取值重復的行：

select distinct name from teacher;

如果沒有指定DISTINCT關鍵詞，則缺省為ALL.

 

查詢滿足條件的元組：

WHERE子句常用的查詢條件:

查詢條件	謂詞
比較	=, >, , >=, =, !=, >, !>, !
確定范圍	BETWEEN AND, NOT BETWEEN AND
確定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重條件(邏輯運算)	AND, OR, NOT

 

(1)比較大?。?/SPAN>

select * from teacher where name = 'test';

select * from teacher where salary > 500;

select * from teacher where salary > 500;

(2)確定范圍：

select * from teacher where salary between 300 and 1000；

select * from teacher where salary not between 500 and 1000

(3)確定集合

select * from teacher where name in('test','test2');

select * from teacher where name not in('test','test2');

(4)字符匹配:

[NOT] LIKE '匹配串>' [ESCAPE '換碼字符>']
匹配串>可以是一個完整的字符串，也可以含有通配符%和_
%代表任意長度(長度可以是0)的字符。例如a%b表示以a開頭，以b結尾的任意長度的字符串。如acb,addgb,ab

_代表任意單個字符。例如a_b表示以a開頭，以b結尾的長度為3的任意字符串。如acb,afb等都滿足該匹配串。

select * from teacher where name like '%2%‘；

select * from teacher where name like '_e%d'；

注意一個漢字要占兩個字符的位置。

(5)涉及空值查詢：

select * from teacher where name is null;

select * from teacher where name is not null;

注意這里的"is"不能用符號(=)代替。

(6)多重條件查詢：

select * from teacher where name = 'test' and salary between 400 and 800;

select * from teacher where name like '%s%' or salary = 500;

 

ORDER BY子句：

ORDER BY 子句對查詢結果按照一個或多個屬性列的升序(ASC)或降序(DESC)排列，缺省值為(ASC)

select salary from teacher order by salary asc;

select * from teacher order by name desc,salary asc;

聚集函數(aggregate functions):

COUNT([DISTINCT|ALL]*)                 統(tǒng)計元組個數
COUNT([DISTINCT|ALL]列名>)       統(tǒng)計一列值的個數
SUM([DISTINCT|ALL]列名>)            計算一列值的總和
AVG([DISTINCT|ALL]列名>)             計算一列值的平均值
MAX([DISTINCT|ALL]列名>)             求一列值中的最大值
MIN([DISTINCT|ALL]列名>)　　       求一列值中的最小值

缺省值為ALL

select count(distinct name) from teacher;
查詢沒有重復的名字的個數

select count(*) from teacher;
查詢teacher表格總記錄數

select sum(salary) from teacher;
查詢teacher表的salary字段的總和

select avg(salary) from teacher;
查詢teacher表的salary字段的平均值

select max(salary) from teacher;
查詢teacher表的salary字段的最大值

select min(salary) from teacher;
查詢teacher表的salary字段的最小值

 

GROUP BY子句：

GROUP BY子句將查詢結果按某一列或多列的值分組，值相等的為一組。
對查詢結果分組的目的是為了細化聚集函數的作用對象。分組后聚集函數將作用于每一個組，即每一組都有一個函數值。

select cno,count(*) from teacher group by cno;
對teacher表格按照cno分組，并算出每組里面有多少個元素

如果分組后還要按照一定的條件對這些組進行篩選，最終只輸出滿足指定條件的組，則可以使用HAVING語句指定篩選條件。

select cno,count(*) from teacher group by cno having count(*) >= 4;
對teacher表格按照cno分組，并算出每組里面有多少個元素，得到元素個數大于等于4的值

 

連接查詢：

連接查詢是關系數據庫中最主要的的查詢，包括等值連接查詢，自然連接查詢，非等值連接查詢，自身連接查詢，外連接查詢和復合條件連接查詢等。

等值與非等值連接查詢：

連接查詢的WHERE子句中用來連接兩個表的條件稱為連接條件或連接謂詞，格式為：

[表名1>.]列名> 比較運算符> [表名2>.]列名2>

其中比較運算符主要有：=，>, , >=, =, !=(或>)等

 

select s.*,t.* from student as s,teacher as t where s.teacherid = t.teacherid;
等值連接查詢，將student表和teacher的信息連接查詢出來，連接條件是s.teacherid = t.teacherid

自身連接：

一個表與自身進行連接，稱為自身連接

select teacher.name,student.name from people as teacher,people as student where teacher.name = student.teacher;
自身連接查詢，在people表里有兩種角色，一種是教師，一種是學生，利用自身連接查詢，得到name字段和teacher字段相等的元組

外連接：

左外連接列出左邊關系中所有元組，右外連接列出右邊關系中所有元組。

左外連接：SELECT 目標列表達式>[,目標列表達式>]…… FROM 表名1> LEFT [OUTER] JOIN 表名2> ON 連接條件>
右外連接：SELECT 目標列表達式>[,目標列表達式>]…… FROM 表名1> RIGHT [OUTER] JOIN 表名2> ON 連接條件>

select s.sno,s.name,c.cno,c.name from student as s left outer join class as c on (s.cno = c.cno);
student表和class表進行左外連接，連接條件是s.cno=c.cno

select c.cno,c.name,s.sno,s.name from student as s right outer join class as c on (s.cno = c.cno); 
student表和class表進行右外連接，連接條件為s.cno=c.cno

student表數據：

+-----+-----+------+
| sno | cno | name |
+-----+-----+------+
| 1 | 1 | 地心 |
| 2 | 2 | 華雄 |
| 3 | 1 | 孝慈 |
| 4 | 3 | 必須 |
+-----+-----+------+

class表數據：

+-----+-----+------+
| cid | cno | name |
+-----+-----+------+
| 1 | 1 | 化學 |
| 2 | 2 | 物理 |
| 3 | 3 | 政治 |
+-----+-----+------+

 

左外連接效果：

+-----+------+-----+------+
| sno | name | cno | name |
+-----+------+-----+------+
| 1 | 地心 | 1 | 化學 |
| 2 | 華雄 | 2 | 物理 |
| 3 | 孝慈 | 1 | 化學 |
| 4 | 必須 | 3 | 政治 |
+-----+------+-----+------+

右外連接效果：

+-----+------+-----+------+
| cno | name | sno | name |
+-----+------+-----+------+
| 1 | 化學 | 1 | 地心 |
| 1 | 化學 | 3 | 孝慈 |
| 2 | 物理 | 2 | 華雄 |
| 3 | 政治 | 4 | 必須 |
+-----+------+-----+------+

MySQL不支持全外連接！

 

復合條件連接：

WHERE子句中可以有多個連接條件，稱為復合條件連接

select s.sno,s.name,c.name,s.score from student s,class c where s.cno = c.cno and s.score 60;
復合條件連接查詢，查詢學生信息和課程信息，并且成績小于60的記錄

嵌套查詢：

一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。

子查詢的SELECT語句中不能使用ORDER BY子句，ORDER BY 子句只能對最終查詢結果排序

帶有IN謂詞的子查詢:

select sno,name from student
where cno in
(
select cno from student
where name = '華雄'
);

查詢和"華雄"選同一課程的所有學生的學號和姓名。

 

子查詢的查詢條件不依賴于父查詢，稱為不相關子查詢。

如果子查詢條件依賴于父查詢，這類子查詢稱為相關子查詢，整個查詢語句稱為相關嵌套查詢語句。

帶有比較運算符的子查詢：

select name,cno from student s1
where score >
(
select avg(score) from student s2
where s2.name = s1.name
);

查詢學生的大于各科平均成績的科目

以上是相關子查詢。

 

帶有ANY(SOME)或ALL謂詞的子查詢

子查詢返回單值時可以用比較運算符，但返回多值時要用ANY(有的系統(tǒng)用SOME)或ALL謂詞修飾。使用ALL或ALL謂詞時必須使用比較運算符。

>ANY                          大于子查詢結果的某個值
>ALL                           大于子查詢結果的所有值 
ANY                           小于子查詢結果的某個值
ALL                            小于子查詢結果的所有值
>=ANY                          大于等于子查詢結果的某個值     
>=ALL                          大于等于子查詢結果的所有值
=ANY                          小于等于子查詢結果的某個值
=ALL                           小于等于子查詢結果的所有值
=ANY                             等于子查詢結果的某個值
=ALL                              等于子查詢結果的所有值(通常沒有實際意義)
!=(或>)ANY                   不等于子查詢結果的某個值
!=(或>)ALL                    不等于子查詢結果的任何一個值

select name,score from student where score = all (select score from student);
查詢成績最小的學生姓名和成績

集合查詢：

SELECT語句的查詢結果是元組的集合，所以多個SELECT語句的結果可進行集合操作。集合操作主要包括并操作(UNION),交操作(INTERSECT),差操作(EXCEPT)。
參加集合操作的各查詢結果的列數必須相同；對應項的數據類型也必須相同。
MySQL數據庫不支持INTERSECT和EXCEPT操作！

select * from student where cno=1
union
select * from student where cno=2;
查詢班級1和班級2所有學生信息

 

 

數據更新：

插入數據：

插入元組：

INSERT
INTO 表名> [(屬性列1>)[,屬性列2>]……]
VALUES (常量1>[,常量2>]……)；

例子：

insert into student (cno,name,score) values (2,'橫切',85);

 

插入子查詢結果：

INSERT 
INTO 表名> [(屬性1>[,屬性2>]……)]
子查詢;

例子：

insert into studentcopy select * from student;
將student表的信息全部復制到studentcopy表中

修改數據：

UPDATE 表名>
SET 列名>=表達式>[,列名>=表達式>]……
[WHERE 條件>]

修改某一元組的值：

update studentcopy set score=80 where sno=1;

修改多個元組的值:

update studentcopy set score=score-20;

刪除數據：

DELETE
FROM 表名>
[WHERE 條件>]; 

 

刪除某一元組：

delete from studentcopy where sno=1;

刪除多個元組：

delete from studentcopy;

帶子查詢的刪除語句：

delete from studentcopy where cno in (select cno from student as s where s.cno = 2);

視圖：

 關于視圖，它的作用和優(yōu)缺點可以參考文章：數據庫視圖介紹

建立視圖：

CREATE VIEW 視圖名> [(列名>[,列名>]……)]
AS 子查詢>
[WITH CHECK OPTION]

子查詢可以是任意復雜的SELECT語句，但通常不允許含有ORDER BY子句和DISTINCT語句。

WITH CHECK OPTION 表示對視圖進行UPDATA,INSERT和DELETE操作時要保證更新，插入或刪除的行滿足視圖定義中的謂詞條件

組成視圖的屬性列名或者全部省略或者全部指定，沒有第三種選擇。如果省略了視圖的各個屬性列名，則隱含該視圖由子查詢中SELECT子句目標列中的諸字段組成。
但在下面三種情況下必須明確指定組成視圖的所有列名：
(1) 某個目標列不是單純的屬性名，而是聚集函數或列表達式。
(2) 多表連接時選出了幾個同名列作為視圖的字段。
(3) 需要在視圖中為某個列啟用新的更合適的名字。

create view part_student
as
select * from student
where cno = 2;
建立物理班學生的視圖

create view student_class (sno,student_name,class_name,score)
as
select s.sno,s.name,c.name,s.score
from student as s,class as c
where s.cno = c.cno;
結合學生表和選課表建立視圖

如果以后修改了基本表的結構，則基本表與視圖的映射關系就被破壞了，該視圖就不能正確工作了。為避免出現這類問題，最好在修改基本表之后刪除由該基本表導出的視圖，然后重建這個視圖。

刪除視圖：

DROP VIEW 視圖名> [CASCADE];

如果視圖上還導出了其他視圖，則使用CASCADE級聯(lián)刪除語句，把該視圖和由它導出的所有視圖一起刪除。

查詢視圖：

查詢視圖和查詢基本表類似。

更新視圖：

更新視圖和更新基本表類似，不過有些限制。