程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL語句實例解釋 便利進修mysql的同伙

SQL語句實例解釋 便利進修mysql的同伙

編輯:MSSQL

SQL語句實例解釋 便利進修mysql的同伙。本站提示廣大學習愛好者:(SQL語句實例解釋 便利進修mysql的同伙)文章只能為提供參考,不一定能成為您想要的結果。以下是SQL語句實例解釋 便利進修mysql的同伙正文


MySQL中形式就是數據庫

SHOW DATABASES;

show databases;
枚舉一切數據庫稱號

CREATE DATABASE <數據庫名>

create database TEST;
創立名為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的一切信息,包含建表語句

創立根本表:
CREATE TABLE <表名>
(<列名> <數據類型>[列級完全性束縛前提],
<列名> <數據類型>[列級完全性束縛前提],
………………
);

create table STUDENT
(
studentId int(30) primary key,
name varchar(255),
address varchar(255)
);
創立名為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)
);
創立名為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等束縛),
不克不及有視圖,不克不及有觸發器,不克不及有存儲進程或函數等。

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的索引

數據庫索引的樹立有益也有弊,參考文章:

數據庫索引的感化和長處缺陷(一)

數據庫索引的感化和長處缺陷(二)

數據庫樹立索引的准繩

數據查詢:

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)比擬年夜小:

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]*)                 統計元組個數
COUNT([DISTINCT|ALL]<列名>)       統計一列值的個數
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(有的體系用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);

視圖:

 關於視圖,它的感化和優缺陷可以參考文章:數據庫視圖引見

樹立視圖:

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級聯刪除語句,把該視圖和由它導出的一切視圖一路刪除。

查詢視圖:

查詢視圖和查詢根本表相似。

更新視圖:

更新視圖和更新根本表相似,不外有些限制。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved