Mysql 的sql语句

#创建数据库
create database a;
#查看数据库
show databases;
#删除数据库
drop database a;
#使用数据库
use wp1102;
#查看数据中所有的表
show tables;
#创建表
create table temp(
 id int primary key AUTO_INCREMENT,#主键 自增长
 name char(20) not null, #不能为空
 address varchar(40) default '武汉', #默认值
 score float UNSIGNED, #不能为负
 sno int unique #唯一性约束
)
#查看表的结构
desc temp;

#查询表内容
SELECT * from temp;
#插入语句
insert into temp values(null,'张三','荆州',89,1102);
insert into temp (name,address,score,sno) values ('李四','武汉',99,1103);
#修改数据
update temp set score=10 where id=2;
#删除学生表
drop table stu;

 

 

#创建学生表
create table stu(
 id int PRIMARY key AUTO_INCREMENT,
 name varchar(20) not null,
 sex char(8) DEFAULT '男',
 sno varchar(20) UNICODE,
 age int UNSIGNED
)
SELECT * from stu;
#插入数据
insert into stu values(null,'张三','男',1101,21);
insert into stu values(null,'李四','男',1102,23);
insert into stu values(null,'王五','男',1103,22);
insert into stu values(null,'小雨','女',1104,20);
insert into stu values(null,'小兰','女',1105,21);
insert into stu (name,sex,sno,age) values ('多多','女',1106,23);
#创建成绩表
create table score(
 id int PRIMARY key AUTO_INCREMENT,
 sno varchar(20) UNICODE,
 chinese float UNSIGNED,
 math float UNSIGNED,
 english float UNSIGNED
)
select * from score;
#插入数据
insert into score values(null,'1101',60,70,80);
insert into score values(null,'1102',40,20,90);
insert into score values(null,'1103',50,80,20);
insert into score values(null,'1104',80,20,99);
insert into score values(null,'1105',97,89,49);
insert into score values(null,'1106',67,74,74);

#查询语句
#查询stu表中所有学生的姓名
select name from stu;
#查询stu表中性别为男的学生
select * from stu where sex = '男';
#查询年龄小于22的学生
select * from stu where age<22;
#查询年龄在21到22之间的
select * from stu where age>=21 and age<=22;
select * from stu where age>22 and sex='男';
select * from stu where age=20 or age=21;
#between 用于检验一个值是否存在一个指定的范围内
select * from stu where age between 20 and 22;
#in 用于检验一个值是否存在一个指定的集合内
select * from stu where sno in('1102','1104','1105');
#排序
select * from stu order by age asc; #升序
select * from stu order by age desc; #降序

#聚合函数
#求学生表数学成绩的平均值
select avg(math) as '数学平均值' from score;
#求学生表中的语文成绩的最大值
select max(chinese) as '语文最高分' from score;
#求学生成绩表中的记录条数
select COUNT(*) from score;

#求学生成绩表中语文成绩的和
select sum(chinese) from score;

#查询学生的所有信息(名字、分数……)
SELECT * from stu ,score where stu.sno = score.sno;
#查询学生的所有信息(名字、性别、学号、分数……)
select
 s1.name ,s1.sex ,s1.sno ,s2.math ,s2.chinese ,s2.english
from
 stu s1 , score s2
where
 s1.sno = s2.sno;
#查询语文成绩最低的学生的信息
select * from stu where sno =(
 select sno from score where chinese in(
  select min(chinese) from score)
);
select s1.*
from stu s1 , score s2
where s1.sno = s2.sno and s2.chinese = (
select min(chinese) from score);

#分组查询 分性别求年龄最大值
select * from stu where

#分别查询出两个班平均成绩最大或者最小的学生信息
#思路一
select * from student
where
 (class,(chinese+math+english)/3)
in  (
 select class,MIN((chinese+math+english)/3) from student group by class
    )
#思路二  根据找到符合条件的id 然后再找到所有符合条件id的所有信息
select * from student where id in(
 select id from student
 where (className,(chinese+math+english)/3)
 in(select className,MIN((chinese+math+english)/3) from student group by className)
)

select sno,avg(chinese) from score group by sno having avg(chinese)>30

#分页的公式算法
(allRecord + lineSize-1)/lineSize = pageSize

#对于book及type表的外键关联操作(级联)
drop table book;
CREATE table type(
 id int primary key,
 name varchar(50)
)
create table book(
 id int AUTO_INCREMENT PRIMARY key,
 name varchar(50) not null,
 price float,
 author varchar(50),
 publishDate Date,
 type_id int
 #foreign key(type_id) REFERENCES type(id)
)
desc book;
insert into type values(1,'言情');
insert into type values(2,'武侠');
insert into type values(3,'军事');
insert into type values(4,'南风窗');

INSERT INTO book VALUES(NULL,'老e',10,'老e','2011-03-02',1);
INSERT INTO book VALUES(NULL,'南方周末',10,'南方','2011-03-02',2);
INSERT INTO book VALUES(NULL,'水浒传',10,'番茄','2011-03-02',3);
INSERT INTO book VALUES(NULL,'钢铁是怎样炼成的',10,'老尧','2011-03-02',1);
INSERT INTO book VALUES(NULL,'神墓',10,'辰东','2011-03-02',2);
INSERT INTO book VALUES(NULL,'污点',10,'平平','2011-03-02',3);
INSERT INTO book VALUES(NULL,'战国',10,'嬴政','2011-03-02',2);
INSERT INTO book VALUES(NULL,'三国',10,'老罗','2011-03-02',4);
INSERT INTO book VALUES(NULL,'污点',10,'平平','2011-03-02',3);
INSERT INTO book VALUES(NULL,'战国',10,'嬴政','2011-03-02',2);
INSERT INTO book VALUES(NULL,'国防时报',10,'国防','2011-03-02',3);
select * from book
select * from type
select * from book where type_id = 1

select * from book where type_id = (select id from type where name = '言情')

#update 有外间相关联的时候 不能被修改或者删除
UPDATE type set id=8 where name='言情';
#delete
delete from type where name='言情';
#从表中外键必须设置为null，才能update或delete操作
update book set type_id=null where type=1

#使用级联增加外键
alter table book add foreign key (type_id) REFERENCES type(id) on update cascade on delete set cascade
#set null
alter table book add foreign key (type_id) REFERENCES type(id) on update set null on delete set null