数据库语句

#创建数据库
create database teacher;
create database teacher2 character set utf8;
#查看数据库
show databases;
#选择（使用）数据库
use teacher2;
drop database teacher;

#插入一条数据
insert into info(name,sex,age,brithday,salary) values('张三','男',20,'2010-5-9','5800.25')
#查询所有数据
select * from info;
#删除编号为3的人
delete from info where id=3;
#更新编号为2的人的性别为女
update info set sex='女' where id=2;

#primary key:主键  auto_increment:自增长，从1开始，每次增加1

create table teacher(
id int(10) primary key auto_increment,
name varchar(20) not null,
sex char(1) default '男',
password varchar(10) not null,
card varchar(18) not null,
unique key(card)
)charset=utf8;

#插入一条数据
insert into teacher(name,password) values('冯文龙','123','79889616');
#插入多条数据
insert into xinxi(name,password) values('曲真浩','963'),
('李江涛','456'),('袁华彬','789');

#修改李江涛密码值为666
update teacher set password='666' where name='李江涛';
#修改李江涛密码值为666，身份账号为11111111111
update teacher set password='666',card='11111111111' where name='李江涛';

#删除李江涛
delete from teacher where name='李江涛';
#删除全部数据
delete from teacher;
truncate table teacher;
#delete和truncate的区别
#1、truncate删除全部数据后会重置自增列，delete不会
#2、truncate执行速度比delete快

#查询全部数据
select*from teacher;


#删除表
drop table teacher;
#查看表
show tables;
#查看表定义（结构）
desc teacher;

#重命名表名
alter table info rename xinxi;
#为xinxi表添加年龄字段
alter table xinxi add age int(3);
#更改年龄字段为性别
alter table xinxi change age sex char(1) not null;
#删除性别字段
alter table xinxi drop sex;

#为表添加一个主键
alter table xinxi add constraint pk_xinxi primary key xinxi(id);
#为score和xinxi表建立外键关系
alter table score add constraint fk_score_xinxi foreign key(xid) references xinxi(id)

#查询id和name
select id,name from xinxi;
#查询id和name，把name和id变成中文
select id as 编号,name 姓名 from xinxi;
#查询编号为1的人的id和name
select id,name from xinxi where id=1;
#查询李四的id和name
select id,name from xinxi where name='李四';

  []:包含 ^:去除（不是） %:任意长度的任意字符  _:任意一个字符
#查询姓李的人的id和name
select id,name from xinxi where name like'李%'
#查询含有三的人的id和name
select id,name from xinxi where name like '%三%' 
#查询姓张、姓李的id和name
select id,name from xinxi where name like'张%' or name like '李%'

#查询密码为空的id和name
select id,name from xinxi where password is null;
#查询密码不为空的id和name
select id,name from xinxi where password is not null;

#查询编号在1--4之间的id和name
select id,name from xinxi where id between 1 and 4;
select id,name from xinxi where id>=1 and id<=4;

#查询去掉重复的人的name
select distinct name from xinxi;

#按id升序查询id和name
#order by:以某个字段排序
select id,name from xinxi order by id;
select id,name from xinxi order by id asc;

#按id降序查询id和name
select id,name from xinxi order by id desc;
#按name降序查询id和name（汉字笔画）
select id,name from xinxi order by name desc;
#按拼音
select id,name from xinxi order by convert(name using gbk )desc;

#按name降序，id降序查询idhe1name（汉字拼音首字母）
#多列拍寻：当第一个排序值相同的时候再按第二个排序
select id,name from xinxi order by convert(name using gbk) desc,id desc;

#查询前三条id和name
select id,name from xinxi limit 0,3;
select id,name from xinxi limit 3;

#查询2--5条id和name
select id,name from xinxi limit 1,4;
#查询1--4条id和name
select id,name from xinxi limit 0,4;

#查询学生的id，name和grade（内连接:只查询有成绩的）
#xinxi主表  score从表（辅表）
select x.id,x.name,s.grade from xinxi x inner join score s on x.id=s.xid;
select x.id,x.name,s.grade from score s inner join xinxi x on x.id=s.xid;
#笛卡尔积
select x.id,x.name,s.grade from xinxi x,score s  where x.id=s.xid;


#查询学生的id，name和grade （左外连接：能查出空值，必须先查主表）
select x.id,x.name,s.grade from xinxi x left join score on x.id=s.xid;
#查询学生的id，name和grade (右外连接)
select x.id,x.name,s.grade from xinxi x right join score on x.id=s.xid;

#truncate table score;

#查询李江涛的英语分数
select x.name,c.name,s.grade from xinxi x 
inner join score s on x.id=s.xid
inner join course c on c.id=s.cid where x.name='李江涛';

#查询平均分
select avg(grade) from score;
#查询总分
select sum(grade) from score;
#统计总共人数
select count(*) from xinxi;
select count(id) from xinxi;
select count(1) from xinxi;
#作业：三个统计的区别
#查询最高分和最低分
select max(grade),min(grade) from score;

#分组查询课程编号和平均分（聚合函数必须跟分组一起查询）
select cid,avg(grade) from score group by cid;
#多列分组（当第一个分组相同时会按第二列进行分组）
select cid,xid,avg(grade) from score group by cid,xid;

#查询平均分大于80的人学号和平均分(having只能在分组基础上继续查询)
select xid,avg(grade) from score group by xid having avg(grade)>80;

#参加考试的人次数的大于1
select xid,avg(grade) from score group by xid having count(*)>1;
#查询补考学生的学号和平均分（当查询重复时用多列分组）
select xid,avg(grade) from score group by xid,cid having count(*)>1;

#查询补考的学生姓名
select x.name,s.xid from xinxi x 
inner join score s on x.id=s.xid 
group by s.xid,s.cid having count(*)>1;

select id+2 from xinxi;
select id from xinxi where id>(1+1);

#运算符
#> >= <= <= = and or not
#[]: 包含 ^:去除  %：任意长度的任意字符 _:任意一个字符

#查询密码比李江涛小的人的姓名和密码
#1、先查询出李江涛的密码
select password from xinxi where nmae='李江涛';
#2、查询出所有人的姓名和密码
select name,password from xinxi;

select name,password from xinxi 
where password<(select password from xinxi where nmae='李江涛');

#查询分数小于60分的人的姓名
select x.name from xinxi x inner join score s on x.id=s.xid where s.grade<60;
#笛卡尔积
select x.name from xinxi x,score s where x.id=s.xid and s.grade<60;
#用子查询实现：查询分数小于60分的人的分数
#1、先查出分数小于60的人的编号
select xid from score where grade<60;
#2、查询所有人的姓名
select name from xinxi;
select name from xinxi where id=(select xid from score where grade<60);
#结论：子查询有时侯可以替换表链接

#in: 在……里面
#查询分数小于60分的人的姓名（结果大于1）
select name from xinxi where id in(select xid from score where grade<60);
#查询分数不小于60分的人的姓名（结果大于1）
select name from xinxi where id not in(select xid from score where grade<60);

#查询分数小于60分的人的姓名和成绩
select x.name,s,grade from xixni x inner join socre s on x.id=s.xid where s.grade<60;

#查询是否有李江涛这个人，如果有，显示查询结果，没有不显示
select id,name from xinxi where exists(select name from xinxi where name='李江涛');

#如果平均分大于70，
select xid from score group by xid having avg(grade)>70;
#就查询前三名成绩
select grade from score order by grade desc limit 3;
#如果平均分大于70，就查询前三名成绩
select grade from score 
where exists
(select xid from score group by xid having avg(grade)>70) 
order by grade desc limit 3;

#求最高分
select max(grade) from score;
#不用max求最高分
select grade from score order by grade desc limit 1;