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