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的复制数据库语句