Mysql

单表

基本查询

    //建库
    //create database if not exists db1;

    //展示数据
    //show databases;

    //查看student3表的表结构.+
    //desc student3.

    //查询
    //select * from student

    //根据id查询
    //select * from student where id = #{id}

    //查询指定字段 name,entrydate 并返回
    //select name,entrydate from tb_emp;

    //新增
    //insert into user(id, name, age, gender, phone) VALUES (null,'光明右使',48,'1','18800000005');

    //删除
    //delete from student where id = #{id}

    //修改
    //update student set name = #{name},birthday = #{birthday},address = #{address} where id = #{id}

    // 查询所有员工的 name,entrydate, 并起别名(姓名、入职日期)  --- as 关键字可以省略
    //select name as '姓名' ,entrydate as '入职日期' from tb_emp;
    //select name '姓名',entrydate '入职日期' from tb_emp;

    //-- 4. 查询员工有哪几种职位(不要重复)
    //select distinct job from tb_emp;

条件查询

//条件查询----------------------------------------------------------------------------------
    //查询在 id小于等于5 的员工信息
    //select * from tb_emp where id <=5;

    //查询 密码不等于 '123456' 的员工信息
    //select * from tb_emp where password != '123456';

    // 查询 没有分配职位 的员工信息
    //select * from tb_emp where job is null;

    //查询 有职位 的员工信息
    //select * from tb_emp where job is not null;


    //查询 id<=5 并且 job=2  的员工信息
    //  * from tb_emp where id <=5 and job = 2;

    //查询 id<=5 或者 job=2  的员工信息
    //select * from tb_emp where id <=5 or job = 2;

    //查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
    // select * from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';
    //select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

    //查询职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
    //select * from tb_emp where job = 2 or job = 3 or job = 4;
    //select * from tb_emp where job in (2,3,4);

    //模糊匹配(_匹配单个字符, %匹配任意个字符)
    //查询姓 '张' 的员工信息
    //select * from tb_emp where name like '张%';

    //查询姓名中包含 '三' 的员工信息
    //select * from tb_emp where name like '%三%';
    //查询姓'张'，并且姓名为三个字的员工信息
    //select * from tb_emp where name like '张__';

聚合函数

    //统计该企业员工数量
    //select count(*) from tb_emp;

    //. 统计该企业最早入职的员工的入职日期
    //select min(entrydate) from tb_emp;

    //统计该企业最迟入职的员工的入职日期
    //select max(entrydate) from tb_emp;

    //统计该企业员工ID的平均值
    //select avg(id) from tb_emp;

    //统计该企业员工的ID之和
    //select sum(id) from tb_emp;

    //分组过滤-------------------------------------------------------------
    //-- 1. 根据性别分组， 统计男性和女性员工的数量
    //select gender,count(1) from tb_emp group by gender;

    //. 先查询入职时间在 '2015-01-01' (包含) 以前的员工
    //select * from tb_emp where entrydate <= '2015-01-01';

    //. 先查询入职时间在 '2015-01-01' (包含) 以前的员工，并对结果根据职位分组
    //select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job;

    //. 先查询入职时间在 '2015-01-01' (包含) 以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
    //select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(1) > 2;

    //排序
    //根据入职时间，对员工进行降序排序
    //select * from tb_emp order by entrydate desc;

   //根据入职时间，对员工进行升序排序
   //select * from tb_emp order by entrydate asc;

   //根据入职时间对公司的员工进行升序排序，入职时间相同再按照ID进行降序排序
   //select * from tb_emp order by entrydate asc, id desc;

分页查询

   //查询第1页员工数据, 每页展示5条记录
   //select * from tb_emp limit 0,5;

   //查询第2页员工数据, 每页展示5条记录
   //select * from tb_emp limit 5,5;

   //查询第3页员工数据, 每页展示5条记录
   //select * from tb_emp limit 10,5;

   //查询第4页员工数据, 每页展示5条记录
   //select * from tb_emp limit 15,5;

多表

一对多建立外键

   create database db3;-- 建库
   use db3; -- 切换库;

   创建班级表
   create table class(
   id int primary key,
   name varchar(30)
   );

   创建学生表
   create table student(
           id int primary key,
           name varchar(30),
   class_id int
   );
    插入数据
   insert into class values (1,'JAVAEE166'),(2,'JAVAEE167');
   insert into student values (1,'张三',1),(2,'李四',2),(3,'王五',2);

   **********************添加外键约束，这是在类外添加**********************
   alter table student add constraint class_id_fk foreign key(class_id) references class(id);

多对多建立外键

需要建立中间表

 create database db4;
  use db4;

-- 创建学生表
    create table student(
            id int primary key,
            name varchar(30)
    );

-- 创建课程表
    create table course(
            id int primary key,
            name varchar(30)
    );

-- 建立中间表
    create  table student_course(
            id int primary key auto_increment,
            student_id int,
            course_id int,
            ***********************添加外键约束，这是在类内添加*****************************
            constraint student_id_fk foreign key (student_id) references student(id), -- 外键约束
            constraint course_id_fk foreign key (course_id) references course(id) -- 外键约束

一对一建立外键

需要给加主键的地方设置唯一约束

 create database db5;
    use db5;

-- 创建用户表
    create table tb_user(
            id int unsigned  primary key auto_increment comment 'ID', //comment是备注的意思
            name varchar(10) not null comment '姓名',
            gender tinyint unsigned not null comment '性别, 1 男  2 女',
            phone char(11) comment '手机号',
            degree varchar(10) comment '学历'
            ) comment '用户基本信息表';

-- 用户身份信息表
    create table tb_user_card(
            id int unsigned  primary key auto_increment comment 'ID',
            nationality varchar(10) not null comment '民族',
            birthday date not null comment '生日',
            idcard char(18) not null comment '身份证号',
            issued varchar(20) not null comment '签发机关',
            expire_begin date not null comment '有效期限-开始',
            expire_end date comment '有效期限-结束',
            user_id int unsigned not null unique comment '用户ID', -- 外键,指向用户表主键, 必须有唯一约束,才能保证1对1

            ****************************外键约束******************************
            constraint fk_user_id foreign key (user_id) references tb_user(id) -- 外键约束
            ) comment '用户身份信息表';

多表查询

交叉查询

-- 解释
	使用左表中的每一条数据分别去连接右表中的每一条数据, 将所有的连接结果都展示出来
    -- 语法
 	select * from  左表,右表
    -- 案例
	select * from emp,dept

内连接

内连接--------------------------------------------------------------：
	-- 解释
	使用左表中的每一条数据分别去连接右表中的每一条数据, 仅仅显示出匹配成功的那部分

   -- 语法
	隐式内连接: select * from  左表,右表 where 连接条件
	显示内连接: select * from 左表 [inner] join 右表 on 连接条件

    -- 案例
    -- 隐式内连接
    select * from emp,dept where emp.dept_id = dept.id;
    select * from emp e,dept d where e.dept_id = d.id;-- 推荐大家使用别名的形式

    -- 显示内连接
    select * from emp e inner join dept d on e.dept_id = d.id; -- 记住这个
    select * from emp e join dept d on e.dept_id = d.id;

左外连接

 -- 解释
	首先要显示出左表的全部, 然后使用连接条件匹配右表,能匹配中的就显示,匹配不中的显示为null

    -- 语法
	select * from 左表 left [outer] join 右表 on 连接条件

    -- 案例
	select * from emp e left outer join dept d on e.dept_id = d.id;

	右外连接----------------------------------------------------------
	-- 解释
	首先要显示出右表的全部, 然后使用连接条件匹配左表,能匹配中的就显示,匹配不中的显示为null

     -- 语法
	select * from 左表 right outer join 右表 on 连接条件

    -- 案例
	select * from emp e right outer join dept d on e.dept_id = d.id;

子查询

	-- 解释:
	一个查询使用了另一个查询的结果

     -- 1: 查询工资小于平均工资的员工有哪些？(子查询结果为一个值)
     -- 1 查询平均工资
     select avg(salary) from emp; -- 6391.5
     -- 2 查询谁的工资小于上面的数
     select * from emp where salary < 6391.5;
     -- 3. 合并
     select * from emp where salary < (select avg(salary) from emp);


     -- 2: 查询工资大于5000的员工，所在部门的名字 (子查询结果为多个值)
     -- 1 查询工资大于5000的员工的部门id
     select distinct dept_id from emp where salary > 5000; -- 1 2
     -- 2 查询这些部门id的名称
     select name from dept where id in (1,2);
     -- 合并
     select name from dept where id in (select distinct dept_id from emp where salary > 5000);


     -- 3: 查询出2011年以后入职的员工信息，包括部门信息 (子查询结果为一张表)
     -- 查询出2011年以后入职的员工信息
     select * from emp where join_date >= '2011-01-01';
     -- 使用上面的结果连接部门表,获取部门信息
     select * from
         (select * from emp where join_date >= '2011-01-01')
     as t1 left outer join dept d on t1.dept_id = d.id

动态sql

if标签和where标签

<!--
    字符串需要跟null和空串比较
    其他类型只要跟null比较

    if：使用test进行条件判断，只有条件成立，条件中的sql才会生效
    where：只会在<where>标签内部有内容的情况下才插入where子句，而且会自动去除子句的开头的AND或OR。
-->
    <select id="findListif" resultType="com.itheima.domain.Emp">
    select * from emp
        <where>
            <if test="name!=null and name!=''">
    name like concat('%',#{name},'%')
            </if>
            <if test="gender!=null">
    and gender= #{gender}
            </if>
            <if test="begin!=null and end!=null">
    and entrydate between #{begin} and #{end}
            </if>
        </where>
    </select>

set标签

  <--set：动态地在set代码块之前加入SET关键字，并删掉set代码块中最后一个多余的逗号（用在update语句中）-->
    <update id="updatee">
    update emp
        <set>
            <if test="username!=null and username!=''">
    username = #{username},
            </if>
            <if test="name!=null and name!=''">
    name=#{name},
            </if>
            <if test="gender!=null">
    gender=#{gender},
            </if>
            <if test="createTime!=null">
    create_time=#{creaetTime}
            </if>
        </set>
    where id= #{id}
    </update>

foreach标签

foreach标签
* collection：集合名称
* item：集合遍历出来的元素
* separator：每一次遍历使用的分隔符
* open： 遍历开始前拼接的片段
* close：遍历结束后拼接的片段
    <delete id="deleteById">
    delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>+

定义sql片段

* 定义SQL片段:  <sql id="selectUser"></sql>
* 引用SQL片段:  <include refid="selectUser"></include>
<sql id="common">
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               dept_id,
               create_time,
               update_time
        from emp;
</sql>

<select id="findAll" resultMap="resultmap">
        <include refid="common"></include>
</select>