javaee学习日记之java基础之Sql语句和MySql方言

1. DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
   创建、删除、修改：库、表结构

   1. 数据库查看所有数据库：SHOW DATABASES切换（选择要操作的）数据库：USE 数据库名创建数据库：CREATE DATABASE tests(数据库名)删除数据库：DROP DATABASE tests(数据库名)修改数据库编码：ALTER DATABASE tests(数据库名) CHARACTER SET utf8(编码)2. 数据类型(列类型)int：整型double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，如111.11decimal：浮点型(不会出现精度缺失问题)char：固定长度字符串类型varchar：可变长度字符串类型text(clob)：字符串类型；blob：字节类型；date：日期类型，格式为：yyyy-MM-dd；time：时间类型，格式为：hh:mm:sstimestamp：时间戳类型；3. 表(必须先进入数据库)* 创建表：（char与varchar,要写长度(一定)如varchar(10)）  CREATE TABLE 表名(    列名 列类型,    列名 列类型,    ...    列名 列类型  );* 查看当前数据库中所有表名称：SHOW TABLES;* 查看指定表的创建语句：SHOW CREATE TABLE 表名;* 查看表结构：DESC 表名;* 删除表：DROP TABLE 表名;* 修改表：ALTER TABLE 表名  > 修改之添加列：    ALTER TABLE 表名 ADD (      列名 列类型,      列名 列类型,      ...    );  > 修改之修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据)：ALTER TABLE 表名 MODIFY 列名 列类型;  > 修改之修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;  > 修改之删除列：ALTER TABLE 表名 DROP 列名;  > 修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;2. DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；   增、删、改：表记录

1. 插入数据

   • INSERT INTO 表名(列名1,列名2, …) VALUES(列值1, 列值2, …);
     (如果没有指定值，则为null,如果指定值与列类型不匹配（会出问题的！）)
   • INTERT INTO 表名 VALUES(列值1, 列值2);
     (值必须和列的顺序，数目，类型相同)

2. 修改数据

   • UPDATE 表名 SET 列名1=列值1, 列名2=列值2, … [WHERE 条件]
     条件必须成立
     条件必须是一个boolean类型的值或表达式如 age = 15;
     运算符：=、!=、<>、>、<、>=、<=、BETWEEN…AND、IN(…)、IS NULL、NOT、OR、AND
     判断为null
     WHERE NAME IS NULL;
     判断为不为null
     WHERE NAME IS NOT NULL;

AND (与)
OR(或)
BETWEEN ‘JLJFA’ AND ‘onon’(如需以字母顺序显示介于 “JLJFA”（包括）和 “onon”（不包括）之间的人)

1. 删除数据
   
   • DELETE FROM 表名 [WHERE 条件];
   • TRUNCATE TABLE 表名：TRUNCATE是DDL语句（删除了还不存在了(清空数据)）

---

字符数据用”包起来；

// 插入所有列
INSERT INTO stu(
name,age,sex
)
VALUES(
‘tom’,15,’man’
);

// 插入部分列，没有指定的列默认为NULL值
INSERT INTO stu(
name,sex
) VAKLUES(
‘jom’,’woman’
)

// 不给出插入列，那么默认为插入所有列！值的顺序要与创建表时列的顺序相同
INSERT INTO stu VALUES(
‘ITCAST_0003’, ‘wangWu’, 82, ‘female’
);

---

更新数据
UPDATE

UPDATE testss(表名) SET name = ‘aa’,age = 15 WHERE name = ‘jom’;
//更新name为jom的用户数据

3. DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

一般一个项目一个数据库，对应的用户名和密码，只能操作指定数据库。

1. 创建用户
   

   • CREATE USER 用户名@IP地址 IDENTIFIED BY ‘密码’;
     

   用户只能在指定的IP地址上登录

2. CREATE USER 用户名@’%’ IDENTIFIED BY ‘密码’;
   用户可以在任意IP地址上登录

3. 给用户授权
   

   • GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP地址
     

   权限、用户、数据库
   给用户分派在指定的数据库上的指定的权限
   例如；GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydbs.* TO users@localhost;
   

   • 给users用户分派在mydbs数据库上的create、alter、drop、insert、update、delete、select权限

4. GRANT ALL ON 数据库.* TO 用户名@IP地址;
   给用户分派指定数据库上的所有权限

5. 撤销授权
   

   • REVOKE 权限1, … , 权限n ON 数据库.* FROM 用户名@IP地址;
     

   撤消指定用户在指定数据库上的指定权限
   例如；REVOKE CREATE,ALTER,DROP ON mydbs.* FROM users@localhost;
   

   • 撤消users用户在mydbs数据库上的create、alter、drop权限

6. 查看权限
   

   • SHOW GRANTS FOR 用户名@IP地址
     

   查看指定用户的权限

7. 删除用户

   • DROP USER 用户名@IP地址

4. DQL*****（Data Query Language）：数据查询语言，用来查询记录（数据）。

一、 基本查询

1. 字段(列)控制
   1) 查询所有列
   SELECT * FROM 表名;
   SELECT * FROM test;
   –> 其中“*”表示查询所有列

2) 查询指定列
SELECT 列1 [, 列2, … 列N] FROM 表名;
SELECT name,age,sex FROM 表名;

3) 完全重复的记录只显示一次

SELECT DISTINCT * FROM test(表名);
//如果存在完全相同的数据则只显示一次

4) 列运算
I 数量类型的列可以做加、减、乘、除运算
SELECT age*1.5 FROM test;
SELECT age+sal FROM test;

II 字符串类型可以做连续运算
SELECT CONCAT(‘$’, sal) FROM emp;

III 转换NULL值

SELECT IFNULL(age, 0)+1000 FROM test;
将age列中为NULL的数据当0来计算。

IV 给列起别名
SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;
AS(可以省略)

1. 条件控制
   1) 条件查询
   与前面介绍的UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录。
   SELECT * FROM TEST WHERE AGE = 15 AND AGE = 20;//获取test表中的age为15或age为20的结果集
   SELECT * FROM TEST WHERE AGE BETWEEN 18 AND 30;//获取test表中的age从18到30之间（不包括30）的结果集
   SELECT * FROM test WHERE name IN (‘张三’, ‘李四’);//获取test表中的name为张三或李四的结果集
   2) 模糊查询
   LIKE，其中_匹配一个任意字符
   使用%匹配0个或多个字符
   SELECT * FROM test WHERE name LIKE ‘李_’;//匹配名字有两个字，姓李的人的结果集
   SELECT * FROM test WHERE name LIKE ‘%怀%’;//匹配名字中有怀的人

二、排序
1) 升序
SELECT * FROM test ORDER BY age ASC;
–> 按age排序，升序！(默认ASC)
2) 降序
SELECT * FROM test ORDER BY age DESC;
按age降序DESC(不能省)
3) 使用多列作为排序条件
SELECT * FROM WHERE emp ORDER BY sal ASC, comm DESC;
–> 使用sal升序排，如果sal相同时，使用comm的降序排

三、聚合函数
聚合函数用来做某列的纵向运算。
1) COUNT
SELECT COUNT(*) FROM emp;
–> 计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;
–> 计算emp表中comm列不为NULL的记录的行数
2) MAX
SELECT MAX(sal) FROM emp;
–> 查询最高工资
3) MIN
SELECT MIN(sal) FROM emp;
–> 查询最低工资
4) SUM
SELECT SUM(sal) FROM emp;
–> 查询工资总和
5) AVG
SELECT AVG(sal) FROM emp;
–> 查询平均工资

四、分组查询
分组查询是把记录使用某一列进行分组，然后查询组信息。
例如：查看所有部门的记录数。
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
–> 使用deptno分组，查询部门编号和每个部门的记录数
SELECT job, MAX(SAL) FROM emp GROUP BY job;
–> 使用job分组，查询每种工作的最高工资

组条件
以部门分组，查询每组记录数。条件为记录数大于3
SELECT deptno, COUNT() FROM emp GROUP BY deptno HAVING COUNT() > 3;

五、limit子句(方言)
LIMIT用来限定查询结果的起始行，以及总行数。
例如：查询起始行为第5行，一共查询3行记录
SELECT * FROM emp LIMIT 4, 3;
–> 其中4表示从第5行开始，其中3表示一共查询3行。即第5、6、7行记录。

多表查询

1. 分类：
   
   • 连接查询
   • 子查询

合并结果集
* 要求被合并的表中，列的类型和列数相同
* UNION，去除重复行
* UNION ALL，不去除重复行

SELECT * FROM cd
UNION ALL
SELECT * FROM ab;

连接查询
1. 分类
* 内连接
* 外连接

左外连接
右外连接
全外连接(MySQL不支持)
* 自然连接（属于一种简化方式）

1. 内连接

   • 方言：SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx
   • 标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
   • 自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
   • 内连接查询出的所有记录都满足条件。

2. 外连接
   

   • 左外：SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
     

   左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为NULL

3. 左外自然：SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
4. 右外：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
   右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为NULL
5. 右外自然：SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
6. 全链接：可以使用UNION来完成全链接

子查询
　　：查询中有查询（查看select关键字的个数！）
1. 出现的位置：
* where后作为条件存在
* from后作为表存在(多行多列)

1. 条件
   单行单列：SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
   多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)
   单行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)
   多行多列：SELECT * FROM 表1 别名1 , (SELECT ….) 别名2 WHERE 条件

====================================================

笛卡尔积
{a, b, c} {1,2}
{a1, a2, b1, b2, c1, c2}

====================================================
“`
约束
主键约束(本表键(不可重复，唯一))
外键约束（可以有多个，是外表的主键(必须在外表中存在)）
创表规范(三范式)
关系模型：一对一，一对多，多对一。（也可以有中间表连接关系）
视图：(更方便的进行数据查询)
索引：(快速进行数据查询)