数据库学习 ( 更新至2024-04-09 )

ikunhuaji

已于 2024-04-22 20:10:51 修改

阅读量1.3k

点赞数 34

文章标签：数据库

于 2024-04-08 19:55:53 首次发布

本文链接：https://blog.csdn.net/qq_66769400/article/details/137521532

版权

数据库学习

编写CSV管理

mkdir score.c//创建文件
cat scores.csv//打开文件
cat scores.csv | wc -l//统计行数
touch score.sh//创建脚本
ll //查看脚本名

vi scoresh//进入脚本

insert 按键进入插入模式

echo hello
echo world

ESC + : + x 退出vi编辑

bash score.sh//运行脚本

查找学号数据

echo -n 'input student no: '//打印
read no//读取学号
echo $no//打印读到的学号
grep $no scores.csv//查找表中对应学号信息

查找对应学号的对应成绩信息

echo -n 'input student no: '
read no
echo $no
grep $no scores.csv
echo -n 'input course no(c[3],pytho[4],ds[5]): '//因为原表中为 学号 姓名 c成绩 py成绩 ds成绩
read col//读取想要的课程
echo $col
grep $no scores.csv | awk -F ',' -v n=$col '{print $n}' | sed 's/\"//g'//打印no学号的col成绩并去双引号

demo.sh

showMenu() {
  clear
  echo -e "\t[1] 查询学生成绩"
  echo -e "\t[2] 添加学生成绩"
  echo -e "\t[3] 修改学生成绩"
  echo -e "\t[4] 删除学生成绩"
  echo -e "\t[0] 退出\n"
  echo -ne "\t请输入操作编号："; read menu
}

showMenu

while [ $menu -eq $menu ];
do
case $menu in
  1) # 查询学生成绩
    echo -ne "\n请输入学生编号："; read no
    echo -ne '[3] c\n[4] python\n[5] ds\n请输入课程编号：'; read col
    grep $no scores.csv | sed 's/\"//g' | awk -F ',' -v n=$col '{print $n}' | xargs -I{} echo 'score:' {}
    echo -n '按任意键继续...'; read
    showMenu
    ;;

  2) # 添加学生成绩
    echo -e "\n输入格式：\"学号\",\"姓名\",\"c-score\",\"python-score\",\"ds-score\""
    read row
    echo $row >> scores.csv
    echo -n '按任意键继续...'; read
    showMenu
    ;;

  3) # 修改学生成绩
    echo -ne "\n请输入学生编号："; read no
    echo -ne '[3] c\n[4] python\n[5] ds\n请输入课程编号：'; read col
    echo -ne '请输入课程成绩：'; read score
    old=$(grep $no scores.csv)
    new=$(grep $no scores.csv | sed 's/"//g' | awk -F ',' -v n=$col -v s=$score '{$n = s}{print}')
    new=$(echo $new | sed 's/ /","/g' | sed 's/^/"/g' | sed 's/$/"/g')
    sed -i "s/$old/$new/g" scores.csv
    echo -n '按任意键继续...'; read
    showMenu
    ;;

  4) # 删除学生成绩
    echo -ne "\n请输入学生编号："; read no
    sed -i -e "/^\"$no/d" scores.csv
    echo -n '按任意键继续...'; read
    showMenu
    ;;

  0) # 退出系统，正常退出，退出码为 0
    exit
    ;;

  *) # 输入不合法，异常退出，退出码为 $menu
    exit $menu
    ;;
esac
done

mysql使用

1.创建数据库

create database mydatabase ;

2.使用

use mydatabase;

3，创建表

CREATE TABLE user( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,password VARCHAR(50) NOT NULL);

4.录入数据

INSERT INTO user(username,password)VALUES('ikunhuaji','114514');

5.查看表

select * from user;

6.查看当前数据库下的表

show tables;

SQL注意点

select * from employees order by salary ; # 按 salary 降序

select可计算 :

select 1+1;
select 3.14*power(4,2) ;

2进制计算

select 1+0b10 # 0b表示2进制，返回值为 3

16进制计算

select 1+0x10 # 0x表示16进制，返回值为 17

变量名要求：不使用 key word, 可用字母数字下划线，大小写敏感，不重名。

查询版本

select version() ;

查询时间

select now() ;

查找变量

select first_name from employees ;

select first_name,salary from employees ; # 多个变量

查询作业

use northwind ;

查询格式

desc employees ;

查询工资大于 12000 的员工

select * 
from employees 
where salary > 12000 ;

查询部门编号不等于 90 的员工 last_name 和部门编号

select last_name,department_id 
from employees 
where department_id != 90 ;

查询工资在 10000 到 20000 之间的员工 last_name、工资以及提成

select last_name,salary,commission_pct 
from employees 
where salary between 10000 and 20000 ;

查询部门编号不是在 90 到 110 之间，或者工资高于 15000 的员工信息

select * 
from employees 
where department_id between 90 and 110 or salary > 15000 ;

查询员工 lastname 中包含字符 a 的员工信息

select * 
from employees 
where last_name like '%a%' ;

查询员工 lastname 中第三个字符为 n，第五个字符为 l 的员工 last_name 和工资

select last_name,salary 
from employees 
where last_name like '__n_l%' ;

// ’_‘表示单个字符 ， ‘%’ 表示多个字符

查询员工 job_id 中第三个字符为 _ 的员工 last_name 和 job_id

select last_name,job_id 
from employees 
where job_id like '__\_%' ; 

// '\_' 转义，只为下划线

查询没有提成的员工 last_name 和提成

select last_name,commission_pct 
from employees 
where commission_pct is null ;

排序

select * 
from * 
order by 字段 方向（默认升序） // asc/升序, desc/降序

查询所有信息，按工资降序排序

select * 
from employees 
order by salary desc ;

查询部门编号大于等于 90 的员工信息，并按员工编号降序排序

select * 
from employees 
where department_id >= 90 
order by employee_id desc ;

// order by 在 where 前

查询所有员工信息，按年薪降序排序

select employee_id,last_name,(salary*12) as year_salary ,commission_pct
from employees 
order by year_salary desc ;

//通过 as 定义 salary*12 为 year_salary

分页

select employee_id,last_name,(salary*12) as year_salary 
from employees
order by employee_id
limit 2,10 ;

//从第3个开始选10个

查询有提成，且工资最高的前 10 位员工信息

select last_name,salary,commission_pct from employees
where commission_pct is not null
order by salary desc
limit 10 ;

// 即 0,10 ;

函数

length() ：字节数

char_length()：返回字符数

select length('hello') ; //5
select length('坤坤') ; //6，1个文字3字节
select char_length('坤坤') ; // 2

concat

select concat(last_name,',',salary) from employees ;

// 合并数据

truncate 小数保留

select last_name,truncate(salary/30,1) from employees ; //

now()

select now(),curdate(),curtime() ;

查询员工编号、工作的年数 work_years（保留小数点后 1 位）、工作的天数 work_days，并按工作年数的降序排序

select employee_id,
truncate(TIMESTAMPDIFF(DAY,hire_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))/360,1) as work_years,
TIMESTAMPDIFF(DAY,hire_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) as work_days
from employees
order by work_days desc;

select lpad('hello',7,'*'); // **hello
select rpad('hello',7,'*'); // hello**

集合

wget http://sample.wangding.co/dbms/set.sql
sql < set.sql
sql -e "show databases;"

并

(select * from set_a)
UNION
(select * from set_b);

交

(select * from set_a)
INTERSECT
(select * from set_b);

差

(select * from set_a)
EXCEPT
(select * from set_b);

积

select * from set_a, set_c;

select * 
from set_a
cross join set_c;

运算

sum()

select sum(salary) from employees ;

count()

select count(*)
from employees
where department_id = 80 ;

select department_id, count(*) as num, sum(salary) as cost
from employees 
group by department_id
order by num desc ;

max(),min() 查询员工最高工资和最低工资的差距 diff

select max(salary) - min(salary) as diff
from employees ;

truncate() 查询工种编号和该工种的平均工资 average_salary（去掉小数部分）

select job_id,truncate(avg(salary),0) as  average_salary
from employees 
group by job_id
order by  average_salary ;

分组前筛选查询部门编号和该部分员工邮箱中包含 a 字符的最高工资

select department_id,max(salary) as maxn
from employees 
where email like '%a%'
group by department_id ;

分组后筛选查询部门编号和该部门的员工人数 num，只要 num>5 的数据

select department_id, count(*) as num
from employees 
group by department_id
having num>5 // group 之后限制
order by num desc ;

分多组

select department_id,sex, count(*) as num
from employees 
group by department_id,sex
order by num desc ;

SQL高级查询

多表查询

查询在department_id在departments表中对应的name

select employee_id,first_name,d.department_name 
from employees as e , departments as d 
where e.department_id = d.department_id ;

行 106，若无where 则为 106 x 27行（笛卡尔积：列为相加，行为相乘）

查询80号部门的员工名字、部门名称、工种名称。

select
	e.first_name,
	d.department_name,
	j.job_title
from 
	employees as e,
	departments as d,
	jobs as j
where e.department_id = 80 
	and e.department_id = d.department_id 
	and e.job_id = j.job_id ;

查询所有部门的名称以及该部门的平均工资 average_salary （不要小数部分），并按平均工资降序排序

select 
	d.department_name,
	truncate(avg(e.salary),0) as  average_salary
from
	employees as e,
	departments as d
where e.department_id = d.department_id
group by e.department_id
order by average_salary desc ;

左外写法

select employee_id,first_name,d.department_name 
from employees as e , departments as d where e.department_id = d.department_id ;

连接

tb_a , tb_b

内连接 AB

select tb_a.*,tb_b.*
from tb_a
join tb_b
on tb_a.c3 = tb_b.c1 ;

**左外连接 ** A

select tb_a.*,tb_b.*
from tb_a
left join tb_b
on tb_a.c3 = tb_b.c1 ;

右外 B

select tb_a.*,tb_b.*
from tb_a
right join tb_b
on tb_a.c3 = tb_b.c1 ;

全外 A+B - AB

(select tb_a.*,tb_b.*
from tb_a
left join tb_b
on tb_a.c3 = tb_b.c1)
union
(select tb_a.*,tb_b.*
from tb_a
right join tb_b
on tb_a.c3 = tb_b.c1);

交叉 AXB

select tb_a.*,tb_b.* from tb_a cross join tb_b;

左外连接去掉内连接 A-AB

(select tb_a.*,tb_b.* 
from tb_a
left join tb_b
on tb_a.c3 = tb_b.c1)
except
(select tb_a.*,tb_b.*
from tb_a
join tb_b
on tb_a.c3 = tb_b.c1);

select tb_a.*,tb_b.* 
from tb_a
left join tb_b
on tb_a.c3 = tb_b.c1
where tb_b.c1 != null ;

A + B - AB

(select tb_a.*,tb_b.* 
from tb_a
left join tb_b
on tb_a.c3 = tb_b.c1
where tb_b.c1 != null)
union
(select tb_a.*,tb_b.* 
from tb_a
right join tb_b
on tb_a.c3 = tb_b.c1
where tb_a.c3 != null);

自然连接

select tb_b.*,tb_c.* 
from tb_b
natural join tb_c ;

自连接

select 
	e1.employee_id, 
	e1.first_name, 
	e2.first_name as manager_name
from employees as e1
join employees as e2
on e1.manager_id = e2.manager_id ;

非等值连接

子查询

select *
from (
	select * 
	from Student
	join SC
	on Student.Sno = SC.Sno ; 
) as R1
where R1.Sdept = 'CS';

查询工资比名为 Ellen 高的员工的名字和工资

select first_name,salary
from employees
where salary>(
	select salary
    from employees 
    where first_name = 'Ellen'
);

查询与编号为 141 的员工工种相同，比编号为 143 的员工月薪高的员工名字、工种编号和月薪

select first_name,job_id,salary
from employees
where job_id = (
    select job_id
    from employees
    where employee_id = 141
) && salary > (
	select salary
    from employees
    where employee_id = 143
);

查询工资最少的员工的名字、工种和月薪

select first_name ,job_id,salary
from employees
order by salary 
limit 1 ;

查询部门编号和该部门的最低月薪 min_salary，要求部门最低月薪大于 100 号部门的最低月薪

select department_id as dp_id,(
	select salary
    from employees
    where salary <= all(
    	select salary
        from employees
        where department_id = dp_id
    )
) as min_salary
from employees
group by department_id
having min_salary > (
	select min_salary
    from employees
    where department_id = 100
) ;

返回位置编号是 1400 和 1500 两个部门中的所有员工名字

select first_name
from employees
where department_id in (
	select department_id
    from departments
    where location_id in (1400,1500)
);

查询其它工种中比 it_prog 工种中任一工资低的员工的员工编号、名字、工种和月薪

select employee_id,first_name,salary
from employees
where job_id != 'IT_PROG' and salary < all (
	select salary
    from employees
    where job_id = 'IT_PROG'
) ;

查询比 it_prog 工种所有工资都高的部门的部门编号和最低月薪

select department_id,min(salary) as min_salary
from employees
where min_salary > (
	select max(salary)
    from employees
    where job_id = 'IT_PROG'
    group by job_id
)
group by department_id ;

查询员工编号最小并且工资最高的员工信息

SELECT *  
FROM employees  
WHERE employee_id = (  
    SELECT MIN(employee_id)  
    FROM employees  
    WHERE salary = (  
        SELECT MAX(salary)  
        FROM employees  
    )  
);

查询 employees 的部门编号和管理者编号在 departments 表中的员工名字，部门编号和管理者编号

select first_name,department_id,manager_id
from employees
where (department_id,employee_id) in (
	select department_id , manager_id
    from departments
);

exists

select exists(
	select * 
    from employees 
    where salary > 300000000
)as exist_salary ;

查询部门编号、工种编号和该部门和工种的员工的最低工资 min_salary，并按最低工资降序排序

select 
	department_id,
	job_id,
	min(salary) as min_salary
from employees
group by department_id , job_id
order by min_salary desc ;

查询工种编号和该工种有提成的员工的工资总和 sum_salary，平均工资 avg_salary,最高工资 max_salary，最低工资 min_salary,只要 max_salary>6000 的数据，对结果按 max_salary 升序排序

select 
	job_id, 
	sum(salary) as sum_salary, 
	truncate(avg(salary),0) as  average_salary,
	max(salary) as max_salary,
	min(salary) as min_salary
from employees
where commission_pct is not NULL
group by job_id
having max_salary > 6000
order by max_salary asc;

DDL

删除库

drop database northwind ;

查看字符集

show variables like 'character%' ;

创建库

create database library ;

查看当前库

select database() ;

查看创建操作

show create database library ; // 创建后使用

直接查看库下的表

show tables from set_demo ;

修改数据库

alter database library default character set utf8mb3 ; //将字符集改为utf8mb3

创建表

create table books(
	id int,
    name varchar(20),
    price double,
    author_id int,
    publish_date date
);

char(20)为固定20长度，varchar(20)为可变长度最大为20

create table emp1
as
select * from northwind.employees ;

复制表employees

删除表

drop table emp1;

修改表

alter table emp1 add column last_name varchar(20) ;
alter table emp1 drop column last_name varchar(20) ;
alter table emp1 add column last_name varchar(20) after first_name;

修改表名

rename table emp1 to emp ;

删除数据

delete from emp1;

create table dt_int(
	f1 tinyint,
    f2 tinyint unsigned
);

insert into dt_int values (1,2),(3,4);

约束

create database cs;
use cs;

create table tb_null(
	id int not null,
    name varchar(15) not null,
    email varchar(25),
    salary decimal(10,2)
);

insert into tb_null
values (1,'ikun','huaji@163.com','4000');

修改约束

alter table tb_null
modify column email varchar(25) not null ;

添加复合约束

alter table tb_uniq
add constraint unique(name, email);

非空约束

create table tb_pk (
	id int not null,
    name varchar(15) not null,
    email varchar(25),
    salary decimal(10,2)
);

unique 约束 (不重复)

create table tb_uniq (
  id int not null,
  name varchar(15) not null,
  email varchar(25) unique,
  salary decimal(10,2)
);

删除 unique 约束

alter table tb_uniq
drop index email;

主键约束（非空唯一）

create table tb_pk (
  id int primary key,
  name varchar(15),
  email varchar(25),
  salary decimal(10,2)
);

外键

create table dept (
    name varchar(20),
    id int primary key
);

create table emp (
	id int primary key,
    name varchar(20),
    dept_id int,
    foreign key(dept_id) references dept(id)
);

dml

create database dml;

use dml;

create table emp(
	id int,
    name varchar(15),
    hire_date date,
    salary double(10,2)
);

--数据插入

insert into emp
values(1,'ikun','2024-04-03',4000);

insert into emp(id,name)
values(2,'huaji');

--复制数据

insert into emp
select employee_id,first_name,hire_date,salary
from northwind.employees ;

--更改
update emp set salary = 6000 ; --把第一个人salary改变

update emp set salary = 6000   --指定更改
where id = 2 ;

--删除
delete from emp where id = 101; --删除 id=101 数据

View

create database vw ;
use vw ;

create table emps
as
select * from northwind.employees;

create table dps
as
select * from northwind.departments;

--创建视图
create view emp1
as
select 
	department_id,
	truncate(avg(salary),0) as  average_salary
from emps
group by department_id;

select * from emp1 ;

create view emp2
as
select 
	department_id,
	salary
from emps;

select * from emp2 ;

--查看视图结构
desc emp1 ;

--查看属性信息
show table status from vw ;

--视图更新数据，原表中数据也更新
--但此处的 average_salary 不能改变原表数据

update emp2 set salary = 6000
where employee_id  = 100 ;

select employee_id,salary from emps limit 5;

--修改视图
alter view emp2
as
select 
	employee_id,
	department_id,
	salary
from emps;

--删除视图
drop view emp2 ;

存储过程/存储函数/触发器 (storaged procedure/function cover)

存储过程

create database pf;
use pf;

--创建存储过程
delimiter $
create procedure all_emp()
begin
	select * from northwind.employees;
end $
delimiter ;

--调用存储过程
call all_emp();

--传出最大salary用于赋值
delimiter $
create procedure max_sal(out sal double)
begin
	select max(salary) into sal
	from northwind.employees;
end $
delimiter ;

set @sal :=0;
call max_sal(@sal);
select @sal;

--查看存储过程
show create procedure all_emp\G

--查看存储过程和存储函数状态
show procedure status where Db = 'pf'\G

--查看存储过程和存储函数对象信息
select * 
from information_schema.routines
where routine_schema = 'pf'\G;

--修改
alter procedure p_name ……
或
--删除
drop procedure all_emp ;

存储函数

--创建函数
delimiter $
create function my_count()
returns int
deterministic
contains sql
reads sql data
begin
  return (
    select count(*) from northwind.employees
  );
end $
delimiter ;

--调用
select my_count();

触发器

create database tg;
use tg;
create table tb_tg(
    id int primary key auto_increment,
    note varchar(20)
);

create table tb_tg_log(
	id int primary key auto_increment,
    log varchar(60)
);

--创建 trigger
delimiter $
create trigger after_insert
after insert on tb_tg for each row
begin
	insert into tb_tg_log(log)
	values (concat('[',now(),']:',new.note));
end $
delimiter ;

--查看触发器
show triggers\G

--插入数据并触发
insert into tb_tg(note)
values ('hello');

--删除触发器
drop trigger after_insert ;