数据库的认识

####    数据模型
1、概念模型，以客户的观点和想法为基础，对现实事物的抽象
    
2、逻辑数据模型，数据库中的数据模型——关系数据模型

3、物理模型

####    概念模型：E-R图
E：实体   R：关系

    一对一的关系：一个学校就一个校长
    一对多的关系：一个专业有多个学生
    多对多的关系：课程和授课老师 （Java 张 邓 王） 张 (Java H5 Android 开车）
表示：可以用矩形表示实体，菱形表示关系，椭圆表示实体的特征（属性）

####    关系数据模型
通过一个二维表描述关系数据模型。用行来表示具体的数据，列表示特征（属性）

    1、每个属性不能再分
    2、关系数据模型中的二维表，不会有重复的行
对于数据库二维表的运用，主要涉及**查询、连接、交、并、插入、修改、删除**等这些操作，常用的四种“增、删、查、改”
####    表相关的知识
主键：为了保证在一张表中，不会出现两个完全相同的数据，主键唯一并且非空

外键：用来定义表与表之间的关系的属性，如学生表有学号，课程表有课程编号，要表示学生成绩时，可以建立一个成绩表，属性有学号、课程号、分数，其中的课程号（引用自课程表）和学号（引用自学生表）属于外键

约束：保证数据的完整性，限制属性，常用的约束有：

    唯一约束（unique）、主键约束（primary key）、外键约束（foering key）、
    检查约束（check）、非空（not null）

####    数据库属性（字段）类型：
    字符串：varchar(30)
    整型：int 
    浮点数：float
    日期：date datetime

    二进制数据：blob

####    建表

    create table 表名(字段1 类型1 [约束],字段2 类型2 [约束]...)

    如：学生表（stuInfo）： stuId 整数 主键 
                stuName 字符串 非空
                age 整数 
                sex 字符串(2) 
                birth 时间 

   

create table stuInfo 
        (stuId int primary key,
        stuName varchar(30) not null,
        age int,
        sex varchar(2),
        birth datetime)

    课程表(curInfo)：课程编号curId，课程名称curName，课时curTime，学分credit（3-8分）
    

create table curInfo(
        curId int primary key,
        curName varchar(30) not null,
        curTime int not null,
        credit int,
        check(credit between 3 and 8)
    )

    联合主键（由多个字段组成的主键）,如成绩表(stu_score)
    学号(stuId)、课程号(curId)、成绩(score)
    

create table stu_score(
        stuId int,
        curId int,
        score int not null,
        primary key(stuId,curId)
    )

####    主键
非空，唯一：1 2 3 4 5(数字，自动递增)
   

create table teacher(
        id int primary key auto_increment,
        teaNam varchar(30) 
    )

####    外键约束
保证数据的完整性，保证数据不一致的问题，提供的一种约束

    表达方式：
    foreign key [表名](字段) references 表名2(字段) [on update [cascade]|[set null]|
    [restrict]][on delete [cascade]|[set null]|[restrict]]
 

####    修改表修改表使用关键字alter table,语法如下    添加一个字段    alter table 表名 add (字段名 类型 [约束],....)    

   alter table teacher add (sex varchar(2) ,major varchar(30))

删除字段
    alter table 表名 drop 字段名 

   alter table teacher drop sex

##### 修改表名
语法：
    
    rename table 原表名 to 新表名 [, 原表名2 to 新表名2]
    

rename table teacher to tea,stuinfo to stu

####    删除表
    drop table 表名
    drop table if exists 表名
    如    

    drop table if exists tea

####    删除数据库（慎用）
    drop database 数据库名
    如

   drop database school

####    表内容的操作
增加数据（插入数据 insert），更新数据（update），删除数据（delete），查询数据（select）
#####    插入数据
insert into 表名(字段1，字段2,...) values (值1，值2,...)

insert into 表名 values (值1，值2,...)
   

    insert into stu (stuId,stuName) values (102,'李四')
    insert into stu values (103,'令狐冲',20,'男','1997-10-01 12:12:12')

注意第二种插入要保证字段值顺序、个数、类型一致

注意：如果向有外键约束的表中插入数据，要插入的内容在原表中要存在，不存在会报错
#####    更新数据
    update 表名 set 字段1=值1[,字段2=值2]... where 条件
    如：

update stuinfo set age=20,sex='女' where stuId=102
	如果不加条件就是所有的年龄都要加
    update stuinfo set age=age+1

注意：如果更新有外键约束关系的表时用了on update cascade，被引用的表的信息发生变化时引用表的信息也跟着变化，引用表的外键如果更新了，那么只能更新到引用表中存在的字段

#####    case条件更新数据
    

update stuinfo set age=
    case
        when age<20 then age+3
        when age=20 then age+5
        else age+10
    end

#####    删除
    delete from 表名 [where 条件]

  delete from  student where stuId=102

如果不加条件会删除表中的全部内容 （表格框架还在）
#####    查询数据
通过select指令获取数据表中的数据情况

    select 查询的字段|数据库函数 from 表名1[,表名2...,视图... as 别名] 
    [where 条件 [分组 [分组过滤]] [排序] [条数限制] 

    使用*表示所有的字段
  

  select * from stuinfo

    自定义查询的字段
   

 select stuName,age from stuinfo

带条件查询

   

 select * from stuinfo where sex='男'

条件可以由一些比较运算得出，比较运算符包括

    =(等于)、>=、<=、>、<、!=(不等)、 <>(不等于) 、!>、!<
多个条件需要使用逻辑运算符连接

    and、or、not（与、或、非）

    如：

select * from stuinfo where sex='男' and age>25

使用between..and...描述在哪个范围之内

   

 select * from stuinfo where age between 20 and 30

使用in来查询分别等于某个值的结果

    

select * from stuinfo where age in (18,20,36)
    select * from stuinfo where age not in (18,20,36)

空值判断

    需要使用is null是否为空，is not null表示非空
    
    查询空值结果
    select * from stuinfo where birth is null

    查询非空结果
    select * from stuinfo where birth is not null
#####    模糊查询
可以使用like关键字实现模糊查询，一般要配合"_"或者"%"来使用，其中"_"表示匹配一个字符字符"%"表示任何东西

    

    select * from stuinfo where stuName like '__'（只有两个字符的信息两个下划线）
    select * from stuinfo where stuName like '张_'（以张开头）
    select * from stuinfo where stuName like '%方%'（含有“方”的信息）

#####    排序
可以使用order by指令来排序

    order by 字段1 [asc|desc],字段2 [asc|desc]
    asc表示升序  desc表示降序默认情况下为升序
    

select * from stuinfo order by age asc,stuId desc

注意：order by一般放在查询条件后（如果没有条件就直接放在最末尾）
#####    聚合函数
count(字段)计算总个数，结果的条数；max(字段)计算最大值;min(字段)最小值；avg（字段）平均数；sum(字段)总和

    

select avg(age) from stuinfo where sex='女'
    select count(*) from stuinfo where sex='男'

可以使用as关键之自定义一个结果名称（别名）

    

select avg(age) as a,sum(age) as s from stuinfo where sex='女'

注意：聚合函数只能放在select、group by、having字句，其他字句不允许
#####    分组
针对查询的结果可以使用group by来进行分组,分组之后可以使用having指令来筛选结果

    

    select * from stuinfo group by sex,stuId  如果没有他，性别不会全部显示出来
    select * from stuinfo group by sex,stuId having age<=30

#####    限制结果条数
可以使用limit指令来限制结果条数
    
    select * from stuinfo order by age limit 5
    
    结合offset设置跳过多少条取多少条
    

select * from stuinfo order by age limit 3 offset 4

    表示结果跳过4条取3条 
#####    子查询
在一个大的查询中嵌入一个内部查询，这种查询就是子查询
  

select * from stuinfo group by sex,stuId having age<=
        (select avg(age) from stuinfo) 
    select * from student group by stuSex,stuId having stuAge<
(select stuAge from student where stuName="rose")

    
#####    联合查询
从多个表中使用外键关联查询结果

    查询分数为9分的学生姓名和分数
    

   select stu.stuName,sc.score from stuinfo as stu,stu_score as sc 
    where sc.score=90 and sc.stuId=stu.stuId

    查询分数为90分的学生名字和所选的科目名称
    

    select stuinfo.stuName,curinfo.curName from stuinfo,stu_score,curinfo
    where stu_score.score=90 and stuinfo.stuId=stu_score.stuId and 
    stu_score.curId=curinfo.curId

####    JDBC
        //驱动的位置
        String driver="com.mysql.jdbc.Driver";
        //连接的数据库地址
        String url = "jdbc:mysql://127.0.0.1:3306/school";
        //加载驱动
        Class.forName(driver);
        //连接数据库
        Connection conn = DriverManager.getConnection(url, "root", "root");

#####    使用Statement
    Statement st = conn.createStatement();
    //st.executeUpdate(sql)  //更新操作（插入、更新、删除）

    //查询
    ResultSet rs = st.executeQuery(sql);
    while(rs.next()){
        int id = rs.getInt("stuId");
        String name = rs.getString("stuName");
        ...
    }
当处理碗数据表操作之后要关闭，如果是查询则需要先关闭查询结果ResultSet
    
    rs.close();
    st.close();
如果数据库连接不再需要也要关闭

    conn.close();

/**
 * 数据库工具
 * @author Administrator
 *
 */
public class DbUtil {
	public static void test(){
		//驱动的位置
		String driver="com.mysql.jdbc.Driver";
		//连接的数据库地址
		String url = "jdbc:mysql://127.0.0.1:3306/school";
		try {
			//加载驱动
			Class.forName(driver);
			//连接数据库
			Connection conn = DriverManager.getConnection(url, "root", "root");
			//可以操作数据库（用Statement）
			Statement st = conn.createStatement();
			String sql = "select * from stuinfo";  //stuId stuName age sex birth
//			st.executeUpdate(sql)  //更新操作（插入、更新、删除）
			//执行查询
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt("stuId");
				String name = rs.getString("stuName");
				int age = rs.getInt("age");
				String sex = rs.getString("sex");
				Date d = rs.getDate("birth");
			System.out.println(id+" | "+name+" | "+age+" | "+sex+" | "+d);
			}
			rs.close();
			st.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		test();
	}
}