数据库篇-MySQL

已于 2023-08-16 23:30:52 修改
阅读量117 收藏 1
点赞数
分类专栏： MySQL 文章标签： 数据库 java mysql
于 2023-08-16 23:08:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/const_wu/article/details/132324678
版权

 一、概念

1.什么是数据库

a.数据库：DB（DataBase）

        概念：存储数据、管理数据。安装在操作系统中的一种数据仓库、软件。

b.数据库分类

        关系型数据库：SQL（Structured Query Language）。

  • 例如：MySQL、Oracle、Sql Server、DB2、SQLlite
  • 通过表和表之间，行和列之间的关系进行数据的存储
  • 通过外键关联来建立表与表之间的关系

        非关系型数据库：No SQL（Not Only SQL）。

  • 例如：Redis、MongoDB
  • 指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。

c.数据库管理系统（Database Management System,简称DBMS）

  1. 专门用于管理数据库的计算机系统软件;

  2. 能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并对数据完整性、安全性进行控制;

  3. MySQL就是数据库管理系统。

d.数据库应用系统（Database Application System）

        使用数据库技术的系统，基本上所有的信息系统都是数据库应用系统,它通常由软件、数据库和数据管理员组成。我们开发一款软件，然后这款软件能使用到数据库(和数据库有关系，有通信)，那么，这一款软件我们就可以称之为数据库应用系统。

e.数据库选择

        建议使用mysql-5.7.22-winx64版本，配合Navicat Premium 15使用。

2.数据库的存储引擎

a.默认存储引擎

INNODB：

  • 默认使用，安全性高，支持事务的处理，多表多用户操作。

MYISAM：

  • 5.5以前默认使用，节约空间，速度较快。

        区别：

MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为2倍

b.数据库文件存储地址

MySQL数据表以文件方式存放在磁盘中：

  • 包括表文件 , 数据文件 , 以及数据库的选项文件
  • 位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

c.存储内容

        InnoDB采用的是B+树存储，将索引和数据文件一起存储。文件类型包括.frm、.ibd以及在上一级目录的ibdata1文件。

        MyISAM采用的是哈希表存储，将索引、数据文件、表结构文件分开存储。文件类型就包括

  • .frm：表结构定义文件
  • .MYD：数据文件
  • .MYI：索引文件

3.JDBC

        我们编写的程序会通过数据库驱动（执行器）来和数据库进行交互，不同的数据库有不同的驱动，这不便于我们程序对各种数据库进行操作；因此为了简化对不同数据库的操作，SUN公司提供了一个Java操作数据库的规范JDBC；不同数据库的规范由对应的数据库厂商完成，对于开发人员，只需要掌握JDBC接口的操作即可。

优点：

        JDBC使得编程人员从复杂的驱动器调用命令和函数中解脱出来，可以致力于应用程序中的关键地方。
        JDBC支持不同的关系数据库，这使得程序的可移植性大大加强。
        JDBC API是面向对象的，可以让用户把常用的方法封装为—个类，以备后用。

缺点：

        使用JDBC，访问数据记录的速度会受到一定程度的影响。
        JDBC结构中包含不同厂家的产品，这就给更改数据源带来了很大的麻烦

a.什么是jdbc

        JDBC(Java DataBase Connectivity（Java数据库的连接）)是一种用于执行SQL语句(DML,DDL,DQL)的Java API，可以为多种关系数据库（oracle,mysql,SQL server）提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

b.JDBC访问数据库的主要方式

  1. 直接使用JDBC的API去访问数据库服务器(MySQL/Oracle)；
  2. 间接地使用JDBC的API去访问数据库服务器。
    1. 第三方O/R Mapping工具，如Hibernate, MyBatis等.(底层依然是jdbc)；
    2. JDNC是java访问数据库的基石，其他技术都是对jdbc的封装。

c.JDBC核心接口与类

        JDBC核心类库包含在java.sql包中。

        接口：

  • Connection：特定数据库的连接（会话）。在连接上下文中执行SQL语句并返回结果。
  • PreparedStatement：表示预编译的 SQL 语句的对象。
  • Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
  • ResultSet ：表示数据库结果集的数据表，通常通过执行查询数据库的语句生成 。
  • CallableStatement ：用于执行 SQL 存储过程的接口 。

        类：

  • DriverManager：负责管理JDBC驱动程序。使用JDBC驱动程序之前，必须先将驱动程序加载并注册后才可以使用，同时提供方法来建立与数据库的连接。
  • SQLException：有关数据库操作的异常

        JDBC对象：

        DriverManager：驱动管理

//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");

        本质上执行DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());

        Statement：

        执行sql的对象，用于向数据库发送SQL语句，想完成对数据库的增删改査，只需要通过这个对象向数据库发送增删改查语句即可

statement.executeQuery();//查询操作,返回结果
statement.execute();//执行sql
statement.executeUpdate();//用于增删改,返回受影响的行数

        代码示例：

//增
public void insert(){
    Statement s = conn.createStatement();
    String sql = "insert into user(...) value(...)";
    int num = s.executeUpdate(sql);
    if(num > 0)
        System.out.println("插入成功！！");
}
//删
public void delete(){
    Statement s = conn.createStatement();
    String sql = "delete from user where id = 1";
    int num = s.executeUpdate(sql);
    if(num > 0)
        System.out.println("删除成功！！");
}
//改
public void update(){
    Statement s = conn.createStatement();
    String sql = "update user set name='' where name=''";
    int num = s.executeUpdate(sql);
    if(num > 0)
        System.out.println("修改成功！！");
}
//查
public void find(){
    Statement s = conn.createStatement();
    String sql = "select * from user where id=1";
    ResultSet rs = s.executeQuery(sql);
    while(rs.next){
        //根据查询到的数据类型分别调用rs的方法映射到java对象中
    }
}

        ResultSet：

        查询的结果集，封装了所有查询的结果

        代码示例：

resultSet.getObject();    //在不知道列类型的情况下使用

//获取指定类型的数据类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();

//遍历 指针
resultSet.beforeFirst();    //移动到最前面
resultSet.afterLast();    //移动到最后面
resultSet.next();    //移动到下一个数据
resultSet.previous();    //移动到前一行
resultSet.absolute(row);    //移动到指定行

d.Statement-SQL注入

        SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

        代码示例：

        拼接整条sql语句是select * from app_user where name=' ' or '1==1'，其中1==1永远是真的，所以该sql语句相当于查询表中所有的数据；这就是sql注入，主要是字符串拼接引起的问题，十分危险！

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLInjection {
    public static void main(String[] args) throws SQLException {
        searchName("' or '1=1");
    }

    //查找指定名字用户信息
    public static void searchName(String username) throws SQLException {
        //获得数据库对象connection
        Connection connection = JDBCUtils.getConnection();
        //获取sql执行对象statement
        Statement statement = connection.createStatement();
        //执行sql
        String sql = "select * from app_user where name='" + username + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
        }
        //释放连接
        JDBCUtils.release(connection, statement, resultSet);
    }
}

        PreparedStatement对象

        PreparedStatement是Statement的子类，与其相比，可以防止SQL注入，并且效率更高。

        代码示例：

        

import java.sql.*;

public class SQLInjection {
    public static void main(String[] args) throws SQLException {
        searchName("' 'or '1=1'");
    }

    //登录
    public static void searchName(String username) throws SQLException {
        //获得数据库对象connection
        Connection connection = JDBCUtils.getConnection();
        //获取sql执行对象preparedStatement(预编译sql,先写不执行,参数用?表示)
        PreparedStatement preparedStatement = connection.prepareStatement("select * from app_user where name=?");
        //手动传参
        preparedStatement.setString(1, username);
        //执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
        }
        //释放连接
        JDBCUtils.release(connection, preparedStatement, resultSet);
    }
}

e.使用JDBC链接数据库

        步骤说明（加载-链接-获取语句对象-执行SQL-释放资源）：

  1. 载入JDBC驱动程序
  2. 定义连接URL
  3. 建立连接
  4. 创建Statement对象
  5. 执行查询或更新
  6. 结果处理
  7. 关闭连接
import java.sql.*;

public class JDBCTest {
	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try{
			// 1、注册驱动
			Driver driver = new com.mysql.jdbc.Driver();//多态，父类型引用指向子类型对象
			//Driver driver = new com.oracle.jdbc.OracleDriver(); //Oracle的驱动
			
			DriverManager.registerDriver(driver);
			
			// 2、获取连接
			/*
			    url:统一资源定位符（网络中某个资源的绝对路径）
				https:www.baidu.com/  这就是一个URL
				url包括哪几部分:
					协议
					IP
					Port
					资源名

					eg:baidu
					http://182.61.200.7:80/index.html
					http://通信协议
					182.61.200.7 服务器IP地址
					80 服务器上软件的端口
					index.html 是服务器上某个资源名

					jdbc:mysql://127.0.0.1:3306/bjpowernode
					jdbc:mysql:// 协议
					127.0.0.1 IP地址
					3306 mysql数据库端口号
					bjpowernode 具体的数据库实例名

					说明：localhost和127.0.0.1都是本机IP地址。

					什么是通信协议，有什么用？
					通信协议是通信之前就提前定好的数据传送格式。
					数据包具体怎么传数据，格式提前定好的。


			*/
			// static Connection getConnection(String url, String user, String password)
			String url = "jdbc:mysql://127.0.0.1:3306/bjpowernode";
			String user = "root";
			String password = "333";
			conn = DriverManager.getConnection(url,user,password);
			System.out.println("数据库连接对象" + conn);	//数据库连接对象com.mysql.jdbc.JDBC4Connection@1ae369b7

			// 3、获取数据库操作对象(Statement专门执行sql语句的)
			// Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。
                stmt = conn.createStatement();

			// 4、执行sql语句
			// int executeUpdate(String sql) 
			String sql = "insert into dept(deptno,dname,loc) values(50,'人事部'，'北京')";
			//专门执行DML语句的（insert delete update）
			// 返回值是“影响数据库中的记录条数”
			int connt = stmt.executeUpdate(sql); 
			//合并语句
			//int count = stmt.executeUpdate("update dept set dname = '销售部',loc = '合肥' where deptno = 20;");
			System.out.println(count == 1 ? "保存成功":"保存失败");

			// 5、处理查询结果集

		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			// 6、释放资源
			//为了保障资源一定释放，在finally语句块中关闭资源
			// 并且要遵循从小到大依次关闭
			//分别对其try..catah
			if(stmt != null) {
				try	{
					stmt.close();	
				}
				catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try	{
					conn.close();	
				}
				catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

二、操作数据库

注释：

1.cmd命令

注意：所有的语句都要以分号结尾

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
describe/desc 表名;	--显示表的信息
create database 数据库名;	--创建一个数据库
exit	--退出连接

2.操作数据库

a.创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;

b.删除数据库

DROP DATABASE [if EXISTS] 数据库名;

c.使用数据库

--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;

d.查看数据库

SHOW DATABASES;

3.修改数据库

a.修改表名

ALTER TABLE 旧表名 RENAME AS 新表名

b.添加字段

ALTER TABLE 表名 ADD字段名 列属性[属性]

c.修改字段

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

d.删除字段

ALTER TABLE 表名 DROP 字段名

        代码示例：

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;

-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);

-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

4.操作表

a.创建表

        常用命令：

SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句
SHOW CREATE TABLE 表名;-- 查看表的定义语句
DESC 表名;-- 显示表的具体结构

        格式：

CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]

注意：

  • 表名和字段尽量使用``括起来

  • AUTO_INCREMENT 代表自增

  • 所有的语句后面加逗号，最后一个不加

  • 字符串使用单引号括起来

  • 主键的声明一般放在最后，便于查看

  • 不设置字符集编码的话，会使用MySQL默认的字符集编码Latin1，不支持中文，可以在my.ini里修改

代码示例：

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

b.删除表

语法：DROP TABLE [IF EXISTS] 表名

  • IF EXISTS为可选 , 判断是否存在该数据表
  • 如删除不存在的数据表会抛出错误
-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teachers;

c.列类型

数值：

数据类型描述大小
tinyint十分小的数据1个字节
smallint较小的数据2个字节
mediumint中等大小的数据3个字节
int标准的整数4个字节
bigint较大的数据8个字节
float浮点数4个字节
double浮点数8个字节
decimal字符串形式的浮点数，一般用于金融计算

字符串：

数据类型描述大小
char字符串固定大小0~255
varchar可变字符串0~65535
tinytext微型文本2^8-1
text文本串2^16-1

时间日期：

数据类型描述格式
date日期格式YYYY-MM-DD
time时间格式HH：mm：ss
datetime最常用的时间格式YYYY-MM-DD HH：mm：ss
timestamp时间戳，1970.1.1到现在的毫秒数
year年份表示

NULL：

  • 没有值，未知
  • 不要使用NULL值进行计算

d.字段属性

UnSigned：

  • 无符号的
  • 声明了该列不能为负数

ZEROFILL：

  • 0填充的
  • 不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement：

  • 通常理解为自增，自动在上一条记录的基础上默认+1

  • 通常用来设计唯一的主键，必须是整数类型

  • 可定义起始值和步长

    • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
    • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL：

  • 默认为NULL , 即没有插入该列的数值
  • 如果设置为NOT NULL , 则该列必须有值

DEFAULT：

  • 默认的
  • 用于设置默认值
  • 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

注意：每一个表，都必须存在以下五个字段

名称描述
id主键
version乐观锁
is_delete伪删除
gmt_create创建时间
gmt_update修改时间

e.表的约束

        数据库就是用来单纯的表，只用来存数据，只有行（数据）和列（属性）。

        我们想使用多张表的数据，使用外键，用程序去实现。

  1. 非空约束(NK)：NOT NULL，不允许某列的内容为空。
  2. 设置列的默认值：DEFAULT。
  3. 唯一约束(UK)：UNIQUE，在该表中，该列的内容必须唯一。
  4. 主键约束：PRIMARY KEY， 非空且唯一。
  5. 主键自增长：AUTO_INCREMENT，从1开始，步长为1。
  6. 外键约束：FOREIGN KEY，A表中的外键列.A表中的外键列的值必须参照于B表中的某一列(B表主键)。

        外键概念：

        如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

        在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

        外键作用：

        保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

三、SQL语句

        SQL：结构化查询语言（Structured Query Language），是关系数据库的标准语言，它的特点是：简单、灵活、功能强大。

1.SQL语句包含六个部分

a.数据查询语言（DataBase Query Language）

        其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

b.数据操作语言（DataBase Manipulation Language）：

        其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

c.事务处理语言（TPL）：java概念

        它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

d.数据控制语言（DCL）：

        它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

e.数据定义语言（DDL）：建库建表，删库删表

        其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

f.指针控制语言（CCL）：Sql编程

        它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

注意：

        1. 在MySQL数据库中，SQL语句大小写不敏感

        2. SQL语句可单行或多行书写

        3. 在SQL语句中，关键字不能跨多行或缩写

        4. 为了提高可读性，一般关键字大写，其他小写

        5. 空格和缩进使程序易读

2.DML

a.添加-insert

        语法：

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

        代码示例：        

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');

-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');

-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1);

注意：

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的，但是值必须完整且一一对应
  3. 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开

b.修改-update

        语法：

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

        代码示例：

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;

-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';

-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';

c.删除-delete

        语法：

DELETE FROM 表名 [WHERE 条件]

代码示例：

-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

d.DELETE删除，重启数据库问题

  • INNODB 自增列会从1开始（存在内存当中，断电即失）
  • MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）

        TRUNCATE：

        作用：完全清空一个数据库表，表的结构和索引约束不会变！

        DELETE和TRUNCATE 的区别：

  • DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表
  • TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增
  • DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

        代码示例：

CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

-- 不会影响自增
DELETE FROM `test`;

-- 会影响自增
TRUNCATE TABLE `test`;

3.DQL

  • 查询数据库数据 , 如SELECT语句
  • 简单的单表查询或多表的复杂查询和嵌套查询
  • 是数据库语言中最核心,最重要的语句
  • 使用频率最高的语句

        代码示例：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

a.基础查询

  • 查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
  • 查询结果是一个虚拟的表格

语法：

SELECT 查询列表 FROM 表名;

代码示例：

-- 查询全部学生
SELECT * FROM student;

-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;

-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;

-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;

-- 查询系统版本(函数)
SELECT VERSION();

-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;

-- 查询自增步长(变量)
SELECT @@auto_increment_increment;

-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;

b.条件查询

where 条件字句：检索数据中符合条件的值

语法：

select 查询列表 from 表名 where 筛选条件;

代码示例：

-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;

-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;

-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';

-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';

-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);

关于WHERE条件语句：

操作符含义
=等于
<>或!=不等于
>大于
<小于
<=小于等于
>=大于等于
BETWEEN…AND…闭合区间
AND和
OR或

c.分组查询

        语法：

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

        区别：

使用关键字筛选的表位置
分组前筛选where原始表group by的前面
分组后筛选having分组后的结果group by 的后面

        代码示例：

-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

d.连接查询

        语法：

-- 左外连接
SELECT *
FROM A LEFT JOIN B
ON A.key=B.key;

-- 左连接
SELECT *
FROM A LEFT JOIN B
ON A.key=B.key
WHERE B.key IS NULL;

-- 内连接
SELECT *
FROM A INNER JOIN B
ON A.key=B.key;

-- 右外连接
SELECT *
FROM A RIGHT JOIN B
ON A.key=B.key;

-- 右连接
SELECT *
FROM A RIGHT JOIN B
ON A.key=B.key
WHERE A.key IS NULL;

-- 全外连接
SELECT *
FROM A FULL OUTER JOIN B
ON A.key=B.key;

-- 两表独有的数据集
SELECT *
FROM A FULL OUTER JOIN B
ON A.key=B.key
WHERE A.key IS NULL
OR B.key IS NULL;

        代码示例：

-- 查询学员所属的年级（学号，学生姓名，年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;

-- 查询科目所属的年级
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;

-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
on r.SubjectNo=sub.SubjectNo
where SubjectName='课程设计';

        自链接：自己和自己的表连接

        一张表拆为两张一样的表即可。

e.排序查询

排序

        语法：

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc
  • order by的位置一般放在查询语句的最后（除limit语句之外）
asc ：升序，如果不写默认升序
desc：降序

  代码示例：

SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;
分页

    语法：

select 查询列表
from 表
limit offset,pagesize;
  • offset代表的是起始的条目索引，默认从0开始
  • size代表的是显示的条目数
  • offset=(n-1)*pagesize

  代码示例：

-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面

f.子查询

        理解：在 where子句中嵌套一个子查询语句

        代码示例：

-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列

-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='课程设计'
ORDER BY StudentResult DESC;
-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='课程设计'
)

g.SQL函数

常用函数
-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串

-- 时间日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT now(); -- 获取当前时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统信息
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();
聚合函数
函数描述
max最大值
min最小值
sum和
avg平均值
count计算个数
SELECT COUNT(StudentName) FROM student; 
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;

SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;
小吴的知识仓库
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
资料-MySQL数据库、视频-MySQL数据库
02-19
MySQL-运维篇.pdf MySQL-进阶篇.pdf MySQL-基础篇.pdf MySQL存储过程.xmind 文档 运维篇.rar 进阶篇.rar 基础篇.rar 资料-MySQL数据库 视频-MySQL数据库 文档 运维篇.rar 进阶篇.rar 基础篇.rar MySQL-运维篇.pdf ...
Web-数据库-mysql篇
04-15
Web-数据库-mysql篇 Web-数据库-mysql篇 Web-数据库-mysql篇
01-MySQL和MariaDB数据库介绍1
08-03
例如，Python的`mysql-connector-python`库可以方便地连接到MySQL数据库，执行SQL查询，并处理结果。 总的来说，MySQL和MariaDB是现代数据存储的关键组成部分，它们为企业提供了高效、可靠的数据管理解决方案。正确...
MySQL入门到精通-MySQL进阶篇
02-06
在“MySQL入门到精通-MySQL进阶篇”中，我们将深入探讨存储引擎、索引和SQL优化等关键概念。 首先，MySQL的体系结构分为四个层次：连接层、服务层、引擎层和存储层。连接层主要处理客户端的连接请求，包括认证和...
linux安装配置mysql数据库
07-13
在本篇文章中，我们将学习如何在 Linux 系统上安装和配置 MySQL 数据库，包括安装 MySQL 软件包、配置 MySQL 服务器、设置 root 用户密码、配置远程登录、开放防火墙端口等步骤。 安装 MySQL 软件包 首先，我们...
PostgreSQL源码分析——CREATE DATABASE
让我思考一下
06-18 612
这里我们分析一下在PostgreSQL中创建数据库的源码，在分析源码之前，最好先阅读《PostgreSQL指南内幕探索》的第一章，数据库集簇、数据库和数据表，弄清其空间布局，理解PG中，数据库、表、元组是怎么布局的。通俗一点说，创建一个数据库相当于创建一个数据目录，在这个目录下，存放该数据库中定义的表、索引等对象。
PostgreSQL源码分析——INSERT
让我思考一下
06-18 319
这里我们对INSERT语句进行分析， 只分析其宏观过程，具体细节后续再分析。我们就分析下面的语句的执行过程。
MySQL修改分隔符
2401_83963227的博客
06-18 224
MySQL修改分隔符
windows桌面运维----第七天
最新发布
2401_83565544的博客
06-19 213
目录 1、运维有哪些方面选择： 2.网络速度慢： 3.无法访问某个网站： 4.无法共享文件或打印机： 5.无法连接VPN：
ORDER BY FIELD
北尘
06-15 311
ORDER BY FIELD 是MySQL中的一个特殊排序函数，它允许你基于一个自定义的值列表对查询结果进行排序。在这个例子中，所有属于’HR’部门的员工会首先列出，接着是’IT’部门的员工，然后是’Finance’部门的员工，最后是其他所有部门的员工，这些其他部门的员工将按照他们在表中原本的顺序排列。假设有一个employees表，包含name和department两列，现在你想按照特定部门顺序列出员工，部门顺序为：‘HR’, ‘IT’, ‘Finance’，其他部门按照自然顺序跟随。
redis-实战篇（3）优惠券秒杀
weixin_52661752的博客
06-18 590
课程中的使用方式是没有像cas一样带自旋的操作，也没有对version的版本号+1 ，他的操作逻辑是在操作时，对版本号进行+1 操作，然后要求version 如果是1 的情况下，才能操作，那么第一个线程在操作后，数据库中的version变成了2，但是他自己满足version=1 ，所以没有问题，此时线程2执行，线程2 最后也需要加上条件version =1 ，但是现在由于线程1已经操作过了，所以线程2，操作时就不满足version=1 的条件了，所以线程2无法执行成功。
简单了解MySql以及一些简单的应用MySql
weixin_63323754的博客
06-14 1375
初始MySql，能够简单地使用MySql
场景设计题
大袤宏图
06-15 394
为什么要这样分表？跨库join如何解决？数据量突增怎么解决？ 如何解决线上问题？cpu狂飙怎么办？频繁minor gc怎么办？可能造成的原因是什么？如何避免？ 数据库 隔离级别，怎么实现的？当前读，快照读？MVCC？ 分库分表的设计？ 分布式事务出现过不一致吗？为什么？怎么解决？有什么方法避免？怎么监控？监控到怎么处理？什么时候需要人工接入 如何优雅的写代码？什么代码算做优雅？什么代码是规范？你们代码规范是什么样的？ 如何进行code review？ mysql innodb下，能不能不设置主键？主键
2024数据库期末综合解析（部分题）
m0_74363339的博客
06-16 699
2024数据库期末综合。第4关：数据记录修改。第6关：数据查询二。
MySQL之复制(三)
Cover_sky的博客
06-18 712
前面的设置都是嘉定主备库均为刚刚安装好且都是默认的数据，也就是说两台服务器上数据相同，并且知道当前主库的二进制日志。这不是典型的案例，大多数情况下有一个已经运行了一段时间的主库，然后用一台新安装的备库与之同步，此时这台备库还没有数据。有几种办法来初始化备库或者从其他服务器克隆到备库。选项–single-transaction使得转储的数据为事务开始前的数据。如果使用的是非事务型表，可以使用–lock-all-tables选项来获得所有表的一致性转储。
pg修炼之道学习笔记
zyjzyjjyzjyz的博客
06-17 299
pg中的不同模式可以相当与mysql的不同库，因为mysql中不同库可以互相访问查询操作，pg中的模式也可以互相访问查询操作，但是pg中的不同库不可以互相访问，所以其实pg中的schema相当于mysql中的db。加入mysql实例中有三个数据库，在要移植到pg时，需要建三个模式，使其与mysql示例中的三个数据库相对应，而不是在pg中创建三个数据库与之对应。1、一个pg数据库服务下有多个db（多个数据库），当应用连接到一个数据库时，一般只能访问这个数据库中的数据，而不能访问其他数据库的内容（限制）
JDBC-Java与数据库
weixin_52242569的博客
06-18 314
boolean next() ：(1) 将光标从当前位置向前移动一行 （2）判断当前行是否为有效行。返回值 ： true ： 有效行，当前行有数据；false ：无效行，当前行没有数据；: Java 中 通过 try - catch 机制来实现 事务的回滚。JDBC 事务管理：Connection接口中定义了3个对应的方法。xxx getXxx(参数)：获取数据。封装了DQL查询语句的结果。mysql 事务管理。
sqlalchemy event监听
youhebuke225的博客
06-18 396
在 SQLAlchemy 中，event系统允许你监听数据库引擎、会话、映射类等对象上的事件，并在这些事件发生时执行自定义的代码。这对于在 SQL 语句执行前后、对象加载、对象刷新等时刻执行特定的逻辑非常有用。要使用 SQLAlchemy 的event系统，你需要首先导入相关的模块，并使用函数来监听事件。
MySQL 高级 - 第十二章 | 数据库的设计规范
xhmico的博客
06-11 1347
在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。范式的英文名称是，简称NF。它是英国人E.F.Codd在上个世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。有的时候不能简单按照规范要求设计数据表，因为有的数据看似穴余，其实对业务来说十分重要。这个时候，我们就要遵循业务优先。
头歌mysql-数据库应用开发(java篇)
08-10
MySQL是一种流行的关系型数据库管理系统，它提供了用于存储、管理和检索数据的强大功能。在Java开发中，我们可以通过MySQL来创建和操作数据库，实现各种应用程序。 在进行MySQL数据库应用开发时，我们首先需要连接...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值