[大数据]mysql

MySQL

数据库的操作

数据库的查询和创建

-- 查询所有数据库
SHOW DATABASES；

-- 查询某个数据库的创建语句
SHOW CREATE DATABASE 数据库的名称；

-- 创建数据库
CREATE DATABASE 数据库名称；

-- 创建数据库，判断，如果不存在则创建
CREATE DATABASE IF NOT EXISTS 数据库名称；

-- 创建数据库、并指定字符集
CREATE DATABASE 数据库名称 CHARACTAR SET 字符集名称；

数据库的修改、删除、使用数据库

-- 修改数据库的字符集
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称；

-- 删除数据库
DROP DATABASE 数据库名称；

-- 删除数据库，判断，如果存在则删除
DROP DATABASE IF EXISTS 数据库名称；

-- 使用数据库
USE 数据库名称；

-- 查询当前使用那个数据库
SELECT DATABASE();

数据表的操作

数据表的查询

-- 查询所有表
SHOW TABLES；

-- 查询表结构
DESC 表名；

-- 查询数据表的字符集
SHOW TABLE STATUS FROM 数据库名称 LIKE '表名'；

数据表的创建

-- 创建数据表
CREATE TABLE 表名（
列名 数据类型 约束，
列名 数据类型 约束，
......
列名 数据类型 约束
）；

数据类型
int:证书类型
double：小数类型
date：日期类型。包含年月日，格式yyyy-MM-dd
datetime：日期类型。包含年月日时分秒，格式yyyy-MM-dd HH:mm:ss
timestamp：时间戳类型。包含年月日时分秒，格式yyyy-MM-dd HH:mm:ss
*如果不给该列赋值、或赋值为null，则默认使用当前系统时间自动赋值
varchar(长度)：字符串类型  

数据表的修改

-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名；

-- 修改表的字符集
ALTER TABLE 表名 CHARACTER SET 字符集名称；

-- 单独添加一列
ALTER TABLE 表名 ADD 列名 数据类型；

-- 修改某列的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型；

-- 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型；

-- 删除某一列
ALTER TABLE 表名 DROP 列名；

数据表的删除

-- 删除数据表
DROP TABLE 表名；

-- 删除数据表（判断，如果存在则删除）
DROP TABLE IF EXISTS 表名；

新增表数据

-- 给指定列添加数据
INSERT INTO 表名(列名1，列名2.....) VALUES (值1,值2，...);

-- 给全部列添加数据
INSERT INTO 表名 VALUES (值1，值2，...);

-- 批量添加数据
INSERT INTO 表名 (列名1，列名2，...) VALUES (值1,值2,...),(值1,值2,....);
INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,....)....;

*列名和值的数量以及数据类型对应,除了数字类型,其他数据类型的数据都需要加引号(单引双引都行,推荐单引)。
*除了数字类型意外，其他类型都需要加引号。

修改和删除表数据

-- 修改表中的数据
UPDATE 表名 SET 列名1=值1,列名2=值2,...WHERE条件；
*修改语句中必须加条件，如果不加条件，则会将所有数据都修改。

-- 删除表中的数据
DELETE FROM 表名 WHERE条件；
*删除语句中必须加条件，如果不加条件，则会将所有数据都删除。

*练习查询数据准备

-- 创建db1数据库
CREATE DATABASE db1;
-- 使用db1数据库
USE db1;

-- 创建数据库
CREATE TABLE product(
id INT,              -- 商品编号
NAME VARCHAR(20),    -- 商品名称
price DOUBLE,        -- 商品价格
brand VARCHAR(10),   -- 商品品牌
stock INT,           -- 商品库存
insert_time DATE     -- 添加时间
);

-- 添加数据
INSERT INTO product VALUES 
(1,'华为手机',3999,'华为',23,'2088-03-10'),
(2,'小米手机',2999,'小米',30,'2088-05-15'),
(3,'苹果手机',5999,'苹果',18,'2088-08-20'),
(4,'华为电脑',6999,'华为',14,'2088-06-16'),
(5,'小米电脑',4999,'小米',26,'2088-07-08'),
(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
(7,'联想电脑',7999,'联想',NULL,'2088-11-11');

查询语法介绍

SELECT
   字段列表
FROM
   表明字段
WHERE
   条件列表
GROUP BY
   分组字段
HAVING
   分组后的过滤条件
ORDER BY
   排序
LIMIT
   分页

查询全部

-- 查询全部的表数据
SELECT * FROM 表名;

-- 查询指定字段的表数据
SELECT 列名1,列名2,...FROM 表名;

-- 去除重复查询
SELECT DISTINCT 列名1,列名2,.....FROM 表名;

-- 计算列的值（四则运算）
SELECT 列名1 运算符(+ - * /)列名2 FROM 表名;

-- 起别名查询
SELECT 列名 AS 别名 FROM 表名;


	   
****sql练习
-- 查询全部
SELECT * FROM `product`;
-- 查询指定列
SELECT NAME,price,brand FROM `product`;
-- 去除重复查询

-- 查询品牌
SELECT brand FROM `product`;
-- 查询品牌，去除重复
SELECT DISTINCT brand FROM `product`;
-- 查询商品名称和库存，库存数量在原有基础上加10
SELECT NAME,stock+10 FROM `product`;
-- 查询商品名称和库存，库存数量在原有的基础上加10，进行null值判断
SELECT NAME,IFNULL(stock,0)+10 FROM `product`;
-- 起别名

-- 查询商品名称和库存，库存数量在原有基础加10。进行null值判断。起别名为getsum
SELECT NAME,IFNULL(stock,0)+10 AS getsum FROM `product`;

条件查询

-- 条件查询语法
SELECT 列名列表 FROM 表名 WHERE 条件；


****sql练习
-- 查询库存大于20的商品信息
SELECT * FROM `product` WHERE stock > 20;
-- 查询品牌为华为的商品
SELECT * FROM `product` WHERE brand='华为';
-- 查询金额在4000 ~ 6000之间的商品信息
SELECT * FROM `product` WHERE price>4000 AND price < 6000; 
SELECT * FROM `product` WHERE price BETWEEN 4000 AND 6000;
-- 查询库存为14、30、23的商品信息
SELECT * FROM `product` WHERE stock=14 OR stock=30 OR stock=23;
SELECT * FROM `product` WHERE stock IN(14,30,23);
-- 查询库存为null的商品信息
SELECT * FROM `product` WHERE stock IS NULL;
-- 查询库存不为null的商品信息；
SELECT * FROM `product` WHERE stock IS NOT NULL;
-- 模糊查询

-- 查询名称以小米为开头的商品信息
SELECT * FROM `product` WHERE NAME LIKE'小米%';
-- 查询名称第二个字是为的商品信息
SELECT * FROM `product` WHERE NAME LIKE '_为%';
-- 查询名称为四个字的商品信息
SELECT * FROM `product` WHERE NAME LIKE '____';
-- 查询名称中包含电脑的商品信息
SELECT * FROM `product` WHERE NAME LIKE '%电脑%';

聚合函数查询

• 聚合函数的介绍

  将一列数据作为一个整体，进行纵向的计算

• 聚合函数的分类

-- 聚合函数查询语法
SELECT 函数名（列名）FROM 表名 WHERE条件；


****sql练习
-- 聚合函数
-- 计算product表中的总记录数
SELECT COUNT(*) FROM `product`;
-- 获取最高价格
SELECT MAX(price) FROM `product`;
-- 获取最低库存
SELECT MIN(stock) FROM `product`;
-- 获取总库存数量
SELECT SUM(stock) FROM `product`;
-- 获取品牌为品牌为苹果的总库存数量
SELECT SUM(stock) FROM `product` WHERE brand='苹果';
-- 获取品牌为小米的平均商品价格
SELECT AVG(price) FROM `product` WHERE brand='小米';

排序查询

-- 排序查询语法
SELECT 列名列表 FROM 表名 WHERE条件 ORDER BY列名 排序方式，列名 排序方式，...;



****sql练习
-- 按照库存升序排列
SELECT * FROM product ORDER BY stock ASC;
-- 查询名称中包含手机的商品信息，按照金额降序排列
SELECT * FROM product WHERE NAME LIKE '%手机%'ORDER BY price DESC;
-- 按照金额升序排列，如果金额相同，按照库存降序排列
SELECT * FROM product ORDER BY price ASC,stock DESC;

排序方式：ASC-升序，DESC-降序

如果有多个排序条件，只有当前边的条件值一样时，才会判断第二条件。

分组查询

-- 分组查询语法
SELECT 列名列表 FROM 表名 WHERE条件 GROUP BY 分组列名
HAViNG 分组后的条件过滤
ORDER BY 排序列名 排序方式；


****sql练习
-- 按照品牌分组，获取每组商品的总金额
SELECT brand,SUM(price) FROM product GROUP BY brand;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额
SELECT brand,SUM(price) FROM product WHERE price>4000 GROUP BY brand; 
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
SELECT brand,SUM(price) AS getSum FROM product WHERE price>4000 GROUP BY brand HAVING getSum>7000;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额。只显示总金额大于7000元的、并按照总金额的降序排列 
SELECT brand,SUM(price) AS getsum FROM product WHERE price>4000 GROUP BY brand HAVING getsum>7000 ORDER BY getsum DESC;

分页查询

-- 分页查询语法
SELECT 列名列表 FROM 表名
WHERE 条件
GROUP BY分组列名
HAVING 分组后的条件过滤
ORDER BY 排序列名 排序方式
LIMIT 当前页数，每页显示的条数



****SQL练习
-- 每页显示3条数据

-- 第1页   当前页数=（1-1）*3
SELECT * FROM product LIMIT 0,3;
-- 第2页   当前页数=（2-1）*3
SELECT * FROM product LIMIT 3,3;
-- 第3页    当前页数=（3-1）*3
SELECT * FROM product LIMIT 6,3;

约束的介绍

• 什么是约束

? 对表中的数据进行限定，保证数据的正确性、有效性、完整性

• 约束的分类

主键约束

• 主键约束的特点

  主键约束默认包含非空和唯一两个功能

  一张表只能有一个主键

  主键一般用于表中数据的唯一标识

  -- 建表时添加主键约束
  CREATE TABLE 表名（
        列名 数据类型 PRIMARY KEY，
        .....
        列名 数据类型 约束
  ）；
  
  -- 删除主键约束
  ALTER TABLE 表名 DROP PRIMARY KEY；
  
  -- 建表后单独添加主键约束
  ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY；
  
  

  主键自增约束

  -- 建表时添加主键自增约束
  CREATE TABLE 表名（
         列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
         ......
         列名 数据类型 约束
  ）；
  
  -- 删除主键自增约束
  ALTER TABLE 表名 MODIFY 列名 数据类型；
  
  -- 建表后单独添加主键自增约束
  ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
  
  *MySQL中的自增约束，必须配合键的约束一起使用！
  

  唯一约束

  -- 建表时添加唯一约束
  CREATE TABLE 表名（
         列名 数据类型 UNIQUE，
         ....
         列名 数据类型 约束
  ）；
  
  -- 删除唯一约束
  ALTER TABLE 表名 DROP INDEX 列名；
  
  -- 建表后单独添加唯一约束
  ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE；
  

  非空约束

  -- 建表时添加非空约束
  CREATE TABLE 表名（
        列名 数据类型 NOT NULL，
        ......
        列名 数据类型 约束
  ）；
  
  -- 删除非空约束
  ALTER TABLE 表名 MODIFY 列名 数据类型；
  
  -- 建表后单独添加非空约束
  ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL；