数据库回顾

数据库概述

• 什么是数据库：数据库就是存储数据的仓库；其本质是一个文件系统，将数据按照特定的格式进行存储。可以通过SQL语句对数据库中的数据进行增删改查。
• 数据库和表：不解释。

MySQL的使用

• MySQL安装（Windows上安装，Linux上的安装略）
  • 下一步。。。
  • 启动/关闭MySQL服务命令
    • 启动：net start mysql
    • 关闭：net stop mysql
    • 登录mysql命令：先切换到MySQL安装目录的bin目录下，然后输入mysql -u root -p密码

SQL语句

• 什么是SQL语句？
  • 结构化查询语言。
  • 关系数据库的国际标准。
  • 各个数据库厂商都支持ISO的数据库标准：普通话。
  • 各个数据库厂商在标准的基础上做了自己的扩展：方言。

SQL分类

DDL

• 数据定义语言（Database Definition Language）
• 用于定义数据库对象
  • 数据库
  • 表
  • 字段
• 关键字
  • create
  • drop
  • alter

DML

• 数据操作语言（Database Manipulation Language）
• 用于对数据库中表记录进行更新、删除、插入
• 关键字
  • insert
  • delete
  • update

DQL

• 数据查询语言（Datebase Query Language）
• 用于对数据库中表的记录进行查询
• 关键字
  • select
  • from
  • where

DCL

• 数据控制语言（Database Control Language）
• 用于定义数据库的访问权限和安全级别
• 关键字
  • grant

数据库操作

创建数据库

• create database 数据库名;
• create database 数据库名 character set 编码;

查看数据库

• 查看数据库服务器中的所有数据库：show databases;
• 查看某个数据库定义的信息：show create database 数据库名;

删除数据库

• drop database 数据库名;

切换数据库

• use 数据库名;

查看正在使用的数据库

• 查看正在使用的数据库：select database();

表操作

创建表

• 单表约束
  • 主键约束
    • primary key
    • 要求被约束的字段非空且唯一
  • 唯一约束
    • unique
    • 要求被约束的字段唯一
  • 非空约束
    • not null
    • 要求被约束的字段非空
• 添加外键约束
  • constraint constraint_name foreign key(外键字段名) references 主表(外键字段名) on delete action(restrict | cascade | set null | no action) on update action(restrict | cascade | set null | no action)
  • 例子
    • 在创建索引时， 可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。 RESTRICT和NO ACTION相同， 是指限制在子表有关联记录的情况下， 父表不能更新； CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录； SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。 针对上面创建的两个表， 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表删除记录的时候， 如果子表有对应记录， 则不允许删除， 主表在更新记录的时候， 如果子表有对应记录， 则子表对应更新 。

  create table city_innodb(
   city_id int NOT NULL AUTO_INCREMENT,
   city_name varchar(50) NOT NULL,
   country_id int NOT NULL,
   primary key(city_id),
   key idx_fk_country_id(country_id),
   CONSTRAINT 'fk_city_country' FOREIGN KEY(country_id) 
   REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE 
   )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

查看表

• 查看当前数据库中所有的表：show tables;
• 查看表结构：desc 表名;

删除表

• drop table 表名;

修改表

• 添加字段：alter table 表名 add 字段名 类型(长度) 约束;
• 删除字段：alter table 表名 drop 字段名;
• 修改字段名：alter table 表名 change 字段名 新字段名 类型(长度) 约束;
  • 修改字段名，肯定要有一个旧字段名和一个新字段名。
• 修改字段的类型（长度）和约束：alter table 表名 modify 字段名 类型(长度) 约束;
• 修改表的字符集：alter table 表名 character set 编码;
• 修改表的名字：rename table 表名 to 新表名;
• 注意：这个没有alter table，直接就rename就可以了。

插入表记录

• 插入语句
  • insert into 表名(字段1,字段2,......,字段n) values(值1,值2,......,值n);
  • insert into 表名 values(值1,值2,......,值n);
    • 这种写法values里面的值要与创建这张表时的字段顺序一一对应，并且所有的字段都要插入值。
  • 解决中文乱码问题
    • 方式一：修改MySQL中的my.ini文件，然后重启服务器（不推荐使用该方法）；
    • 方式二：在dos窗口中写入set names gbk; 仅对当前窗口有效；
• 注意点
  • 字段名与后面的值要一一对应；
  • 值如果是字符串或者日期需要加引号（一般是单引号）；

更新表记录

• 语句
  • update 表名 set 字段名=值;
    • 这是无条件更新，就是该表中所有的记录都会更新。
  • update 表名 set 字段名=值 where 条件;
• 注意点
  • 值如果是字符串或者日期需要加单引号。

删除表记录

• 语句
  • delete from 表名;
    • 这是无条件删除，就是会把该表中的所有记录删除
  • delete from 表名 where 条件;
• delete from 表名和truncate table 表名 删除记录的区别？
  • 删除方式
  1. delete是一条一条记录进行删除，不清空auto_increment的记录数。
  2. truncate删除时，把整张表摧毁，然后新建一张一模一样的表，并将auto_increment的记录是归零，从新开始计数。
  • 事务方面
  1. 如果开启了一个事务，那么使用delete删除的记录是可以通过回滚恢复的。
  2. 而使用truncate删除的记录是不能通过回滚恢复的。
  • 注意
    • truncate table 表名;这种删除方式是不能加where条件的，只能全部删除。

查询表记录

• 简单查询
  • 语句：select distinct(字段名) | * | 字段名1,字段名2,......,字段名n from 表名 [where 条件]
  • 查询所有商品
    • select * from 表名;
  • 查询商品名和商品价格
    • select pname,price from product;
  • 别名查询
    • 表别名
      • select * from product [as] p;
    • 列别名
      • select pname [as] p1,price [as] p2 from product;
    • 注意点：别名中的as是可以省略的，一般都省略。
  • 去掉重复值查询
    • select distinct(字段名) from 表名;
  • 运算查询（查询结果是表达式）
    • 例子：将所有商品价格加10元显示
    • select price+10 from product;
• 条件查询
  • 运算符
    • 比较运算符
      • 大于：>
      • 大于等于：>=
      • 小于：<
      • 小于等于：<=
      • 等于：=
      • 不等于：<>
      • 在某一区间的值（含头含尾）：between a and b
      • 在in列表中的值：in (23,34,45)
      • 模糊查询：like
        • 占位符
          • _：表示一个字符
          • %：表示任意个字符（可以是0个字符）
      • 判断是否为空：is null
    • 逻辑运算符
      • and：多个条件同时成立
      • or：多个条件任意一个成立
      • not：条件不成立。
        • select * from product where not (price>10)：表示查询price>10这一条件不成立的商品信息。
  • 例子

排序

• select * from 表名 [where 条件] order by 字段名 asc | desc
• asc是递增排序；desc是降序排列。
• 注意点
  • order必须写在where条件语句后面
  • 如果不写asc或desc，那么默认是按照升序排列

聚合函数

• 常用的聚合函数
  • sum(字段名)：求和
  • avg(字段名)：求平均数
  • count(字段名)：求个数。count()里面是可以写字段名，也可以写*
• 注意点：聚合函数不统计null值。

分组

• select * from 表名 where 查询条件 group by 字段名 having 分组条件;
• 分组条件一定要写在having后面，而不能通过where来写，因为where后面的是查询条件。
• 例子
  • 根据cid字段分组，分组后统计商品的个数：select cid,count(*) from product group by cid;
  • 根据cid分组，分组统计每组商品的平均价格，并且平均价格大于20000元：select cid,avg(price) from product group by cid having avg(price) > 20000;
    • 注意：这里的“并且平均价格大于20000元”是分组条件，不是查询条件。

查询语句总结

• select * from 表名 where 查询条件 group by 字段名 having 分组条件 order by 字段名 asc | desc;
• 排序order by一定要写在最后。
• 分组和排序都是写在where后面（分组，排序 排序在最后）

JDBC

JDBC介绍

• 全称：Java DataBase Connectivity（Java数据库连接）
• JDBC是一种用于执行SQL语句的Java API。
• JDBC可以为多种关系型数据库提供统一的访问入口。
• JDBC由一组Java工具类和接口组成。

JDBC原理

• SUN公司提供访问数据库规范成为JDBC，而生产厂商提供规范的实现类成为驱动。

JDBC开发步骤

1. 注册驱动

• Class.forName("com.mysql.jdbc.Driver");：这种是推荐写法
• DriverManager.registerDriver(new com.mysql.jdbc.Driver())：这种写法不推荐，因为这种写法有缺点
  • 01.硬编码，如果换了别的数据库（不再是MySQL），那么要创建别的Driver，所以要重写括号里面的Driver了，而使用Class.forName()方法括号里面的参数一般都只从配置文件里面读取的，只需要修改配置文件即可。
  • 02.这种方法会注册两次，因为com.mysql.jdbc.Driver类中有静态代码块，该静态代码块会再次注册驱动。

2. 获得连接

• Connection conn=DriverManager.getConnection(url,username,password);

3. 编写SQL语句并获得语句执行者

• 编写SQL语句：
• 通过Statement对象来执行SQL语句：
  • Statement stmt=conn.createStatement();

4. 执行SQL语句

• int executeUpdate(String sql);：执行insert、update、delete操作，返回影响的行数
• ResultSet executeQuery(String sql);：执行查询操作，返回查询到的结果集
• boolean execute(String sql);：
  • 执行查询操作返回true：如果返回true，需要用ResultSet getResultSet()来获取查询结果
  • 执行insert、update、delete操作返回false：如果返回false，需要用int getUpdateCount()来获取影响行数
  • 一般这种方法使用的比较少，使用前两种比较多。

5. 处理结果

• ResultSet解释：ResultSet其实就是一张二维的表格，它里面有一个“光标”，初始时，“光标”在第一行的上面，可以使用next()方法来将“光标”移到下一行；第一次调用next()方法时，“光标”会移动到第一行。可以通过getXXX()方法来获取当前行的数据。
• boolean next()：该方法返回一个boolean类型的数据，如果遍历到了行末尾，那么就会返回false，否则就会返回true。
• 获取当前行的数据
  • getXXX(参数)方法里面的“参数”有两种写法
    • 字段名（columnName）：resultSet.getInt("cid");（获取字段名为cid的数据）
    • 字段所处的列（columnIndex）：resultSet.getInt(1);（获取当前行第一列的数据，columnIndex从1开始）
    • 常用的getXXX()方法
      • Object getObject(String columnName | int columnIndex)：如果不知道获取的数据是什么类型，就是用getObject()方法，返回一个Object类型的对象。
      • int getInt(String columnName | int columnIndex)：如果知道要获取的数据是int类型，那么就可以使用该方法。一定要能确保获取到的数据是int类型，下同。
      • String getString(String columnName | int columnIndex)：如果知道获取的数据时String类型，那么就可以使用该方法。
      • double getDouble(String columnName | int columnIndex)：如果知道获取的数据时double类型，那么就可以使用该方法。

6. 释放资源

• 与IO流一样，使用后的资源都需要关闭
• 释放资源的原则：先得到的后关闭，后得到的先关闭。

7. jdbc数据库查询数据完整写法

@Test 
public void test1(){ 
	Connection conn=null; 
	Statement stmt=null; 
	ResultSet resultSet=null; 
	try {
		//注册驱动 
		Class.forName("com.mysql.jdbc.Driver"); 
		//获得连接 
		conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/w eb08","root","pgmx0835"); 
		//编写 SQL 语句并执行 
		String sql="select * from category"; 
		stmt=conn.createStatement(); 
		resultSet = stmt.executeQuery(sql); 
		//处理执行结果 
		while(resultSet.next()){ 
			//获取查询到的数据 
			Integer cid=resultSet.getInt(1); 
			String cname=resultSet.getString(2); 
			System.out.println("cid="+cid+",cname="+cname); 
			} 
		} 
		catch (ClassNotFoundException | SQLException e) { 
			e.printStackTrace(); 
			}finally{ 
				try {
				//释放资源 
				//注意：释放资源要按照创建顺序的逆序来释放 
				if (resultSet != null) { 
					resultSet.close(); 
					}
					if(stmt!=null){ 
					stmt.close(); 
					}
					if(conn!=null){ 
					conn.close(); 
					} 
				}catch(SQLException e){ 
					e.printStackTrace(); 
						} 
					} 
				}

SQL注入问题

• 登录功能实现
  • 实现原理：根据传入的name和password去数据库中查询，查询条件为同时满足名字为name和密码为password，如果查询到了，那么登录成功，否则登录失败。
  • 实现方式：
    • 方式一
      • 使用字符串拼接，把name和password拼接进SQL语句中。
      • 实现代码：
      • 这种方式带来的问题（缺点）:当传入的name中有or时，可能也会登录成功；即根本没有用户的名字叫传入的name参数.
      • 解决办法：使用预处理PreparedStatement（见方式二）
    • 方式二
      • 实现原理：使用预处理PreparedStatement，在编写SQL语句时，使用占位符，然后通过给参数设置值，从而解决上述问题。
      • 实现代码
      • PreparedStatement总结
        • 01.创建PreparedStatement：PreparedStatement prepareStatement(String sql)，创建时，要把SQL语句传入，因为要预处理。
        • 02.编写SQL语句：使用预处理后，编写SQL语句可以使用占位符。
        • 03.设置参数：因为使用了占位符，所以要设置参数，setXXX(int parameterIndex,Object value)
        • 04.执行SQL语句：执行SQL语句的方法与Statement的一样，只不过此处执行时，不需要把SQL语句传入。

limit[m],n

• 参数解释
  • m：m表示从第几条记录开始查询，注意：该索引是从0开始的。比如：m为0，那么就表示从第1条记录开始查询。m参数是可选择的，也就是可写可不写。如果m不写，那么就默认从第一条记录开始查询。
  • n：n表示查询几条记录。例如，n为3，就表示此次查询3条记录。
• 例子：select * from 表名 limit 2,5;：表示从第3条记录开始查询，一共查询5条记录。也就是查询第3,4,5,6,7条记录。