MySQL操作记录

创建一张数据表

create table users( id smallint unsigned primary key auto_increment, username varchar(20) not null, password varchar(32) not null, age tinyint unsigned not null default 10, sex Boolean );

插入单值

insert users values(NULL,'tom','123',25,1); insert users values(DEFAULT,'john','456',25,1); 用数学表达式也可以插入

插入多个值

insert users values(DEFAULT,'john','456',25,1),(NULL,'tom','123',25,1);

INSERT-SET方法可以使用子查询

INSERT users set username = 'Ben',password = '456';

UPDATE语句 更新记录 单表更新

update users set age = age + 5;//更新所有的列 update users set age = age - id,sex=0;//特定的修改 update users set age = age + 10 where id % 2 =0;//利用where语句

单表删除

delete from users where id = 6;

limit语句

select * from users limit 2;//返回两条 select * from users limit 2,2;//返回id为3，4的两条，前面为索引，后面为个数 insert test(username) select username from user where age >= 30;//写入指定的数据表

Select语句

select username,id from users; select username as tang ,id from users;//使用别名

group by关键词

select sex from users group by sex;

having 语句

select sex,age from users group by sex having age >35;

order by

order by id desc;//降序，由大到小 order by id,age desc;//降序，id优先