MySQL对表的操作及单表数据操作

一 对表的操作

1.建立一个student表

CREATE TABLE student
(
id int(11) NOT NULL,
name varchar(255)NOT NULL,
age int(11)NOT NULL,
department varchar( 255) NOT NULL,
PRIMARY KEY(id)
)

2.增加一个字段grade

alter table student add grade varchar(4);

3.删除一个字段grade

alter table student drop grade;

4.修改一个字段（department改为grade）

alter table student change department grade varchar(5);

5.删除表student

drop table student;

6.建表的同时设初始值

CREATE TABLE stu 
    (  
    id int(11) NOT NULL,  
    name varchar(255)not null default 'ww',  
    age int(11)NOT NULL,  
    department varchar( 255) NOT NULL,  
    PRIMARY KEY(id)  
    )  

二 对数据的操作

1.插入记录

insert into student  values(1,'萌萌',21,'计算机科学与技术');
insert into student (id,name,age,department) values(2,'思思',23,'物理系');

2.修改

update student set name='灵思' where id=2;

3.查询

表结构：

（1）普通查询

select * from student where id=3;

结果：

（2）排序查询

①默认升序

select name,age from student order by age;

结果：

②desc降序

select name,age from student order by age desc;

结果：

（3）分组查询

select department,count(department) from student group by department;

结果：

（4）求和

select department,sum(age) from student where department='计算机科学与技术';

（5）求平均数

select avg(age) from student;

结果：

（6）求最大值（最小min）

select name,age from student where age=(select max(age) from student);

结果：

（7）查询个数

select count(department) from student;

结果：

（8）查询年龄在20-21之间的学生

①in

select * from student where age in (20,21);

②between  and

select * from student where age between 20 and 21;

结果：

4.删除记录

delete from student where id=3;