name的值如果是字符串,那么值必须要加上单引号。
插入数据,如果表名后面没有指定字段,就需要给所有的字段都设置值。
如果前面写了字段,就根据字段设置对应的值就行。
insert into stu values(1,'凯',12)
insert into stu(age,name) values(10,'大乔')
更新语句:
update stu set age=0 where id=1
delete from stu where id=2
1.设计表-添加一个字段isdelete
0代表没删除,1代表删除。
isdelete相当于标记这条记录是否被删除,实际上数据没有被删除。
2.把所有的数据isdelete都改为0
3.要删除某一条数据时,更新它的isdelete为1
4.当要查询数据时,只查询isdelete为0的数据。
update students3 set isdelete=1 where id=1
select * from students3 where isdelete=0
先创建表:
create table students(
students varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
)
准备数据:
insert into students values
('001','王昭君','女','北京','20','1班','340223199001247654'),
('002','诸葛亮','女','北京','20','1班','340223199001247354'),
('003','张飞','女','上海','20','1班','340223199003247654'),
('004','白起','女','北京','20','1班','340223199000427654'),
('005','大乔','女','新疆','20','1班','340223199004247654'),
('006','孙尚香','女','北京','20','1班','340223199006247654'),
('007','百里玄策','女','北京','20','1班','340223199007247654'),
('008','小乔','女','北京','20','1班','null'),
('009','百里守约','女','北京','20','1班',''),
('010','妲己','女','北京','20','1班','340223199007247654'),
('011','李白','女','北京','20','1班','340223199005267754'),
('012','孙邴','女','北京','20','1班','340223199001297655')
查询所有字段,*代表查询出所有的列:
select * from students
显示自己想显示的列:
select name,sex,hometown from students
给字段起别名,用as,不用as都行。
查询不会影响表中的数据,仅仅显示的时候不一样。
select name as 姓名,sex as 女,hometown 家乡 from students
给表起别名:
给一个表起别名是没有意义的。
这个.是"的"的意思。
select s.name,s.sex,s.hometown from students as s
剔除重复的数据:
distinct
select distinct sex from students
多列,一整行记录和别人重复才被认为是重复的数据。
select distinct sex,class from students
如果完全一样,才是重复的数据。
select distinct * from students
条件查询:
select * from students where name='小乔'
select age from students where name='小乔'
select * from students where age<20
select * from students where hometown !='北京'
select * from students where hometown <>'北京'
-- select * from students where age<20 and sex='女'
-- select * from students where sex='女' or class='1班'
select * from students where not hometown='天津'
-- select * from students where name like '孙%'
-- select * from students where name like '孙__'
-- select * from students where name like '孙_'
-- 查询以乔结尾的学生
-- select * from students where name like '%乔'
-- 查询姓名包含白的学生
select * from students where name like '%白%'
-- 查询姓名为2个字的学生
-- select * from students where name like '__'
-- 查询姓百且年龄大于20的学生
-- select * from students where name like '百%' and age>20
-- select * from students where studentno like '%1'
between... and ...表示在一个连续的范围内
例如:between 18 and 20 相当于大于等于18,小于等于20
-- select * from students where hometown='北京' or hometown='上海' or hometown='广东'
-- select * from students where hometown in ('北京','上海','广东')
-- select * from students where age>=18 and age<=20
-- select * from students where age between 20 and 10 这个是age既大于等于20又小于等于18 ,这个不存在的哦
-- between and 小的值在前面,大的值在后面
-- 查询年龄在18或19或22的女生
-- select * from students where age in(18,19,22) and sex='女'
select * from students where not age between 20 and 25
null是空。
交卷子,交白卷是空字符串,把卷子扔了就是null。
-- 查询没有填写身份证的学生
-- select * from students where card is null 为null
-- select * from students where card='' 为空
-- 查询填写了身份证的学生
-- select * from students where not card is null
-- 插入一条数据,给字段没有填写东西就是null
-- insert into students(studentno) values(20)
-- insert into students(studentno,name) values(21,null) 为null
insert into students(studentno,name) values(22,'') 为空