mysql

mysql在线学习：

https://www.shiyanlou.com/courses/9

查询前三名的学生

SELECT * FROM score where Grade in ( select Grade from (SELECT Grade from score GROUP BY Grade ORDER BY Grade desc LIMIT 3) as c);

1、写出创建上面这两张表的sql

CREATE TABLE students (
Id int(10) PRIMARY KEY NOT NULL UNIQUE auto_increment,
Name VARCHAR(20) NOT NULL,
Sex VARCHAR(4),
Age int(10),
Class VARCHAR(20) NOT NULL,
Addr VARCHAR(50)
);

CREATE TABLE score (
Id int(10) PRIMARY KEY NOT NULL UNIQUE auto_increment,
Stu_id int(10) NOT NULL,
C_name VARCHAR(20),
Grade int(10)
);

2、使用while循环和repeat循环写各两个存储过程，传入一个行数，控制插入多少条数据，往第一题创建的两个表中各插入500条数据，参考ppt上的存储过程

delimiter $$;
CREATE PROCEDURE myInsertByRepeat(myRow INT)
BEGIN
DECLARE myName VARCHAR(20);
DECLARE myId INT(10);
DECLARE i INT;
SET i =1;
REPEAT
set myName=CONCAT('学生',i);
insert into students(Name,Sex,Age,Class,Addr) VALUE(myName,'男','20','班级001','地址001');
select id from students where name=myName into myId;
insert into score(stu_id,c_name,grade) VALUE(myid,'语文','90');
set i=i+1;
UNTIL i>myRow
END REPEAT;
END
$$;
delimiter;

delimiter $$;
CREATE PROCEDURE myInsertByWhile(myRow INT)
BEGIN
DECLARE myName VARCHAR(20);
DECLARE myId INT(10);
DECLARE i INT;
SET i =1;
WHILE i<=myRow DO
set myName=CONCAT('学生',i);
insert into students(Name,Sex,Age,Class,Addr) VALUE(myName,'男','20','班级001','地址001');
select id from students where name=myName into myId;
insert into score(stu_id,c_name,grade) VALUE(myid,'语文','90');
set i=i+1;
END WHILE;
END
$$;
delimiter;

CALL myInsertByRepeat(3);

CALL myInsertByWhile(3);

TRUNCATE students;
TRUNCATE score;

3、写insert语句插入右边两个表的数据

insert into students(id,Name,Sex,Age,Class,Addr) VALUES
('801','刘海洋','男','21','乔巴','北京市海淀区'),
('802','周飞','男','18','乔巴','北京市昌平区'),
('803','味全','男','26','路飞','湖南省永州市'),
('804','孙洋','女','21','乔巴','辽宁省阜新市'),
('805','李佳','女','22','超人','福建省厦门市'),
('806','保总','女','30','乔巴','湖南省衡阳市'),
('1001','徐振永','男','21','索隆','辽宁省阜新市'),
('1002','李卫强','男','18','索隆','福建省厦门市'),
('1003','狄枫','男','26','蜘蛛侠','湖南省衡阳市'),
('1004','女屌丝','女','21','蜘蛛侠','北京市海淀区'),
('1005','郁燕','女','22','索隆','北京市昌平区'),
('1006','裴颖菲','女','30','索隆','辽宁省阜新市'),
('1007','戴小龙','男','50','索隆','福建省厦门市');

 

insert into score(Stu_id,C_name,Grade) VALUES
('801','计算机','98'),
('801','英语','80'),
('802','计算机','65'),
('802','中文','88'),
('803','中文','95'),
('804','计算机','70'),
('804','英语','92'),
('805','英语','94'),
('806','计算机','57'),
('806','英语','45'),
('1001','计算机','98'),
('1007','英语','80'),
('1002','计算机','65'),
('1002','中文','88'),
('1003','中文','95'),
('1004','计算机','70'),
('1004','英语','92'),
('1005','英语','94'),
('1006','计算机','57'),
('1006','英语','45');

4、查询students表的所有记录

SELECT * from students st

5、查询students表的第2条到4条记录

SELECT * from students st LIMIT 1,3

6、从students表查询所有学生的学号（id）、姓名（name）和班级（class）的信息

SELECT st.Id,st.`Name`,st.Class from students st 

7、从students表中查询乔巴和索隆的学生的信息

SELECT * from students st WHERE st.Class in('乔巴','索隆')

8、从students表中查询年龄18~25岁的学生信息

SELECT * from students st WHERE st.Age BETWEEN 18 AND 25；

9、从students表中查询每个班有多少人

SELECT st.Class,COUNT(*) FROM students st GROUP BY st.Class；

10、从score表中查询每个科目的最高分

SELECT sc.C_name,max(sc.grade) FROM score sc GROUP BY sc.C_name

11、查询女屌丝的考试科目（c_name）和考试成绩（grade）

SELECT st.`Name`,sc.C_name,sc.grade from students st INNER JOIN score sc on st.Id = sc.stu_id where st.`Name` ='女屌丝'；

12、用4种多表连接的方式查询所有学生的信息和考试信息（左连接、右连接、内连接、=号连接）

SELECT * from students st INNER JOIN score sc on st.Id = sc.stu_id;

SELECT * from students st LEFT JOIN score sc on st.Id = sc.stu_id;

SELECT * from students st RIGHT JOIN score sc on st.Id = sc.stu_id;

SELECT * FROM students st,score sc WHERE st.Id = sc.stu_id;

13、计算每个学生的总成绩

SELECT st.`Name`,sum(sc.grade) from students st INNER JOIN score sc ON st.Id = sc.stu_id GROUP BY st.`Name` ORDER BY sum(sc.grade) DESC；

14、计算每个考试科目的平均成绩

SELECT sc.C_name,AVG(sc.grade) from score sc GROUP BY sc.C_name；

15、查询计算机成绩低于95的学生信息

select * from students a INNER JOIN score b
on a.Id = b.Stu_id
where b.C_name = '计算机' and b.Grade <95;

16、查询同时参加计算机和英语考试的学生的信息

SELECT *from students a INNER JOIN score b
on a.Id = b.Stu_id
WHERE b.C_name = '计算机' and b.Stu_id in(
SELECT Stu_id from score where C_name = '英语'
)

另一种写法

SELECT * from students a,score b
where a.Id = b.Stu_id and b.C_name = '计算机' and b.Stu_id in(
SELECT Stu_id from score where C_name = '英语'
)

17、将计算机考试成绩按从高到低进行排序

SELECT * from score
where C_name = '计算机'
ORDER BY Grade desc;

18、从student表和score表中查询出学生的学号，然后合并查询结果

SELECT st.Id from students st UNION SELECT sc.stu_id from score sc；

19、查询索隆班姓李的男同学的成绩和学生信息

SELECT b.Grade,b.C_name,a.* from students a INNER JOIN score b
on a.Id = b.Stu_id
WHERE a.Class = '索隆' AND a.Sex = '男' and a.Name LIKE '李%';

20、查询都是湖南的学生的姓名、年龄、班级和考试科目及成绩

SELECT a.Name,a.Age,a.Class,b.C_name,b.Grade from students a INNER JOIN score b
on a.Id = b.Stu_id
WHERE a.Addr like '湖南%';

21、把总成绩小于100的学生名称修改为天才

SELECT a.Name,sum(b.Grade) from students a INNER JOIN score b
on a.Id = b.Stu_id
GROUP BY a.Name
HAVING sum(b.Grade)<100;

 

CREATE VIEW students_view (class,c_name,grade,stu_id,name,sex) as
SELECT st.Class,sc.C_name,sc.grade,sc.stu_id,st.`Name`,st.Sex FROM students st INNER JOIN score sc ON st.Id = sc.stu_id;

SELECT * FROM students_view;

 

update students set name='天才' where id in (select stu_id from score group by stu_id having sum(grade)<100)

22、查询只学过一门课的学生信息

SELECT *from students a INNER JOIN score b
on a.Id = b.Stu_id
GROUP BY a.`Name`
HAVING count(b.C_name) = 1;

23、查出有多少个年龄一样的学生

SELECT count(a.`Name`),a.Age from students a
where a.Age in(
SELECT a.Age from students a
GROUP BY a.Age
HAVING count(*) > 1
)
GROUP BY a.Age;

思路：

1- 先查询有多少个年龄相同的数据
SELECT st.Age FROM students st GROUP BY st.Age HAVING count(*) > 1

2- 从学生表中找出年龄相同的所有人的名字和年龄
select st.name,st.Age from students st where st.Age in (SELECT st.Age FROM students st GROUP BY st.Age HAVING count(*) > 1)

3- 根据年龄分组，并计算每组人数 Count
select COUNT(st.name),st.Age from students st where st.Age in (SELECT st.Age FROM students st GROUP BY st.Age HAVING count(*) > 1) GROUP BY st.Age

24、查询出每门课程低于平均成绩的学生姓名、课程名称、分数

SELECT a.`Name`,b.C_name,b.Grade,d.myAvg from students a,score b,
(SELECT C_name Cname,avg(Grade) myAvg FROM score
GROUP BY C_name) d
where a.Id = b.Stu_id and b.C_name = d.Cname and b.Grade < d.myAvg;

 

思路：

1- 先查询出每门课程的平均成绩
SELECT sc.C_name,avg(sc.grade) FROM score sc GROUP BY sc.C_name

2- 把学生表、成绩表、平均分表生成一张大表
select * from students st,score sc,(SELECT sc.C_name,avg(sc.grade) FROM score sc GROUP BY sc.C_name) d WHERE st.Id = sc.stu_id

3- 在大表中，添加筛选条件 sc.C_name = d.Cname ，查询出课程名和平均成绩的课程名一致的数据
select * from students st,score sc,(SELECT sc.C_name as Cname,avg(sc.grade) as myAvg FROM score sc GROUP BY sc.C_name) d WHERE st.Id = sc.stu_id and sc.C_name = d.Cname

4- 再添加筛选条件 sc.C_name < d.Cname
select * from students st,score sc,(SELECT sc.C_name as Cname,avg(sc.grade) as myAvg FROM score sc GROUP BY sc.C_name) d WHERE st.Id = sc.stu_id and sc.C_name = d.Cname and sc.grade < d.myAvg；

25、查询出每个人成绩最高的课程名称及分数

select a.id,Name,C_name,Grade from students a,Score b,
(select c.stu_id,max(grade) g from Score c group by c.stu_id) n
where n.stu_id= b.stu_id and b.grade=n.g and a.id=b.Stu_id;

 

SELECT a.Stu_id,a.C_name,a.Grade from score a,
(SELECT Stu_id,max(Grade) g FROM score
GROUP BY Stu_id) b
where a.Stu_id = b.Stu_id and a.Grade = b.g;

26、索引是什么，如何创建索引，为什么要使用索引？写自己的理解

27、创建一个视图，要求显示总成绩大于160的学生的班级、课程名称、分数、学号、学生姓名、学生性别

CREATE VIEW students_view (class,c_name,grade,stu_id,name,sex) as
SELECT st.Class,sc.C_name,sc.grade,sc.stu_id,st.`Name`,st.Sex FROM students st INNER JOIN score sc ON st.Id = sc.stu_id GROUP BY st.`Name` HAVING sum(sc.grade) > 160

SELECT * FROM students_view;

 

 

 

 

 

 

 

•显示所有数据库：show databases;
•选定默认数据库：use dbname;
•显示默认数据库中所有表：show tables;
•放弃正在输入的命令：\c
•显示命令清单：\h
•退出mysql程序：\q
•查看mysql服务器状态信息：\s

create table school(
school_id int(10) not null auto_increment primary key,
school_name varchar(20) default null,
phone int(10) unique
);

大哥表
CREATE TABLE `dage` (
`id` INT (11) NOT NULL auto_increment,
`name` VARCHAR (32) DEFAULT '',
PRIMARY KEY (`id`)
) ;
小弟表
CREATE TABLE `xiaodi` (
`id` INT (11) NOT NULL auto_increment,
`dage_id` INT (11) DEFAULT NULL,
`name` VARCHAR (32) DEFAULT '',
PRIMARY KEY (`id`),
KEY `dage_id` (`dage_id`),
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
);

set autocommit = 0;   #自动提交关闭

commit;      #提交   ，mysql默认自动提交

flush privileges；  #刷新权限

use mysql ；        select user,password,host from user;  #在mysql下的user表查看

多表查询:

select a.name 学生姓名,b.score 学生成绩 from students a left join score b on a.id=b.student_id;
select a.name 学生姓名,b.score 学生成绩 from students a right join score b on a.id=b.student_id;
select a.name 学生姓名,b.score 学生成绩 from students a INNER join score b on a.id=b.student_id;
select a.name 学生姓名,b.score 学生成绩 from students a,score b where a.id=b.student_id;

带in查询:

select * from users where id in (1 ,2);
select * from users where id not in (1 ,2);

带or查询：

select * from student where id=1 or id=2;
select * from student where id!=1 or id!=2;

and查询：

Select * from users where id >100 and sex = 2;
Select * from users where id >100 and sex = 2 and addr not null;

剃重：

select distinct phone from classes;

查询条数：

Select * from users limit 5;   #前5行
Select * from users limit 10,20;  #从第11行开始，20行内容

合并结果集：

select 字段列表1 from table1
union [all]
select 字段列表2 from table2

把子查询的结果作为一个表：

select b.name,b.sex,a.grade  from score a, (select id, name,sex from stu where name = 'yzf')  as b  where a.id=b.id;

数据库的备份：

mysqldump -uroot -p123456 > data.sql;  #数据库外

mysqldump -uuser -pPassword dbname table [option] > xx.sql;

mysqldump -ulepus -p123456 bugfree bf_bug_info -n -t > bug.sql； #备份数据库的那张表

mysqldump -ulepus -p123456 -A > /tmp/all.sql；  #导出数据库中的所有表

mysql -uroot -p123456 < data.sql;  #恢复数据库

备份数据库的脚本：

#!/bin/bash
file_name=`date +%Y%m%d%H%M%S`
db_path='/usr/local/db_back/'
mysqldump -ulepus -p123456 -A > ${db_path}${file_name}_all.sql
echo "backup ok"

------------------------ 传入文件中数据库名跟文件名

#!/bin/bash

da_name=$1      #变量赋值不能有空格

sql_name=$2

echo $db_name

echo $sql_name

./test.sh data data.sql

----------------------------------------------

索引：

给students 表的 phone加上唯一索引
–Create unqiue index st_phone on students(phone);
–给students表的name加上普通索引
–Create index st_name on students(name);
–给订单表中的订单状态和用户id加上组合索引
–Create index status_user on orders(status,user_id);

drop index complex_index on book;

视图：

create view bug_view (id,title,status) as select id,title,bug_status from bf_bug_info;

DROP VIEW IF EXISTS bug_view,bug_view_new;

插入多条数据：

delimiter $$;

create procedure test_pll(count int)

begin

declare name varchar(20);

declare phone int(20);

declare i int;

set i = 0;

while i<count do

set name=CONCAT('安大',i);

set phone=134343433+i;

insert into stu (stu_name,phone,salary) values (name,phone,i);

set i=i+1;

end while;

end

$$;

delimiter;

 

call test_pll(500);

select count(*) from stu;

#可能需要修改phone的唯一索引，插入数据phone的id重复了