MySQL小结

搬砖柯

于 2019-08-01 13:33:17 发布

阅读量164

点赞数 1

文章标签： MySQL小结

本文链接：https://blog.csdn.net/weixin_45292690/article/details/98051940

版权

文章目录

MySQL小结
备注

MySQL小结

检查本机是否安装过MySQL

在运行(Window标徽键 + R)输入指令services.msc会弹出Window服务菜单.按照名称排列找M开头的.

如果所安装的MySQL是5.5以下版本那数据库的实例名默认是MySQL,如果是5.5以上版本比如5.6,5.7那实例名就是MySQL56或者MySQL57.

使用指令C:\Users\Administrator>sc delete mysql8来删除数据库.还可以使用下面指令来删除数据库.

F:\WN07\mysql-8.0.15-winx64\bin>mysqld --remove
The service doesn't exist

注意点:删除服务一定要在服务停止之后

安装数据库

我们使用解压版的数据库来进行安装.压缩文件解压后得到目录结构

bin Binary 二进制的意思放的是所有的可执行文件.即我们启动数据库,停止数据库所使用的所有指令都在这里.
docs Document 放的是文档
etc 配置
include 存放Header头文件(.h 是头文件)
lib Library 放MySQL运行期间所需要使用的各种库文件. DLL是动态链接库文件.
run
share
var

提供解压版安装所需要的配置文件

解压版的安装需要提供一个配置文件一般命名为my.ini(纯粹的习惯,没任何道理可讲)

[mysqld]
# 设置3306端口  端口号的取值范围:0-65535 前1000不要用.往往是系统使用的
port=3306
# 设置mysql的安装目录
basedir=F:\WN08\mysql-8.0.15-winx64
# 设置mysql数据库的数据的存放目录
datadir=F:\WN08\mysql-8.0.15-winx64\Data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=InnoDB
# 默认使用“mysql_native_password”插件认证 MySQL8修改了秘钥验证机制.如果使用第三方软件连接比如Navicat,在新的密码验证机制下是无法连接的.所以要改成之前的密码验证机制.
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口 要保证和上面的端口一致.
port=3306
# 当使用日志时需要小心,可能会导致日志乱码.
default-character-set=utf8mb4

使用指令来完成数据库MySQL的安装

整个安装过程需要使用指令有5个

初始化数据库指令(生成Data文件夹.) mysqld --initialiaze --console
安装数据库服务 mysqld --install [服务名] []代表可选,如果不写默认服务名MySQL(数据库实例名)
启动数据库服务 net start 数据库实例名
登陆数据库 mysql -u用户名 -hlocalhost/127.0.0.1 -p密码 -P端口号.端口号是可选的.一般最小的书写方式:mysql -u用户名 -p密码
修改数据库的登陆密码.初始化时会提供一个临时密码,一般都不太好记,所以把密码修改下.alter user '用户名'@'主机地址' identified by '新密码'

初始化数据库

@:mysqld --initialize --console
2019-07-27T02:04:52.951678Z 0 [System] [MY-013169] [Server] F:\WN08\mysql-8.0.15
-winx64\bin\mysqld.exe (mysqld 8.0.15) initializing of server in progress as pro
cess 7840
2019-07-27T02:05:00.210094Z 5 [Note] [MY-010454] [Server] A temporary password i
s generated for root@localhost: sk6/88aOj;GS
2019-07-27T02:05:01.886189Z 0 [System] [MY-013170] [Server] F:\WN08\mysql-8.0.15
-winx64\bin\mysqld.exe (mysqld 8.0.15) initializing of server has completed

@:

这个过程可能会产生错误,如果产生会以[ERROR]的形式体现.出现的原因多数是下面几点

Data文件已经存在
所在磁盘没有写权限.(因为初始化的目的就是生成一些文件)
缺少VC++库支持.

安装数据库服务!

@:mysqld --install mysql8
Service successfully installed.

@:

如果安装的过程是服务名重复,可能会安装失败!.

启动MySQL服务

@:net start mysql8
mysql8 服务正在启动 ..
mysql8 服务已经启动成功。


# 停止的指令是stop

登陆MySQL数据库

@:mysql -uroot -hlocalhost -P3306 -psk6/88aOj;GS
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

修改登陆密码

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

重新登陆测试

@:mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

数据库的基本概念

最无聊的东西

数据库(DataBase)一般简称DB,是数据存储的仓库.是按照数据结构来存储,组织管理数据的仓库.所以完整的数据库技术涉及三个方面

存储技术
组织技术
管理技术

如果说按照技术的发展史,可以把数据库的历史分为5个阶段

数据管理阶段(人工管理)
关系型数据库阶段
结构化查询语句阶段
商业数据库阶段
面向对象数据库阶段

数据管理阶段

和数据存储技术有关

在计算机刚诞生的时候,使用打孔卡片来存储数据,由赫尔曼-何乐礼.这种存储方式非常的原始存储的是二进制数据,所以它的存储量非常有限.

1951年雷明顿兰德发明磁带驱动器,是一种可以秒级记录数百条数据的全新存储方式.理论上一卷磁带可以记录1万张打孔卡片上的技术.

IBM基于磁带驱动器发明了磁盘驱动器还提出了随机存储的概念(RAM).到这里已经把存储的技术发展到一定高度了,所以当初的研究者就把研究的重心由存储技术转移到数据的组织上.就进入了第二阶段

关系型数据库阶段

在这个阶段比较有名的是三种结构

网状模型
层次模型
关系模型

网状模型和层次模型是先于关系模型产生的,它们的优点在于数据的集中和共享上.但是有一个弊端:数据独立性和抽象级别不够.为了解决这个问题就产生了关系模型.

关系模型是把现实生活中的一个实体作为一个独立的个体存储在数据表中.在表中所对应的就是一行记录.个体和个体之间的关系通过属性来实现连接.

这样做的好处是保留了数据的独立性.代表关系的属性体现了灵活性.所以目前主流的数据库都是关系型数据库.(这句话没毛病.因为现在有NoSQL这个概念非关系型数据库缓存.使用键值对key-value的形式保存数据.)

当关系型数据库的产生就代表了数据库的组织和设计不成问题.那研究的中心再次转移.由IBM提出结构化查询语句 Structured Query Language.从而进入第三阶段:

结构化查询语句阶段

Structured Query Language 简称SQL

1974年IBM提出的结构化查询语句.SQL包括了对数据库生命周期各个阶段的各种操作.被ANSI(美国国家标准协会)和ISO(国际标准化组织)录入SQL的标准存在.经过不断的完善产生的第一个正式版本就是SQL92.这几年又产生了SQL99,SQL2003和SQL2008版本.

结构化查询语句在标准中有三个部分

数据定义语言 DDL
数据操作语言 DML
数据控制语言 DCL

数据定义语言

Data Definition Language

主要用于定义数据库,数据表,视图,索引等.如果以Oracle数据库中的概念来理解.这些内容都属于数据库对象,所以我们可以理解为凡是对数据库对象进行增加(Create),修改(Alter),删除(Drop)的操作都是DDL

数据操作语言

Data Manipulation Language

数据操作语言主要用于对数据进行增(Insert)删(Delete)改(Update)查(Select)简称为CRUD.

后期查询的业务越来越重要,所以就把查询独立出来形成DQL(Data Query Language 数据查询语言)

当业务的复杂度提高(一条SQL搞不定)需要多条SQL才可以完成时,就产生事务的概念,把事务相关的操作独立出来形成TCL(Transaction Control Language 事务操作语言)

数据库控制语言

Data Control Language

主要体现在对用户的授权和权限收回上,使用两个关键字:Grant,Revoke

这个时候的数据库主要给实验室,高校,研究院使用.因为成本很高.随着三方面技术的发展,整体的成本在降低,就进入第四个阶段

商业数据库阶段

商业数据库阶段是把数据库彻底普及的一个关键阶段.推向民用.

甲骨文
- Oracle
  - varchar2
- MySQL
微软
- MSSQLServer
  - nchar
  - nvarchar
Sybase
- Sybase
IBM
- DB2
开源数据库
- PostgreSQL
NoSQL

面向对象数据库

主流的编程语言都是在往面向对象方向靠拢.而数据库还是关系型数据库.这样编程语言在操作数据库时就出现类型不对等的现象.所以在开发时会使用ORM.所以数据库的发展趋势是面向对象数据库.

MySQL数据库的版本介绍

MySQL数据库的版本常见的会使用几个英文单词来代表

Alpah 当前版本处于开发阶段.该阶段可能会增加大量新功能或对已有代码进行重量级修改
Beta 处于测试阶段的版本.基本功能完成.但是没有进行全面测试
Gamma 处于测试阶段.处于某个阶段测试.是一个已经测试过一段时间的阶段.比Beta要稳定些.
GA(General Availability) 是已经足够稳定.可以在开发时实际使用.有些人会把它叫做Production版本叫做生产版本.

mysql-essential-8.0.15-alpha-winx64.rar/msi

essential 代表包含了MySQL的必要内容
8.0.15是版本号

8 是主要版本号凡是8开头的版本拥有相同的文件格式

0 是发行级别

15 是发行级别下的版本号
alpah 代表这是开发中的版本
winx64 平台要求是window 64位
rar/msi代表文件的格式 msi是微软提出的文件格式.安装格式.

数据库操作

创建数据库

C:\Users\Administrator>mysql -uroot -p123456
'mysql' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

C:\Users\Administrator>

凡是提示不是内部或外部命令的都代表所使用的指令没有配置环境变量.

环境变量改变后要重新CMD

使用DDL中的CREATE来创建数据库

mysql> create database wn08;
Query OK, 1 row affected (0.00 sec)

mysql>

因为我们使用的配置文件中已经规定了数据库的编码.所以在后面不需要跟编码格式

删除数据库

删除数据库同样使用DDL中的DROP来实现

# 查看当前有哪些库 注意细节:databases.复数.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wn08               |
+--------------------+
5 rows in set (0.30 sec)

mysql>

mysql> drop database wn08;
Query OK, 0 rows affected (0.38 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

存储引擎

什么是存储引擎

存储引擎是决定了数据库以什么样的一种存储方式来存储数据.算是MySQL的特有概念.我们可以通过show关键字来查看MySQL有哪些存储引擎

mysql> show engines \G;
*************************** 1. row ***************************
      # 引擎的名称
      Engine: MEMORY
      # 当前数据库是否支持
     Support: YES
      # 对引擎的描述
     Comment: Hash based, stored in memory, useful for temporary tables
      # 表示当前引擎是否支持事务
Transactions: NO
	  # 是否支持分布式的规范.
          XA: NO
      # 事务中的保存点
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

关于存储引擎所需要关注点不多就以下几个

是否支持事务
是否支持索引

因为现在对于数据库的定位越来越简单,就是定位用来存储数据的.目前常用的存储引擎是下面几个

InnoDB 也是默认存储引擎做事务
MyISAM 做全文索引(至少MySQL5.0)

数据表操作

切换库

也可以叫做使用库

想要建表一定是位于某个库下的.登陆后默认位于系统库下.往往是msyql或者sys库下.这是我们需要把当前库切换成我们所需要使用的.

mysql> create database wn08;
Query OK, 1 row affected (0.00 sec)

# 把当前库切换到wn08.
mysql> use wn08;
Database changed
mysql>

新建数据表

设计一张表账户表:账户表中应该有字段

编号( 流水号和业务无关纯粹的一个数字) 往往是主键 (唯一标识一条记录的.)
账户号 (111111111)
用户姓名()
余额
登陆密码(默认也是支付密码)
注册日期

新建数据表的语法是

create table 数据表的名称(字段1 数据类型 [完整性约束条件],
                    	字段2 数据类型 [完整性约束条件],
                   		...,
                   		字段n 数据类型 [完整性约束条件])

mysql> create table account(aid int primary key auto_increment,anumber char(19)
unique , aname varchar(50)  not null, abalance decimal(10,2),apwd varchar(50),ar
egistertime varchar(50) );
Query OK, 0 rows affected (0.33 sec)

mysql>

查看表的结构

mysql> desc account;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+--------
| aid           | int(11)       | NO   | PRI | NULL    | auto_increment |
| anumber       | char(19)      | YES  | UNI | NULL    |                |
| aname         | varchar(50)   | NO   |     | NULL    |                |
| abalance      | decimal(10,2) | YES  |     | NULL    |                |
| apwd          | varchar(50)   | YES  |     | NULL    |                |
| aregistertime | varchar(50)   | YES  |     | NULL    |                |
+---------------+---------------+------+-----+---------+--------
6 rows in set (0.00 sec)

mysql>

查看原始的建表语句

mysql> show create table account \G;
*************************** 1. row ***************************
       Table: account
Create Table: CREATE TABLE `account` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `anumber` char(19) DEFAULT NULL,
  `aname` varchar(50) NOT NULL,
  `abalance` decimal(10,2) DEFAULT NULL,
  `apwd` varchar(50) DEFAULT NULL,
  `aregistertime` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`aid`),
  UNIQUE KEY `anumber` (`anumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

不加\G的效果因为篇幅问题就不复制了,自行尝试@!

向表中插入数据

插入数据使用关键字insert.它的语法格式

insert into 表名 (字段1,字段2,...,字段n) values (值1,值2,...,值n);

如果要插入的是所有字段,表名后面的字段列表可以省略.它的语法格式

insert into 表名 values (值1,值2,...,值n);

注意:因为省略了字段列表,所以MySQL会采用表中字段列表的默认顺序,并且要保证给的值得数量必须和字段的数量一致(针对自动增长列)

mysql> insert into account values ('1111111111111111111','张三',8000,'123','2019
/07/27');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>

# 怎么解决这个问题.自动增长列随便给个值就好了.习惯性给0

mysql> insert into account values (0,'1111111111111111111','张三',8000,'123','20
19/07/27');
Query OK, 1 row affected (0.00 sec)

mysql>

一次性插入多条数据

在值的后面添加逗号提供下一行的数据

insert into account values (0,'1111111111111111112','李四',8000,'123','20
19/07/27'),(0,'1111111111111111113','王五',8000,'123','20
19/07/27'),(0,'1111111111111111114','赵六',8000,'123','20
19/07/27');

ERROR 1062 (23000): Duplicate entry '1111111111111111111' for key 'anumber'
mysql>

# 错误的意思是:有重复的数据插入同一个字段 :anumber.违反了唯一性约束

查看表中的数据

基本查询.不加任何子句和条件的

查询数据使用的关键字是select.它的语法结构是

select 字段1,字段2,..,字段n from 表名;

如果查询的是全部字段,字段列表可以用*代替.

select * from 表名;

mysql> select * from account;
+-----+---------------------+--------+----------+------+---------------+
| aid | anumber             | aname  | abalance | apwd | aregistertime |
+-----+---------------------+--------+----------+------+---------------+
|   1 | 1111111111111111111 | 张三   |  8000.00 | 123  | 2019/07/27    |
|   5 | 1111111111111111112 | 李四   |  8000.00 | 123  | 20
19/07/27   |
|   6 | 1111111111111111113 | 王五   |  8000.00 | 123  | 20
19/07/27   |
|   7 | 1111111111111111114 | 赵六   |  8000.00 | 123  | 20
19/07/27   |
+-----+---------------------+--------+----------+------+---------------+
4 rows in set (0.00 sec)

疑问?这里有两个关键字From和Select谁先执行.

From永远是第一个.而Select永远是最后一个.

修改表中的数据

修改使用关键字update.它的语法结构

update 表名 set 字段1 = 值 , 字段2 = 值;

mysql> update account set apwd = '456',aname = '正式';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql>

删除表中的数据

删除使用的是delete关键字.它的语法结构

delete from 表名;

mysql> delete from account;
Query OK, 4 rows affected (0.00 sec)

mysql>
# 4 rows affected 有4行受影响.代表有4条记录被删除
mysql> select * from account;
Empty set (0.00 sec)

mysql>

删除数据还有一种写法:truncate table 表名.

Truncate和Delete的区别

Truncate 速度更快
Truncate 会重置自动增长列会重新回到1开始.
Truncate会隐式的自动提交.是不可以回滚的
Truncate不会触发Delete的触发器.

各种子句

上面说的是最基础的语句也是基本语句.很少会单独的使用基本语句.往往都是要配合各种子句来使用.常用的子句有以下几个

条件子句:Where
排序子句:Order By
分组子句:Group By
筛选子句:Having 一定是在分组之后使用
去重关键字:Distinct
Limit关键字
Offsert关键字 (可以在谁用limit的时候一起使用,省略该关键字.用逗号间隔)

####　Where子句.

# where 子句
mysql> select * from account where aname = '李四';
+-----+---------------------+--------+----------+------+---------------+
| aid | anumber             | aname  | abalance | apwd | aregistertime |
+-----+---------------------+--------+----------+------+---------------+
|   1 | 1111111111111111112 | 李四   |  8000.00 | 123  | 20
19/07/27   |
+-----+---------------------+--------+----------+------+---------------+
1 row in set (0.00 sec)

# 执行顺序:From -> Where -> Select

mysql> select * from account where 1 = 1;
+-----+---------------------+--------+----------+------+---------------+
| aid | anumber             | aname  | abalance | apwd | aregistertime |
+-----+---------------------+--------+----------+------+---------------+
|   1 | 1111111111111111112 | 李四   |  8000.00 | 123  | 20
19/07/27   |
|   2 | 1111111111111111113 | 王五   |  8000.00 | 123  | 20
19/07/27   |
|   3 | 1111111111111111114 | 赵六   |  8000.00 | 123  | 20
19/07/27   |
+-----+---------------------+--------+----------+------+---------------+
3 rows in set (0.00 sec)

mysql> select * from account where 1 <> 1;
Empty set (0.00 sec)

mysql>

Where的执行可以这么来理解:首先From拿到数据源.从中取出第一条记录到Where中进行条件判断,如果Where的结果为True,则代表该记录满足条件要交给Select.依次类推,把From拿到的所有数据遍历一次.

Order By子句

# Order By的语法结构
select 字段列表 from 数据源 [where 条件] order by 要排序的字段 [asc][desc]

ASC是默认值为升序.即不写的情况下就是升序.DESC为降序.想要降序必须提供DESC.

mysql> select aid,aname,abalance,aage from account;
+-----+--------+----------+------+
| aid | aname  | abalance | aage |
+-----+--------+----------+------+
|   1 | 李四   |  7000.00 |   29 |
|   2 | 王五   |  6000.00 |   30 |
|   3 | 赵六   |  6000.00 |   28 |
+-----+--------+----------+------+
3 rows in set (0.00 sec)

mysql>

现在需求按照存款余额升序排列

mysql> select aid,aname,abalance,aage from account order by abalance;
+-----+--------+----------+------+
| aid | aname  | abalance | aage |
+-----+--------+----------+------+
|   2 | 王五   |  6000.00 |   30 |
|   3 | 赵六   |  6000.00 |   28 |
|   1 | 李四   |  7000.00 |   29 |
+-----+--------+----------+------+
3 rows in set (0.00 sec)

mysql> select aid,aname,abalance,aage from account order by abalance asc;
+-----+--------+----------+------+
| aid | aname  | abalance | aage |
+-----+--------+----------+------+
|   2 | 王五   |  6000.00 |   30 |
|   3 | 赵六   |  6000.00 |   28 |
|   1 | 李四   |  7000.00 |   29 |
+-----+--------+----------+------+
3 rows in set (0.00 sec)

mysql> select aid,aname,abalance,aage from account order by abalance desc;
+-----+--------+----------+------+
| aid | aname  | abalance | aage |
+-----+--------+----------+------+
|   1 | 李四   |  7000.00 |   29 |
|   2 | 王五   |  6000.00 |   30 |
|   3 | 赵六   |  6000.00 |   28 |
+-----+--------+----------+------+
3 rows in set (0.00 sec)

mysql>

现在更改排序条件.按照余额升序排列,当余额一样时按照年龄升序排序.

mysql> select aid,aname,abalance,aage from account order by abalance asc , aage
asc;
+-----+--------+----------+------+
| aid | aname  | abalance | aage |
+-----+--------+----------+------+
|   3 | 赵六   |  6000.00 |   28 |
|   2 | 王五   |  6000.00 |   30 |
|   1 | 李四   |  7000.00 |   29 |
+-----+--------+----------+------+
3 rows in set (0.00 sec)

mysql>

Group By子句

分组往往是根据某个字段作为分组条件,比如性别.那就是以性别的取值分为若干组.

mysql> select * from sc;
+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 01   | 02   |  90.0 |
| 01   | 03   |  99.0 |
| 02   | 01   |  70.0 |
| 02   | 02   |  60.0 |
| 02   | 03   |  80.0 |
| 03   | 01   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 03   |  80.0 |
| 04   | 01   |  50.0 |
| 04   | 02   |  30.0 |
| 04   | 03   |  20.0 |
| 05   | 01   |  76.0 |
| 05   | 02   |  87.0 |
| 06   | 01   |  31.0 |
| 06   | 03   |  34.0 |
| 07   | 02   |  89.0 |
| 07   | 03   |  98.0 |
+------+------+-------+
18 rows in set (0.00 sec)

mysql>

比如上面的数据想知道不同科目的平均分.那我们就需要以科目来进行分组.求每个科目的平均分.

mysql> select * from sc group by cid;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and c
ontains nonaggregated column 'wn08.sc.SId' which is not functionally dependent o
n columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group
_by
mysql>

想要使用分组有两点需要注意

select的字段列表必须出现在分组条件中.
有字段不在分组条件中,就必须在聚合函数中.

select cid as '课程编号' , AVG(score) as '平均分' from sc group by cid;

聚合函数

所谓的聚合函数是指统计学上的5个常用函数

求最大值
求最小值
求和
求平均值
汇总(求数量)

Having语句

筛选语句

完全基于分组后的结果,所以Having是必须用在Group By之后.比如上面的查询我们得到了三门科目的平均分.现在想追加一个需求:得到哪些平均分在70分以上的科目.所以需要对分组后的结果进行再次筛选.这里是不可以用Where…它的语法格式

select 字段1,聚合函数(字段2) from 表 where 条件 gruop by 字段1 having 条件.

这个语句的执行顺序

From 先获取数据源
利用Where 过滤From所获取的数据源
Group By 对Where过滤后的数据进行分组
Having 对分组后的结果再次进行筛选.

select cid as '课程编号' , AVG(score) as '平均分' 
from sc 
group by cid
HAVING avg(score) > 70;

Limit子句

Limit关键字是MySQL所特有的.Oracle使用的是RowNumber.它的作用是进行限制输出.比如有10条数据,输出时是要前三个.

# 获取课程编号为1的所有学生成绩
select * from sc where cid = 1;

# 追加需求
select * from sc where cid = 1 order by score desc;

# 追加需求. 只要前三名.
select * from sc where cid = 1 order by score desc limit 3

# 追加需求. 求下一组前三名  offset 对已有的数据偏移(移动)多少条记录
select * from sc where cid = 1 order by score desc limit 3 offset 3;

备注

数据源

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');


create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(‘01’ , ‘02’ , 90);
insert into SC values(‘01’ , ‘03’ , 99);
insert into SC values(‘02’ , ‘01’ , 70);
insert into SC values(‘02’ , ‘02’ , 60);
insert into SC values(‘02’ , ‘03’ , 80);
insert into SC values(‘03’ , ‘01’ , 80);
insert into SC values(‘03’ , ‘02’ , 80);
insert into SC values(‘03’ , ‘03’ , 80);
insert into SC values(‘04’ , ‘01’ , 50);
insert into SC values(‘04’ , ‘02’ , 30);
insert into SC values(‘04’ , ‘03’ , 20);
insert into SC values(‘05’ , ‘01’ , 76);
insert into SC values(‘05’ , ‘02’ , 87);
insert into SC values(‘06’ , ‘01’ , 31);
insert into SC values(‘06’ , ‘03’ , 34);
insert into SC values(‘07’ , ‘02’ , 89);
insert into SC values(‘07’ , ‘03’ , 98);

搬砖柯

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL小结

文章目录MySQL检查本机是否安装过MySQL安装数据库提供解压版安装所需要的配置文件使用指令来完成数据库MySQL的安装初始化数据库安装数据库服务!启动MySQL服务登陆MySQL数据库修改登陆密码重新登陆测试数据库的基本概念数据管理阶段关系型数据库阶段结构化查询语句阶段数据定义语言数据操作语言数据库控制语言商业数据库阶段面向对象数据库MySQL数据库的版本介绍数据库操作创建数据库删除数据库存储...
复制链接

扫一扫