-- 創(chuàng)建數(shù)據(jù)庫

create database if not   exists school default charset = utf8

;
-- 使用數(shù)據(jù)庫

use school;

-- 創(chuàng)建表格
create table if not exists studentinfo(
id int primary key auto_increment,
`name` varchar(20),
sex char,
age int,
address varchar(100)
);
-- 查看數(shù)據(jù)表
show tables;

-- 插入數(shù)據(jù)

insert into studentinfo(`name`,sex ,age ,address)values
("賈寶玉","男",18,"北平"),
("賈政","男",40,"浙江"),
("晴雯","女",20,"四川"),
("襲人","女",29,"貴州"),
("薛寶釵","女",19,"北平"),
("林黛玉","女",17,"浙江");    

-- 查看數(shù)據(jù) 通配符* 表示所有
select * from studentinfo;

-- 手動寫出字段 全量查詢
select id ,`name `,sex ,age ,address from studentinfo;

-- 只查詢指定的數(shù)據(jù)列
select id ,`name`,sex, from studentinfo;

-- 條件查詢 
select * from studentinfo where sex ='女';

-- 根據(jù)查詢結(jié)果排序 order by 
select * from studentinfo order by age desc ;
select * from studentinfo order by age desc , id desc ;

-- 顯示查詢結(jié)果的條數(shù)
select * from studentinfo limit 3 ;
-- 顯示查詢結(jié)果中從索引為5 開始的往后3條數(shù)據(jù)
select * from studentinfo limit 5, 3 ;

-- 復(fù)合
select * from studentinfo where sex = '女' order by age desc limit 3 ;

-- 別名 As as 可以省略
select id as 編號 , `name `as 姓名,sex as 性別,age  as 年齡 , address as 地址, from studentinfo;
select id 編號, `name ` 姓名 ,sex 性別,age 年齡, address 地址, from studentinfo;

-- 單條件查詢

select * from studentinfo where age <20;
select * from studentinfo where age is not null;

-- 多條件查詢
-- 不等于
select * from studentinfo where age!=20;
select * from studentinfo where age<>20;
select * from studentinfo where not  <age=20>;

-- 與 或
select * from studentinfo where age>20&& sex = '女';