create database itcast default character set utf8;
use itcast;
show create table XXX 显示创建表的语句
create table it_user_info(
ui_user_id bigint unsigned auto_increment comment '用户ID',
ui_name varchar(64) not null comment '用户名',
ui_passwd varchar(128) not null comment '密码',
ui_age int unsigned null comment '年龄',
ui_mobile char(11) not null comment '手机号',
ui_avatar varchar(128) null comment '头像',
ui_ctime datetime not null default current_timestamp comment '创建时间',
ui_utime datetime not null default current_timestamp on update current_timestamp comment '更新时间',
primary key (ui_user_id), #定义主键
unique (ui_mobile)
) engine=InnoDB default charset=utf8 comment '用户表';
create table it_house_info(
hi_house_id bigint unsigned auto_increment comment '房屋ID',
hi_user_id bigint unsigned not null comment '用户ID',
hi_name varchar(64) not null comment '房屋名',
hi_address varchar(256) not null comment '地址',
hi_price int unsigned not null comment '价格',
hi_ctime datetime not null default current_timestamp comment '创建时间',
hi_utime datetime not null default current_timestamp on update current_timestamp comment '更新时间',
primary key (hi_house_id), #定义主键
constraint foreign key (hi_user_id) references it_user_info(ui_user_id)
) engine=InnoDB default charset=utf8 comment '房屋信息表';
create table it_house_image(
hi_image_id bigint unsigned auto_increment comment '房屋图片ID',
hi_house_id bigint unsigned comment '房屋ID',
hi_url varchar(128) not null comment '图片url',
hi_ctime datetime not null default current_timestamp comment '创建时间',
hi_utime datetime not null default current_timestamp on update current_timestamp comment '更新时间',
primary key (hi_image_id), #定义主键
constraint foreign key (hi_house_id) references it_house_info(hi_house_id)
) engine=InnoDB default charset=utf8 comment '房屋图片';
执行 mybuntu01@ubuntu:~/Desktop$ mysql -uroot -p < db.sql 注意:CD 目录下执行
drop database XXX
drop table XXX
truncate table XXX
insert into it_user_info(ui_name,ui_passwd,ui_age,ui_mobile) values ("a","a",20,12345678911),("b","a",20,12345688911),("c","a",21,12340678911),("d","a",22,12345978911)
select * from it_user_info;
#查找年龄并去重
select distinct ui_age from it_user_info;
#查找年龄在20~23用户所有信息
select * from it_user_info where ui_age between 20 and 23;
#查找所有用户个数
select count(*)from it_user_info;
#查找所有用户个数 并取名字
select count(*) zlp from it_user_info;
#查找年龄在20~23用户所有信息 并按年龄排序
select * from it_user_info where ui_age between 20 and 23 order by -ui_age;
#查找年龄在20~23用户所有信息 并按年龄排序 只取后两条 注意:不加desc取前两条
select * from it_user_info where ui_age between 20 and 23 order by -ui_age desc limit 2;
#查找 不同年龄有多少人
select ui_age,count(ui_age) from it_user_info group by ui_age;
#当group by后没有字段值;前面必须聚合参加max(ui_name)
select max(ui_name),ui_age,count(ui_age) from it_user_info group by ui_age;
#向数据库表中插入字段 不为空加default int类型可不加 默认为0
alter table it_user_info add ui_area varchar(10) not null default 'a' comment '区域ID';
alter table it_user_info add ui_area_id int not null comment '区域ID';
#删除表里的字段
alter table it_user_info drop ui_area_id;
#更新某个字段值
update it_user_info set ui_area_id=1 where ui_user_id=1;
select ui_area_id,ui_age,count(*) from it_user_info group by ui_area_id,ui_age;
select * from it_user_info where ui_user_id=(select hi_user_id from it_house_info where hi_house_id=2);