电子商务网站建设定义,东莞建设网站培训,邵阳网站建设的话术,支持api网站开发2019独角兽企业重金招聘Python工程师标准 -----创建表----
----------表格头英文换中文显示select name as 名字,age 年龄,class from student
-----if not exists判断表存在否--字符串用char也行---
--如果用自增长#xff0c;只能用包装类型integer,不能用int… 2019独角兽企业重金招聘Python工程师标准 -----创建表----
----------表格头英文换中文显示select name as 名字,age 年龄,class from student
-----if not exists判断表存在否--字符串用char也行---
--如果用自增长只能用包装类型integer,不能用int--
create table if not exists student(id Integer primary key autoincrement not null, name String not null, sex String not null, age int not null, hight real , class String
)
------用逗号分隔最后一个字段不用写-----
---删除表------
drop table student
------插入一条数据------
insert into student (name,sex,age,hight,class) values(张三,男,21,178,cc111)
------插入多条数据------
insert into student (name,sex,age,hight,class) values(李四,男,23,170,cc111),(王五,女,20,173,cc111)
,(赵六,男,22,179,cc112), (朱七,女,24,171,cc112)
------查询所有数据------
select * from student
-------------------
select * from student where age22
------修改数据------
---- update 表名 set 字段 -加条件 where ---
update student set hightnull where id5
-----班级为cc111,男hight不为null,则每个高度都增长1
update student set highthight1 where classcc111 and sex女 and hight not null
---------------凡是cc111班年龄减1----update student set ageage-1 where classcc111
-------------凡是cc111班或者性别为女的则高度1----------------------update student set highthight1 where classcc111 or sex女
--------修改名字的第一个字为王的年龄减1岁
update student set age age-1 where name like 王%
-----------修改名字为张三或者为李四的班级为cc112----------------
update student set classcc112 where name in(张三,李四)
----删除所有
delete from student
-------删除名字含朱的
delete from student where name like %朱%
--查询所有
select * from student
---女的
select * from student where sex女
------查询cc111班的人的名字
select name from student where classcc111
--查询大于21小于23的人
select * from student where age21 and age23
---查询名字含朱的
select * from student where name like %朱%
---------查询cc111班女生并且按年龄从小到大排序- order by,降序 desc默认就是升序-
select * from student where sex女 and classcc111 order by age desc
-------------查询所有女生并且按年龄从大到小排序-,只显示前3个----
----limit 偏移量0表示第一个显示的个数-----
select * from student where sex女 order by age desc limit 0,2
---查询出cc112班的总人数--------
select count(*) from student where classcc112
------查询出cc112班的所有人的年龄总和---------------
select sum(age) from student where classcc112
-------查询出cc111班的平均年龄-------------
select avg(age) from student where classcc112
-----查询所有的班级的男性人数并且按班级进行分组 group by ---------
select class,count(*) from student where sex男 group by class
-----查询cc112班年龄最大的学员-----------
select max(age),name from student where classcc112
-----查询班上的所有学员按班级进行分组再进行降序并且只列出总人数大于等于1个班级-----------
select class,count(*) from student
group by class having count(*)1 order by count(*) desc 转载于:https://my.oschina.net/u/2541146/blog/551994
