Mysql数据库基础知识

本文主要是介绍Mysql数据库基础知识，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

数据库相关概念

MySQL服务的启动和停止

MySQL服务的登录和退出

MySQL的常见命令

MySQL的语法规范

DQL语言

基础查询

常见函数

分组查询

连接查询

子查询

分页查询

联合查询

DML语言

常见约束

标识列

TCL语言

视图

数据库相关概念

DB：数据库
DBMS：数据库管理系统，或数据库软件，用于管理数据库
SQL：结构化查询语言，用于和DBMS通信的语言，几乎所有的DBMS都支持SQL

MySQL服务的启动和停止

方式一：通过计算机—管理—服务

方式二：通过管理员身份运行：net start mysql(启动) net stop mysql(停止)

MySQL服务的登录和退出

方式一：通过mysql自带的客户端(仅限于root用户)

方式二：通过命令提示符：

退出：exit或ctrl+c

MySQL的常见命令

查看当前所有的数据库：show databases;
打开指定的库：use 库名;
查看当前库的所有表：show tables;
查看其他库的表：show tables from 库名；
创建表：create table 表名(

列名列类型，

列名列类型，

......

)；
查看表结构：desc 表名；
查看服务器的版本：

方式一：登录到mysql的服务端：select version();

方式二：未登录到mysql服务端：mysql --version或mysql --V

MySQL的语法规范

DQL语言

基础查询

use + 库名＋分号

语法：
select 查询列表 from 表名

特点：
1、查询列表可以是：表中的字段、常量值、函数、表达式
2、查询的结果是一个虚拟的表格
*/

#1、查询表中的单个字段
SELECT last_name FROM employees;
#2、查询表中的多个字段
SELECT last_name,first_name FROM employees;
#3、查询表中的所有字段
SELECT * FROM employees;
SELECT `first_name`,`last_name` FROM employees;(双击栏位)(不是单引号，是着重号，1左边)
#4、查询常量值
SELECT 100;
#5、查询表达式
SELECT 98*100;
#6、查询函数
SELECT VERSION();#表示查询函数的返回值
#7、起别名
/*
好处1.便于理解
好处2.当要查询的字段有重名的时候，别名便于区分
语法：
用as连接，但as可以省略；
当别名中有特殊符或者是关键字时要加双引号
*/
select last_name as 别名,first_name "别名" from employees;
#8、去重
SELECT DISTINCT department_id from employees;
#9、+号的作用 仅仅作为运算符作用：
/*
若两个操作数都为数值型则直接做加法 
只要其中一方为字符型，则试图将字符转换为数值型，若转换成功则直接做加法运算，如转换失败，则将字符数值转换成0
只要其中一方为null，则结果肯定为null
*/					
#10、使用concat()方法连接字符 注意null和任意字符相拼接将返回null
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
#11、显示表的结构
DESC 表名;

条件查询

/*
语法：
select 查询列表
from 表名
where 筛选条件

条件分类：
1、按条件表达式筛选
	条件运算符：>,<,=,!=,<>,>=,<=
2、按逻辑表达式筛选
	逻辑运算符：&&或and ||或or !或not
3、模糊查询
	like:即查询列表中含有like中的某个字符
	特点：一般和通配符搭配使用
		通配符有：%：代表有任意多个字符，包含0个字符
			  _:(下划线)代表任意的单个字符
			  
	特殊情况：当通配符也是要查询的内容之一时，需要使用转义字符
		1、可使用\+通配符(反斜杠)
		2、可使用escape语句
			例如(若_为要查询的内容)select 查询列表 from 表名 where 查询列表 like ‘$__’ escape '$';(表示$后的是转义效果)
		
	用法：
		#案例1：查询员工名中含有a的员工
		select 									------------1
			*
		from									------------2	
			employees
		where									------------3
			last_name like '%a%' ;                 (以上语句的执行顺序为231)
			
	
	between and：用于查询大于等于---小于等于---的表列
		     1、包含临界值
		     2、顺序不能颠倒
	in：查询某字段的值是否属于in列表中的某一项
		特点：1.使用in提高了语句的整洁度
			 2.in列表中的值必须是同类型的字段或是兼容的
			 3.不支持like的模糊搜索
	    1、in列表里的值类型必须相同或者兼容
	    #案例2：查询员工的工种编号是'IT_PROT','AD_VP'员工名和工种编号
	    select 
	    	last_name,
	    	job_id
	    from 
	    	employees
	    where 
	    	job_id in('IT_PROT','AD_VP');
	is null / is not null(即><=不可以判断null)
	案例3：查询没有奖金的员工名和奖金率
	select 
		last_name,commission_pct
	from 
		employees
	where 
		commission is null;(is不可以换为=)
	安全等于<=>		(安全等于可用于判断数字也可以用于null，但代码可读性较差，难以判断出是大于等于小于号)
	案例3：查询没有奖金的员工名和奖金率
	select 
		last_name,commission_pct
	from 
		employees
	where 
		commission <=> null;(is不可以换为=)
	
*/

排序查询

/*
语法：
	1.select 查询列表
	2.from 表
	3.【where 筛选条件】
	4.order by 排序列表 【asc 升序|desc降序】
	执行过程：2314
注意：order by语句一般放在最后，limit子句除外
	asc或者desc如果不写默认是升序
*/
#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#案例2：查询部门编号>=90的员工信息，按入职时间的先后顺序排序【添加筛选条件】
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;
#按表达式排序
#案例3：按年薪的高低显示员工的信息和年薪【按表达式排序或别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) ASC;#或者将表达式改为别名
按函数返回值排序
#案例4：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#【按多个字段排序】
#案例5：查询员工信息，要求先按工工资升序，再按员工编号降序
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
#案例6：查询工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

常见函数

单行函数

/*
分类：
	1、单行函数，如concat，length，ifnull
	2、分组函数：主要用于统计，又称为统计函数、聚合函数、组函数

*/
单行函数
#一、字符函数
#1、length()获取参数的字节数 一个字母占一个字节，一个汉字占3个字节
#2、cncat()拼接字符串
#3、upper()和lower() 前者变大写 后者变小写
SELECT UPPER('john');，
#4、substr substring从指定索引处(sql中索引从1开始)截取后面所有字符，可以有多个参数，类似方法重构
SELECT SUBSTR('',8) output;#截取指定索引后的所有字符
SELECT SUBSTR('',1,3) output;#即截取第一个到第三个字符
#姓名中字符大写，其他字符小写，然后用_拼接，显示出来【函数可以嵌套使用】
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',SUBSTR(last_name,2)) AS output
FROM employees;
#5、instr 返回子串第一次出现的起始索引，如果找不到则返回0
SELECT INSTR('','');  #返回8
#6、trim 去除字符串里的字符
SELECT TRIM('张设定     ') AS output; #去除的参数未设定字符时，则默认去除字符前后的空格 输出为张设定
SELECT TRIM('a' FROM 'aaaaaaaaa李aaaaa') AS output;#输出为李aaaaa 若去除的参数设定为aa时，输出为a李aaaaa
#7、lpad 用指定的字符实现左填充使整个字符串达到指定长度 rpad 用指定的字符实现右填充使整个字符串达到指定长度 
SELECT LPAD('lijiaying',12,'*');#输出为***lijiaying
SELECT RPAD('lijiaying',12,'*');#输出为lijiaying***
#8、replace('对象','要替换的内容','替换后的内容');
SELECT REPLACE("")；

#二、数学函数
#round(数值1，数值2即要保留几位小数) 四舍五入
SELECT ROUND(10.23); #参数2不写表示不保留小数
SELECT ROUND(10.32432,2);#保留两位小数

#ceil 向上取整，返回不小于该参数的最小整数
SELECT CEIL(-1.23);#输出为-1		
#floor 向下取整，返回不大于该参数的最大整数
SELECT FLOOR(-2.32);

#truncate(参数1，参数2) 截断，参数2表示要截断几位小数
SELECT TRUNCATE(1.87634,1);#输出为1.8

#mod(参数1，参数2) 取模 
/*
mod(a,b);a-a/b*b
mod(-10,-3); -10-(-10)/(-3)*(-3) = -1
*/

#三、日期函数
#now() 返回当前时间
SELECT NOW();
#curdate() 返回当前系统的日期，不包含时间
SELECT CURDATE();
#curtime 返回当前系统的时间，不包含日期
SELECT CURTIME();

#获取日期或时间的指定部分，例如年，月，日，时，分，秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1992-10-10') 年;
#从字段hiredate中获取年
SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH('1998-10-2') 月;
SELECT MONTHNAME(NOW()); #获取当前时间的月份名称

#str_to_date(参数1，参数2) 将指定格式的字符转换成指定格式的日期 参数1存放指定的日期，参数2存放sql读取指定日期的格式
SELECT STR_TO_DATE('1998-10-10','%Y-%m-%d'); #输出为1998-10-10
#具体场景：查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');
#date_format(参数1，参数2) 将日期转换成字符 参数1为具体的日期，参数2为转换后的日期字符格式
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日'); 输出为2022年02月27日
#具体场景：查询员工的入职日期（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') FROM employees;

#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

#五、流程控制函数
#1.if(参数1，参数2，参数3) if else的效果 参数1的结果为true时返回参数2，否则返回参数3
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'无奖金,呵呵','有奖金，嘻嘻')
FROM employees;
/*2.case函数的使用一：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
when 常量3 then 要显示的值3或语句3;
...
else 默认要显示的值n或语句n
end

案例：查询员工的工资，要求：
部门号 = 30，显示的工资为1.1倍
部门号 = 40，显示的工资为1.2倍
部门号 = 50，显示的工资为1.3倍
其他部门显示原工资

*/
SELECT salary 原工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

#case函数的使用2：类似于多重if
/*
case 
when 条件1 then 要显示的值1(或者语句1;)
when 条件2 then 要显示的值2(或者语句2;)
when 条件3 then 要显示的值3(或者语句3;)
else 要显示的值n或者语句n
end
*/

#案例：查询员工的工资情况
/*
如果工资＞20000，显示A级别
如果工资＞15000，显示B级别
如果工资＞10000，显示C级别
否则，显示D级别
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 等级
FROM employees;

分组函数

#二、分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum()求和 avg()求平均值  max()求最大值 min()求最小值 count()计数

特点：
1、sum、avg的参数一般用于处理数值型，但使用其他数值类型不会报错
   max、min、count可以用于处理任何类型
2、处理数据时以上所有分组函数都忽略null值
3、可以和distinct(去重)搭配使用	


*/
#1、简单使用
SELECT SUM(salary) 和,AVG(salary) 平均 FROM employees;

/*2、参数支持哪些类型
*/

#3、可以和dintinct搭配使用
SELECT COUNT(DISTINCT salary) FROM employees;

/*4、count函数的详细介绍
count(字段)
count(*)除了整行都为null的一行，其他的字段都被计数
count(常量值)相当于在最前列加了一列该值，所以全部会被计数

效率：
MYISAM引擎下：count(*)效率最高
INNODB引擎下，count(*)和count(1)的效率差不多，但比count(字段)高，后者还要判断该字段所在行是否有null，一般用count(*)

*/
/* 
5、和分组函数一同查询的字段有限制
和分组函数一同查询的字段要求是group by后的字段
*/

分组查询

#分组查询
/*
基础语法：
select 分组函数，列（要求出现在group by后面）
from 表
where 筛选条件
group by 
order by

明确where筛选条件放在from后面

注意：
	查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
	1、分组查询中的筛选条件可以分为两类：
			数据源			位置			关键字
	分组前筛选	原始表		    group by子句的前面		where
	分组后筛选	分组后的结果集	    group by子句的后面		having
	
	分组函数做条件肯定是放在having子句中
	能用分组前筛选的，就优先考虑
	
	2、group by 子句支持单个字段分组或多个字段分组(多个字段分组之间用逗号隔开，字段顺序没有要求)，表达式和函数（相对较少）
	3、也可以添加排序 
*/
#案例1：要求查找每个工种的最高工资
#简单的分组查询
SELECT MAX(salary) 最高工资,job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#添加筛选条件
#案例3：查询邮箱里包含a字符的每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE `email` LIKE '%a%'
GROUP BY department_id;

#案例4：查询有奖金的每个领导的手下员工的最高工资
SELECT MAX(salary),`manager_id`
FROM employees
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id;

#添加复杂的筛选条件(添加分组后的筛选
#案例5：查询哪个部门的员工个数大于2
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#案例6：查询领导编号＞102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT manager_id,MIN(salary) 最低工资
FROM employees 
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

#按表达式或函数分组
#按员工姓名的长度分组，查询每一组员工个数，筛选员工个数>5的有哪些

#按多个字段分组
#案例8：查询每个部门每个工种的学员的平均工资
SELECT AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id;


#添加排序
#案例9：查询每个部门每个工种的学员的平均工资，并且按平均工资的高低排序
SELECT AVG(salary),job_id,department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY job_id,department_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;

连接查询

/* 
也称为多表查询，当查询的字段来自多个表时，就会用的多表查询

笛卡尔积现象：表1有m行，表2有n行，结果表会有m*n行

出现原因：缺少有效的连接条件

连接方式分类：
	按年代分类：
		sql192标准【仅支持内连接】
		sql199标准【推荐】：支持内连接+外连接（左外和右外）+交叉链接
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		交叉连接：


*/

#一、sql192标准
#1.等值连接
/*
1.多表等值连接的结果为多表的交际部分
2.n表连接至少需要n-1个条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配排序、分组、排序等子句
*/
#案例1：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#为表起别名
/*
1.提高语句的简洁度
2.区分多个重名的字段

注意：如果起了别名，则查询的字段就不能使用原来的表名去限定
	两个表的顺序可以调换
*/
#案例2：查询员工名，工种名、工种号
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id;

#添加筛选
#案例3：查询有奖金的员工名和部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

#案例4：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND l.city LIKE "_o%";

#4.添加分组
#案例5：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM locations l,departments d
WHERE d.location_id  = l.location_id
GROUP BY city;

#案例6：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id = e.department_id
AND e.commission_pct IS NOT NULL
GROUP BY department_name;

#添加排序
#案例7：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;


#实现三表连接
#案例8：查询员工名、部门名和所在城市
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
AND city LIKE "%s%";

#2.非等值连接
#即在连接多个表的条件where中 把等号改为其他筛选条件，例如>,<,bewteen and等等

#3.自连接(不是所有的表都能完成自连接)
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 筛选条件】
	【group by分组】
	【having 筛选条件】
	【order by 分组】
	
分类：
内连接（★）：inner
外连接
	左外（★）：left 【outer】
	右外（★）：right【outer】
	全外（★）：full【outer】
交叉连接：cross
*/

#一、内连接
/*
select 查询列表
from 表1 别名 
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
1.可添加排序、分组、筛选
2.inner可以省略
3.筛选条件放在where后面，连接条件放在on后面，提高可读性
4.inner join连接和sql192语法中的等值连接效果是一样的，都是查询多表的交集

*/
#等值连接：案例1：查询员工名、部门名、工种名，并按部门名降序
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 d.department_name DESC;
#非等值连接和自连接同sql192语法


/*
二、外连接
应用场景：用于查询一个表中有，另一个表中没有的记录

特点：
1.外连接的查询结果为主表中的所有记录：
	如果从表中有和他匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果 = 内连接结果+主表中有而从表中没有的记录
2.左外连接：left join 左边的是主表
  右外连接：right join 右边的是主表
3. 左外和右外交换两个表的顺序，可以实现同样的效果
*/

子查询

#子查询
/*
含义：出现在其他语句中的select语句称为子查询或者内查询 
	而外部的查询语句称为主查询或外查询
分类：
按子查询出现的位置分类：
	select后面：仅支持标量子查询
	from后面：支持表子查询
	where或having后面：标量子查询、列子查询和行子查询
	exists后面（相关子查询）：表子查询
按结果集的行列数不同：
	标量子查询：结果集只有一行一列
	列子查询：结果集一列多行
	行子查询：结果集一行多列
	表子查询：一般多行多列
*/


/*
一、where或having后面：标量子查询、列子查询和行子查询
特点：
	1、子查询放在小括号内
	2、子查询一般放在条件的右侧
	3、标量子查询一般搭配单行操作符使用：><= <>等等
	4、列子查询一般搭配多行操作符使用：in any/some all
	5、执行的顺序是先做子查询再做主查询
	

*/

#1.标量子查询 
#非法使用标量子查询：子查询只能出现一行一列 单行运算符只能搭配标量子查询
#案例1：谁的工资比Abel高
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name = "Abel"
);
#案例2：返回job_id与141员工相同，salary与143号员工多的员工 姓名 job_id和salary
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 
);

#2、列子查询（多行子查询）
/*
注意：一行多列要用多行操作符
一些多行操作符：
*/

#案例1:返回location_id是1400或者1700的部门中的所有员工的姓名
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
#3、行子查询(使用较少)
#案例：查询员工编号最小且工资最高的员工信息
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 = e.employee_id
) 个数
FROM departments d;

#三、from后面
#注意：将子查询的结果作为一张表时必须起别名
#案例：查询每个部门的平均工资和相应的工资等级
SELECT avg_dep.ag,g.grade_level
FROM(
	SELECT AVG(salary) ag,department_id
	FROM employees 
	GROUP BY department_id
)avg_dep
INNER JOIN job_grades g
ON avg_dep.ag BETWEEN lowest_sal AND highest_sal;


#四、exists后面的子查询（即相关子查询）
#exists() 查询括号内的查询是否有结果 返回值会1或者0 	使用exits的可以使用in
#案例：查询有员工的部门名名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id = e.department_id
);

分页查询

/*分页查询：查询一张表内的其中几条
语法：
	select 查询列表
	from 表
	【join 
	on 连接条件】
	【where 筛选条件】
	【group by分组】
	【having 筛选条件】
	【order by 分组】
	limit 起始索引，显示条目数	(当起始索引为0时可以省略不写 索引从0开始）
特点：1.limit 子句放在最后，执行也是在最后（执行顺序：from→连接条件→where→group by→having→select→order by→limit
      2.网页请求数据库分页查询时，应用公式
      例如：select 查询列表
	    from 表
	    limit （page-1）*size，size
	    （page为第几页 size为每页条数）
*/

#案例：查询有奖金的员工信息，显示工资较高的前10条
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0,10;

联合查询

/*
联合查询
union合并：将多条查询语句的结果合并到同一个结果表

语法：
查询语句1
union
查询语句2
union
。。。

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

特点：1.查询的列数要一致
	2.多条查询语句查询的每一列的类型和顺序要一致
	3.使用union关键字默认是去重的，union后追加all可以取消去重
*/
#案例：查询中国用户中男性的信息以及外国用户中男性用户的信息
SELECT id,cname csex FROM t_ca WHERE csex = '男'
UNION
SELECT t_id,tName,tGender FROM t_ua WHERE tGender = 'male';

DML语言

/*
DML语言：即数据操作语言
插入：insert
修改：update
删除：delete
*/
#一、输入语法

/*
方式一：经典插入
语法：
insert into 表名(列名...)
values（）
*/
#1.插入的值的类型要与列的类型一致
/*#2.不可以为null的列必须插入值，而可以为null的列插入值时有两种方式：

*/
#方式1：不加入列名
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES(13,'唐义熙','女','1990-4-21','138888888888','10');

#方式2：在对应的列名中值置为null
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐义熙','女','1990-4-21','138888888888',NULL,'10');

#3.列名的顺序可以调换，但值要与调换后的列名相对应
#4.列和值的个数必须一致
#5.可以省略列名，会默认添加所有列名，顺序和表内的顺序一致

/*
方式二：
语法：
insert into 表名
set 列名= 值，列名 = 值...
*/


#两种方式大pk
/*
1.方式一支持插入多行,方式二不支持
*/
#2.方式二支持子查询，方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;

#二、修改语句
/*
分类：
1.修改单表的记录：
语法：
update 表名 
set 列名 = 值,列名= 值,...
where 筛选条件
（执行顺序为update→where→set） 


2.修改多表的记录【补充】
sql92语法
update 表1 别名，表2 别名
set 列= 值，...
where 筛选条件
and 筛选条件；

sql99语法：
update 表1 别名
inner(left|right) join 表2 别名
on 连接条件
set 列=值，...
where 筛选条件；


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

#多表修改
#案例：修改张无忌的女朋友手机号码为114
UPDATE boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
SET phone = '114'
WHERE bo.boyName = '张无忌';

#案例2：修改没有男朋友的女生的男朋友编号都为2
UPDATE boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
SET b.boyfriend_id = '2'
WHERE b.boyfriend_id IS  NULL;

#删除语句
/*
方式一：delete
语法：
1.单表的删除
delete from 表名 where 筛选条件

2.多表删除【补充】

语法：truncate table 表名；
sql92语法：
delete 表一别名，表二别名（如果只删除一个就只写一个表的别名）
from 表1 别名，表2 别名
where 连接条件
and 筛选条件

sql99语法：
delete 表1别名，表二别名（如果只删除一个就只写一个表的别名）
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件

方式二：truncate
一次性删除表中的所有数据
*/
#方式一
#1.单表删除：
#案例1：删除手机号以9结尾的女生信息
DELETE FROM beauty WHERE phone LIKE '%9';

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

#delete和truncate对比
/*
1.delete可以加where等筛选条件而truncate不可以
2.truncate删除效率要高一点
3.如果要删除表中有自增长的列
如果用delete删除后再插入数据，自增长的值从断点开始
用truncate删除再插入数据，自增长的值从1开始
4.truncate删除没有返回值而delete有返回值
5.truncate删除不能回滚而delete可以
*/

DDL语言

/*
数据定义语言：库和表的管理

一、库的管理：创建、修改、删除
二、表的管理：创建、修改、删除

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


*/

#一、库的管理
#1.库的创建
/*
语法：
create database 【if not exixts】 库名
*/

#案例：创建books
CREATE DATABASE books;

#2.库的修改（一般不修改，贸然修改容易出现错误）
RENAME DATABASE books TO book;#修改库名已经不存在关键字

#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#3.库的删除
DROP DATABASE IF EXISTS books;

#二、表的管理
#1.表的创建
/*
create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)
*/
#案例：创建表book
CREATE TABLE book(
	id INT,
	bookname VARCHAR(20),
	price DOUBLE
)

#2.表的修改
#核心语法：alter table 表名 add|drop|modify|change column 【列类型|类型】

#i.修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubdate DATETIME; 

#ii.修改列的类型或约束
ALTER TABLE book CHANGE MODIFY pubdate TIMESTAMP;

#iii.删除列
ALTER TABLE book DROP pubdate;#不需要添加column


#iv.添加新列
ALTER TABLE book ADD COLUMN annual DOUBLE;

#v.修改列名
ALTER TABLE book RENAME TO book_author;


#3.表的删除
#drop table if exists 表名;

#4.表的复制
#i.仅仅复制表的结构
CREATE TABLE copy1 LIKE book;#但不会复制数据
#ii.复制表的结构和数据
CREATE TABLE copy2
SELECT * FROM book;
#iii.只复制表的部分数据
CREATE TABLE copy3
SELECT id
FROM book
WHERE id = 1;
#iv仅仅复制表的某些列而没有数据
CREATE TABLE copy4
SELECT id
FROM book
WHERE 1=2;#【即设置否命题】

数据类型

#字符型
/*
较短的文本：
	char

其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
	varchar
较长的文本：
	text
	blob(较大的二进制)
	
	
	
特点：
		写法			特点				空间的耗费	效率
									
char		char(M)			固定长度的字符  可以省略默认为1	比较耗费	高
varchar		varchar(M)		可变长度的字符	不可以省略	比较节省	低
*/


/*
日期类型：
日期和时间类型		字节 		最小值				最大值
date			4		1000-01-01			9999：12：31
datetime		8		1000-01-01 00：00：00		9999-12-31 23：59：59
timestamp		4		19700010108001			2038的某个时刻
time			3		-838：59：59			838：59：59
year			1		1901				2155


timestamp：时间戳，和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时间

*/

常见约束

/*
约束：限制表中的数据
分类：六大约束
	not null：非空，用于保证该字段的值不能为空，例如姓名
	DEFAULT:设置默认值，例如性别
	PRIMARY KEY：主键，默认非空且唯一不能重复，例如学号，身份证号
	UNIQUE:唯一，可以为空，例如座位号
	check：检查约束【mysql不支持】，添加条件以限制字段值只能在某些范围内，例如年龄，性别
	FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值
			在从表添加外键约束用于引用主表中的某列的值
			比如学生表的专业编号，员工表的部门编号

添加约束的时机：
1.创建表时
2.修改表但表内还没有数据时

约束的添加分类：
1.列级约束：六大约束语法时都支持，但外键约束没有效果
2.表级约束：除了非空和默认，其他都支持

主键和唯一的对比：
		唯一性		是否允许为空		一个表中可以有多少个		是否允许组合
主键		√  			否			一个			是，但不推荐，不稳定
唯一		√			是			多个			是，但不推荐，不稳定

键的组合形式：
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)，
	constraint pk primary key(id,majorName)

);

外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表的关联列必须是一种key（一般是主键或者唯一）
	4、插入数据时，先插入主表再插入从表；删除数据时，先删除从表再删除主表
*/
#一.创建表时添加约束
#1.添加列级约束 只支持主键、外键、唯一、默认 支持同个字段添加多个约束 直接加，用空格和其他约束隔开
CREATE DATABASE students;
USE students;

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)

);
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(10) NOT NULL,
	gender CHAR(1) CHECK(gender = '男' OR gender = '女'),
	seat INT UNIQUE,
	age INT DEFAULT 18,
	majorid INT REFERENCES major(id)
);

#查看表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

#添加表级约束
/*
语法：在各个定义字段的下面加：
	【constraint 约束名】 约束类型（字段名）【foreign key（字段名） references 表名（字段名）】
*/
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT,
	stuName VARCHAR(20),
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),
	CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)

)

#二、修改表时添加元素
/*
1.添加列级约束：
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束
2.添加表级约束：
ALTER TABLE 表名 ADD 【constriant 约束名】 约束类型(字段名) 【外键的引用】
*/

#1.添加非空约束(因为只支持通过列级约束方式添加)
#ALTER TABLE 表名 MODIFY COLUMN 字段名() NOT NULL;

#2.添加默认约束
#ALTER TABLE 表名 MODIFY COLUMN 字段名() INT(字符类型) DEFAULT 18(字符个数);

#3.添加主键
#i.列级约束
#ALTER TABLE 表名 MODIFY COLUMN 字段名 int(字符类型) PRIMARY KEY;
#ii.表级约束
#Alter TABLE 表名 ADD PRIMARY KEY(字段名);

#4.添加唯一
#i.列级约束
#ALTER TABLE 表名 MODIFY COLUMN 字段名 约束的字符类型 UNIQUE;
#ii.表级约束
#ALTER TABLE 表名 ADD UNIQUE(字段名)

#5.添加外键
#ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(从表字段名) references 主表名(关联字段名);


#三、修改表时删除约束
/*
方式一：
	按修改表时的语法进行删除，将尾部的约束类型设置为NULL即可
	如：#1、删除非空约束
	ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL; 

方式二（约束的字段是具有唯一性时可用，如主键，唯一键，外键）：
	删除主键：ALTER TABLE 表名 DROP PRIMARY KEY ;
	删除唯一：ALTER TABLE 表名 DROP INDEX 字段名；
	删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 约束名
	
通过以下两种方式删除主表的记录：
方式一：级联删除：
alter table stuinfo ADD CONSTRAINT fk_stu FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
会一次性删除所有内容
方式二：级联置空：
alter table stuinfo ADD CONSTRAINT fk_stu FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
只删除主表关联字段信息
*/

标识列

/*  
标识列：又称为自增长列，可以不用手动的添加值，系统提供默认的序列值
关键字：AUTO_INCREMENT

特点：
	1.标识列不一定是主键，但必须是一个key
	2.一个表最多只能有一个标识列
	3.标识列的类型：只能是数值型
	4.标识列可以设置步长SET auto_increment_increment = 3;#设置步长为3，
	  通过手动插入起始值来设置起始值
	  



*/
#一、创建表时设置标识列(即在约束名后面空格加上AUTO_INCREMENT)
CREATE TABLE tab_identity (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
#在插入值时，设置了标识符的字段在对应的字段值设置为nulll或直接省略，如：
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'John');
INSERT INTO tab_identity(NAME) VALUES('John');

#查看默认步长和起始值，auto_increment_increment是步长，auto_increment_offset是起始值，mysql不支持设置起始值
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment = 3;#设置步长为3


#二、修改表时设置表示列(即设置列级约束的语法，在约束后面空格加AUTO_INCREMENT)
ALTER TABLE tab_indentity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

#三、修改表时删除标识列(即设置列级约束的语法，在约束后面空格不加AUTO_INCREMENT)
ALTER TABLE tab_indentity MODIFY COLUMN id INT ;

TCL语言

TCL:即事务控制语言
- 事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行，整个执行单元是一个不能再分割的整体，如果其中的某一条语句执行失败，那么整个单元的语句将回滚。
- 存储引擎：在mysql中的数据用各种不同的技术存储在文件（或内存）中通过show engines;来查看mysql支持的存储引擎在mysql中用的最多的存储引擎有：innodb,myisam，memory等。其中innodb支持事务，其他不支持
- 事务的ACID属性：
  - 原子性：事务是不可分割的最小单位
  - 一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态
  - 隔离性：一个事务的执行不能被另一个事务干扰，即一个事务内部的才操作即使用的数据对并发的其他事务是隔离的
  - 持久性：一个事务一经提交，对数据库的改变就是永久性的。
事务的创建：
- 事务分类：
  - 隐式事务：事务没有明显的开启和结束的标记，比如insert，delete等，事务在一般情况下是默认开启的
  - 显示事务：事务具有明显的开启和结束的标志前提：必须事先设置自动提交事务功能为禁用语句：set autocommit=0；（一次执行并不是永久关闭事务）步骤1：开启事务 set autocommit=0； start transaction;可写可不写步骤2：编写事务中的sql语句（dql语句：seletct insert update delete而create alter等语句不可以）语句1 语句2 ... 步骤3：结束事务 commit；提交事务 rollback；回滚事务
  savepoint 结点名；设置保存点
并发事务的异常：对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题: ➢脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的. ➢不可重复读:对于两个事务T1，T2,T1读取了一个字段，然后T2更新了该字段.之后，T1再次读取同一个字段，值就不同了 ➢幻读:对于两个事务T1, T2, T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，结果命令行会显示和先前不一样的行数

事务并发异常的处理：

数据库提供的4种事务隔离级别:

隔离级别	描述
READ UNCOMMTTED(读未提交数据)	允许事务读取未被其他事物提交的变更，脏读,不可重复读和幻读的问题都会出现
READ COMMITED(读已提交数据)	只允许事务读取已经被其它事务提交的变更.可以避免脏读。但不可重复读和幻读问题仍然可能出现
REPEATABLE READ (可重复读)	确保事务可以多次从一个字段中读取相同的值在这个事务持续期问.禁止其他事物对这个字段进行更新可以避免脏读和不可重复读，但幻读的问题仍然存在
SERIALIZABLE(串行化)	确保事务可以从一个表中读取相同的行在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作.所有并发问题都可以避免，但性能十分低下，

● Oracle支持的2种事务隔离级别: READ COMMITED,SERIALIZABLE。Oracle 默认的事务隔离级别为: READ COMMITED ● Mysql支持4种事务隔离级别.Mysq|默认的事务隔离级别为: REPEATABLE READ

每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量@@tx_isolation，表示当前事务隔离级别
查看当前的隔离级别：select @@tx_isolation
设置当前mysql连接的隔离级别：set transaction isolation level read committed；
设置数据库系统的全局的隔离级别：set grobal transaction isolation level read committed；
mysql默认repeatable read ；Oracle 默认 read committed

#演示savepoint的使用
SET autocommit = 0;
START TRANSACTION;
DELETE FROM accoun WHERE id = 25;
SAVEPOINT a;#设置保存点
DELETE FROM accoun WHERE id = 28;
ROLLBACK TO a;#回滚到保存点 28号最终没删

delete和truncate在事务使用时的区别：执行了truncate不支持回滚而delete支持回滚

视图

#视图:虚拟表，和普通表一样使用，只保存是sql逻辑，不保存查询结果
/*
应用场景：
	多个地方用到相同的查询结果
	该查询结果的sql语句较为复杂
	
好处：重用sal语句
      简化复杂的sql操作，不必知道它的查询细节
      保护数据，提高安全性
      
视图和表的对比：
			语法		占用物理空间		使用上	
	视图			     占用很小一部分内存		增删改查，一般不增删改
	表				保存了数据		都可

*/
#一、创建视图
/*
语法：
create view 视图名
as
查询语句;

*/

USE myemployees;
#案例1：查询邮箱中包含a字符的员工名、部门名和工种名
CREATE VIEW my
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
#使用
SELECT * FROM my WHERE last_name LIKE "%a%";


/*
二、视图的修改
方式一：
create or replace view 视图名
as
查询语句；

方式二：
alter view 视图名
as
查询语句;

*/

/*
三、视图的删除
drop view 视图名1，视图名2......；
*/

/*
四、查看试图的结构
方式一：desc 视图名;
方式二：show create view 视图名;

*/

/*
五、视图的更新（更新数据）
视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。
包含以下关键字的sgl语句:分组函数、distinct、 group by 、having、 union或者union all
常量视图
Select中包含子查询
1oin
from一个不能更新的视图
where子句的子查询引用了from子句中的表

*/

#1.插入（对原始表也有影响，其他字段会值为null）
INSERT INTO 表名 VALUES('zzz','xx','xx');
#2.修改
UPDATE 表名 SET 字段名 = '' WHERE ;
#3.删除
DELETE FROM 表名 WHERE ...;
#为视图添加权限，只读不可修改，但一般也不修改视图

这篇关于Mysql数据库基础知识的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

Mysql数据库基础知识

数据库相关概念

MySQL服务的启动和停止

MySQL服务的登录和退出

MySQL的常见命令

MySQL的语法规范

DQL语言

基础查询

常见函数

分组查询

连接查询

子查询

分页查询

联合查询

DML语言

常见约束

标识列

TCL语言

视图

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯