数据库概述与SQL语句介绍

数据库

    数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。
    数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
    数据库中以表为组织单位存储数据。
    表类似我们的Java类，每个字段都有对应的数据类型。根据表字段所规定的数据类型，我们可以向其中填入一条条的数据，而表中的每条数据类似类的实例对象。表中的一行一行的信息我们称之为记录。

常见的数据库管理系统
    MYSQL ：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
    Oracle ：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
    DB2 ：IBM公司的数据库产品,收费的。常应用在银行系统中.
    SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
    SyBase ：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
    SQLite : 嵌入式的小型数据库，应用在手机端。

SQL语句

  结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

  SQL分类：

1. 数据定义语言：简称DDL(Data Definition
   Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等
2. 数据操作语言：简称DML(Data Manipulation
   Language)，用来对数据库中表的记录进行更新。关键字：insert, delete, update等
3. 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
4. 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

DDL语句

-- 选择数据库
-- use tm;

-- 查询数据库
-- show databases;

-- 创建数据库 --
CREATE DATABASE test2;
CREATE DATABASE test3 CHARACTER SET utf-8;

-- 删除数据库 --
DROP DATABASE test2;

-- 显示数据库的创建语句 ---
show CREATE DATABASE tm;

-- 创建表(重点),创建列 ---

```sql
use test1;
    create TABLE test_table(
	id int(11) PRIMARY KEY,
	uname VARCHAR(50) not null,
	uno VARCHAR(50) UNIQUE
);
-- 查询所有的表
show tables;

-- 删除表--
DROP table test_table;

-- 对列的操作 (重点)--
-- 增加一列   	alter table 表名 add  列名 类型(长度) [约束]--
ALTER TABLE test1.test_table add usex VARCHAR(5) NOT NULL;

-- 修改一列   alter table 表名 MODIFY  列名 类型(长度) [约束] --
-- 1.MODIFY 只能修改列的类型，大小，约束--
-- 2.当这列有数据的时候你修改的类型要与原类型兼容--
ALTER TABLE test1.test_table MODIFY usex int(11);

-- 修改整列包含列名(替换整列) alter table 表名 CHANGE  旧列名 新列名 类型(长度) [约束]
-- 满足和modify一样的规则
ALTER TABLE test1.test_table CHANGE usex user_sex VARCHAR(11); 

-- 删除列--
ALTER TABLE test1.test_table DROP user_sex;

-- 修改表名--
RENAME TABLE test1.test_table TO test1.test_user;

-- 修改表的字符集 --
ALTER TABLE test1.test_user CHARACTER SET utf8;

DML语句

-- 增加  INSERT INTO table (字段1,字段2)VALUES(值1,值2);---
INSERT INTO test_user (id,uname,uno)VALUES(2,'xiaozhang','002');

-- 变种 INSERT INTO TABLE VALUES(值1,值2,值3) --
-- 1.要求所有字段都要增加
-- 2.要和原表的列顺序
-- 3.有非空约束的字段(没有默认值)必须要增加
-- 4.有不能重复的字段，加的时候不能重复
-- 5.只要不是整形都需要加单引号
-- 6.如果你要插入空的，可以不写或者插入null
INSERT INTO test_user VALUES(10,'xiaozhao','010');
INSERT INTO test_user VALUES(5,'xiaozhang1','005');
INSERT INTO test_user VALUES(6,'xiaoli1','012');

-- mysql方言 支持多条插入 --
INSERT INTO test_user (id,uname,uno)VALUES(7,'xiaoli2','007'),
(8,'xiaoli3','008'),(9,'xiaoli4','009');

INSERT INTO test_user (id,uno)VALUES(2,'002');

-- 删除语句(数据) --
-- delete FROM 表名
-- TRUNCATE TABLE 表名
DELETE FROM ceshi;

-- DELETE from 表名 where 条件
-- 1.WHERE 条件大多都是主键
-- 2.WHERE 条件可以是多个，请用连接符连接 (and,or)
DELETE FROM test_user WHERE id>4 and id<7;

-- 1.TRUNCATE是针对表的删除】
-- 2.TRUNCATE会删除表的所有内容，包含索引，外键。。。。。。，自增归0
TRUNCATE TABLE ceshi;

-- 修改数据 --
-- UPDATE 表名 SET 列名1=值1,列名2=值2,列名3=值3 [WHERE 条件]--
-- 1.修改的列类型一定要一致
-- 2.修改的值不能超过他的大小
-- 3.除了整型，其他字段都要加单引号
UPDATE test_user SET uname='xiaopan',uno='020' WHERE id=4; 

DQL语句

--查询--
-- SELECT [DISTINCT] * 列名1,列名2 FROM 表名  [WHERE 条件] 

-- 针对列进行的查询----
-- 1.查询所有的商品
SELECT * from d_product;
-- 2.查询商品名称和价格
SELECT pname,price FROM d_product;
-- 3.查询出的结构用中文名显示
SELECT pname as '名称',price as '价格' FROM d_product;
SELECT pname '名称',price '价格' FROM d_product;
SELECT d.pname '名称',d.price '价格' FROM d_product d;
-- 4.查询价格(去除重复的) DISTINCT 修饰后面所有的列组合去重 一般用于查询单列去重
SELECT DISTINCT price,pname FROM d_product;
-- 5.查询商品打九折之后进行显示
SELECT pname,price*0.9 '跳楼价' FROM d_product;


-- 针对查询条件 WHERE (重点)---
-- 1.查询价格小于2000的商品
SELECT * FROM d_product WHERE price<2000;
-- 2.价格小于2000并且是c002的商品
SELECT * FROM d_product WHERE price<2000 and category_id='c002';
-- 3.不是c001的商品
SELECT * FROM d_product WHERE category_id<>'c001';
SELECT * FROM d_product WHERE category_id!='c001';
-- 4.价格在200到1000之间
SELECT * FROM d_product WHERE price BETWEEN 200 AND 1000;
-- 5.查询c001和c002的商品
SELECT * FROM d_product WHERE category_id in('c001','c002');
-- 6.查询种类(不)为null的商品
SELECT * FROM d_product WHERE category_id is NULL;
SELECT * FROM d_product WHERE category_id is not NULL;
-- 7.查出商品名称中带o的商品 % 代码任意多个字符 _代码任意一个字符
SELECT * FROM d_product WHERE pname LIKE '%o%';
-- MYSQL 字符串拼接 函数
-- mysql 方言
-- CONCAT(str1,str2,...)
SELECT CONCAT(pid,',',pname,',',price,',',category_id) '商品详情' FROM d_product;
-- oracle 方言
-- CONCAT(str1,str2)
SELECT CONCAT(pid,CONCAT(',',pname)) '商品详情' FROM d_product;

-- 排序 order by 列名 规则(默认升序 ASC  DESC降序)  --
-- 1.根据价格排序商品
SELECT * FROM d_product ORDER BY price DESC; 
-- 2.查询c001产品并且降序排列
SELECT * FROM d_product WHERE category_id='c001'  ORDER BY price DESC;
-- 3.查询按价格降序,再按种类升序的商品排列
SELECT * FROM d_product ORDER BY price DESC,category_id ASC;
-- 4.查出所有的价格降序排列
SELECT DISTINCT price FROM d_product  ORDER BY price DESC;

-- 5.商品中带有o字母的商品价格降序排列
SELECT * FROM d_product WHERE pname LIKE '%o%' ORDER BY price DESC;

-- 聚合(函数) 根据多行记录合并成一条记录--
-- count ,sum ,max,min,avg

SELECT * FROM d_product;

-- 1.count 求数量
-- 查出c001一共有多少件商品
SELECT COUNT(0) FROM d_product WHERE category_id='c001';
-- 查出一共有多少种价格
SELECT count(DISTINCT price) FROM d_product;

-- 2.SUM 求和
-- 查询所有c001商品的总价格
SELECT sum(price) FROM d_product WHERE category_id='c001';

-- 3.max 求最大值
-- 查询最大的价格
SELECT max(price) FROM d_product;

-- 4.min 求最小值
-- 查询最小的价格
SELECT min(price) FROM d_product;

-- 5.avg 求平均值
-- 求价格的平均值
SELECT avg(price) FROM d_product;


-- 分组 GROUP BY  HAVING--
-- 1.查询出类别的种类 
-- 分组只能获取分组之后的列或者 其他列的聚和函数
SELECT category_id,pname FROM d_product GROUP BY category_id;
-- mysql 可以获取其他列但是无意义
-- oracl 不可以获取其他的列(报错)
-- 获取各个种类的商品的数量
SELECT category_id,COUNT(0) '总数' FROM d_product GROUP BY category_id;
-- 获取每种商品中最贵的价格
SELECT category_id,pname FROM d_product GROUP BY category_id;

-- 2.多项分组 
-- 获取商品中所有的种类以及 各种中的所有的价格
SELECT category_id,price FROM d_product GROUP BY category_id,price;

-- 3.分组结果进行筛选 HAVING 在同一块sql语句 where --》 GROUP BY  --》HAVING
-- 每个大于 1000 的商品的种类 
SELECT category_id from d_product WHERE price>1000 GROUP BY category_id;
-- HAVING 条件一定要是分组的列或者其他列的聚会函数
SELECT category_id FROM d_product GROUP BY category_id HAVING max(price)>1000;


-- mysql条件判断 --
-- -- CASE 列 WHEN (条件1) THEN (显示内容) ELSE (其他条件显示内容)  End ----
SELECT *,(CASE usex WHEN 0 THEN '女' WHEN 1 THEN '男' END) '性别' FROM test_user ;

-- mysql数据库的备份和还原(重点) --
-- 备份 ---
-- 1. 要求mysql的bin目录需要加入环境变量 --
-- 2.mysqldump -uroot -p123456 tm > d:\tm.sql


-- 还原 ---
-- 1.进入mysql控制台
-- 2.创建数据库(选择数据库)
-- 3.切换到你要使用的数据库
-- 4.source (指定文件位置 不能使用\)

-- 约束 --

-- 使用constraint 区域增加约束
-- [constraint 约束名] PRIMARY KEY
-- 1.修改表的结构
ALTER TABLE ceshi ADD CONSTRAINT id_pk PRIMARY KEY(id);
-- 2.在创建表的约束区加约束
-- 3.主键约束必须唯一，可以有多列主键,联合主键
create TABLE ceshi1(
	id int(11),
	uname VARCHAR(50),
	uno VARCHAR(50),
	PRIMARY KEY(id,uname)
);
-- 删除主键约束
ALTER TABLE ceshi1 DROP PRIMARY KEY;

-- 了解--
-- not null 加在列的后面(创建表,ALTER列),不使用CONSTRAINT
-- UNIQUE 加在列的后面(创建表,ALTER CONSTRAINT)

-- 自动增长列(重点)--
-- AUTO_INCREMENT
-- 1.工具自增
-- 2.创建表的时候或者修改列的时候在最后加上AUTO_INCREMENT
create TABLE ceshi2(
	id int(11) PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR(50),
	uno VARCHAR(50)
);
INSERT INTO ceshi2 (uname,uno)VALUES('xiaowang','001');
INSERT INTO ceshi2 (uname,uno)VALUES('xiaozhang','002');
INSERT INTO ceshi2 (uname,uno)VALUES('xiaoli','003');
SELECT * FROM ceshi2;
-- 3.自增可以修改自增序列，修改之后会选择最大的序列进行自增
ALTER TABLE ceshi2 AUTO_INCREMENT=1;
INSERT INTO ceshi2 (uname,uno)VALUES('xiaozhao','004');
INSERT INTO ceshi2 (uname,uno)VALUES('xiaopan','005');

-- delete 和truncate
-- DELETE删除不会重置自增序列，truncate会重置自增序列为1
DELETE FROM ceshi2;
TRUNCATE TABLE ceshi2;

-- 多表操作 --
-- 1.一对一 一对一在设计的时候一般不会设计2张表
-- 2.一对多(多对一)
-- 3.多对多

 
-- 设计数据库的原则(重点)-- 
-- A.抽取模型，抽取基本单元-->抽取对象
-- B.设计关系
-- 		1.一对一 ，一般把一对一合并成一张表，或者在任意一个表中加逻辑外键。
-- 		2.一对多，多对一永远在多的那个表中加外键。
-- 		3.多对多，使用关系表来实现，关系表中的字段分别是2个表的主键。


-- 外键 -- 
-- 练习2个表的查询字段 ，一般使用从表的主键 --
-- FOREIGN KEY 增加物理外键(了解)
-- 1.设置了物理外键，不能随意的删除和更新
-- 2.表之间的关系更加明确了
ALTER TABLE online_class ADD CONSTRAINT class_teacher_fk FOREIGN KEY(tid) REFERENCES teacher(id);
DELETE from teacher where id=4;

-- 1.不允许使用物理外键，因为物理外键非常不灵活，无法随意的删改。
-- 2.一定要使用逻辑外键，使用注释的方式描述出来。

-- A.笛卡尔积 交叉连接查询
SELECT * FROM category,products ORDER BY cid;

-- B.内连接 是2个表的交集
-- 		1.主表通过 inner JOIN去连接副表(主副无顺序)
--    2.连接一定要加连接条件，如果不加就是笛卡尔积
-- 写法1 使用inner JOIN
SELECT c.*,p.*  FROM category c INNER JOIN products p ON p.category_id=c.cid
-- 写法2 使用组合查询带条件
SELECT c.*,p.* FROM category c,products p  WHERE p.category_id=c.cid

-- 查询所有的商品(显示商品的种类名称)
-- C.外连接 单个表的所有的数据
-- 		1.外连接分主表和附表
-- 		2.外连接一定要加连接条件，使用on增加外键条件
-- 写法1.left [outer] join
-- 写法2.right [outer] join
SELECT * FROM products p LEFT OUTER JOIN category c on p.category_id=c.cid;
SELECT * FROM products p RIGHT OUTER JOIN category c on p.category_id=c.cid;
-- 		3.左外和右外连接本质上其实是一样的，和主表和附表有关系
--    4.A主 +B副 +左外 == A副 +B主 +右外  (A主左=A) (A主右=B)
--   我的经验 请不要使用右连接，全部使用左连接

-- 查询所有的家电
SELECT c.cname,p.* FROM products p LEFT JOIN category c ON p.category_id=c.cid
WHERE c.cname='家电';


-- 子查询 --
-- 1.在查询语句中嵌套其他查询--

-- 查询所有的是家电的商品
-- 查询的是所有的商品，商品的类型是家电
-- 1.子查询在查询条件中
SELECT * FROM products WHERE category_id=(SELECT cid FROM category WHERE cname='家电');
SELECT cid FROM category WHERE cname='家电';
-- 2.子查询在表的位置
-- 查询各种商品的平均价格，取出平均值大于1000的种类 
SELECT category_id,avg(price) '平均价格' FROM products GROUP BY category_id HAVING avg(price)>1000;
SELECT t.* FROM (SELECT category_id,avg(price) '平均价格' FROM products GROUP BY category_id)t 
WHERE t.平均价格>1000;
-- 3.子查询在列的位置
-- 查询所有的商品(显示商品的种类名称)
SELECT p.*,(SELECT c.cname FROM category c WHERE c.cid=p.category_id) '种类' FROM products p;

-- 查出家电和化妆品的商品
-- 子查询
SELECT * FROM products WHERE category_id in
(SELECT cid FROM category WHERE cname='家电' OR cname='化妆品');
-- 连表查询
SELECT p.*,c.cname FROM products p LEFT JOIN category c ON p.category_id=c.cid 
WHERE c.cname='家电' OR c.cname='化妆品';

-- 子查询的注意点
-- 1.当子查询作为条件是，如果是等号 那么子查询必须只返回一列，一行数据
-- 2.当子查询查询结果是多行，那查询条件要使用in关键字
-- 3.当子查询作为临时表，或者列时，需要加别名

-- 扩展内容
-- 1.条件判断 CASE 列名 WHEN 条件1 THEN 结果1 .... ELSE 默认结果 END
-- 查询商品，要求显示出热销商品，字段是flag
SELECT *,(CASE flag WHEN 1 THEN  '热销商品' ELSE '非热销商品' END)'销售状态' FROM products;

-- 2.分页查询(重点) mysql的方言 LIMIT
-- limt 起始下标(从0开始) 显示几条
-- LIMIT配合order by 经常用来取最新的一条数据 (先按时间倒序排列然后再使用limit 0,1)
-- 查询价格最高的5个商品
SELECT * FROM products ORDER BY price DESC LIMIT 0,5;
-- 查询价格是5-8位的商品
SELECT * FROM products ORDER BY price DESC LIMIT 4,4;