数据库的增删改查

1、数据库:person

创建 create databases person;

查看 show databases;

指定使用  use person;

删除 drop databases person;

2、表:person

creat table person(

列名1 数据类型描述,

列名2 数据类型描述,

列名3 数据类型描述,

列名4 数据类型描述,

);

creat table person(

id int unsigned not null auto_increment primary key,

name char(10) not null,

age tinyint not null,

sex varchar(5) not null,

tel char(13) null default "-"

);

[unsigned 无符号, not null 非空, auto_increment 递增, primary key 主键]

显示表:show tables;

查看表结构:

describe person ;

或者  show columns from/ in person;

根据旧表创建新表

create table student like person;

create table student as select id,name from person;

复制整个表

create table student as select * from person;

[as 可以省略]

删除表:

drop table person;

重新命名表:

rename table person to person1

或者

alter table person rename person1

表名:person 

字段:id,name,age

1 张三 20

2 李四 22

3 王五 23

查询:

select id,name,age from person;

select * from person

select id from person where name="李四"

selext id from person where name like "%王%"  名字中带有王字的id

select name from person where age is null

select name from person where id>=2 order by desc    降序

select name from person where id>=2                  默认升序

select name from person where id>=2 order by asc    升序

select top 2 name from person              mysql显示name的前二行

select * from person  where rownum < 2    oracle 显示name的前二行

select name '成都' as 地址 from person    查询name列 添加地址列 值为成都

select name as 姓名 from person where id=2 选出表中id为2的行,显示name列,并将name列改为姓名显示

删除:delete from person where id=1;

修改:update person set name="刘德华" where id=2;

增加:insert into person(id,name,age) values(4,'刘能',24);

      或者不要列名,按表定义的顺序填写

      insert into person values(4,'刘能',24);

另外一张表:

student

insert into student(id,name,age) select id,name,age from person

删除整个表的数据

truncate table person

添加列:

表的最后添加

alter table  person add  sex char(3) not null; 

在指定的列后面添加:

alter table person add address cha(5) after age;

在表的最前面添加

alter table person add address cha(5) first;

修改列的列名:

alter table person change address address1 char(10) null default "-";

修改列的数字类型:

alter table person change name name char(20) not null;

删除列:

删除表person 中sex列;

alter table person drop sex;

求某列数据总数(不包含缺省)

select count(age) as totalcount from person;

统计表的总行数:

select conut(*) as totalcount from person;

求某列数据的总和;

select sum(id) as idsum from person;

求某列数据的平均值;

select avg(age) as ageavg from person;

求某列数据的最大最小值:

slect max(age) as maxage from person;

slect min(age) as minage from person;

posted @ 2018-10-06 13:12  科学小怪癖  阅读(139)  评论(0编辑  收藏  举报