SQL語句實例說明 方便學習mysql的朋友
MySQL中模式就是數(shù)據(jù)庫
SHOW DATABASES;
show databases;
羅列所有數(shù)據(jù)庫名稱
CREATE DATABASE <數(shù)據(jù)庫名>
create database TEST;
創(chuàng)建名為TEST的數(shù)據(jù)庫
DROP DATABASE <數(shù)據(jù)庫名>
drop database TEST;
刪除名為TEST的數(shù)據(jù)庫
USE <數(shù)據(jù)庫名>
use TEST;
使用名為TEST的數(shù)據(jù)庫
SHOW TABLES
show tables;
顯示當前數(shù)據(jù)庫所有表格
SHOW [FULL] COLUMNS FROM <表名>
show [full] columns from <表名>
在MySQL數(shù)據(jù)庫中顯示表的結(jié)構(gòu),如果加上full則更加詳細
DESC <表名>
desc TEST;
查看表TEST的結(jié)構(gòu),同show columns from TEST
SHOW CREATE TABLE <表名>
show create table TEST;
查看表TEST的所有信息,包括建表語句
創(chuàng)建基本表:
CREATE TABLE <表名>
(<列名> <數(shù)據(jù)類型>[列級完整性約束條件],
<列名> <數(shù)據(jù)類型>[列級完整性約束條件],
………………
);
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ā)器,不能有存儲過程或函數(shù)等。
drop table STUDENT cascade;
若選擇cascade,則該表刪除沒有限制。在刪除基本表的同時,相關(guān)的依賴對象,例如視圖,都將被一起刪除。
但是我在MySQL測試的時候給錯誤提示Cannot delete or update a parent row: a foreign key constraint fails,不予以刪除,不知道是什么原因。
關(guān)于完整約束性:
參考文章:完整性約束的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 表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄。
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的索引
數(shù)據(jù)庫索引的建立有利也有弊,參考文章:
數(shù)據(jù)庫索引的作用和優(yōu)點缺點(一)
數(shù)據(jù)庫索引的作用和優(yōu)點缺點(二)
數(shù)據(jù)庫建立索引的原則
數(shù)據(jù)查詢:
SELECT [ALL|DISTINCT] <目標列表達式> [,<目標列表達式>]……
FROM <表名或視圖名> [<表名或視圖名>]……
[WHERE <條件表達式>]
[GROUP BY <列名1> [HAVING <條件表達式>]]
[ORDER BY <列名2> [ASC|DESC] [,<列名3> [ASC|DESC]]……];
查詢經(jīng)過計算的值:
select teacherId as id,salary - 100 as S from teacher;
查詢經(jīng)過計算的值,從teacher表中查詢出teacherId字段,別名為id,并且查詢出salary字段減去100后的字段,別名為S
使用函數(shù)和字符串:
select teacherid as id,'birth',salary - 20 as SA, lower(name) from teacher;
<目標表達式>可以是字符串常量和函數(shù)等,'birth' 為字符串常量,lower(name)為函數(shù),將name字段以小寫字母形式輸出
消除取值重復(fù)的行:
select distinct namefrom teacher;
如果沒有指定DISTINCT關(guān)鍵詞,則缺省為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結(jié)尾的任意長度的字符串。如acb,addgb,ab
_代表任意單個字符。例如a_b表示以a開頭,以b結(jié)尾的長度為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 子句對查詢結(jié)果按照一個或多個屬性列的升序(ASC)或降序(DESC)排列,缺省值為(ASC)
select salary from teacher order by salary asc;
select * from teacher order by name desc,salary asc;
聚集函數(shù)(aggregate functions):
COUNT([DISTINCT|ALL]*) 統(tǒng)計元組個數(shù)
COUNT([DISTINCT|ALL]<列名>) 統(tǒng)計一列值的個數(shù)
SUM([DISTINCT|ALL]<列名>) 計算一列值的總和
AVG([DISTINCT|ALL]<列名>) 計算一列值的平均值
MAX([DISTINCT|ALL]<列名>) 求一列值中的最大值
MIN([DISTINCT|ALL]<列名>) 求一列值中的最小值
缺省值為ALL
select count(distinct name) from teacher;
查詢沒有重復(fù)的名字的個數(shù)
select count(*) from teacher;
查詢teacher表格總記錄數(shù)
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子句將查詢結(jié)果按某一列或多列的值分組,值相等的為一組。
對查詢結(jié)果分組的目的是為了細化聚集函數(shù)的作用對象。分組后聚集函數(shù)將作用于每一個組,即每一組都有一個函數(shù)值。
select cno,count(*) from teacher group by cno;
對teacher表格按照cno分組,并算出每組里面有多少個元素
如果分組后還要按照一定的條件對這些組進行篩選,最終只輸出滿足指定條件的組,則可以使用HAVING語句指定篩選條件。
select cno,count(*) from teacher group by cno having count(*) >= 4;
對teacher表格按照cno分組,并算出每組里面有多少個元素,得到元素個數(shù)大于等于4的值
連接查詢:
連接查詢是關(guān)系數(shù)據(jù)庫中最主要的的查詢,包括等值連接查詢,自然連接查詢,非等值連接查詢,自身連接查詢,外連接查詢和復(fù)合條件連接查詢等。
等值與非等值連接查詢:
連接查詢的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字段相等的元組
外連接:
左外連接列出左邊關(guān)系中所有元組,右外連接列出右邊關(guān)系中所有元組。
左外連接: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表數(shù)據(jù):
+-----+-----+------+
| sno | cno | name |
+-----+-----+------+
| 1 | 1 | 地心 |
| 2 | 2 | 華雄 |
| 3 | 1 | 孝慈 |
| 4 | 3 | 必須 |
+-----+-----+------+
class表數(shù)據(jù):
+-----+-----+------+
| 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不支持全外連接!
復(fù)合條件連接:
WHERE子句中可以有多個連接條件,稱為復(fù)合條件連接
select s.sno,s.name,c.name,s.score from student s,class c where s.cno = c.cno and s.score < 60;
復(fù)合條件連接查詢,查詢學生信息和課程信息,并且成績小于60的記錄
嵌套查詢:
一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。
子查詢的SELECT語句中不能使用ORDER BY子句,ORDER BY 子句只能對最終查詢結(jié)果排序
帶有IN謂詞的子查詢:
select sno,name from student
where cno in
(
select cno from student
where name = '華雄'
);
查詢和"華雄"選同一課程的所有學生的學號和姓名。
子查詢的查詢條件不依賴于父查詢,稱為不相關(guān)子查詢。
如果子查詢條件依賴于父查詢,這類子查詢稱為相關(guān)子查詢,整個查詢語句稱為相關(guān)嵌套查詢語句。
帶有比較運算符的子查詢:
select name,cno from student s1
where score >
(
select avg(score) from student s2
where s2.name = s1.name
);
查詢學生的大于各科平均成績的科目
以上是相關(guān)子查詢。
帶有ANY(SOME)或ALL謂詞的子查詢
子查詢返回單值時可以用比較運算符,但返回多值時要用ANY(有的系統(tǒng)用SOME)或ALL謂詞修飾。使用ALL或ALL謂詞時必須使用比較運算符。
>ANY 大于子查詢結(jié)果的某個值
>ALL 大于子查詢結(jié)果的所有值
<ANY 小于子查詢結(jié)果的某個值
<ALL 小于子查詢結(jié)果的所有值
>=ANY 大于等于子查詢結(jié)果的某個值
>=ALL 大于等于子查詢結(jié)果的所有值
<=ANY 小于等于子查詢結(jié)果的某個值
<=ALL 小于等于子查詢結(jié)果的所有值
=ANY 等于子查詢結(jié)果的某個值
=ALL 等于子查詢結(jié)果的所有值(通常沒有實際意義)
!=(或<>)ANY 不等于子查詢結(jié)果的某個值
!=(或<>)ALL 不等于子查詢結(jié)果的任何一個值
select name,score from student where score <= all (select score from student);
查詢成績最小的學生姓名和成績
集合查詢:
SELECT語句的查詢結(jié)果是元組的集合,所以多個SELECT語句的結(jié)果可進行集合操作。集合操作主要包括并操作(UNION),交操作(INTERSECT),差操作(EXCEPT)。
參加集合操作的各查詢結(jié)果的列數(shù)必須相同;對應(yīng)項的數(shù)據(jù)類型也必須相同。
MySQL數(shù)據(jù)庫不支持INTERSECT和EXCEPT操作!
select * from student where cno=1
union
select * from student where cno=2;
查詢班級1和班級2所有學生信息
數(shù)據(jù)更新:
插入數(shù)據(jù):
插入元組:
INSERT
INTO <表名> [(<屬性列1>)[,<屬性列2>]……]
VALUES (<常量1>[,<常量2>]……);
例子:
insert into student (cno,name,score) values (2,'橫切',85);
插入子查詢結(jié)果:
INSERT
INTO <表名> [(<屬性1>[,<屬性2>]……)]
子查詢;
例子:
insert into studentcopy select * from student;
將student表的信息全部復(fù)制到studentcopy表中
修改數(shù)據(jù):
UPDATE <表名>
SET <列名>=<表達式>[,<列名>=<表達式>]……
[WHERE <條件>]
修改某一元組的值:
update studentcopy set score=80 where sno=1;
修改多個元組的值:
update studentcopy set score=score-20;
刪除數(shù)據(jù):
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);
視圖:
關(guān)于視圖,它的作用和優(yōu)缺點可以參考文章:數(shù)據(jù)庫視圖介紹
建立視圖:
CREATE VIEW <視圖名> [(<列名>[,<列名>]……)]
AS <子查詢>
[WITH CHECK OPTION]
子查詢可以是任意復(fù)雜的SELECT語句,但通常不允許含有ORDER BY子句和DISTINCT語句。
WITH CHECK OPTION 表示對視圖進行UPDATA,INSERT和DELETE操作時要保證更新,插入或刪除的行滿足視圖定義中的謂詞條件
組成視圖的屬性列名或者全部省略或者全部指定,沒有第三種選擇。如果省略了視圖的各個屬性列名,則隱含該視圖由子查詢中SELECT子句目標列中的諸字段組成。
但在下面三種情況下必須明確指定組成視圖的所有列名:
(1) 某個目標列不是單純的屬性名,而是聚集函數(shù)或列表達式。
(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;
結(jié)合學生表和選課表建立視圖
如果以后修改了基本表的結(jié)構(gòu),則基本表與視圖的映射關(guān)系就被破壞了,該視圖就不能正確工作了。為避免出現(xiàn)這類問題,最好在修改基本表之后刪除由該基本表導(dǎo)出的視圖,然后重建這個視圖。
刪除視圖:
DROP VIEW <視圖名> [CASCADE];
如果視圖上還導(dǎo)出了其他視圖,則使用CASCADE級聯(lián)刪除語句,把該視圖和由它導(dǎo)出的所有視圖一起刪除。
查詢視圖:
查詢視圖和查詢基本表類似。
更新視圖:
更新視圖和更新基本表類似,不過有些限制。
版權(quán)聲明:本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學習參考,不代表本站立場,如有內(nèi)容涉嫌侵權(quán),請聯(lián)系alex-e#qq.com處理。