前言
对于JAVA程序员来说,数据库编程技术是必须要掌握的一项技能。数据库可以把数据持久化到本地,非常方便我们管理和存储数据。数据库的难度不大,但也要通过不断的练习从而能够熟练的编写Sql语句,本章主要介绍了什么是数据库以及Sql语句的基本内容,通过本章的学习我们可以编写一些基础的Sql语句,为以后的学习打下基础。
一、数据库概述
为什么学习数据库:
1.实现数据持久化到本地;
2.可以实现结构化查询,方便管理
什么是数据库?
数据库(DataBase):存储数据的仓库,将数据按照特定的规则存储在磁盘上
数据库的相关概念 :
1.DB:数据库(DataBase) 存储数据的容器,它保存了一系列有组织的数据。
2. DBMS:数据库管理系统(DataBase Management System) 又称为数据库软件或数据库产品,用于创建或管理DB。
3.SQL:结构化查询语言(Structure Query Language) 用于和数据库通信的语言。不是某个数据库软件特有的,而是几乎所有的主流数据 库软件通用的语言。
根据存储类型的不同数据库可以分为
1.关系型数据库:Oracle,MySQL,SQL Server
2.非关系型数据库:MongdoDB,Redis
关系型数据库和非关系型数据库的区别:
1.实质:
非关系型数据库产品是传统关系型数据库的功能阉割版本,通过减少用不到或很少用的功能,来大幅度提高产品性能。
2.价格:
目前基本上大部分主流的非关系型数据库都是免费的,而比较有名气的关系型数据库,比如Oracle、DB2、MSSQL是收费的。虽然Mysql(关系型)免费,但它需要做很多工作才能正式用于生产。
3.功能:
实际开发中,有很多业务需求,其实并不需要完整的关系型数据库功能,非关系型数据库的功能就足够使用了。这种情况下,使用性能更高、成本更低的非关系型数据库当然是更明智的选择。
二、MySQL
MySQL:是关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle旗下产品。
MySQL特点:
1.是一种关系数据库管理系统。
2.MySql数据库服务器具有快速、可靠和易于使用特点。
3.使用标准sql语言,支持多种操作系统。
4.分为商业版和社区版,商业版需要付费,社区版可以免费使用。
MySQL语法规范:
MySQL语法规范 : 不区分大小写,建议关键字大写,表名、列名小写
不用编译可以直接运行
数据库名一旦创建就不可修改
/*注释
1.单行注释:#注释文字
2.单行注释:-- 注释文字(要有空格)
3.多行注释:/* 注释文字*/
三、SQL–DDL
SQL:结构化查询语言(Structure Query Language) 用于和数据库通信的语言。不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言。
DDL(data definition language):数据(结构)定义语言,用于创建和修改数据库表结构,例如create, alter, drop, rename
CREATE DATABASE IF NOT EXISTS student_db; -- 创建数据库
CREATE DATABASE IF NOT EXISTS school_db CHARSET utf8; -- 创建数据库并设置编码格式
DROP DATABASE IF EXISTS student_db; -- 删除数据库
ALTER DATABASE student_db CHARSET utf8; -- 修改字符集
数据库存储数据的特点:
1.将数据放到表中,表放到库中。
2.一个数据库可以有多个表,表名具有唯一性。
3.表由列组成,也称字段。
4.表是按行存储的。
数据库表:是数据存储最常见的形式,是构成关系型数据库的基本要素。
记录:是表里的一行,也被称为一行数据。
字段:表里的一列,用来保存记录的特定信息,如ID,姓名,电话,地址。
# 创建学生信息基本表 姓名 性别 年龄 身高 生日
CREATE DATABASE IF NOT EXISTS student_db; -- 创建数据库
CREATE TABLE t_stuinformation(
id INT ,
birthday DATE,
sex CHAR(1),
height FLOAT(4,1),
age INT
)
/*主键:
在一张表中代表唯一的一条记录,不能为空,不能重复
约束:
PRIMARY KEY 设置主键约束
NOT NULL 不能为空约束
UNIQUE 唯一性约束
检查约束 设置条件
主键自动增长 :AUTO_INCREMENT
默认值 DEFAULT default_value
字段注释: comment '注释'
*/
CREATE TABLE t_stuinformation2(
NAME INT PRIMARY KEY AUTO_INCREMENT,
birthday DATE NOT NULL COMMENT '你的生日',
sex CHAR(1) DEFAULT '男',
height FLOAT(4,1) CHECK(height>165.0),
age INT NOT NULL
)
-- 删除表 DROP TABLE [if exists ]表名
DROP TABLE t_student;
-- 修改表名 RENAME TABLE 旧表名 TO 新表名
RENAME TABLE t_stuinformation TO t_student;
-- 复制表结构(不复制数据) CREATE TABLE 新表名 LIKE 被复制表名
CREATE TABLE t_student2 LIKE t_student;
-- 添加主键约束 删除主键约束
ALTER TABLE t_stuinformation ADD PRIMARY KEY(id);
ALTER TABLE t_stuinformation DROP PRIMARY KEY;
-- 设置自动增长,删除自动增长
ALTER TABLE t_stuinformation MODIFY id INT AUTO_INCREMENT;
ALTER TABLE t_student MODIFY id INT;
-- 设置不能为空,设置能为空
ALTER TABLE t_stuinformation MODIFY sex CHAR NOT NULL;
ALTER TABLE t_stuinformation MODIFY sex CHAR NULL;
-- 添加唯一约束,删除唯一约束
ALTER TABLE t_stuinformation ADD CONSTRAINT unique_s UNIQUE(sex);
ALTER TABLE t_stuinformation DROP INDEX unique_s;
ALTER TABLE t_user ADD CONSTRAINT unique_user UNIQUE(account);
-- 添加检查约束
ALTER TABLE t_stuinformation ADD CONSTRAINT check_h CHECK(height>100);
ALTER TABLE t_stuinformation DROP CHECK check_h;
-- 修改表结构
-- 添加列,删除列
ALTER TABLE t_stuinformation ADD weight FLOAT(4,1);
ALTER TABLE t_stuinformation DROP weight;
ALTER TABLE t_stuinformation ADD weight FLOAT(4,1) FIRST;
ALTER TABLE t_stuinformation ADD weight FLOAT(4,1) AFTER id;
-- 修改列名 ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型
ALTER TABLE t_stuinformation CHANGE age OLD INT;
-- 修改列的数据类型 ALTER TABLE 表名 MODIFY 列名 新数据类型
ALTER TABLE t_stuinformation MODIFY sex VARCHAR(2);
四、SQL–DML
DML(Data Manipulation Language):数据操作语言,包括insert ,delete ,update。
/**插入数据
方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;
方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2….., 值n);
方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)*/
INSERT INTO t_student2(id,birthday,sex,height,age) VALUES('1','2000-05-01','男',170.1,16);
INSERT INTO t_student2 SET id='2',birthday='2000-5-02',sex='女',height=165.8,age=17;
-- 复制表结构
CREATE TABLE t_student3 LIKE t_student2 ;
-- 把表内容复制到新的表结构当中去
INSERT INTO t_student3(id,birthday,sex,height,age) SELECT id,birthday,sex,height,age FROM t_student2;
-- 修改数据 UPDATE 表名 SET 列名 = ‘新值’WHERE 条件
UPDATE t_student2 SET age=20 WHERE id='1';
UPDATE t_student SET weight=180.0,height=190.0 WHERE id=15;
-- 删除数据 DELETE FROM 表名 WHERE 条件
-- TRUNCATE TABLE 表名;清空整张表
DELETE FROM t_student2 WHERE id='1';
五、SQL–DQL
DQL(Data Query Language):数据查询语言,是使用频率最高的,可以从表中查询数据,也可以从多个表中查询数据。
SELECT 100 FROM t_student3
SELECT 10*80 FROM t_student3
SELECT VERSION() FROM t_student3
-- 特定列查询
SELECT sex,birthday FROM t_student3
-- 全部列查询
SELECT * FROM t_student3
-- 排除重复行
SELECT DISTINCT id,sex,birthday,age FROM t_student3
-- length():获取参数值的字节个数
SELECT id,LENGTH(birthday)birthday,height FROM t_student2
-- char_length()获取参数值的字符个数
SELECT id,CHAR_LENGTH(NAME)NAME,height FROM t_student3
-- concat(str1,str2,.....):拼接字符串
SELECT CONCAT(NAME,':',sex) FROM t_student3
-- 单行函数
-- 逻辑处理
-- case when 条件 then 结果1 else 结果2 end; 可以有多个when
SELECT
NAME,
(CASE
WHEN height > 180
THEN '高'
WHEN height>170
THEN '正常'
ELSE '矮'
END)weight,
sex
FROM
t_student3
-- ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回原 本的值
SELECT NAME,IFNULL(height,'暂未登记')height FROM t_student3
-- if函数:if else的效果 if(条件,结果1,结果2)
SELECT IF(height>170,'高','矮')height FROM t_student3
/*分组函数
sum 求和、avg 平均值、
max 最大值、min 最小值、
count 计数 (非空)*/
SELECT SUM(id) FROM t_student3
SELECT COUNT(height) FROM t_student3
SELECT MAX(age) FROM t_student3
SELECT AVG(age) FROM t_student3
/*单行函数
数学函数
round(数值):四舍五入
ceil(数值):向上取整,返回>=该参数的最小整数
floor(数值):向下取整,返回<=该参数的最大整数 t
truncate(数值,保留小数的位数):截断,小数点后截断到几位
mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
rand():获取随机数,返回0-1之间的小数*/
SELECT ROUND(height) FROM t_student3
SELECT CEIL(height) FROM t_student3
SELECT FLOOR(height) FROM t_student3
SELECT TRUNCATE(height,0) FROM t_student3
SELECT MOD(height,2) FROM t_student3
SELECT RAND() FROM t_student3
/*日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间
curtime():返回当前时间,不包含日期 可以获取指定的部分,年、月、日、小时、分钟、秒
YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期)
str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1998-3-2', '%Y-%m-%d');
date_format:将日期转换成字符串
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日)') AS output ;
datediff:返回两个日期相差的天数*/
SELECT NOW() FROM t_student3
SELECT CURDATE() FROM t_student3
SELECT CURTIME() FROM t_student3
SELECT YEAR(register) FROM t_student3
SELECT MONTH(register) FROM t_student3
SELECT STR_TO_DATE('1998-3-2', '%Y-%m-%d') FROM t_student3
SELECT DATE_FORMAT(register, '%y年%m月') AS output FROM t_student3;
SELECT DATEDIFF(register,'2000-5-21')FROM t_student3
SELECT * FROM t_student3 WHERE DATEDIFF(register,CURTIME()) =0
/*条件查询
-- 使用WHERE 子句,将不满足条件的行过滤掉,WHERE 子句紧随 FROM 子句。 语法:select <结果> from <表名> where <条件
-- 比较 =, != 或<>, >, <, >=, <=
-- 逻辑 and 与 or 或 not 非*/
SELECT * FROM t_student3 WHERE age=17
SELECT * FROM t_student3 WHERE age!=17 # SELECT * FROM t_student3 WHERE age<>17 结果一样
SELECT * FROM t_student3 WHERE age=17 AND sex='男'
SELECT * FROM t_student3 WHERE age=17 AND height=198.0 OR sex='男'
/*模糊查询
-- LIKE :是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值 或数值型.
-- 通配符: % 任意多个字符,包含0个字符 _ 任意单个字符*/
SELECT * FROM t_student3 WHERE NAME LIKE 'j%'
SELECT * FROM t_student3 WHERE NAME LIKE 'j__'
-- between and 两者之间,包含临界值;
SELECT * FROM t_student3 WHERE age BETWEEN 16 AND 17
-- in 判断某字段的值是否属于in列表中的某一项
SELECT * FROM t_student3 WHERE age IN(17,16)
-- IS NULL(为空的)或 IS NOT NULL(不为空的)
SELECT * FROM t_student3 WHERE height IS NULL
SELECT * FROM t_student3 WHERE height IS NOT NULL
-- 使用union 时,mysql 会把结果集中重复的记录删掉,
SELECT
*
FROM
t_student3
WHERE age BETWEEN 16
AND 20
UNION
SELECT
*
FROM
t_student3
WHERE height BETWEEN 170.0
AND 180.0
-- 使用union all , mysql 会把所有的记录返回,且效率高于union。
SELECT
*
FROM
t_student3
WHERE age BETWEEN 16
AND 20
UNION ALL
SELECT
*
FROM
t_student3
WHERE height BETWEEN 170.0
AND 180.0
-- 排序 查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC asc代表的是升序,desc代表的是降序,如果不写,默认是升序
SELECT * FROM t_student3 WHERE age BETWEEN 16 AND 20 ORDER BY height DESC
-- 数量限制limit子句:对查询的显示结果限制数目 (sql语句最末尾位置)
-- mysql语法规则 先条件-->排序-->限制
SELECT * FROM t_student3 WHERE age BETWEEN 16 AND 20 ORDER BY height DESC LIMIT 0,1
-- 分组查询
-- 数据源 源位置 关键字 分组前筛选 原始表 group by子句的前面 where 分组后筛选 分组后的结果集 group by的后面 having
SELECT sex,COUNT(*) FROM t_student3 GROUP BY sex ORDER BY COUNT(*)
-- 分组前条件筛选 ->然后分组 -> 分组后对分组结果进行筛选 ->对分组结果进行排序 ->对分组结果进行限制
SELECT sex,COUNT(*) FROM t_student3 WHERE height>170 GROUP BY sex HAVING COUNT(*)>2 ORDER BY COUNT(*) LIMIT 0,1
六、MySql练习
-- 第一题
CREATE TABLE t_score(
stu_num INT,
stu_name VARCHAR(10),
stu_subject VARCHAR(10),
stu_score INT)
INSERT INTO t_score(stu_num,stu_name,stu_subject,stu_score)VALUES(1,'张三','语文',70),(1,'张三','数学',80),(1,'张三','英语',66),
(2,'李四','语文',50),(2,'李四','数学',75),(2,'李四','英语','80'),(3,'王五','语文',77),(3,'王五','数学',55),(3,'王五','英语',88),
(3,'王五','物理','90');
SELECT stu_num,stu_name,SUM(stu_score)AS 总成绩 FROM t_score GROUP BY stu_num,stu_name ORDER BY 总成绩 DESC
SELECT stu_num,stu_name,AVG(stu_score)AS 平均成绩 FROM t_score GROUP BY stu_num,stu_name ORDER BY 平均成绩 DESC
SELECT stu_subject,AVG(stu_score) AS 平均成绩 FROM t_score GROUP BY stu_subject ORDER BY 平均成绩 DESC
SELECT stu_num,stu_name,stu_score 数学成绩 FROM t_score WHERE stu_subject='数学'
-- 6.查询学生成绩,按照如下格式展示, 小于60分为C,60-79为B,80-100为A
SELECT stu_name,stu_subject,(CASE WHEN stu_score<60 THEN 'C' WHEN stu_score>=60 AND stu_score<=79 THEN 'B' ELSE 'A' END)FROM t_score
-- 7.只查询每门成绩都及格的学生(要求显示字段: 学号,姓名,课程,最高成绩)
SELECT * FROM t_score WHERE stu_num NOT IN (SELECT stu_num FROM t_score WHERE stu_score <60)
SELECT * FROM t_score WHERE stu_name IN(SELECT stu_name FROM t_score GROUP BY stu_name HAVING MIN(stu_score)>=60)
SELECT stu_num,stu_name,stu_subject,stu_score FROM t_score WHERE
SELECT stu_num,stu_name,COUNT(stu_subject)AS 课程总数 FROM t_score GROUP BY stu_num,stu_name HAVING 课程总数>3
-- 第二题 (当前页数-1)*页面数量 页面数量
SELECT stu_num,stu_name,stu_subject,stu_score FROM t_score LIMIT 0,3 -- 第一页
SELECT stu_num,stu_name,stu_subject,stu_score FROM t_score LIMIT 3,3 -- 第二页
SELECT stu_num,stu_name,stu_subject,stu_score FROM t_score LIMIT 6,3 -- 第三页
-- 第三题
CREATE TABLE t_win(
gamedate DATE,
message VARCHAR(5)
)
INSERT INTO t_win(gamedate,message)VALUES ('2005-05-09','胜'),('2005-05-09','胜'),('2005-05-09','负'),('2005-05-09','负'),('2005-05-10', '胜'),
('2005-05-10', '负'),('2005-05-10', '负')
SELECT gamedate,SUM(IF(message='胜',1,0))AS 胜,SUM(IF(message='负',1,0))AS 负 FROM t_win GROUP BY gamedate
-- 第五题
CREATE TABLE t_choice(
A INT,
B INT,
C INT
)
INSERT INTO t_choice(A,B,C)VALUES(1,2,3),(5,4,6),(9,8,7),(12,11,13)
-- 第四题
四.表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。
SELECT (CASE WHEN A>B THEN A ELSE B END)AS max(a,b) ,(CASE WHEN B>C THEN B ELSE C END)AS max(b,c) FROM t_choice
总结
本文主要介绍了数据库的基本概念,MySql数据库的特点,以及SQL语句的几种基本语言。本文的重点是SQL语句的编写,虽然难度不大,但都是SQL最基本的内容,对以后的学习有着重要的意义,需要反复练习从而能够熟练运用。