MySQL基础

MySQL基础

一、数据库

（一）数据库概念

1.数据库(Database，简称DB)：存数据的仓库

2.数据库管理系统(Database Management System，简称DBMS)：MySQL

3.数据库应用系统(Database Application System，简称DAS)：类似网站，不限制于网站

（二）数据库的由来和发展

1. 人工管理阶段

2. 文件系统阶段

3. 关系数据库技术阶段

4. 后关系数据库技术阶段

   ORDBMS：面向对象数据库技术。

   NoSQL ：结构化数据库技术

（三）常见的关系型数据库服务器

数据库	公司	特点
Oracle	Oracle(甲骨文/神域)	稳定，功能齐全，适用于大型企业领域
DB2	IBM	速度快，适用于海量数据
SQL SERVER	微软	全面，操作容易，但不跨平台
MYSQL	AB–>SUN–>Oracle	开源，体积小，速度快，适用于于中小型企业领域

（四）SQL

结构化查询语言（Structured Query Language）

1.数据查询语言（DQL）：query查询，从表中获得数据

2.数据操作语言（DML）：INSERT、UPDATE和DELETE分别用于添加、修改和删除表中的行

3.事务处理语言（TPL）

4.数据控制语言（DCL）

5.数据定义语言（DDL）

6.指针控制语言（CCL）

书写

1. 在MySQL数据库中，SQL语句大小写不敏感
2. 一般关键字大写，其他小写
3. SQL语句可单行或多行书写

（五）表的概念

在数据库中所有数据都是以表的形式存在的

一个表相当于java中的一个类；一列（表属性）相当于java中的字段；一行（一条数据）相当于java中的对象

二、MySQL数据库

（一）启动MySQL服务

必须先在操作系统中开启MySQL的服务,才能够使用MySQL软件

1.cmd以管理员身份运行，输入services.msc ，找到MySQL服务

2.dos命令的方式：

启动MySQL服务 ： net start MySQL

关闭MySQL服务 ： net stop MySQL

（二）MySQL数据库操作

系统数据库（系统自带的数据库）：不能被修改

用户数据库（用户自定义的数据库）：一个项目一个用户数据库

1.查看数据库 show databases;

2.使用数据库 use 数据库名;

3.创建数据库 create database 数据库名;

4.删除数据库 drop database 数据库名;（一但删除不能恢复）

（三）MySQL常用列类型

JAVA	Java备注	MYSQL	MYSQL备注
int	短整型	int	短整型
long	长整型	bigint	长整型
float	单精度	float	单精度
double	双精度	double	双精度
Bigdecimal[类]	金融数据	decimal	金融数据
String	字符串	char	不可变字符串
		varchar	可变字符串
boolean	布尔值	Bit	取值0和1

（四）表的操作

CREATE TABLE `product` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,//不允许为空，主键自增
  `name` varchar(20) DEFAULT `姓名`,//设置默认值为姓名
  `IDCard` varchar(18) DEFAULT NULL,//设置默认值为NULL
  PRIMARY KEY (`id`)//非空且唯一
  UNIQUE KEY `IDCard` (`IDCard`),//内容必须唯一
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

1.创建表语法

CREATE TABLE 表名(
   列名1    列的类型   约束,
   列名2    列的类型   约束,
   ....
   列名N    列的类型   约束    
);

注意事项：

1）最后一行没有逗号

2）约束不一定要写

2.查看表结构和删除表

dos命令的方式：

1）SHOW TABLES; 查看数据库中存在哪些表:

2）DESC table_name; 查看表结构：

3）SHOW CREATE TABLE table_name; 查看表的详细定义(定义表的SQL语句)

4）DROP TABLE table_name; 删除表

3.表的约束

针对某一列

1）非空约束(NK)：NOT NULL 不允许某列的内容为空

2）设置列的默认值：DEFAULT

3）唯一约束(UK)：UNIQUE 在该表中，该列的内容必须唯一

4）主键约束：PRIMARY KEY 非空且唯一

5）主键自增长：AUTO_INCREMENT 从1开始，主键增加1

6）外键约束：FOREIGN KEY A表中的外键列的值必须参照于B表主键

三、查询操作

（一）简单查询

1.简单数据查询

SELECT 要查询的列名（ *表示查询所有的列名） FROM 表名；

查询所有货品信息
SELECT * FROM product;
查询所有货品的id,productName,salePrice
SELECT id,productName,salePrice FROM product;

2.消除结果中重复的数据

SELECT DISTINCT 要查询的列名 FROM 表名；

查询商品的分类编号去重
SELECT DISTINCT dir_id FROM product;

3.实现数学运算查询

对NUMBER型数据可以使用算数操作符创建表达式（+ - * /）

对DATE型数据可以使用部分算数操作符创建表达式 （+ -）

查询所有货品的id，名称和批发价(批发价=卖价*折扣)
SELECT id,productName,salePrice*cutoff  FROM product;
查询所有货品的id，名称，和各进50个的成本价(成本=costPrice)
SELECT id,productName,costPrice*50  FROM product;

4.设置列名的别名

SELECT 要查询的列名 as 列的别名 FROM 表名;

查询所有货品的id，名称，各进50个，并且每个运费1元的成本
SELECT id,productName,(costPrice+1)*50 as 进50个加运费成本 FROM product;

5.设置显示格式

SELECT CONCAT(str1,str2,…) FROM 表名;

查询商品的名字和零售价，格式：xxx商品的零售价为：xxx
SELECT CONCAT(productName,'商品的零售价为：',salePrice) FROM product;

（二）过滤查询

执行顺序：FROM --> WHERE --> SELECT

1.比较运算符

!=（<>） 不等于

SELECT 要查询的列名 FROM 表名 WHERE 条件；

查询货品零售价为119的所有货品信息.
SELECT * FROM product WHERE salePrice = 119;
查询货品名 不为 罗技G9X的所有货品信息.
SELECT * FROM product WHERE productName != '罗技G9X';
SELECT * FROM product WHERE productName <>'罗技G9X';
查询分类编号不等于2的货品信息
SELECT * FROM product WHERE dir_id !=2;
查询货品名称,零售价小于等于200的货品
SELECT productName FROM product WHERE salePrice <= 200;
查询id，货品名称，批发价大于350的货品
SELECT id,productName FROM product WHERE salePrice*cutoff > 350;

2.逻辑运算

AND(&&)
OR
NOT(!)

SELECT 要查询的列名 FROM 表名 WHERE 条件1 AND(&&)/OR 条件2;

SELECT 要查询的列名 FROM 表名 WHERE NOT 条件;

SELECT 要查询的列名 FROM 表名 WHERE ！（条件）；

选择id，货品名称，批发价在300-400之间的货品
SELECT id,productName FROM product WHERE salePrice*cutoff > 300 AND salePrice*cutoff < 400;
SELECT id,productName FROM product WHERE salePrice*cutoff > 300 && salePrice*cutoff < 400;
选择id，货品名称，分类编号为2,4的所有货品
SELECT id,productName FROM product WHERE dir_id =2 OR dir_id = 4;
选择id，货品名称，分类编号不为2的所有商品
SELECT id,productName FROM product WHERE dir_id !=2;
SELECT id,productName FROM product WHERE NOT dir_id =2;

3.优先级规则

() > 所有比较运算符 > NOT > AND > OR

4.范围查询

SELECT 要查询的列名 FROM 表名 WHERE 列名 BETWEEN minvalue AND maxvalue;

选择id，货品名称，批发价在300-400之间的货品
SELECT id,productName FROM product WHERE salePrice*cutoff BETWEEN 300 AND 400;
选择id，货品名称，批发价不在300-400之间的货品
SELECT id,productName FROM product WHERE !(salePrice*cutoff BETWEEN 300 AND 400);
SELECT id,productName FROM product WHERE NOT salePrice*cutoff BETWEEN 300 AND 400;

5.集合查询

SELECT 要查询的列名 FROM 表名 WHERE 列名 IN (值1，值2，…);

选择id，货品名称，分类编号为2,4的所有货品
SELECT id,productName FROM product WHERE dir_id IN(2,4);
选择id，货品名称，分类编号不为2,4的所有货品
SELECT id,productName FROM product WHERE NOT dir_id IN(2,4);
SELECT id,productName FROM product WHERE !(dir_id IN(2,4));

6.空值查询

SELECT 要查询的列名 FROM 表名 WHERE 列名 IS NULL；

查询商品名为NULL的所有商品信息
SELECT * FROM product WHERE productName IS NULL;

7.模糊查询

% 任意匹配
_ 必须匹配任意的一个符号

SELECT 要查询的列名 FROM 表名 WHERE 列名 LIKE '%_ ';

查询id，货品名称，货品名称匹配'%罗技M9_'
SELECT id,productName FROM product WHERE productName LIKE '%罗技M9_';
查询id，货品名称，分类编号,零售价大于等于50并且货品名称匹配'%罗技M1__'
SELECT id,productName,dir_id FROM product WHERE salePrice >=50 AND productName LIKE '%罗技M1__';

8.正则表达式

SELECT 要查询的列名 FROM 表名 WHERE 列名 REGEXP ‘正则表达式’;

查找productname字段中包含字母的所有数据
SELECT * FROM product WHERE productName REGEXP '[a-z]';

（三）结果排序

执行顺序：FROM --> WHERE --> SELECT --> ORDER BY

ASC : 升序（不写默认）

DESC: 降序

SELECT 要查询的列名 FROM 表名 WHERE 条件 ORDER BY 列名1 ASC/DESC , 列名2 ASC/DESC，…；

选择id，货品名称，分类编号,零售价并且按零售价降序排序
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 *,salePrice*cutoff as '批发价' FROM product WHERE productName LIKE '%M%' ORDER BY salePrice*cutoff DESC;

（四）分页查询

1.假分页(基本不用)

一次查完部分显示，翻页比较快

2.真分页

一次查几条，翻页比较慢

SELECT 要查询的列名 FROM 表名 LIMIT ?(表示本页,开始索引从0开始),?(每页显示的条数);

SELECT 要查询的列名 FROM 表名 LIMIT (currentPage-1) * pageSize,pageSize;（在Java中写的）

真分页
SELECT * FROM product LIMIT 0,5;
SELECT * FROM product LIMIT 5,5;
SELECT * FROM product LIMIT （第几页-1）,5;

（五）聚集函数

1）COUNT：统计结果记录数（如果列的值为null不会计算在内）

SELECT COUNT(要查询的列名) FROM 表名； 性能不好

SELECT COUNT(主键) FROM 表名； 主键唯一查询效率高

2）MAX： 统计计算最大值

SELECT MAX(要查询的列名) FROM 表名；

3）MIN： 统计计算最小值

SELECT MIN(要查询的列名) FROM 表名；

4）SUM： 统计计算求和

SELECT SUM(要查询的列名) FROM 表名；

5）AVG： 统计计算平均值（如果列的值为null不会计算在内）

SELECT AVG(要查询的列名) FROM 表名；

查询所有商品平均零售价
SELECT AVG(salePrice) FROM product;
查询商品总记录数(注意在Java中必须使用long接收)
SELECT COUNT(id) FROM product;
查询分类为2的商品总数
SELECT COUNT(dir_id) FROM product WHERE dir_id = 2;
查询商品的最小零售价，最高零售价，以及所有商品零售价总和
SELECT MIN(salePrice),MAX(salePrice),SUM(salePrice) FROM product;

（六）分组查询

执行顺序：FROM --> WHERE（筛选） --> SELECT（查询） --> GROUP BY（分组） -->HAVING（最后筛选）

SELECT 要查询的列名 FROM 表名 （WHERE 条件） GROUP BY 根据什么分组 （HAVING 筛选的条件）

查询每个商品分类编号和每个商品分类各自的平均零售价
SELECT dir_id,AVG(salePrice) FROM product GROUP BY dir_id;
查询每个商品分类编号和每个商品分类中零售价大于100的商品总数：
SELECT dir_id,COUNT(dir_id) FROM product WHERE salePrice >100 GROUP BY dir_id; 
查询零售价总和大于1500的商品分类编号以及总零售价和
SELECT dir_id,SUM(salePrice) FROM product GROUP BY dir_id HAVING SUM(salePrice)>1500;

注意事项：

1. WHERE后面不能用聚合函数
2. WHERE后面不能用别名
3. 分组之后的条件要使用HAVING字句
4. GROUP BY后面的列名的值要有重复性分组才有意义
5. GROUP BY可以去重