数据库笔记

  • 时间:
  • 浏览:
  • 来源:互联网

DDL

创建表

create table student (
	id bigint,
	stu_name varchar(50),
	stu_age int
);

修改表

alter table student add stu_gender tinyint;  #添加一列
alter table student modify stu_name char(30);  #修改一列
alter table student change stu_name s_name char(30); #修改列名
desc student; #查看表结构
alter table student drop stu_name; #删除一列
drop table student; #删除表

DML

insert into student (id, stu_name, stu_age) value (1,'张三',18); #插入一条
insert into student values (2,'张三',18),(3,'张三',18),(4,'张三',18); #插入多条
update student set id = 5, stu_age = 22 where stu_name = '张三'; #修改记录
delete from student where id = 1; #删除记录
truncate table student; #删除表中所有记录 清空表结构

修改数据库密码(mysql8前)

use mysql;
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
flush privileges; #刷新MySQL的系统权限相关表

修改数据库密码(mysql8后)

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

命令行修改密码

mysqladmin -u root -p password 123456

DQL

普通查询

select * from student;
select stu_id,stu_name from student;

条件查询

select * from student where stu_sex = '男' and stu_age = 20;
select * from student where stu_name = '张三' or stu_id = 1; 
select * from student where id in(10001,10002,10003);
select * from student where stu_age is null;
select * from student where stu_age is not null;
select * from student where stu_sex != '男';
select * from student where stu_age between 18 and 20;

模糊查询

select * from student where stu_name like '_____'; #名字由5个字母组成的记录
select * from student where stu_name like '____s';
select * from student where stu_name like 'm%';
select * from student where stu_name like '_u%';
select * from student where stu_name like '%s%';

字段控制查询

select distinct stu_name from student; #去重
select  *,stu_age+stu_socre from student; #对应列相加
select *,IFNULL(stu_age,0)+ifnull(stu_socre,0) from student; #相加,如果为Null结果集变为0
select *,IFNULL(stu_age,0)+ifnull(stu_socre,0) as total from student; #取别名

排序

select * from employee order by salary; #默认升序(ASC)   降序DESC
select * from employee order by salary ASC;
select * from employee order by salary DESC,id DESC;

聚合函数

select count(*) from employee;  
select count(salary) from employee; #统计 salary不为null的记录
select count(*) from employee where salary > 2500;
select count(*) from employee where IFNULL(salary,0)+IFNULL(performance,0) > 5000;
select count(performance),count(manage) from employee;

select SUM(salary) from employee;
select SUM(salary),SUM(performance) from employee;
select SUM(salary + IFNULL(performance,0)) from employee;

select AVG(salary) from employee;

select max(salary) from employee;

select min(salary) from employee;

分组查询

select * from employee group by department;  #group by 单独使用时,只显示每组第一条记录
select department,GROUP_CONCAT(`name`) from employee group by department; #GROUP_CONCAT 将分组中对应字段的所有记录给出
select department,GROUP_CONCAT(salary),SUM(salary),AVG(salary) from employee group by department; #分组后对数据进行聚合函数计算
select department,count(*) from employee GROUP BY department;
select department,count(*) from employee where salary > 1500 GROUP BY department;

-- 先where再分组再having,having只能用于分组
select department,SUM(salary) as totle from employee GROUP BY department HAVING totle >= 9000;

select department,GROUP_CONCAT(salary),SUM(salary) from employee where salary > 2000 GROUP BY department having SUM(salary) > 6000 order BY SUM(salary) desc;   #查询工资大于2000的,工资总和大于6000的部门名称以及工资和,按工资和降序

Limit分页查询

select * from employee LIMIT 0,2;  #参数1: 从哪一行开始查(从0开始  参数2: 一共要查几行    

数据完整性

实体完整性

主键约束

primary key

-- 数据唯一,且不能为null
create table person(ID BIGINT primary key, name varchar(50));  #法一
create table person(ID BIGINT , name varchar(50),primary key(ID));  #法二
create table person(ID BIGINT , name varchar(50),primary key(ID,name));  #法三:联合主键

alter table student add constraint primary key(sid); #修改sid为 主键

唯一约束

unique

-- 指定列数据不能重复,可以为空
create table student (sid bigint primary key,sname varchar(50) unique);

自动增长列

auto_increment

create table student (sid bigint primary key auto_increment,sname varchar(20));

域完整性

域完整性约束

1.数据类型

2.非空约束(not null)

3.默认值约束(default)

create table student (sid bigint primary key auto_increment,sname varchar(20) unique not null, gender char(1) default '男');

参照完整性

create table stu(id int primary key, name varchar(50),age int); #创建基础表
create table score(sid int, score int, constraint sc_st_fk foreign key(sid) references stu(id));  #创建外键连接的表
alter table score add constraint sc_st_fk foreign key (sid) references stu(id); #修改外键表

表之间关系

-- 一对一
-- 一对多
	在多的表上建立外键指向主键
-- 多对多
	要创建中间表,中间表建立外键,指向两个主键
	create table teacher (tid int primary key auto_increment, name varchar(50));
	create table student (sid int primary key,name varchar(50));
	create table tea_stu_rel (tid int,sid int);  #关系表
	alter table tea_stu_rel add constraint foreign key (tid) references teacher(tid);
	alter table tea_stu_rel add constraint foreign key (sid) references student(sid);
	

多表查询

合并结果集 被合并的两个结果 列数、列类型必须相同

-- union 合并时去除重复记录
select * from aaa union select * from bbb;

-- union all 合并时不去除重复记录
select * from aaa union all select * from bbb;

连接查询

多表查询

-- 1.使用99连接法 / 隐式连接
select * from stu st,score sc where  st.sid = sc.sid;  -- 99查询写法

select  st.name,sc.score,c.cname from stu st,score sc,course c where st.sid = sc.sid and sc.cid = c.cid;

-- 2.内联查询写法
select * from stu st join score sc on st.sid = sc.sid join course c on sc.cid = c.cid;

内连接

-- 等值连接
	select * from stu st  inner join score sc on st.sid = sc.sid;
	select * from stu st join score sc on st.sid = sc.sid where sc.score > 60;  #inner可省略
-- 非等值连接
	select e.ename,e.salary,d.dname,g.grade from emp e,dept d,salgrade g where e.deptno = d.deptno and e.salary > g.lowSalary and e.salary <= g.highSalary;
	
	select e.ename,e.salary,d.dname,g.grade from emp e,dept d,salgrade g where e.deptno = d.deptno and e.salary between g.lowSalary and g.highSalary;
	
	SELECT e.ename,e.salary,d.dname,g.grade from emp e join dept d on e.deptno = d.deptno join salgrade g on e.salary BETWEEN g.lowSalary and g.highSalary;
-- 自然连接 两表列名和类型一样
	select * from stu natural join score; #要有相同列名

外连接

-- 左外连接  如果左表有不同数据,也把左表数据查出来  左表全查,右表只查满足条件的
select * from stu st left outer join score sc on st.id = sc.sid;
-- 右外连接
select * from stu st right join score sc on st.id = sc.sid;  #outer可省略


自然连接

-- 列表名和类型要一致  若出现多个匹配的 要同时满足多个列

子查询

-- 放where后 作为条件
select ename from emp 
where deptno = (select deptno from emp where ename = '项羽');

-- 放from后 作为新表
select ename from 
(select ename,salary,deptno from emp where deptno = 30) s
where s.salary > 2000;



select ename,salary from emp where salary > (select salary from emp where ename = '程咬金');

select ename,salary from emp where salary > (select max(salary) from emp where deptno = 30);

#多条件满足 法一
select * from emp 
where (job,salary) 
in (SELECT job,salary from emp where ename = '妲己');

#多条件满足 法二
SELECT * from emp,
(SELECT job,salary from emp where ename = '妲己') s
where emp.job = s.job and emp.salary = s.salary;

#查询有2个以上直接下属的员工信息
SELECT * from emp where empno in 
(select mgr from emp
 GROUP BY mgr having count(mgr) >= 2
);


select emp.ename,emp.salary,dept.dname,dept.`local` from emp,dept where emp.deptno = dept.deptno and emp.empno = 7788;

自连接

-- 自己连接自己
# 求7369员工编号、姓名、经理编号、经理姓名
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2
where e1.mgr = e2.empno
and e1.empno = 7369;

常用函数

可用在select update delete语句中

字符串函数

-- concat 拼接字符串
select concat('aaa','bbb','ccc');  #任意字符串与null相连都为null
select CONCAT('111','222','333') -> 111222333

-- insert('abcdefg',3,2,'**');
select INSERT('123456789',3,2,'**') -> 12**56789

-- LOWER(xxx) UPPER(xxx) 大小写
-- left(str,x) right(str,x) 返回最左边的x个字符和最右边x个字符  如果第二参数为Null 就不返回
select left('123456789',3) -> 123

-- lpad(str,len,str) Rpad()
select LPAD('test',6,12345) -> 12test
select RPAD('test',6,12345) -> test12

-- Ltrim() Rtrim() trim()  去左边 / 右边/ 左右 空格
-- repeat(str,count) 重复count次
select REPEAT('123',4) -> 123123123123

-- replace(str,a1,a2) 用a2替换str中的a1
select REPLACE('test','te','a') -> ast

-- substr(str,num,num2) 截取字符串
select substr('abcdfgh',2,3) -> bcd

数值函数

-- abs(x)绝对值
-- ceil(x)向上取整
-- floor(x) 向下取整
-- mod(x,y)求余
-- rand() 随机0-1的值
select ceil(RAND() * 10);

日期时间函数

-- curdate()  
2020-08-30
-- curtime()
16:52:27
-- now()
2020-08-30 16:52:46
-- UNIX_TIMESTAMP()
1598777602
-- FROM_UNIXTIME(x) 时间戳转日期
select FROM_UNIXTIME(1598777602) -> 2020-08-30 16:53:22
-- week(date) 当前是一年第几周
-- year(date) hour(time) minute(time)
-- date_format(date,fmt) 按字符串格式化日期date值
-- date_add(date,xxx) 日期相加减
select DATE_ADD(NOW(),INTERVAL 31 DAY) -> 2020-09-30 16:59:46
-- datediff(date1,date2) 计算两日期相差天数
select DATEDIFF('2020-7-10',NOW()) -> -51

流程函数

-- if(value,t,f) 如果真,返回t,否则返回f
select IF((SELECT salary from emp where ename = '李白')>5000,'经理','员工')
-- ifnull(value1,value2) 如果value1不为空返回value1 否则返回value2
-- case when thn end   等同于 if else

其他函数

-- database() 返回当前数据库名
-- version() 当前数据库版本
-- user() 当前用户
-- password(str) 对str进行加密
-- md5() 返回str的md5值

事务

事务的使用

start transaction;  #开启事务
update account set money = money - 2000;  #操作
update account1 set money = money + 2000;  #操作
commit; #提交事务

rollback; #回滚事务

并发问题

-- 脏读:  读取到了未提交事务的数据,解决方法read committed 读提交,解决脏读问题
select @@global.tx_isolation,@@rx_isolation; #查看隔离级别
mysql8中: SELECT @@global.transaction_isolation,@@transaction_isolation; 
-- 不可重复读: 一个事物范围内两个相同查询返回了不同数据
-- 重复读:事务开启,不允许其他事务update修改操作
-- 幻读:在事务开启查询时,又进行了更新操作

隔离级别(从低到高

read uncommitted  #一个事务可以读取另一个未提交事务的数据
read committe #一个事物要等另一个事务提交后才能读取数据 (读提交
repeatable read (默认 #无法避免幻读
serializable 


set global transaction isolation level read uncommitted; #设置隔离级别read uncommitted 

权限

用户

create user '用户名'@'localhost' identified by '密码'; #创建用户
drop user '用户名'@'localhost';  #删除用户

grant 权限 on 数据库对象 to 用户 identified by "密码" with grant option;

grant all PRIVILEGES on *.* to 'zic'@'localhost' with grant option;
flush privileges;

grant select,insert,update,delete on my_test.stu to 'zic'@'localhost';

show grants; #查看权限
show grants for root@localhost; #查看指定用户权限

revoke 权限 on 数据库对象 from 用户; #删除权限

视图

由select结果集组成的表

替换式:操作视图时,将视图当作一个sql语句与新的语句组合

具化式:将视图数据存在内存中,再进行查询(不可更新

create view 视图名 (列名) as xxxx with check option;  # with check option 不能插入或更新不符合视图限制条件的记录
create view emp_salary_view as (select * from emp where salary > 2000); #创建视图
select * from emp_salary_view WHERE job = '经理'; #视图的查询
create or replace view 视图名 as xxxxxx; #修改视图
drop view 视图名; #删除视图


CREATE ALGORITHM=MERGE view emp_salary_Views
as (select ename,salary from emp); #merge 是替换式

-- 只要视图中的数据不来自于基表字段(使用聚合函数等) 就不可更改

存储过程

delimiter 将 分 隔 符 分 号 ; 改 为 将分隔符 分号;改为 ;

创建及调用

 delimiter $$
 -- 创建存储过程
CREATE PROCEDURE show_emp()
BEGIN
	select * from emp;
END$$

-- 调用存储过程
delimiter ;
call show_emp();

-- 查看存储过程
show PROCEDURE STATUS;
show PROCEDURE STATUS where db = 'Mystore';
show create PROCEDURE show_emp; #查看创建源码

-- 删除存储过程
drop PROCEDURE show_emp;

存储过程变量

-- 声明变量
declare 变量名 数据类型(大小) default 默认值;
同时声明多个:  declare x,y int default 0;

-- 分配变量值
set x = 3;
select avg(salary) into avgRes from emp; #将vag(salary)赋值给avgRes

delimiter $$
create procedure test()
BEGIN
-- 声明变量
declare res varchar(255) default '';
declare x,y int default 0;
select avg(salary) from emp;
set x = 3;
set y = 4;

END $$

存储过程参数

in 传入参数
out  输出参数
inout 又输入输出

-- 根据传入名称,获取对应的信息
delimiter $$
create procedure getName(in name varchar(255))
begin 

select * from emp where ename = name;

end $$
delimiter ;

call getName('鲁班');


-- 给我一个名字,把薪资给你
delimiter $$
create procedure getSalary(in name varchar(255),out salary int)
begin 
select salary into salary from emp where ename = name;
end $$
delimiter ;

call getSalary('鲁班',@s);
select @s;  #select @s from dual;

-- 变量相加
delimiter $$
create procedure test(inout num int, in inc int)
begin
set num = num + inc; 
end $$
delimiter ;


set @num1 = 20;
call test(@num1,10);
select @num1;

存储过程语句

-- if语句

if expression then 
	statements;
end if;
###
if expression then
	statements;
else
	else-statements;d
end if;


-- case语句

case case_expression
	when xxxx then xxx
	when xxxx then xxx
	else xxx
end case;

-- 循环
while xxx do
	xxxxxx
end while;

###

repeat 
	xxx;
until xxxx
end repeat;

自定义函数

-- 随即生成指定个数字符串
delimiter $$
create function rand_str(n int) returns varchar(255)
BEGIN
-- 声明一个str 52字母
DECLARE str varchar(100) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
-- 记录当前第几个
declare i int default 0;
-- 生成结果
declare res_str varchar(255) default '';

while i < n DO
	set res_str = concat(res_str,SUBSTR(str,FLOOR(1+RAND()*26),1));
	set i = i + 1;
end while;
return res_str;
end $$

delimiter ;

###
select rand_str(5);

插入千万条数据

-- 插入千万条数据
delimiter $$
create procedure insert_emp(in startNum int,in maxNum int)
begin 
-- 声明一个变量 记录当前是第几条数据
declare i int default 0;

-- 默认情况下是自动提交sql
set autocommit = 0; -- 不让他自动提交sql
repeat 
set i = i + 1;
insert into emp1 values(startNum+i,rand_str(5),FLOOR(10+RAND()*30));
until i = maxNum
end repeat;
commit; -- 整体提交所有的sql 提高效率
end $$

delimiter ;

索引

一般经常查询的字段才创建索引

本文链接http://xiahunao.cn/article/show-994406.html