mySQL

1创建一个表
CREATE TABLE person3(
firstName varchar(222),
lastName varchar(222),
age varchar(222)
)

2删除
drop table if exists `person3`

3 插入数据
insert into person3(firstName,lastName,age)
values('zhang','xiaolei','23')；
insert into person3(firstName,lastName,age)
values('jia','ningning','23')；
insert into person3(firstName,lastName,age)
values('zhang','lei','25')；
insert into person3(firstName,lastName,age)
values('zhao','mengyao','21')；
insert into person3(firstName,lastName,age)
values('du','ruihong','22')；
insert into person3(firstName,lastName,age)
values('zheng','zongjiao','17')；
insert into person3(firstName,lastName,age)
values('tian','zhen','18')；
insert into person3(firstName,lastName,age)
values('wu','shuaipeng','19')；
insert into person3(firstName,lastName,age)
values('li','cong','20')

4查询前两个数据
SELECT 'firstName','lastName' FROM `person3`

5查询id>2
SELECT * FROM `person3` WHERE age<18

6按年龄从小到大排序
SELECT * FROM `person3` order by age

7按年龄从大到小排序
SELECT * FROM `person3` order by age desc

8改变已设的年龄
UPDATE `person3` SET age='24'
WHERE firstName='zhang'and lastName='xiaolei'

9删除firstName为du的
DELETE FROM `person3` WHERE firstName='du'

10相同的合并distinct
SELECT distinct firstName FROM `person3` WHERE 1

11取18岁到23岁的between  and
SELECT * FROM `person3` WHERE age between 18 and 23

12查找不同年龄人的个数group  by
SELECT age as age,count(1) FROM `person3` group by age

13将firstName和lastName合并在一起concat
SELECT concat(firstName,lastName) as 名字,age as 年龄 FROM `person3` WHERE 1

 

老师的作业

1.查询person表所有成年人的信息。

SELECT * FROM `person3` WHERE age>18

 

2.查询person表中firstName以zh开头的所有人的 name（包括firstName+lastname）和年龄。

SELECT concat(firstName,lastName) as name,age as age FROM `person3` WHERE firstName like 'zh%'

3.查询person表所有人的年龄段和该年龄段的人数。

SELECT age as age FROM `person3` WHERE group by age

4.查询person 表中 年龄在16,17,18岁的人数的个数。

SELECT * FROM `person3` WHERE age in(16,17,18)

 

5.把姓zhang的人加1岁。

UPDATE `person3` SET age=age+1
 WHERE firstName='zhang'