Mysql，sql语句，索引，联合查询，函数，事务，

	关系型数据库	非关系型数据库
使用SQL	是	不强制要求，一般不基于SQL实现
事务支持	支持	不支持
复杂操作	支持	不支持
海量读写操作	效率低	效率高
基本结构基于表和列，结构固定	灵活性比较高	使用场景业务方面的OLTP系统用于数据的缓存、或基于统计分析的OLAP系统

数据库操作

显示当前的数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name（数据库名字） [create_specification [,create_specification] ...]

create_specification:

[DEFAULT] CHARACTER SET charset_name //（指定数据库采用的字符集：比如字符集）

[DEFAULT] COLLATE collation_name//（指定数据库字符集的校验规则）

其实麻，记住这个就好了，因为：

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则

是：utf8_ general_ ci

CREATE DATABASE 数据库名；

如果没有这个数据库，就创建这个数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

如果系统没有数据库，则创建一个使用 utf8mb4 字符集的数据库，如果有则

不创建

CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET utf8mb4；

使用数据库

use 数据库名 ;

删除数据库

数据库删除以后，内部看不到对应的数据库，里边的表和数据全部被删除（危险，不，应该是极具危险）在怎么操作也不该删除。留着或许没用，删了绝对会死

DROP DATABASE [IF EXISTS] 数据库名;

数据表操作

数值类型

都得记

字符串类型

时间类型

常用类型

INT ：整型

DECIMAL(M, D) ：浮点数类型

VARCHAR(SIZE) ：字符串类型

TIMESTAMP ：日期类型

使用想要操作的数据库

use 数据库名字;

查看表结构

desc 表名 ;

创建表

CREATE TABLE table_name (

        字段名数据属性,

        字段名数据属性,

        字段名数据属性

);

字段名：尽量加上 ` 字段名 ` 符号

删除表（也据危险）

语法

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

删除表

drop table 表名;

如果表存在，就删除表

drop table if exists 表名;

例子

create table if not exists goods

(

goods_id int comment ' 商品编号 ' ,

goods_name varchar ( 32 ) comment ' 商品名称 ' ,

unitprice int comment ' 单价，单位分 ' ,

category varchar ( 12 ) comment ' 商品分类 ' ,

provider varchar ( 64 ) comment ' 供应商名称 '

);

comment ：作用是对字段进行重命名，但是表结构字段依旧是原来的字段。

CREATE TABLE student (

          id INT ,

          sn INT comment ' 学号 ' ,

          name VARCHAR ( 20 ) comment ' 姓名 ' ,

          qq_mail VARCHAR ( 20 ) comment 'QQ 邮箱 '

);

插入数据

单行插入

INSERT INTO student VALUES ( 2 , 1000 , '小三 ' , NULL );

多行插入

INSERT INTO student (id, sn, name) VALUES

( 102 , 2004 , '李四 ' ),

( 103 , 2006 , '张珊 ' );

数量必须和指定列数量及顺序一致

查询数据

SELECT [ DISTINCT ] {* | {column [, column ] ...} [ FROMtable_name ] [ WHERE ...]

[ ORDERBYcolumn [ ASC | DESC ], ...] LIMIT ...

全列查询

SELECT * FROM student ;

1. 查询的列越多，意味着需要传输的数据量越大；

2. 可能会影响到索引的使用。

指定查询

SELECT id ，sn ，name FROM student ;

CREATETABLE exam_result (

        id INT ,

        name VARCHAR ( 20 ),

        chinese DECIMAL ( 3 , 1 ),

        math DECIMAL ( 3 , 1 ),

        english DECIMAL ( 3 , 1 )

);

INSERTINTO exam_result (id,name, chinese, math, english) VALUES

( 1 , '李三 ' , 67 , 98 , 56 ),

( 2 , '李四 ' , 87.5 , 78 , 77 ),

( 3 , '李五 ' , 88 , 98.5 , 90 ),

( 4 , '李六 ' , 82 , 84 , 67 )；

表达式不包含字段

SELECT id, name, 10 FROM exam_result;

表达式包含一个字段

SELECT id, name, english + 10 FROM exam_result;

表达式包含多个字段

SELECT id, name, chinese + math + english FROM exam_result;

设置查询别名

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称

SELECT column [ AS ] alias_name [...] FROM 表名 ;

SELECT id, name, chinese + math + english 总分 FROM exam_result;

去重： DISTINCT

使用 DISTINCT 关键字对某列数据进行去重

SELECT DISTINCT math FROM exam_result;

排序： ORDER BY

ASC 为升序（从小到大）

DESC 为降序（从大到小）

默认为 ASC

注意：

1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

SELECT ... FROMtable_name [ WHERE ...] ORDER BY column [ ASC | DESC ], [...];

//升序

SELECT name, qq_mail FROM student ORDERBY qq_mail;

//降序

SELECT name, qq_mail FROM student ORDERBY qq_mail DESC ;

使用表达式及别名排序

ELECT name, chinese + english + math total FROM exam_result ORDER BY total DESC ;

可以对多个字段进行排序，排序优先级随书写顺序

查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

SELECT name, math, english, chinese FROM exam_result ORDERBY math DESC , english, chinese;

条件查询（where）

比较运算符

逻辑运算符

注意：

1. WHERE 条件可以使用表达式，但不能使用别名。

2. AND 的优先级高于 OR ，在同时使用时，需要使用小括号 () 包裹优先执行的部分

SELECT name, english FROM exam_result WHERE english < 60 ;

查询语文成绩大于80分，且英语成绩大于80分的同学

SELECT * FROM exam_result WHERE chinese > 80 and english > 80 ;

查询语文成绩大于80分，或英语成绩大于80分的同学

SELECT * FROM exam_result WHERE chinese > 80 or english > 80 ;

范围查询：

BETWEEN ... AND ...

SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90 ;

//等同

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese

<= 90 ;

SELECT name, math FROM exam_result WHERE math IN ( 58 , 59 , 98 , 99 );

SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math

= 98 OR math = 99 ;

模糊查询： LIKE

% 匹配任意多个（包括 0 个）字符

SELECT name FROM exam_result WHERE name LIKE ' 孙 %' ;

NULL 的查询： IS [NOT] NULL

查询 qq_mail 已知的同学姓名

SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL ;

分页查询：LIMIT

从 0 开始，筛选 n 条结果

SELECT ... FROM 表 [ WHERE ...] [ ORDER BY ...] LIMIT n;

从 s 开始，筛选 n 条结果

SELECT ... FROM 表 [ WHERE ...] [ ORDER BY ...] LIMIT s, n;

从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

SELECT ... FROM 表 [ WHERE ...] [ ORDER BY ...] LIMIT n OFFSET s;

修改数据（Update）

UPDATE table_name SET column = expr [, column = expr ...]

[ WHERE ...] [ ORDER BY ...] [ LIMIT ...]

将李四同学的数学成绩变更为 80 分

UPDATE exam_result SET math = 80 WHERE name = '李四 ' ;

将李三同学的数学成绩变更为 60 分，语文成绩变更为 70 分

UPDATE exam_result SET math = 60 , chinese = 70 WHERE name = '李三 ' ;

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3 ;

将所有同学的语文成绩更新为原来的 2 倍

UPDATE exam_result SET chinese = chinese * 2 ;

删除数据（非必要不删除）

DELETEFROM table_name [ WHERE ...] [ ORDERBY ...] [ LIMIT ...]

DELETE FROM exam_result WHERE name = '李四 ' ;

数据库约束

约束类型

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句

NULL 约束

创建表时，可以指定某列不为空

CREATETABLE student (

id INT NOT NULL ,

sn INT ,

name VARCHAR ( 20 ),

qq_mail VARCHAR ( 20 )

);

UNIQUE ：唯一约束

指定 sn 列为唯一的、不重复的：

DROPTABLE IF EXISTS student;

CREATETABLE student (

          id INT NOT NULL ,

          sn INT UNIQUE,

          name VARCHAR ( 20 ),

          qq_mail VARCHAR ( 20 )

);

DEFAULT ：默认值约束

指定插入数据时， name 列为空，默认值 unkown

CREATETABLE student (

id INT NOT NULL ,

sn INT UNIQUE,

name VARCHAR ( 20 ) DEFAULT 'unkown' ,

qq_mail VARCHAR ( 20 )

);

PRIMARY KEY ：主键约束

指定id列为主键，对于整数类型的主键，常配搭自增长 auto_increment 来使用。插入数据对应字段不给值时，使用最大值 +1

CREATETABLE student (

        id INT NOT NULL PRIMARY KEY,

        sn INT UNIQUE,

        name VARCHAR ( 20 ) DEFAULT 'unkown' ,

          qq_mail VARCHAR ( 20 )

);

FOREIGN KEY ：外键约束

外键用于关联其他表的主键或 唯一键 ，语法：

foreign key ( 字段名 ) references 主表 ( 列 )

创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，

classes_id为外键，关联班级表id

CREATETABLE student (

id INT PRIMARY KEY auto_increment,

sn INT UNIQUE,

name VARCHAR ( 20 ) DEFAULT 'unkown' ,

qq_mail VARCHAR ( 20 ),

classes_id int ,

FOREIGN KEY (classes_id) REFERENCES classes(id)

);

CHECK 约束

MySQL 使用时不报错，但忽略该约束

createtable test_user (

id int ,

name varchar ( 20 ),

sex varchar ( 1 ),

check (sex = ' 男 ' or sex= ' 女 ' )

);

表得设置

三大范式：

一对一

一对多

多对多

//创建课程表

CREATE TABLE course (

id INT PRIMARY KEY auto_increment,

name VARCHAR ( 20 )

);

//学生表

CREATE TABLE student (

id INT PRIMARY KEY auto_increment,

sn INT UNIQUE,

name VARCHAR ( 20 ) DEFAULT 'unkown' ,

qq_mail VARCHAR ( 20 ),

classes_id int ,

);

//成绩表

CREATE TABLE score (

id INT PRIMARY KEY auto_increment,

  score DECIMAL ( 3 , 1 ),

student_id int ,

  course_id int ,

   FOREIGN KEY (student_id) REFERENCES student(id),

FOREIGN KEY (course_id) REFERENCES course(id)

);

新增

INSERT INTO 表名 [( column [, column ...])] SELECT ...

创建一张用户表，设计有 name 姓名、 email 邮箱、 sex 性别、 mobile 手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name 、 qq_mail

CREATETABLE test_user (

id INT primary key auto_increment,

name VARCHAR ( 20 ) comment ' 姓名 ' ,

age INT comment ' 年龄 ' ,

email VARCHAR ( 20 ) comment ' 邮箱 ' ,

sex varchar ( 1 ) comment ' 性别 ' ,

mobile varchar ( 20 ) comment ' 手机号 '

);

insert into test_user(name, email) select name, qq_mail from student;

聚合查询

说明	函数
返回查询到的数据的数量	COUNT([DISTINCT] expr)
返回查询到的数据的总和，不是数字没有意义	SUM([DISTINCT] expr)
返回查询到的数据的平均值，不是数字没有意义	AVG([DISTINCT] expr)
返回查询到的数据的最大值，不是数字没有意义	MAX([DISTINCT] expr)
返回查询到的数据的最小值，不是数字没有意义	MIN([DISTINCT] expr)

COUNT（计数）

SELECT COUNT (qq_mail) FROM student;

SUM（总和）

SELECT SUM (math) FROM exam_result WHERE math < 60 ;

AVG（平均分）

SELECT AVG (chinese + math + english) 平均总分 FROM exam_result;

MAX （最大值）

SELECT MAX (english) FROM exam_result;

MIN（最小值）

SELECT MIN (math) FROM exam_result WHERE math > 70 ;

GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是 “ 分组依据字段 ” ，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。

select column1, sum(column2), .. from table group by column1,column3;

创建的一个案例

create table play(

id int primary key auto_increment,

name varchar ( 20 ) not null ,

role varchar ( 20 ) not null ,

salary numeric ( 11 , 2 )

);

insert into play(name, role, salary) values

( ' 马云 ' , ' 服务员 ' , 1000.20 ),

( ' 马化腾 ' , ' 游戏陪玩 ' , 2000.99 ),

( ' 孙悟空 ' , ' 游戏角色 ' , 999.11 ),

( ' 猪无能 ' , ' 游戏角色 ' , 333.5 ),

( ' 沙和尚 ' , ' 游戏角色 ' , 700.33 ),

( ' 隔壁老王 ' , ' 董事长 ' , 12000.66 );

select role,max(salary),min(salary),avg(salary) from emp groupby role;

HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

select role,max(salary),min(salary),avg(salary) from emp groupby role having avg(salary)< 1500 ;

联合查询

内连接

select 字段 from 表 1 别名 1 [inner] join 表 2 别名 2 on 连接条件 and 其他条件 ;

select 字段 from 表 1 别名 1, 表 2 别名 2 where 连接条件 and 其他条件 ;

SELECT stu .sn , stu .NAME , stu .qq_mail , sum( sco .score ) FROM student stu JOIN score sco ON stu .id = sco .student_id GROUP BY sco .student_id ;

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

select 字段名 from 表名 1 right join 表名 2 on 连接条件 ;

select 字段名 from 表名 1 left join 表名 2 on 连接条件 ;

select * from student stu left join score sco on stu .id =sco .student_id ;

自连接

自连接是指在同一张表连接自身进行查询。

select id,name from course where name= 'Java' or name= ' 计算机原理 ' ;

SELECT s1.* FROM score s1 JOIN score s2 ON s1 .student_id = s2 .student_id AND s1 .score < s2 .score AND s1 .course_id = 1 AND s2 .course_id = 3 ;

子查询

单行子查询

子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询

select * from student where classes_id=( select classes_id from student where name= ' 不想毕业 ' );

查询与 “ 不想毕业 ” 同学的同班同学

多行子查询

返回多行记录的子查询

[NOT] IN 关键字

select * from score where course_id in ( select id from course where name= ' 语文 ' or name= ' 英文 ' );

select * from score where course_id not in ( select id from course where name!= ' 语文 ' and name!= ' 英文 ' );

[NOT] EXISTS 关键字

select * from score sco where exists ( select sco .id from course cou where (name= ' 语文 ' or name= ' 英文 ' ) and cou .id = sco .course_id );

select * from score sco where not exists ( select sco .id from course cou where (name!= ' 语文 ' and name!= ' 英文 ' ) and cou .id = sco .course_id );

合并查询

在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union ， union all 。使用 UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

select * from course where id< 3

union

select * from course where name= ' 英文 ' ;

//或者使用or来实现

select * from course where id< 3 or name= ' 英文 ' ;

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

select * from course where id< 3

union all

select * from course where name= ' 英文 ' ;

索引

特点：

数据量较大，且经常对这些列进行条件查询。
该数据库表的插入操作，及对这些列的修改操作频率较低。
索引会占用额外的磁盘空间。

查看索引

show index from 表名 ;

创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名 ( 字段名 );

删除索引

drop index 索引名 on 表名 ;

事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。其实封装了一组实际操作的sql语句。用来具体操作一个逻辑功能。

使用：

开启事务：start transaction;
执行多条SQL语句
回滚或提交：rollback/commit;

rollback即是全部失败，commit即是全部成功。

start transaction;

账户减少2000

update accout set money=money- 2000 where name = '456 ' ;

账户增加2000

update accout set money=money+ 2000 where name = '123 ' ;

commit;

java的JDBC编程

基础条件

编程语言，如Java，C、C++、Python等
数据库，如Oracle，MySQL，SQL Server等
数据库驱动包：不同的数据库，对应不同的编程语言提供了不同的数据库驱动包，如：MySQL提供了Java的驱动包mysql-connector-java，需要基于Java操作MySQL即需要该驱动包。同样的，要基于Java操作Oracle数据库则需要Oracle的数据库驱动包ojdbc

Statement对象主要是将SQL语句发送到数据库中。JDBC API中主要提供了三种Statement对象。

实际开发中最常用的是PreparedStatement对象

参数化slq查询
占用符不能使用多值
性价比Statement高
sql预编译
占位符下标从1开始
阻止常见sql注入攻击

executeQuery() 方法执行后返回单个结果集的，通常用于 select 语句

executeUpdate() 方法返回值是一个整数，指示受影响的行数，通常用于 update 、 insert 、 delete

语句

ResultSet

ResultSet 对象它被称为结果集，它代表符合 SQL 语句条件的所有行，并且它通过一套 getXXX 方法提供了对这些行中数据的访问。

ResultSet 里的数据一行一行排列，每行有多个字段，并且有一个记录指针，指针所指的数据行叫做当前数据行，我们只能来操作当前的数据行。我们如果想要取得某一条记录，就要使用ResultSet 的 next() 方法 , 如果我们想要得到 ResultSet 里的所有记录，就应该使用 while 循环。

实例：

 public static void main(String[] args) throws SQLException {
        //JDBC需要通过几个步骤建立
        //1.创建并初始化一个数据库
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/tb_user/characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("xietian1314");
        //2.和数据库服务器建立链接
        Connection connection= dataSource.getConnection();
        //3.构造sql语句
        String sql= "intsert into student(id,name) value(1,'123')";
        String sqlDelete="delete from student where name='123'";
        String sqlUpdate="update student set id=1 where name='123'";
        String sqlSelect="select id,name from student";
        PreparedStatement statement4=connection.prepareStatement(sqlSelect);
        PreparedStatement statement3=connection.prepareStatement(sqlUpdate);
        PreparedStatement statement2=connection.prepareStatement(sqlDelete);
        PreparedStatement statement= connection.prepareStatement(sql);
        //4.之行sql语句
        int ret= statement.executeUpdate();
        System.out.println(ret);
        //5.释放资源
        statement.close();
        connection.close();