mysql入门

第一部分： 数据库 SQL
1、数据库的创建
创建数据库 --- create database 数据库名称；
创建指定编码集，并带有校对规格的数据库---- create database 数据库名称 character set 编码集 collate 校对规格;
练习：
创建一个名称为mydb1的数据库---- create database mydb1;
创建一个使用utf8字符集的mydb2数据库。---- create database mydb2 character set utf8;
查看数据库使用的字符集 --- show create database mydb2;
创建一个使用utf8字符集，并带校对规则的mydb3数据库 ---- create database mydb3 character set utf8 collate utf8_unicode_ci;

2、查看和删除数据库
查看所有的数据库 ---- show databases;
查看当前数据库的编码集 --- show create database 数据库名称
删除数据库 --- drop database 数据库名称
练习：
查看当前数据库服务器中的所有数据库--- show databases;
查看前面创建的mydb2数据库的定义信息--- show create database mydb2;
删除前面创建的mydb1数据库---- drop database mydb1;


3、修改数据库的编码
alter database 数据库名称 character set 编码集;
练习
查看服务器中的数据库，并把其中某一个库的字符集修改为gbk; ----alter database mydb2 character set gbk;

4、切换数据库
要想对数据库中的数据表和数据表中的记录进行操作，必须先切换到指定的数据库 ---- use 数据库名称
查看当前正在使用的数据库 ---- select database();   或者  ----  \s 查看正在使用的数据库



第二部分：  数据库表  SQL 
1、创建数据表---- create table 表名(列名 列类型(长度) 约束,列名 列类型(长度) 约束,....) character set 字符集 collate 校对规格;
注意：若果没有设定字符集，数据表将采用数据库默认的字符集；
 在创建表时，要先切换指定的数据库 ---use 数据库名称


练习：
创建user表
create table user(
id int,                                    
name varchar(40),
password varchar(40), 
birthday date
);

创建一个员工表employee ---- 查看表结构: desc 表名;
id 整形
name 字符型
gender 字符型
brithday 日期型
entry_date 日期型
job 字符型
salary 小数型
resume 大文本型
create table employee(
id int,
name varchar(40),
gender varchar(10),
birthday date,
entry_date date,
job varchar(40),
salary double,
resume longtext
);


2、定义单表字段约束
1)主键约束(唯一标识一条记录) ------ primary key : 不允许为空，不允许重复 
删除主键---- alter table 表名 drop primary key;
主键自动增长 ----- auto_increment
2)唯一约束(该列字段内容不允许重复) ---- unique
3）非空约束(值不能为空)  ----- not null


create table employee(
id int primary key not null auto_increment,
name varchar(40) unique not null,
gender varchar(10) not null,
birthday date not null,
entry_date date not null,
job varchar(40) not null,
salary double not null,
resume longtext not null
);


3、数据库表的修改
1)修改表的名称 --- rename table 旧表名 to 新表名;
2)向已有的数据表添加一列 ---- alter table 表名 add 列名 列类型(长度) 约束;
3)改变已有的数据表中一列的类型、长度---- alter table 表名 modify 列名 列类型(长度) 约束;
4)改变已有的数据表中一列的名称 ---- alter table 表名 change 旧列名 新列名 列类型(长度) 约束;
5)删除已有的一列 ---- alter table 表名 drop 列名;
6)修改表的字符集---- alter table 表名 character set 字符集;


练习
在上面员工表的基础上增加一个image列----alter table employee add image varchar(40);
修改job列，使其长度为60 ---- alter table employee modify job varchar(60) not null;
删除gender列---- alter table employee drop gender;
表名改为user ---- rename table employee to user;
修改表的字符集为gbk----alter table user character set gbk;
查看当前表的字符集--- show create table user;
列名name修改为username---- alter table user change name username varchar(40) unique not null;


4、数据库中表的删除
drop table 表名;
查看当前使用数据库中的所有的表 ---- show tables;

第三部分: 对数据表中的数据记录进行 增删改查
1、表记录的插入---- insert into 表名(列名,列名,...) values(值,值,...);
注意: 值得个数应该与列的个数相同; 值得类型应该与列字段的类型相同; 值中列出的数据位置应该与列名出现的位置相同；
 字符和日期型数据应该在单引号中


步骤：
1)启动命令行(cmd)窗口
2)mysql -u root -p 回车 输入密码
3)创建数据库 ---- create database day11;
*查看当前数据库服务器中所有的数据库---- show databases;
*查看指定数据库的字符集---- show create database day11;
4)创建数据表,先要切换数据库---- use day11;
*查看当前正在使用的数据库 ---- select database();
5)创建数据库表
id 整形
name 字符串型
gender 字符串型
birthday 日期型
salary 浮点型
entry_date 日期型
resume 大文本型

create table employee(
id int primary key not null auto_increment,
name varchar(40) unique not null,
gender varchar(10) not null,
birthday date not null,
salary double not null,
entry_date date not null,
resume longtext not null
);


*查看表的结构---desc employee;
6)插入数据
注意： 插入数据时，字符和日期型数据时要加单引号; 
在插入数据时，如果有些列有默认值或者可以为空时，这些列就可以省略不写;
在插入数据时，如果所有的列名省略不写，那么所有列的值都必须要写，且要按照列的顺序
insert into employee(id,name,gender,birthday,salary,entry_date,resume) values(null,'zs','male','1988-06-17',3000,'2013-01-12','He is a good man!!');
insert into employee(id,name,gender,birthday,salary,entry_date,resume) values(null,'li','male','1990-03-27',2500,'2012-01-03','He is a boss!!');
insert into employee(id,name,gender,birthday,salary,entry_date,resume) values(null,'ww','male','1993-07-17',4000,'2000-12-12','He is a employee!!');
*查询表中插入的数据时，可以使用---- select * from 表名;

*****在插入中文数据时，中文乱码问题？？？？
insert into employee values(null,'小丽','female','1995-12-12',1000,'2013-04-27','她是一个漂亮的女孩');
查看数据库相关的编码集----show variables like 'character%';
在cmd黑色命令行窗口中输入时gbk字符集，告诉数据库服务器，要用gbk输入数据
怎么解决中文乱码问题呢？？？
将客户端相关的三个编码集设置为gbk   ---- set character_set_results = gbk;或者使用-----set names gbk;
2、数据表记录的修改操作 update
语法--- update 表名 set 列名 = 值, 列名 = 值,... where 条件语句;
注意： 如果没有where语句，将更新所有的行


要求
将所有员工薪水修改为5000元----update employee set salary = 5000;
将姓名为’zs’的员工薪水修改为3000元----update employee set salary = 3000 where name = 'zs';
update employee set salary = 3000 where binary name = 'ZS';----在条件语句之前binary，使条件语句更加精确严格
将姓名为’ls’的员工薪水修改为4000元,job改为ccc-----update employee set salary = 4000,resume = '他是一位老板' where name = 'ls';
将ww的薪水在原有基础上增加1000元----update employee set salary = salary + 1000 where binary name = 'ww';


3、数据表记录通过delete语句进行删除
语法----delete from 表名 where 条件语句;
注意：如果没有where条件语句，将会删除表中的所有数据
 delete语句不能删除某一列的值，可以使用update语句更新某一列的值
 使用delete语句仅删除一条记录，不能删除表本身。要删除表的本身，使用 drop table 表名;
删除一个表的所有记录，也可以使用 truncate 表名;但是它和delete from 表名; 语句不同
区别：
truncate 删除记录后是不可恢复的，不受事物管理。原理：先删除整个表，然后重新创建一个新表
delete 语句是可以被事物管理的，在事物中删除数据可以回滚恢复的。原理：一行一行删除数据
truncate 删除所有数据性能上要比delete from语句要好

练习：
删除表中名称为’zs’的记录---- delete from employee where binary name = 'zs';
删除表中所有记录----delete from employee;
***演示事物的回滚，通过delete from 语句在事物中删除数据是可以恢复的。
mysql>start transaction;
mysql>delete from employee;
mysql>rollback;
使用truncate删除表中记录 --- truncate employee; 数据永远的删除，不可以恢复


4、数据记录的查询
语法 ---- select [distinct] * | 指定的列名,指定的列名,... from 表名;
其中distinct 是指 显示结果时，是否过滤重复的数据
1)基本的查询
查询employee表中所有的记录---- select * from employee;
查询employee表中的gender数据--- select gender from employee;
查询employee表中所有的性别--- select distinct gender from employee;

创建scores表-----
create table scores(
id int primary key not null auto_increment,
name  varchar(40),
math double,
chinese double,
english double
);

insert into scores values(null,'老徐',60,86,78);
insert into scores values(null,'老汪',87,98,79);
insert into scores values(null,'老李',91,68,90);

练习：
查询表中所有学生的信息---select * from scores;
查询表中所有学生的姓名和对应的英语成绩----select name,english from scores;
过滤表中重复数据----select distinct name,math,chinese,english from scores;


2)在select语句中使用表达式对查询的列进行运算
语法---select 列名表达式 from 表名;
在select语句中使用as语句---- select 列名 as 别名 from 表名;  或者 select 列名 别名 from 表名;


练习：
在所有学生英语分数上加10分特长分---select name,english+10 from scores;
统计每个学生的总分----select name,math+chinese+english from scores;
使用别名表示每个学生总分分数----select name，math+chinese+english as 总分 from scores;

3)使用where子句，进行过滤查询。
练习：
查询姓名为老徐的学生成绩----select * from scores where name = '老徐';
查询英语成绩大于79分的同学----select * from scores where english > 79;
查询总分大于250分的所有同学----select * from scores where math+chinese+english > 250;


4)在where子句中经常使用的运算符
练习：
查询英语分数在 80－90之间的同学----select * from scores where english between 80 and 90;
查询数学分数为89,90,91的同学。----select * from scores where math in(89,90,91);
查询所有姓李的学生成绩----select * from scores where name like '李%';
查询数学分>80，语文分>80的同学----select * from scores where math > 80 and chinese > 80;


5)对select查询结果通过 order by 语句进行排序
语法----select * | 列名,.... from 表名 where 条件语句 order by 列名 asc|desc ,列名 asc | desc,...;

练习：
对数学成绩排序后输出----select * from scores order by math ;   ---默认是升序
对总分排序按从高到低的顺序输出----select name,math+chinese+english 总分 from scores order by 总分 desc;
对姓老的学生数学成绩排序输出----select * from scores where name like '老%' order by math desc;
6)聚集函数(分组函数) -----结合查询分组进行数据统计

*count(列名) 返回某一列，行的总数
语法---select count(*) | count(列名) from 表名 where 条件语句;
练习：
统计一个班级共有多少学生----select count(*) from scores;
统计数学成绩大于90的学生有多少个-----select count(*) from scores where math > 90;
统计总分大于250的人数有多少----select count(*) from scores where math+chinese+english > 250;

*sum(列名)对一列的数据求和
语法----select sum(列名),sum(列名),...from 表名 where 条件语句; 
注意：sum仅对数值起作用，否则会报错。
 对多列求和，“，”号不能少。
 sum求和时null不参与运算
练习：
统计一个班级数学总成绩---select sum(math) 数学总成绩 from scores;
统计一个班级语文、英语、数学各科的总成绩---select sum(chinese),sum(math),sum(english) from scores;
统计一个班级语文、英语、数学的成绩总和---select sum(math+english+chinese) from scores;
统计一个班级语文成绩平均分----select sum(chinese)/count(chinese) from scores;


*avg(列名) 对一列数据求平均值
语法----select avg(列名),avg(列名),.... from 表名 where 条件语句;
练习：
求一个班级数学平均分-----select avg(math) from scores;
求一个班级总分平均分----select avg(math+chinese+english) from scores;


*max min 对一列数据求最大值和最小值
语法----select max(列名),... from 表名 where 条件语句;
练习：
求班级总分最高分和总分最低分（数值范围在统计中特别有用）----select max(math+chinese+english),min(math+chinese+english) from scores;
思考题：语文最高分是谁？select max(chinese) from scores;
select name from scores where chinese = (select max(chinese) from scores);

*group by 对列进行分组，目的用于统计，前提：有重复的数据
语法---select 列名,列名,.... from 表名 group by 列名 having ....
说明:having子句对分组的结果进行过滤

create table orders(
id int,
product varchar(20),
price float
);


insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);

练习：
对订单表中商品归类后，显示每一类商品的总价----select product,sum(price) from orders group by product;
查询购买了几类商品，并且每类总价大于100的商品---select product,sum(price) from orders group by product having sum(price) > 100;





第四部分: 数据库的备份与恢复
备份---- cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql   //在cmd中执行
例如：mysqldump -u root -p day11 > c:\day11.sql

恢复----首先删除数据库 drop database day11;
1)创建空的数据库---create database day11;
2)恢复数据库---
方式一--->source c:\day11.sql    //在mysql内部使用
例如：
mysql>use day11;
mysql>source c:\day11.sql

方式二--->mysql –u 用户名 -p 数据库名 < 文件名.sql  // 在cmd下使用
例如：
mysql -u root -p day11 < c:\day11.sql

第五部分：多表设计


1、关联子查询：
*查询student表中年龄最大学员的信息
查询最大年龄---select max(age) from student;
查询最大年龄的学员----select * from student where age = (select max(age) from student);

2、in/exists  当前查询记录结果在子查询中存在
*查询所有成绩小于60分的同学名称
1)用in实现查询
先在studentcource表中查询成绩小于60分的student_id 
----select student_id from studentcource where score < 60;
在根据student_id在student表中查询 
----select * from student where id in (select student_id from studentcource where score < 60);
2)用exists实现
select * from student where exists(select student_id from studentcource where score < 60 and student.id = studentcource.student_id);
3、any some all 用法
*查询获得最高分的学生学号
1)select student_id from studentcource where score = (select max(score) from studentcource);
2)select student_id from studentcource where score >= all(select sccre from studentcource);

*查询编号2课程比编号1课程成绩高所有学号
查询编号2课程的成绩---select score from studentcource where cource_id = 2;
查询标号1课程的成绩---select score from studentcourc where cource_id = 1;
select score from studentcource where cource_id = 2 and score > any(select score from studentcource where cource_id = 1);

第六部分：练习
1、查询语文课程比数学课程成绩高的所有学生的学号

select t1.student_id 学号,t1.score 语文,t2.score 数学 from 
(select * from studentcource,cource where studentcource.cource_id = cource.id and cource.name = '语文') t1,
(select * from studentcource,cource where studentcource.cource_id = cource.id and cource.name= '数学') t2 
where t1.student_id = t2.student_id and t1.score > t2.score;

2、查询平均成绩大于70分的同学的学号和平均成绩
select student_id,avg(score) from studentcource group by student_id having avg(score) > 70;

select student.id 学号, t.avgscore 平均分 from student,
(select student_id,avg(score) avgscore from studentcource group by student_id having avg(score) > 70) t 
where student.id = t.student_id;


3、查询所有同学的学号、姓名、选课数、总成绩
select student_id,count(*),sum(score) from studentcource group by student_id;
select * from student;
-----select student.id,student.name,t.courcenum 选课数,t.sumscore 总成绩 from student,(select student_id,count(*) 
courcenum,sum(score) sumscore from studentcource group by student_id) t where 
student.id = t.student_id;
4、查询没学过关羽老师课的同学的学号、姓名
关羽老师教授哪些课---select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '关羽';
选过关羽老师课的学生---select distinct student_id from studentcource where 
cource_id in(select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '关羽');

最后的结果----select id,name from student where id not in(select distinct student_id from studentcource where 
cource_id in(select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '关羽'));

5、查询学过语文并且也学过数学课程的同学的学号、姓名
select studentcource.student_id from cource,studentcource where cource.id = studentcource.cource_id and cource.name= '语文';
select studentcource.student_id from cource,studentcource where cource.id = studentcource.cource_id and cource.name = '数学';

select t1.student_id from 
(select studentcource.student_id from cource,studentcource where cource.id = studentcource.cource_id and cource.name= '语文') t1,
(select studentcource.student_id from cource,studentcource where cource.id = studentcource.cource_id and cource.name = '数学') t2
where t1.student_id = t2.student_id;

最后的结果是---select student.id,student.name from student,(select t1.student_id from 
(select studentcource.student_id from cource,studentcource where cource.id = studentcource.cource_id and cource.name= '语文') t1,
(select studentcource.student_id from cource,studentcource where cource.id = studentcource.cource_id and cource.name = '数学') t2
where t1.student_id = t2.student_id) t where student.id = t.student_id;


6、查询学过赵云老师所教的所有课的同学的学号、姓名
赵云老师教授哪些课---select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '赵云';

学过赵云老师的课的学生---select studentcource.student_id from studentcource where 
studentcource.cource_id in(select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '赵云');

最后结果----select student.id,student.name from student,(select studentcource.student_id from studentcource where 
studentcource.cource_id in(select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '赵云')) t
where student.id = t.student_id;

7、查询没有学三门课以上的同学的学号、姓名
select student_id,count(*) from studentcource group by student_id having count(*) < 3;

最后的结果----select student.id,student.name from 
student,(select student_id,count(*) from studentcource group by student_id having count(*) < 3) t
where student.id = t.student_id;

8、查询至少有一门课与学号为“1”的同学所学课相同的 同学的学号和姓名
学好为1的同学学哪些课---
select studentcource.cource_id from student,studentcource where student.id =studentcource.student_id and student.id = 1;

select distinct student_id from studentcource where 
cource_id in(select studentcource.cource_id from student,studentcource where student.id =studentcource.student_id and student.id = 1);

最后的结果----select student.id,student.name from student,(select distinct student_id from studentcource where 
cource_id in(select studentcource.cource_id from student,studentcource where student.id =studentcource.student_id and student.id = 1)) t
where student.id = t.student_id;

9、查询和小李同学学习的课程完全相同的其他同学学号和姓名
小李同学学习的课程----select count(studentcource.cource_id) from student,studentcource where student.id =studentcource.student_id and student.name ='小李';

select studentcource.student_id from studentcource,
(select studentcource.cource_id from student,studentcource where student.id =studentcource.student_id and student.name ='小李') t
where studentcource.cource_id = t.cource_id;

select t.student_id,count(*) from (select studentcource.student_id from studentcource,
(select studentcource.cource_id from student,studentcource where student.id =studentcource.student_id and student.name ='小李') t
where studentcource.cource_id = t.cource_id) t group by t.student_id having 
count(*) = (select count(studentcource.cource_id) from student,studentcource where student.id =studentcource.student_id and student.name ='小李');


最后的结果---select student.id,student.name from student,(select t.student_id,count(*) from (select studentcource.student_id from studentcource,
(select studentcource.cource_id from student,studentcource where student.id =studentcource.student_id and student.name ='小李') t
where studentcource.cource_id = t.cource_id) t group by t.student_id having 
count(*) = (select count(studentcource.cource_id) from student,studentcource where student.id =studentcource.student_id and student.name ='小李')) tt
where student.id = tt.student_id;

10、查询各科成绩最高和最低的分
select cource_id,max(score),min(score) from studentcource group by cource_id;

11、查询学生信息和平均成绩
select student_id,avg(score) from studentcource group by student_id;

最后的结果---select * from student,(select student_id,avg(score) avgscore from studentcource group by student_id) t
where student.id = t.student_id;

12、查询上海和北京学生数量
select city,count(*) from student where city = '上海' or city = '北京' group by city;

13、查询不及格的学生信息和课程信息
select student_id,cource_id from studentcource where score < 60;

最后的结果---- select * from student,cource,(select student_id,cource_id from studentcource where score < 60) t
where student.id = t.student_id and cource.id = t.cource_id;

14、查询每门功课成绩最好的前两名
select cource_id from studentcource group by cource_id;
select * from studentcource where cource_id in(select cource_id from studentcource group by cource_id) order by score desc limit 0,2;




15、统计每门课程的学生选修人数（超过两人的进行统计）
select cource_id,count(*) from studentcource group by cource_id having count(*) > 2;

16、把成绩表中“关羽”老师教的课的成绩都更改为此课程的平均成绩
关羽老师教授的课程---select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '关羽';
平均成绩---- select cource_id,avg(score) from studentcource where 
cource_id in(select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '关羽') group by cource_id;


最后的结果----update studentcource,(select cource_id,avg(score) avgscore from studentcource where 
cource_id in(select cource.id from teacher,cource where teacher.id = cource.teacher_id and teacher.name = '关羽') group by cource_id) t
set score = t.avgscore where studentcource.cource_id = t.cource_id;