MySQL之入门

最新推荐文章于 2022-08-13 12:44:31 发布

青年痴呆

最新推荐文章于 2022-08-13 12:44:31 发布

阅读量1.1k

点赞数 1

分类专栏： selfLearning 文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_25358293/article/details/112316124

版权

selfLearning 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL的基本操作

服务的启动和停止

方式一：计算机–右击管理–服务

方式二：通过管理员服务运行
net start 服务名 启动服务
net stop 服务名 停止服务

MySQL服务的登入和登出

方式一：通过MySQL自带的客户端（仅限管理员）

方式二：通过DOS
登录： mysql [-h 主机名 -p 端口号] -u 用户名 -p密码
退出： EXIT或者ctrl+c

MySQL的常用命令

查看所有的数据库
SHOW DATABASES；
打开指定的库
USE 库名
查看当前库的所有表
SHOW TABLES
查看其他库的所有表
SHOW TABLES form 库名

创建表

CREATE TABLE 表名(
    列名 列类型,
    列名 列类型,
    ...  ...
);

查看表结构
DESC 表名;
查看服务器版本
方式一：登录到MySQL服务端
SELECT version()
方式二：在DOS下
mysql --version或者mysql --V

MySQL的语法规范

不区分大小写，但建议关键字大写，表名、列名小写
每条命令最好用 ; 结尾
每条命令根据需要，可以进行缩进或者换行
注释
1. 单号注释： #加注释文字
  –注释文字
2. 多行注释： /* 注释文字 *

DQL语言的学习

基础查询

介绍

语法
SELECT 查询列表 FROM 表名;
（F12快捷键格式化）
特点
1. 查询列表可以是：表中的字段、常量值、表达式、函数
2. 查询的结果是一个虚拟的表格

功能使用

查询表中的单个字段
SELECT last_name FROM empoyee;
查询表中的多个字段
SELECT last_name,firt_name,email FROM empoyee;
查询表中的所有字段
SELECT * FROM 表名 （*即表示匹配所有）
查询常量值
SELECT 100; SELECT 'john';
查询表达式
SELECT 100*9; SELECT 100%98;
查询函数
SELECT fun();
为字段起别名（提高可读性，便于理解）

（如果需要查询的字段有重名的情况，使用重名可以区分开来）

使用AS关键字
SELECT 100%98 AS 结果; SELECT lASt_name AS 姓;
使用空格
SELECT last_name 姓,first_name 名 FROM 表名;

==注：==若别名中有空格，则请将整个别名用 ` 括起来，表示是一个字符串
8. 去重（搜索到的重复结果）

添加关键字DISTINCT
SELECT DISTINCT employee_id FROM employees;
+号的作用（在MySQL中的+号仅有运算符的功能）
SELECT 100+90; 运算符
SELECT '123'+90; 其中一方为字符型，则会试图将字符型转换成数值型。若转换成功，则继续做加法运算。若失败，则将字符型换成0
SELECT null+10; 一方为null，则结果为null

注： +号不能用于连接字符串（可以用 CONCAT()函数实现此功能）

注意事项

查询前先进入目标库
可以用 “ ` ”来表示是一个字符串（可将关键字变成字符串）

条件查询

介绍

语法：

SELECT
	查询列表
FROM
	表名
WHERE
	筛选条件;

分类：
1. 按条件运算符筛选
  条件运算符：> < = != <>(不等于) >= <=
2. 按逻辑表达式查询（主要用于连接条件表达式）
  逻辑表达式：&& || ! and or not
3. 模糊查询
  like 、 between and 、 in 、 is null

功能使用

按条件表达式筛选

SELECT
    last_name,
    department_id
FROM
    employees
   WHERE
    department_id <> 90; # !=也支持

按逻辑表达式筛选

SELECT
	last_name,
	salary,
FROM
	employees
WHERE
	salary>=10000
AND 
	salary<=20000;
### 初学时可将and、or、not写在一行，以方便理解

模糊查询

like（一般和通配符搭配使用）

通配符：
	% 任意多个字符
	_ 任意单个字符
	\ 转义符
	(推荐用 ESCAPE 关键字任意指定转义符)

#案例：查询员工名中包含字符'a'的员工信息
SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '%a%'; # %号代表任意字符

between and（两边两个连接词也被包含在范围中）

# 案例：查询员工标号在 100~120 的员工信息
SELECT
	*
FROM
	emplpyees
WHERE
	employee_id BETWEEN 100 AND 120;
	### between and 的连接词是 左小右大！

in（包判断某字段的值是否属于in列表）

#案例：查询员工的工种编号是 IT_PROG、AD_VP的员工名、工种编号
SELECT
	last_name,
	job_id,
FROM
	employees
WHERE
	job_id IN( 'IT_PROG','AD_VP');
	### in列表的值类型必须一致或者兼容！	
	### in列表里的值不满足通配符运算！

is null（仅用来判断NULL值）

#案例：查询没有奖金（奖金率为null）的员工名和奖金率
SELECT
	last_name,
	commission-pct
FROM
	employees
WHERE
	commission_pct IS NULL;
	## 若查询有奖金的，则可写成 IS NOT NULL

==Ps：==安全等于<=>（可读性较低）
commission_pct <=> NULL;
salary <=> 12000;

排序查询

介绍

语法：

SELECT
	查询列表
FROM
	表
[WHERE 筛选条件]
ORDER BY
	排序列表 [ASC|DESC];
	# ASC 表示升序（默认ASC）

例：SELECT * FROM employees ORDER BY salary DESC;

功能使用

#案例：查询部门编号>=90的团工信息，按入职时间的先后排序
SELECT *
FROM employees
WHERE department_id >= 90
ORDER BY hiredate ASC;

常见函数

单行函数
分组函数：主要用于统计使用

单行函数

字符函数

length：获取字符的字节长度（UTF8中一个中文占3个字节长度）
SELECT LENGTH('John');
concat：拼接字符
SELECT CONCAT(last_name,' ',first_name) AS 姓名;
upper、lower：转大小写字符
SELECT UPPER('john');
SELECT LOWER('JOHN');

substr（substring）：截取字符
==注意：==在MySQL中的索引是从 1 开始

SELECT SUBSTR('好好学习天天向上',5);
# 天天向上
SELECT SUBSTR('好好学习天天向上',3,4);
# 学习天天（ 3是索引，4是字符长度 ）

instr：查询字符第一次出现的索引，未找到则返回 0
SELECT INSTR('好好学习天天向上','学习');

trim：删除字符左右的指定字符（默认删除空格）

SELECT TRIM('    哈哈    ');
# 返回'哈哈'
SELECT TRIM('a' FROM 'aaa哈a哈aaa');
# 返回'哈a哈'
SELECT TRIM('aa' FROM 'aaa哈a哈aaa');
# 返回 'a哈a哈a'

lpad、rpad：从左（右）填充字符至指定长度

SELECT LPAD('好好学习',8,'*');
# 返回'****好好学习'
SELECT LPAD('好好学习',2,'*');
# 返回'好好'

replace：替换
SELECT REPLACE('好好学习天天向上','学习','钻研');

数学函数

round：四舍五入
SELECT ROUND('1.65');
ceil、floor：向上（向下）取整
SELECT CEIL(1.2);
SELECT CEIL(1.0);，返回1
truncate：截断
SELECT TRUNCATE(1.257,2);，返回 1.25
mod：取余（与算术运算符**%**相同）
SELECT MOD(10,3);
==注意：==取模的实质方法是mod(a,b) = a - a/b*b，返回值得正负只取决于a

日期函数

now：返回当前系统日期+时间
SELECT NOW();返回字符串’2020-04-28 13:26:25’
curdate：返回当前系统日期（不包含时间）
crutime：返回时间（不包含日期）
year、month、day、hour、minute、second：获取指定的部分
SELECT YEAR(NOW());
SELECT MONTH('2020-04-28');
datediff：得到两个日期的差值
SELECT DATEDIFF('2020-04-28','2020-04-01'); 返回28
str_to_date：将日期格式的字符转换成指定格式的日期
STR_TO_DATE('4-28-2020','%m-%d-%Y');，返回 ‘1999-09-13’
date_format：将日期转换成字符
DATE_FORMAT('2020/4/28','%Y年%m月%d日');，返回 ‘2020年04月28日’

格式符	功能
%Y	四位的年份
%y	两位的年份
%m	月份（01,02…）
%c	月份（1,2…）
%d	日（01,02…）
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（00,01…）
%s	秒（00,01…）

其他函数

select database();
select version();
select user();
show databases;
desc 表名;
show index from 表名;

流程控制函数

if 函数：效果同三目运算符
SELECT IF(10>5,'大','小');，返回 ‘大’

case 函数（一）：类似于 switch case

CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的(值1,)或(语句1;)
WHEN 常量2 THEN 要显示的(值2,)或(语句2;)
...
ELSE 要显示的(值n,)或(语句n;)
END

case 函数（二）：类似于 if … else if … else

CASE
WHEN 条件1 THEN 要显示的(值1,)或(语句1;)
WHEN 条件2 THEN 要显示的(值2,)或(语句2;)
...
ELSE 要显示的(值n,)或(语句n;)
END

分组函数

主要是用于统计使用，又称为聚合函数或统计函数或组数组
可以和 distinct 搭配使用，实现去重的作用
SELECT SUM(DISTINCT salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;

==注意：==分组函数会忽略 null 值

简单的使用

sum 求和、avg 平均值、max 最大值、min 最小值
SELECT SUM(salary) FROM employees;，返回salary的总和（返回一个值）

count 计算个数

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees; # --> 在 MYISM存储引擎下效率更高
# 一般可用来统计行数（个数）
SELECT COUNT(常量值) FROM employees; # --> 在INNODB储存引擎下，效率差不多。但效率比COUNT(字段)高
# 同样用来统计行数（通常常量值用1）

和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees; 得到的结果没有意义！！！
和分组函数一同查询的字段，要求是 group by 后面的字段

分组查询

介绍

通过 group by 语句将表中的数据分成若干组

SELECT 分组函数，查询列表 #列表要求出现在group by的后面
FROM 表
[WHERE 筛选条件]
[GROUP BY 分组的列表]
[ORDER BY 排序列表];

==注意：==查询列表必须特殊，要求是分组函数和 group by 后出现的字段

功能使用

简单的筛选

# 案例一：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

# 案例二：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id
ORDER By salary ASC;

添加复杂的筛选条件（对分组后的条件进行筛选）

# 查询每个工种有奖金的员工的最高工资＞12000的工种编号的最高工资
# ①查询每个工种有奖金的员工的最高工资
SELECT MAX(sakary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
# ②根据①的结果进行筛选,工资＞12000
HAVING MAX(salary) > 12000;

按表达式或函数分组

# 按照员工姓名的长度分组，查询每一组的员工个数，筛选员工个数＞5的有哪些
SELECT COUNT(*),LENGTH(last_name) AS len_name
FROM employees
GROUP BY LENGTH(last_name) # 一般放字段，不放函数。可以使用别名
HAVING COUNT(*) > 5;

按多个字段分组

# 查询每个部门的每个工种的员工的平均工资
SELECT AVG(salary), department_id,job_id
FROM employees
GROUP BY department_id,job_id;

总结

分组查询中的筛选条件可分为“ 分组前筛选 ”和“ 分组后筛选 ”
1. 分组函数做条件一定是放在 having 子句中
2. 能用分组前筛选的，就优先考虑分组前筛选
group by 子句支持单个字段分组，多个字段分组（多个字段直接用逗号隔空，没有顺序要求），表达式或函数分组（使用较少）
可以添加排序（放在语句最后）

连接查询

介绍

又称：多表查询。当查询的值设计多个表时，即可使用多表连接

△笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行
SELECT 'name',boyname FROM boys,beauty;

发生原因：没有有效的连接条件
解决办法：添加连接条件

SELECT 'name',boyname FROM boys,beauty
WHERE beauty.boysfriend_id = boys.id;

连接分类

按年代分类

sql92标准
特点：仅仅支持内联接
sql99标准【推荐】
特点：支持所有的内连接 + 外连接（左外、右外）+ 交叉连接

按功能分类(sql92)

#####内连接

等值连接

为表起别名

# 查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title  # 这里的job_id有歧义，需要用表名限定
FROM employees AS e,jobs j  # 使用别名
WHERE e.job_id = j.job_id

加入分组

# 查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;

三表连接

# 查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
ORDER BY last_name  ASC;

非等值连接

# 案例：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grade g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY salary D ESC;

自连接

# 案例：查询员工名以及上级的名字
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

按功能分类(sql99)

语法

SELECT 查询列表
FROM 表1 别名 
[连接类型] JOIN 表2 别名
ON 连接条件
[WHERE 筛选条件]
... ...

内连接( inner )

包含：等值连接、非等值连接、自连接

‘INNER’ 可以省略

# 案例：查询部门个数＞3的城市名和部门个数（分组+筛选）
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING 部门个数 > 3;

# 查询员工名、部门名、工种名，并按部门名降序（三表连接）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

#####外连接

介绍

一般用于查询 [ 一个表中有，另一个表中没有的 ] 值

左外连接( left [outer] )
右外连接( right [outer] )
全外连接( full [outer] )

######特点

外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示 NULL
外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
左外连接，left join 左边的是主表
右外连接， right join右边的是主表
全外连接 = 内连接结果 + 表1有但表2没有 + 表2有但表1没有

功能使用

# 案例：查询男朋友不在男神表 的女神名
SELECT b.name[,bo.*]
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;  # 一般选择判断 id，因为id作为主键不能为null

# 案例：查询没有员工的部门
SELECT d.*,e.employee_id
FROM departemnt d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

#全外连接（MySQL不支持）

#####交叉连接( cross )

即实现：笛卡尔乘积（表1有m行，表2有n行，结果有m*n行）

子查询

介绍

含义：出现在其他语句中的 select 语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：

按子查询出现的位置：
1. select后面：仅仅支持标量子查询
2. from后面：支持表子查询
3. where或having后面：支持 ★标量子查询（单行）、★列子查询（多行），也支持行子查询（使用较少）
4. exists后面（相关子查询）：表子查询
按结果集的行列数不同：
1. 标量子查询（结果集只有一行一列）
2. 列子查询（结果集只有一列多行）
3. 行子查询（结果集可以有一行多列）
4. 表子查询（结果集都行，一般为多行多列）

功能使用

####where或having后面

特点：

子查询一般放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用> < >= <= = <>
列子查询，一般搭配多行操作符使用in/not in 、 any|some 、 all
子查询的执行优于主查询的执行

#####标量子查询

（单行子查询）

# 案例：返回 job_id 与 141号 员工相同，salary 比 143号 员工多的员工的姓名，job_id 和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

# 案例：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

==注意：==子查询的结果必须是一行一列！

#####列子查询

（多行子查询）

搭配多行操作符使用in/not in 、 any/some 、 all

★in/not in ：等于（不等于）列表中的任意一个
any|some：和子查询返回的某一个值比较（一般用MIN MAX代替且可读性更高，所以使用较少）
all：和子查询返回的所有值比较（一般用MIN MAX代替且可读性更高，所以使用较少）

# 案例：返回 location_id 是 1400 或 1700 的部门中的所有员工
SELECT last_name
FROM employees
WHERE department_id IN (
	# location_id 是 1400 或 1700 的部门id
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

#####行子查询

（一列多行，或多行多列，使用较少）

# 案例：查询员工编号最小 并且 工资最高 的员工信息（可能null）
# ----------------------原方法----------------------
SELECT *
FROM employees
WHERE employee_id = (
	#查询最小的员工编号
	SELECT MIN(employee_id)
    FROM employees
) AND salary = (
	#最高工资
    SELECT MAX(salary)
    FROM employees
);
# ----------------------行子查询----------------------
SELECT *
FROM employees
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

select 后面

只支持标量子查询（一行一列）

# 案例：查询每个部门的员工个数
SELECT d.*, (
    #查每个部门人数
	SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.department_id
) 个数
FROM department d;

from 后面

支持表子查询（多行多列都行）

# 案例：查询每个部门的平均工资的工资等级
SELECT ag.*, g.grade_level
FROM (
    # 每个部门的平均工资表
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep # 必须起别名，否则后面不能使用
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists 后面

相关子查询（表子查询），布尔类型，即结果只有 1 或者 0

可以用 in 代替实现

SELECT EXISTS(
	SELECT employee_id
    FROM employees			# 到此返回 1
    WHERE salary = 9999		# 到此返回 0
);

分页查询

介绍

应用场景：当需要显示的数据一页显示不全，需要分页提交sql请求

语法：

SELECT 查询列表
... ...
LIMIT offset,size;
# offset：要显示的条目的起始索引（起始索引从 0 开始）
# size：要显示的条目个数

功能使用

SELECT * FROM employees LIMIT 0,5;

联合查询

介绍

关键字：union

功能：将多条查询语句的结果（多张表的结果）合并成一个结果

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致

特点：

要求多条查询语句的查询列数一致
要求多条查询语句的查询的每一列的类型和顺序最好一致
注意会自动去重（使用UNION ALL可不去重）

功能使用

# 案例：查询 部门编号＞90 或 邮箱包含a 的员工信息
SELECT * FROM employees WHERE email LIKE '&a&'
UNION
SELECT * FROM employees WHERE department_id > 90;

DML语言的学习

即数据操作语言。

插入

插入（一）

介绍

关键词：insert

语法：

INSERT INTO 表名(列名1，列名2，...)
VALUES(值1,值2, ...)
[,(值1,值2, ...)
 , ... ... ... ];

功能使用

注意

插入的值的类型要与列的类型一致或兼容
不可以为 null 的列必须插入值
列数和值的个数必须一致
可以省略列名，默认为所有列，而且列的顺序和表中的列的顺序一致

# 案例
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'李莉','女','2000-3-23','18888888888',null,5)
,(13,'李莉','女','2000-3-23','18888888888',null,5);

INSERT INTO beauty
VALUES(13,'李莉','女','2000-3-23','18888888888',null,5);

插入（二）

介绍

语法：

INSERT INTO 表名
SET 列名=值，列名=值，... ;

两种插入方式PK

方式一支持插入多，方式二不支持

方式一支持子查询，方式二不支持

INSERT INTO beauty(id,name)
SELECT 12,'Lily';
# 此处省略，直接使用常量代替

修改

修改单表的记录

介绍

UPDATE 表名
SET 列=新值, 列=新值, ...
WHERE 筛选条件;   #若不加筛选条件，则修改表中每一行的信息

功能使用

# 案例：修改boys表中的id为2，名称为张飞，魅力值10
UPDATE boys
SET boyname='张飞',usercp=10
WHERE id=2;

修改多表的记录【补充】

介绍

sql92 语法

UPDATE 表1 别名1，表2 别名2
SET 列=值，...
WHERE 筛选条件
AND 筛选条件;

sql99 语法

UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件
SET 列=值
WHERE 筛选条件;

功能使用

# 案例：修改张无忌的女朋友的电话为114
UPDATE boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
SET b.phone = '114'
WHERE bo.boyname = '张无忌';

删除

删除（一）：delete

介绍

单表的删除

DELETE FROM 表名 
WHERE 筛选条件;

多表的删除【补充】

sql92 语法

DELETE 表1的别名 或 表2的别名  # 加谁删谁
FROM 表1 别名，表2 别名
WHERE 连接条件
AND 筛选条件

sql99 语法

同【修改】

功能使用

单表的删除

# 案例：伤处手机号以9结尾的女神信息
DELETE FROM beauty
WHERE phone LIKE '%9';

多表的删除

# 案例：删除张无忌的女朋友信息
DELETE b
FROM beauty b
INNER　JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyname = '张无忌';

删除（二）：truncate

TRUNCATE TABLE 表名;

注意

truncate 一删全删，不能添加 WHERE 条件
假如要删除的表中有自增长列
如果用 delete 删除，再插入数据，自增长列的值从断点开始
如果用 truncate 删除，再插入数据，自增长列的值从 1 开始
delete 删除后有返回值（几行受影响）
truncate 删除后没有返回值（返回“0行受影响”）
delete 删除可以回滚
truncate 删除不能回滚

DDL语言的学习

即数据定义语言，主要是库和表的管理

创建：create
修改：alter
删除：drop

库的管理

库的创建

CREATE DATABASE 库名；

CREATE DATABASE IF NOT EXISTS 库名;

库的修改

一般不建议修改库，容易引起数据崩溃丢失

更改库的字符集：

ALTER DATABASE 库名 CHARACTER SET gbk;

库的删除

DROP DATABASE 库名;

DROP DATABASE IF EXISTS 库名;

表的管理

表的创建★

CREATE TABLE 表名(
	列名 列的类型[(长度)] [列的约束],
    ... ... ,
    列名 列的类型[(长度)] [列的约束]
)

PS通用：一般创建表（库）前，尝试将原表（库）删除再创建。

表的修改

修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

修改列名（可以同时修改类型）

ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 类型; #类型必须加上

修改列的类型或约束

ALTER TABLE 表名 MODIFY COLUMN 列名 类型;

添加新列

ALTER TABLE 表名 ADD COLUMN 新列 类型;

删除列
```
ALTER TABLE 表名 DROP COLUMN 列名;
```

表的删除

DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;

表的复制

仅仅复制表的结构
```
CREATE TABLE 新表名 LIKE 源表;
```

复制表的结果加数据

CREATE TABLE 新表名
SELECT * FROM 源表;

仅仅复制部分结果

CREATE TABLE 新表名
SELECT 列，列，列
FROM 源表
WHERE 0;  #添加一个 false 的条件

数据类型

数值型：整型，小数（浮点数、定点数）
字符型：
1. 较短的文本 char、varchar
2. 较长的文本 text、blob（较长的二进制数据）
日期型

整型

tinyint(1字节)、smallint(2字节)、mediumint(3字节)、int(4字节)、bigint(8字节)

设置无符号和有符号

CREATE TABLE table1(
	test int	# 默认是有符号值
);
CREATE TABLE table2(
	test int UNSIGNED	#无符号值
    # 若对此插入 负值，则在表中值为0
)

当插入的值超过了值得范围时，插入的值会变成范围的临界值，同时会报out of range的警告
如果不设置长度，则会有默认的长度
注意整型的长度值不代表范围，代表表中的值显示的最大宽度
（配上关键词ZEROFILL会使宽度不足用0代替，同时自动变为无符号型）
```
test int(7) ZEROFILL  #表示长度为7的无符号int，不足7位会用0自动填充
```

小数

浮点型：float(M,D) (4字节)、double(M,D) （8字节）
定点型：DEC(M,D) 或 DECIMAL(M,D) 字节数为M+2

特点：

M 和 D 可以省略
则浮点型插入任何范围内的规格都可以
若是Decimal，则默认为（10,0）
M 代表总宽度
D 表示小数点后保留的小数位（自动四舍五入）
定点型的精度较高
如插入的数据对精度要求较高如货币运算，则使用定点型

原则所选择的类型越简单越好，能保存数值的类型越小越好

主要的字符型

较短的文本：char(M)、varchar(M) M代表最长字符数 (binary、varbinary)
char：固定长度，比较耗费空间，效率较高
varchar：可变长度，节省空间，效率较低

注意 char 若省略长度，则默认为 1
varchar 不能省略长度
较长的文本：text、blob（较大的二进制）(blob的介绍和使用在 JDBC.md 中)
枚举型：ENUM
```
enum1 ENUM('a''b','c')
```
注意若插入的值不在 enum 中，则在表中会为 null，且警告

集合型：Set

set1 SET('a','b','c','d');
INSERT INTO 表名 VALUES
('a'),
('a','b','c'),
('a','b');
# 表中有三行，分别是 'a   'a,b,c'  'a,b'

日期型

类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp ★	4	19700101080001	2038年的某一时刻
time	3	-839:59:59	838:59:59
year	1	1901	2155

timestamp 和实际的时区有关，更能反映实际的日期（因此使用较多）
而 datetime 则只能反映出插入时的当地时区
timestamp 的属性受 MySQL 版本和 SQLMode 的影响

常见约束

介绍

含义：用于限制表中的数据，为了最终保证表中数据的一致性

分类：

NOT NULL ：非空，用于保证该字段的值不能为空（比如姓名等）
DEFAULT：默认，用于保证该字段有默认值（比如性别等）
PRIMARY KEY：主键，用于保证该字段的值具有非空唯一性（比如ID等）
UNIQUE：唯一，用于保证该字段的值具有唯一性，但可以为空（比如座位号等）
CHECK：检查【MySQL中不支持，无用但不报错】，用于保证添加的值符合要求（比如年龄等）
FOREIGN KEY：外键，在从表添加外键约束。用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值（比如专业编号等）

PS：添加约束的时机：①创建表时；②修改表时；

功能使用(创建表时)

列级约束

CREATE TABLE stu(
	id INT PRIMARY KEY, # 主键
    stuName VARCHAR(20) NOT NULL, # 非空
    gender CHAR(1) CHECK(gender='男' OR gender='女'), # 检查，MySQL不支持
    seat INT UNIQUE, # 唯一
    age INT DEFAULT 18, # 默认
    major_id INT FOREIGN KEY REFERENCES major(id) #外键，引用 major 表的 id 
);

注意六大约束语法上支持，但外键约束无效果！！！

表级约束

[CONSTRAINT 约束名] 约束类型(字段名)

CREATE TABLE stuinfo(
	id INT,
    stuName VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    major_id INT,
    
    PRIMARY KEY(id), # 主键
    #PRIMARY KEY(id,stuName), # 表示两个列组合成一个主键，可单独重复，不可成对重复（不推荐！！！）
    CONSTRAINT uq UNIQUE(seat), # 唯一键
    FOREIGN KEY(major_id) REFERENCES major(id) # 外键
);

注意除非空、默认以外，其他都支持

通用写法

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    major_id INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(mayjor_id) REFERENCES major(id)
);

注意

主键和唯一

唯一？非空？表中可以有多少个允许组合？
主键 √ × 1 √，但不推荐
唯一 √ √ 可以多个 √，但不推荐
外键特点
1. 在从表设置外键关系
2. 从表的外键列的类型和主表的关联列要求一致或兼容，名称无要求
3. 要求主表中的关联列必须是一个 key（一般是主键或者唯一键）
4. 插入数据时，先插入主表，再插入从表
  删除数据时，先删除从表，再删除主表

	唯一？	非空？	表中可以有多少个	允许组合？
主键	√	×	1	√，但不推荐
唯一	√	√	可以多个	√，但不推荐

功能使用（修改表时）

添加约束条件

列级约束

ALTER TABLE 表名 MODIFY COLUMN 列名 类型 新约束条件;
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

表级约束

ALTER TABLE 表名 ADD MODIFY [CONSTRAINT 约束名] 约束类型（字段名）[外键的引用]；
ALTER TABLE stuinfo ADD MODIFY [CONSTRAINT ...] PRIMARY KEY(id)；

删除约束条件

删除非空约束

ALTER TABLE 表名 MODIFY COLUMN 列名 列类型;  #省略约束条件 即 删除
# 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
# 删除唯一键（即索引）
ALTER TABLE 表名 DROP INDEX 唯一键;

标识列

介绍

又称为自增长列

自增列的步长可以设置，起始值可以通过手动第一条插入的值进行设置

SET auto_increment_increment = 步长;

含义：可以不用手动插入的插入值，系统提供默认的序列值（默认从1开始）

注意：

标识列必须和主键搭配使用吗？不一定。但要求是一个 key
一个表中可以有多个标识列吗？不能。一个表至多一个增长列。
标识列只能放在 数值型 上。

功能使用

创建表时

CREATE TABLE table1(
	id INT PRIMARY KEY AUTO_INCREMENT ,
    stuName VARCHAR(20)
);
# 插入时，id值可以乱填，一般可null

修改表时

# 添加
ALTER TABLE 表名 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT ;
# 删除
ALTER TABLE 表名 MODIFY COLUMN id INT PRIMARY KEY ;

TCL语言的学习

Transaction Control Language. 事务控制语言

事务

一个或一组 sql 语句组成一个执行单元，在这个单元中，每个MySQL语句是相互依赖的。要么全部执行，要么全部不执行。（例：转账）

若某一句执行失败，就会回滚。

事务的ACID属性

原子性（Atomicity）：一个事务不可再分割，都执行或都不执行
一致性（Consistency）：一事务执行会使数据从一个一致状态切换到另一个一致状态
隔离性（Isolation）：一个事务的执行不受其他事务的干扰
持久性（Durability）：一个事务一旦提交，则会永久的改变数据

事务的创建

隐式事务：事务没有明显的开起和结束的标记
比如insert、update、delete语句

显式事务：事务具有明显的开启和结束的标记

**前提：**必须先设置自动提交功能为禁用

步骤一：开始事务
SET autocommit = 0; # 仅在当前询问有效
START TRANSACTION;  # 可选
步骤二：编写事务中的 sql 语句(select、insert、update、delete)
...;
步骤三：结束事务（两种方式）
COMMIT;  # 提交事务
ROLLBACK;# 回滚事务（即撤销）

哪些操作会使得事务自动提交？

DDL操作一旦执行，都会自动提交。set autocommit=false对DDL操作无效
DML默认情况下会自动提交，可以通过上 ↑↑↑ 取消DML操作的自动提交
默认在关闭连接时，会自动的提交数据

常见的并发问题

原因：对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，没有采取必要的隔离机制。

脏读：获取到了未提交的数据，临时且无效。（更新数据、回滚）
不可重复读。
幻读。（插入数据）

事务的隔离

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed（Oracle默认）	×	√	√
repeatable read（MySQL默认）	×	×	√
serializable	×	×	×

查看当前隔离级别：

SELECT @@tx_isolation;

设置隔离级别

set session transaction level 隔离级别;  #只针对当前连接有效（连接，不是询问）
set GLOBAL session transaction level 隔离级别;  # 设置数据库全局隔离级别

SAVEPOINT


SET autocommit = 0; # 仅在当前询问有效
START TRANSACTION;  # 可选

sql 语句 ... ;
SAVEPOINT a;
sql 语句 ... ;

ROllBACK TO a;

视图

介绍

含义：虚拟表，和普通的表一样使用（MySQL 5.1 版本的新特性，是通过表动态生成的数据）

特点：只保存了 sql 逻辑，不保存查询结果

应用场景：

多个地方用到相同的查询结果
该查询结果使用到的 sql 语句较为复杂

特点：

实现了sql 语句的重用
简化了负责的 sql 操作，不必知道它的具体查询细节
保护数据，提高了安全性

视图的操作

创建

CREATE VIEW 视图名
AS
SELECT ... ... ;

修改

方式一：

CREATE OR REPLACE VIEW 试图名  # 存在则修改
AS
查询语句;

方式二：

ALTER VIEW 视图名
AS
查询语句;

删除

DROP VIEW 视图名1，试图名2，... ;

查看

DESC 视图名;

SHOW CREATE VIEW 视图名;

更新（更改视图中的数据）

插入：insert
修改：update
删除：delete

注意： insert 对视图的修改会同步到原始表（因此一般给视图只读的权限）

+++

具有以下特点的视图不允许更新

包含以下任意关键字的 sql 语句
分组函数、distinct、group by、having、union或者union all
常量视图
SELECT 中包含子查询
使用了 join 等连接语句
FROM 一个不能更新的视图
WHERE 子句的子查询引用了 FROM 子句中的表

与表的对比

视图没有实际占用物理空间（只占用了逻辑，极小）
表实际占用了物理空间（保存数据）

变量

系统变量

变量由系统提供，非用户定义，属于服务器局面

#① 查看所有的系统变量
SHOW GLOBAL|[SESSION] VARIABLES ;
#② 查看满足条件的部分系统变量
SHOW GLOBAL|[SESSION] VARIABLES LIKE '%char%';
#③ 查看指定的某个系统变量
SELECT @@[GLOBAL|SESSION.]系统变量名;
#④ 为某个系统变量赋值
SET GlOBAL|[SESSION] 系统变量名 = 值；
SET @@[GLOBAL|SESSION.]系统变量名 = 值;

**注：**如果是全局级别，需加 GLOBAL
如果是会话级别，可不写或写 SESSION

全局变量

作用域：服务器每次启动将为所有的全局变量赋值，针对所有的会话（连接）有效。但不能跨重启！

会话变量

作用域：聚聚针对当前会话（连接）有效

自定义变量

变量是用户自定义的，非系统定义

用户变量

作用域：同于系统变量中的会话变量的作用域

可以应用在任何地方

#① 声明并初始化
SET @用户变量名 = 值 ;
SET @用户变量名 := 值 ；
SELECT @用户变量名 := 值 ;
#② 赋值（更新用户变量的值）
方式一： 再执行一遍①
方式二： SELECT 字段 INTO @用户变量名 FROM 表;
#③ 使用（查看、比较、运算等）
SELECT  @用户变量名;

局部变量

作用域：仅仅在定义它的 begin-end 中有效

注意只能应用在 begin-end 中的第一句话

#① 声明（并声明）
DECLARE 变量名 类型 [ DEFAULT 值] ;
#② 赋值（更新）
SET 局部变量名 := 值;
SELECT @局部变量名 := 值；
SELECT 字段 INTO 局部变量名 FROM 表;
#③ 使用
SELECT 局部变量名;

存储过程和函数

存储过程

介绍

存储过程和函数类似于 Java 中的方法

优点：

提高代码的重用性
简化操作

存储过程

含义：一组预先编译好的 SQL 语句的集合，理解成批处理语句

优点：减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

DELIMITER 结束标记

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体(方法体,一组合法的SQL语句)
END

# 调用
CALL 存储过程名(实参列表) 结束标记

注意

参数列表有三部分：参数模式、参数名、参数类型
如果存储过程体仅有一句话，则 begin-end 可以省略
存储过程中的每条 SQL 语句的结尾要求必须加分号
存储过程的结尾可以使用 DELIMITER 重新设置
```
DELIMITER 结束标记 
```

参数模式

in：该参数可以作为输入，也就是改参数需要调用方传入值
out：该参数可以作为输出，也就是说改参数可以作为返回值
inout：该参数既可以做为输入又可以作为输出

功能使用

####带空参

# 案例：插入到 admin 表中五条记录
DELIMITER $
CREATE PROCEDURE myp()
BEGIN
	INSERT INTO admin(username,'password') VALUES
	('john1',0000),
	(...)
END $

####带 in 模式

# 案例：根据女神名，查询对应的男神信息
CREATE PROCEDURE myp(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
# 调用
CALL myp2('Lily')$

# 案例：验证用户是否登录成功
CREATE PROCEDURE myp(IN username VARCHAR(20),IN 'password' VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT '';	# 声明并初始化
	SELECT COUNT(*) INTO result	#赋值
	FROM admin a
	WHERE a.username = username
	AND a.'password' = 'password';
	SELECT IF(result>0,'成功','失败'); #使用
END $
# 调用
CALL myp3('admin','123456') $

带 out 模式

# 案例：根据女神名，返回男神名和魅力值
CREATE PROCEDURE myp(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
   SELECT bo.boyName,bo.userCP INTO boysName,userCP
   FROM boys bo
   INNER JOIN beauty b ON bo.id = b.boyfriend_id
   WHERE b.name = beautyName
END $
# 调用
CALL myp('Lily',@bName,@userCP)$

带inout模式

# 案例1:翻倍
CREATE PROCEDURE myp(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $
# 调用
SET @m = 8 $
SET @n = 10 $		# 先定义两个变量
CALL myp(@m,@n) $
SELECT @m,@n $

存储过程的删除和查看

DROP PROCEDURE 存储过程名;
DROP PROCEDURE 存储过程名1,存储过程名2,...;   ---> 不能这样用，一次只能删一个！！！

SHOW CREATE PROCEDURE 存储过程名;

注意存储过程中逻辑语句不能修改

函数

与存储过程的区别：

有且仅有一个返回

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
	RETURN 返回值;  # 建议将 return 放在函数体的最后
END $
# 调用
SELECT 函数名(参数列表) $

参数列表：参数名参数类型

功能使用

# 案例：返回公司的员工个数
CREATE FUNCTION myf() RETURNS INT
BEGIN
	DECLARE nums INT DEFAULT 0;		# 声明变量并初始化
	SELECT COUNT(*) INTO nums
	FROM employees;
	RETURN nums;	# 返回变量 
END $
# 调用
SELECT myf() $

# 案例：根据员名返回他的工资
CREATE FUNCTION myf(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE DEFAULT 0;
	SELECT salary INTO sal
	FROM employees
	WHERE last_name = empName;
	RETURN sal;
END $
# 调用
SELECT myf('Lionel') $

查看和删除

SHOW CREATE FUNCTION 函数名;

DROP FUNCTION 函数名;

流程控制结构

分支结构

程序可以从两条或多条路径中选择一条执行

If 函数

SELECT IF(表达式1，表达式2，表达式3);

若表达式1成立，则返回表达式2的值，否则返回表达式3的值

case 结构

类似于 switch 语句，一般用于等值判断

CASE 变量|表达式|字段
WHEN 值1 THEN 要显示的(值1,)或(语句1;)
WHEN 值2 THEN 要显示的(值2,)或(语句2;)
...
ELSE 要显示的(值n,)或(语句n;)
END [CASE];  # 用在 begin—end 中 则需要加 case

类似于多重 if…else 语句，一般用于实现区间判断

CASE
WHEN 条件1 THEN 要显示的(值1,)或(语句1;)
WHEN 条件2 THEN 要显示的(值2,)或(语句2;)
...
ELSE 要显示的(值n,)或(语句n;)
END [CASE]; # 用在 begin—end 中 则需要加 case

特点：

即可以作为表达式嵌套在其他语句中使用，可以放在任何地方
也可以作为独立的语句使用，则只能放在 begin-end 中
else 可以省略

如果省略了，且所有 when 条件都不满足，则返回 null

If 结构

实现多重分支

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
... ...
[ELSE] 语句n;
END IF;

注意只能应用在 begin-if 中

循环结构

介绍

程序在满足一定条件的基础上，重复执行一段代码

分类：while、loop、repeat

循环控制：

iterate（类似于continue）：继续
leave（类似于break）：跳出

while

[标签: ]WHILE 循环条件 DO 
	循环体;
END WHILE [标签] ;

功能使用

# 案例：批量插入多条记录
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i<insertCount DO
		INSERT INTO admin(username,'password') VALUES (CONCAT('Rose',i), '123');
		SET i = i + 1;
	END WHILE;
END $
# 调用
CALL pro_while(100);

# 案例：批量插入多条记录，当次数大于20则停止
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a: WHILE i<insertCount DO
		INSERT INTO admin(username,'password') VALUES (CONCAT('Rose',i), '123');
		IF i>20 THEN LEAVE a;	# 跳出循环
		END IF;
		SET i = i + 1;
	END WHILE a ;
END $
# 调用
CALL pro_while(100);

loop

[标签:] LOOP 
	循环体
END LOOP [标签] ;

repeat

[标签:] REPEAT
	循环体 ;
UNTIL 结束循环的条件
END REPEAT [标签] ;

其他

sql 语句执行速度

当你在同一次连接中，执行一次 sql 语句时，会进行编译然后运行

若之后你再执行一次一模一样的 sql 语句，则会跳过编译直接执行

青年痴呆

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL之入门

MySQL的基本操作服务的启动和停止方式一：计算机–右击管理–服务方式二：通过管理员服务运行net start 服务名启动服务net stop 服务名停止服务MySQL服务的登入和登出方式一：通过MySQL自带的客户端（仅限管理员）方式二：通过DOS登录： mysql [-h 主机名 -p 端口号] -u 用户名 -p密码退出： EXIT或者ctrl+cMySQL的常用命令查看所有的数据库SHOW DATABASES；打开指定的库USE 库名查看当前库
复制链接

扫一扫

专栏目录