MYSQL基本语法、备份、增删改查、实例

 

sql: structured querylanguage(结构化查询语言)


用户名和密码：root

创建一个名称为mydb1的数据库。
create database mydb1;
查看所有数据库
show databases;
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
创建一个使用utf-8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collateutf8_general_ci;


显示库的创建信息
show create database mydb3;


删除前面创建的mydb1数据库
drop database mydb1;


查看服务器中的数据库，并把其中某一个库的字符集修改为gb2312;
alter database mydb2 character set gb2312;
show create database mydb2;


备份库
1、准备库的数据
create database mydb1;
use mydb1;
create table test
(
            idint
);
insert into test(id) values(1);
select * from test;


2、备份库
  2.1 退出mysql客户端:quit
  2.2 在windows命令行窗口中下执行：mysqldump-uroot -p mydb1>c:\test.sql


3、删除库：drop database mydb1;


4、恢复库(1)：
            4.1创建库：create database mydb1;
            4.2source c:\test.sql  (通过执行脚本文件实现)
5、恢复库(2)：mysql-uroot -p mydb1<c:\test.sql  (window命令)




创建一个员工表
use mydb1;  进入库
create table employee
(
            idint,
            namevarchar(20),
            gendervarchar(4),
            birthdaydate,
            entry_datedate,
            jobvarchar(40),
            salarydouble,
            resumetext
)character set utf8 collate utf8_general_ci;


查看库中所有表
show tables;


查看表的创建细节
show create table employee;


查看表的结构
desc employee;


在上面员工表的基本上增加一个image列。
alter table employee add image blob;


修改job列，使其长度为60。
alter table employee modify jobvarchar(60); 


删除sex列。
alter table employee drop gender;


表名改为user。
rename table employee to user;


修改表的字符集为utf-8
alter table user character set gb2312;
show create table user;


列名name修改为username
alter table user change column name usernamevarchar(20);


使用insert语句向表中插入一个员工的信息。
insert intoemployee(id,username,birthday,entry_date,job,salary,resume)values(1,'aaa','1980-09-09','1980-09-09','bbb',1000,'bbbbbbbb');


查看插入的数据
select * from employee;




使用insert语句向表中插入一个员工的信息。
insert intoemployee(id,username,birthday,entry_date,job,salary,resume) values(2,'小李子','1980-09-09','1980-09-09','bbb',1000,'bbbbbbbb');


插入失败后的解决方案
show variables like 'chara%';
set character_set_client=gb2312;




显示失败后的解决方案
set character_set_results=gb2312;


将所有员工薪水修改为5000元。
update employee set salary=5000;


将姓名为’aaa’的员工薪水修改为3000元。
update employee set salary=3000 whereusername='aaa';


将姓名为’aaa’的员工薪水修改为4000元,job改为ccc
update employee set salary=4000,job='ccc' whereusername='aaa';


将aaa的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 whereusername='aaa';


删除表中名称为’zs’的记录。
delete from employee where username='小李子';


删除表中所有记录。
delete from employee;


使用truncate删除表中记录。
truncate table employee;


查询表中所有学生的信息。
select id,name,chinese,english,math fromstudent;
select * from student;


查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;


过滤表中重复数据。
select distinct english from student;


在所有学生的英语分数上加10分特长分。
select name,english+10 from student;


统计每个学生的总分。
select name,(english+chinese+math) from student;


使用别名表示学生分数。
select name as 姓名,(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;


查询总分大于200分的所有同学
select * from student where(english+chinese+math)>200;


查询英语分数在 80－90之间的同学。
select * from student where english>80 andenglish<90;
select * from student where english between 80and 90;


查询数学分数为89,90,91的同学。
select * from student where math=80 or math=90or math=91;
select * from student where math in(80,90,91);


查询所有姓李的学生成绩。
select * from student where name like '李%';


对数学成绩排序后输出。
select name,math from student order by math;


对总分排序后输出，然后再按从高到低的顺序输出
select name from student order by(math+english+chinese) desc;


对姓李的学生成绩排序输出
select name 姓名,(math+english+chinese)总分 from student where name like '李%'order by (math+english+chinese) desc;


统计一个班级共有多少学生？
select count(*) from student;
select count(name) from student;


统计数学成绩大于90的学生有多少个？
select count(*) from student where math>90;


统计总分大于250的人数有多少？
select count(*) from student where(math+english+chinese)>250;


统计一个班级数学总成绩？
select sum(math) from student;


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


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


统计一个班级语文成绩平均分
select sum(chinese)/count(chinese) from student;


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


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


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


对订单表中商品归类后，显示每一类商品的总价
select product from orders group by product;
select product,sum(price) from orders group byproduct;


查询购买了几类商品，并且每类总价大于100的商品
select product from orders  group byproduct having sum(price)>100;




定义带有主键约束的表
create table test1
(
            idint primary key,
            namevarchar(20),
            passwordvarchar(20)
);


定义一个主键 自动增长的表
create table test2
(
            idint primary key auto_increment,
            namevarchar(20),
            passwordvarchar(20)
);


create table test3
(
            idint primary key auto_increment,
            namevarchar(20) unique
);


create table test4
(
            idint primary key auto_increment,
            namevarchar(20) unique not null
);


//什么是外键约束


create table husband
(
            idint primary key,
            namevarchar(20)
);


create table wife
(
            idint primary key,
            namevarchar(20),
            husband_idint,
            constrainthusband_id_FK foreign key(husband_id) references husband(id)
);




//一对多或多对一


create table department
(


)


create table employee
(


)




//多对多


create table teacher
(
            idint primary key,
            namevarchar(20),
            salarydouble
);


create table student
(
            idint primary key,
            namevarchar(20) 
);




create table teacher_student
(
            teacher_idint,
            student_idint,
            primarykey(teacher_id,student_id),
            constraintteacher_id_FK foreign key(teacher_id) references teacher(id),
            constraintstudent_id_FK foreign key(student_id) references student(id)          
);


//一对一


create table person
(
            idint primary key,
            namevarchar(20)
);


create table idcard
(
            idint primary key,
            addressvarchar(40),
            constraintid_FK foreign key(id) references person(id)
);

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

 

            create database db_stu_course;
            use db_stu_course;


create table student(
stu_no  varchar(20) primary key ,     
stu_name  varchar(20),        
stu_sex  varchar(20),         
stu_age  int ,               
stu_dept varchar(20),          
stu_grade varchar(20),        
stu_date  Date
);


insert into student(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('001','wqy','男','19','计算机学院','一班','1990-01-23');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('002','zmm','女','19','计算机学院','一班','1993-04-25');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('003','lidawei','男','19','计算机学院','一班','1997-05-13');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('004','yangzhiming','男','19','化学学院','二班','1992-11-23');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('005','wangqing','男','19','化学学院','二班','1994-01-13');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('006','shaoyuchun','男','19','化学学院','二班','1988-02-11');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('007','liminghao','女','19','物理学院','三班','1987-01-03');
insert into student(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('008','wqy','男','19','物理学院','三班','1988-01-23');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('009','chengwei','男','19','物理学院','三班','1999-02-13');
insert into student(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('010','tingting','女','19','英语学院','四班','1995-03-22');
insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('011','yuyu','女','19','英语学院','四班班','1994-04-26');
insert into student(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('012','huahua','女','19','英语学院','四班','1991-05-27');




create table teacher(
teacher_no        varchar(20) primary key,
teacher_name       varchar(20),
teacher_sex       varchar(20) check(teacher_sex  ="男" orteacher_sex  = "女" ),
teacher_age     int     check(teacher_age between0 and 100) 
);


insert into teacher(teacher_no ,teacher_name ,teacher_sex,teacher_age )values('teacher01','熊建华','女','49');
insert into teacher(teacher_no ,teacher_name ,teacher_sex,teacher_age )values('teacher02','王明文','男','50');
insert into teacher(teacher_no ,teacher_name ,teacher_sex,teacher_age )values('teacher03','刘超','男','55');
insert into teacher(teacher_no ,teacher_name ,teacher_sex,teacher_age )values('teacher04','雷博士','男','38');
 


create table course(
course_no        varchar(20) primary key,
course_name       varchar(20),
course_xuefen       int check(course_xuefen between 1 and5),
course_teacher_no   varchar(20),
constraint fk_teacher_no foreign key(course_teacher_no) referencesteacher(teacher_no)
);


insert intocourse(course_no,course_name,course_xuefen,course_teacher_no)values('course01','java','2','teacher01');
insert intocourse(course_no,course_name,course_xuefen,course_teacher_no)values('course02','化学','3','teacher02');
insert intocourse(course_no,course_name,course_xuefen,course_teacher_no)values('course03','物理','4','teacher03');
insert intocourse(course_no,course_name,course_xuefen,course_teacher_no)values('course04','英语','5','teacher04');
 
                                                                     


create table student_course(
stu_no        varchar(20) ,
course_no       varchar(20),
score     int     check(score between 0 and 100),
constraint fk_stu_no foreign key(stu_no) references student(stu_no),
constraint fk_course_no foreign key(course_no) references course(course_no),
primary key(stu_no, course_no)
);
 


insert into student_course(stu_no, course_no,score)values('001','course01','98');
insert into student_course(stu_no, course_no,score)values('002','course01','45');
insert into student_course(stu_no, course_no,score)values('003','course01','56');
insert into student_course(stu_no, course_no,score)values('004','course02','78');
insert into student_course(stu_no, course_no,score)values('005','course02','98');
insert into student_course(stu_no, course_no,score)values('006','course02','23');
insert into student_course(stu_no, course_no,score)values('007','course03','88');
insert into student_course(stu_no, course_no,score)values('008','course03','77');
insert into student_course(stu_no, course_no,score)values('009','course03','66');
insert into student_course(stu_no, course_no,score)values('010','course04','74');
insert into student_course(stu_no, course_no,score)values('011','course04','91');
insert into student_course(stu_no, course_no,score)values('012','course04','94');




计算出男女学生的个数
select  count(*) from student group by stu_sex;
select  count(*) from student  where stu_sex='女';
select  stu_sex,count(*) from student group by stu_sex ;
select stu_sex,count(*) from student where stu_sex in('男','女')group by stu_sex;




根据学生的stu_date升序排序
select stu_name,stu_date from student order by stu_date ASC;
根据学生的stu_date降序排序
select stu_name,stu_date from student order by stu_date DESC;


根据学生的stu_age升序排序
select stu_name,stu_age from student order by stu_age ASC;




分别计算每个学院学生的人数
select stu_dept,count(*) from student group by stu_dept ;
分别计算每个学院女生的人数
select stu_dept,count(*) from student where stu_sex='女'group by stu_dept  ;
计算每个学院的总分
select stu_dept,sum(sc.score) from student as s,student_course as sc wheres.stu_no=sc.stu_no  group by stu_dept   ;
计算每个学院的总分并升序排序
select stu_dept,sum(sc.score) from student as s,student_course as sc wheres.stu_no=sc.stu_no  group by stu_dept order by sum(sc.score) ;
计算每个学院的平均分并升序排序
select stu_dept,avg(sc.score) from student as s,student_course as sc wheres.stu_no=sc.stu_no  group by stu_dept order by sum(sc.score) ;


选了熊建华老师的学生名单
select stu_dept,avg(sc.score) from student as s,student_course as sc wheres.stu_no=sc.stu_no  group by stu_dept ;








z

create table student(
stu_no  varchar(20) primary key ,     
stu_name  varchar(20),        
stu_sex  varchar(20),         
stu_age  int ,               
stu_dept varchar(20),          
stu_grade varchar(20),        
stu_date  Date
);


create table teacher(
teacher_no        varchar(20) primary key,
teacher_name       varchar(20),
teacher_sex       varchar(20) check(teacher_sex  ="男" orteacher_sex  = "女" ),
teacher_age     int     check(teacher_age between0 and 100) 
);


create table course(
course_no        varchar(20) primary key,
course_name       varchar(20),
course_xuefen       int check(course_xuefen between 1 and5),
course_teacher_no   varchar(20),
constraint fk_teacher_no foreign key(course_teacher_no) referencesteacher(teacher_no)
);


create table student_course(
stu_no        varchar(20) ,
course_no       varchar(20),
score     int     check(score between 0 and 100),
constraint fk_stu_no foreign key(stu_no) references student(stu_no),
constraint fk_course_no foreign key(course_no) references course(course_no),
primary key(stu_no, course_no)
);
 
 选了熊建华老师的学生名单
select stu_name  from student  as s,teacher as t,course asc,student_course as sc where  sc.course_no=c.course_no and t.teacher_no=c.course_teacher_no and t.teacher_name='熊建华' and s.stu_no=sc.stu_no ;




选了熊建华老师且成绩大于90的学生名单
select stu_name  from student  as s,teacher as t,course asc,student_course as sc where  sc.course_no=c.course_no and t.teacher_no=c.course_teacher_no and t.teacher_name='熊建华' and s.stu_no=sc.stu_no and  sc.score>90;


选择成绩是20或 100 的学生
select stu_name  from student where stu_age in (20,100);


选择姓名是是wqy或 zmm的学生
select stu_name,stu_sex  from student where stu_name in ('wqy','zmm');
select stu_name,stu_sex  from student where stu_name='wqy' orstu_name='zmm';
选择年龄是19-25的学生
select stu_name,stu_sex ,stu_age from student where  stu_age between 19and 25;


选择姓wweenn学生的名单
select *from student where stu_name like 'wweenn%' ;
选择姓包含‘源’的学生名单
select *from student where stu_name like '%源%' ;


一源结尾的学生姓名
select *from student where stu_name like '%源' ;
年龄大于19且姓名为wweennsource的学生
select *from student where stu_age > 19 and stu_name='wweennsource';


select *from student where stu_age > 19 having stu_name='wweennsource';


distinct过滤重复的数据
select distinct(stu_sex) from student ;


select count(distinct(stu_sex))from student ;


select *from student  order by stu_age limit 4;
最大年龄的学生
select  stu_name,stu_age from student where stu_age=(select max(stu_age) from student);
最小年龄的学生
select  stu_name,stu_age from student where stu_age=(select min(stu_age) from student);


最小年龄的学生
select  min(stu_age) as '年龄' from student;




insert intostudent(stu_no,stu_name,stu_sex,stu_age,stu_dept,stu_grade,stu_date)values('013','test','女','19','英语学院','四班','1980-05-27');


复制标的结构
create table old_student select *from student where 1=2;


create table old2_student like student;


create table old_student select *from student;
delete *from old_student where 1=1;


            create table tb1(
    no  varchar(20) primary key ,     
    name  varchar(20),               
    age  int ,         
    date  Date
                       );
            insert intotb1(no,name,age,date)values('001','tom',15,'1990-02-22');
            insert intotb1(no,name,age,date)values('002','tom2',25,'1990-02-22');
            insert intotb1(no,name,age,date)values('003','tom3',35,'1990-02-22');
            insert into tb1(no,name,age,date)values('004','tom4',55,'1990-02-22');


            create table tb2(
    no2  varchar(20) primary key ,     
    name2  varchar(20),               
    age2  varchar(20) ,         
    date2  varchar(20)
                       );




            create table tb3(
    no3  varchar(20) primary key ,     
    name3 varchar(20),               
    age3  int ,         
    date3  Date
                       );


            create table tb2(
    no2  varchar(20) primary key ,     
    name2  varchar(20),               
    age2  int ,         
    date2  Date
                       );
           
mysql中用命令行复制表结构的方法主要有一下几种: 


1.只复制表结构到新表


1 CREATE TABLE 新表 SELECT * FROM 旧表WHERE 1=2;
或


1 CREATE TABLE 新表 LIKE 旧表;
注意上面两种方式，前一种方式是不会复制时的主键类型和自增方式是不会复制过去的，而后一种方式是把旧表的所有字段类型都复制到新表。


 
2.复制表结构及数据到新表


1 CREATE TABLE 新表 SELECT * FROM 旧表
 


3.复制旧表的数据到新表(假设两个表结构一样) 


1 INSERT INTO 新表 SELECT * FROM 旧表
 


4.复制旧表的数据到新表(假设两个表结构不一样)


1 INSERT INTO 新表(字段1,字段2,.......)SELECT 字段1,字段2,......FROM 旧表



























create table student(
stu_no  varchar(20) primary key ,    --auto_increment, -- 学号
stu_name  varchar(20),        --姓名
stu_sex  varchar(20)  check(stu_sex = "男"or stu_sex = "女" ),        --性别
stu_age  int check(stu_age between 10 and 100),               --年龄
stu_dept varchar(20),         --学院
stu_grade varchar(20),        --班级
stu_date  Date
);





学生表：
create table student
(
stuId varchar(10) primary key,
stuName varchar(10) not null,
stuSex char(2) check(stuSex = "男" or stuSex = "女"),
age int check(age between 15 and 45)
)
课程表：
create table course
(
couId varchar(5) primary key,
couName varchar(20) not null
)
成绩表：
create table score
(
stuId varchar(10),
couId varchar(5),
grade int check(grade between 0 and 100),
constraint fk_stuId foreign key(stuId) references student(stuId),
constraint fk_couId foreign key(couId) references course(couId),
primary key(stuId, couId)
)


每个学生的所有信息查询：
selectstudent.stuId,student.name,student.sex,student.age,course.couName,score.scorefrom student,course,score where student.stuId = score.stuId and course.couId =score.couId;
查询每个学生的平均成绩：
select student.name as "姓名", avg(score) as"平均成绩" from student leftjoin score on  student.stuId = score.stuId group by student.stuId;
查询每个学生的总成绩：
mysql> select student.name as "姓名", avg(score) as"总成绩" from student leftjoin score on  student.stuId = score.stuId group by student.stuId;
查询每门科目的总成绩：
select course.couName as ”科目“, sum(score) as ”总成绩“from course left join score on course.couId = score.couId group bycourse.couId;
查询每门科目的平均成绩：
mysql> select course.couName as "科目名称", avg(score) as"平均成绩" from course leftjoin score on course.couId = score.couId group by course.couId;he



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

http://blog.csdn.net/u011686226/article/details/41014745   存储过程与触发器的应用
https://wenku.baidu.com/view/94552add6f1aff00bed51e48.html   学生选课表的建立
http://blog.csdn.net/u013156691/article/details/49248051    学生选课表的建立
http://blog.csdn.net/yan456jie/article/details/52557140      学生选课表的建立
http://blog.163.com/hks_blog/blog/static/214926090201382225845920/   group by 与having的用法
http://www.cnblogs.com/zhangjpn/p/6231662.html   mysql的复制数据库语句