数据库基础

1、数据库分类

关系型数据库：将数据保存在不同的表中，一类数据一个表，表之间相互联系，例子：Oracle、MySQL、SQL Server、Microsoft Access、DB2

非关系型数据库：MongoDB，redis

2、navicate  导入表格

表格不能有任何格式，比如边框、加粗  都不能有

导入数据时要把excel文件打开

3、sql关键语法

DDL数据定义语言  CREATE DROP ALTER  针对数据库、表

DML数据操纵语言 SELECT INSERT UPDATE DELETE 查询变更表中的记录

DCL数据控制语言 COMMIT ROLLBACK  确认、取消对数据库中数据的更改

注：sql里用的是英文的单引号

• 单表查询

SELECT [列] FROM [表];            查询单列
SELECT [列1],[列2] FROM [表];     查询多列
SELECT * FROM [表];               查询所有列

设置别名-AS （重新命名或者新生成的列,对列使用或者表名使用）
SELECT [列1], [列2] AS [别名] FROM [TABLE]；
SELECT tb.[列1], tb.[列2] FROM [biaobiaobiao] AS tb；

SELECT id, prod_name AS name FROM prod;
SELECT tb.id, tb.price FROM prod_information AS tb；


添加常数 --添加特定的值
SELECT '常数' AS [列名] FROM [TABLE];


四则运算
SELECT [四则运算表达式] FROM [TABLE];
SELECT inprice, saleprice, saleprice-inprice AS profit FROM prod;

字符拼接-CONTCAT 将多个字段拼接为一个字段
SELECT CONTCAT([列1], [列2]) AS new FROM [TABLE];

SELECT *, CONTCAT(name,'的重量是', weight) AS new FROM prod;



指定拼接符-  CONCAT_WS   多字段拼接，字段之间使用相同的拼接符
SELECT CONTCAT_WS('拼接符'，[列1], [列2],...) AS new FROM [TABLE];

SELECT CONCAT_WS('+',name, weight, price) AS new FROM prod;


内容去重 DISTINCT  取出不重复的列值
SELECT DISTINCT [列名] FROM [TABLE];

结果排序 ORDER BY  对结果进行排序
ORDER BY [列名];                        单列排序
ORDER BY [列名1], [列名2],...;          多列排序  如果某列升序某列降序要分别指明
ORDER BY [列位置1], [列位置2],...;      列位置排序（某列在第几列，用数字代替，第二列用2）

升序 ASC
降序 DESC

对中文排序时 ORDER BY CONVERT(prod_name USING gbk);   转成gbk格式之后就能按拼音字母排序了

过滤-WHERE  过滤条件

常规运算符 = < > != <= >=
计算表达式 列与常数，列与列

空值处理 IFNULL(列名，0)
SELECT * FROM tb WHERE IFNULL(price,0) <= 10;  把空值处理为0

数值过滤 BETWEEN...AND... 简化取值范围的书写，两端均包含
SELECT * FROM tb WHERE price BETWEEN 0 AND 10;

空值过滤  IS NULL   异常值处理
SELECT * FROM tb WHERE price IS NULL;

非空值处理 IS NOT NULL
SELECT * FROM tb WHERE price IS NOT NULL;

LIKE 模糊查询
通配符 下划线 _    WHERE [列] LIKE '..._...'    知道字符的数目、字符的位置
SELECT * FROM prod WHERE name LIKE '李_狗';   _ 一个下划线代替一个字符


通配符 百分号 %  WHERE [列] LIKE '...%...'   不知道字符的数目 但是知道字符的位置
SELECT * FROM prod WHERE name LIKE '李%';     %一个百分号代替任意个字符（包含0）

转义字符\ 

且操作--AND  同时满足：WHERE [条件1] AND [条件2]...;    满足条件的结果去交集
或操作--OR  满足一个即可：WHERE [条件1] OR [条件2]...;  满足条件的结果并在一起
组合操作 建议加括号  WHERE [条件1] AND ([条件2] OR [条件3])...;   先算括号里的，在接着算w外面的


取值限制 IN --明确而不连续的值  
WHERE [字段] IN (值1,值2...);
SELECT * FROM prod WHERE name IN ('张三', '李四','王五');  

否定条件 NOT --否定一个或多个过滤条件，
WHERE NOT [条件1];
SELECT * FROM prod WHERE NOT name ='张三';  

• 数据类型

数值型：
整型 INTERGER 只能存储整数  INTERGER(10)
DEMICAL  储存固定位数的小数  Decimal(4,2)  长度为4  小数位2位  多出位数四舍五入
FLOAT/DOUBLE/REAL 存储不固定位数的小数  FLOAT(5，3) 长度为5  小数位3位  多出位数五舍六入

文本型
CHAR 定长字符串 CHAR(10) 不超过10位字符串，不足十位空位占位
VARCHAR 可变场字符串   VARCHAR(100)  100长度以内都行

日期型：
DATE 日期型 Y-M-D
TIME 时间型 H:I:S
DATETIME 日期型 Y-M-D H:I:S

• 函数

参数为数值型：绝对值ABS  平方根SQRT 指数函数EXP 四舍五入ROUND 圆周率PI
文本处理函数： 字符长度CHAR_LENGTH 字节数LENGTH 去除左边空值LTRIM 去除右边空值RTRIM 大写字母转换UPPER 小写字母转换LOWER
日期处理函数：获取具体日期段YEAR/MONTH/DATE 获取具体时间段HOUR/MINTUE/SECOND 获取月份名称MONTHNAME 获取当前日期时间NOW 获取当前日期CURDATE 获取当前时间CURTIME 时间增加DATE_ADD 时间减少DATE_SUB

聚合函数
计数函数 COUNT(*)/COUNT(1)  计算指定行的数目
         COUNT(col)          非null行的数目
         COUNT(DISTINCT col) 对行去重计数

求和函数 SUM(col)   指定列的总和
均值函数 AVG(COL)   指定列非空值的平均值
最大值函数 MAX(COL) 指定列的最大值 
最小值函数 MIN(COL) 指定列的最小值

数据分组 GROUP BY：
结果中一行代表一个分组
SELECT AVG(sale_price) FROM table GROUP BY weight;  // 找出weight相同的数据进行分组，在对每组的sale_price求平均值

SELECT AVG(sale_price) FROM table WHERE weight IN (100,150) GROUP BY weight; //先找出where过滤条件里weight IN (100,150)的所有数据，在对数据用weight进行分组，weight相同的数据为一组，求出每组里面的sale_price求平均值

HAVING  对查询出来的结果进行过滤:
SELECT name, AVG(sale_price)
FROM prod
GROUP BY name
HAVING AVG(sale_price)>15    

• 多表查询

将查询结果用在不同的位置
SELECT [列]
FROM T1
WHERE X IN (
            SELECT [列]
            FROM T2
            WHERE [条件]);

SELECT [列]
FROM (
            SELECT [列]
            FROM T2
            WHERE [条件])
WHERE [条件];

SELECT [列], (
            SELECT [列]
            FROM T2
            WHERE [条件])
FROM T1
WHERE [条件];

表的联结
内部联结 INNER JOIN   必须有共同的列，结果为所有表的所有列、共同列重复的行
SELECT [列]
FROM T1,T2        
WHERE T1.a=T2.b

外部联结  
左外部联结 包含左边表的所有行 LEFT OUTER JOIN
又外部联结 包含右边表的所有行 RIGHT OUTER JOIN

组合查询 
SLECT...
UNION
SELECT...    两个语句的结果合并在一起

• 增删改

数据插入 INSERT INTO：
INSERT INTO [表] VALUES(每个字段的值);
INSERT INTO [表](列1，列8) VALUES(列1的值，列8的值);

INSERT INTO [表](列A，列B) 
SELECT...
FROM...
WHERE...);   把查询结果插入

数据更新 UPDATE
UPDATE 表名
SET 字段=值
WHERE 过滤条件;

删除数据 DELETE
DELETE FROM  表名
WHERE...                 -- 删除表中的数据

TRUNCATE TABLE 表名  ---删除表

事物管理：管理必须成批执行的SQL操作，使之完全执行或者完全不执行

COMMIT:将SQL语句的执行结果写入数据库表中

ROLLBACK：回滚，撤销SQL语句的执行结果

SAVEPOINT：保留点，时间点的备份，可以对其发布回退