创建 
create database student 
go 
学生表 
create table student 

stu_name varchar(30) not null, 
stu_id char primary key (stu_id), 
stu_specialty varchar(30) not null, 
stu_sex char(2) check( stu_sex in (‘男’,’女’)), 
stu_age int check (stu_age>16) 

课程表 
create table studentclass 

stu_classid varchar(30) not null, 
stu_id char not null primary key (stu_id), 
stu_classname varchar(30) not null, 
stu_age int check (stu_age>16), 
stu_time datetime not null 

成绩表 
create table studentscore 

澳门新葡亰游戏网址,stu_id char primary key (stu_id), 
stu_classid varchar(30) not null, 
stu_score int not null 

select *from student 
插入学生表数据 
insert into student values (‘张三’,’01’,’计算机专业’,’男’,’10’) 
insert into student values (‘张四’,’02’,’计算机专业’,’男’,’11’) 
insert into student values (‘王五’,’03’,’计算机专业’,’男’,’12’) 
插入课程表数据 
insert into studentclass values(‘001’,’java’,’2’,’40’) 
insert into studentclass values(‘001’,’c’,’2’,’41’) 
insert into studentclass values(‘001’,c++’,’2’,’42’) 
插入成绩表数据 
insert into studentscore values(‘01’,’001’,’60’) 
insert into studentscore values(‘01’,’002’,’70’) 
insert into studentscore values(‘01’,’003’,’80’)

基础MySQL语句

#创建表
#create table stu(id int primary key, name char(10), age int);

#删除表
#drop table stu;

#增加
#insert into stu(id, age, name) values(1, 20, ‘小明’);
#insert into stu(id, name, age) values(5, ‘小明5’, 40);

#删除
#全部删了
#delete from stu;
#delete from stu where id>3 and age=40;
#delete from stu where name=’小明’ or age=21;

#改
#update stu set name=’mmmm’;
#update stu set name=’xxx’ where id>3;
#update stu set name=’yyy’, age=’100′ where name=’mmmm’;

#查询
#select * from stu;
#select id, age from stu;
#select id, age, name from stu where id>2 and id<5;

基础MySQL语句 #create table stu( #StudyNo int primary key
auto_increment, #IdCarNo char(20) not null unique, #Name char(6) not
null, #Sex char not null, #Elective char(10));
#create table country( #name char(10) primary key, #language
char(10));
#create table president( #name char(10) primary key, #sex char,
#f_country_name char(10) unique);
#alter table president add constraint foreign key(f_country_name)
references country(name) on delete cascade;#设外键
#create table class( #classname char(10) primary key);
#alter table class add column headteacher char(10);#增加字段
#create table stuclass( #no int primary key auto_increment, #name
char(10), #age int, #f_classname char(10));
#alter table stuclass add constraint foreign key(f_classname)
references class(classname) on delete cascade;
#create table Teacher( #teacher_no int primary key auto_increment,
#name char(10));
#create table stu2( #stu_no int primary key auto_increment, #name
char(10));
#create table middle( #id int primary key auto_increment,
#f_teacher_no int, #f_stu_no int);
#alter table middle add constraint foreign key(f_teacher_no)
references teacher(teacher_no); #alter table middle add constraint
foreign key(f_stu_no) references stu2(stu_no);
#create table people( #id int primary key auto_increment, #name
char(10), #sex char, #myyear year, #mydate date, #mytime time);
#create table mytest( #id int primary key, #no1 int check(no1<20),
#no2 int check(no2>20 and no2<30), #no3 int check(no3>20 or
no3<30), #sex char(2) check(sex=’男’,sex=’女’));
#create table stu( #id int primary key auto_increment, #Name char(6)
not null, #Sex char not null check(Sex=’男’,Sex=’女’) , #age int
check(age>0 and age<120));
#insert into stu(id,Name,Sex,age) values(13,’小一’,’男’,30); #insert
into stu(Name,Sex,age) values(‘小二’,’男’,31); #insert into
stu(id,Name,Sex,age) values(15,’小三’,’男’,31); #insert into
stu(id,Name,Sex,age) values(17,’小四’,’女’,33) #insert into
stu(Name,Sex,age) values(‘小⑥’,’男’,33); #insert into stu(Name,Sex,age)
values(‘小五’,’女’,20) #insert into stu(Name,age,Sex)
values(‘小七’,20,’女’)
#delete from stu where id=15 #delete from stu where name=’小一’ and
sex=’男’ #delete from stu where name=’小一’ and sex=’男’ #delete from
stu where name=’小七’ and sex=’女’ and age=20 #delete from stu where
age>30
#update stu set sex=’W’ where sex=’M’ #update stu set sex=’M’ where
id>20 and id<25 #update stu set name=’小七’,sex=’W’,age=18 where
age = 26
#select * from stu #select id,name,age from stu #select * from stu
order by id #select * from stu order by age desc #select * from stu
where sex=’M’ #select * from stu where id not in (19,25,23) #select
* from stu where id not in (19,25,23) #select * from stu where id =19
or id =25 or id =23 #select sex,count(*) as 性别人数 from stu group by
sex #select sex,count(*) as 性别人数 from stu where id not in(19,30)
group by sex

select * into newstu from stu where id>20====>此句有疑问

MySQL不区分大小写问题
delete from stu;====>清空表的记录

2、查询:升序:select * from stu order by +字段=====>按字段升序

order by + 字段 + desc =====>按字段降序排列

3、select * from stu where id in(29,30,31,32)

====>where id=29 or id=30 or id=31 or id=32

select sex, count(*) from stu group by
sex;====>统计出不同性别各有多少人

select sex from stu group by sex;====>统计有多少种性别

select sex, count(*) as 性别人数 from stu group by
sex;====>统计出两个字段,表示不同性别各有多少人

select sex, avg(stu.age) from stu group by
sex;====>统计出不同性别的平均年龄

select sex, max(stu.age) from stu group by
sex;====>统计出不同性别各自最大的年龄

alter table class add column headteacher char(10);====>添加表中字段
alter table 表名 add 列名 varchar(20) alter table id_name drop column
age,drop column address;====>删除表中的两个字段

 

 

1.增加一个字段

alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL;
//增加一个字段,默认为空

alter table user add COLUMN new2 VARCHAR(20) NOT NULL;
//增加一个字段,默认不能为空

 

2.删除一个字段

alter table user DROP COLUMN new2;   //删除一个字段

 

3.修改一个字段

alter table user MODIFY new1 VARCHAR(10);  //修改一个字段的类型

 

alter table user CHANGE new1 new4 int; 
//修改一个字段的名称,此时一定要重新指定该字段的类型

 

#创建表 #create table stu(id int
primary key, name char(10), age int); #删除表 #drop table stu; #增加
#insert into stu(id, age, name) values(1, 20, 小明…

select stu_id,stu_classid,stu_score 
from student,studentclass,studentscore 
where (student .stu_id=studentscore. stu_id and
studentclass.stu_classid.studentscore.stu_classid) 
order by stu_score desc

网站地图xml地图