数据库增删查改

--  sql 结构化查询语言  操作数据库的

-- 注释  --空格

-- 创建数据库
create database itquanmingxing;

-- 创建数据库的时候设置编码
create database itquanmingxing2  CHARACTER set utf8;

-- 删除数据库 
drop database itquanmingxing;

-- 删除数据库前 先判断数据库是否存在
drop database if exists  itquanmingxing;

-- 使用库
use itquanmingxing2;

-- 创建表
create table teacher
(
    tid int primary key ,
    tname varchar(2),
    professional_title varchar(20)
);

-- 打印表结构
desc teacher;

-- 查询表数据 
select * from teacher;

-- 删除表
drop table teacher;

drop table if exists teacher;

create table teacher
(
    tid int primary key  auto_increment, -- 自增 默认从1开始 每次自增1
    tname varchar(2),
    professional_title varchar(20)
)ENGINE=INNODB default charset=utf8 auto_increment=100; -- 自增 从100开始

select * from teacher;

-- 修改表

-- 添加列
alter table teacher    -- 修改 表  表名 
add salary float    -- 添加  列名  列的属性

-- 删除列
alter table teacher
drop column salary      -- 删除  列  列名

-- 修改列
alter table teacher
modify column tname varchar(20)  -- 修改 列  列名  列的属性

desc teacher;

-- 给表添加约束
create table student
(
   sid int primary key auto_increment,  -- 主键约束
   sname varchar(20) not null,  -- 非空约束
   sex char(2) default  '男',  -- 默认约束
   card_id char(18) unique,  -- 唯一约束
   age int, -- check(age>=18 and age<=28)
   tid int, 

   foreign key(tid) REFERENCES teacher(tid)     -- 外键约束  必须是其它表中的主键
)ENGINE=INNODB;

select * from teacher;

select * from student;

-- 实体完整性： 主键约束 唯一约束
-- 域完整性 ： not null  default 
-- 引用完整性: 外键

-- insert 添加数据

select * from teacher;

-- 添加表中的所有列
insert into teacher values(null,'daimenglaoshi','高级');

-- 添加部分列
insert into teacher(tname) values('wanglaoshi');

insert into teacher values(null,'zhanglaoshi','高级');

insert into student values(null,'lisi',default,556777,null,101);

insert into student values(null,'wangwu',default,888888,null,102);

insert into student values
(null,'zhaoliu',default,99999,null,101),
(null,'王二',default,77777,null,102);

-- update  更新
-- 语法: update  表名 set 新的值 where 条件

-- 将王五的性别改为女
update student set sex='女' where sname='wangwu'

-- 将zhaoliu的性别改为女 将年龄改为18
update student set sex='女',age=18 where sname='zhaoliu' 

-- 将所有年龄为null的学生的年龄改为28
update student set age=28 where age is null;

select * from student;

-- 将所有的年龄都清空
update student set age = null;

-- 将学号在2--5范围内的学生的年龄改为18
update student set age=18 where sid>=2 and sid<=5

-- delete  删除

-- delete from 表名  where 条件

-- 删除表中所有的数据
delete from student;

delete from student where  sid=7

delete from student where age is null

delete from student where age>28 or tid=102

-- insert  update  delete  dml语言:数据操纵语言  
-- 操纵的是数据 跟结构无关 不会影响到结构的

-- 查询
-- select 
-- 简单查询

use itquanmingxing2;

-- 1.查询所有列(查询整张表的数据)
-- * 所有的列
select * from student;

-- 2.查询部分列
select sname,age from student;

-- 3.查询的时候 给字段取别名
select sname as '姓名',age as '年龄' from student;
-- as可以省略
select sname  '姓名',age  '年龄' from student;

-- 

update student set age=19 where sid=2;
update student set age=29 where sid=5;
update student set age=27 where sid=6;

-- 4.查询的时候排序
select * from student order by age -- asc 默认是升序

select * from student order by age desc -- 降序

-- 排序可以针对多个字段
select * from student order by tid ,age desc 

-- 5. 读取部分记录  limit 偏移量 ，记录数
-- 查询年龄最大的前三个学生
select * from student order by age desc limit 0,3

select * from student order by age desc limit 1,3

-- 分页查询
select * from student limit 0,2

select * from student limit 2,2

-- 6. 去掉重复行
select distinct tid from student 

-- 7. 带条件的查询

-- 1) 关系运算符 > < >= <= = !=
select * from student where sex='男'

-- 2) and  or  
select * from student where sex='男' and age>18; 
select * from student where sex='男' && age>18; 

-- 查询 tid=101号老师的学生 或者年龄>25
select * from student where tid=101 or age>25
select * from student where tid=101 || age>25

select * from student where age>=18 and age<=28
-- 3) between and   在18到28之间
select * from student where age between 18 and 28

--  4) in  not in  在 某个范围内的  
select * from student where sid in (2,3,6);
-- 等价于
select * from student where sid=2 or sid=3 or sid=6

select * from student where sid not in (2,3,6);

update student set tid=null where sid=2

select * from student 

-- 5) is null  is not null查询出tid为null的学生
select * from student where tid is null

select * from student where tid is not null

-- 6) like  not like  查询 模糊查找

insert into student values(null,'张三',default,8767,null,101);

insert into student values(null,'张四',default,8989,null,102);

insert into student values(null,'小张三',default,89898,null,102);

select * from student

--  查询姓张的所有学生
-- % 通配符  匹配任意字符任意多个 包括0个
select * from student where sname like '张%'
-- 查询名字中带张的
select * from student where sname like '%张%'

-- 查询两个字的名字 第二个字为三的
-- _通配符 匹配一个任意字符  
select * from student where sname like '_三'

select * from student where sname not  like '_三'

-- 查询第二个字为张的
select * from student where sname like '_张%'

-- 查询最后一个字是三或者四的
select * from student where sname like '%三' or sname like  '%四'

-- 聚合查询(集函数)

-- 1) count() 返回总记录数
select count(*) from student

-- 查询当前字段的总个数 (null 不计算)
select count(tid) from student

-- 2) max() 最大值
select max(age) from student

-- 3) min()最小值
select min(age) from student

-- 4) sum() 求和
select sum(age) from student

-- 5) avg() 求平均值
select avg(age) from student

-- 请帮我查询年龄最大的学生的信息

-- 普通字段不能和聚合函数放在一起查询  除非 聚合函数和 group by 的字段放一起
-- 或者聚合函数放在子查询中
--  select sname,max(age) from student  -- 错误的
select sname,age from student where age=
(select max(age) from student)

-- 查询男生和女生中年龄最大的学生
update student set age=23,sex='女' where sid=7

select sex,max(age) from student group by sex

select * from student

-- 对分组后的结果进行删选  用having 
select tid,count(*) from student group by tid  having  count(*)>2

-- 高级查询  多表联查
use itquanmingxing2;

select * from student;

select * from teacher;

-- 联接查询
-- 查询所有选择了教师的学生信息以及对应的老师信息
-- 
-- 1)内联接  两张表都有的记录才查询出来 
-- 简化写法
select * from student,teacher  where  student.tid=teacher.tid
-- 标准写法
select * from student inner join teacher on student.tid=teacher.tid

-- 联接成一张表后  可以在此基础上做查询 
select sname,tname from student inner join teacher on student.tid=teacher.tid
where sid=8 

-- 也可以直接用and
select sname,tname from student inner join teacher on student.tid=teacher.tid
and sid=8 

-- 查询所有的学生信息以及对应的老师信息
-- 2) 左外联接   左边这张表的数据全部查询出来 右边只显示与之能关联的数据
select * from student left outer  join teacher 
on student.tid=teacher.tid

select * from teacher

-- 3)右外  右边的表为主表  
select * from student right outer  join teacher 
on student.tid=teacher.tid

select * from teacher left outer  join  student
on student.tid=teacher.tid 

select * from teacher left outer  join  student
on student.tid=teacher.tid  where teacher.tid=101 and sex='男'

-- 嵌套子查询

-- 查询 lisi的教师名称

select tname from teacher where tid=
(select tid from student where sname='lisi')

-- 也可以用联接查询
select tname from student inner join teacher on student.tid=teacher.tid 
and sname='lisi'

select tname from teacher where tid in
(select distinct tid from student where sex='男')

-- all any 

-- 查询年龄>所有的女生的男生信息

select * from student where age>all
(select age from student where sex='女') and sex='男'
-- >all等价于 >max()  
select * from student where age>
(select max(age) from student where sex='女') and sex='男'

-- 查询年龄>任意一个女生的男生信息
select * from student where age > any
(select age from student where sex='女') and sex='男'

select * from student where age > 
(select min(age) from student where sex='女') and sex='男'

select * from student;
select * from teacher;

use itquanmingxing2;

select sname '姓名' from student

select * from student a inner join teacher b on a.tid=b.tid

update student set age=18 where sid=7

select * from 
(select * from student where sex='男') a,

(select * from student where sex='女') b where a.age=b.age