编写sql语句的位置
数据库下》查询》右键新建查询;
数据表的操作
创建表
语法:
create table 表名{
字段名 类型 约束,
字段名 类型 约束
...
}
注:单词之间有空格,多有的符号都是英文格式,约束必须写在类型后面。
例:
创建学生表,字段要求为:姓名(长度为10),年龄,身高(保留小数点2位),需要主键id(整数、无符号、字段递增)
create table students(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
);
tips:
创建表之后要注意刷新一下才会出现,表》右键》刷新
unsigned 无符号只能输入大于等于0得数
删除表
语法:
格式一
drop table 表名
格式二
drop table if exists 表名
注:格式一删除不存在的表会报错,格式二不会报错
例:删除 学生表
drop table students
drop table if exists students
数据操作-增删改查
简单查询
select * from 表名
例:查询所有学⽣数据
select * from students
添加数据
添加⼀⾏数据
格式⼀:所有字段设置值,值的顺序与表中字段的顺序对应
- 说明:主键列是⾃动增⻓,插⼊时需要占位,通常使⽤0或者 default 或者 null 来占位,插⼊成功后以实际数据为准
insert into 表名 values(...)
例:插⼊⼀个学⽣,设置所有字段的信息
insert into students values(0,'亚瑟',22,177.56)
格式⼆:部分字段设置值,值的顺序与给出的字段顺序对应
insert into 表名(字段1,...) values(值1,...)
例:插⼊⼀个学⽣,只设置姓名
insert into students(name) values('⽼夫⼦')
添加多⾏数据
⽅式⼀:写多条insert语句,语句之间⽤英⽂分号隔开
insert into students(name) values('⽼夫⼦2');
insert into students(name) values('⽼夫⼦3');
insert into students values(0,'亚瑟2',23,167.56)
⽅式⼆:写⼀条insert语句,设置多条数据,数据之间⽤英⽂逗号隔开
格式⼀:insert into 表名 values(...),(...)...
例:插⼊多个学⽣,设置所有字段的信息
insert into students values(0,'亚瑟3',23,167.56),(0,'亚瑟4',23,167.56)格式⼆:insert into 表名(列1,...) values(值1,...),(值1,...)...
例:插⼊多个学⽣,只设置姓名
insert into students(name) values('⽼夫⼦5'),('⽼夫⼦6')
修改
格式:update 表名 set 列1=值1,列2=值2... where 条件(如果没有写where条件,会改所有数据)
例:修改id为5的学⽣数据,姓名改为 狄仁杰,年龄改为 20
update students set name='狄仁杰',age=20 where id=5
删除
格式⼀:delete from 表名 where 条件
例:删除id为6的学⽣数据
delete from students where id=6
逻辑删除:对于重要的数据,不能轻易执⾏delete语句进⾏删除,⼀旦删除,数据⽆法恢复,这时可以进⾏逻辑删除。
1、给表添加字段,代表数据是否删除,⼀般起名isdelete,0代表未删除,1代表删除,默认值为0
2、当要删除某条数据时,只需要设置这条数据的isdelete字段为1
3、以后在查询数据时,只查询出isdelete为0的数据
例:
1、给学⽣表添加字段(isdelete),默认值为0,如果表中已经有数据,需要把所有数据的isdelete字段更新为0
update students set isdelete=0
2、删除id为1的学⽣ update students set isdelete=1 where id=1
3、查询未删除的数据 select * from students where isdelete=0
格式⼆:truncate table 表名(删除表的所有数据,保留表结构)
例:删除学⽣表的所有数据 truncate table students
格式三:drop table 表名(删除表,所有数据和表结构都删掉)
例:删除学⽣表
drop table students
Truncate和Delete、Drop的区别
1、Delete删除数据时,即使删除所有数据,其中的⾃增⻓字段不会从1开始
2、Truncate删除数据时,其中的⾃增⻓字段恢复从1开始
3、Drop是删除表,所有数据和表结构都删掉
总结
在速度上,drop > truncate > delete
如果想删除部分数据⽤delete,注意带上where⼦句
如果想删除表,⽤drop
如果想保留表⽽将所有数据删除,⾃增⻓字段恢复从1开始,⽤truncate
数据操作-查询
查询所有字段
语法:
select * from 表名
例:查询所有学生字段
select * from students
查询部分字段
语法:
select 字段1,字段2,... from 表名
例:查询学生的姓名、性别、年龄
select name,sex,age from students
起别名
给表起别名,在多表查询中经常使用
语法:
select 别名.字段1,别名.字段2,... from 表名 as 别名
例:给学生表起别名
select s.name,s.sex,s.age from students as s;
给字段起别名,这个别名出现在结果集中
语法:
select 字段1 as 别名1,字段2 as 别名2,... f rom students;
例:查询所有名称的姓名、性别、年龄,结果中的字段名显示为中文
select name as 姓名,sex as 性别,age as 年龄 from students;
去重
语法:
select distinct 字段1,... from 表名
例:查询所有学生的性别,不显示重复数据
select distinct sex from students;
注:distinct后可以写多可以字段,结果中一行记录与另一行记录一模一样才算重复
条件
使用where子句对标中的数据筛选,符合条件的数据会出现在结果集中
语法如下:
select 字段1,字段2... from 表名 where 条件;
例:select * from students where id = 1;
拓展
where后面支持多种运算,进行条件的处理
- 比较运算
- 逻辑运算
- 模糊查询
- 范围查询
- 空判断
1、比较运算
- 等于:=
- 大于:>
- 大于等于:>=
- 小于:<
- 小于等于:<=
- 不等于:!=或<>
例:
查询20岁以下的学生
select * from students where age < 20
查询家乡不在北京的学生
SELECT * FROM students WHERE hometown != "北京"
2、逻辑运算符
- and
- or
- not
例:
--查询年龄小于20的女同学
SELECT * from students where age < 20 AND sex = "女"
--查询非天津的学生
SELECT * FROM students where not hometown = "天津"
3、模糊查询
- 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 '%白%'
4、范围查询
- in表示在一个非连续的范围内
- between...and...在一个连续的范围内
ֺ--查询家乡是北京或上海或广东的学生
select * from students where hometown in("北京","上海","广东")
--查询年龄为18至20的学生
select * from students where age between 18 and 20
5、空判断
- 判空:is null
- 判非空:is not null
注:null与''不同
例:
--查询没有填写身份证的学生
select * from students where card is null
排序
语法:
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
- 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
- 默认按照列从小到大排列
- asc从小到大排序,即升序
- desc从大到小排序,即降序
例:
--查询所有学生信息,按年龄从小到大排序
select * from students order by age
聚合函数
count(*)表示计算总行数,括号中写星与列名,结果是相同的
--查询学生总数
select count(*)from students;
注:括号中放*或者放字段,count不会统计为null的数据
max(列)表示求此列的最大值
--查询女生的最大年龄
select max(age)from students where sex = '女';
min(列)表示求此列的最小值
--查询一班的最小年龄
SELECT MIN(age) FROM students WHERE class = '1班'
sun(列)表示求此列的和
--查询北京学生的年龄总和
select sum(age)from students where houmetown = '北京'
avg(列)表示求此列的平均值
--查询女生的平均年龄
select avg(age)from students where sex = '女'
分组
- 按照字段分组,此字段相同的数据会被放到一个组中
- 分组的目的是对每一组的数据进行统计(使用聚合函数)
- 语法:
sleelct 字段1,字段2,聚合...from 表名 group by 字段1,字段2,...
例:
--查询每个班各种性别的人数
--查询各个班级的平均年龄、最大年龄、最小年龄
分组后的数据筛选
- 语法:
select 字段1,字段2,聚合...from 表名
group by 字段1,字段2,字段3...
having 字段1,...聚合...
--查询男生的总人数
--查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
- having后面的条件运算符与where的相同
- 比较运算
- 逻辑运算
- 模糊查询
- 范围查询
- 空判断
- 对比where与having
where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
having是对group by的结果进行的筛选
having后面的条件可以用聚合函数,where后面不可以
获取部分行
当数据量过大时,在一页中查看数据查看数据是一件非常麻烦的事情
- 语法
select * from 表名
limit start,count
- 从start开始,获取count条数据
- start索引(起始位置)从0开始
--获取前3行学生信息
--查询第4到第6行学生信息
分页
每页显示m条数据,查出第n页的数据
select * from students limit (n-1)*m,m
求总页数的步骤:
- 查询总条数p1
- 使用p1除以m得p2
- 如果整除p2为总页数
- 如果不能整除p2+1为总页数