MySQL学习笔记三

1.插入记录insert

第一种方式

insert [into] tbl_name [(col_name,…)] {values|value} ({expr | default},…),(…),…

案例：

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

insert into users values (null,'zhuheng','123',25,1);

insert into users values (default,'xiaoyang','345',43,1);

insert into users values (default,'benben','343',34,1),(null,'haha','234',54,0);

第二种方式

insert [into] tbl_name set col_name={expr | default},…

说明：与第一种方式的区别在于，此方式可以使用子查询

案例：

 insert into users set username='ben',password='456';

第三种方式

insert [into] tbl_name [(col_name,…)] select …

说明：此方法可以将指定查询结果插入到数据表中

案例：

create table test(
id tinyint unsigned primary key auto_increment,
username varchar(20)
);

 insert test(username) select username from users where age>30;

2.update

update [low_priority] [ignore] table_reference set col_name1={expr1|default} [,col_name2={expr2|default}]… [where where_condition]

案例：

update users set age=age+5;

update users set age=age-id,sex=0;

3.delete

delete from tbl_name [where where_condition]

案例：

delete from users where id=5;

4.查询表达式解析

查找记录

select select_expr [,select_expr…]

[

   from table_references

   [where where_condition]

   [group by {col_name | position} [asc | desc],…]

   [having where_condition]

   [order by {col_name | expr | position} [asc | desc],…]

   [limit {[offset,] row_count | row_count offset offset}]

]

查询表达式可以使用[as] alias_name为其赋予别名

别名可用于group by,order by或having子句

案例：

 select id as userId,username as uname from users;

where语句进行条件查询

group by语句分组

[group by {col_name | position} [asc | desc],…]

案例：

select sex from users group by sex;

分组条件having

select sex from users group by sex having count(id)>2;

对结果进行排序的order by

limit语句限制查询数量