程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 基礎MySQL語句

基礎MySQL語句

編輯:MySQL綜合教程

基礎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;  //修改一個字段的名稱,此時一定要重新指定該字段的類型

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved