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

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

（内容很多，但大部分都是如何操作）我认为会用就行，除非真心想搞数据库，否则大概率这篇文章是够用的。顺便我自我复习一下。主要是复习，所以很多都不实际演示截图了。自己去数据库敲一下最好

目录

概要

数据库操作

显示当前的数据库

创建数据库

使用数据库

删除数据库

数据表操作

数值类型

字符串类型

时间类型

常用类型

使用想要操作的数据库

查看表结构

创建表

删除表（也据危险）

插入数据

查询数据

条件查询（where）

分页查询：LIMIT

修改数据（Update）

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

数据库约束

约束类型

表得设置

新增

聚合查询

GROUP BY子句

HAVING

联合查询

子查询

索引

事务

---

概要

数据库大体可以分为 关系型数据库 和 非关系型数据库 关系型数据库（ RDBMS ）： 是指采用了关系模型来组织数据的数据库。 简单来说，关系模型指的就是二维表格模型，而一个 关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。 基于标准的SQL ，只是内部一些实现有区别。常用的关系型数据库如： 1. Oracle ：甲骨文产品，适合大型项目，适用于做复杂的业务逻辑，如 ERP 、 OA 等企业信息系 统。收费。 2. MySQL ：属于甲骨文，不适合做复杂的业务。开源免费。 3. SQL Server ：微软的产品，安装部署在 windows server 上，适用于中大型项目。收费。 非关系型数据库： 不规定基于 SQL 实现，现在更多是指 NoSQL 数据库，如： 1. 基于键值对（ Key-Value ）：  memcached 、 redis 2. 基于文档型：  mongodb 3. 基于列族：  hbase 4. 基于图型：  neo4j

	关系型数据库	非关系型数据库
使用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 ;

IN

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 = '李四 ' ;

数据库约束

约束类型

1. NOT NULL - 指示某列不能存储 NULL 值。
2. UNIQUE - 保证某列的每行必须有唯一的值。
3. DEFAULT - 规定没有给列赋值时的默认值。
4. PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
5. FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
6. 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= ' 英文 ' ;

索引

特点：

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

查看索引

show index from 表名 ;

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

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

删除索引

drop index 索引名 on 表名 ;

事务

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

1. 开启事务：start transaction;
2. 执行多条SQL语句
3. 回滚或提交：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编程

 基础条件

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

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

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

1. 参数化slq查询
2. 占用符不能使用多值
3. 性价比Statement高
4. sql预编译
5. 占位符下标从1开始
6. 阻止常见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= Connection();//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= uteUpdate();System.out.println(ret);//5.释放资源statement.close();connection.close();

​​​​​​​

其他文章接口

 1.String方法（重要，对于操作字符串有巨大的帮助）

文章链接

2.java常用的接口及其方法（包含拷贝，比较，排序，构造器）

文章链接

3.初阶数据结构

 3.1 顺序表：ArrayList

[文章链接]

3.2 链表：LinkedList

[文章链接]

3.3 栈：Stack

[文章链接]

 3.4 队列：Queue

[文章链接]

3.5 二叉树：Tree

[文章链接]

3.6 优先级队列：PriorityQueue（堆排序）

[文章链接]

3.7 Map和Set
HashMap和HashSet，TreeMap和TreeSet

[文章链接]

4. 排序（7种方式）

4.1 插入排序（两种）

4.2  选择排序（两种）

4.3 快速排序

4.4 堆排序
里面有堆排序的实现和逻辑

[文章链接]

 4.5  归并排序

5.多线程

[文章链接]

6.网络编程

7.HTML

8.数据库Mysql

[文章链接]