create database if not exists school default charset=utf8;
--使用數(shù)據(jù)庫
use school;
--創(chuàng)建表--創(chuàng)建數(shù)據(jù)庫
create database if not exists school default charset=utf8;
--使用數(shù)據(jù)庫
use school;
--創(chuàng)建表
create table studentinfo (
id int auto_increment primary key,
`name` varchar (20),
sex char,
age int ,
address varchar(50)
)default charset=utf8;
--插入數(shù)據(jù)
insert into studentinfo(`name` , sex , age , address) values ("張三豐" , "男" , 58,"武當(dāng)山"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ("張無忌" , "男" , 18,"日月神教"),
("周芷若" , "女" , 18 ,"峨嵋派"),
? ("趙敏" , "女" , 19 ,"蒙古"),
("小趙" , "女" , 20 ,"日月神教");
--模糊查詢
select * from studentinfo;
--完整查詢
select id , `name` , sex , age , address from studentinfo;
--條件查詢
select * from studentinfo where sex = "男" ;
-- 排序民珍，按照年齡進(jìn)行排序 asc 升序 desc 降序
select * from studentinfo order by age asc ;
-- 限制查詢結(jié)果的個(gè)數(shù)
select * from studentinfo order by age asc limit 2 ;
-- 復(fù)合
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;
--插入數(shù)據(jù)
insert into studentinfo(`name` , sex , age , address) values ("張三豐" , "男" , 58,"武當(dāng)山"),
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ("張無忌" , "男" , 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 ;
-- 排序叮雳，按照年齡進(jìn)行排序order by 排序? asc 升序 desc 降序
select * from studentinfo order by age asc ;
-- 限制查詢結(jié)果的個(gè)數(shù) 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 ;引用從
-- 復(fù)合
select * from studentinfo where sex = '女' order by age desc limit 2;
--別名 as? 且可以省略
select id as 學(xué)號，`name`as 名字,sex as性別挨摸，age as年齡，address as地址 from studentinfo;
select id 學(xué)號, `name` 名字, sex 性別? 岁歉，age 年齡得运，address? 地址 from studentinnfo;
--and or
select * from studentinfo where sex="女"and age >18;--兩個(gè)條件都要滿足
select * from studentinfo where sex="男"or age >18;--只要有一個(gè)條件滿足
-- 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;
