Mysql的基本语法了解

#### 数据模型 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(数字，自动递增) 如：有教师表（teacher） ，id 整数 主键 自动递增 teaName 字符串。。。 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 条件] 如果不加条件会删除表中的全部内容  ##### 查询数据 通过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] 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 stuinfo where age<小龙女的年龄 ##### 联合查询 从多个表中使用外键关联查询结果 查询分数为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();