MySQL数据库语句

一、增删改查语句

-- value是添加一条数据，而values可以一次
-- 添加多条数据
insert into student
VALUE(6,"李四四",22,"男","计科")
-- 添加数据时候要注意主键是不可以重复的
-- 要保存中文，最好要把varchar类型改成utf8

-- 不按照默认顺序怎么往数据库中添加数据
-- 按照我们写的列表顺序给其赋值
insert into student(id,sex,age,`name`,major)
value(5,"女",18,"公孙","软件")
-- 数据库的查询语句
-- *表示所有，我要查询此数据库中所有的数据，所有指的是所有
-- from进行查询，form表单，两者不要混了
select * from student
select `name`,age,sex from student

-- 条件查询，where后方跟的是条件，我们用ID进行查询
select * from student where id = 1
-- 条件查询只要是满足条件的数据都会正常显示出来，所以
-- 查询结果时零条或者是多条
select * from student where major = "软件"

-- 修改语句
update student set age=25 where id = 1
-- 修改此人的多个属性值
update student set age=25,major="物联" where id = 1

-- 删除语句
delete from student where id=1
-- 只有查询会让我们看到结果，其他三个语句都是告诉我们
-- 语句执行成功，不会有结果返回给我们的

-- 高级查询
-- 排序 asc表示升序排序 desc是降序排序
select * from student order by age asc,id desc
SELECT * from student where major="软件" order by age asc

-- 重命名
SELECT `name` as 姓名,age as 年龄,sex as 性别 from student
SELECT * FROM student WHERE student.id = 2
SELECT * from student as s where s.id = 2

INSERT into student 
values(7,"李四四",22,"男","计科"),
(8,"李四四",22,"男","计科"),
(9,"李四四",22,"男","计科"),
(10,"李四四",22,"男","计科"),
(11,"李四四",22,"男","计科"),
(12,"李四四",22,"男","计科"),
(13,"李四四",22,"男","计科"),
(14,"李四四",22,"男","计科")

-- limit 限制查询行数
-- 查询中只显示8条数据
select * from student limit 8
-- 两个数据的limit，第一个数据是从哪条开始显示
-- 这个值是从0开始，0表示的是第一条数据
-- 表示显示的条数
select * from student limit 2,8


-- like 模糊查询的关键字_表示一个字符，%表示多个字符
-- 等号不能进行模糊查询
SELECT * from student where major like "物__"
SELECT * from student where major like "物%"
SELECT * from student where major like "%物__"

-- BETWEEN AND 区间内符合要求的数据都会显示出来，闭区间
SELECT * from student where age between 18 and 23

-- IN 模糊查询,查询范围是我们自己指定的，就是后面小括号中的
-- 值，里面有的是范围，没有就不存在也不显示
SELECT * from student where age in(15,18,19,23)

-- null查询数据库中某个属性值是否为空
select * from student where major is null


-- 聚合函数的应用
-- count查询表中一共有多少条数据
select count(*) from student
-- sum是用于求和
select sum(age) from student
-- avg进行求数据表中的平均年龄
select avg(age) from student
-- max求最大值
select max(age) from student
-- min求最小值
select min(age) from student

-- 分组查询
select * from student where age = 22
select * from student group by major
-- HAVING 分完组后，组中大于三条的数据的组显示出来
select * from student group by major HAVING count(*)>3

-- 子查询
select * from student where 
major=(select major from student where id = 3) 

select * from student where 
age=(select max(age) from student) 

-- 返回值为多个值的子查询怎么去写呢
-- 等号无法实现多个值的时候的子查询，但是我们有
-- in，他是可以进行多值查询的
select * from student WHERE
age in(select age from student)