MySql-2019-4-21-复习
数据库对象:存储,管理和使用数据的不同结构形式,如:表、视图、存储过程、函数、触发器、事件、索引等。
数据库:存储数据库对象的容器。
数据库分两种:
-
- 系统数据库(系统自带的数据库):不能修改
information_schema:存储数据库对象信息,如:用户表信息,列信息,权限,字符,分区等信息。
performance_schema:存储数据库服务器性能参数信息。
mysql:存储数据库用户权限信息。
test:任何用户都可以使用的测试数据库。
- 系统数据库(系统自带的数据库):不能修改
-
- 用户数据库(用户自定义的数据库):一般的,一个项目一个用户数据库。
常用的操作命令:
- 查看数据库服务器存在哪些数据库:
SHOW DATABASE;
- 使用指定的数据库:
USE database_name;
- 查看指定的数据库中有哪些数据表:
SHOW TABLES;
- 创建指定名称的数据库:
CREATE DATABASE database_name;
- 删除数据库:
DROP DATABASE database_name;
MySQL的存储引擎:
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供不同的功能和能力。
通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。
-
MyISAM:拥有较高的插入,查询速度,但不支持事务,不支持外键。
-
InnoDB:支持事务,支持外键,支持行级锁定,性能较低。
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但对比MyISAM,处理效率差,且会占用更多的磁盘空间以保留数据和索引。
最常用的整数类型:
MySQL列类型(Java数据类型)
-
INT/INTEGER(int/Integer)
-
BIGINT(long/Long)
MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展,这样当从数据库检索一个值时,可以把这个值加长到指定的长度。
例如,指定一个字段的类型为 INT(6),就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。
需要注意的是,使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。一般不用指定位宽。
age int(2),并不是代表age最多存储99,查询age值得时候使用两个0来占位.
-
FLOAT[(s,p)]
-
DOUBLE[(s,p)]
小数类型,可存放实型和整型 ,精度(p)和范围(s)。
money double(5,2): 整数和小数一共占5位.其中小数占2位,最大值:999.99,最小-999.99.
都不够精确。
定点数据类型: DECIMAL,高精度类型,金额货币优先选择。
MySQL列类型(Java数据类型)
-
FLOAT(float/Float)
-
DOUBLE(double/Double)
-
DECIMAL(BigDecimal)
char(size) 定长字符,0 - 255字节,size指N个字符数,若插入字符数超过设定长度,会被截取并警告。
varchar(size) 变长字符,0 - 255字节,从MySQL5开始支持65535个字节,若插入字符数超过设定长度,会被截取并警告。
一般存储大量的字符串,比如文章的纯文本,可以选用TEXT系列类型。
注意:在MySQL中,字符使用单引号引起来。 相当于Java中字符串(String,StringBuilder/StringBuffer);
日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
注意:在MySQL中,日期时间值使用单引号引起来。 相当于Java中Date,Calender。
BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB:
存放图形、声音和影像,二进制对象,0-4GB。
但是,在开发中,我们一般存储二进制文件保存路径的路径存储在数据库中。
BIT:我们一般存储0或1,存储是Java中的boolean/Boolean类型的值。
表操作
-
先进入某一个数据库.
-
输入建表的命令:
CREATE TABLE 表名(
列名1 列的类型 [约束],
列名2 列的类型 [约束],
....
列名N 列的类型 约束
);
注意:最后一行没有逗号
若在建表中使用到了数据库的关键字.
比如新建一张订单表:(order),但是order是数据库中的关键字(排序使用).
表名:t_order,若费用使用order这个单词.此时使用反引号()括起来,
order`.
一般,起表名为:t_名字。
创建一张学生信息表,记录学生的id,name,age.
CREATE TABLE `t_student`
(
`id ` bigint,
`name ` varchar(20),
` age ` int
);
查看表结构:
- DESC table_name;
查看表的详细定义(显示表的定义SQL语句):
- SHOW CREATE TABLE table_name;
删除表:
- DROP TABLE table_name;
表的约束(针对于某一列):
-
非空约束:
NOT NULL
,不允许某列的内容为空。 -
设置列的默认值:
DEFAULT
。 -
唯一约束:
UNIQUE
,在该表中,该列的内容必须唯一。 -
主键约束:
PRIMARY KEY
, 非空且唯一。 -
主键自增长:
AUTO_INCREMENT
,从1开始,步长为1。 -
外键约束:
FOREIGN KEY
,A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键)-明天讲解。
主键设计,唯一标识某一行数据的:
-
单字段主键,单列作为主键,建议使用。
复合主键,使用多列充当主键,不建议。 -
主键分为两种:
- 自然主键:使用有业务含义的列作为主键(不推荐使用),比如身份证号码;
- 代理主键:使用没有业务含义的列作为主键(推荐使用);
简单查询
语法:
SELECT {*, column [alias],...}
FROM table_name;
SELECT <select_list>
FROM table_name;
说明:
SELECT 选择查询列表
FROM 提供数据源(表、视图或其他的数据源)
*表示表中所有的列,但是 * 和创建表时的顺序一致。
可以自己调整顺序,在select后边加上要查询的列名。
- 避免重复数据-
DISTINCT
:
语法:
SELECT DISTINCK 列名,...
实现数学运算查询:
对NUMBER型数据(整数和小数)可以使用算数操作符创建表达式(+ - * /)
对DATE型数据可以使用部分算数操作符创建表达式 (+ -)
运算符优先级:
- 乘法和除法的优先级高于加法和减法
- 同级运算的顺序是从左到右
- 表达式中使用"括号"可强行改变优先级的运算顺序
设置列名的别名。
-
改变列的标题头;
-
用于表示计算结果的含义;
-
作为列的别名;
-
如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都需加单引号;
`
不建议列的别名使用单引号,因为一旦使用,该列就不能再排序了.
设置显示格式查询:
- 为方便用户浏览查询的结果数据,有时需要设置显示格式,可以使用CONCAT函数来连接字符串。
SELECT CONCAT(name,'前后拼接',age) AS person FROM t_person;
过滤查询
使用WHERE子句限定返回的记录:
语法:
SELECT <selectList>
FROM table_name
WHERE 条件1 AND/OR 条件2;
注意:WHERE子句在 FROM 子句后
SQL的各个子句执行先后顺序:
-
FROM 子句: 确定了从哪一张表中去做查询.
-
WHERE子句:从表中直接筛选出符合条件数据.
-
SELECT子句:从筛选之后的结果集中显示出某些列.
-
ORDER BY子句:对查询结果做排序操作
比较运算符:
运算符 含义
= 等于
> 大于
>= 大于或等于
< 小于
<= 小于或等于
!=(<>) 不等于
逻辑运算符:
运算符 含义
AND(&&) 如果组合的条件都是TRUE,返回TRUE
OR(||) 如果组合的条件之一是TRUE,返回TRUE
NOT(!) 如果下面的条件是FALSE,返回TRUE`
优先级 运算符
1 所有比较运算符
2 NOT
3 AND
4 OR
如果有多个查询条件,尽量把过滤最多的条件放在 WHERE之后,提高性能.
范围查询-BETWEEN AND:
使用BETWEEN运算符显示某一值域范围的记录,这个操作符最常见的使用在数字类型/日期类型数据的范围上,但对于字符类型数据和日期类型数据同样可用。
- 格式:SELECT * FROM table_name WHERE 列名 BETWEEN 最小值 AND 最大值:闭区间。
使用IN运算符,判断列的值是否在指定的集合中。
- 格式:SELECT * FROM table_name WHERE 列名 IN (值1,值2....);
空值查询-IS NULL:
NULL表示某一列没有数据,也不是空字符串.
IS NULL:判断列的值是否为空。
- 格式:WHERE 列名 IS NULL;
模糊查询:
使用LIKE运算符执行通配查询,查询条件可包含文字字符或数字:
通配符表示匹配模式:
%
通配符:可表示零或多个字符任意。_
通配符:可表示一个字符。
通配符:用来实现匹配部分值得特殊字符。'_木%'
使用ORDER BY子句将查询记录进行排序:
- ASC : 升序,缺省。
- DESC : 降序。
ORDER BY 子句出现在SELECT语句的最后。
格式:
SELECT <selectList>
FROM table_name
WHERE 条件
ORDER BY 列名1 [ASC/DESC],列名2 [ASC/DESC]...;
注意:不能使用中文的别名排序。
SELECT语句执行顺序:
- 先执行FROM--->接着执行WHERE--->再执行SELECT--->最后执行ORDER BY
假分页/逻辑分页/内存分页:
- 一次性查询出所有的数据,存放在内存中(List集合),每次翻页的时候,都从内存中取出指定的条数.
特点:翻页比较快,如果数据量过大,可能造成内存溢出.
真分页/物理分页/数据库分页(推荐):
- 每次翻页都从数据库中截取指定的条数,假设每页10条数据,第一页:查询09条数据,第二页:查询1019条数据.
特点:翻页比较慢,不会造成内存溢出.
MySQL的分页设计:
int pageSize = 3;//表示每页最多显示3条数据.
分页查询结果集的SQL:
SELECT * FROM 表名 LIMIT ?,?;
第一个?:(currentPage - 1) * pageSize;
第二个?:pageSize
第一页:SELECT * FROM product
LIMIT 0, 3
第二页:SELECT * FROM product
LIMIT 3, 3
第三页:SELECT * FROM product
LIMIT 6, 3
什么是聚集函数:
- 聚集函数作用于一组数据,并对一组数据返回一个值。
COUNT:统计结果记录数
MAX: 统计计算最大值
MIN: 统计计算最小值
SUM: 统计计算求和
AVG: 统计计算平均值
多表查询
-
单表查询:从一张表中查询数据
-
多表查询:从多张表中联合查询出数据
单表查询:
SELECT <selectList>
FROM 表名
多表查询(最简单的):
SELECT <selectList>
FROM 表名A , 表名B
多表查询,如果没有连接条件,则会产生笛卡尔积:
数学中定义:假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
实际运行环境下,应避免使用全笛卡尔集。
解决方案: 在WHERE加入有效的连接条件---->等值连接
注意: 连接 n张表,至少需要 n-1个连接条件。
外键约束
主键约束(PRIMARY KEY): 约束在当前表中,指定列的值非空且唯一.
外键约束(FOREIGN KEY): A表中的外键列的值必须引用于于B表中的某主键列.
为了保证数据的合理性,我们需要建立外键约束关系.
规定: employee表中的deptno列的值,应该来源于department表中的主键列deptno, 我们就把employee表中的deptno列称之为外键列.
注意:在MySQL中,InnoDB支持事务和外键.修改表的存储引擎为InnDB:
ALTER TABLE 表名 ENGINE='InnoDB';
InnoDB存储引擎,支持外键又支持事务.
多表查询
-
内连接查询:是相对于外连接。
-
内连接分为:隐式内连接、显示内连接,其查询效果相同。
隐式内连接:
SELECT <selectList>
FROM A ,B WHERE A.列 = B.列
显示内连接(推荐写法):.
SELECT <selectList>
FROM A [INNER] JOIN B ON A.列 = B.列
使用表名前缀在多个表中区分相同的列。
在不同表中具有相同列名的列可以用表的别名加以区分。
使用别名可以简化查询。
使用表名前缀可以提高执行效率。
如果使用了表的别名,则不能再使用表的真名。
作者: AntzUhl
首发地址博客园:http://www.cnblogs.com/LexMoon/
代码均可在Github上找到(求Star) : Github
个人博客 : http://antzuhl.cn/
公众号 |
![]() |
赞助
支付宝 |
微信 |
![]() |
![]() |