MySql

MySQL

• 启动,连接,断开和停止MySQL服务器
  1. 启动MySQL服务器
     1. 在window启动
        1. win+r --> services.msc(快速打开windows服务管理器)1
        2. 找到MySQL服务,双击打开,直接启动
     2. 在命令提示符下启动/停止
        • 此时注意,服务的名字不要写错,使用具有权限的用户去运行
        • win+r —> 已管理员形式运行dom窗口

​

3. 连接和断开MySQL服务

   1. 连接本地MySQL服务

   

   2. 连接远程的MySQL服务
      1. 对方开放了远程连接权限
      2. 通过inter网连接,对方的 IP
      3. 通过局域网连,对方的IP/同一个局域网下

4. mysql数据库中常用命令

   1. select now();现在的时间

   2. show databases; 查看数据库

   3. create database java220201; 创建数据库

   4. use java220201; 使用数据库

5. 数据库 的宏观了解

— 前期准备工作完成

SQL语言是用来操作数据库的数据的

mysql数据库里面可以创建很多数据库,众多数据库对象(表)的集合

MySQL数据库常用命令(SQL语言操作数据的常用命令,mysql数据库提供的命令)

• 创建数据库 : create database 数据库名;

• 删除数据库 : drop database 数据库名;

• 指定使用的数据库 : use 数据库名;

• 显示所有的数据库 : show databases;

• 创建表命令 : create table 表名(字段名 属性,字段名2 属性,…);

• 显示数据库中的所有表 : show tables;

• 查看表结构 : describe/desc 表名;

• 删除表 : drop table 表名;

• 表名重命名 : alter 表名 rename to 新表名;

• 增加列 : alter table 表名 add column 列名 列字段属性(长度);

• 添加表数据命令 : insert into 表名 (字段名1,字段名2,字段名3…) values(值1,值2,值3…);

PS : 在插入中文的时候,可能会报1366错误,目前数据库中的字符集不支持中文

解决步骤:

1. 查看目前数据库的字符集

• show variables like ‘character’;

2. 保证database和server的字符集支持中文;

• set 名字 = 字符集名字;

3. 如果上述不能成功,则单独修改表和表字段字符集

• alter table 表名 charset = utf8mb4;
• alter table 表名 modify 字段名 varchar(10) character set utf8 collate utf8_general_ci;

查询数据表命令

• 全字段查询+全表扫描 : select * from 表名;

  • ‘*’ 表示通配符,匹配所有字段

• 投影查询 : select 字段1,字段2,… from 表名;

• 如果字段名太长可以取别名: select 字段1 别名1 , 字段2 别名2… from 表名;

• 条件查询 : select 字段1,字段2,… from 表名 where 条件;

  select * from student where name = '李氏';
  

  • 查询条件连接符
    • 运算符 >,<,<=,>=,!=,<>,IS NULL.

• 插入NULL值 : insert into student(id,age,gender) values (4,‘55’,‘女’);

• 插入空值 : insert into student(id,name,age,gender) values (5,‘’,‘55’,‘女’);

• NULL不确定的,未知的值

• select * from student where name is NULL;

• select * from student where name is not NULL;
  

• 关键字

  • In, not,in,like,not like,between…and,not between…and, regexp;
  • select * from student where id [not] in (2,3);
    • 关键字in可以判断某个字段的值是否在指定的集合中.

• 模糊查询 : like

  • select * from student where name like ‘李%’;
  • % 表示0到多个任意字符
  • _ 表示一个任意字符
  • 也可以用正则表达式
  • 如果查询内容中含有特殊的字符,可以使用"\"对其进行转义

• between…and : 指定查询条件区间

• AND 可以用来联合多个条件进行查询(类似于&&)

  • 同时满足所有条件

• OR 可以用来联合多个条件进行查询(类似于 ||)

  • 只满足一个就可以

• 去除重复记录

  • distinct:可以去除查询结果中的重复记录.

• 使用order by对查询结果排序

  • asc按照升序进行排序
  • desc按照降序进行排序

  select * from student where gender = '男' order by id desc,name asc;
  

  先按照id进行降序,id相同的按照name升序排序

• 分组查询

  • 通过通过关键字GROUP BY 可以将数据划分到不同的组中,实现对记录进行分组查询

  • 在查询时,所查询的列必须包含在分组的类中,目的是使查询的数据没有矛盾

  • 单独使用GROUP BY查询只显示每组的一条记录

  • GROUP BY和GROUP_CONCAT()函数查询可以将每个组中的所有字段全部显示

  • 使用分组函数时 select 后面只跟两种字段(1.分组字段,聚合函数,其他均不合理)

> 求部门平均工资大于10000的信息
>
> select avg(salary) from employess group by department_id having avg(salary) > 1000;

• 多个字段进行分组,按顺序依次分组

  PS: 分组函数一般和聚集函数配合使用

  1. count() 查询总记录数
  2. max() 最大值
  3. min() 最小值
  4. avg() 平均数
  5. sum() 求和

• 分页查询

  • 用关键字limit限制查询结果的数量
  • 查询数据时,可能会查询出很多的记录,而用户需要的记录可能只是很少的一部分,这样就需要限制查询结果的数量
  • 关键字limit 可以对查询结果的记录条数进行限制,控制它的输出数量
  • limit 开始的索引，每页查询条数；(索引从零开始)

• 多表查询

  • 连接是把不同的表记录连接到一起进行查询,这种连接语句可以以多种高级方法来组合表记录

  • 内连接查询

    • 内连接查询是最普遍的连接类型,它们要求构成连接的每一部分的每个表的匹配,不匹配的行将排除
    • 内连接是最常见的例子就是相等链接,也就是连接后的表中的某个字段与表中的都相同,这种情况下,最后的结果只包含参加连接的表中与指定字段相等的行

    显示所有员的姓名,部门号,部门名称

    select t1.name t2.department_id ,t2.department_name

    from employess t1,departments t2

    where t1.department_id = t2.department_id;

  • 外连接查询

    • 外连接是指使用OUTER JOIN关键字将两个表连接起来,外连接生成的结果集不仅包含符合连接条件的行数据,而且还包含左表或右表或者两个表中不符合连接条件的行数据
    • 左外连接
      • left join 是指将左边中的所有数据分别与右表中的每条数据进行连接组合,返回的结果除内连接的数据外,还包含左边中不符合条件的数据,并在右边的相应列中添加NULL值
      • 多个连接条件不可使用where(其他数据库用法)

    以左外连接的形式查询员工名,部门名称

    select name ,department_name
    from employess left join departments
    on employess.department_id = departments.department_id;

    8.0不适用

    select name ,department_name
    from employess left join departments
    where employess.department_id = departments.department_id;

    –

    select name ,department_name
    from employess, departments
    where employess.department_id = departments.department_id(+);

    • 右外连接
      • right join 是指将右表中的所有数据分别与左表中的每条数据进行连接组合,返回的结果除内连接的数据外,还包含右边中不符合条件的数据,并在左边的相应列中添加NULL值

    以右外连接的形式查询员工名,部门名称

    select name ,department_name
    from employess right join departments
    on employess.department_id = departments.department_id;

    • 全外连接
      • 略 full outer join

  • 复合条件连接查询

    • 在连接查询时,也可以增加其他的限制条件

    select name ,department_name

    from employess right join departments

    on employess.department_id = departments.department_id and name like ‘%a%’;

  • 执行sql脚本文件

    • source 文件路径

    source d://employess.sql

  • 子查询(多层查询)

    • 子查询就是select 查询是另外一个查询的附属
    • 查询语句中可以嵌套多个查询语句,在外面一层的查询可以使用里面一层查询产生的结果集
    • 当遇到这样的多层查询时,MySQL从最内层的查询开始,然后从它开始向外向上移动到外层查询(主查询),这个过程每个查询产生的结果集都被赋给包围他的父查询,接着这个父查询被执行.
    • 内层子查询语句的执行结果位空值,那个外层的where就始终不会满足条件,这样的查询最后就必然是空值.

    查询名字中带有a字母的员工的信息

    1. select * from employess where name like ‘%a%’;

    2. 子查询方式

       先查询出名字带有a字母的员工名字

       根据这些名字查询员工的所有信息

       select * from employess where name in (select name from employess where name like ‘%a%’);

    查询员工信息,但是该员工所在部门号一定是存在于department是表中

    select * from employess where department_id in (select department_id from departments);

    显示所有员的姓名,部门号,部门名称

    select t1.name t2.department_id ,t2.department_name

    from employess t1,departments t2

    where t1.department_id = t2.department_id;

    PS : 从代码量,逻辑简单易读以及时间复杂度来看,子查询更加具备优势.

    带比较运算符的子查询

    查询工资大于8000的员工信息

    select * from employess where salary>=(select salary from employess where id = 8);

    查询技术支持部的员工的信息

    select * from employess where department_id = (select department_id from departments where department_name = ‘技术支持部’);

    查询employess表中工资既不是最低又不是最高的员工信息

    select * from employess t1,(select max(salary) max ,min(salary) min from employess) t2 where salary not in (t2.max,t2.min);

  • 单行操作符,<,>,>=,<=,!=,=

  • 子查询中使用(IN,ANY,ALL)关键字

    • ANY关键字

      • 必须与单行操作符结合使用,并且返回只要匹配自查的任何一个结果即可
      • 表示满足其中任意一个条件,只要满足内层查询语句返回的结果中任意一个,就可以通过该条件执行外层查询语句.

      在employess表中,查询工资大于1002号部门的任意一个员工工资的其他部门的员工信息.

      select * from employess where department_id != 1002 and salary > ANY(select salary from employess where department_id = 1002);

    • ALL关键字

      • 满足所有条件,使用ALL时,只有满足内层查询语句返回的所有结果,才可以执行外层循环

      在employess 表中,查询工资大于部门编号为1002的所有员工工资的员工信息

      select * from employess where salary > ALL(select salary from employess where department_id = 1002);

    • EXISTS 关键字

      • 内层查询语句不返回查询记录,而是返回一个真假值,如果内层查询语句查询到满足条件的记录,就返回一个真值(true),否则返回一个假值(false),当返回true时,外层查询语句将执行,否则外层不执行

      使用子查询,查询departments表中 是否存在2000号部门,如果存在则查询employess表中的记录

      select * from employess where exists(select * from departments where department_id = 2000);

      select * from employess where exists(select * from departments where department_id = 1002);

      使用子查询,查询departments表中 是否存在2000号部门,如果存在则查询employess表中id= 176员工的记录

      select * from employess where exists(select * from departments where department_id = 2000) and id = 176 ;

  • 合并查询结果

    • 是将多个select语句的查询结果合并在一起,使用UNION和UNION ALL
    • UNION : 是将所有查询结果合并在一起,然后去除相同的记录
    • UNION ALL : 简单的合并在一起

    查询employess 表中 name 字段和departments 表中 department_name字段,并使用UNION关键字合并查询结果

    select name from employess union all select department_name from departments;

    select name from employess union select department_name from departments;

  • 关联子查询

  • 在单行子查询和多行子查询中,内查询和外查询是分开执行的,也就是说,内查询的执行与外查询的执行是没有关系的,外查询仅仅是使用内查询的最终结果

  • 在一些特殊需求的子查询中,内查询的执行需要借助外查询,而外查询的执行又离不开内查询,这时,内查询和外查询是互相关联,这种子查询就称为关联子查询

  该岗位上的员工大于同职位的平均工资

  select id,name,salary from employess f where salary > (select avg(salary) from employess where job_id = f.job_id) order by job_id;

  查询所有管理者的下属员工信息

  select * from employess where id not in (select distinct manager_id from employess where manager_id is not null) and department_id is not null;

  • 自连接

    • 在应用系统开发中,用户可能会拥有"自引用式"外键,"自引用式"外键是指表中的一个列可以是该表主键的一个外键

    select emp2.name 上层管理者,emp2.name 下属员工 from employess emp1 left join employess emp2 on emp1.manager_id = emp2.id;

SQL注入问题

• 下列代码无论是使用statement 还是使用具备预编译的 prepareStatement都会引发SQL注入

    public void testFindAll(){
   
        //模拟用户名和密码数据
//        String name = "永志" ;
        String name = "1' or 1=1 -- '";
        String password = "123456";
        Statement stat = null;
        try {
   
            conn = JDBCUnit.getConnection();
            //2.获取连接
            String sql = "select * from user where name = '"+name+"' and password = '"+password+"';";
            //4.预编译语句(负责将sql语句通过通道conn放入数据库中执行.防止sql注入,预编译提升效率)
//            stat = conn.createStatement();
//            //5.执行sql语句
//            resultSet = stat.executeQuery(sql);
            ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            //遍历结果集
            if(resultSet.next()){
   
                Syste