create database if not exists school default charset=utf8;
--使用数据库
use school;
--创建表--创建数据库
create database if not exists school default charset=utf8;
--使用数据库
use school;
--创建表
create table studentinfo (
id int auto_increment primary key,
`name` varchar (20),
sex char,
age int ,
address varchar(50)
)default charset=utf8;
--插入数据
insert into studentinfo(`name` , sex , age , address) values ("张三丰" , "男" , 58,"武当山"),
("张无忌" , "男" , 18,"日月神教"),
("周芷若" , "女" , 18 ,"峨嵋派"),
("赵敏" , "女" , 19 ,"蒙古"),
("小赵" , "女" , 20 ,"日月神教");
--模糊查询
select * from studentinfo;
--完整查询
select id , `name` , sex , age , address from studentinfo;
--条件查询
select * from studentinfo where sex = "男" ;
-- 排序，按照年龄进行排序 asc 升序 desc 降序
select * from studentinfo order by age asc ;
-- 限制查询结果的个数
select * from studentinfo order by age asc limit 2 ;
-- 复合
select * from studentinfo where sex = '女' order by age desc limit 2;
create table studentinfo (
id int auto_increment primary key,
`name` varchar (20),
sex char,
age int ,
address varchar(50)
)default charset=utf8;
--插入数据
insert into studentinfo(`name` , sex , age , address) values ("张三丰" , "男" , 58,"武当山"),
("张无忌" , "男" , 18,"日月神教"),
("周芷若" , "女" , 18 ,"峨嵋派"),
("赵敏" , "女" , 19 ,"蒙古"),
("小赵" , "女" , 20 ,"日月神教");
--模糊查询
select * from studentinfo;
--完整查询
select id , `name` , sex , age , address from studentinfo;
--条件查询 where
select * from studentinfo where sex = "男" ;
select * from studentinfo where age>18 ;
-- 排序，按照年龄进行排序order by 排序 asc 升序 desc 降序
select * from studentinfo order by age asc ;
-- 限制查询结果的个数 limit
select * from studentinfo order by age asc l ;
select * from studentinfo order by age asc limit 2 ;
select * from studentinfo order by age asc limit 3,4 ;引用从
-- 复合
select * from studentinfo where sex = '女' order by age desc limit 2;
--别名 as 且可以省略
select id as 学号，`name`as 名字,sex as性别，age as年龄，address as地址 from studentinfo;
select id 学号, `name` 名字, sex 性别 ，age 年龄，address 地址 from studentinnfo;
--and or
select * from studentinfo where sex="女"and age >18;--两个条件都要满足
select * from studentinfo where sex="男"or age >18;--只要有一个条件满足
-- not 不等于
select * from studentinfo where age!=18;
select * from studentinfo where age<>=18;
select * from studentinfo where not (age=18);
--去重
select distinct name from studentinfo;
