Java 数据库基础（二）

1、SQL概述

2.1  什么是SQL（了解）

结构化查询语言，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的

SQL被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准, 后来被国际化标准组织(ISO)采纳为关系型数据库语言的国际标准

2.2  SQL优点

标准统一：

不是特定数据库供应商专有的语言, 几乎所有DBMS都支持SQL

简单易学：

SQL语言由描述性很强的英语单词组成, 这些单词数量不多, 且都很常用

声明式：

SQL是一种声明式的语言, 相对应的, Java是一种命令式的语言. 在SQL中, 你只需要指出 "想做什么", 而无需指明 "怎么做", 具体的操作过程由DBMS自动完成

2.3  分类 

2.3.1 分类

DDL (Data Definition Language)

数据定义语言, 用来定义数据库对象, 包括数据库, 表, 列, 索引, 视图等

DML (Data Manipulation Language)

数据操作语言, 用来操作数据库表中的记录

DQL (Data Query Language)

数据查询语言, 用来查询数据库表中的记录

DCL (Data Control Language)

数据控制语言, 用来定义访问权限和安全级别

2.3.2 重要性 和 复杂度（了解）

DDL：数据定义语言-重要性:中, 复杂度:低

DML：数据操作语言-重要性:高, 复杂度:低

DQL：数据查询语言-重要性:高, 复杂度:高

DCL：数据控制语言: 本课程中不涉及

2.4  SQL中的数据类型

数值类型

int：整形

double：浮点型, 例如double(5, 2)

文本和二进制类型

char：固定长度字符串, char(10)  

varchar：可变长度字符串, varchar(255)

text：字符串类型(非标准)

时间日期类型

date：日期类型, 格式为 yyyy-MM-dd

datetime：yyyy-MM-dd hh:mm:ss

2、DDL和DML

3.1 DDL

3.1.1概述：

DDL用来操作数据库对象, 即数据库, 表, 列等. 对数据库对象的创建, 修改和删除

3.1.2关键字：

CREATE：用于创建操作

ALTER： 用于修改操作

DROP： 用于删除操作

3.1.3操作数据库：

创建 、查询、修改、删除、其它

3.1.4操作数据表：

创建、查询、修改表、修改列、删除表

案例一： 创建数据表

1、创建一张students数据表：

语法：( 列名  数据类型 , 列名  数据类型 , ......)

代码：CREATE  TABLE  students(id  int , name  varchar( 50 ) ,age  int ) ;

空值和非空值

NULL   NOT NULL（列值不可以为空，必须传值）

自动增长 :自动增长，需要配合主键使用，默认起始值为1 增量为1

AUTO_INCREMENT

主键约束：每张表只能存在一个主键，保证数据唯一性，自动位NOT NULL

关键字：PRIMARY  KEY

唯一约束; 保证数据唯一性，可以为空值，一张表可以存在多个

关键字：UNIQUE  KEY

默认约束：如果没有输入明确的字段值，则会自动设置为默认值

关键字default

案例：  .....   sex enum(‘1’,’2’,’3’) default 3....  后面讲

外键约束：后面讲。。。。。

2、注意点:

1/名称在前，类型在后，和Java相反  int a;

2/最后一个字段没有逗号

3、验证：

查看数据库中数据表，show columns  from students ;

查看数据表字段信息，desc students ;

查看数据表创建细节，show create table students;

案例二：修改操作

1、修改数据表表名称

语法：RENAME  TABLE  oldname  TO  newname ;

代码：RENAME  TABLE  students  TO  stud;

2、修改数据表字符集-编码格式

语法：ALTER  TABLE  表名称  CHARACTER  SET  字符集名称;

代码：ALTER  TABLE  students  CHARACTER  SET  gbk;

3、修改列的数据类型

语法：ALTER  TABLE  表名称   MODIFY  列名称  数据类型 ;

代码：ALTER  TABLE  students  MODIFY  gender   VARCHAR(10);

4、修改列名

语法：ALTER  TABLE  表明称  CHANGE  列名称(old)  列名称(new)  数据类型;

代码：ALTER  TABLE  students  CHANGE  name   sex  VARCHAR(10);

案例三：删除操作

1、删除表中的列

语法：ALTER  TABLE  表名称  DROP   列名称;

代码：ALTER  TABLE  students  DROP  sex;

2、删除数据表

语法：DROP  TABLE  表名称 ;

代码：DROP  TABLE  students ;

3、删除表中所有的数据（后面演示）

语法：TRUNCATE  TABLE  表名称;

代码：TRUNCATE  TABLE  students;

案例四：增加操作

1、在表中增加一列 列名称：age

语法：ALTER  TABLE  表名称  ADD  列名 数据类型;

3.2 DML

3.2.1概述：

DML用来操作表中的数据. 对表中记录的创建, 修改和删除

3.2.2关键字：

INSERT： 数据插入

UPDATE：数据修改

DELETE：数据删除，只删除数据, 表结构还在, 被删除的数据可以找回

TRUNCATE: 先DROP表, 再创建一个新的. 数据不可找回, 执行速度快

语法：TRUNCATE  TABLE  name

特点：

TRUNCATE  TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE  TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

3.2.3操作数据表

案例一：插入数据

语法：INSERT  INTO 表名称 (column1, column1, ....)  VALUES (value1, value2, .....);

代码：INSERT  INTO  students(id, name)  VALUES (1, 'TOM');

注意点：插入一行记录, 注意列名和列值的类型和顺序要一一对应

日期和字符串要使用单引号括起来; 如果要插入空值, 请使用null

如果所有参数都提供, 可以忽略列名

INSERT  INTO  students  VALUES (2, 'Jack', 19, 'M');

可以批量插入多条记录

INSERT  INTO  students  VALUES (3, 'Lily', 18, 'F'), (4, 'Lucy', 18, 'F');

验证：select * from  students;

案例二：数据修改

语法：UPDATE 表名 SET 列名1=列值1, 列名2=列值2 ... WHERE 列名=列值

代码：UPDATE  students  SET  name = 'Tom' , age = 17  WHERE  id = 1 ;

批量修改

将所有学生的年龄改成18岁：UPDATE  students  SET  age = 18;

将所有学生的年龄在当前基础上加1岁：UPDATE  students  SET  age = age + 1 ;

案例三：数据删除

语法：DELETE  FROM 表名  WHERE 列名=列值

代码：DELETE  FROM  students  WHERE  id = 1;

删除表中所有的记录：DELETE  FROM  students;

 

3.3 对比

DDL操作的是数据库对象

DML操作的是数据库表中的数据

DDL表操作要加关键字TABLE

DML因为都是表操作, 无需加关键字TABLE

4、DQL

4.1 概述

数据查询语言

执行DQL语句不会改变数据库原始数据, 而是让数据库发送结果集给客户端查询返回的结果集是一张虚拟表

4.2查询关键字

关键字：select

4.3 基础语法

SELECT 要查询的列 FROM 表名

WHERE 行条件

GROUP BY 对结果分组

HAVING 分组后的行条件

ORDER BY 对结果排序

LIMIT 结果限定

4.4 常用操作

4.4.1 基础查询

查询students表中所有数据：SELECT  *  FROM  students;

查询指定列：SELECT  id ,  gender  FROM  students ;

4.4.2 条件查询

概述：在查询时通过 WHERE 子句给出查询条件, WHERE子句可以使

用到的运算符：

关系运算

=, !=, <>, <, <=, >, >= <>和!=用法相同。

BETWEEN 20 AND 40 20<=值<=40。

IN('a', 'b', 'c') 值包含在集合中。

NULL判断：IS NULL, IS NOT NULL 判断是否为NULL

逻辑运算：AND, OR, NOT 与, 或, 非

案例：查询年龄在15岁至20岁之间的学生：SELECT * FROM students WHERE age BETWEEN 15 AND 20;

4.4.3 模糊查询

关键字：like

使用的通配符： _ 匹配任意一个字符     _b

% 匹配任意0~n个字符

案例：查询名字中包含字母a的学生: SELECT * FROM students WHERE  name  LIKE  '%a%' ;

4.4.4 字段控制

关键字： DISTINCT(去重), AS(别名)   函数： IFNULL()

案例：

1、查询雇员薪资, 使用DISTINCT去除重复记录

SELECT DISTINCT salary FROM employee;

2、查询雇员薪资和奖金之和

  SELECT *, salary+bonus FROM employee;

3、bonus为NULL时, 任何值和NULL相加还是NULL，需要使用 IFNULL() 函数将NULL转换成数值0

  SELECT *, salary+IFNULL(bonus, 0) FROM employee;

4、此时列名出现了 salary+IFNULL(bonus, 0), 很不美观，可以用 AS 关键字取一个别名

  SELECT *, salary+IFNULL(bonus, 0) AS total FROM employee;

4.4.5 排序和聚合

1、 排序：使用关键字可以给查询结果排序, 有两种排序方式

关键字：ORDER BY   

结果：asc  升序(默认)  从小到大

desc 降序   从大到小

案例：查询所有学生, 按年龄降序排列：SELECT * FROM students ORDER BY age DESC;

2、聚合

概述，聚合函数用来做纵向运算,

分类：

计数 — COUNT

最大值 — MAX

最小值 — MIN

求和 — SUM

求平均值 — AVG

案例：

1、查询工资+奖金大于2500的员工人数：

SELECT COUNT(*) FROM employee WHERE  salary+IFNULL(bonus, 0) > 2500;

2、查询员工最高工资和最低工资

SELECT MAX(salary), MIN(salary) FROM employee;

3、 查询员工总工资和平均工资

SELECT SUM(salary), AVG(salary) FROM employee;

4.4.6 分组查询

概述：使用 GROUP BY 子句做分组查询, 分组查询一般和聚合函数一起使用

案例：将查询结果根据年龄分组

Select age from students group by age;

  或者指定分组条件的位置

Select age from students group by 1;

Select id,age from students group by 2;

查询所有年龄大于15 的人的总数

Select age count(*) from students where age>15 group by age;

 

4.4.7HAVING子句

概述：HAVING子句也是用来过滤查询条件的

案例：查询总工资大于9000的所有部门, 部门总工资, 和部门总员工数

SELECT department, SUM(salary), COUNT(*)

FROM employee

GROUP BY department

HAVING SUM(salary)>9000;

注意 having后面可以添加聚合函数或者属性字段 如果添加字段，name字段必须在语句中出现

WHERE和HAVING的区别

WHERE是在分组前过滤数据; HAVING是在分组后过滤数据

WHERE子句不可以使用聚合函数; HAVING子句可以使用聚合函数

4.4.8分页查询

概述：使用 LIMIT 关键字可以用来限定查询结果的起始行和总行数

案例：从第一行开始, 查询5条记录

SELECT * FROM employee LIMIT 0, 5;

分页查询: 可以使用LIMIT实现分页功能, 例如一页10条记录, 则有

SELECT * FROM employee LIMIT 0, 10; -- 第一页数据

SELECT * FROM employee LIMIT 10, 10; -- 第二页数据

SELECT * FROM employee LIMIT 20, 10; -- 第三页数据

注意: LIMIT不属于标准的SQL语法, MySQL支持, 其它DBMS不一定支持.