测试、练习

-- 创建数据库
create database webapp1 charset utf8mb4;
-- 创建用户名、密码
create user'webapp1'@'localhost'identified by'webapp1';
-- 授权
grant all on webapp1.*to'webapp1'@'localhost';
-- 用用户名、密码登录
mysql -uwebapp1 -pwebapp1;
-- 创建表 
create table table0
(
	id int not null primary key auto_increment,
    name varchar(12),
    age int,
    sex int
);

-- 查看表结构
describe table0;
-- 添加数据 
insert into table0
(`id`,`name`,`age`,`sex`)
values
(001,"小向",19,1);
-- 添加多条数据 
insert into table0
(`name`,`age`,`sex`)
values
("小王",19,1),
("小张",19,1),
("小李",19,1),
("小四",19,1),
("小二",19,1),
("小三",19,1);
-- 查询表
select * from table0;
-- 删除一条数据
delete from table0 where id = 3;
-- 更新一条数据
update table0 set name="josn",sex=0 where  id = 7;
-- 添加表字段 
alter table table0 add column score double(6,1); 
-- 添加数据
insert into table0
(`name`,`age`,`sex`,`score`)
values
("小王",19,1,95),
("小张",19,1,88),
("小李",19,1,62),
("小四",19,1,59),
("小二",19,1,53),
("小三",19,1,99);
-- 比较运行符
select * from table0 where score>=60;
-- 范围运行符
select * from table0 where score between 60 and 80;
-- 模糊查询
select * from table0 where name like '%j%';
-- 逻辑运行符
select * from table0 where name ="json" or age >18 and sex = 0;
-- 排序
select * from table0 order by score desc;