2025年.sql文件的作用（sql文件的作用）

在mysql shell中链接到本地mysql数据库：

c root@localhost:3306

在cmd/shell中链接到本地mysql数据库：

mysql -u root -p

database:保存有组织的数据的容器（通常是一个文件或一组文件）

table：某种特定类型数据的结构化清单，注意，存储在表中的数据是一种类型的数据或一个清单，对于不同的数据应该创建不同的表。数据库中的每个表都有一个名字，用来标识自己，这个名字是唯一的。但是不同的数据库中可以使用相同的。

schema:关于数据库和表的布局及特性的信息，如可以存储什么样的数据，数据如何分解，各部分信息如何命名，etc.

column:表中的一个字段，所有表都是由一个或多个列组成的，列中存储着表中某部分的信息，数据库中的每个列都有相应的数据类型。

datatype：所容许的数据的类型，每个表列都有相应的数据烈性，它限制该列中存储的数据。

row:表中的一个记录，例如，顾客表可以每行存储一个顾客，存在某列存储所有顾客的姓名。

primary key;一列（或者一组列),其值能够唯一区分表中的每个行。要注意，没有主键，更新和删除表中的特定行很困难，并且任意两行都不具有相同的主键值，主键列不允许NULL值，当然也可以让特定列的组合是唯一的来确定主键。好习惯：不更新主键列中的值，不重用主键列中的值，不在主键列中使用可能会更改的值。

auto_increment:自动增量，在每个行添加到表中时，mysql可以自动的为每个行分配下一个可用编号，而不用在添加一行时手动分配唯一值。

USE(关键字)：当最初链接到mysql时，没有任何数据库打开让我们使用，USE （数据库名）能让我们选择一个数据库进行操作，必须先用USE打开数据库，才能读取其中的数据。

SHOW DATABASES：返回可用数据库的一个列表。

SHOW TABLES:获得当前选择的数据库内可用表的列表(得先用USE选中数据库)。

SHOW COLUMNS FROM （表名）：显示选中表的表列信息，他对每个字段返回一行，行中包含字段名，数据类型，是否允许NULL，键信息，默认值以及其他信息。

DESCRIBE：DESCRIBE (表名)是SHOW COLUMNS FROM (表名)的一种快捷方式。

SHOW STATUS:用于显示广泛的服务器状态信息。

SHOW CREATE DATABASE和SHOW CREATE TABLE:分别用来显示创建特定数据库或表的mysql语句。

SHOW GRANTS:用来显示授予用户（所有用户或特定用户）的安全权限。

SHOW ERRORS和SHOW WARNINGS:用来显示服务器错误或警告信息。

HELP SHOW:显示允许的SHOW语句。

SELECT （列名）FORM（表名）:该语句将会检索指定的列，允许如下形式：SELECT(列名)，（列名）…FROM(表名),即一次检索多个列，列名使用逗号分隔。

SELECT * FROM (表名)：使用*（星号）通配符来检索表中的所有列，当然，也可以检索未知的列。

SELECT DISTINCT (列名) FROM （表名）：DISTINCT后面的列只会返回不相同的值的行。注意：DISTINCT关键字应用于所有的列而不仅仅是前置它的列。

SELECT ALL (列名) FROM（表名）：显式指明不去除重复。

LIMIT n:可以应用于SELECT子句后，指示mysql返回不多于n行。

LIMIT n,m:可以应用于SELECT子句后，指示mysql返回从第n行开始不多于m行的数据。注意：在mysql中，检索出来的第一行为行0而不是行1.

LIMIT n OFFSET m:可以应用于SELECT子句后，意思是从行m开始取n行。

SELECT（表名.列名）FROM（表名）：可以使用完全限定的名字来引用列。

SELECT (表名.列名) FROM (数据库名.表名)：也可以使用完全限定的表名来引用表。

clause(子句)：SQL语句由子句构成，有的子句是必须的，有的子句是可选的，一个子句通常由一个关键字和所提供的数据组成，例子就如上面SELECT语句的FROM子句。

ORDER BY：ORDER BY子句，ORDER BY子句取一个或者多个列的名字，据此对输出进行排序，这个被选择的列不一定非要是输出的列。当然和SELECT语句一样，可以在BY后面跟许多用来排序的行，但是不同的是，这些行的先后顺序决定了他们决定输出顺序的优先级。

ORDER BY (列名) DESC:DESC关键字让数据降序排列，注意，DESC关键字只应用到直接位于它前面的列名。默认是升序排列。在给出ORDER BY子句时，应该保证它位于FROM子句之后，如果使用LIMIT，它必须位于ORDER BY之后，使用子句的次序不对将产生错误信息。

WHERE:在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤，WHERE子句在表名（FROM子句）之后给出。如果将值与串类型的列进行比较，则需要限定引号，用来与数值列进行比较则不需要使用引号。

WHERE (列名) BETWEEN n AND m:可以使用BETWEEN操作符来检索在一定范围内的所有列中的值。

WHERE (列名) IS NULL:该子句可以用来检查具有NULL值的列，要注意：在通过过滤选择不具有特定值的行时，我们可能希望返回具有NULL值的行，但是未知具有特殊的含义，数据库并不会在匹配过滤或不匹配过滤中返回他们。（因为是null所以数据库也不知道到底匹不匹配），因此在过滤数据时，必须验证返回数据中确实给出了被过滤列具有NULL的行。

AND/OR：可以使用这两个操作符来联结多个条件，注意，在同时使用AND和OR操作符时，SQL将优先处理AND操作符，因此要想限制条件便于理解，可以使用圆括号明确的分组相应的操作。

WHERE (列名) IN(合法值1，合法值2，合法值3…):IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，实际上IN关键字的功能与OR相当。

NOT:WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。在MySQL中支持使用NOT对IN，BETWEEN和EXISTS子句去翻。

如果限定区间是一个范围，可以使用between … and …运算符来简化where子句。

wildcard（通配符）：用来匹配值的一部分的特殊字符。

search pattern：由字面值，通配符或者两者组合构成的搜索条件。

LIKE：LIKE操作符，指示MySQL，后跟的搜索模式利用模式匹配而不是直接相等匹配进行比较，注意模式匹配是大小写敏感的。

%：%（百分号）通配符，在搜索串中，%表示任何字符出现的任意次数，也就是说%可以替代任何串，例如，为了找出所有以词jet起头的串，可以使用如下SELECT语句：SELECT id FROM products WHERE id LIKE ‘jet%’(注意串需要被单引号引起来)，重要的是：除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个，1个，或者多个字符。还有，虽然似乎%通配符可以匹配任何东西，但还是有一个例外，即NULL，即使是WHERE id LIKE '%'也不能匹配用值NULL的行。

通配符：通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符，通配符也可以出现在搜索模式的中间。

_:(下滑线)通配符，用途与‘%’一样，但是下划线只匹配单个字符而不是多个字符。也就是说，不能多也不能少，必须是一个字符。

举个例子：’—%‘可以用来匹配至少包含三个字符的字符串。

**技巧：**1，不要过度使用通配符。2，在确实要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处，把通配符置于搜索模式的开始处，搜索起来是最慢的。

REGEXP:REGEXP关键字告诉MySQL，REGEXP后面跟的东西作为正则表达式处理。

.:(点号)是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符。

MySQL创建数据库：CREATE DATABASE IF NOT EXISTS 数据库名;

MySQL创建新关系（table)：CREATE TABLE 关系名

（属性名1 属性域1，

…

<完整性约束1>,

…

<完整性约束k>);

完整性约束:

primary key(属性名…)：表明该属性构成关系的主码，主码属性必须非空且唯一

foreign key(属性名…) references 对应的关系名（属性名…):表明这个表上的外码约束

not null:加在特定的属性域后，表明在该属性上不允许空值，换句话说，此约束把空值排除在该属性域外

unique(属性名…):表明在关系中没有两个元组能在所有列出的属性上取值相同

check(谓词…):例如check(budget>0)；check(semeter in (‘summer’,‘spring’,‘fall’,‘winter’))；

多模式匹配下，他会将选定的几个关系中的属性全部加起来，然后把这些属性中相互没有关联的元组组合起来，注意，如果关系a和关系b组合，那么：关系a中的每一个元组和关系b中的所有元组都要进行组合

如a :1 2 ,b:1 2 3,那么将会形成一个11,12,13,21,22,23的大的关系（称为笛卡尔积），这是from语句形成的结果，我们select语句也是从这个新的关系中查找符合条件的数据。

自然连接：不同于笛卡尔积，自然连接运算作用于两个关系，它只考虑在两个关系上都出现的属性中取值相同的元组对，打个比方：关系a中有属性（id,name) 关系b中有属性（id,score)，自然连接只考虑这两个关系中id上取值相同的元组对，并把它们对应连接起来。

语法为：关系A natural join 关系B，这个运算的结果是一个新的关系。（注意所有同名的属性都得相同）

与之对应，下列语法允许我们来指定需要哪些属性相等：关系a join 关系b using(属性列表)

只要属性列表中的属性上两个关系中的元组对应相等即可连接。

更名运算：我们可以对一个select的结果关系中的属性的名称重命名，通过使用如下形式的as子句

old_name as new_name

as子句既可出现在select子句中，也可出现在from子句中

如：select name as teacher_name

from teachers;

这样返回的关系中属性名就是teacher_name。

如果放在from子句中，可以简化语句：

select T.name,S.course_id

from instructor as T, teaches as S

where T.ID = S.ID;

注意，是先执行from生成新的关系，再是where，最后才select，所以可以先把名字给改掉。

而且改名有一个很重要的原因：如果我们要比较同一个关系中元组的情况，就必须把他重新命名，如下

select distinct T.name

from instructor as T,instructor as S

where T.salary >S.salary and S.dept_name = ’Biology‘

即：找出满足比biology系教师最低工资（因为只要比某一个biology系教师工资高即可，实际上就是高于最低工资）高的所有教师的姓名。

如果用instructor.salary就显然会引发冲突。

在上述查询中，T,S可以被认为是instructor关系的别名，在标准中称作相关名称，但通常也称表别名，或者相关变量，元组变量。

字符串运算：sql使用一对单引号来标识字符串，如果单引号是字符串的组成部分，那么就使用两个单引号字符来标识，比如it’s应写为’it’‘s’,在标准中，字符串的相等运算是大小写敏感的，但是MySQL的默认匹配是不区分大小写，但是这种默认方式可以被修改。

为了使模式中能够包含特殊模式的字符，SQL允许定义转义字符，转义字符直接放在特殊字符的前面，表示该特殊字符被当成普通字符，语法如下：

like ‘ab%cd%’ escape ‘’ :匹配所有以“ab%cd"开头的字符串

like ‘abcd%’ escape ‘’ :匹配所有以“abcd”开头的字符串

sql允许我们用记号（v1,v2,…vn)来表示一个分量为v1,v2…vn的n维元组，在此元组上可以进行运用比较运算符，例如：（a1,a2）<= （b1,b2) 在a1<= b1且a2<=b2时为真。利用这个可以简化查询语句。

集合运算：

sql作用在关系上的union，intersect，except运算对应于数学集合论中的并集，交集，和集合的差

语法：关系A union/intersect/except 关系B ，结果仍然是一个关系。

union运算自动去除重复，如果我们想要保留所有重复，就必须使用union all来代替union

intersect运算自动去除重复，要想保留，就使用intersect all来代替intersect

except运算自动去除重复，要想保留，就使用except all来代替except

聚集函数：

平均值：avg

最小值：min

最大值：max

总和：sum

计数：count

sum,和avg的输入必须是数字集，但是其他运算符可以作用在非数字数据类型的集合上，比如上说字符串。

基本聚集：

查询找出Computer Science系教师的平均工资：

select avg(salary) as avg_salary

from instructor

where dept_name=‘Comp.Sci’;

//如果使用聚集函数时想要去除重复元组，以上述语句为例，应写为：select avg(distinct salary)

注意：SQL不允许在使用count（*）时使用distinct，允许count(distinct 特定属性名)

分组聚集：

group by子句：在该子句中给出的一个或者多个属性是用来构造分组的，在group by子句中给出的所有属性上取值相同的元组将被分在一个组中

查询每个系的平均工资：

select dept_name,avg(salary) as avg_salary

from instructor

group by dept_name;//在总的元组集中，系名相同的被单独分为一组，然后在这组元组集上执行聚集函数。

查询每个系在2010年春季学期讲授一门课程的教师人数：

select dept_name,count (distinct ID) as instr_count

from instructor natural join teaches

where semester = ‘Spring’ and year = 2010

group by dept_name;

注意：当SQL查询使用分组时，我们必须保证出现在select语句中但是没有被聚集的属性只能是出现在group by子句中的那些属性，换句话说，任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数内部，否则该查询是错误的。

原因很简单，因为聚集函数的结果对于每个分组值输出一个元组，但是对于不在group by子句中的属性可能是有多个值的，比如说

select dept_name,ID,avg(salary)

from instructor

group by dept_name

对于按系名分的每个组，avg（salary）是只有一个确定的值的，但在每个组中，可能存在多个老师，多个ID,此时无法确定选定哪个ID值作为输出，因此这样的语句是错误的。

有时，对分组限定条件比对元组限定条件更有用。//where是在元组上设定条件。

having子句：这个子句可以针对于group by子句构成的分组给出条件

查询教师平均工资超过42000美元的系：

select dept_name,avg(salary) as avg_salary

from instructor

group by dept_name

having avg(salary) > 42000; //这一个条件是对于group by分出的元组集上执行的，having子句中的谓词在形成分组时才起作用。

包含聚集，group by或having子句的查询的含义可通过下述操作序列来定义

1.与不带聚集的查询情况类似，最先根据from子句来计算出一个关系。

2.如果出现了where子句，where子句中的谓词将应用到from子句的结果关系上

3.如果出现了group by子句，满足where谓词的元组通过group by子句形成分组，如果没有group by子句，满足where谓词的整个元组集被当成一个分组。

4.如果出现了having子句，他将应用到每个分组上，不满足having子句谓词的分组将被抛弃

5.select子句利用剩下的分组产生出查询结果中的元组，即在每个分组上应用聚集函数来得到单个结果元组。

为了说明在同一个查询中同时使用having子句和where子句的情况，我们考虑以下查询：

对于在2009年讲授的每个课程段，如果该课程段有至少两名学生选课，找出选修该课程段的所有学生的总学分(tot_cred)的平均值：

select course_id,semester,year,sec_id,avg(tot_cred) //semester:学期

from takes natural join student //通过ID,将course_id,sec_id,semester,year,grade,name,dept_name,tot_cred连接到一个关系里

where year = 2009 //限定year属性为2009

group by course_id,semester,year,sec_id //将course_id,semester,year,sec_id相同的元组合为一组，得到元组集

having count(ID) >= 2; //在元组集中选定ID>=2的分组

对空值和布尔值的聚集：

聚集函数根据以下原则处理空值：除了count（*）外所有的聚集函数都忽略输入集合中的空值。规定空集的count运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值。

bool数据类型可以取true，false，unknown三个值，有两个聚集函数:some和every。它们可以用来处理bool值的集合。

嵌套子查询：

（1）集合成员资格：SQL允许测试元组在关系中的成员资格。连接词in测试元组是否是集合中的成员，集合是由select子句产生的一组值构成的。连接词not in则测试元组是否不是集合中的成员。

查询：找出在2009年秋季和2010年春季学期同时开课的所有课程

写法一：

(select course_id

from section

where semester = ‘Fall’ and year = 2009)

intersect

(select course_id

from section

where semester = ‘Spring’ and year = 2010);

写法二：

select course_id

from section

where semester = ‘Fall’ and year = 2009 and

course_id in (select course_id

from section

where semester = ‘Spring’ and year = 2010);

in和not in操作符也能用于枚举集合。下面的查询找出既不叫"Mozart",也不叫"Einstein"的教师的姓名：

select distinct name

from instructor

where name not in (‘Mozart’ , ‘Einstein’);

我们还可以测试任意多个属性的关系中的成员资格：

select count (distinct ID)

from takes

where (course_id, sec_id,semester, year) in (select course_id,sec_id,semester,year

from teaches

where teaches.ID= 10101);

//在takes关系中，且在（teaches关系中，满足ID=10101的course_id,sec_id,semester,year属性集合）中的course_id,sec_id,semester,year属性集合对应的ID属性的数据数量。

（2）集合的比较：

短语“至少比某一个要大”在SQL中使用 >some 来表示

查询：找出工资至少比Biology系的某一个教师的工资要高的所有教师的姓名。

select name

from instructor

where salary > some(select salary

from instructor

where dept_name = ‘Biology’);//BIology系的薪水的集合

注意： =some 意为：至少和其中的一个相等，即等价于in

<> some 意为：至少和其中的一个不相等，不等价于not in

<>all意为：全部都不同，即等价于not in

=all：意为全部都相同，但是in只需要有一个相同就可以了，所以不等价于in

发现了吗：some实际上等价于数学集合中学的"存在"，而all实际上等价于数学集合中学的"任意"。

（3)空关系测试：

使用exists子句可以将作为参数的子查询非空时返回true，空时返回false。

查询：找出在2009年秋季学期和2010年春季学期同时开课的所有课程

select course_id

from section as S

where semester = ‘Fall’ and year = 2009 and

exists (select *

from section as T

where semester = ‘Spring’ and year = 2010 and S.course_id = T.course_id)

（4）重复元组存在性测试：

SQL提供了一个布尔函数，如果作为参数的子查询结果中没有重复的元组，unique结构将返回true值

查询：找出所有在2009年最多开设一次的课程

select T.course_id

from course as T

where unique(select R.course_id

from section as R

where T.course_id = R.course_id and R.year = 2009);

(5)from子句中的子查询：

查询：找出系平均工资超过42000美元的那些系中教师的平均工资

select dept_name,avg_salary//找系名和平均工资

from (select dept_name,avg(salary) as avg_salary//找系名和平均工资

from instructor//在instructor关系中

group by dept_name)//将相同系的分在一组

//这三行构成了一个新的关系

where avg_salary > 42000;//我们再在这个新关系上看大于42000的

注意：from子句嵌套的子查询中不能使用来自from子句中其他关系的相关变量，而允许from子句中的子查询用lateral作为前缀，以便访问from子句中在它前面的表或者子查询中的属性

查询：每位教师姓名，以及他们的工资和所在系的平均工资

select name, salary, avg_salary

from instructor L1 , lateral (select avg(salary) as avg_salary //加上lateral以便访问L1

from instructor L2

where L2. dept_name = L1. dept_name) ;

(6)with子句：

with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效

查询：找出所有工资总额大于所有系平均工资总额的系

with dept_total (dept_name, value) as//第一个临时关系

(select dept_name, sum(salary)

from instructor

group by dept_name)，

dept_total_avg(value) as //第二个临时关系

(select avg(value)

from dept_total)

select dept_name

from dept_total , dept_total_avg //在这两个临时关系中查询

where dept_total. value >= dept_total_avg. value ;

（7）标量子查询：

查询：筛选薪资高于平均薪资的员工

SELECT emp_id, emp_name, salary,

(SELECT AVG(salary) AS average_salary//获取平均薪资

FROM employees)

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

数据库的修改：

1、删除元组：

delete命令：delete命令可以从关系中删除元组（表中所有的数据，关系还在)，语法如下：

delete from 关系名;//可以搭配where语句使用

注意以下语句:

delete from instructor

where salary < (select avg (salary) //先测试出哪些要被删，再删除，不然一边删平均值一边变

from instructor) ;

sql中在执行任何删除之前会先进行所有元组的测试，然后删除所有符合条件的元组（测试就像打了个标记）

2、删除关系：

drop命令：drop命令可以从数据库中去除一个关系，它不仅删除元组，还删除模式，语法如下：

drop table 关系名;

drop database 数据库名;

3、增加属性：

alter table命令：alter table命令为已有关系增加属性，关系中的所有元素在新属性上的取值将被设为null。语法如下：

alter table 关系名 add 属性名属性域;

也可以通过 alter table 关系名 drop 属性名;来从关系中去掉属性，但许多数据库系统不支持。

4、插入元组：

insert命令：insert命令可以将数据加载到关系中，语法如下：
insert into 关系名

values(对应属性列表的值);

一般来说，我们可能想在查询结果的基础上插入元组：

假设我们想让Music系每个修满144学分的学生成为Music系的教师，其工资为18000美元：

insert into instructor

select ID, name, dept_name, 18000

from student

where dept_name = ‘Music’ and tot_cred > 144 ;

我们没有指定一个元组，而是用select选出一个元组集合，然和把这整个元组集合插进去

注意：在执行插入之前先执行完select语句非常重要

否则，如果student上没有主码约束：

INSERT INTO student SELECT * FROM student;就可能会插入无数元组，因为select出来的可能一直是第一个元组，如果select语句先执行完，那么这个insert语句就只是把student关系中的每个元组复制一遍。

5、更新元组：

update命令：update命令可以将关系中的特定属性的数据更新，语法如下：

update 关系名

set 属性名 …//省略号描述这个属性是如何变化的

//常搭配查询语句使用

这里也提供了case结构，一般格式如下：

case

when pred1 then result1

when pred2 then result2

…

else result0

end//相当于很多的if…else

2025年.sql文件的作用（sql文件的作用）

相关推荐