0322-数据库与前后端的连接、数据库表的增删改查

前端

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src='js/jquery-3.7.1.min.js'></script>
<script>
//jquary+ajax发起请求
//传参形式不同 post用data{}传参
//get 只要能写地址的地方都可以发起get请求-->查询
//post ajax+jquery或form表单发起请求-------->修改 删除 添加
$.ajax({url:"demo?account=admin&password=123456",//请求路径type:"get",//请求方式 get post/*data:{account:"adminxsdf",password:"123456"},//参数域*/success:function(value){console.log(value);console.log(value.alexa)},//请求成功的回调函数error:function(){alert("出错啦")},//请求失败的回调函数})</script>
</head>
<body>hello
</body>
</html>

后端

package com.qc.servlet;import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;/*** Servlet implementation class lkj*/
@WebServlet("/demo")
public class lkj extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#HttpServlet()*/public lkj() {super();// TODO Auto-generated constructor stub}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*///getprotected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {System.out.println("get执行啦！");//接收参数String acc=request.getParameter("account");String pass=request.getParameter("password");String res="登录失败";if(acc.equals("admin")&&pass.equals("123456")) {res="{ \"name\":\"runoob\",\"alexa\":1000 }";}//设置编码request.setCharacterEncoding("utf-8");response.setCharacterEncoding("utf-8");//设置返回的数据为JSON格式response.setContentType("text/json;charset=utf-8");//给前端响应数据（相当于return）response.getWriter().write(res);}/*** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)*/protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {System.out.println("post执行啦");}}

Navicat Premium

-- 查找(不区分大小写)
select name,age,sex,id from student 
select *from student-- 条件查询 where子语句
-- 运算符
-- =等于
-- >大于
-- >=大于等于
-- <小于
-- <=小于等于
-- != <> 不等于
select *from student where sex="男"
select *from student where age=20
-- between ..and   []select *from student where age between 20 and 25
-- and 与
-- or 或
-- not 非 主要用在is inselect *from student where age>20 and age<25
select *from student where age<20 or age<25select *from student where age=20 or age=25 or age=23-- in 包含
select *from student where age in(20,25,23)
select *from student where id not in(1,5,3)-- is null 判空
select *from student where age is not null-- 模糊查找 like _代表一个字符 %代表任意个字符
select *from student where name like "张_"
select *from student where name like "%张%"-- 分页查询 limit
-- limit a,b a表示起始索引值(0开始) b表示查询的个数
-- limit b offset a
select *from student limit 0,5
select *from student limit 5,5
select *from student limit 10,5-- 一页大小 pageSize  当前页码 page
select *from student limit (page-1)*pageSize,pageSizeselect *from student where name like "%张%" limit 0,3-- 排序子语句
-- order by 列名 desc 降序| asc 升序（默认）
select *from student order by age desc-- where 排序 限制
select * from student where sex="女" order by age desc limit 0,3-- 聚合函数 分组函数
-- min() 最小
-- max() 最大
-- sum() 求和
-- avg() 求平均
-- count() 求数量 count(字段名称)不统计值为null的字段
select sum(age) from student
-- 后续修改条件用having ；group by分组
select avg(age),class from student where sex="女" group by class having class=1-- 添加
insert into student(name,age,sex) values("王祖贤",26,"女")insert into student(name,age,sex) values("王祖",26,"女")
-- 修改
update student set name="韩孝周",age=24,sex="女" where id=5-- 删除
delete from student where id=2
delete from student where id in (3,6,7)-- 多表查询
-- 重命名 as 新名字 as可省
select * from student,class.c_name where student.c_id=class.id 
select s1.*,class.c_name as 班级名称 from student as s1,class where s1.c_id=class.id
select 学生表.*,cname,score from 学生表,课程表,选课表 where 学生表.id=选课表.s_id and 课程表.id=选课表.c_id-- 连表查询
-- 内连接 join... on
-- 外连接
--   左外连接 left join...on
--   右外连接 right join...on
select *from student right join class on student.c_id=class.idselect 学生表.*,cname,score from 学生表 join 课程表 join 选课表 on 学生表.id=选课表.s_id and 课程表.id=选课表.c_id
select 学生表.*,cname,score from 学生表 join 选课表 on 学生表.id=选课表.s_id join 课程表 on 课程表.id=选课表.c_id