mysql表数据增删改查、子查询

//操作数据表中的记录

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

--insert--
insert user2 values(null,'孙千','123',25,1);//自增序列可以用null，或default代替
insert user2 values(default,"孙千","123",25,1);
insert user2 set username="lisi",password="456";//insert set插入语法
insert test (username) select username from user2 order by id desc limit 2;//查询插入 
--update--
update user2 set age=age+5;//没有条件将更新所有的列
update user2 set age=age-id, sex=0;
update user2 set age=age+10 where id%2=0;
--delete--
delete from user2 where id=2;
--select--
select version(); select now();

--子句--
where 条件子句 后边可以跟列、函数、算术逻辑运算
group by 分组子句 后边跟列名、列位置
having 也是条件子句的一种，主要解决where无法与聚合函数一起使用的问题
order by 排序子句，asc升序，desc降序排序
limit 2 数量限制、查询数量限制

--子查询-------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tdb_goods(
    goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_name  VARCHAR(150) NOT NULL,
    goods_cate  VARCHAR(40)  NOT NULL,
    brand_name  VARCHAR(40)  NOT NULL,
    goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
    is_show     BOOLEAN NOT NULL DEFAULT 1,
    is_saleoff  BOOLEAN NOT NULL DEFAULT 0
  );
---由比较运算符印发的子查询---
select round(avg(goods_price),2) from tdb_goods;  查询某列平均值
select goods_id,goods_name,goods_price from tdb_goods where goods_price > (select round(avg(goods_price),2) from tdb_goods);//查询大于平均值的信息
//对于返回多列的子查询，可以加 any、some、all修饰符 
select goods_id,goods_name,goods_price from tdb_goods where goods_price > any (select goods_price from tdb_goods where goods_cate="超级本");
---此外，还有由in（not in）、exist（not exist）引发的子查询
----多表查询---
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
    cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    cate_name VARCHAR(40)
  );

insert tdb_goods_cate (cate_name) select goods_cate from tdb_goods group by goods_cate;//查询插入
update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;//参照类别表更新商品表
create table tdb_brand(
    brand_id SMALLINT unsigned primary KEY auto_increment,
    brand_name varchar(40) not null
)select brand_name from tdb_goods group by brand_name;//新建表时并赋值
update tdb_goods as g inner join tdb_brand as b on g.brand_name=b.brand_name set g.brand_name=b.brand_id;//更新商品名信息
alter table tdb_goods 
    change goods_cate cate_id smallint unsigned not null,
    change brand_name brand_id  smallint unsigned not null ;//修改表结构