复习第二 数据库

sudo service mysql start

一.数据库

1.创建数据库(create database gss)     删除数据库(drop database gss)       显示所有的数据库(show databases)     用数据库(use gss)

2.显示所有表(show tables)  查看表结构 (desc  表名)

3.表约束(主键:primary key   外键:foreign key   非空:not null  唯一:unique  默认:default   检查:check   [mysql枚举enum]    )   s enum('a','b','c')

4.数据类型(int , float ,  char(10)  ,varchar(10) , enum('m','f'),   日期:data(年月日)     year(年)      datetime(年月日时分秒)     timestamp(年月日时分秒默认系统时间 只能用此类型 timestamp current_timestamp)

二.表结构(增/删/改/查)

1.增加表         create table 表名(字段1  字段类型  约束1  约束2,字段2 字段类型 约束,....primary key (字段)，foreign key(kch) references kcb(kch),);

2.删除表        drop table  表名;

3.修改表       alter table  表名(1.加列  add(alter table xsb add bj char(10)  default '188';)    2.删列  drop table (alter table xsb drop bj;)   3.改字段 类型  modify(alter table xsb modify xb enum('m','f'))

                                                4.改字段名称   change (alter table xsb change xh  cust_id char(10);)   5.改表名  rename(alter table xsb rename student;) )

4.查表结构    desc  xsb;

5.复制表结构   create  table  新表名   like  旧表名(只复制表结构)

 

三.练习

#创建学生表---xsb
/* 学号（主键），姓名（非空）,性别（枚举），年龄，班级（默认188）籍贯，身份证号（唯一），注册日期（默认系统时间）*/
create table xsb(xh char(10) primary key,xm varchar(20) not null,xb enum('男','女'),nl int,bj char(5) default '188',jg varchar(100),sfzh char(18) unique,zcrq timestamp default current_timestamp);

#创建课程表 #课程号（主键） 课程名（非空）
create table kcb(kch int primary key,kcm varchar(10) not null);

 

#创建成绩表 #学号（外键） 课程号（外键） 成绩（非空） #设置学号和课程号作为联合主键
create table cjb(xh char(10) ,kch int ,cj int not null,foreign key (xh) references xsb(xh),foreign key(kch) references kcb(kch),primary key(xh,kch));

 

#添加一个自增列作为主键
create table cjb1(id int primary key auto_increment,xh char(10) ,kch int ,cj int not null,
foreign key (xh) references xsb(xh),foreign key(kch) references kcb(kch));

 

1.#查询李晨的全部信息 select *from xsb where xm='李晨'
2.#查询年龄是23的全部学生信息select *from xsb where nl = 23
3.#查询不及格的成绩信息 select * from cjb where cj <60
4.#查询成绩在80以上(包含80)的成绩信息select * from cjb where cj >=80
5.#查询年龄小于20的全部学生信息 select * from xsb where nl < 20
6.#查询籍贯是北京的学生全部信息 select * from xsb where jg='北京'
7.#查询籍贯不是北京的学生全部信息 select * from xsb where jg !='北京'
select * from xsb where jg <> '北京'

8.#查询张三或李四的全部基本信息 select * from xsb where xm = '张三' or xm= '李四'
9.#查询188班所有男生的信息 select * from xsb where bj='188' and xb='男'
10.#查询籍贯是北京、上海和广东的学生信息 select * from xsb where jg='北京' or jg='上海' or jg='广东';
select * from xsb where jg in ('北京','上海','广东')

11.#查询籍贯不是北京、上海或广东的学生信息 select * from xsb where jg !='北京' and jg !='上海' and jg !='广东'
select * from xsb where jg not in ('北京','上海','广东')                  select * from xsb where jg <> '北京'

12.#查询张三或李四的全部基本信息 select * from xsb where xm = '张三' or xm= '李四'

13.#查询188班所有男生的信息 select * from xsb where bj='188' and xb='男'

14.#查询籍贯是北京、上海和广东的学生信息 select * from xsb where jg='北京' or jg='上海' or jg='广东';
select * from xsb where jg in ('北京','上海','广东')

15.#查询籍贯不是北京、上海或广东的学生信息 select * from xsb where jg !='北京' and jg !='上海' and jg !='广东'
select * from xsb where jg not in ('北京','上海','广东')

16. 18岁只限制张三 select * from xsb where nl=18 and xm='张三' or xm='李四';

17.查询年龄在20到25之间的学生信息（包含20、25）select * from xsb where nl >=20 and nl <=25

18.查询未填写身份证号的学生信息select *  from xsb   where sfzh is null or  sfzh=''

19. 查询填写身份证号的学生信息select * from xsb where sfzh is not null

20.查询学生姓名中以'冰'字开头的学生信息 select * from xsb where xm like '冰%'        

查询学生姓名中以'冰'字结尾的学生信息 select *  from xsb  where xm like '%冰'

#查询学生姓名中以'冰'字结尾的学生信息 select * from xsb where xm like '%冰'
#查询学生姓名中第二个字是'冰'的学生信息select * from xsb where xm like '_冰%'

21.#查询李四的班级和籍贯 select bj '000' ,jg '籍贯' from xsb where xm='李四'

22.查询学生表中的学生来自哪些省份  select  distinct jg  from xsb   (distinct 用于去重,可用于多列,也可以用于单列,distinct放到开头)

聚合函数
count(*)---统计函数--数一数      max(字段名)---最大值     min(字段名)---最小值    sum(字段名)---求和   avg(字段名)---平均值

1.# 查询成绩表中最高分和最低分  select max(cj),min(cj)from cjb

2.#查询001号学生的总分和平均分 select sum(cj),avg(cj),xh  from cjb where xh='001'

3.#查询学生表中学生来自几个省份  select  count(distinct jg)  from xsb

1.#查询188班男女生人数 select count(*),xb  from xsb  where bj='188' and xb is not null  group by xb

2.#查询每个学生的最高分，总分和平均分 select xh, max(cj),sum(cj),avg(cj) from cjb group by xh

3.#查询各班男女生人数  select count(*),bj,xb  from xsb  group by bj,xb

4.查询个人总分大于400分的学号 select xh  from cjb  group by xh  having sum(cj)>400

5.#按年龄从小到大显示全部学生信息 select * from xsb where nl is not null order by nl

6.按学号升序，成绩降序显示全部的成绩信息 select * from cjb order by xh,cj desc  limit 5,6(从第6条数据向下 取6条)

 

四.多表查询(级联删除.外键关联主键后面增加 on  delete  cascade)

1.等值连接查询  select *  from A,B,C where A.主键= B.外键  and B.主键=C.外键   and   查询条件  (迪卡尔积)   先连接再判断

2.子查询/嵌套查询    多条单表查询 结果拼接为一条SQL语句

3.内连接查询  select * from A inner join  B on  A.主键= B.外键 inner join C on B.主键=C.外键   where 查询条件  先判断 再查询

4.左连接  left join    左表的数据都显示出来,没有的部分用null 补充.

 

五.表的增删改查

1.表中增加值:insert into  表名(字段名) values(),();

2.表中删除值: delete *  from  表名  where 条件;

3.修改: update  表名  set 字段= 修改后字段的值   where  修改条件

4.查询:

select --查询内容
from---从哪里查询（表名）
where---分组前的查询条件
group by---分组字段
having---分组后查询条件
order by---排序（升序，降序）
limit---截取记录

5.复制:

create table 新表明
select * from 旧表名
（复制的只是表的记录）无约束内容.

 

六.数据库其他内容

1.索引  加快 对表的查询.        创建索引  create  index 索引名  on  表名(字段1,字段2)

2.视图是一个虚拟的表. 数据只存放定义,不存放数据.    用来存储查询语句.  create view  view_1 as  select * from xsb where xm='李白'   

 view_1 视图对象.    select  *  from view_1 (可以对关联的表进行增 删  修改);   视图作用:提高了查询的复用性,简单 ;提高了安全性,

3.存储过程,完成特定功能的sql语句集,经编译存储再数据库中.

 create procedure 存储过程(参数1,参数2)  begin  实现功能的代码  end  ;    call  存储过程名(参数1,参数2);

4.事务   简单说事务就是一组操作的集合，而且这组操作必须全部执行成功否者回滚到所有操作前的状态。

5.触发器:就是一张表发生了某件事（插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句的执行；

mysql的触发时间:  insert(before  after)  update(before  after)  delete(before  after)   

create trigger get_insert  after insert on t1 for each row   begin insert into t2 values(new.id,new.name)  end;

                       触发器名字      之前          那个表      每一行           执行那个操作.

删除的数据进入old表 .  增加的数据进入new表.