小白学习Java第二十九天

今日内容

1. 数据库的导入和导出
2. DQL

基本查询

条件查询

排序查询

函数

分组查询

分页查询

连接查询

一. 导入导出数据库

1.导出数据库

选中要导出的数据库，鼠标右键备份/导出——>备份数据库，转储到SQL...

 

选择导出文件存储路径并导出

 

1. 导入数据库

选择服务器鼠标右键执行SQL脚本

 

选择之前导出的sql文件并执行

 

二. DQL

1、基本查询

语法：select 查询列表 from 表名;

1）查询全部列 *通配符表示所有列

2）筛选部分列

3）查询起别名 as

2、条件查询

语法：select 查询列表 from 表名 where 条件;

1. 单条件查询
2. 多条件查询，使用and  or  not
3. 空值查询is null / is not null
4. 范围查询in/between...and
5. 模糊查询like

3、排序查询

语法：
         select 查询列表
         from 表名
         [where 条件]
         order by 排序列表 asc|desc;

注意：默认是升序排列，ASC关键字可以省略不写，如果需要降序排列，DESC关键字必须要写，针对多列排序时，每个列的后面都需要指定排序方式，如不指定均采用默认的升序排列。

1. 升序排列：ASC 默认值
2. 降序排列：DESC

3）多列排序：分主排序列和次排序列，先按主进行排序，如果存在重复数据，再按次排序。

1. 常见函数

1. 字符串函数

length(str)        获取字符串长度，UTF8下1个汉字3个字节,gbk下1个汉字2个字节

lower(str)          转小写

upper(str) 转大写

substr(str,index,len) 截取字符串

lpad(str,n,a)              左填充

rpad(str,n,a)              右填充

trim(str)   去除两侧空格

concat(str1,str2,....) 字符串拼接

-- length字节为单位 UTF8编码汉字3个字节  字母与数字是1个字节 SELECT LENGTH('abc'); SELECT LENGTH('中国'); -- lower转小写 SELECT LOWER('abcABC'); -- upper转大写 SELECT UPPER('ABCabc'); -- 截取字符串 参数1：待截取的原始字符串，参数2：起始下标从1开始，参数3：截取长度 SELECT SUBSTR('hello',2,3) /* lpad(str,n,a)左填充：str：原始字符串，n：总长度，a：填充字符 作用：如果字符串str的长度不够n时，用a填充其左面直到长度为n */ SELECT LPAD('中国人',9,'#'); -- rpad()右填充 SELECT RPAD('中国人',9,'#'); -- trim()去除两侧空格 SELECT TRIM('    abc    '); -- concat()字符串拼接     SELECT CONCAT('how','are','you');

1. 日期函数

now()        获取系统当前时间

year(date)/month(date)/day(date) 年  月   日

date_format(date,’%y%m%d%h%i%s’) 指定的日期格式

curdate()           当前日期

curtime()           当前时间

current_timestamp 当前时间 年月日

-- now()获取系统当前时间 SELECT NOW(); -- year()/month()/day() 年  月   日 SELECT YEAR(NOW()); SELECT MONTH(NOW()); SELECT DAY(NOW()); -- date_format() 指定的日期格式 SELECT DATE_FORMAT(NOW(),'%Y%m%d%H%i%s'); -- curdate()当前日期 SELECT CURDATE(); -- curtime()当前时间 SELECT CURTIME(); --current_timestamp SELECT CURRENT_TIMESTAMP;

1. 聚合函数:多与分组查询一起使用

max()        最大值

min()        最小值

sum()        求和

count()     计数

avg()          平均值

SELECT MAX(age) FROM stu; SELECT MIN(age) FROM stu; SELECT AVG(age) FROM stu; SELECT COUNT(*) FROM stu; SELECT SUM(age) FROM stu;

5、分组查询

定义：通过group by 对原始表中的数据先进行分组再进行数据汇总（聚合函数）的一种查询。

分组查询的小技巧：

1)一般在题意描述中都会出现一个“每”字

2)分组查询中select关键字后面的列是有限制的，能出现在select后面的列只有两种情况：一是被分组的列，二是包含在聚合函数中的列。
语法：
         select 分组字段和分组函数
         from 表名   
         [where 条件] 
         [group by 分组字段]  
         [having 分组后筛选] 
         [order by 排序列表 asc|desc]

-- 查询每种学历有多少人 SELECT degree 学历,COUNT(*) AS 人数 FROM stu GROUP BY degree; -- 查询男生和女生各有多少人 SELECT sex AS 性别,COUNT(*) AS 人数 FROM stu GROUP BY sex; -- 查询哪些学历的人数超过3人 SELECT degree 学历,COUNT(*) AS 人数 FROM stu GROUP BY degree HAVING COUNT(*)>=3 ORDER BY COUNT(*)DESC; -- 查询每种学历的人数，并按人数进行降序排列 SELECT degree 学历,COUNT(*) AS 人数 FROM stu GROUP BY degree ORDER BY COUNT(*); -- 查询男生每种学历的人数 SELECT degree 学历,COUNT(*) AS 人数 FROM stu WHERE sex = 1 GROUP BY degree;

6、分页查询

关键字：limit

limit row; -- 限制返回的行数

limit startindex,pagesize; -- startindex:每页中第一条记录的起始下标  -- pagesize：每页数据量

SELECT * FROM stu LIMIT 3; /*    currentPage        startIndex pageSize    第1页：   0             3    第2页：   3             3    第3页：   6             3    发现规律： starIndex = pageSize*(currentPage-1); */ SELECT * FROM stu LIMIT 0,3; SELECT * FROM stu LIMIT 3,3; SELECT * FROM stu LIMIT 6,3;

1. 子查询

1. 概述
   一个查询语句中内嵌另一个完整的查询语句，被嵌套的语句称为子查询或者内查询
   外面的语句称为主查询或者父查询。
2. 语法
   select (子查询)
3. 特点
   1. 子查询必须写在小括号中
   2. 子查询优先执行，父查询需要用到子查询的结果
   3. 子查询结果
         单行子查询：结果只有一个 搭配的符号 =  >   <   >=   <=   <>
         多行子查询：结果多个 搭配的符号 in / not in / any / all

-- 1）查询和李小蒙年龄相同的学生信息 SELECT age FROM stu WHERE NAME = '李小蒙'; SELECT * FROM stu WHERE age = (SELECT age FROM stu WHERE NAME = '李小蒙'); -- 查询比李小蒙年龄大的同学的信息 SELECT * FROM stu WHERE age > (SELECT age FROM stu WHERE NAME = '李小蒙'); -- 2）查询年龄最大的学生姓名 SELECT NAME FROM stu WHERE age = (SELECT MAX(age) FROM stu); -- 3）查询和赵四年龄相同的学生信息 SELECT * FROM stu WHERE age IN (SELECT age FROM stu WHERE NAME='赵四'); -- 查询比赵四年龄大的同学信息(大于最小) SELECT * FROM stu WHERE age > ANY(SELECT age FROM stu WHERE NAME='赵四'); -- 查询比赵四年龄大的同学信息(大于最大) SELECT * FROM stu WHERE age > ALL(SELECT age FROM stu WHERE NAME='赵四');

1. 表关系

在一个关系型数据库中，利用关系可以避免表中数据的冗余（重复数据），保证数据紧密性和方便查阅。

数据库中表关系有三种：

一对一：ONE_TO_ONE    人-身份证号    丈夫-妻子

一对多：ONE_TO_MANY   也叫多对一，表示同一种关系。 班级-学生   用户-订单  

多对多：MANY_TO_MANY  学生-课程   商品-订单

1. 一对一

特点：

• 数据库表中存在两张表，其中一张表的主键是另外一张表的外键，即两张表的主键关联
• 外键所在的表叫从表，另外一张表叫主表
• 添加数据的时候需要先添加主表数据，后添加从表数据
• 删除数据的时候需要先删除从表数据，后删除主表数据

#创建丈夫表 CREATE TABLE husband( hid INT PRIMARY KEY, hname VARCHAR(40) NOT NULL ); #创建妻子表 CREATE TABLE wife( wid INT PRIMARY KEY, wname VARCHAR(40) NOT NULL ); #创建外键约束 alter table wife add constraint foreign key (wid) references husband(hid) #插入数据 INSERT INTO husband(hid,hname) VALUES(1,'付笛生'); INSERT INTO husband(hid,hname) VALUES(2,'张嘉译'); INSERT INTO husband(hid,hname) VALUES(3,'唐伯虎'); INSERT INTO wife(wid,wname) VALUES(3,'秋香'); INSERT INTO wife(wid,wname) VALUES(2,'王海燕'); INSERT INTO wife(wid,wname) VALUES(1,'任静');

1. 一对多

特点：

• 在两个表中，一张是多方表，一张是一方表，通常设置外键在多方表中，需要单独设置一个字段来作为外键，外键和主表的主键进行关联。
• 外键所在的表叫从表，外键关联的表叫主表
• 增加数据的时候需要先增加主表数据，后增加从表数据
• 删除的时候先删除从表数据后删除主表数据（如果删除主表数据，需要确认当前主表下面没有和从表数据有关联）

#创建部门表 CREATE TABLE dept( did INT PRIMARY KEY, dname VARCHAR(40) NOT NULL ); #创建员工表 CREATE TABLE emp( eid INT PRIMARY KEY AUTO_INCREMENT, did INT , ename VARCHAR(40), FOREIGN KEY (did) REFERENCES dept(did) ); #插入数据 INSERT INTO dept VALUES(1,'市场部'),(2,'教学部'),(3,'行政部'); INSERT INTO emp(did,ename)VALUES(1,'秋香'),(1,'王海燕'),(2,'任静'),(3,'付笛生'),(3,'张嘉译');

1. 多对多

特点：

• 有两张表，还需要一张中间表，中间表用来存其他两张表的外键。多对多关系中，中间表是从表，其他两个表是主表。
• 增加数据的时候需要先增加主表，后增加中间表数据
• 删除先删除从表也就是中间表数据，在删除主表数据。如果要删除主表数据需要先确定中间表中有没有关联的数据存在，如果有不能删除。

#创建课程表 CREATE TABLE course( cid INT PRIMARY KEY, cname VARCHAR(40) ); #创建中间选课表 CREATE TABLE stu_conn_course( id INT, cid INT, FOREIGN KEY(id) REFERENCES stu(id), FOREIGN KEY(cid) REFERENCES course(cid) ); #插入数据 INSERT INTO course VALUES(1,'Java'),(2,'UI'),(3,'大数据'); SELECT * FROM course; SELECT * FROM stu; SELECT * FROM stu_conn_course; INSERT INTO stu_conn_course VALUES(1,1),(1,2),(2,1),(3,2); #测试删除 DELETE FROM stu_conn_course WHERE cid = 1; DELETE FROM course WHERE cid = 1;

9、连接查询(多表查询)

连接查询定义：通过一定的连接条件将多个表中数据查询出来。

分类：内连接   外连接

1）内连接

实现方式：隐式内连接   显示内连接

 

1. 隐式内连接：通过where关键字指定连接条件的查询。

语法：

select 列名1,列名2......(这些列来自于多个表)

from A,B

where A.id = B.id [and/or/not 查询条件]

案例：

-- 查询学生的姓名，年级名，年龄 SELECT NAME, gname ,age FROM stu,grade WHERE stu.gid = grade.gid; -- 查询女学生的姓名，年级名，年龄 SELECT NAME, gname ,age FROM stu,grade WHERE stu.gid = grade.gid AND sex = 0; -- 多表连接 -- 查询学生的姓名，年龄，所选课程名称 SELECT NAME,age,cname FROM stu s,course c,stu_conn_course sc WHERE s.id = sc.id AND c.cid = sc.cid

1. 显式内连接：通过inner join ...on来实现多表之间的连接查询。

语法：

select 列名1，列名2.....(列也是来自于多张表)

from A inner join B on A.id = B.id

[where  查询条件]

案例：

-- 查询学生的姓名，年级名，年龄 SELECT NAME, gname ,age FROM stu INNER JOIN grade ON stu.gid = grade.gid; -- 查询女学生的姓名，年级名，年龄 SELECT NAME, gname ,age FROM stu INNER JOIN grade ON stu.gid = grade.gid WHERE sex = 0; -- 多表连接 -- 查询学生的姓名，年龄，所选课程名称 SELECT NAME,age,cname FROM stu s INNER JOIN stu_conn_course sc ON s.id = sc.id INNER JOIN course c ON c.cid = sc.cid;

注意：内连接查询中，多个表之间没有先后顺序要求。

2）外连接

定义：外连接是以一张表为基表，返回基表中所有记录及连接表中符合条件的记录，如果连接表中没有匹配数据则用null值填充。

分类：左外连接    右外连接

1. 左外连接：以左表为基表，返回左表中所有记录以及右表中符合条件的记录，如果右表中没有匹配的数据是用null填充。

 

语法:left join .... on

select 列名1，列名2.....(列也是来自于多张表)

from A left join B on A.id = B.id

[where  查询条件]

1. 右外连接:以右表为基表，返回右表中所有记录以及左表中符合条件的记录，如果左表中没有匹配的数据是用null填充。

 

语法:

select 列名1，列名2.....(列也是来自于多张表)

from A right join B on A.id = B.id

[where  查询条件]

案例：

-- 左外连接：查询所有班级名称和学生姓名，包括没有学生的班级也要显示出来 SELECT gname ,NAME FROM grade LEFT JOIN stu ON stu.gid = grade.gid; -- 右外连接：查询所有班级名称和学生姓名，包括没有学生的班级也要显示出来 SELECT gname ,NAME FROM stu RIGHT JOIN grade ON stu.gid = grade.gid;

注意：外连接查询中左表与右表的顺序不能随意调换。如果是左外连接，left关键字左边的表是基表，如果是右外连接，right关键字右边的表是基表。

查询的原则：
         1. 数据来源于多张表使用表连接
        2. 数据来源于一张表使用子查询

三． 综合案例

在图书数据库的基础上，为book表添加一个新的字段cid，与图书分类表中的cid进行外键关联，同时，在图书表中创建外键约束。

-- 给book表添加新列

ALTER TABLE book ADD cid INT ;

-- 添加外键约束

ALTER TABLE book ADD CONSTRAINT fk_cid FOREIGN KEY (cid) REFERENCES booktype(cid);

完成以下查询练习：

-- 1.查询图书价格在30-70之间的图书信息

SELECT * FROM book WHERE price >=30 AND price<=70;

SELECT * FROM book WHERE price BETWEEN 30 AND 70;

-- 2.查询书名中包含“计算机”的图书信息

SELECT * FROM book WHERE title LIKE '%计算机%';

-- 3.查询2020年出版的图书的书名、作者及出版日期

SELECT title,author,pdate FROM book WHERE pdate LIKE '2020%'

-- 4.查询表中所有信息并将列指定别名显示

SELECT id 编号,title 书名,price 价格,author 作者,publisher 出版社,description 描述,pdate 出版日期

FROM book;

-- 5.查询图书的书名，价格，作者，并按价格进行降序排列

SELECT title,price,author FROM book ORDER BY price DESC;

-- 6.查询所有图书信息，并且先按价格进行降序排课，再按出版日期进行升序排列

SELECT * FROM book ORDER BY price DESC,pdate ASC;

-- 7.查询表中前两条图书的信息

SELECT * FROM book LIMIT 2;

-- 8.查询出版社是“北京”的图书的书名和作者

SELECT title,author FROM book WHERE publisher LIKE '%北京%';

-- 9.查询作者姓杨，且名字是2个字的图书信息

SELECT * FROM book WHERE author LIKE '杨_';

-- 10.查询作者是冰心且价格在50元以上的图书信息

SELECT * FROM book WHERE author = '冰心' AND price > 50;

-- 11.查询不是北京的出版社出版的图书信息

SELECT * FROM book WHERE publisher NOT LIKE '%北京%';

-- 12.查询价格最高的图书信息

SELECT * FROM book WHERE price = (SELECT MAX(price) FROM book);

-- 13.分页查询，假设每页显示3条数据，查询第2页3本图书的信息

SELECT * FROM book LIMIT 3,3;

-- 14.查询每种分类下图书的数量

SELECT cid,COUNT(*) FROM book GROUP BY cid;

-- 15.查询每类图书的平均价格

SELECT cid,AVG(price) FROM book GROUP BY cid;

-- 16.查询显示图书的分类名称、图书名和图书的价格

SELECT cname,title,price

FROM booktype  t INNER JOIN book b ON t.cid = b.cid;

 

-- 17.查询所有图书的分类名和图书名，包括没有图书的分类信息也要显示

SELECT cname,title

FROM booktype t LEFT JOIN book b ON t.cid = b.cid;