MYSQL

mysql

基本命令

启动关闭mysql服务:

net start mysql80(本机的mysql服务) 管理员身份cmd

net stop mysql80

mysql登录和退出：

登录:
mysql [-h 主机名 -P 端口号]([]里面命令当连接本机mysql时可以省略) -u用户名  -p密码

退出:
exit/ctrl+c

mysql常用命令:

1.查看当前所有的数据库:
show databases;

2.打开指定的数据库:
use 数据库名字

3.查看当前数据库包含的所有表:
show tables;

4.查看其他库的所有表:
show tables from 库名;

5.创建表:
create table 表名(
字段名 数据类型,
字段名 数据类型,
.......
);

6.查看表结构:
desc 表名;

基本查询语句:

语法:
select 查询列表 from 表名
查询列表可以是表中字段,常量值,表达式,函数

条件查询:

语法:
select 
     查询列表
from 
     表名
where
     条件筛选
     
关键执行顺序:
from, where, group by, having, select, order by, limit

条件表达式:
简单的条件运算符:> < = != <> >= <=

逻辑运算符:
&& || ! and or not
作用:
用来连接条件表达式

模糊查询:

 like , between and , in , is null
 
 like模糊查询:
支持%或下划线匹配,%匹配任意多个字符,下划线:任意一个字符

例子:查询员工名中第二个字符为_的员工
select 
    last_name
from 
	  employees
where
	  last_name like '_\_%';#\转译字符

select 
    last_name
from 
	  employees
where
	  last_name like '_$_%' escape '$';
# escape '$': 描述'$'为转义符

between and:

查询包扩边界

#查询员员工奖金不在12000和15000之间的
SELECT 
      salary as '奖金'
FROM
		  employees
where
		  salary NOT BETWEEN 12000 AND 15000;

in:

判断某字段是否属于in列表中的某一项

is null:

=或<>不能用于判断null值
is null或is not null 可以判断null值固定语法.

<=>安全等于:

既可以判断null值,也可以判断普通的值

#查询员工号为176的员工的姓名和部门和年薪
SELECT
       last_name,
			 department_id,
			 salary*12*(1+IFNULL(commission_pct,0))
FROM 
       employees
WHERE
			employee_id = 176;	 

distinct去重:

select distinct 字段名 from 表名

concat字符串拼接:

select concat(字符1,字符2,字符3,.....)
将字符拼接起来

ifnull函数:

功能:
判断某字段或表达式是否为null,如果为null换为指定的值,否者返回原值。
select infull(commission_pct,0) from employees;

isnull函数：

功能:
判断某字段或表达式是否为null,如果是,返回1(true),否者返回0(false)

order by排序查询:

语法:
select 查询字段
from 表
[where 筛选条件]
order by 排序列表 [asc desc]

#案例:查询员工信息,要求先按照工资升序，在按照员工编号降序排列【按多个字段排序】
SELECT * 
FROM employees
ORDER BY salary asc,employee_id desc;
先按照salary升序排序，当salary相等时,在按照employee_id降序排列.

分组查询:

常见的的分组函数:
min()  max()  avg()  count()  

语法：
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
having 筛选条件
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
where是先筛选在分组
having 是先筛选在分组

问题1：分组函数做筛选能不能放在where后面
答：不能

问题2：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

#按多个字段分组
#查询每个工种,每个部门的部门名,工种名和最低工资
SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;			 

分页查询:

select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset】size;
	
	offset要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
特点：
	①limit语句放在查询语句的最后
	②公式
	要显示的页数 page，每页的条目数size
	
	select 查询列表
	from 表
	limit (page-1)*size,size;

连接查询:

sql99语法:
    select 查询列表
		from 表1  别名 
		[连接类型] join 表2  别名 
		on  连接条件
		[where 筛选条件]
		[group by 分组]
		[having 筛选条件]
		[order by 排序]
分类:
内连接:inner[连接类型]
外连接
       左外:left outer[连接类型]
			 右外:right outer[连接类型]
			 全外:full[连接类型]
 交叉连接:cross[连接类型]

常用函数:

字符函数：

调用:select 函数名(实参列表) from 表(当函数中用到了表中的字段才需要from表)

#lenth() 获取参数数值的字节个数
select lenth('john');

#concat 拼接字符串
select concat(last_name,'_',first_name) from employees;

#upper,lower 大小写
select upper('john');
select lower('joHn');

#sustr  截取字符串 
//截取从指定索引处后面的所有字符
select substr('李莫愁爱上了陆展元',7)  sql语句索引从1开始

//截取从指定索引处指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) out_put(别名)  from employees;

#instr
//返回字串第一出现的索引,找不到返回零
select instr('杨不悔爱上殷六侠','殷六侠');

#trim 
//去除前后的空格,也可以指定去出的元素
select length(trim('    刘亦菲     '));
//去前后的a
select trim('a' from 'aaaaaaaaa刘亦菲aaaaaaa邓紫棋aaaaa');

#lpad 用指定的字符实现左填冲指定长度
select  lpad('小龙女',12,'*');

#rpad 用指定的字符实现右填冲指定长度
select  rpad('小龙女',12,'*');

#replace 替换
select replace('张无忌爱上了周芷若','周芷若','赵敏');

数学函数:

#round 四舍五入
select round(1.55); (2)
select round(1.567,2) (保留两位)

#ceil 向上取整,返回大于等于该参数的最小整数
select ceil(-1.02);

#floor 向下取整,返回<=该参数的最大整数
select floor(-9.99);

#truncate 截断
select truncate(1.69999,1); (1.6)

#mod 取余

日期函数:

#now 返回当前系统日期+时间
select now();

#curdate 返回当前系统日期,不包含时间
select curdate();

#curtime 返回当前时间,不包含日期
select curtime();

#获取指定部分,年,月,日,小时,分钟,秒
select year(now()) 年;
select year(hiredate) 年 from employees;

其他函数:

select version(); 查看版本号
select database();  查看当前库
select user();  查看当前用户

流程控制函数:

#流程控制函数
#if函数  if()类似于三元运算符

select if(10>5,'大','小');


case函数的使用:放select后面用:

case  要判断的字段或表达式
when  常量1 then 要显示的值1或表达式;
when  常量2 then 要显示的值1或表达式;
......
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;
		

#2 case 函数的使用二:
case 
when 条件一 then 要显示的值1或语句1
when 条件二 then 要显示的值2或语句2
when 条件三 then 要显示的值3或语句3
......
else 要显示的值n或语句n
end

DML语言:

#DML语言//对数据的操作
/*
数据操作语言:
插入:insert
修改:update
删除:delete
*/

#一:插入语句
/*
语法:
方式一:
insert into 表名(列名(字段名).......) values(值1,......);
方式二:
insert into 表名
set 字段名=值,字段名=值

特点:
方式一:支持批量插入数据
insert into 表名
values(值1......),(值2);

方式一:支持子查询

*/

#插入到girls中beauty表中数据
insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
values(13,'唐艺昕','女','1990-4-23','15537660403',null,2);
#可以为空的字段和数据可以不用写

insert into beauty 
set id = 19,name = '邓紫棋', phone = '999';
select * from beauty;

#批量插入多个数据
insert into beauty
values(14,'刘亦菲','女','1990-3-2','1890000033',null,3),
(15,'李庚希','女','1990-3-2','1890000033',null,1),
(16,'杨幂','女','1990-3-2','1890000033',null,3);

#子查询
insert into beauty(id,name,phone)
select id,boyname,'123456'
from boys where id<3;

#二:修改语句
/*
1.修改单表记录:
语法:
update 表名
set 列=新值,列=新值,.....
where  筛选条件;

2.修改多表的记录
语法:
92语法
update 表1 别名,表2 别名
set 列=值,....
where 连接条件
and 筛选条件;

99语法:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,....
where 筛选条件;

*/

#单表
#案例:把姓唐的女神中的电话改为18903973666
update beauty
set phone = '18903973666'
where name like '唐%';

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



#三:删除语句
/*
方式一:delete 
语法:
1.单表删除
delete from 表名 where 筛选条件  //只删除符合条件的数据
delete from 表名 //删除表中的所有数据不删除表结构

2.多表的删除
92语法:
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

99语法:
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 
on 连接条件
where 筛选条件

DELETE sr,
 srd,
 srm
FROM
	sys_role sr
LEFT JOIN sys_role_dept srd ON sr.role_id = srd.role_id
LEFT JOIN sys_role_menu srm ON srm.role_id = sr.role_id
WHERE
	sr.role_id = 1



方式二:
truncate 
语法:
truncate table 表名 //删除表的所有数据包括表的结构

*/

#方式一:delete
#1.单表的删除
#案例1:删除手机号以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 = '张无忌';

DDL语言:

#DDL语言
/*
数据定义语言
库和表的管理
一.库的管理
创建  修改  删除
二.表的管理
创建: create 
修改: alter
删除: drop

*/

#库的管理
#1.库的创建
/*
语法:
create database [if no exists] 库名;

*/
#2.库名的修改
/*
语法:
rename database 库名 to 新库名

*/
#3.更改库的字符集
/*
语法:
alter database 库名  CHARACTER set gbk;

*/

#库的删除:drop database 库名;


#二:表的管理
#表的创建
/*
语法格式:
create table 表名(
   列名  列的类型[(长度) 约束],
   列名  列的类型[(长度) 约束],
   列名  列的类型[(长度) 约束],
   .......
   列名  列的类型[(长度) 约束]
)【character set】;"指定表的字符集,不指定默认数据库的字符集".
*/

create database books;

use books;

create table book(
        id int, #书的编号
		name varchar(20), #书的名字
		price double, #价格
		authorid int #作者
);

create table author(
        id int,
		name varchar(20),
		location varchar(10)
);

desc book;
desc author;


#2.表的修改

/*
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

*/

#①修改列名(b)

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;


#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE; 

#④删除列

ALTER TABLE book_author DROP COLUMN  annual;
#⑤修改表名

ALTER TABLE author RENAME TO book_author;

DESC book;


#3.表的删除

DROP TABLE IF EXISTS book_author;

SHOW TABLES;

#通用的写法：

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

#表的复制:(后跟子查询把查询结果一样插入到对应表的列中)
insert into author values
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'金庸','中国');

select * from author;

存储过程:

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作

*/
#存储过程
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

*/

#一、创建语法

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

#注意：
/*
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

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

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/

#二、调用语法

CALL 存储过程名(实参列表);

#--------------------------------案例演示-----------------------------------
#1.空参列表
#案例：插入到admin表中五条记录

SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $


#调用
CALL myp1()$

#2.创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息

CREATE PROCEDURE myp2(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('柳岩')$

#案例2 ：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'成功','失败');#使用
END $

#调用
CALL myp3('张飞','8888')$


#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName ;
	
END $


#案例2：根据输入的女神名，返回对应的男神名和魅力值

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName ;
	
END $


#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$

#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×

#四、查看存储过程的信息
SHOW CREATE PROCEDURE  myp2;

函数:

#函数
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

*/

#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
/*

注意：
1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记

*/

#二、调用语法
SELECT 函数名(参数列表)


#------------------------------案例演示----------------------------
#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN

	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
	
END $

SELECT myf1()$


#2.有参有返回
#案例1：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

SELECT myf2('k_ing') $

#案例2：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('IT')$

#三、查看函数

SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;

#案例
#一、创建函数，实现传入两个float，返回二者之和

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$

流程控制结构:

#流程控制结构
/*
顺序、分支、循环
*/

#一、分支结构
#1.if函数
/*
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面

*/

#2.case结构
/*
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end 

情况2：
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end 

应用在begin end 中或外面

*/

#3.if结构

/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if

只能应用在begin end 中

*/

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

delimiter $ //定义结束标识符
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $

SELECT test_if(87)$


#二、循环结构
/*
分类：
while、loop、repeat

循环控制：

iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于  break，跳出，结束当前所在的循环

*/

#1.while
/*

语法：

【标签:】while 循环条件 do
	循环体;
end while【 标签】;

*/

#2.loop
/*

语法：
【标签:】loop
	循环体;
end loop 【标签】;

可以用来模拟简单的死循环

*/

#3.repeat
/*
语法：
【标签：】repeat
	循环体;
until 结束循环的条件
end repeat 【标签】;


*/

#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
	
END $

CALL pro_while1(100)$

#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $


CALL test_while1(100)$


#3.添加iterate语句

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $

CALL test_while1(100)$

视图:

#视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

比如：舞蹈班和普通班级的对比
创建语法的关键字	是否实际占用物理空间	使用

视图	create view	 只是保存了sql逻辑	增删改查 只是一般不能增删改

表	create table		保存了数据		增删改查

*/

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

*/

列:
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
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 myv1 WHERE last_name LIKE '%a%';

#二、视图的修改

#方式一：
/*
create or replace view  视图名
as
查询语句;

*/
SELECT * FROM myv3 

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;

*/
ALTER VIEW myv3
AS
SELECT * FROM employees;

#三、删除视图

/*

语法：drop view 视图名,视图名,...;
*/

DROP VIEW emp_v1,emp_v2,myv3;

#四、查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

#五、视图的更新

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

SELECT * FROM myv1;
SELECT * FROM employees;
#1.插入

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';

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


#①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all

事务:

#TCL
/*
Transaction Control Language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.

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

delete from 表 where id =1;

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

set autocommit=0;
*/

#1.演示事务的使用步骤

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';

#结束事务
ROLLBACK;
#commit;

约束:

#常见约束

/*


含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性


分类：六大约束
	NOT NULL：非空，用于保证该字段的值不能为空
	比如姓名、学号等
	DEFAULT:默认，用于保证该字段有默认值
	比如性别
	PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
	比如学号、员工编号等
	UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
	比如座位号
	CHECK:检查约束【mysql中不支持】
	比如年龄、性别
	FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
		在从表添加外键约束，用于引用主表中某列的值
	比如学生表的专业编号，员工表的部门编号，员工表的工种编号
	

添加约束的时机：
	1.创建表时
	2.修改表时

约束的添加分类：
	列级约束：
		六大约束语法上都支持，但外键约束没有效果

	表级约束：
		除了非空、默认，其他的都支持
		
主键和唯一的大对比：

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

*/

CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束

)
CREATE DATABASE students;
#一、创建表时添加约束

#1.添加列级约束
/*
语法：

直接在字段名和类型后面追加 约束类型即可。

只支持：默认、非空、主键、唯一

*/

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

#2.添加表级约束
/*
语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
    //FOREIGN KEY(majorid) REFERENCES major(id)#外键
    [CONSTRAINT <外键名>] 
FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]
);



#二、修改表时添加约束

/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;


*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 


#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

创建用户:

#创建用户语法:
create user '用户名' @'主机名' identify by '密码';
#删除用户:
drop user '用户名'@'主机名';
#修改用户密码:
update user set password = password('新密码');
或
set password for '用户名'@'主机名' = password('新密码');
#查询用户:
select * from user;

#%表示可以在任意主机使用用户登录数据库