适合入门的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);