1
 drop table if exists student;
2
 create table student(
3
  -- 学号
4
  sno int auto_increment,
5
  -- 姓名
6
  sname varchar(16),
7
  birthday date,
8
  height fioat(3,2),
9
  tel char(11),
10
  classno int,
11
  constraint pk_student primary key(sno)
12
 )

1
 alter table student add constraint pk_student primary key(sno)
2
 alter table student change sno sno int auto_increment

1
 create table student(
2
  -- 学号
3
  sno int primary key,
4
  -- 姓名
5
  sname varchar(16),
6
  birthday date,
7
  height fioat(3,2),
8
  tel char(11)
9
 )

1
create table student(
2
  -- 学号
3
  sno int ,
4
  -- 姓名
5
  sname varchar(16),
6
  birthday date,
7
  height fioat(3,2),
8
  tel char(11),
9
  constraint pk_student primary key(sno)
10
)

1
 drop table if exists student;
2
 create table student(
3
  -- 学号
4
  sno int ,
5
  -- 姓名
6
  sname varchar(16),
7
  birthday date,
8
  height fioat(3,2),
9
  tel char(11),
10
  classno int,
11
  constraint pk_student primary key(sno),
12
  constraint fk_student_classno foreign key(classno)
13
  references classes(classno)
14
)
15
drop table if exists classes ;
16
create table classes(
17
  classno int,
18
  classname varchar(32),
19
  constraint pk_classes primary key(classno)
20
)

1
drop table if exists student;
2
create table student(
3
  -- 学号
4
  sno int ,
5
  -- 姓名
6
  sname varchar(16),
7
  birthday date,
8
  height fioat(3,2),
9
  tel char(11),
10
  classno int,
11
  constraint pk_student primary key(sno),
12
  constraint fk_student_classno foreign key(classno)
13
  references classes(classno),
14
  constraint unique_student_tel unique(tel)
15
)
16
drop table if exists classes ;
17
create table classes(
18
  classno int,
19
  classname varchar(32),
20
  constraint pk_classes primary key(classno)
21
)

1
sname varchar(16) not null,

1
 drop table if exists student;
2
 create table student(
3
  -- 学号
4
  sno int ,
5
  -- 姓名
6
  sname varchar(16) not null,
7
  birthday date,
8
  height fioat(3,2),
9
  tel char(11),
10
  classno int,
11
  constraint pk_student primary key(sno),
12
  constraint fk_student_classno foreign key(classno)
13
  references classes(classno),
14
  constraint unique_student_tel unique(tel),
15
  constraint check_student_birthday check(birthday < '2026-02-05')
16
 )

1
 -- 1页 每页5条
2
 select * from employee
3
 limit 0,5
4
 -- 2页  每页5条
5
select * from employee
6
limit 5,5
7
select * from employee
8
limit 10,5
9
-- 假设当前页数是pageNo,每页显示pageSize条记录
10
-- 分页的SQL语句：
11
select * from employee
12
limit (pageNo-1)*pageSize,pageSize

1
 select * from employee,departments
2
 where employee.department_id = departments.department_id
3
 select * from employee e,departments d
4
 where e.department_id = d.department_id

1
select first_name,e.department_id,department_name,city
2
from employee e,departments d,locations loc
3
where e.department_id = d.department_id
4
and d.location_id = loc.location_id
5
and city = '北京'

1
 select city,avg(salary)
2
 from employee e,departments d,locations loc
3
 where e.department_id = d.department_id
4
 and d.location_id = loc.location_id
5
 group by city

1
select * from employee e,job_grades j
2
where salary>=lowest_sal and salary<highest_sal

1
 select first_name,e.department_id,department_name from employee e
2
 inner join departments d on e.department_id = d.department_id
3
 where e.department_id =  5001

1
 select first_name,e.department_id,department_name from employee e
2
 inner join departments d on e.department_id = d.department_id
3
 and e.department_id =  5001

1
select first_name,e.department_id,department_name
2
from employee e
3
left outer join departments d on e.department_id = d.department_id

1
 -- 查询所有程序员的姓名、工作所在地
2
 select first_name,city
3
 from employee e
4
 left outer join departments d on e.department_id = d.department_id
5
 left outer join locations loc on d.location_id = loc.location_id
6
 where job_id = '程序员'

1
select e.first_name,m.first_name from employee e,employee m
2
where e.manager_id = m.employee_id

1
 select e.first_name,eloc.city,m.first_name,mloc.city
2
 from employee e
3
 left outer join employee m on e.manager_id = m.employee_id
4
 left outer join departments ed on e.department_id = ed.department_id
5
 left outer join locations eloc on ed.location_id = eloc.location_id
6
 left outer join departments md on m.department_id = md.department_id
7
 left outer join locations mloc on md.location_id = mloc.location_id

1
 update
2
 employee e
3
 left outer join departments d on e.department_id = d.department_id
4
 left outer join locations loc on d.location_id = loc.location_id
5
 set  salary = salary + 5
6
 where city = '北京'

1
select * from employee
2
where department_id in
3
(select department_id from departments where manager_id = 100)
4
select * from employee e
5
where
6
exists (select 0 from departments d where e.department_id = d.department_id and  manager_id = 100)

1
 -- 查询工资比5001部门员工高的员工
2
 select first_name,salary from employee
3
 where salary >
4
 any(select salary from employee where department_id = 5001)

1
-- 查询工资比5001部门员工高的员工
2
select first_name,salary from employee
3
where salary >
4
all(select salary from employee where department_id = 5001)

1
 select max(avgsal)
2
 from
3
 (Select avg(salary) avgsal from employee group by department_id) a

1
 select * from
2
 (select * from employee ) e
3
 left outer join (select * from departments) d on e.department_id = d.department_id

1
-- 查询一列数据包括员工姓名和所在地
2
(select first_name from employee)
3
union
4
(select city from locations)
5
-- 查询两遍员工姓名
6
(select first_name from employee)
7
union all
8
(select first_name from employee)

1
 (select first_name,salary from employee)
2
 union
3
 (select '总计：',sum(salary) from employee)

1
 create table score(
2
  sname varchar(16),
3
  shuxue int,
4
  yuwen int,
5
  yingyu int
6
 )
7
 create table score2(
8
  sname varchar(16),
9
  kemu varchar(32),
10
  chengji int
11
 )

1
 --  查询出所有的学生 按学生姓名 循环
2
 insert into score2
3
 (select sname,'shuxue',shuxue from score where sname = 'tom')
4
 union
5
 (select sname,'yuwen',yuwen from score where sname = 'tom')
6
 union
7
 (select sname,'yingyu',yingyu from score where sname = 'tom')

1
-- 当前连接执行的所有事务都取消自动提交
2
set autocommit = 0;
3
insert into departments(department_id,department_name)
4
values(5005,'行政部');
5
insert into employee(first_name,phone_number,department_id)
6
values('jackABC','133123467888888888888888888888',5005);
7
commit;

1
 set autocommit = 0;
2
 insert into departments(department_id,department_name)
3
 values(5008,'行政部');
4
 insert into employee(first_name,phone_number,department_id)
5
 values('jackABC','13312346788',5008);
6
 rollback;
7
 commit;

1
 -- 当前连接执行的所有事务都取消自动提交
2
 set autocommit = 0;
3
 savepoint a ;
4
 insert into departments(department_id,department_name)
5
 values(5019,'行政部2');
6
 savepoint b ;
7
 insert into employee(first_name,phone_number,department_id)
8
 values('jackABC','13312346788',5019);
9
 savepoint c ;
10
 rollback to c;
11
 commit;

1
 -- 创建存储过程（如果已存在则先删除）
2
 DROP PROCEDURE IF EXISTS `proc_get_user_info`;
3
 DELIMITER //  -- 临时修改语句结束符为//（避免存储过程内的;提前终止）
4
 CREATE PROCEDURE `proc_get_user_info`(
5
  IN p_user_id INT,          -- 输入参数：用户ID
6
  IN p_include_address BOOLEAN,  -- 输入参数：是否返回详细地址（true/false）
7
  OUT p_result_code INT      -- 输出参数：执行结果码（0成功，1失败）
8
 )
9
 BEGIN
10
  -- 声明局部变量
11
  DECLARE v_error INT DEFAULT 0;
12
  -- 捕获异常
13
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET v_error = 1;
14
  -- 初始化输出参数
15
  SET p_result_code = 0;
16
  -- 核心逻辑：查询用户信息
17
  IF p_include_address THEN
18
    -- 返回包含详细地址的完整信息
19
    SELECT id, username, age, address, create_time
20
    FROM `t_user`
21
    WHERE id = p_user_id;
22
  ELSE
23
    -- 仅返回基础信息（隐藏地址）
24
    SELECT id, username, age, create_time
25
    FROM `t_user`
26
    WHERE id = p_user_id;
27
  END IF;
28
  -- 异常判断：如果执行出错，设置结果码为1
29
  IF v_error = 1 THEN
30
    SET p_result_code = 1;
31
    SELECT '查询用户信息失败' AS error_msg;
32
  END IF;
33
 END //