l数据库基础前四章总结（增删改查基础命令语句的的使用）

 

我是 MySQL 初学者，通过对前四章的基础知识学习，我认为其前四章的精华所在可以用四个字加以概括---增、删、改、查

常用最基本的语句（下面是最简单的方式，且在My SQL中不区分大小写，其中可省略部分已经省略，如需更多读者可自行查寻MySQl帮助文档）【注：以下语句中xs ，kc，xs_kc 表参照My SQL附录 】
一、增（创建，添加）

创建
1、创建数据库
   create database DB;   //创建一个名为DB的数据库，其中database 为建库关键字

2、创建表
   create table DB_b (    
字段一 int  not null primary key,  
                   字段二 varchar(8)  null
                   );
  //创建表 名为DB_b{说明：数据库及表名可随意命名，符合命名规则既可}， 其中table 为建表关键字
  // 字段一 ：为字段名 ；int ：为该字段数据类型 ； primary key ：声明该字段为主键(当该列声明为主键时，其默认值就一定不能为空)
  //字段二 ：为字段名 ；varchar（8） ：为该字段数据类型，为char类型但是加了var 说明该字段值长度为可变的，（8）为默认长度
3、创建视图
  （1） use xscj
        create view shitu
        as
        select *
        from xs;
  // use xscj 命令转到xscj 数据库
  // create view shitu   创建名为shitu 的视图 其中 view 为建视图关键字
// * 是所有信息的意思，就是将xs表中的所有信息作为视图信息
        //xs 为xscj数据库中的表 为视图来源
（2）
create view shitu1(姓名,性别)
 as
 select 姓名,性别
 from xs
 where 学号 = '081101';
    // shitu1(姓名,性别) 中的 性别、姓名 为视图 属性
  //  select 姓名,性别  为将要从xs表中获取的属性
  // where 为获取条件
添加
1、添加表属性
   alter  table  xs
add 年龄 int default 18 ;
//其中alter为修改的关键字，但是有了add 后 就为添加属性了
2、添加表数据
  insert xs(学号,姓名,出生日期) values ('081199','张三','1990-09-08');
    //insert 后为表名 表名后的可以加括号，也可以什么都不加，当不加时，为默认添加所有内容，当加了括号时，括号里的指定的是要添加的属性，记住values 后的是属性值，不管前面的表名加不加括号指定属性其中属性值都要和前面属性名要一一对应
添加表数据和添加表属性使用的关键字是不同的
3、添加视图数据
insert shitu values ('081125','李牧','计算机','1990-09-08',50,null,null);
// 向视图添加数据时，和向表中添加数据的语法相同
// 值得注意的是当通过对视图做出相应操作，也就是对该视图的基表做出了相应的修改（满足更新条件的前提下）

二、删
1、删除数据库
   drop database xs;
2、删除表（和表属性）
   drop table xs;
// 删除表
alter table xs
drop 姓名;
//删除表属性
3、删除表数据
   delete from xs
where 姓名 = '王燕';
// 只删除符合where条件的数据
4、删除视图（和属性）
   drop  view  shitu;
5、删除视图数据
  delete from shitu
where 姓名 ='李牧';
//delete

三、改
1、修改数据库
    alter database DB
character set gb2312;
// alter 为修改关键字
//将DB数据库的字符集编码改为gb2312；
2、修改表属性
   alter table db_b
 modify 字段一 char
change  旧列名  新列名 列属性;
   // 将表db_b 中属性字段一的数据类型改为char类型
// modify 为改字段属性专用关键字
//具体该法：在modify 关键字后 重写改后内容即可，没重写的就依用原来的
3、修改表内数据
update xs
 set 总学分 = 80
 where 学号 = '081101';
//update 为修改表数据关键字 其后面跟的是将要被修改的表名
// set 为修改关键字 其后面 是修改的内容,当修改内容为多条时，每条之间用逗号隔开
// where 限定修改条件 当有where 限定时,只修改符合条件的记录，没有where 条件限定 修改的是整个表的该属性值
 修改表属性和修改表数据的关键字是不同的
4、修改表名（两种方法，记住即可）
alter table xs
 rename sx;
// alter table xs 指向将要被修改的表
// rename 为修改表明关键字 其后跟新表名

rename table kc to ck;
// to 前 是原来表名  后是新表名
5、修改视图数据
       update shitu
set 专业名 = '3G'
where 学号 ='081101';
//如何修改表数据就如何修改视图数据，这里不再重复
四、查
单表查询：数据源只有一个表
1选择列
select * from xs;
select 学号,姓名 from xs;
select 学号 as sno,姓名  as name  from xs;
select 学号,总学分*1.2 as 新学分 from xs;

select 姓名,case
when 性别=1 then '男'
when 性别=0 then '女'
end  as 性别,专业名
from xs;

2  选择行

where条件
1）比较运算符：= >  >=   <   <=  <>   !=  <=>

2) 逻辑运算符    and   &&     or  ||  not  !

查找计算机系的男学生的信息
select * from xs  where  专业名='计算机' and  性别=1

3）模式匹配：like    %   _
select * from xs where 姓名 like '王%';         ESCAPE 转义字符 关键字    

4)范围比较
between  ：成绩 between 80 and 90     (成绩 >=80  and 成绩<=90)
not  between   :  select * from xs_kc  where  成绩  not between  80 and 90
 

查看产地是广州，上海，北京的产品信息
select * from product
where 产地='广州' or 产地='上海' or  产地='北京';
in:     产地  in ('广州','上海','北京')
查看关键字与列表中的任何一个值匹配，就返回true

not in:      产地 not in ('广州','上海','北京')

5)空值比较
is  null
is not null

select * from xs where 备注 is not null;

6)去掉重复的行：distinct

select  distinct 专业名 from xs;

 

3 对查询结果排序
order by 子句：
升序：asc  （默认）
降序:desc
如果是按多个字段排序，先按第一个字段排，当第一个字段的值相同时，在按第二个排。

4 limit子句：限制结果集中的行数

一般limit子句放在select语句的最后
limit 5   ：表示返回结果集的前面5条记录
limit 3,5：表示返回从第4行开始的5条记录

 

4 分组：分类汇总
聚合函数：
count(*)：统计记录的条数 
count(字段名):统计字段中有值的记录个数。（不考虑null）
count(distinct 字段名): 去掉重复值后在计算有值的个数

max(字段名):计算某一列最大值
min(字段名):计算某一列最小值

sum(字段名):求和
avg(字段名):求平均值

统计xs表中的记录数：select count(*) from xs

查询选修了课程的学生人数
select count(distinct 学号) as 人数 from xs_kc;

查询选修101课程的学生的最高分

select max(成绩) as 最高分 from xs_kc
where 课程号=101;

分组：group by 字段名：
根据字段的值对记录进行分组

group  by 性别

select 性别,count(*) as 人数 from xs
group by 性别;

分组后可以看哪些字段：一般是分组的字段，和使用聚合函数的列。

group by 字段名1，字段名2

select 专业名,性别,count(*) as 人数
from xs
group by 专业名,性别
with rollup; //汇总

 

查询平均分是大于77的课程号，和相应的平均分。

select 课程号,avg(成绩) as 平均分
from xs_kc
group by 课程号
having avg(成绩)>77;  //  分组后筛选

使用分组后在进行挑选   having关键字

having 与where的区别：where是对原始记录进行挑选，跟在from后。
having：对分组后的记录进行挑选，跟在group by 后。

select 学号  from xs_kc
where 成绩>=80
group by 学号
having count(*)>2

查询每个学生的学号和选课数
select 学号,count(*) as 选课数
from xs_kc
group by 学号;

查询选修了2门课程以上的学生学号。

select 学号
from xs_kc
group by 学号
having count(*)>2;

查询每门课程号及其选课人数。

select  课程号,count(*) as 人数
from xs_kc
group by 课程号;
多表查询：数据源是多个表

要求：查询每个学生的姓名，所在系，课程名，成绩：3个表

要求：查询每个学生的姓名，所在系，课程号，成绩：2个表

做多表连接：要找到连接的条件，2个表的公共字段（属性）

两种：
1.全连接：等值连接
select  姓名,所在系,课程号,成绩
from xs,xs_kc
where    xs.学号=xs_kc.学号;  (等值连接)

要求：查询每个学生的学号，姓名，课程号，成绩：
1.考虑数据源  ：xs：学号  姓名
             xs_kc:课程号  成绩
 2.连接条件：两表的学号相等   xs.学号=xs_kc.学号

当查看的列在多个表中都存在的话，必须在字段名前加上表名进行限制。
select xs.学号,姓名,课程号,成绩
from xs,xs_kc
where xs.学号=xs_kc.学号;

 

要求：查询每个学生的姓名，专业名，课程名，成绩
数据源：xs：姓名，专业名              kc：课程名     xs_kc  :成绩
连接条件：xs.学号=xs_kc.学号   and   kc.课程号=xs_kc.课程号

select 姓名,专业名,课程名,成绩
from xs,kc,xs_kc
where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号;

select 姓名,专业名,课程名,成绩
from xs,kc,xs_kc
where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号
and 课程名='计算机基础' and 成绩>=80
order by 成绩 desc;

第二种方式：join连接
内连接：inner join：满足连接条件的记录出现在结果中。
外连接：左外连接：left [outer] join 左边表的所有记录和右边表满足条件的记录
        右外连接： right  [outer] join右边表的所有记录和左边表满足条件的记录

内连接：  from 表名1  inner join 表名2 on 条件

要求：查看学生的学号，姓名，课程号，成绩。

select xs.学号,姓名,课程号,成绩
from xs inner join xs_kc
on xs.学号=xs_kc.学号;

select xs.学号,姓名,课程号,成绩
from xs,xs_kc
where xs.学号=xs_kc.学号;

要求：查看课程号是101的学生的学号，姓名，课程号，成绩。

select xs.学号,姓名,课程号,成绩
from xs inner join xs_kc
on xs.学号=xs_kc.学号
where 课程号='101';

要求：查询每个学生的姓名，专业名，课程名，成绩

select 姓名,专业名,课程名,成绩
from xs inner join xs_kc on xs.学号=xs_kc.学号
inner join kc on xs_kc.课程号=kc.课程号;

select 姓名,专业名,课程名,成绩
from xs inner join xs_kc on xs.学号=xs_kc.学号
inner join kc on xs_kc.课程号=kc.课程号
where 课程名='计算机基础' and 成绩>=80;

如果是三张表：from  表1 inner join 表2 on 条件1
                                inner join 表3 on 条件2

from  表1，表2，表3
where 条件1   and  条件2

自连接：
雇员表（a）：编号，姓名，性别，领导编号
                                   1            aa        1           
                                  2          bb          0           1
                                   3          cc           1            1

雇员表（b）：编号，姓名，性别，领导编号
                                  1            aa        1           
                                  2          bb          0           1
                                  3          cc           1            1

 

查询每个雇员的名字和他的领导姓名  
select a.姓名,  b.姓名 as 领导姓名
from 雇员表 as a   join 雇员表 as  b
on     a.领导编号=b.编号

给表加别名：
from  表名 as 别名
注意：如果给表加了别名，以后的使用必须用别名，不能再使用原表名

select s.学号,姓名,专业名,课程名,成绩
from xs as s  inner join xs_kc  as  sc  on s.学号=sc.学号
inner join kc as c on sc.课程号=c.课程号
where 课程名='计算机基础' and 成绩>=80;

将计算机专业的学生的“计算机基础”课程成绩按降序排列。
查询结果包括 姓名，课程名，成绩
略
查询每门课程各专业的平均成绩。每门课程的总平均成绩和
所有课程的总平均成绩
略
关键字总结
在MySQL 中,不区分大小写字母
总体查看
Show
   | databases;  //查看所有数据库名字
   |dabbles;    // 查看所在数据库的所有表名；
   |Columns from 表名；// 查看所指向表 字段属性

Create
   | database DB; //创建数据库 DB；
   | table TB；   //创建表 TB；
   | view VW；  // 创建视图 VW；

Alter
   | database DB; //修改数据库 DB；
   | table TB；   //修改表 TB；
   | view VW；  // 修改视图 VW；

Drop
 | database DB; //s删除数据库 DB；
   | table TB；   //删除表 TB；
   | view VW；  // 删除视图 VW；

Delete
   |from 表名；
   |from 视图名；
    也是删除，但其操作对象是数据
 
Select
 该关键字应用灵活，但在应用时 要用 from 指向数据源