MySql的相关使用

一、数据库概述

 存储数据的仓库

二、Sql语句–库、表操作

1.数据库

CHARACTER SET	指定数据库采用的字符集
COLLATE	指定数据库字符集方式

 A.创建数据库
  a.创建数据库mydb1：

create database mydb1;

  b.创建数据库mydb2，编码集为gbk：

create database mydb2 character set gbk;

  c.创建数据库mydb3，如果不存在，再执行，也不报错。

create database if not exists mydb3;

  d.创建数据库mydb4，编码集为utf-8,带有校验规则

create database mydb4 character set utf8 collate utf8_bin;

 B.查看全部数据库名称

show databases;

 C.查看数据库创建语句

show create database mydb2;

 D.删除数据库
  a.删除数据库mydb3。

drop database mydb3;

  b.删除数据库mydb3，如果存在，执行删除，不报错。

drop database if exists mydb3;

 E.修改数据库的编码集为utf-8,带有校验规则

alter database mydb2 character set utf8 collate utf8_bin;

 F.选择数据库（必须选择数据库，才能操作选中的数据库中的标以及数据）

use mydb1；

 G.查看当前使用的数据库名称

select database();

2.操作表
 A.创建表

create table user(
id int,
name char(10),
password char(20),
birthday date
);

 B.查看数据库中所有的表

show tables;

 C.查看表创建语句

show create table user;

 D.创建employee表

create table employee(
id int,
name varchar(10),
gender char(2),
birthday date,
entry_date date,
job varchar(20),
salary double,
resume text
);
#添加主键约束和自动增长约束的employee表：
create table employee(
id int primary key auto_increment,
name varchar(20),
gender varchar(2),
birthday date,
entry_date date,
job varchar(20),
salary double,
resume text
);

 E.查看表结构

desc table_name;
desc employee;

 F.修改数据库表
  a.添加字段

alter table table_name add column_name dateType;

  b.修改字段

alter tabel table_name modify column_name dateType;

  c.删除字段

alter table table_name drop column_name;

  d.修改表名

rename table employee to emp;

  e.修改字段名称

alter table emp name username varchar(20);

  f.修改表的字符集

alter table table_name character set 字符集；

 G.删除表

drop table emp;

 练习：
  a.在上面员工表的基本上增加一个image列

alter table employee add image blob;

  b.修改job列，使其长度为60

alter table employee modify job varchar(60);

  c.删除gender列

alter table employee drop gender;

  d.表名改为emp

rename table employee to emp;

  e.修改表的字符集为utf8

alter table emp character set utf8;

  f.列名name修改为username

alter table emp change name username varchar(20)

3.表数据的操作
 A.插入数据
  a.基本语句

insert into table_name values(value......);

insert into table_name(id,name) values(null,'mxb');

  b.向employee表插入数据：
   第一种方式：

insert into employee values(null,'ls','m','1999-01-01','2000-01-01','dance',10000.0,'like dance');
insert into employee values(null,'gfs','f','1899-01-01','2100-01-01','drive',10.0,'like driving');
insert into employee values(null,'李四','m','1099-01-01','2090-01-01','rap',12000.0,'like rap');

   第二种方式：

insert into employee(id,name) values(null,'mxb');
#自动增长的字段也可以添加数值，这个数值一定在表中没有出现过，才能成功添加这条数据。
insert into employee values(4,'pq1','m','1099-01-01','2090-01-01','rap',null,'like rap');

  c.插入数据的注意事项：
    (1).字符串和日期类型的数据需要使用单引号或双引号括起来。
    (2).如果字段为自动增长，则这个字段不需要填写具体值，填写null即可完成自动添加数据。
    (3).在插入语句中，表名后如果指定了字段名称，则在values关键字之后，需要按照指定的顺序添加字段对象值。
    (4).在插入语句中，表名后如果没有书写任何字段名称，则需要在values关键字之后，按照表格字段定义的顺序，依次添加每一个字段对应的值。
    (5).插入数据的类型必须和字段的类型一致。
    (6).插入的数据长度不能超过字段限制的长度。
  c.插入中文

insert into employee values(null,'朴乾','男','1099-01-01','2090-01-01','唱跳rap',12000.0,'善于rap');

    (1).插入中文产生乱码的原因:
     i.cmd窗口使用GBK字符集，mysql数据库使用utf-8字符集，两者不统一，造成编码解码字符集不一致，出现乱码。
     ii.修改cmd窗口的字符集很难，效果不好，所以考虑修改mysql数据库的字符集。
    (2).修改数据库字符集的方式：
     i.在mysql客户端中书写 set names gbk;这种方式为临时修改，只在当前窗口生效。
     ii.永久修改，修改mysql根目录中的my.ini文件57行，将utf8改为gbk。重启mysql服务即可。
 B.更新数据
  a.基本语句

update table_name set column_name=value;

#有条件的更新语句(过滤)
update table_name set column_name=value where column_name=value;

  b.练习
   (1).将所有的员工的薪水修改为5000元。

update employee set salary=5000;

   (2).将姓名为‘siri’的员工薪水修改为3000元。

update employee set salary=3000 where name='siri'；

   (3).将姓名为‘lili’的员工薪水修改为4000元，job改为ccc。

update employee set salary=4000,job='ccc' where name='lili';

   (4).将’tom’的薪水在原来的基础上增加1000元。

update employee set salary=salary+1000 where name='tom';

 C.删除数据
  a.基本语句

delete from table_name;//删除所有的数据但是表还存在

#有条件的删除(过滤)
delete from table_name where column_name=value;

  b.练习
   (1).删除表中的名称为“朴乾”的记录。

delete from employee where name="朴乾";

   (2).删除表中所有记录。

delete from employee;

   (3).使用truncate删除表中记录。

truncate employee;

  c.注意：
   (1).truncate是将整个表摧毁并重建,表中的数据被清空然后重建表结构。delete from是将表中的数据进行逐行删除。truncate比delete from删除数据的效率要高
   (2).truncate会摧毁表的结构，但是当面对多表时，这种删除方式可能会影响表与表之间的关系，所以多表关系中不推荐使用truncate。
 D.查询操作
  a.基本语句
   (1).第一种方式：

select * from table_name;
*代表所有的列名（字段名称）

   (2).第二种方式：

select id,name,gender,birthday,entry_date,job,salary,resume from employee;

  b.问题：这两种写法的返回数据一致,那么效率高低呢?
   直接书写比效率稍高一点。直接书写的过程数据库就是按照书写的列名来查询返回数据。
   *需要先判断表里有哪些数据,再根据列名来进行查询返回数据
   但是上述的内容影响的效率差别几乎可以忽略不计，影响查询数据效率的主要原因是索引。
  c.去重查询

select distinct * from table_name;

#根据指定字段名来做去重查询
select distinct column_name from table name;

  练习
   (1).查询表中所有学生的信息。

select * from exam;

   (2).查询表中所有学生的姓名和对应的英语成绩。

select name,english from exam;

   (3).过滤表中重复数据,distinct去重

select distinct english from exam;

  d.查询中可以使用的表达式
  练习
   (1).在所有学生分数上加10分特长分显示。

select name,math+10,english+10,chinese+10 from exam;

   (2).统计每个学生的总分。

select name,math+english+chinese from exam;

  e.sql支持运算
   (1).统计每个学生的总分并且右移一位。

select name,(math+english+chinese)>>1 from exam;

  f.别名 as
   (2).使用别名表示学生总分。

select name,math+english+chinese as sum from exam;

  g.where子句(sql语句的执行顺序是from(先找表)—>where(再进行过滤)—>select(最后查询))
  练习(使用where子句进行过滤查询)
   (1).查询姓名为xxx学生成绩

select name,math,chinese,english from exam where name='张飞;

   (2).查询英语成绩大于90分的同学

select name from exam where english> 90;

   (3).查询总分大于200分的所有同学

select name from exam where math+english+chinese >200;

select name,math+english+chinese as sum from exam where sum>200;//会出错
//sql语句的执行顺序是from(先找表)---where(再进行过滤)--select(最后查询)

   (4).查询英语分数在80-100之间的同学。

select name from exam where english bettween 80 and 90;
#或：
select name from exam where english>=80&&english<=100;

   (5).查询数学分数为75,76,85的同学。

select name,math from exam where math in(75,76,85);

   (6).查询所有姓张的学生成绩。

select name,math,chinese,english from exam where name like '张%';

   (7).查询数学分>70,语文分>80的同学。

select name from exam where math>70 and chinese>80;

   (8).查询数学分数不低于60分的同学

select name from exam where not (math<60);

   (9).查询数学缺考的同学

    先添加一条存在null的数据

insert into exam values(null,'刘备','100',null,'120');

select name from exam where math is null;

   (10).查询所有人的数学成绩,如果是null就用0代替

select name,ifnull(math,0) from exam;0

  h.order by子句
   在得到查询结果进行结果进行排序输出
   注意: order by关键字永远出现sql语句的最后面,执行顺序也是最后一个
   基本语句

select column_name... from table_name order by column_name asc|desc;

   练习
    (1).对语文成绩排序后输出。

select chinese from exam order by chinese;

    (2).对总分排序按从高到低的顺序输出

select chinese+ifnull(math,0)+english from exam order by chinese+idnull(math,0)+english desc;
#使用别名
select chinese+ifnull(math,0)+english as sum from exam order by sum desc;

    (3).对姓赵的学生成绩排序输出

select chinese+ifnull(math,0)+english from exam where name like '赵%' order by chinese+ifnull(math,0)+english;

    注意:
     order by不推荐使用。因为order by需要对于已经查询好了的数据来进行排序,这个排序比较漫长。
  i.聚集函数
   (1).聚集函数–count
    对于表中满足条件的行进行计数
    基本语句

select count(*) from table_name;
select count(column_name) from table_name;
select count(*) from table_name where (column_name)=value;

    练习
     i.统计一个班级共有多少学生。

select count(*) from exam;
select count(name) from exam;
#
select count(1) from exam;
count(1):对表中第一个字段进行计数操作
count(1)和count(*)谁的效率高？对于大量数据，效率几乎相当。数据比较少，count(1)效率高一些。

     ii.统计数学成绩大于90的学生有多少个。

select count(math) from exam where math>90;

     iii.统计总分大于250的人数有多少。

select count(math+english+chinese) from exam ifnul(math,0)+english+chinese > 250;

   (2).聚集函数–sum
    对于满足添加的行,将指定字段的全部数据进行求和
    基本语句

select sum(column_name) from table_name;

    注意：只能对数值字段进行求和
    练习
     i.统计一个班级数学总成绩。

select sum(column_name) from table_name;

     ii.统计一个班级语文、英语、数学各科的总成绩

select sum(chinese),sum(english),sum(math) from exam;

     iii.统计一个班级语文、英语、数学的成绩总和。

select sum(math+english+chinese) from exam;

     iiii.统计一个班级语文成绩平均分。

select sum(chinese)/count(chinese) from exam;

   (4).聚集函数–Avg
    对于满足条件的行,将指定字段全部的数据平均数
    基本语句

select avg(column_name) from table_name;

    练习
     i.求一个班级数学平均分。

select avg(ifnull(math,0)) from exam;

     ii.求一个班级总分平均分。

select avg(ifnull(math,0)+english+chinese) from exam;

   (5).聚集函数–max/min
    对于满足条件的行，将指定字段内的查找最大值/最小值
    基本语句

select max(column_name)|min(column_name) from exam;

    扩展：
     子查询
     在查询语句中，添加where子句，where子句的过滤条件使用另一个sql语句来完成
    练习
     i.求班级最高分和最低分(数值范围在统计中特别有用)

select max(ifnull(math,0)+english+chinese),min(ifnull(math,0)+english+chinese) from exam;

     ii.查询语文成绩最高的学生的姓名和语文成绩

select name,chinese from exam where chinese in (select max(chinese) from exam);

  j.分组操作
   根据指定字段名称来分组,字段值相同分在一组，在查询是同一组只能展示一条数据(默认是当前的第一条数据，剩余的数据只是没有展示，可以做正常操作)
   基本语句

select * from table_name group by collumn_ name... ;

   练习
    i.对订单表中商品归类后,显示每一类商品的总价

select product,sum(price) from orders group by product;

    ii.查询购买了几类商品,并且每类总价大于100的商品

select product,sum(price) from orders group by product having sum(price)>100;

三、数据库备份

1.备份数据
 cmd中：mysqlump -u 用户名 -p 数据库名称>路径信息
2.备份回来
 第一种：
  mysq中：mysql -u 用户名 -p 数据库名称<路径信息
 第二种：
  mysql中：source 路径信息

四、表间关系

1.一对一：
2.一对多：
3.多对多：
注意：虽然建立表间关系时需要新建字段,但是这个并不会被数据库来维护。需要通过外键约束来通知数据库来维护

五、外键约束

1.概念
 可以使用外键约束来通知数据库来维护表与表之间的关系以及数据的完整性和正确性
2.作用
 维护表间数据的完整性以及正确性

foreign key(orders id) references order(id)

3.案例
 A.新建员工表

create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');

create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int，
foreign key (dept_id ) references dept(id)//添加外键约束
);
insert into emp values (null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',4);

扩展
 外键不推荐使用。在高吞吐量情况下，应该优先保证数据入库,。如果大量使用外键有可能会造成数据无法正常入库，甚至造成数据丢失。

六、多表查询

1.多表查询操作

select * from emp,dept;

 上述查询称之为笛卡尔积查询
 笛卡尔积查询
  是将左边的表的数据数量(m)乘以右边表的数据数量(n)
   总数量=m*n;
 结果展示
  在笛卡尔积查询基础上，添加一个过滤条件

dept.id=emp.dept_id;
select * from dept, emp where dept.id=emp.dept_id;

2.内连接查询
 在笛卡尔积查询基础上,获取左边表有的数据且右边表也有的数据

select * from dept inner join emp on dept.id=emp.dept_id;

 结果展示

3.外连接查询
 A.左外连接查询
  在笛卡尔积查询基础上,获取左边表有的数据但是右边表没有的数据

select * from dept left join emp on dept.id=emp.dept_id;

 结果展示

 B.右外连接查询
  在笛卡尔积查询基础上,获取右边表有的数据但是左边表没有的数据

select * from dept right join emp on dept.id=emp.dept_id;

  结果展示

  使用别名

select * from dept d right join emp e on d.id=e.dept_id;

  结果展示

 C.全外连接查询
  在笛卡尔积查询基础上,获取左边表有的数据但是右边表没有的数据和获取左边表没有的数据但是右边表有的数据
  mysql没有全外连接查询的关键字full join
  union将两个查询结果联合在一起并且会进行去重

select * from dept left join emp on dept.id=emp.dept_id union select * from dept d right join emp e on d.id=e.dept_id;

  结果展示

SQl注入攻击

1.由于sql语句参数部分是由前台发送后台,后台sql语句是拼接而来的, 在拼接过程中如果出现一些数据库关键字就有可能导致sql语义的改变，从而执行其他操作，这种攻击叫做SQL注入攻击，使用PreparedStatement来避免SQL注入攻击
2.PreparedStatement (传输器)
 是具有预编译的传输器
 先将sql语句的主干部分发送到服务器，参数位置通过？预留,sql语句主干部分服务器会变成一段机器码。
 在发送参数时，会以文本的形式来发送参数。如果参数中包含数据库相关的关键字，也会被认为是一段文本内容。

批处理机制

1.在一次jdbc中可以发送多条sql语句
2.批处理实现
 A.Statement批处理

statement.addBatch(String);//向当前批处理中添加一条sql语句
statement.executeBatch();//执行批处理
statement.clearBatch();//清空批处理

 B.PreparedStatement批处理

PreparedStatement.addBatch(String sql);//向当前批处理中添加条sql语句PreparedStatement.executeBatch()://执行批处理
PreparedStatement.clearBatch (); //清空批处理