数据库的联系

SHOW DATABASES;

USE systme_school_manager;

– 显示数据库中所有有的表
SHOW TABLES;

– 查询student_tb表中所有的数据信息
SELECT * FROM student_tb
– 添加学生信息
INSERT INTO student_tb values(‘stu004’,’小青’,’女’,23,’数学’,’r001’);

DESC course_tb

– 创建一张新表来练习
CREATE TABLE IF NOT EXISTS practise(
id int primary key AUTO_INCREMENT,
name varchar(100),
sex ENUM(‘男’,’女’)
);

DESC practise;

– 插入一个数据
INSERT practise(name) value(“小青”);
– 查询表中所有信息
SELECT * FROM practise;

INSERT practise(name,sex) value(‘sadddsfs’,’女’);
INSERT practise(name,sex) value(‘asdfsd’,’男’);
INSERT practise(name,sex) value(‘sdasda’,’女’);
INSERT practise(name,sex) value(‘sas’,’女’);

– 查询姓名以a结尾的数据
SELECT * FROM practise WHERE NAME like ‘%a’;
– 查询姓名以a开头的数据
SELECT * FROM practise WHERE NAME like ‘a%’;

DELETE FROM practise;
– 插入一系列数据
INSERT practise(id,name,sex) value(121,’sasa’,’女’);
INSERT practise(id,name,sex) value(100,’sasa’,’女’);
INSERT practise(id,name,sex) value(181,’sasa’,’女’);

– 删除id是7的数据
DELETE FROM practise where id = 7;
– 删除id大于10，小于101的数据
DELETE FROM practise where id > 10 and id < 101;
– 删除sex为空的数据
DELETE FROM practise where sex is null;
– 删除sex不为空的数据
DELETE FROM practise where sex is NOT NULL;
– 查询表格内容
SELECT * FROM practise;

– 修改语句练习
UPDATE practise SET name = ‘哈哈’ where name like ‘%a’;
UPDATE practise SET name = ‘你好’ where name = ‘哈哈’;
UPDATE practise SET name = ‘欧阳锋’ where name like ‘%d%’;
UPDATE practise SET name = ‘黄老邪’ where name IS NOT NULL;
UPDATE practise SET name = ‘南帝’ where id > 100 and id < 120 ;
UPDATE practise SET name = ‘北丐’ where id = 121 ;

– 显示表的信息
show tables;

select * from student_tb;

– 创建一个索引
create index stu_name_index on student_tb(student_name);
– 查看表中索引信息
show index from student_tb;
show keys from student_tb;

– 删除索引
drop index stu_name_index on student_tb;

select student_id,student_name,student_sex,student_age,room_id
from student_tb where student_id = ‘stu006’;

– 创建一个视图
create view stu_id6_view
as select student_id,student_name,student_sex,student_age,room_id
from student_tb where student_id = ‘stu006’;
– 描述视图结构
desc stu_id6_view;
describe stu_id6_view;

– 查看有视图信息
show table status where comment = ‘view’;

select * from information_schema.tables
where table_schema = ‘systme_school_manager’
and table_type = ‘view’;

– 删除视图
drop view stu_id6_view;

---

– 回顾
show databases;
show tables;
drop table haha_tb;
drop table stuhh_tb;

select * from teacher_tb;
select * from student_tb;
– 升序排列 order by
select student_id id,student_age age from student_tb order by student_age asc;

– 统计
select count(1) from student_tb;
– 计算学生年纪的总和
select sum(student_age) from student_tb;
– 分组查询
select student_id,student_sex,count(1) as cnt from student_tb group by student_sex;

select student_id,student_sex,student_age,count(0) cnt from
student_tb group by student_age
having (student_age > 24 and student_age < 40)
order by student_age asc;

select concat(student_id,’‘,student_name,’‘,student_sex) as umion from student_tb;
– 联合查询 注意查询字段必须对应
– union 代表查询去重，排序
– union all 代表不去重，不排序
select s.student_id,s.student_name from student_tb s,room_tb r
where s.room_id = r.room_id
union all
select s1.student_id,s1.student_name from student_tb s1;

– 内连接
select s.student_id,s.student_name,b.room_id
from student_tb s inner join room_tb b
on s.room_id = b.room_id;

– 左联接
select s.student_id,s.student_name,b.room_id
from student_tb s left join room_tb b
on s.room_id = b.room_id;

select student_id,student_name,student_sex,student_age student_age from student_tb
where(student_age < 20 or student_age > 35);

select student_id,student_name,student_sex student_age from student_tb
group by student_age
having(student_age < 20 or student_age > 35);

---

show tables;
select * from teacher_salary;
select * from teacher_tb;

– 查询老师的工资（笛卡尔积）
select t.,ts. from teacher_tb t,teacher_salary ts
where t.salary_id = ts.salary_id;
– 左联接老师的工资
select t.,ts. from teacher_tb t
left join teacher_salary ts on t.salary_id = ts.salary_id;

– 创建视图（尽量用精确查询，不然会出问题）
create view teacher_salary_view
as (select t.teacher_id,t.teacher_name,t.teacher_sex,t.teaccher_leval,t.teacher_major,t.salary_id,
ts.salary_sub,ts.salary_add from teacher_tb t
left join teacher_salary ts on t.salary_id = ts.salary_id);

desc teacher_salary_view;
– 查看试图结构
show create view teacher_salary_view;

select * from student_tb;

create view stu_view_age
as select * from student_tb where student_age > 23;

– 查询数据库的记录数据
select * from teacher_salary_view;

alter table teacher_salary change salary_add1 salary_add float(4,1);

drop view teacher_salary_view;

– 创建索引
create index hahah_idx
on student_tb(student_age);

– 查看索引
show index from student_tb;

– 删除索引
drop index hahah_idx on student_tb;

show tables;
select * from room_tb;
select * from student_tb;

select sc.,s.,r.,c. from
(student_tb s left join room_tb r on s.room_id = r.room_id
)left join course_tb c on sc.course_id = c.course_id and sc.student_id = s.student_id;