基礎(chǔ)MySQL語(yǔ)句
來(lái)源：程序員人生 發(fā)布時(shí)間：2015-01-28 08:18:50 閱讀次數(shù)：3327次
#創(chuàng)建表
#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';
#查詢(xún)
#select * from stu;
#select id, age from stu;
#select id, age, name from stu where id>2 and id<5;
基礎(chǔ)MySQL語(yǔ)句
#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;#設(shè)外鍵
#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,'小1','男',30);
#insert into stu(Name,Sex,age) values('小2','男',31);
#insert into stu(id,Name,Sex,age) values(15,'小3','男',31);
#insert into stu(id,Name,Sex,age) values(17,'小4','女',33)
#insert into stu(Name,Sex,age) values('?、?,'男',33);
#insert into stu(Name,Sex,age) values('小5','女',20)
#insert into stu(Name,age,Sex) values('小7',20,'女')
#delete from stu where id=15
#delete from stu where name='小1' and sex='男'
#delete from stu where name='小1' and sex='男'
#delete from stu where name='小7' 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='小7',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 性他人數(shù) from stu group by sex
#select sex,count(*) as 性他人數(shù) from stu where id not in(19,30) group by sex
select * into newstu from stu where id>20====>此句有疑問(wèn)
MySQL不辨別大小寫(xiě)問(wèn)題
- delete from stu;====>清空表的記錄
2、查詢(xún)：升序：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;====>統(tǒng)計(jì)出不同性別各有多少人
select sex from stu group by sex;====>統(tǒng)計(jì)有多少種性別
select sex, count(*) as 性他人數(shù) from stu group by sex;====>統(tǒng)計(jì)出兩個(gè)字段，表示不同性別各有多少人
select sex, avg(stu.age) from stu group by sex;====>統(tǒng)計(jì)出不同性別的平均年齡
select sex, max(stu.age) from stu group by sex;====>統(tǒng)計(jì)出不同性別各自最大的年齡
- alter table class add column headteacher char(10);====>添加表中字段 alter table 表名 add 列名 varchar（20）
- alter table id_name drop column age,drop column address;====>刪除表中的兩個(gè)字段
1.增加1個(gè)字段
alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL; //增加1個(gè)字段，默許為空
alter table user add COLUMN new2 VARCHAR(20) NOT NULL; //增加1個(gè)字段，默許不能為空
2.刪除1個(gè)字段
alter table user DROP COLUMN new2; //刪除1個(gè)字段
3.修改1個(gè)字段
alter table user MODIFY new1 VARCHAR(10); //修改1個(gè)字段的類(lèi)型
alter table user CHANGE new1 new4 int; //修改1個(gè)字段的名稱(chēng)，此時(shí)1定要重新指定該字段的類(lèi)型
生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
