Mysql数据库(一)
1.数据库和数据库对象
数据库:存储数据库对象的容器。
数据库对象:存储,管理和使用数据的不同结构形式,如:表、视图、存储过程、函数、触发器、事件等。
数据库分两种:
- 系统数据库(系统自带的数据库):不能修改
information_schema:存储数据库对象信息,如:用户表信息,列信息,权限,字符,分区等信息。
performance_schema:存储数据库服务器性能参数信息。
mysql:存储数据库用户权限信息。
test:任何用户都可以使用的测试数据库。
- 用户数据库(用户自定义的数据库):一般的,一个项目一个用户数据库。
2.数据库存储引擎
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。
MyISAM:拥有较高的插入,查询速度,但不支持事务,不支持外键。
InnoDB:支持事务,支持外键,支持行级锁定,性能较低。它提供了具有提交、回滚和崩溃恢复能力的事务安全。但对比MyISAM,处理效率差,且会占用更多 的磁盘空间以保留数据和索引
3.Mysql数据类型----Java数据类型
常用对照
JAVA | Java备注 | MYSQL | MYSQL备注 |
---|---|---|---|
Int | 短整型 | int | 短整型 |
long | 长整型 | bigint | 长整型 |
float | 单精度 | float | 单精度 |
double | 双精度 | double | 双精度 |
bigdecimal[类] | 金融数据 | decimal | 金融数据 |
String | 字符串 | char | 不可变字符串 |
varchar | 可变字符串 | ||
Boolean | 布尔值 | Bit | 取值0和1 |
MySql日期类型
1. DATETIME(年月日,时分秒)
2. DATE(年月日)
3. TIMESTAMP(时间戳–范围19700101080001 - 203801191111407 基本不会用它,了解)对于到Java中也是 Date时间
4. TIME:存时间,不带日期
5. YEAR:只存年份
注意:在MySQL中,日期时间值使用单引号引起来。 相当于Java中Date(主要),Calender
4. DDL(数据库定义语言)
4.1 数据库相关
查看&使用数据库
show databases; //查看数据库
use 数据库名; //使用数据库
创建数据库
create database 数据库名;
删除数据库
drop database 数据库名;
显示建库语句:
show create database 数库名;
4.2 表相关
4.2.1 表的创建
语法:
-
先进入某一个数据库: use database_name;
-
输入建表的SQL命令:{dos 图形化界面写命令} [ 图形化界面创建 ]
CREATE TABLE 表名(
列名1 列的类型 [约束], 列名2 列的类型 [约束], .... 列名N 列的类型 约束
);
注意:
- 最后一行没有逗号
- 不能使用MySQL中的关键字作为标识符.
CREATE TABLE `student` (
`id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(11) SHOW TABLES; //查看数据库中存在哪些表:
DESC table_name; //查看表结构:
SHOW CREATE TABLE table_name; //查看表的详细定义(定义表的SQL语句)
DROP TABLE table_name; //删除表:
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4.2.2 表结构
SHOW TABLES; 查看数据库中存在哪些表:
DESC table_name; 查看表结构:
SHOW CREATE TABLE table_name; 查看表的详细定义(定义表的SQL语句)
DROP TABLE table_name; 删除表:
rename table 旧表名 to 新表名;修改表名
create table [if not exists] 新表名 like 旧表名;复制表结构
4.2.3 列操作
alter table 表名 add (列1,列2…) 增加列
alter table 表名 change 旧列名 新列名 新列名类型; 修改字段名及其类型:
alter table 表名 modify 列名 新数据类型; 修改字段类型
alter table 表名 drop 列名,列名… 删除列
#添加字段
ALTER table ddltest add age INT(20)
#修改表名
ALTER TABLE ddltest RENAME ddltest1
#修改表名
ALTER TABLE ddltest1 RENAME ddltest
#修改字段类型
ALTER TABLE ddltest MODIFY id BIGINT(11)
#删除字段
ALTER TABLE ddltest DROP age
#删除整张表
drop TABLE ddltest
4.3 表的约束
表的约束(针对于某一列):
-
非空约束(NK):NOT NULL,不允许某列的内容为空。
-
设置列的默认值:DEFAULT。
-
唯一约束(UK):UNIQUE,在该表中,该列的内容必须唯一。
-
主键约束:PRIMARY KEY, 非空且唯一。
-
主键自增长:AUTO_INCREMENT,从1开始,步长为1。
-
外键约束:FOREIGN KEY,A表中的外键列.A表中的外键列的值必须参照于B表中的某一列(B表主键)。
5. 数据查询语言(DQL)
5.1 简单查询
5.1.1 简单数据查询
语法:
SELECT {*, column [alias],…}
FROM table_name;
说明:
SELECT 选择查询列表
FROM 提供数据源(表、视图或其他的数据源)
如果为 * 和创建表时的顺序一致。匹配所有的字段
可以自己调整顺序,在select后边加上要查询的列名。
5.1.2 消除结果中重复的数据。只对某一列有效
语法:
SELECT DISTINCT 列名,…
5.1.3 实现数学运算查询
-
对NUMBER型数据可以使用算数操作符创建表达式(+ - * /)
-
对DATE型数据可以使用部分算数操作符创建表达式 (+ -)
-
运算符优先级:
-
乘法和除法的优先级高于加法和减法
-
同级运算的顺序是从左到右
-
表达式中使用"括号"可强行改变优先级的运算顺序
5.1.4 设置列名的别名
1、改变列的标题头;
2、用于表示计算结果的含义;
3、作为列的别名;
4、如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都需加单引号;—>英文单词
5.1.5 设置显示格式
为方便用户浏览查询的结果数据,有时需要设置显示格式,可以使用CONCAT函数来连接字符串。
格式:xxx商品的零售价为:xxx
SELECT CONCAT(productName,‘商品的零售价为:’,salePrice) AS productSalePrice FROM product;
5.2 过滤查询
5.2.1 比较运算符
语法
SELECT
FROM table_name
WHERE 条件1 AND/OR 条件2;
注意:字符串和日期要用单引号扩起来:
要让MySQL查询区分大小写,可以:
SELECT * FROM table_name WHERE BINARY productName=‘g9x’
SELECT * FROM table_name WHERE BINARY productName=‘G9X’
5.2.2 逻辑运算
#选择id,货品名称,批发价在300-400之间的货品
select id,productName,salePrice*cutoff from product where salePrice*cutoff>=300 and salePrice*cutoff<=400
#选择id,货品名称,分类编号为2,4的所有货品
select id,productName,dir_id from product where dir_id=2 or dir_id=4
#选择id,货品名词,分类编号不为2的所有商品
select id,productName,dir_id from product where dir_id !=2
5.2.3 优先级规则
注意: () 小括号跨越所有运算符优先级
5.2.4 范围查询-BETWEEN AND
使用BETWEEN运算符显示某一值域范围的记录,这个操作符最常见的使用在数字类型数据的范围上,但对于字符类型数据和日期类型数据同样可用。
语法:
SELECT
FROM table_name
WHERE 列名 BETWEEN minvalue AND maxvalue:闭区间。
#选择id,货品名称,批发价在300-400之间的货品
select id,productName,salePrice from product where salePrice BETWEEN 300 and 400
5.2.5 集合查询- IN
使用IN运算符,判断列的值是否在指定的集合中。
语法:
SELECT
FROM table_name
WHERE 列名 IN (值1,值2…);
#选择id,货品名称,分类编号为2,4的所有货品
select id,productName,dir_id from product where dir_id in (2,4);
#选择id,货品名称,分类编号不为2,4的所有货品
select id,productName,dir_id from product where dir_id not in (2,4);
5.2.6 空值查询 IS NULL
IS NULL:判断列的值是否为空。
语法:
WHERE 列名 IS NULL;
注意 :
列的值是 null 和空字符串不一样,如果是空字符串 应该 = “”
#查询商品名为NULL的所有商品信息。
select * from product where productName is null
select * from product where productName =""
5.2.7 模糊查询 LIKE
使用LIKE运算符执行通配查询,查询条件可包含文字字符或数字:
%:通配符:可表示零或多个任意的字符。
_:通配符:可表示任意的一个字符。
通配符:用来实现匹配部分值得特殊字符。
#查询id,货品名称,货品名称匹配'%罗技M9_' %匹配多个 _匹配一个
select id,productName from product where productName like '%罗技M9%'
5.3 结果排序
使用ORDER BY子句将结果的记录排序:
ASC : 升序,缺省–不写默认。
DESC: 降序。
ORDER BY 语句出现在SELECT语句的最后。
语法:
SELECT
FROM table_name
WHERE 条件
ORDER BY 列名1 [ASC/DESC],列名2 [ASC/DESC]…;
#选择id,货品名称,分类编号,零售价并且按零售价降序排序 缺省asc 升序 desc降序
select id ,productName,dir_id,salePrice from product order by salePrice desc
#选择id,货品名称,分类编号,零售价先按分类编号排序,再按零售价排序
select id ,productName,dir_id,salePrice from product order by dir_id,salePrice
#查询M系列并按照批发价排序(加上别名)
select id,productName,salePrice*cutoff sc from product where productName like "%M%" order by sc
#查询分类为2并按照批发价排序(加上别名)
select id,productName,dir_id,salePrice*cutoff sc from product where dir_id=2 order by sc
5.4 分页查询 LIMIT
语法:
SELECT * FROM table_name LIMIT ?,?;
SELECT * FROM table_name LIMIT beginIndex,pageSize;
eginIndex = (currentPage-1) * pageSize;
第一个?: 表示本页,开始索引(从0开始).
第二个?: 每页显示的条数
#分页 每页展示五条数据 limit index ,step index (索引 从0开始) step 步长
select * from product order by salePrice desc limit 0,5
select * from product order by salePrice desc limit 5,5
5.5 聚集函数
定义:聚集函数作用于一组数据,并对一组数据返回一个值。
COUNT:统计结果记录数 如果列的值为null 不会计算在内的
MAX: 统计计算最大值
MIN: 统计计算最小值
SUM: 统计计算求和
AVG: 统计计算平均值 如果列的值为null 不会计算在内的
#统计有多少条商品
select count(*) from product
#查询所有商品平均零售价
select avg(saleprice) from product
#查询商品总记录数(注意在Java中必须使用long接收)
#查询分类为2的商品总数
select count(*) from product where dir_id=2
#查询商品的最小零售价,最高零售价,以及所有商品零售价总和
select min(salePrice),max(salePrice),sum(salePrice) from product
5.6 分组查询 GROUP BY
可以使用GROUP BY 子句将表中的数据分成若干组,再对分组之后的数据做统计计算,一般使用聚集函数才使用GROUP BY.
语法格式:
SELECT 聚集函数或者分组的列
FROM table_name
WHERE 条件
GROUP BY 列名
HAVING 分组之后的条件;
注意:
-
GROUP BY 后面的列名的值要有重复性分组才有意义;
-
使用HAVING字句,对分组之后的结果作筛选;
-
不能在 WHERE 子句中使用组函数(注意);
-
可以在 HAVING 子句中使用组函数;
#查询每个商品分类编号和每个商品分类各自的平均零售价
select avg(salePrice),dir_id from product GROUP BY dir_id
#查询每个商品分类编号和每个商品分类各自的商品总数。
select count(*),dir_id from product GROUP BY dir_id
#查询每个商品分类编号和每个商品分类中零售价大于100的商品总数:
select count(*),dir_id,saleprice FROM product where salePrice>100 GROUP BY dir_id
#查询零售价总和大于1500的商品分类编号以及总零售价和: 要先分组求出每组的总和 再进行
#having 和group by 连在一起使用 是先分组 再对组数据进行过滤
select sum(salePrice),dir_id from product GROUP BY dir_id having sum(salePrice)>1500