SQL语句练习

本文不具有参考性和阅读意义，是最近笔试考到数据库大题都不会写，为了复习sql语句在参考书上撸的的一篇文章。

                  
查询student表中的Sname,Ssex,Class
Select  Sname,Ssex,Class from Student

查询教师所有不重复的单位
Select  distinct  Depart  from  teacher

查询Student表中所有的数据
Select * from Student

查询Score表中成绩60到80的所有记录
Select * from Score where degree between 60 and 80

查询Score表中成绩为85或86或88的记录
Select * from score where degree in (85,86,88)

查询student表中“95031”班或性别为“女”的同学记录
Select * from student where class=’95031’ or ssex=’女’

以class降序查询Student表的所有记录
Select * from Student order by class desc

以Cno升序，Degree降序查询Score表中的所有记录
Select * from score order by cno asc , degree desc

查询Score表中最高分的学生学号和课程号
Select * from score order by degree desc 
或 select sno , cno from score where degree = (select max(degree) from score)

创建表
Create table Student
( 
Sno  Char(3),
Sname  Char(8),
Ssex  Char(2),
Sbirthday  datetime,
Class  Char(5)
)

Create table Course
(
Cno Char(5),
Cname Varchar(10),
Tno Char(3)
)

Create table Score 
(
Sno Char(3),
Cno Char(5),
Degree Decimal (4,1)
)

Create table Teacher
(
Tno char(3),
Tname char(4),
Tsex char(2),
Tbirthday datetime,
Prof char(6),
Depart varchar(10)
)

插入数据
Insert into course (Cno,Cname,Tno)
Values
(‘3-105’,’计算机导论’，825)，
(‘3-245’,’操作系统’，804)，
（‘6-166’，‘数字电路’，856），
（‘9-888’，‘高等数学’，831）；


数据库和表基本操作

Create database itcast;

Show databases;

Drop  databases  itcast;

Create  table  tb_grade
(
Id  int(11),
Name  varchar(20),
Grade  float
);

Show tables;

Describe  tb_grade;

Alter  table  tb_grade  rename  to  grade;

Alter table grade change name username varchar(20);

Alter  table  grade modify id int(20);

Alter  table  grade  add  age  int(10);

Alter table grade drop age;

Alter  table  grade  modify  username  varchar(20) first;

Alter  table  grade  modify  id  int(20)  after  grade;

Create  table  example01
(
Id int primary key,
Name varchar(20),
Grade float
);

Create table example02
(
Stu_id int,
Course_id int,
Grade float,
Primary key(stu_id,course_id)
);

Create table example04
(
Id int primary key,
Name varchar(20) not null,
Grade float
);

Create table example05
(
Id int primary key,
Stu_id int unique,
Name varchar(20) not null
);

Create table example06
(
Id int primary key auto_increment,
Stu_id int unique,
Grade float default 0
);

Create table t1
(
Id int,
Name varchar(20),
Score float,
Index(id)
);

Explain select * from t1 where id=1

Create table t2
(
Id int not null,
Name varchar(20) not null,
Score float,
Unique index unique_id(id asc)
);

Create table t3
(
Id int not null,
Name varchar(20) not null,
Score float,
Fulltext index fulltext_name(name)
)engine=myisam;

Create table t4
(
Id int not null,
Name varchar(20) not null,
Score float,
Index single_nme(name(20))
);


Create table t5
(
Id int not null,
Name varchar(20) not null,
Score float,
Index multi(id,name(20))
);

Create index index_id on book(booked);

Create unique index uniqueidx on book(bookid);

Create index mulitidx on book(authors(20),info(20));

Alter table book add index index_id(bookid);

Alter table book add index multidx (authors(20),info(50));

Insert into student(id,name,grade)
Values(1,’zhangsan’,98.5);

Select * from student;

Insert into student 
Set id=5,name=’boya’,grade=99;

Insert into ssel
tudent values
(6,’lilei’,99),
(7,’hanmeimei’,100),
(8,’poly’,40.5);

Insert into student(id,name) values
(9,’liubei’),(10,’guanyu’),(11,’zhangfei’);

Update student set name=’caocao’,grade=50
Where id=1;

Update student 
Set grade=100
Where id<4;

Update student set grade=80;

Delete from student where id=1;
Delete from student where id>5;

Select id, grade, name, gender from student where id in (1,2,3);

Select id, name from student where id between 2 and 5;

Select id,name,grade,gender from student where gender is null;

Select distinct gender from student;

Select id,name from student where name like”s%”;

Select id, name from student where name like ‘w%g’;

Select id, name from student where name not like ‘%y%’;

Select * from student where name like ‘wu_ng’;

Select id,name,gender from student where id<5 and gender=’女’;

Select id,name,grade,gender  from student where id in(1,2,3,4) and name like ‘%ng’ and grade<80;

Select count(*) from student;

Select sum(grade) from student;

Select avg(grade) from student;

Select * from student order by grade;

Select * from student order by gender asc,grade desc;

Select * from student group by gender;

Select count(*) ,gender from student group by gender;

Select sum(grade),gender from student group by gender having sum(grade)<300;

Select sum(grade),gender from student where gender=’男’;

Select * from student limit 4;

Select * from student order by grade desc limit 4,4;

Select  concat (id,’_’,name,’_’,grade,’_’,gender) from student;

Select id,if(gender=’男’，1,0) from student;

Select * from student as s where s.gender=’女’;

Select name as stu_name, gender stu_gender from student;

Alter table student add constraint FK_ID foreign key(gid) references grad(id);

Alter table student drop foreign key FK_ID;

Select * from department cross join employee;

Select employee.name, department.dname from department join employee on department.did=employee.did;

Select department.did,department.dname,employee.name from department left join employee on department.did=employee.did;

Select * from department where did in (select did from employee where age=20);

Select * from department where did not in (select didi from employee where age=20);

Select * from department where did exist (select didi from employee where age>20);

Select * from department where did>any (select did from employee);

Select *from department where did>all (select did from employee);

Select * from department where did=(select did from employee where name=‘赵四’);

Rollback;

Declare cursor_student cursor for select s_name,s_gender from student;

Create view view_stu as select math, Chinese, math+Chinese from student;

Show table status like ‘stu_class’

备份单个或多个数据库 mysqldump;
备份所有数据库，最后加—all-databases
数据还原 backup或者source
修改root密码mysqladmin