mysql的备份与练习

sql structed query language

连接MYSQL：mysql -uroot -proot

显示所有数据库
show databases;
mysql -uroot -proot --default-character-set=gbk
新建一个数据库：
create database mydb1;
create database mydb2 CHARACTER SET gbk ;
create database mydb3 CHARACTER SET utf8 collate utf8_general_ci;

显示创建数据库的语句信息
show create database mydb2;

创建一个名称为mydb1的数据库。
创建一个使用utf8字符集的mydb2数据库。
创建一个使用utf8字符集，并带校对规则的mydb3数据库

查看库
查看所有的库：show databases;
查看指定的库：show create database mydb1; --显示创建数据库的语句信息

修改数据库
alter database mydb1 character set utf8;

删除库：
drop database mydb1;

备份数据

准备数据：
create database mydb1;
use mydb1;
create table user(
id int,
name varchar(20)
);
insert into user(id,name) values(1,‘Tom’);
select * from user;

desc 表名

备份：
mysqldump -uroot -p mydb1>d:\test.sql;–备份数据库里的数据

恢复；
mysql -uroot -p mydb1<d:\test.sql
source d:\test.sql

表的操作：

建表：

create table person(
	id int,
	name varchar(20)
);

数据类型：
1 数值：
bit 1位
tinyint

建表：
create table employee(
id int,
name varchar(20),
sex bit,
birthday datetime,
entry_date datetime,
job varchar(20),
salary double,
resume text
);
查看表：
查看所有的表：
show tables;
查看指定表的创建语句
show create table employee;
显示指定表的结构：
desc employee;
删除表：
drop table employee;
修改表：
更改表名：rename table employee to worker;
修改表的字符集:alter table worker character set gbk;
增加一个字段：alter table worker add column height double;
修改一个字段：alter table worker modify column height float;
删除一个字段：alter table worker drop column height;
修改一个字段的名称：alter table worker change column name username varchar(20);

DDL: Data define Language

DML:

CURD

C（Create添加数据）：insert
建表：
create table employee(
id int,
name varchar(20),
sex bit,
birthday date,
salary double,
entry_date date,
resume text
);
insert into employee(name,id,sex,birthday,salary,entry_date,resume) values(‘张三’,1,1,‘1983-09-21’,15000,‘2012-06-24’,‘一个大牛’);
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,‘李四’,1,‘1984-09-21’,10000,‘2012-07-24’,‘一个中牛’);
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,‘王五’,0,‘1985-09-21’,7000,‘2012-08-24’,‘一个小牛’);

更新：
将所有员工薪水都增加500元。
update employee set salary=salary+500;
将王五的员工薪水修改为10000元，resume改为也是一个中牛
update employee set salary=10000,resume=‘也是一个中牛’ where name=‘王五’;

删除：
delete from employee where name=‘王五’;
delete from employee where id=3;
delete from employee;–可以有条件，但删除所有记录差了一点
truncate employee;–无条件 效率高

查询：

查询表中所有学生的信息。
select * from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select english from student;
select DISTINCT english from student;
select DISTINCT english,name from student;

select english+chinese+math from student;
select english+chinese+math as 总分 from student;
select name,english+chinese+math as 总分 from student;

在所有学生英语分数上加10分特长分。
select name,english+10 from student;
统计每个学生的总分。
select english+chinese+math from student;
使用别名表示学生分数
select name,english+chinese+math as 总分 from student;
select name,english+chinese+math ‘总分’ from student;

查询姓名为何东的学生成绩
select * from student where name=‘何东’;
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于250分的所有同学
select * from student where english+chinese+math>250;

查询英语分数在 85－95之间的同学。
select * from student where english>=85 and english<=95;
select * from student where english between 85 and 95;
查询数学分数为84,90,91的同学。
select * from student where math=84 or math=90 or math=91;
select * from student where math in(84,90,91);
查询所有姓何的学生成绩。
select * from student where name like ‘何%’;
查询数学分>85，语文分>90的同学。
select * from student where math>85 and chinese>90;

对数学成绩排序后输出。
select * from student order by math;
对总分排序后输出，然后再按从高到低的顺序输出
select * from student order by math+chinese+english desc;
对姓何的学生成绩排序输出
select * from student where name like ‘何%’
order by math+chinese+english desc;

select name, math+history+english from student where name
like ‘l%’ order by math+history+english desc;

统计一个班级共有多少学生？
select count() from student;
统计数学成绩大于90的学生有多少个？
select count() from student where math>90;
统计总分大于250的人数有多少？
select count(*) from student where math+chinese+english>250;

统计一个班级数学总成绩？
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math), sum(chinese), sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(math+chinese+english)from student;
select sum(math)+sum(chinese)+sum(english) from student;

求一个班级数学平均分？
select avg(math) from student;
求一个班级总分平均分
select avg(math+chinese+english)from student;
select avg(math)+avg(chinese)+avg(english) from student;

求班级最高分和最低分
select max(math+chinese+english),
min(math+chinese+english) from student;

给表添加一个字段：alter table student add column class_id int;
更新表：
update student set class_id=1 where id<=5;
update student set class_id=2 where id>5;

查出各个班的总分，最高分
select sum(math+chinese+english),max(math+chinese+english) from student group by class_id;

查询出班级总分大于1300分的班级ID
select class_id from student group by class_id having sum(math+chinese+english)>1300 ;

select id,name,sum(math+english+chinese) from stuinfo group by id having sum(math+chinese+english)>250;
select id,name,sum(math+english+chinese) from stuinfo group by id where sum(math+chinese+english)>250;

select RAND(3);

create table user(
id int primary key,
name varchar(20)
);

insert into user(name) values(‘Tom’);–执行不通过

insert into user(id,name) values(1,‘Tom’);
insert into user(id,name) values(1,‘kl’);

alter table user modify column id int auto_increment;

insert into user(id,name) values(2,‘kl’);

insert into user(name) values(‘kl’);
insert into user(id,name) values(6,‘kl’);
insert into user(id,name) values(4,‘kl’);
insert into user(name) values(‘kl’);

alter table user modify column name varchar(20) unique;
insert into user(name) values(‘Tom’);

alter table user modify column name varchar(20) not null;

create table class(
id int primary key auto_increment,
name varchar(20)
);
insert into class(name) values(‘JAVAEE’);
insert into class(name) values(‘Android’);

alter table student add constraint student_class_id_FK foreign key(class_id) references class(id);

insert into student(name,class_id) values(‘dd’,3);–失败
insert into student(name,class_id) values(‘dd’,null);–成功，但是不可以－－－〉外键要为not null

删除一个表的主键约束
alter table test drop primary key;

create table test(
id int primary key,
age int
);