SQL增删查改
-- 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
