mysql数据库 学习（二）

测试、练习

-- 创建数据库
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;
-- 别名
select name,sex,age,score from table0 t;

select t.name,t.age,t.score from table0 t;

select t0.name,t1.name from table0 t0,table1 t1;

-- 创建表  table1
create table table1
(
	id int not null primary key auto_increment,
    name varchar(12),
    age int,
    sex int,
    score double(6,1)
);
-- 添加数据
insert into table1
(`name`,`age`,`sex`,`score`)
values
("Abraham",25,1,95),
("Amos",19,0,88),
("Alexandra",19,1,62),
("Diese",24,0,59),
("David",23,1,53),
("John",22,0,99);
-- 分页
select * from table1 limit 0,8;