适合入门的MySQL基础知识

初级程序员，如Structured Query Language：结构化查询语言 定义了操作所有关系型数据库的规则 ，本图适合入门的MySQL基础知识，了解这些就可以使用sql，去搬砖啦。

MySQL

• SQL分类
  • DDL (Data Definition Language)数据定义语言 用来定义数据库对象：数据库，表，列等
    • 操作数据库
      • <span style="font-size: inherit;">C(Create):创建</span>
        • 创建数据库： * create database 数据库名称
        • 创建数据库，判断不存在，再创建： * create database if not exists 数据库名称
        • 创建数据库，并指定字符集 * create database 数据库名称 character set 字符集名
        • 创建数据库，判断是否存在，并制定字符集为gbk * create database if not exists 数据库名称 character set gbk;
      • R(Retrieve)：查询
        • 查询所有数据库的名称: * show databases
        • 查询某个数据库的字符集:查询某个数据库的创建语句 * show create database 数据库名称
      • U(Update):修改
        • 修改数据库的字符集 * alter database 数据库名称 character set 字符集名称
      • D(Delete):删除
        • 删除数据库 * drop database 数据库名称
        • 判断数据库存在，存在再删除 * drop database if exists 数据库名称
      • 使用数据库
        • 查询当前正在使用的数据库名称 * select database()
        • 使用数据库 * use 数据库名称
    • 操作表
      • C(Create):创建
        • 数据库数据类型
          • 数据库类型： 1. int：整数类型 * age int, 2. double:小数类型 * score double(5,2) 3. date:日期，只包含年月日，yyyy-MM-dd 4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss 5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss * 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
        • 1、创建表 create table 表名( 列名1 数据类型1, 列名2 数据类型2, .... 列名n 数据类型n ) 注意：最后一列，不需要加逗号
        • 2、复制表 create table 表名 like 被复制的表名
      • R(Retrieve)：查询
        • 查询某个数据库中所有的表名称 * show tables
        • 查询表结构 * desc 表名
      • U(Update):修改
        • 1、修改表名 alter table 表名 rename to 新的表名
        • 2、修改表的字符集 alter table 表名 character set 字符集名称
        • 3、添加一列 alter table 表名 add 列名 数据类型
        • 4、修改列名称 类型 alter table 表名 change 列名 新列名 新数据类型; alter table 表名 modify 列名 新数据类型
        • 5、删除列 alter table 表名 drop 列名
      • D(Delete):删除
        • drop table 表名
        • drop table if exists 表名
  • DML (Data Manipulation Language)数据操作语言用来对数据库中表的数据进行增删改
    • 添加数据
      • insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n)
      • 注意： 1. 列名和值要一一对应。 2. 如果表名后，不定义列名，则默认给所有列添加值 insert into 表名 values(值1,值2,...值n); 3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
    • 删除数据
      • delete from 表名 [where 条件]
      • 注意： 1. 如果不加条件，则删除表中所有记录。 2. 如果要删除所有记录 1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作 2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表
    • 修改数据
      • update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件]
      • 注意： 1. 如果不加任何条件，则会将表中所有记录全部修改
  • DQL(Data Query Language)数据查询语言 用来查询数据库中表的记录(数据)
    • 基础查询
      • 多字段查询
        • select 字段名1,字段名2... from 表名
      • 去除重复
        • select distinct 字段名 from 表名
      • 计算列
        • 可以做数值类型的四则运算 null参与的运算结果都为null
        • ifnull(表达式1,表达式2) 如果表达式1为null 则将表达式1替换为表达式2
      • 起别名
        • 字段 as 别名
    • 条件查询
      • [where 条件]
      • 运算符
        • like 模糊查询
          • 占位符： _:单个任意字符 %：多个任意字符
          • -- 查询姓马的有哪些 SELECT * FROM student WHERE NAME LIKE '马%'
          • -- 查询姓名第二个字是化的人 SELECT * FROM student WHERE NAME LIKE "_化%"
          • -- 查询姓名是3个字的人 SELECT * FROM student WHERE NAME LIKE '___' （三个占位符）
          • -- 查询姓名中包含德的人 SELECT * FROM student WHERE NAME LIKE '%德%'
        • <>、！= 都为不等于
        • between ... and ... 在两者之间
          • SELECT * FROM student WHERE age BETWEEN 20 AND 30
        • in(a,b,c... ) 查询含a b c 的字段
          • SELECT * FROM student WHERE age IN (22,18,25)
        • > 、< 、<= 、>= 、= and 或 && or 或 || not 或 !
        • is null / is not null 注意null值要用is判断 不能用=
          • SELECT * FROM student WHERE english IS NOT NULL
    • 排序查询
      • select * from 表名 order by 列名 默认升序
      • 升序：select * from 表名 order by 列名 asc
      • 降序：select * from 表名 order by 列名 desc
      • select * from 表名 order by 列名1 asc,order by 列名2 asc 如果列名1相等，再排列名2
    • 聚合函数
      • 将一列数据作为一个整体，进行纵向的计算
        • 计算个数：select count(列名) from 表名
        • select max(列名) from 表名
        • select min(列名) from 表名
        • select sum(列名) from 表名
        • select avg(列名) from 表名
    • 分组查询
      • group by 分组字段 分组之后查询的字段：分组字段、聚合函数
        • select sex,avg(math),count(id) from student3 group by sex
        • select sex,avg(math),count(id) from student3 [where 条件] group by sex
        • select sex,avg(math),count(id) from student3 [where 条件] group by sex [having 条件]
      • where和having的区别： where在分组之前进行限定，如果不满足条件泽不参与分组， 而having在分组之后进行限定，不满足结果则不会被查询出来 <li>where 后不可以跟聚合函数，having可以进行聚合函数的判断 例： SELECT sex,AVG(math),COUNT(id) AS 人数 FROM student3 WHERE math >80 GROUP BY sex HAVING 人数>1
    • 分页查询
      • 注意： limit 只有MySQL才能用 其他数据库有特有的分页方式
      • select * from 表名 limit 开始的索引, 每页查询的条数
      • 公式：开始的索引=（当前的页码-1）*每页显示的条数
        • select * from student3 limit 0,3-->第一页 select * from student3 limit 3,3-->第二页 select * from student3 limit 6,3-->第三页
  • DCL(Data Control Language)数据控制语言(了解) 用来定义数据库的访问权限和安全级别，及创建用户
    • 管理用户
      • 添加用户
        • CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'
      • 删除用户
        • DROP USER '用户名'@'主机名'
      • 修改用户密码
        • UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'
        • mysql中忘记了root用户的密码？ 1. cmd -- > net stop mysql 停止mysql服务 * 需要管理员运行该cmd 2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables 3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功 4. use mysql; 5. update user set password = password('你的新密码') where user = 'root'; 6. 关闭两个窗口 7. 打开任务管理器，手动结束mysqld.exe 的进程 8. 启动mysql服务 9. 使用新密码登录。
      • 查询用户
        • SELECT * FROM USER
        • 通配符： % 表示可以在任意主机使用用户登录数据库
    • 权限管理
      • 查询权限
        • SHOW GRANTS FOR '用户名'@'主机名'
      • 授予权限
        • grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'
      • 撤销权限
        • revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
• 事务
  • 操作
    • 1.开启事务
      • start transaction
    • 2. 回滚
      • rollback
    • 3. 提交
      • commit
      • 事务提交的两种方式
        • 自动提交
          • * mysql就是自动提交的 * 一条DML(增删改)语句会自动提交一次事务
        • 手动提交
          • * Oracle 数据库默认是手动提交事务 * 需要先开启事务，再提交
        • 修改事务的默认提交方式
          • 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
          • 修改默认提交方式： set @@autocommit = 0
  • 四大特征：笔试题中经常见到
    • 1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
    • 2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
    • 3. 隔离性：多个事务之间。相互独立
    • 4. 一致性：事务操作前后，数据总量不变
  • 事务隔离级别（了解）
    • 1. read uncommitted：读未提交 * 产生的问题：脏读、不可重复读、幻读
    • 2. read committed：读已提交 （Oracle） * 产生的问题：不可重复读、幻读
    • 3. repeatable read：可重复读 （MySQL默认） * 产生的问题：幻读
    • 4. serializable：串行化 * 可以解决所有的问题
    • * 数据库查询隔离级别： * select @@tx_isolation; * 数据库设置隔离级别： * set global transaction isolation level 级别字符串;
• JDBC
  • 实质
  • 使用步骤
    • 1、导入驱动jar包
      • mysql-connector-java-5.1.37-bin.jar 1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下 2.右键-->Add As Library
    • 2、注册驱动
      • Class.forName("com.mysql.jdbc.Driver")
    • 3、获取数据库连接对象
      • Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root")
    • 4、定义sql
    • 5、获取执行sql语句的对象Statement
      • Statement stmt = conn.createStatement()
    • 6、执行sql（可以接收返回结果并处理）
      • stmt.executeUpdate(sql) stmt.executeQuery(sql)
    • 7、释放资源
      • stmt.close() conn.close()
  • 各个对象
    • DriverManager类：驱动管理对象
      • 注册驱动：告诉程序该使用哪一个数据库驱动jar
      • 获取数据库连接
        • 方法：static Connection getConnection(String url, String user, String password) * 参数： * url：指定连接的路径 * 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称 * 例子：jdbc:mysql://localhost:3306/db3 * 细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称 * user：用户名 * password：密码
    • Connection接口：数据库连接对象
      • 获取执行sql 的对象
        • Statement createStatement()
        • PreparedStatement prepareStatement(String sql)
      • 管理事务
        • 开启事务：setAutoCommit(boolean autoCommit) ： 调用该方法设置参数为false，即开启事务
        • 提交事务：commit()
        • 回滚事务：rollback()
    • Statement接口：执行sql的对象
      • 执行sql
        • boolean execute(String sql) ： 可以执行任意的sql 了解
        • int executeUpdate(String sql) ： 执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句 返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功， 反之，则失败。
        • ResultSet executeQuery(String sql) ： 执行DQL（select)语句
    • PreparedStatement接口：执行sql的对象
      • 解决sql注入问题，效率更高
      • 预编译的SQL：参数使用?作为占位符
      • 使用步骤
        • 1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar 2. 注册驱动 3. 获取数据库连接对象 Connection 4. 定义sql * 注意：sql的参数使用？作为占位符。 如：select * from user where username = ? and password = ?; 5. 获取执行sql语句的对象 PreparedStatement Connection.prepareStatement(String sql) 6. 给？赋值： * 方法： setXxx(参数1,参数2) * 参数1：？的位置编号 从1 开始 * 参数2：？的值 7. 执行sql，接受返回结果，不需要传递sql语句 8. 处理结果 9. 释放资源
    • ResultSet接口：结果集对象,封装查询结果
      • boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)， 如果是，则返回false，如果不是则返回true，常搭配while循环进行读取操作
      • getXxx(参数):获取数据 * Xxx：代表数据类型 如： int getInt() , String getString() * 参数： 1. int：代表列的编号,从1开始 如： getString(1) 2. String：代表列名称。 如： getDouble("balance")
  • 抽取JDBC工具类：JDBCUtils
    • 封装JDBC的使用步骤
      • 1、抽取注册驱动
      • 2、抽取一个方法获取连接对象
      • 3、抽取一个方法释放资源
  • JDBC控制事务
    • 1. 开启事务
      • setAutoCommit(boolean autoCommit) ： 调用该方法设置参数为false，即开启事务
    • 2. 提交事务
      • commit() * 当所有sql都执行完提交事务
    • 3. 回滚事务
      • rollback() * 在catch中回滚事务
• 数据库连接池
  • 概念及优点
    • 概念： 其实就是一个容器(集合)，存放数据库连接的容器。 当系统初始化好后，容器被创建，容器中会申请一些连接对象， 当用户来访问数据库时，从容器中获取连接对象， 用户访问完之后，会将连接对象归还给容器 好处： 1. 节约资源 2. 用户访问高效
  • 常见数据库连接池技术
    • C3P0
      • 步骤
        • 1、导入C3P0jar包和数据库驱动jar包
          • c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar
        • 2、定义配置文件
          • 名称： c3p0.properties 或者 c3p0-config.xml 路径：直接将文件放在src目录下即可。
        • 3、创建数据库连接池对象ComboPooledDataSource
          • DataSource ds = new ComboPooledDataSource();
        • 4、获取连接getConnection()
          • Connection conn = ds.getConnection();
    • Druid
      • 步骤
        • 1、导入druid jar包和数据库驱动jar包
          • druid-1.0.9.jar
        • 2、定义配置文件
          • 是properties形式的 可以叫任意名称，可以放在任意目录下
        • 3、加载配置文件
          • Properties pro = new Properties(); InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"); pro.load(is);
        • 4、获取数据库连接池对象，通过工厂来获取 DruidDataSourceFactory
          • DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        • 5、获取连接getConnection()
          • Connection conn = ds.getConnection();
    • 定义JDBCUtils工具类 (使用数据库连接池技术)
• SQL概念
  • 概念 Structured Query Language：结构化查询语言 定义了操作所有关系型数据库的规则
  • 通用语法
• 约束
  • 非空约束：not null
    • 创建表时添加约束
      • CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 )
    • 删除
      • ALTER TABLE 表名 MODIFY 列名 数据类型
    • 创建表之后添加非空约束
      • ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL
  • 唯一约束：unique
    • 注意mysql中，唯一约束限定的列的值可以有多个null
    • 创建时添加约束
      • CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束 )
    • 删除
      • ALTER TABLE 表名 DROP INDEX 列名
    • 创建表之后添加唯一约束
      • ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE
  • 主键约束：primary key 非空且唯一
    • 注意： 1. 含义：非空且唯一 2. 一张表只能有一个字段为主键 3. 主键就是表中记录的唯一标识
    • 创建时添加约束
      • create table stu( id int primary key,-- 给id添加主键约束 name varchar(20) )
    • 删除
      • ALTER TABLE 表名 DROP PRIMARY KEY (列名可以省略，因为一张表中只有一个主键)
    • 创建表之后添加主键约束
      • ALTER TABLE stu MODIFY id INT PRIMARY KEY
    • 自动增长
      • 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长 (一般用于主键，也可以用于其他数值类型列)
      • 创建表时，添加主键约束 并且完成主键自增长
        • create table stu( id int primary key auto_increment,-- 给id添加主键约束 name varchar(20) )
      • 删除自动增长
        • ALTER TABLE 表名 MODIFY 列名 数据类型
      • 添加自动增长
        • ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT
    • 联合主键 primary key(aid,uid)
      • 在创建多对多中使用，中间表的每一行不能重复
  • 外键约束：foreign key
    • 创建表时添加外键
      • create table 表名( .... 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) )
      • constraint 外键名称 可以省略 让系统自动分配一个名称
    • 删除外键
      • ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
    • 创建表后添加外键
      • ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称)
    • 级联操作
      • 添加级联操作
        • ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE
      • 分类
        • 级联更新：ON UPDATE CASCADE
        • 级联删除：ON DELETE CASCADE
• 范式（没整理）
• 多表查询
  • 查询步骤
    • <ol><li> 从哪些表中查询数据 <li> 条件是什么 <li> 查询哪些字段 </ol>
  • 内连接查询
    • 隐式内连接
      • 笛卡尔积
        • 有两个集合A,B .取这两个集合的所有组成情况。 select * from A ,B;
      • 使用where条件
        • -- 查询员工表的名称，性别。部门表的名称 SELECT t1.name, -- 员工表的姓名 t1.gender,-- 员工表的性别 t2.name -- 部门表的名称 FROM emp t1, dept t2 WHERE t1.dept_id = t2.id
    • 显示内连接
      • select 字段列表 from 表名1 [inner] join 表名2 on 条件
      • inner 可以省略 SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id
  • 外连接查询
    • 左外连接
      • 查询的是左表所有数据以及其交集部分
        • select 字段列表 from 表1 left [outer] join 表2 on 条件
        • 例子： -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称 SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id
    • 右外连接
      • 查询的是右表所有数据以及其交集部分
        • select 字段列表 from 表1 right [outer] join 表2 on 条件
        • 例子： SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id
  • 子查询
    • 概念：查询中嵌套查询，称嵌套查询为子查询
    • 分类
      • 子查询的结果是单行单列的
        • 查询可以作为条件，使用运算符去判断。 运算符： > >= < <= = -- 查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp)
      • 子查询的结果是多行单列的
        • 子查询可以作为条件，使用运算符in来判断 -- 查询'财务部'和'市场部'所有的员工信息 SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部' SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2 -- 子查询 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部')
      • 子查询的结果是多行多列的
        • 子查询可以作为一张虚拟表参与查询 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 -- 子查询 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id
        • 或者使用普通内连接： SELECT * FROM emp t1,dept t2 WHERE t1.dept_id= t2.id AND t1.join_date > '2011-11-11'
• Spring JDBC
  • Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
  • 使用步骤
    • 1、导入jar包
    • 2、创建JdbcTemplate对象
      • 依赖于数据源DataSource JdbcTemplate template = new JdbcTemplate(ds)
    • 3、调用JdbcTemplate的方法来完成CRUD的操作
      • update():执行DML语句。增、删、改语句
      • queryForMap():查询结果将结果集封装为map集合，
        • 将列名作为key，将值作为value 将这条记录封装为一个map集合 * 注意：这个方法查询的结果集长度只能是1
        • String sql = "select * from emp where id = ? or id = ?"; Map<String, Object> map = template.queryForMap(sql, 1001,1002); System.out.println(map);
      • queryForList():查询结果将结果集封装为list集合
        • String sql = "select * from emp"; List<Map<String, Object>> list = template.queryForList(sql); for (Map<String, Object> stringObjectMap : list) { System.out.println(stringObjectMap); } }
      • query():查询结果，将结果封装为JavaBean对象
        • query的参数：RowMapper * 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装 * new BeanPropertyRowMapper<类型>(类型.class)
        • String sql = "select * from emp"; List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class)); for (Emp emp : list) { System.out.println(emp); } }
        • 用户信息查询
      • queryForObject：查询结果，将结果封装为对象
        • 一般用于聚合函数的查询 用户登陆验证
        • String sql = "select count(id) from emp"; Long total = template.queryForObject(sql, Long.class); System.out.println(total);