数据库-001

数据库-基本操作

生成表结构
create table users(
user_id int,
name varchar(20),
age int,
city varchar(20),
state varchar(20),
monthly_active int,
primary key(user_id)
);

插入数据
insert into users
values
(1,‘jack’,19,‘广州’,‘清华大学’,22),
(2,‘Lucy’,20,‘长沙’,‘北京大学’,8),
(3,‘Tom’,14,‘北京’,‘河北大学’,17),
(4,‘Alice’,22,‘成都’,‘河南大学’,15),
(5,‘Zhang’,30,‘重庆’,‘武汉大学’,22);

为特定字段设置限制
create table users2(
user_id int,
name varchar(20) not null,
age int,
city varchar(20) unique,
state varchar(20),
monthly_active int,
primary key(user_id)
);

设置缺省值
create table users3(
user_id int,
name varchar(20),
age int,
city varchar(20) default ‘unknown’,
state varchar(20),
monthly_active int,
primary key(user_id)
);
insert into users3 (user_id,name,age,state,monthly_active)
values(1,‘Tom’,19,‘山东大学’,22);

自动设置主键
create table users4(
user_id int auto_increment,
name varchar(20),
age int,
city varchar(20) ,
state varchar(20),
monthly_active int,
primary key(user_id)
);
insert into users4 (name,age,city,state,monthly_active)
values(‘Tom’,19,‘广州’,‘山东大学’,22);