mysql 安装请点击
mysql下载速度慢请点击
可视化工具请点击(不想用命令行的)
mysql和java集成:jdbc
mysql 索引的创建以及含义
mysql一般函数的使用(需要一定的sql基础)
mysql 高级 union、case when、leftjoin、关联删除、行转列、having和where、count和sum对于null、批量修改不同条件数据、create和select使用、insert和select使用(需要一定sql基础)
mybatis的使用
mysql可视化工具 如何查看sql’的执行效率
mysql的事务(需要一定的sql基础)
mysql建数据库如何给用户权限的问题
索引失效问题请点击(需要一定的sql基础)
数据库引擎了解(需要有数据结构基础)
nosql数据库(需要一定的sql基础)
Mysql数据库以及sql语言:基础版
- 一 、数据库的概述
- 1. 什么是数据库?
- 2. 常见的数据库
- 二 、mysql 中的数据类型
- 三、sql
- 1. DDL语句
- 2. DML语句
- 3. DQL语句
- Select 关键字:
- Distinct 关键字
- 运算:
- As 关键字
- 条件查询
- 模糊查询 Like关键字
- 排序 Order by 关键字
- 聚合函数
- 分组
- 分页
- 四 、 MYSQL的case when
- Case具有两种格式。简单Case函数和Case搜索函数。
- 简单Case函数
- Case搜索函数
- Case When的行转列
一 、数据库的概述
数据库(DataBase,DB):指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
注意:通常情况下,经常会用数据库来表示他们使用的数据库软件 ,这经常会引起混淆,确切的说,数据库软件应该为数据库管理系统,数据库是通过数据库管理系统创建和操作的。
1. 什么是数据库?
简而言之存储数据的仓库;
数据库是一个文件系统;通过标准的sql语句获取数据;
2. 常见的数据库
数据库 | 介绍 |
---|---|
MySQL | 免费的数据库系统。被广泛用于中小型应用系统。体积小、速度快、总体拥有成本低,开放源代码。2008年被SUN收购,2009年SUN被Oracle收购。 |
Oracle | 目前比较成功的关系型数据库管理系统。运行稳定、功能齐全、性能超群、技术领先。主要应用在大型的企业数据库领域。 |
DB2 | IBM的产品 |
SQL Server | Microsoft的产品。软件界面友好、易学易用,在操作性和交互性方面独树一帜。 |
SQL优点:
不是某个特定数据库供应商专有的语言。几乎所有重要的数据库管理系统都支持SQL。
简单易学,该语言的语句都是由描述性很强的英语单词组成,且这些单词的数目不多。
高度非过程化,即用SQL操作数据库,只需指出“做什么”,无须指明“怎么做”,存取路径的选择和操作的执行由DBMS自动完成。
Sql语言的大致功能:
sql类型 | 功能 |
---|---|
DDL | 数据定义语言,用来定义数据库对象:库、表、列等;创建 表、库,删除表、库,更改表结构;主要操作关键字:create,drop,alter; |
DML | 数据操作语言,用来操作数据库表中的记录(数据);操作表中数据的 增、删、更改; 主要操作关键字:insert,update,delete; |
DQL | 数据查询语言,用来查询记录(数据); 查询 ;主要操作关键字:select; |
DCL | 数据控制语言,用来定义访问权限和安全级别 ;主要操作关键字:grant,if |
二 、mysql 中的数据类型
JAVA中 | MYSQL中 |
---|---|
INT | int |
Float | float(5,2) 长度是5小数点后两位 |
Double | double |
Char/String | char和varchar oracle里用 varchar2 |
tips:面试会问
Char和varchar都是存储字符或字符串的
区别:
字段类型 | 解释 |
---|---|
char | 是固定长度字符串 char(8) 存储hello会在后面加三个空格占位 |
varchar | 长度是可变的 如:varchar(20) 存储 hello 只占五个位置 |
mysql里面的时间 | 表示含义 |
---|---|
date | 年月日 |
datatime | 年月日时分秒 |
timestamp | 年月日时分秒 |
- date、datetime如果没有没有添加内容的话 存储在数据库中就是null。
- 而timestamp如果没有没有添加内容的话 存储在数据库中就是存储数据库当前时间。
Ps:varchar和日期类型的使用要用单引号括起来:
Ps:
写在前面的话:
文中所说的列和字段同义
drop、delete、truncate 表示删除
alter、modify、update 表示修改
insert、add 表示增加
select 表示查
三、sql
sql 菜鸟教程
SQL 指结构化查询语言,全称是 Structured Query Language。
SQL 让您可以访问和处理数据库,包括数据插入、查询、更新和删除。
SQL 在1986年成为 ANSI(American National Standards Institute
美国国家标准化组织)的一项标准,在 1987 年成为国际标准化组织(ISO)标准。
- 能干什么:
SQL 访问和处理数据系统中的数据,这类数据库包括:MySQL、SQL Server、Access、Oracle、Sybase、DB2 等等。 - 但是:
虽然 SQL 是一门 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机语言,但是仍然存在着多种不同版本的 SQL 语言。
然而,为了与 ANSI 标准相兼容,它们必须以相似的方式共同地来支持一些主要的命令(比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等)。
例如:ifnull :用mysql判断null,nvl 用于oracel 判断null
1. DDL语句
DDL的含义:
Data Definition Language的缩写,数据定义语句,主要是用在定义或改变表的结构,创建视图、索引聚簇
DDL语句:1、数据库操作部分 | 解释 |
---|---|
1、格式:create database 库名; | 创建数据库; |
2、格式:Show databases ; | 展示所有的数据库; |
3、格式:Drop database 库名; | 删除对应的库; |
4、格式:use 库名; | 选择要使用的数据库; |
5、格式:Select databases(); | 查看正在使用的数据库; |
6、格式:Create table 表名 (字段名 字段类型,字段名 字段类型………字段名 字段类型) | 创建表 |
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
//2、展示当前所有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
//3、使用db1数据库
mysql> use database db1;
ERROR 1049 (42000): Unknown database 'database'
mysql> use db1;
Database changed
//4、选择当前数据库并展示
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
表的一些介绍:
- 一个竖列表示的是一个字段 ,
- 一个横行表示的是一条记录,记录包含每个字段的内容
- 创建表的时候需要先使用库 use关键字指定使用那个库建表
字段1 | 字段2 | 字段3 | 字段4 | 字段5 | 字段6 | 字段7 |
---|---|---|---|---|---|---|
1-值1 | 1-值2 | 1-值3 | 1-值4 | 1-值5 | 1-值6 | 1-值7 |
2-值1 | 2-值2 | 2-值3 | 2-值4 | 2-值5 | 2-值6 | 2-值7 |
DDL语句:2、表操作部分 | 解释 | |||||
– | – | |||||
6、格式:Create table 表名 (字段名 字段类型,字段名 字段类型………字段名 字段类型) | 创建表 | |||||
7、格式:show tables; | 列出所有表 | |||||
8、格式:drop table 表名字; | 删除表 | |||||
9、格式:desc 表名; | /展示表格属性,字段名字和字段类型、是否允许为空、默认值多少、以及长度等等 | |||||
10、格式:rename table 原名字to新名字; | 重命名表格 |
//5、创建表格;
mysql> create table user(
id int,
username varchar(20),
password varchar(20),
age int,
birthday date);//后面可以增加列
Query OK, 0 rows affected (0.01 sec)
//6、show 表格
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user |
+---------------+
1 row in set (0.00 sec)
//7、删除表;
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)
//再把表创建出来
mysql> create table user(id int,username varchar(20),password varchar(20),age int,
birthday date);
Query OK, 0 rows affected (0.01 sec)
//8、展示表格属性,字段名字和字段类型以及长度等等
解释:image:为加入一列之后,参考后面的给表添加字段 参照第11条
Field | 表示字段名称 |
---|---|
Type | 表示字段类型 |
Null | 是否允许为空 |
Key | 索引类型 |
Default | 默认值是多少 |
Extra |
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image | varchar(20) | YES | | NULL | |//横着的为字段
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
//9、重命名表格
mysql> rename table user to user2;
Query OK, 0 rows affected (0.01 sec)
//9.1重命名表格后、展示表格属性
mysql> desc user;
ERROR 1146 (42S02): Table 'db1.user' doesn't exist
//9.2展示表格属性
mysql> desc user2;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
DDL语句:3、字段操作部分 | 解释 |
---|---|
11、格式:Alter table 表名 add 列名(字段名)类型 约束; | 增加列ps:列表示的字段 |
12、格式:alter table 表名 drop 列名; | 删除列 |
13、格式:Alter table 表名 modify 字段名字 新类型(新长度) 新约束 | 修改字段信息 类型、长度、约束 |
14、格式:alter table 表名 change 字段名字 新名字 新类型 约束 | 更改列名字和类型 |
展示当前的使用的数据库 :
格式:
Select database();
//11、增加列;ps:列表示的字段
mysql> alter table user add image varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
//11.1、展示表格属性,字段名字和字段类型以及长度等等
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image | varchar(20) | YES | | NULL | |//横着的为字段
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
//13、更改某个字段的长度
不仅仅是长度
可以将 alter table user modify image varchar(100);改为
alter table user modify image int default not null after username;
表示将image 改为int类型 (长度为默认,没有写表示默认) 、不为null、并且再username字段之后
mysql> alter table user modify image varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
//13.1、展示表格属性
mysql> desc user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
//重命名表格
mysql> rename table user to user2;
Query OK, 0 rows affected (0.01 sec)
//展示表格属性
mysql> desc user;
ERROR 1146 (42S02): Table 'db1.user' doesn't exist
//展示表格属性
mysql> desc user2;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
2. DML语句
DML:
Data Manipulation Language的缩写,数据库操纵语句,主要是UPDATE、INSERT、DELETE
添加一条记录;横向
INSERT 关键字:
DML语句:1、插入部分 | 解释 |
---|---|
1.1、格式:Insert into 表名 values(’内容1’,值2,值2,……) | 给全部字段添加内容; |
1.2、格式:Insert into 表名(字段名字1,字段名字2…) values (值1,值2….). | 给指定字段添加内容; |
注意事项:
- 插入的数据应与字段的数据类型相同。
- 数据的大小应在列的规定范围内,例如:不能将一个长度为80的字符串加入到长度为40的列中。
- 在values中列出的数据位置必须与被加入的列的排列位置相对应。
- 字符和日期型数据应包含在单引号中。
- 插入空值,不指定或insert into table value(null) 当一个字段设置成自增长就可以使用null
直接给数据库中插入中文,会报错;
解决方案:
查看数据库中的字符集相关参数- show variables like ‘%character%’
只要更改客户端的编码格式为 gbk就可以解决乱码问题
需要在mysql里的配置文件中改 安装路径下 找 my.ini文件
更改 客户端编码格式为 gbk
更改添加记录后的字段;竖向
UPDATE关键字:
DML语句:2、修改部分 | 解释 |
---|---|
2.1、格式:update 表名 set 字段名字 =‘值’,字段名字=值 | 更改该字段(列)的所有值,可以修改多个字段(列)的所有值 |
2.2、Update 表名 set 字段名1 = 值1,字段名2 = 值2,… where 要改的字段名 = 值; | 更改具体记录中的一个或者几个字段 |
2.3、格式: Update 表名 set 字段名 = 它的值±*/某个数 where 目标字段 = 值; | 更改和计算同时 |
2.1、例子:
表示将所有的password改为 222
UPDATE users SET PASSWORD ='222';
2.2、例子1:
更改某个人的password 需要加条件
使用where
表示将id等于2的password改为333
UPDATE users SET PASSWORD ='333' WHERE id =2;
2.2、例子2:
更改多个字段的值
把username是张三的人的信息中pwd改成444,id改成4
UPDATE users SET PASSWORD='444',id =4 WHERE username='张三';
2.3、例子:
给张三加10岁
UPDATE users SET age= age+10 WHERE username='张三';
删除表格 或者删除记录;横向
DELETE 关键字:
DML语句:3、删除部分 | 解释 |
---|---|
3.1、格式:Delete from 表名; | 删除整个表格数据,保留了下了一个空的表格 |
3.2、 Delete from 表名 where 字段名 = 值; | 删除指定条件下的记录,加了条件可以使得只删除固定的一个或多个记录;记录不是字段 |
truncate
格式:
Truncate 表名字;
TRUNCATE 删除整个表 然后再给我创建一个格式一摸一样的表;
truncate 是DDL语句 只能作用于表,即 只有“Truncate 表名字;”这种格式。truncate同样是删除数据保留空表,效率上比”delete from 表名;“效率高,但无法回滚。警惕使用
select * from 表名;表示查看该表的所有数据。
mysql> select * from telnum;
Empty set (0.00 sec)
mysql> insert into telnum values ('王凡',23,'13118459625');
Query OK, 1 row affected (0.00 sec)
mysql> insert into telnum (name,age)values ('老李',23);
Query OK, 1 row affected (0.00 sec)
mysql> select *from telnum;
+------+------+-------------+
| name | age | telephone |
+------+------+-------------+//一个|符号对应一个+符号;
| 王凡 | 23 | 13118459625 |
| 老李 | 23 | NULL |
+------+------+-------------+
2 rows in set (0.00 sec)
//更改name 在name= 老李的位置;
//如果没有 where语句则更改所有的字段
2.2、更改部分数据:例子
mysql> update telnum set name = '老王' where name = '老李';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//展示表中内容;
mysql> select *from telnum;
+------+------+-------------+
| name | age | telephone |
+------+------+-------------+
| 王凡 | 23 | 13118459625 |
| 老王 | 23 | NULL |
+------+------+-------------+
2 rows in set (0.00 sec)
//2.1、例子:更改多个字段:在name=老王的位置;
mysql> update telnum set name = '赵四',age = age+18 where name = '老王';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select *from telnum;
+------+------+-------------+
| name | age | telephone |
+------+------+-------------+
| 赵四 | 41 | 13118459625 |
| 赵四 | 41 | NULL |
+------+------+-------------+
2 rows in set (0.00 sec)
3. DQL语句
DQL:
Data Query Language的缩写,数据查询语句,基本结构是SELECT子句、FROM子句、WHERE子句组成的查询块。
查询数据、统计数据、按条件查询数据等
Select 关键字:
DQL语句 | 解释 |
---|---|
1. 格式:select * from 表名 ; | 查询表所有数据,*:表示所有字段 |
2.格式:select 字段1,字段2,字段3… from 表名 ; | 表示查询部分字段 |
创建 exam表:
CREATE TABLE exam(
id INT , NAME VARCHAR(20) , english INT , Chinese INT , math INT);
添加测试数据:
INSERT INTO exam VALUES(1,'张三',80,90,30);
INSERT INTO exam VALUES(2,'李四',89,33,58);
INSERT INTO exam VALUES(3,'王五',85,59,30);
INSERT INTO exam VALUES(4,'赵四',86,77,90);
INSERT INTO exam VALUES(5,'田七',22,33,44);
INSERT INTO exam VALUES(6,'李老四',99,0,40);
INSERT INTO exam VALUES(7,'王老五',38,66,70);
INSERT INTO exam VALUES(8,'李二狗',87,30,NULL);
查询表中所有数据的记录 select 要显示的字段
-- * 代表显示所有字段
-- “-- ”:杠杠空格:表示这是一个注释 还可以使用 /**/ 来注释
SELECT * FROM exam;
-- 只返回exam 中id、name 字段数据,且按照id name的顺序。如果没有id字段sql会报错
SELECT id,NAME FROM exam;
-- 查询学生的name 和英语成绩
SELECT NAME,english FROM exam;
Distinct 关键字
去重:
//去掉重复的部分;只显示一个;
Select distinct 字段名 from 表名
查询数学成绩 (不显示重复的值)-- distinct 关键字
SELECT DISTINCT math FROM exam;
//去掉两个重复的部分;
Select distinct 字段名1,字段名2 from表名;
如果是*号的话就是去掉相同的记录,保留一个
运算:
一个字段运算;
Select 字段1,字段2+-*/等等… from 表名
加法运算;
//如果表中的一条记录的某个字段为null 不能直接相加,加的结果为null
Select 字段1,字段x+字段y+字段z+… from表名;
Ifnull(有可能出现null的字段,替换内容);
Select 字段1,字段x+字段y+ifnull(字段z,替换内容)+… from 表名;
例:给英语成绩 +10分
SELECT NAME,english+10 FROM exam;
例:求总分
SELECT NAME,english+math+chinese FROM exam;
如果数据库表中的成绩是null 这个时候直接和数据相加就变成null
emp 工资 100000 奖金 null null
把null 转换成0 这个时候就可以正常运算了
ifnull(有可能出现null的字段,0);
例:将null值去掉
SELECT NAME,english+IFNULL(math,0)+chinese FROM exam;
-- 含义为将math 为null的当作0参与计算
As 关键字
//as可以省略,运算结果名字可以不加上单引号;
Select 字段1,字段x+字段y+ifnull(字段z,替换内容)+… as 运算结果名字from 表名;
SELECT NAME,english+IFNULL(math,0)+chinese AS SUM FROM exam;
SELECT NAME,english+IFNULL(math,0)+chinese AS 总分 FROM exam;
as 关键字可以省略
SELECT NAME,english+IFNULL(math,0)+chinese SUM FROM exam;
SELECT NAME,english+IFNULL(math,0)+chinese 总分 FROM exam;
条件查询
全是查询这张表中的数据
以后如果做登录 查询表 带条件 where+条件
第一类 等值查询条件 =
第二类不等值查询条件
– >< >= <= != <> between… and… and not or in()
-- 查询 id 是3的学生的所有信息
SELECT * FROM exam WHERE id = 3;
-- 查询 id 是3的学生姓名
SELECT NAME FROM exam WHERE id = 3;
-- 查询 语文成绩>50分的学生信息
SELECT * FROM exam WHERE chinese>50;
-- 或者 成绩<=50 然后取反 not
SELECT * FROM exam WHERE NOT chinese<=50;
-- 查询 数学成绩>50分的学生信息 -- null
SELECT * FROM exam WHERE math<=0;
-- 这种查询都查不到null 我想查询null
-- is null
-- 查询 数学成绩是null 的人
SELECT * FROM exam WHERE math IS NULL;
-- 查询 数学成绩不是null 的人
SELECT * FROM exam WHERE NOT math IS NULL;
Not放在where后面
查询null
用:Where 字段 Is null
查询非null
用:Where 字段Is not null
用:Where not 字段 is null
Between and
-- 包含边界 左闭右闭。比如查询19年到21年的使用 year between 2019 to 2021
In (值1,值2…);
-- 只取括号里的值;比如查询 学习了 高数、模电、数电任意一门课程的人 使用 in ("高数","模电","数电")
例子:
-- 1 where math is not null 是查询不为null的格式
-- 2 where not math is null 查询为null 然后取反
这两者是有区别的
-- 查询数学成绩不等于 30 的人
SELECT * FROM exam WHERE math <> 30;
SELECT * FROM exam WHERE math != 30;
-- 查询 姓名是张三并且英语成绩是80分的人 and 并且
SELECT * FROM exam WHERE NAME = '张三' AND english = 80;
-- 查询数学成绩是30或英语成绩是80的人 or 或者
SELECT * FROM exam WHERE math=30 OR english=89;
-- 查询数学成绩是30或英语成绩是80的人 or 或者
SELECT * FROM exam WHERE math=30 AND english=89;
-- between.. and 包含边界
-- 查询数学成绩30~80之间的人
SELECT * FROM exam WHERE math BETWEEN 30 AND 80;
-- 查询数学成绩30~70之间的人 查看边界
SELECT * FROM exam WHERE math BETWEEN 30 AND 70;
-- in(20,30) 只取括号里的值
-- 查询数学成绩是30分的人
SELECT * FROM exam WHERE math IN(30);
SELECT * FROM exam WHERE math = 30;
-- 查询数学成绩是30分或 40或70或44 的人
SELECT * FROM exam WHERE math IN(30,40,44,70);
SELECT * FROM exam WHERE math = 30 OR math=44…;
模糊查询 Like关键字
"%":查询0-n个
"_":查询一个。下划线查询一个,写几个下划线就表示有几个模糊信息
//模糊信息%加不加分号 主要看字段类型;
格式:
Where 字段 like "模糊信息%";
Where 字段 like "%模糊信息%";
Where 字段 like "%模糊信息";
Where 字段 like "模糊信息%";
Where 字段 like "_模糊信息";
Where 字段 like "__模糊信息";
例子:
-- 姓张的人 这种查询只要包含关键字就可以被展示
-- 关键字 like % 匹配0~n个字符 _匹配一个字符
-- 查询 姓李的人的信息
SELECT * FROM exam WHERE NAME LIKE '李%';
-- 查询名字以四结尾的人的信息
SELECT * FROM exam WHERE NAME LIKE '%四';
-- 查询名字以四结尾的两个字的名字的人的信息
SELECT * FROM exam WHERE NAME LIKE '_四';
-- 查询包含李的人的信息
SELECT * FROM exam WHERE NAME LIKE '%李%';
-- 查询以第二个字为‘老’字的信息
SELECT * FROM exam WHERE NAME LIKE '_老%';
面试:
Sql 优化:
尽量少使用*
1. Select *from 表名 和 select 字段名 from 表名;后者效率高
2. Select *from 表名 where 字段 = 目标值;
和 select 字段名 from 表名 where 条件;
*表示的查询整张表,每次都会走完整张表;
按一个字段查询的速率更快,当它查到想要的结果就会停下
排序 Order by 关键字
排序:
排序前面都有 select *from 表名
Order by 默认升序;
Order by 字段
升序排列
Order by 字段 asc
//Null 是最小的比负数还小
降序排列
Order by 字段 desc;
两个字段排序
Order by 字段1,字段2;
-- 主要排序的字段1,字段1如果有相同的则排字段2的顺序 中文或者任意值数据库有自己的一套方案排大小
注:字段后面都可以跟升序或者降序;如 Order by 字段1 desc,字段2 asc;
Order by 永远放在最后的位置
例子:
-- 排序 升序排序(小到大) 降序排序
-- 关键字 order by 默认是升序
-- 排序 数学成绩
SELECT * FROM exam ORDER BY math ASC;
-- asc 表示升序
-- 认为 null最小
-- 排序 数学成绩降序排序 desc
SELECT * FROM exam ORDER BY math DESC;
-- 排序两个列 主前面这个排序 次 后面的
SELECT * FROM exam ORDER BY math ,chinese;
-- 使用新表演示效果
-- 创建 exam表
CREATE TABLE exam2(
id INT,
NAME VARCHAR(20),
english INT,
chinese INT,
math INT
);
-- 添加数据
INSERT INTO exam2 VALUES(1,'张三',80,90,30);
INSERT INTO exam2 VALUES(2,'李四',80,30,40);
INSERT INTO exam2 VALUES(3,'王五',80,30,30);
INSERT INTO exam2 VALUES(4,'赵四',86,40,40);
INSERT INTO exam2 VALUES(5,'赵五',86,40,50);
SELECT * FROM exam2;
-- 排序两个列 主前面这个排序 次 后面的
SELECT * FROM exam2 ORDER BY math ,chinese ;
-- 班主任是数学老师 语文老师
-- 如果两个人总分完全一样 1数学成绩高的再前面 2
-- 如果两个人总分一样 数学成绩一样 再排语文成绩
-- 1: 30 40 50
-- 2: 40 30 50
聚合函数
- 聚合;
()括号内部表示按什么查询有多少条记录
如:(*)表示按照整张表查询有多少条记录
(字段)表示按照这个字段查询,有多少个这样的字段(记录) - 计数 count
Count() 统计表中有多少个数据
//注意:count后面不能有空格
//自动过滤掉null值;
Select count(*) from 表名;//返回有多少条记录;
Select count (字段名)from 表名;//返回此字段在表中有多少个;
可以和where 和ifnull 等等组合计算; - 合计;sum
Sum()统计合计值
//注意:sum后面不能有空格
//自动过滤null值//括号里面可进行计算;
sum(字段1+字段2…)括号里面有null值的时候不会过滤,内部是加法运算
Select sum(字段名1),sum(字段名2),… from 表名;
Select sum(字段名1)±*/ from 表名; - 平均值;avg
Avg()计算平均值;括号里面放所求字段
//自动过滤null值;
//就是说若有10个数据,其中一个为null 则求得是九个的和除以九 - 最小值;min
Min() - 最大值;max
Max ()
例子:
-- 聚合函数 合计函数 类似java中的方法
-- 合计函数 过滤null值
-- count() 统计个数
-- 统计 exam表中有多少条数据
SELECT COUNT(*) FROM exam;
-- 统计 exam表中有name 多少个数据
SELECT COUNT(NAME) FROM exam; //count 也会过滤null值
SELECT * FROM exam;
-- 统计 exam表中有math成绩的人数
SELECT COUNT(math) FROM exam;
-- sum() 求和函数过滤null值
-- 求数学成绩之和
SELECT SUM(math) FROM exam;
-- 获取学生英语成绩总和 和数学成绩总和
SELECT SUM(math),SUM(english) FROM exam;
-- 下面这个会报错 因为sum()只有一个参数
SELECT SUM(math,english) FROM exam;
-- 所有成绩
SELECT SUM(math+english) FROM exam;
-- 计算总成绩
-- 方法1
SELECT SUM(math)+SUM(english)+SUM(chinese) FROM exam;
-- 1386// 上面的计算方法会过滤掉null值
-- 方法2
SELECT SUM(math+english+chinese) FROM exam;
-- 1269
-- 上面1269成绩不对,我还感觉SUM(math+english+chinese)简单
-- ifnull //下面的这个也会计算出正确结果
SELECT SUM(IFNULL(math,0)+english+chinese) FROM exam;
-- 获取姓李的学生的个数
SELECT COUNT(*) FROM exam WHERE NAME LIKE '李%';
-- avg 平均值
-- 获取语文成绩平均值
SELECT AVG(chinese) FROM exam;
//就是说若有10个数据,其中一个为null 则求得是九个的和除以九
-- 获取数学成绩平均值
SELECT AVG(math) FROM exam; -- 37.75
-- sum 过滤null /9
SELECT SUM(math)/8 FROM exam;
-- 根据自己未来的题目需求去使用
-- max()
SELECT MAX(math) FROM exam;
-- min
分组
分组显示
Group by 字段;
Select 要展示的字段1,字段2,计数,求和… from 表名 group by 按什么分组;
条件分组
Select 要展示的字段1,字段2,计数,求和… from 表名
Where 条件group by 按什么分组;
//条件不可以是聚合函数 sum
解决办法:
Select 要展示的字段1,字段2,计数,求和… from 表名
group by 按什么分组 having 条件;
having和where 的区别:
1.位置区别
2.where过滤 是在分组之前进行筛选 having 过滤是在分组后过滤
3.where 不可以接聚合函数,having可以 并且也可以接普通字段;
Select 后面的顺序;
-- 总结 关键字顺序:
SELECT (字段) FROM 表名
WHERE 条件 GROUP BY 分组字段 HAVING 过滤条件
例子:
-- 不同部门 业绩比拼
-- 分组 group by
CREATE TABLE orderitem(
id INT,
product VARCHAR(20),
price INT
);
INSERT INTO orderitem VALUES(1,'电视机',2999);
INSERT INTO orderitem VALUES(2,'电视机',2999);
INSERT INTO orderitem VALUES(3,'电视机',2999);
INSERT INTO orderitem VALUES(4,'冰箱',3000);
INSERT INTO orderitem VALUES(5,'冰箱',3000);
INSERT INTO orderitem VALUES(6,'冰箱',3000);
INSERT INTO orderitem VALUES(7,'空调',8000);
INSERT INTO orderitem VALUES(8,'洗衣机',4000);
INSERT INTO orderitem VALUES(9,'洗衣机',4000);
-- 格式
-- select 展示的列 from 表 group by 分组条件
-- 按照商品名称统计(分组) 商品个数
SELECT product,COUNT(*) FROM orderitem GROUP BY product;
//count(*)是统计的分组后的记录数目;
//按照product 分组,然后统计相同的product内部有多少条记录
-- 按照名称统计 每类商品所花费的金额 sum(price)
SELECT product ,SUM(price) FROM orderitem
GROUP BY product;
-- 按照价格分组 展示商品名称
SELECT product,price FROM orderitem GROUP BY price;
-- 展示商品总个数
SELECT product, COUNT(*) FROM orderitem GROUP BY product ;
-- count(*) 表示查询一个表中有多少行数据
-- count(price) 表示查询一个表中price这个字段里有多少行数据
SELECT * FROM orderitem;
-- 按照商品名称统计 统计每类商品花费金额大于2000的
SELECT product, price FROM orderitem WHERE price>2000 GROUP BY product;
-- 按照商品名称统计 统计每类商品花费总金额大于5000的
SELECT product, SUM(price) FROM
orderitem WHERE SUM(price)>5000
GROUP BY product;
重要:
-- 上面这个代码报错
-- where后面不能跟聚合函数
-- having 后面可以更聚合函数也可以跟普通字段
-- having 书写位置再group by 后面
-- 按照商品名称统计 统计每类商品花费总金额大于5000的
SELECT product, SUM(price) FROM
orderitem
GROUP BY product HAVING SUM(price) >5000;
HAVING he WHERE 区别:
1:位置区别 where再group BY 前面
HAVING 再 GROUP BY 后面
2:having 是对分组后的结果进行筛选
WHERE 是再分组前对数据进行过滤
3:where后面不能跟聚合函数
HAVING 后面可以更聚合函数也可以跟普通字段
/* 按照商品名称统计,统计每类商品花费的总金额在5000
以上的商品,并且按照总金额升序排序
*/
SELECT product, SUM(price) FROM orderitem
GROUP BY product
HAVING SUM(price) >5000
ORDER BY SUM(price) ASC;
分页
只适用于mysql
Limit 关键字 用来限定查询结果的起始行,以及总行数
Limit 数字一,数字二;
数字一:表示起始行,数字二表示展示的行数;
//注意起始行并有展示,而是从下一行开始展示 相当于左开右闭
-- mysql分页
-- limit 用来限定查询结果的起始行,以及总行数
-- 查询前五行的数据 第一个数字是起始行 第二个数字是展示的行数
SELECT * FROM exam LIMIT 0,5;
-- 从第5行开始查询 展示4行数据 从数数字
SELECT * FROM exam LIMIT 5,5;
-- java 中如何使用分页 123 456 789
INT page = 5 -- 当前页面
INT pagesize = 3 -- 每页展示的记录数,当前页减一乘以一页多少条数据,
SELECT * FROM exam LIMIT (page-1)*pagesize,pagesize;
四 、 MYSQL的case when
转载自
Case具有两种格式。简单Case函数和Case搜索函数。
简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
两种方式,可以实现相同的功能。
简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
比如说,下面这段SQL,你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
WHEN col_1 IN ('a') THEN '第二类'
ELSE'其他' END
Case When的行转列
CREATE TABLE Pivot
( YEAR SMALLINT,
QUARTER TINYINT,
Amount DECIMAL(2,1) )
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3) ;
INSERT INTO Pivot VALUES (1990, 4, 1.4) ;
INSERT INTO Pivot VALUES (1991, 1, 2.1) ;
INSERT INTO Pivot VALUES (1991, 2, 2.2) ;
INSERT INTO Pivot VALUES (1991, 3, 2.3) ;
INSERT INTO Pivot VALUES (1991, 4, 2.4) ;
INSERT INTO Pivot VALUES (1992, 1, 3.1) ;
INSERT INTO Pivot VALUES (1992, 2, 3.2) ;
INSERT INTO Pivot VALUES (1992, 3, 3.3) ;
INSERT INTO Pivot VALUES (1992, 4, 3.4) ;
SELECT * FROM pivot;
SELECT YEAR,
SUM(CASE QUARTER WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE QUARTER WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE QUARTER WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE QUARTER WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY YEAR ;
按照年度分类,展示每个季度的账户金额
注意:要用sum函数求和,原因:(Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略)
SELECT * FROM pivot;的结果
SELECT YEAR,
SUM(CASE QUARTER WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE QUARTER WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE QUARTER WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE QUARTER WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY YEAR ;
的结果
更多推荐
Mysql数据库以及sql语言
发布评论