mysql数据库基础知识

-- 数据库管理
-- 查询所有数据库
show databases;
-- Database
-- information_schema  mysql元数据，基础数据
-- mysql               mysql配置数据库，其中包含用户信息（用户名和密码，权限管理）
-- performance_schema  mysql数据库运行数据，日志信息，性能数据
-- test                测试数据库，空的

-- 创建数据库
create database 数据库名 default character set utf8;  --指定字符集
-- 查看数据库的默认字符集
show create database 数据库名;
-- 删除数据库
drop database 数据库名;
-- 修改数据库字符集
alter database 数据库名 default character set gbk;

-- 表管理
-- 查看所有表
show tables;
-- 创建表
create table 表名(          
	字段名 字段类型,
	字段名 字段类型,
	字段名 字段类型
);
-- 查看一张表的结构
desc 表名;
-- 删除表
drop table 表名;
-- 修改表
-- 添加表字段
alter table 表名 add (column) 新字段 新字段类型;
-- 删除表字段
alter table 表名 drop (column) 字段名;
-- 修改字段类型
alter table 表名 modify (column) 字段名 新的字段类型;
-- 修改字段名
alter table 表名 change 旧字段名 新字段名 新字段类型;
-- 修改表名
alter table 表名 rename (to) 新表名; 




--表数据管理
create table student(
	id int,
	NAME varchar(20),
	gender varchar(2);
	age int,
	jsp int,
	servlet int,
	address varchar(10)
);

--1.1 增加数据
-- 插入所有字段
INSERT INTO student VALUES(1,'张三'，'男',20);
-- 插入部分字段
insert into student(id, NAME) VALUES (2,'李四');

-- 1.2 修改数据
-- 修改所有数据（建议少用）
UPDATE student SET gender='女';
-- 带条件的修改(推荐使用)
UPDATE student SET gender='男' WHERE id=1; -- 修改id为1的学术，修改性别为男
-- 修改多个字段
UPDATE student SET gender='男', age=30 WHERE id=2;

-- 1.3删除数据
-- 删除所有数据(建议少用)
DELETE FROM student; -- 可以带条件删除 只能删除数据，不能删除约束
-- 另一种方式
TRUNCATE TABLE student;  -- 不能带条件删除 既可以删除数据，也可以删除约束

-- 查询数据
-- 查询所有列
SELECT * FROM student;
-- 2.查询指定列
select id,NAME from student;
-- 3.查询时指定别名
select id as '编号',NAME as '姓名' from student;
-- 4.查询时添加常量列
-- 在查询student表时添加一个班级列，内容为“java学习班”
select id,NAME,gender,age,'java学习班' as '班级'  from student; 
-- 5.查询时合并列
-- 需求：查询每个学生servlet和jsp的总成绩
select id,NAME,(servlet+jsp) as '总成绩' from student;
-- 注意：只能合并数值类型字段
-- 6.查询时去除重复记录
-- 需求：查询学术性别
select distinct gender from student;
-- 第二种方式
select distinct(gender) from student;
-- 查询学生所在地区
select distinct address from student;
-- 7.条件查询(where)
-- 7.1 逻辑条件：and    or
-- 需求：查询id为2，且姓名为李四的学生
select * from student where id=2 and NAME='李四';
-- 需求：查询id为2，或姓名为张三的学生
select * from student where id=2 or NAME='张三';
-- 7.2 比较条件：> < >= <= <> between and
-- 需求：查询servlet成绩大于70的学生
select * from student where servlet>70;
-- 需求：查询jsp成绩大于等于75，且小于90的学生
select * from student where jsp>=75 and jsp<=90;
-- 另一种写法
select * from student where jsp between 75 and 90;  --(包头包尾) 
-- 7.3 判空条件(null 空字符串):is null / is not null /=='' /<>''
-- null vs 空字符串
-- null:表示没有值
-- 空字符串：有值
-- 需求：查询地址为空的学生
select * from student where address is null or address=='';
-- 需求：查询地址不为空的学生
select * from student where adress is not null and address <>'';  
-- 7.4 模糊条件 like
-- % :表示任意个字符
-- _ :表示一个字符
-- 需求：查询姓张的学生
select * from student where NAME like '张%';
-- 需求：查询姓‘李’且姓名只有两个子的学生
select * from student where NAME like '李_';
-- 8.聚合查询
-- 常用聚合函数：sum() avg() max() count()
-- 需求:查询学生的servlet的总成绩
select SUM(servlet) as 'servlet的总成绩' from student;
-- 需求：查询学生的servlet的平均分
select AVG(servlet) as 'servlet的平均分' from student;
-- 需求：查询当前servlet最高分
select MAX(servlet) as '最高分' from student;
-- 需求：统计当前有多少学生
select count(*) from student;
-- 9.分页查询 (limit 起始行, 查询几行 )
-- 起始行从0开始
-- 分页： 当前页 每页显示多少条
-- 分页查询当前页的数据的sql：select * from student limit (当前页-1)*每页显示多少条， 每页显示多少条; 
-- 需求：查询第1，2条记录(第一页的数据)
select * from student limit 0, 2;
-- 需求：查询第3，4条记录(第二页的数据)
select * from student limit 2, 2;
-- 需求：查询第5，6条记录
select * from student limit 4, 2;
-- 10.查询排序 order by
-- asc:顺序，正序。数值：递增，字母：自然顺序(a-z)
-- desc:倒序，反序。数值：递减，字母：自然反序(z-a)
-- 需求：按照id顺序排序
select * from student order by id asc; 
-- 多个排序条件
-- 需求：按照servlet正序，按照jsp的倒序
select * from student order by servlet asc, jsp desc;
-- 11.分组查询(group by)
-- 需求：查询男女人数
-- 1) 把学生按照性别分组(GROUP BY gender)
-- 2）统计每组的人数（count(*)）
select gender, count(*) from student group by gender;
-- 12.分组查询后筛选
-- 需求：查询总人数大于2的性别
-- 1) 查询男女的人数
-- 2）筛选出人数大于2的记录(having)
select gender count(*) from student where group by gender having count(*)>2;


-- 外键
create table employee(
	id int primary key,
	empName varchar(20),
	deptId int
	-- 声明外键约束
	constraint employee_dept_fk foreign key(deptId) references dept(id) on update cascade on delete cascade -- 级联修改 级联删除
);

create table dept(
	id int primary key;
	deptName varvhar(20)
);

-- 关联查询
-- 需求： 查询员工及其所在部门
-- 交叉连接查询（笛卡尔积）
select empName,deptName from employee, dept;

-- 需求： 查询员工及其所在部门
-- 多表查询规则：1）确定查询那些表 2）确定哪些字段 3）表与表之间连接条件(规律：连接条件数量是表数量-1)
-- 内连接查询
select empName, deptName -- 2) 确定哪些字段
	from employee, dept  -- 1) 确定查询那些表
  	where employee.deptId=dept.id  -- 3) 表与表之间连接条件

-- 内连接的另一种写法
select empName, deptName
	from employee
	inner join dept
	on employee.deptId=dept.id
-- 使用别名
select e.empName, d.deptName
	from employee as e
	inner join dept as d
	on e.deptId=d.id

-- 需求：查询每个部门的员工
-- 预期结果：
-- 部门		员工
-- 左外连接查询：使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合则显示null
-- 左外连接：左边的数据一定会完成显示
select d.deptName, e.empName
	from dept d
	left outer join employee e
    on d.id=e.deptId;

-- 右外连接查询：使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合则显示null
-- 右外连接：右边的数据一定会完成显示
select e.empName,d.deptName
	from employee e
	right outer join dept d
	on e.deptId=d.id;


-- 自连接查询
--employee表结构：
-- id	empName	deptId	bossId
-- 1	  张三	  1		 null
-- 2	  李四	  1		  1
-- 3	  王五	  2		  2
-- 4	  陈六	  3		  3
-- 需求：查询员工及其上司
-- 预期结果：
	-- 张三 null
	-- 李四 张三
	-- 王五 李四
	-- 陈六 王五
select e.empName,b.empName
	from employee e
	left outer join employee b
	on e.bossId=b.id



-- 存储过程
-- 声明结束符号
delimiter $
create procedure 存储过程名()
begin
		-- 可以写多个sql语句
		select * from employee;
end $
-- 执行存储过程
call 存储过程名();

-- 参数
-- IN 表示输入参数，可以携带数据到存储过程
-- OUT 表示输出参数，可以从存储过程返回结果
-- INOUT 表示输入输出参数，既可以输入，也可以输出

-- 1.带有存储输入参数的存储过程
delimiter $
create procedure pro_findById(IN eID INT)
begin
		select * from empolyee where id=eID;
end $
call pro_findById(4);
-- 2.带有存储输出参数的存储过程
delimiter $
create procedure pro_testOut(OUT str varchar(20))
BEGIN
		-- 给参数赋值
		SET str='这是一个输出参数';
END $

-- ******mysql的变量******
-- 全局变量（内置变量）：mysql内置的变量
	-- show variables 查看全局变量
-- 会话变量：只存在于当前客户端与数据库服务器的一次连接当中。如果连接断开，那么会话变量会全部丢失!
	-- 定义会话变量 ：set @变量=值
	-- 查看会话变量 ：select @变量
-- 局部变量：只要存储过程执行完毕，局部变量就会丢失

-- 1)定义一个会话变量 2)使用NAME会话变量接收存储过程的返回值
call pro_testOut(@NAME)
-- 查看变量值
select @NAME

-- 删除存储过程
drop procedure 存储过程名;

-- 3.带有输入输出参数的存储过程
delimiter $
create procedure pro_testInOut(INOUT N INT)
begin
		-- 查看变量
		select n;
		set n=500;
end $
call pro_testInOut(10);
select @n;

-- 4.带有条件判断的存储过程
-- 需求：输入一个整数，如果1，则返回星期一。。。
delimiter $
create procedure pro_testIf(IN num int, OUT str varchar(20))
begin
		if num=1 then
				set str='星期一';
		elseif num=2 then 
				set str='星期二';
		elseif num=3 then
				set str='星期三';
		else 
				set str='输入错误';
		end if;
end $
call pro_testIf(1,@str)

-- 4.带有循环功能的存储过程
-- 需求：输入一个整数，求和
delimiter $
create procedure pro_testWhile(IN num int, OUT result int)
begin
		-- 定义一个局部变量
		declare i int default 1;
		declare vsun int default 0;
		while i<num do
				set vsum = vsum+i;
				set i=i+1;
		end while;
		set result=vsum;
end $

-- ******触发器******

create table log(
	id int primary key auto_increment,
	content varchar(100)
);
-- 需求：当向员工表插入一条记录时，希望mysql自动往日志表插入数据
-- 创建触发器(增加)
create trigger tri_empAdd after insert on employee for each row
		insert into log(content) values('员工表插入了一条记录');
-- 创建触发器（修改）
create trigger tri_empAdd after update on employee for each row
		insert into log(content) values('员工表修改了一条记录');
-- 创建触发器（删除）
create trigger tri_empAdd after delete on employee for each row
		insert into log(content) values('员工表删除了一条记录');

-- ******权限******
-- root:拥有所有权限
-- 权限账户，只拥有部分权限

-- mysql数据库，用户配置；user表
-- use mysql
SELECT * FROM USER

-- 修改密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

--分配权限账户
GRANT [SELECT|DELETE] ON 数据库名.表名 TO '账户名'@'localhost' IDENTIFIED BY '密码';


-- ******MYSQL备份和还原*******
-- 都无需登陆
-- 备份
mysqldumn -u root -p 数据库名 > c:/bf.sql
-- 恢复
mysql -u root -p 数据库名 < c:/bf.sql