MySql教程

MySQL数据库学习笔记(一)

本文主要是介绍MySQL数据库学习笔记(一),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

数据库回顾

数据库概述

  • 什么是数据库:数据库就是存储数据的仓库;其本质是一个文件系统,将数据按照特定的格式进行存储。可以通过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类中有静态代码块,该静态代码块会再次注册驱动。
      在这里插入图片描述
  1. 获得连接
  • Connection conn=DriverManager.getConnection(url,username,password);
    在这里插入图片描述
  1. 编写SQL语句并获得语句执行者
  • 编写SQL语句:
    在这里插入图片描述
  • 通过Statement对象来执行SQL语句:
    • Statement stmt=conn.createStatement();
      在这里插入图片描述
  1. 执行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()来获取影响行数
    • 一般这种方法使用的比较少,使用前两种比较多。
  1. 处理结果
  • 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类型,那么就可以使用该方法。
  1. 释放资源
  • 与IO流一样,使用后的资源都需要关闭
  • 释放资源的原则:先得到的后关闭,后得到的先关闭。
  1. 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条记录。
    在这里插入图片描述
这篇关于MySQL数据库学习笔记(一)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!