创建学生表

创建 
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’,’Computer专门的学问’,’男’,’11’卡塔尔国 
insert into student values (‘王五’,’03’,’Computer专门的职业’,’男’,’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(*State of Qatar 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.ageState of Qatar 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 VARCHARAV4(20卡塔尔国 DEFAULT NULL;
//增添叁个字段,默以为空

alter table user add COLUMN new2 VARCHALAND(20卡塔尔 NOT NULL;
//扩展叁个字段,暗许无法为空

 

2.刨除二个字段

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

 

3.校正叁个字段

alter table user MODIFY new1 VARCHATiguan(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

发表评论

电子邮件地址不会被公开。 必填项已用*标注