数据库知识（简略版）

数据库知识（简略版）

DDL:数据库操作

创建数据库：

create database [dbname]

创建表：

create table tablename(columnname datatype(length) null/not null primary key)

创建外键：

alter table [tablename] add CONSTRAINT [约束名称] foreign key [column] reference tablename(columnnuam) 

DML：数据操作语言

insert

insert into table(columns...)values(.....)

==注意：自增长的数据在插入的时候不写。==在MySQL中，字符串类型和日期类型都要用单括号括起来。空值使用null表示

如：

'abc'  '2016-01-01' '2016-01-01 00:00:00:000'

注意：

1、插入到数据应与字段的数据类型、个数、顺序要一一对应。

2、在values中列出的数据位置必与被加入的列的排列位置相对应。

3、数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

4、字符和日期型数据应包含在单引号中。

5、如果插入空值，请使用null，也可以不写入字段的值，直接将字段的默认值设为null。

单行插入

一次向数据表中只插入一条记录，这是最常见的操作情形。

例如，使用 insert语句向 employee表中插入一条数据。

insert into employee id, name, gender, birthday, entrydate, job, salary, RESUME） values
（9527,'zhangsan','男','1986-0101','201802-02-05','程序员',8000,'工作负责');

多行插入

这是将多条记录插入到当前数据表中.

 INSERT  INTO 表名 [ (属性列表) ] 
	VALUES(取值列表1),(取值列表2)
	… ,
	(取值列表n) ;

**例：**向sc表同时插入3条记录

 insert into sc values('7', 'e', 90),
	('8', 'f' , 80),
 	('9', 'g', 85)  

例：向sc表的sno,cno同时插入3条记录

 insert into sc(sno,cno) values('10', 'e'),
	('11', 'f'),
	('12', 'g')

多行插入（子查询）

这是将一个子查询的结果插入到当前数据表中。注意：子查询返回的列的个数和顺序要和 insert语句中列的个数的顺序要匹配。

例如， backup_employee和 employee有相同的表结构，只是表名不同。

insert into backup_employee(name, job, manager, hiredate salary, bonus, deptid)
(select name,job, manager, hiredate, salary, bonus, deptid FROM employ);

例如， backup_ other employee和 employee的表结构不相同， backup other employee中只有两个字段（ id name），并且id不是自增长的.

insert into backup_other_employee(id, name)
	(SELECT id, FROM employee);

这种插入方式不需要两个表的结构相同，只要子查询结果的列和被插入表中的列相同即可。

update

使用update语句修改表中的数据。 UPDATE可用于更新单一行或多行，一切交给WHERE子句决定 。

UPDATE语句的语法格式：

UPDATE    table_name 
	SET 
	column_name1 = expr1,
	column_name2 = expr2,
   ...
WHERE
   condition;  

在上面UPDATE语句中：

首先，在UPDATE关键字后面指定要更新数据的表名。

其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。

第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行。

例：将a号课程的成绩修改为0。

update sc set  grade=0  where cno='a'

例：将所有选课成绩修改为0

 update sc set  grade=0  

注意：

**修改条件：**修改操作时不可逆的操作，所以在执行修改语句的时候一定要再三确定修改条件。一般在项目中采用主键进行精确条件确认。

Delete

格式 ：

  delete  from  表名  where   条件表达式 

例：删除stu表中数学系的学生信息

  delete  from  stu  where sdept='MA'

例：删除sc表中的所有记录

   delete  from sc

注意：

1、没有删除条件会删除全部的数据

2、删除条件一般为主键，做到精确删除。

3、在系统中，一般不做物理删除（delete），而是假删除（在表中增减一个字段置为不可用）

DQL：数据查询语言

1．列出表的所有字段

格式：

select  字段名1,字段名2,...,字段名n  from 表名

例：查询学生的个人信息

select  sno , sname , sex , sage from s 

2．使用“*”查询所有字段

格式：

select  *  from  表名

例：查询学生的个人信息

select  *  from  s 

3 查询指定字段

格式：

select 字段名1，字段名2，...,字段名s   from 表名

例：查询学生的学号和姓名

 select  sno , sname  from  s 

4 带条件的查询
格式 ：

select  目标列表达式  from 表名  where  条件表达式  (字段名θ{字段名/常量}) 

（1） θ可以为: =, <, >, >=, <=, !=, <>
例：查询学号为”2015144101”的记录

select   *   from    s   where  sno ='2018123112'

​ 例：查询CS系的学生姓名

 select  sname  from  student  where  sdept=‘cs’;

​ 例：查询年龄小于20的学生姓名和年龄

select    sname ,  sage   from  student   Where   sage<20  ;

（2） 指定范围：字段名[not]between {字段名/常量}and{字段名/常量}
例：查询年龄在20—23岁之间的学生的姓名、系别和年龄

Select  sname,sdept,sage  from  student  where sage  between   20 and  23;

​ （3） 指定集合：in 、not in
例：查询计算机科学系、数学系和信息系学生的姓名和性别

 select   sname,  ssex   From   stu    where   sdept   in('CS','MA','IS');
         select  sname,  ssex  From stu   where sdept not in('CS','MA','IS')

​ （ 4） 匹配字符串 like 、not like
％：任意长度的字符串
－：任意单个字符
例：查询所有姓刘的学生的姓名、学号和性别

select  sname,sno,sex  from  s  Where  sname  like  '刘％'

例：查询姓“欧阳”且全名为3个汉字的学生的姓名

select   sname,sno,sex  from  s  where   sname  like   '欧阳_'

​ （5 ） 是否为空值 is null、 is not null
​ 例：查询选课成绩为空的学号和课程号

 select   sno ,cno  from  sc  where  grade   is    not  null;

​ 例：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT  sno ,cno   FROM   SC    WHERE   Grade   IS NULL；

​ （6） 多个查询条件 and 、 or
​ 例：查询计算机系年龄在20岁以下的学生姓名。

 SELECT  Sname  FROM  Student  WHERE  Sdept= 'CS'  AND  Sage<20；

​ 例：查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别

 Select  Sname,   Ssex    from    stu
   Where  Sdept= ' IS ‘  or   Sdept= ' MA’ or  Sdept= ' CS '

（7） 查询结果不重复

----使用distinct关键字删除重复值

例：查询选过课的学生学号

select  distinct  sno   from  sc

（8） 对查询结果排序

为了使查询结果顺序满足用户的要求，可以使用order by关键字对记录进行排序，语法规则如下：

 select 目标列表达式 from 表名 where 条件表达式 order by 属性名 ASC | DESC  

属性名：按该字段排序，ASC参数表示按升序的顺序进行排序；DESC参数表示按照降序的顺序进行排列，默认情况按照ASC方式进行排序。

例：按照学生年龄升序排列查询学生信息

 select   *   from   stu   order  by   sage  ASC

例：按照学生学号降序排列查询学生的学号、姓名、所在系

select  p_phone, sname, sdept from stu  order by  p_phone DESC 

升序排列时，如果排序字段的值为空值时，这条记录将排在最前面，可以理解为空值时是该字段的最小值，而按降序排列时，排序字段为空值的记录将最后显示。MySQL中，可以指定按多个字段A1,A2进行排序，排序过程中,先按照A1字段排序，再按照A2字段排序， A1字段相等的按照A2字段排序。

聚合函数

​ 集合函数包括COUNT()、SUM()、AVG()、MAX()和MIN()。其中，COUNT()用来统计记录的条数；SUM()用来计算字段的值的总和；AVG()用来计算字段的值的平均值；MAX()用来查询字段的最大值；MIN()用来查询字段的最小值。当需要对表中的记录求和、求平均值、查询最大值、查询最小值等操作时，可以使用集合函数。例如，需要计算学生成绩表中的平均成绩，可以使用AVG()函数。GROUP BY关键字通常需要与集合函数一起使用。本节中将详细讲解各种集合函数。

COUNT( )函数:用来统计记录的条数。如果要统计employee表中有多少条记录，可以使用COUNT( )函数。如果要统计employee表中不同部门的人数，也可以使用COUNT( )函数。

例：下面使用COUNT( )函数统计学生表的记录数。

 select   count(*)  from   stu ;

**SUM( )函数:**是求和函数，使用SUM( )函数可以求出表中某个字段取值的总和。

例: 查询学号为001同学的学生成绩。

select sno, sum(grade)  from  sc where  sno=‘001’

例：

 select   d_id,  count(*)   from   examplee  group by d_id; 

**AVG( )函数:**是求平均值的函数。使用AVG()函数可以求出表中某个字段取值的平均值。

例：查询学生的平均年龄

  select   avg(sage)  from  stu

例：查询每个同学的选课平均成绩。

  select  sno, avg(grade) from  sc group by sno

MAX( )函数: 是求最大值的函数。使用MAX()函数可以求出表中某个字段 取值的最大值。 MAX()函数可以计算字符和字符串的最大值， MAX()函数是使用字符对应的ASCII码进行计算的。

例：查询学生的最大年龄

  select max(sage) from stu

例：查询sc表中不同科目的最高成绩

select  cno ,max(grade) from sc  group  by cno 

•**MIN( )函数：**是求最小值的函数。使用MIN()函数可以求出表中某个字段取值的最小值。

例：查询学生的最小年龄

select  min(sage)  from stu

例：查询选课表中各科的最低成绩。

  select cno , min(grade) from sc group by cno 

连接查询

​ 连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或两个以上的表时使用的。当不同的表中存在表示相同意义的字段时，可以通过该字段来连接这几个表。例如，学生表中有course_id字段来表示所学课程的课程号，课程表中有num字段来表示课程号。那么，可以通过学生表中的course_id字段与课程表中的num字段来进行连接查询。连接查询包括内连接查询和外连接查询。

内连接查询

内连接查询是一种最常用的连接查询。内连接查询可以查询两个或两个以上的表。为了读者更好的理解，暂时只讲解两个表的连接查询。当两个表中存在表示相同意义的字段时，可以通过该字段来连接这两个表。当该字段的值相等时，就查询出该记录。

其语法格式如下：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

在该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。

例：查询每个学生及其选修课程的情况。

 select   Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
            from   Student,SC
            where  Student.Sno = SC.Sno；

例：查询每个同学所选课程的课程信息和选课信息

select   *    from   c ,   sc    where   c.cno=sc.cno

外连接查询

​ 外连接查询可以查询两个或两个以上的表。外连接查询也需要通过指定字段来进行连接。当该字段取值相等时，可以查询出该记录。而且，该字段取值不相等的记录也可以查询出来。外连接查询包括左连接查询和右连接查询。其基本语法如下：

SELECT 属性名列表  
FROM  表名1  LEFT | RIGHT JOIN 表名2
ON  表名1.属性名1=表名2.属性名2 ;

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

1 左连接

左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。

​ 进行左连接查询，可以查询出“表名1”所指的表中所有记录，“表名2”所指的表中，只能查询出匹配的记录。

例：查询选课学生的选课信息和个人信息

select *  from sc  left join stu on sc.sno=stu.sno

2 右连接

例: 请用外链接查询每个同学的个人信息和选课信息

select *  from sc  right join stu on sc.sno=stu.sno