提交学习笔记专用

16 KiB

Raw Permalink Blame History

10.29MySQL学习笔记

1.分类

DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）
DML: 数据操作语言，用来对数据库表中的数据进行增删改
DQL: 数据查询语言，用来查询数据库中表的记录
DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限

2.数据库操作

2.1查看数据库

# 查看所有的数据库
mysql> SHOW DATABASES;

3.表操作（DDL）

DDL是对表的结构进行操作

3.1数据类型

3.1.1 数值

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
DOUBLE	8 字节	（-1.797E+308,-2.22E-308）	(0,2.22E-308,1.797E+308)	双精度浮点数值
DOUBLE(M,D)	8个字节，M表示长度，D表示小数位数	同上，受M和D的约束 DOUBLE(5,2) -999.99-999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	DECIMAL(M,D)	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值，和钱相关，不会出现精度缺失的问题

3.1.2字符串

类型	大小	用途
CHAR	0-255字符	定长字符串CHAR(10)10个字符
VARCHAR	0-65535 字节	变长字符串VARCHAR(10)10个字符
BLOB（binary large object）	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据

3.1.3、日期时间类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3.2创建表

CREATE TABLE [IF NOT EXISTS] 表名（
	列名 数据类型 [约束],
	列名 数据类型 [约束],
	列名 数据类型 [约束]       //最后一列的末尾不加逗号
）[CHARSET=utf8];			//可根据需要指定表的字符编码集

3.3查看表

# 查看当前数据库中所有表名称
SHOW TABLES;
# 查看指定表的创建语句
SHOW CREATE TABLE 表名;
# 查看表结构
DESC 表名;

3.4修改表

# 添加列
ALTER TABLE 表名 ADD (
    列名 列类型,
    列名 列类型
)

# 修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据), 修改表中的某列时，也要写全列的名字，数据类型，约束
ALTER TABLE 表名 MODIFY 列名 列类型;   

# 修改列名, 在给定列新名称时，要指定列的类型和约束
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

# 删除列, 删除列时，每次只能删除一列
ALTER TABLE 表名 DROP 列名;

# 修改表名称
ALTER TABLE 原表名 RENAME TO 新表名;
ALTER TABLE 原表名 RENAME 新表名;

3.5删除表

DROP TABLE 表名;

4.DML

DML就是对表的内容进行操作

4.1插入

基本格式：

INSERT INTO 表名(列名1, 列名2, ...) VALUES(列值1, 列值2, ...);
或者
INSERT INTO 表名 VALUES(列值1, 列值2, ...);

4.2修改

# WHERE条件是可选的, 如果没有条件, 就修改所有记录, 多数时候我们都加上WHERE条件
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ...[WHERE 条件]

4.3删除

# WHERE条件是可选的, 如果没有条件, 就删除所有记录, 多数时候我们都加上WHERE条件
DELETE FROM 表名 [WHERE 条件];

4.4约束

4.4.1主键约束

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY,
	列名 数据类型,
	列名 数据类型
);

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型,
	列名 数据类型,
	列名 数据类型,
    PRIMARY KEY(设置为主键的列名)
);

# 修改表时指定主键
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 PRIMARY KEY;

# 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

主键自增长：

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
	列名 数据类型,
	列名 数据类型
);

# 修改表时设置主键自增长
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY AUTO_INCREMENT;

# 修改表时删除主键自增长
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

4.4.2唯一约束

# 创建表时指定唯一约束
CREATE TABLE 表名(
    列名 数据类型 UNIQUE,
	列名 数据类型,
	列名 数据类型
);

# 修改表时设置唯一约束
ALTER TABLE 表名 MODIFY 列名 列类型 UNIQUE;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 UNIQUE;

# 删除唯一约束
ALTER TABLE 表名 DROP INDEX name;

4.4.3非空约束

# 创建表时指定非空约束
CREATE TABLE 表名(
    列名 数据类型 NOT NULL,
	列名 数据类型,
	列名 数据类型
);

# 修改表时设置非空约束
ALTER TABLE 表名 MODIFY 列名 列类型 NOT NULL;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 NOT NULL;

# 删除非空约束
ALTER TABLE 表名 MODIFY 列名 列类型;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

4.4.4默认值

# 创建表时指定默认值
CREATE TABLE 表名(
    列名 数据类型 DEFAULT 默认值,
	列名 数据类型,
	列名 数据类型
);

# 修改表时设置默认值
ALTER TABLE 表名 MODIFY 列名 列类型 DEFAULT 默认值;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 DEFAULT 默认值;

# 删除默认值
ALTER TABLE 表名 MODIFY 列名 列类型;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

4.4.5外键约束

5.DQL

查询操作：所有操作中使用最频繁

5.1单表查询

# 查询指定列
SELECT 列1 [, 列2, ...列N] FROM 表名;
#去重查询
SELECT DISTINCT 列1 [, 列2, ...列N] FROM 表名;

5.2列运算

# 列可以进行加、减、乘、除运算
如：SELECT ename, sal*1.5 FROM emp;
# 格式：列名 AS 别名
# AS也可以省略，格式：列名 别名
# 查询所有员工的姓名和总工资（工资加奖金, 如果奖金为NULL则按照奖金为0进行运算）
SELECT ename AS '姓名', sal+IFNULL(comm, 0) AS '总工资' FROM emp;
SELECT ename '姓名', sal+IFNULL(comm, 0) '总工资' FROM emp;
# 除了可以给列起别名，也可以给表起别名，在多表查询中会使用到为表起别名

5.3条件查询

主要使用where连接

# 查询部门编号为20的所有员工的信息
SELECT * FROM emp WHERE deptno=20

# 查询工种为工程师的所有员工的信息
SELECT * FROM emp WHERE job='工程师'

# 查询有奖金的所有员工的信息
SELECT * FROM emp WHERE comm IS NOT NULL and comm <> 0;
SELECT * FROM emp WHERE comm IS NOT NULL and comm != 0;

5.4模糊查询

"_"匹配一个任意字符，只匹配一个字符而不是多个

"%"匹配0~N个任意字符

模糊查询需要使用运算符：LIKE

# 查询姓“周”的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '周%';

# 查询姓名中包含“杰”的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '%杰%';

# 查询姓“周”并且姓名只有三个字的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '周__';

5.5排序

对查询的结果进行排序

排序分成升序（ASC）和降序（DESC），可以使用多列作为排序条件

排序使用关键字ORDER BY

# 排序
# 规则：列名 升序/降序
SELECT 列1, 列2, 列3 FROM 表名 WHERE 条件 ORDER BY 规则1, 规则2,....,规则n　

# 查询所有员工信息，按照工号升序排列
SELECT * FROM emp ORDER BY empno ASC;
# 如果是升序，ASC可以省略
SELECT * FROM emp ORDER BY empno;
多个排序规则就按顺序执行
SELECT * FROM emp ORDER BY sal ASC, empno DESC;

5.6聚合函数

函数功能

COUNT 计算个数

MAX 最大值

MIN 最小值

AVG 平均值

SUM 和

函数	功能
COUNT	计算个数
MAX	最大值
MIN	最小值
AVG	平均值
SUM	和

例如：

# 查询公司员工个数
SELECT count(1) FROM emp;
SELECT count(*) FROM emp;

# 查询公司最高工资
SELECT MAX(sal) FROM emp;

# 查询公司最低工资
SELECT MIN(sal) FROM emp;

# 查询公司所有员工工资和
SELECT SUM(sal) FROM emp;

# 查询公司员工工资的平均值
SELECT AVG(sal) FROM emp;

5.7分组查询

主要使用GROUP BY关键字

例如：

# 查询每个工种的平均工资
SELECT job, AVG(sal) FROM emp GROUP BY job;

# 查询每个工种的员工数量
SELECT job, COUNT(1) FROM emp GROUP BY job;

分组查询设置条件的话，分组前的条件使用where,分组后的条件使用having

# 查询工资大于15000的员工的工种，以及工种的平均工资
SELECT job, AVG(sal) FROM emp WHERE sal>15000 GROUP BY job;

# 查询工资大于15000的员工的工种，以及工种的平均工资，只显示超过两人的工种
SELECT job, AVG(sal) FROM emp WHERE sal>15000 GROUP BY job HAVING COUNT(*)>=2;

5.8Limit子句

主要用于分页查询

# 语法
SELECT 列名 FROM 表名 LIMIT 起始行，查询行数;

# 查询员工表中前五名员工的所有信息
# 起始行是从 0 开始，代表了第一行
SELECT * FROM emp LIMIT 0, 5;

#查询员工表中从第4条开始，查询10行
SELECT * FROM emp LIMIT 3,10;

5.9 多表查询

5.9.1合并结果集

合并结果集就是把两个select语句的查询结果合并到一起，结果集就是一个表格。

要求：被合并的两个结果：列数必须相同。

# UNION：去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
# UNION ALL：不去除重复记录
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

5.9.2内连接

# 方式1(MySQL特有，不符合SQL标准)
SELECT 列名 FROM 表1, 表2 WHERE 表1.列名 条件运算符 表2.列名 [AND 条件];
# 方式2(符合SQL标准)
SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列名 条件运算符 表2.列名 [WHERE 条件];

# 列出员工的姓名和部门名称
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

上面的查询只能查询出拥有部门的员工和拥有员工的部门，没有部门的员工和没有员工的部门是查询不到的。如果要将所有的员工和部门都查询出来需要使用外连接。

5.9.3外连接

结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

5.9.3.1左外链接

左外连接是以左表为主表，去关联右表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

# 语法
SELECT 列名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 左表.列名  条件运算符 右表.列名 [WHERE 条件];

# 列出员工的姓名和部门名称, 包括没有部门的员工
SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

# 列出员工的姓名和部门名称, 包括没有员工的部门
SELECT e.ename, d.dname FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;

5.9.3.2右外连接

右外连接是以右表为主表，去关联左表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

# 语法
SELECT 列名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 左表.列名 条件运算符 右表.列名 [WHERE 条件];

# 列出员工的姓名和部门名称, 包括没有员工的部门
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

# 列出员工的姓名和部门名称, 包括没有部门的员工
SELECT e.ename, d.dname FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;

5.9.3.3 全外连接

完全连接左表和右表中所有行，当某行数据在另一个表中没有匹配时，则另一个表的选择列值为NULL。

# 语法
SELECT 列名 FROM 左表 FULL [OUTER] JOIN 右表 ON 左表.列名 条件运算符 右表.列名 [WHERE 条件]

MySQL不支持这种语法，可以使用合并结果集进行模拟全外连接。

SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

5.10子查询

子查询出现的位置：

WHERE后，作为条件的一部分；

FROM后，作为被查询的一条表。

5.10.1列子查询

 SELECT id,stu_id,`name`,(SELECT `class_name`FROM t_class WHERE id = class_id ) class_name,class_id,sex,birthday FROM t_student  LIMIT 10;

5.10.2表子查询

SELECT id,stu_id,`name`,pinyin,sex,birthday FROM (SELECT *FROM t_student WHERE sex = '女')t1;

5.10.3where之后

   -- 等号（不等号）子查询 要求子查询结果必须是一行一列
       SELECT id,stu_id,`name`,pinyin,sex,birthday FROM t_student 
       WHERE class_id = (SELECT id FROM t_class WHERE class_name = '080503-JAVA'); 
       SELECT id,stu_id,`name`,pinyin,sex,birthday FROM t_student 
       WHERE class_id != (SELECT id FROM t_class WHERE class_name = '080503-JAVA'); 
       -- 大于号子查询，小于号子查询
      SELECT id,stu_id,`name`,pinyin,sex,birthday,class_id FROM t_student 
       WHERE class_id > ALL(SELECT id FROM t_class WHERE class_name LIKE '%JAVA%'); 
       
       SELECT id,stu_id,`name`,pinyin,sex,birthday,class_id  FROM t_student 
       WHERE class_id > ANY(SELECT id FROM t_class WHERE class_name = '080503-JAVA');
       -- 4.in 和not in 子查询
        SELECT id,stu_id,`name`,pinyin,sex,birthday,class_id  FROM t_student WHERE id in(1,2,3);
        SELECT id,stu_id,`name`,pinyin,sex,birthday,class_id  FROM t_student 
        WHERE id in(SELECT id FROM t_class WHERE  class_name LIKE '%JAVA%');

5.10.4exists

      SELECT id,stu_id,`name`,pinyin,sex,birthday,class_id  FROM t_student 
       WHERE EXISTS(SELECT id From t_class WHERE class_name LIKE '%JAVA%')

16 KiB Raw Permalink Blame History

10.29MySQL学习笔记

1.分类

2.数据库操作

2.1查看数据库

3.表操作（DDL）

3.1数据类型

3.1.1 数值

3.1.2字符串

3.1.3、日期时间类型

3.2创建表

3.3查看表

3.4修改表

3.5删除表

4.DML

4.1插入

4.2修改

4.3删除

4.4约束

4.4.1主键约束

4.4.2唯一约束

4.4.3非空约束

4.4.4默认值

4.4.5外键约束

5.DQL

5.1单表查询

5.2列运算

5.3条件查询

5.4模糊查询

5.5排序

5.6聚合函数

5.7分组查询

5.8Limit子句

5.9 多表查询

5.9.1合并结果集

5.9.2内连接

5.9.3外连接

5.9.3.1左外链接

5.9.3.2右外连接

5.9.3.3 全外连接

5.10子查询

5.10.1列子查询

5.10.2表子查询

5.10.3where之后

5.10.4exists

16 KiB

Raw Permalink Blame History