SQL 的小总结 嘻嘻

 

 

 

SQL 的小总结：
防止产生乱码可以插入中文：
set character＿set＿client=gb2312;
set character＿set＿results=gb2312;

 

 

 

0522  数据库和表
1.创建数据库
create database 库名

create database if not exists 库名

创建库的同时指定字符集,校对规则
create database mydb character set utf8
collate  utf8-general_ci;

显示所有数据库
show databases;

显示创建数据库的定义信息
show create dabasese 库名

2.修改数据库，只能改参数
alter database 库名character set gb2312  collate gb2312_chinese_ci;

3.删除数据库
drop database 库名
drop database if exists 库名

4.创建表
创建表就是创建表的结构：包含哪些字段（属性），字段名，字段类型

create table 表名
(字段名1 类型,
  字段名2  类型，
。。。。
)

创建一个学生表
xs:   id     name    sex   

确定你的表建在哪个库中，把此库变为当前数据库
use 库名；
create table xs
(
        id int,
        name char(10),
        sex char(2)
);

5.修改表结构：增加字段   修改旧字段   删除字段
alter table 表名
add 字段名 类型（宽度）//增加字段
modify 旧字段 类型   //修改旧字段
drop 字段名//删除字段

change 旧字段名 新字段内容//改列名

在学生表中增加birthday字段
alter table xs
add birthday date;

alter table xs
modify name varchar(20);

alter table xs
change name sname varchar(20);

6.修改表名
rename table 旧名 to 新名

7 复制表
create table 表名 like 表名1

8 删除表
drop table 表名

 

0523  表数据操作
1.防止产生乱码可以插入中文：set character＿set＿client/result＝ｇｂ２３１２‘
 

2.插入记录命令
   insert into 表名（字段名1，字段名2，。。。）
   values（值1,值2，。。。）
  

   select * from 表名 //查询表的信息

3.删除 记录命令
   delete from 表名//删除表中的所有数据，此表为空表
   delete from 表名 where 条件(逻辑表达式)//删除表中符合条件的记录

   delete from xs where 学号='081102';
  
   drop table xs；//删除整张表，结果和记录
  
   truncate table表名；//快速删除所有记录，不能恢复的
4.修改表数据（更新字段的值）
   update 表名
   set 字段名1=值1[，字段名2=值2....]
   where 条件

   update xs
   set 专业名=‘软件设计’；  
  

   update xs
   set 总学分=30
   where 学号=081103；

   update xs
   set 出生日期='1911-01-01'，总学分=40
   where 学号='081104'；
 

   update xs
   set 总学分=总学分+10；

 

 

0528
 基本查询：（单表查询）
   select  列1，列2，。。。from 表名
1.选择列
   使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔。
   1）看所有字段：*
   select *from product；
   2）部分字段：
   select pid，panme from product；
   3）给列加列标题
       当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，
   可以在列名之后使用AS子句来更改查询结果的列别名。
   字段名[as] 列标题（若列标题中间有空格的话，必须加单引号）
   select pid as 产品编号，panme 产品名称 from product；
  
   4）替换查询结果中的数据  替换查询中的结果
  格式为：
  CASE
     WHEN 条件1 THEN 表达式1
     WHEN 条件2 THEN 表达式2
     ……
     ELSE 表达式
  END

    
     //select 学号，姓名，性别 from xs；
     select 学号，姓名，
   case
     when 性别=1 then ‘男’
     when 性别=0 then ‘女’
   end as 性别
   from xs；

 

    SELECT学号, 姓名,
        CASE
          WHEN 总学分 IS NULL THEN '尚未选课'
           WHEN 总学分 < 50 THEN '不及格'
           WHEN 总学分 >=50 and 总学分<=52 THEN '合格'
          ELSE '优秀'
        END    AS 等级
    FROM XS
     WHERE 专业名='计算机';

 
  
 /* select 学号，姓名，
  case
   when 总学分 is null then '尚未选课'(总学分为空值时 写成is null）
   when 总学分 < 50 then '不及格'
   when 总学分 >=50 and 总学分 <=52 then '合格'
   else '优秀'
  end as 等级
  from xs
  where 专业名='计算机'；

  可以看做时if的条件判断句   和switch有些相同  注意条件的写法啦 
*/

5） 计算列值
   select expression[,expression]

  

  
6) 消除结果集中的重复行
   对表只选择其某些列时，可能会出现重复行。
例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。
可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行，其格式是：
 select distinct | DISTINCTROW column_name [ , column_name…]
 其含义是对结果集中的重复行只选择一个，保证行的唯一性。

 

2 选择行
1） where 条件
  比较运算符：=> >= < <= <>
 

 

逻辑运算符：and or not
  计算机系女生系记录  表中女用0表示
  select 姓名，专业名，性别
  from xs
  where 专业名='计算机'and  性别=0；

 

  查看学号是081101  和   081105 的姓名，性别，专业名
  select 姓名，性别，专业名
  from xs
  where 学号='081101' or 学号='081105'；

  查看1990年以后出生的学生的姓名
  select 姓名 ， from xs
  where 出生日期>'1990-01-01';

 

   查看不是1990年以后出生的学生的姓名
   select 姓名， from xs
   where  出生日期<'1990-01-01'or 出生日期>'1990-12-31';
   where  not(出生日期>='1990-01-01'or 出生日期<='1990-12-31');

 模式匹配：模糊查询  匹配符：%_

   %:任意多个任意字符
   _ :任意单个字符

  like运算符
  查询姓李的学生

  select 姓名 from 学生
  where  姓名 like '李%'；
 

  name中倒数第二个字母是b 的姓名
  name like'%b_';

  书名 like '%java%';
 

 

 

查询XS表中名字包含下画线的学生学号和姓名。
SELECT 学号,姓名
   FROM XS
   WHERE 学号 LIKE '%#_%' ESCAPE '#';
     说明：由于没有学生满足这个条件，所以这里没有结果返回。定义了“#”为转义字符以后，
     语句中在“#”后面的“_”就失去了它原来特殊的意义。

 

0529
1  范围比较
 between：
 成绩 between 80 and 90
        等价的

 成绩>=80 and 成绩<=90                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

   not between：select * from xs
   where  成绩 not between 80 and 90
in：in（）查看关键字与列表中的一个值匹配
 
   in     查看产地是广州上海北京的产品信息
 
   in：   产地in（'广州','上海','北京'）                                                                

2. 空值比较
is null   is not null

select * from xs where 备注 is not null；
3. 去掉重复的行
  distinct：

 select distinct 专业名 from xs

4.对查询结果排序：
  order by  默认是升序 asc   desc 是降序
  如果是多个字段排序，先按第一个排，若相同，再按第二个排；
  select  姓名，专业名，出身日期
  from xs
  where 专业名='通信工程'
  order by 性别，出生日期（升序）  desc （降序）；

5. limit 字句：用来限制结果集中的行数
   一般limit放在select的最后

   limit 5：表示返回结果集的前面5条记录
   limit 3,5：表示返回从第4行开始的5条记录
  
6. 分组：分类汇总
  
   聚合函数：

   count(*)：统计记录的条数
   统计xs表中的记录数：
   select count（*） as 总人数 from xs；
   count（字段名）：统计字段中有值的记录个数（不考虑 null值）
   count（distinct  字段名）：去掉重复值之后计算有值的个数
  

   max（字段名）：计算某一列最大值
   min（字段名）：计算某一列最小值
   select max（成绩）as 最高分，min（成绩）as 最低分 from xs；

   sum（字段名）：求和
   avg（字段名）：求平均值

   查询选修了课程的学生人数
   select   count（distinct 学号）as 人数 from xs_kc;
   
   查询选修了101课程的学生的最高分

   select max（成绩）as 最高分 from XS；
   where 课程号=101；

 

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

   select 性别，count（*）as 人数 from xs
   group by 性别；
   分组后可以看哪些字段：一般是分组的字段，和使用聚合函数的列。
  

   group by  字段名1， 字段名2

   select 专业名，性别，count（*） as 人数 fromxs
   group by 专业名，性别；
 

 

   select 专业名，性别，count（*）as 人数 fromxs
   group by 专业名，性别
   with rollup；

  
使用分组后在进行挑选使用having 关键字
   查询平均分大于77的课程号和相应的平均分
   select 课程号，avg（成绩）as 平均分
   group by 学号
   from xs_kc
   having avg（成绩）>77；
  
  having和where的区别：
  where  是对原始记录进行挑选，跟在from后
  having 是对分组后的记录进行挑选跟在group by 后

 
  查询选修课程超过2门的且成绩在80分以上的学生的学号
  select 学号 ，count（*） from xs
  where 成绩>80
  group by 学号
  having count（*）>2；

 
  select 学号 ，avg(成绩), count（学号） from xs
  group by 学号
  having count（*）>2, avg(成绩)>80；

 

 

0530多表查询:核心价值是找出多个表中的存在的连接条件，
             找出内在的关系

 把新表的内容插入到另一个表中的语句：
 insert into new_stedent（sno，name）
 select 学号，姓名from xs
 where 性别=1；
 高级查询：
 多表查询：数据源是多个表
 要求：查询每个学生的姓名，所在系，课程名，成绩：3个表
 

 要求：查询每个学生的姓名，所在系，课程号，成绩：2个表
 做多表连接：要找到连接的条件，2个公共字段（属性）                                                    ）
 两种：
 一。 全连接：
  select 姓名，所在系，课程号，成绩
  from xs，xs_kc;
  where xs.学号=xs_kc.学号；（等值连接）

 查询每个学生的学号，姓名，课程号，成绩
 
  1 考虑数据源：xs：学号 姓名
                xs_kc:课程号，成绩；
  2连接条件： 学号相等
 
   当查看的列在多个表中存在的话，必须在字段名前加上表名进行限制
  select xs. 学号，姓名，课程号，成绩
  from xs，xs_kc;
  where xs.学号=xs_kc.学号；

 

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

 
 数据源：xs：姓名，专业名
     kc：课程名
     xs_kc：成绩

 连接条件：
     xs.学号=xs-kc.学号 and kc.课程名=xs-kc.课程名
 

2.第二种方式： 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.学号；

 

 

 

 

 

  
如果是三张表：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 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.课程号；
 where 课程名='计算机基础'and 成绩>=80;

  select a.cname.课程名b.cname as 先修课名
  from course as a join course as b
  on a.cpno=b.cno;
 

 select 姓名，课程名， 成绩
 from  xs inner join xs_kc on xs.学号=xs_kc.学号
 inner join kc on xs_kc.课程号=kc.课程号；
 where 课程名='计算机基础'
 order by 计算机基础 decs;
 

 

:0531： 子查询：
select 列。。。from 表 where（select 列。。。from ）

查询与李方方式同一个系的学生信息。

select 专业名 from xs where 姓名='李方方';

查询选修了计算机的学生姓名
select 姓名 from xs
where 学号 in(select 学号 from xs_kc
where 课程号=(select 课程号 from kc
where 课程号='计算机基础'));

 

 

 

0604
视图与表的区别：
表是一个实际存储数据的结构：包含数据结构和记录
视图：是一个虚拟表，只存视图的定义，没有数据结构。
视图从基表提取数据
当基表的数据修改后，将反映到视图中
优点：
1. 简化用户操作
2. 简化权限的管理

select。。。
 

create view myview
as

要求查询学生的姓名，课程名，成绩

创建视图：

create 【or replace】视图名称【（字段名。。。）】
as
select 语句

 

要求：在学生表上创建一个视图v_student
create view v_student
as
select 学号，姓名，性别，出生日期，专业名 from xs
where 性别=1；

with check option：修改的数据满足条件
作用：限定通过视图修改的数据，应该满足视图定义的条件
也就是 修改后的的数据一定会出现在视图中

3 更新视图

查询的结果是只读的
视图的结果是可以修改的，修改后会反映到基表中

4 修改是视图的定义
alte view 旧视图名
as
select * from xs
where 总学分>50
with check option；

5删除视图
drop view 视图名，。。。。。

1。select pname from CPXS.产品表
where price>2000 and price<2900;

 

2.use CPXS；
select 产品名称， sum(price*number) from 产品表 as c，产品销售表 as cx
where c.产品编号=cx.产品编号
group by 产品名称;  

3.creat view CPXS.bxcp（bx）
  as
  select 产品名称（冰箱） from CPXS.bxcp；

4.creat view CPXS.bxcp（cpbh）
as
select 产品编号 from sales
select * from CPXS.bxcp
where 库存量<100;

 

1 索引：就是列表，包含索引字段的值和相应的页码定位
  索引关键字进行了排序。通过索引提高查询速度
  书上的目录相似
  表上查询：全表扫描

  结构：btree 根 叶 中间节点
 
  索引的优点：提高查询速度。
  缺点：占磁盘空间
        会降低写的速度。（插入，更新，删除）
  
  什么时候建索引：如果表主要是提供查询，那么在查询字段上建立索引
 

索引分类：
  1.普通索引
 

  create index 索引名
  on 表名（字段名，。。。）

  create index inde_name
  on xs(姓名)；
 

  2.主键索引
  primary key 在一个表上只能创建一个主键，主键字段值不能为空
 
 

  3.唯一性索引：在一个表上可以创建多个，索引值不能重复，可以存在空值
 
  create unique index 索引名
  on 表名（字段名，。。。）
 
  create  unique  index index _name
  on xs(姓名)；
  4.全文索引
  create fulltext index 索引名
  on 表名（字段名，。。。）

  通过修改表结构添加索引
  alter table 表名
  add index 索引名（索引字段，。。。）

  查看索引： show index from 表名
  删除索引： drop index 索引名 on 表名
 

 索引的创建：
 1.表已经建好了：
   1）create index 索引名 on 表名（字段名，。。）
   2）alter table 表名 add index 索引名 （字段名）

 2.在建表的同时创建索引
  create table abc
 （id int primary key，
  name varchar（10），
  index ind_name(name)）；
 
  create table abc
 （id int，
  name varchar （10），
  primary key（id），
  index ind_name(name)）；
 

索引的删除：
  1.drop index 索引名 on 表名
  2.alter table 表名
    drop index 索引名