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优先