mysql针对表格数据的增删改查

表

CREATE TABLE grand(
	gid int(4),
	gname VARCHAR(10),
	gage int(2)
)

1 增

-- 插入所有字段(列)的数据,顺序必须和字段的顺序相同
insert into grand values(1,"大一",10); 

-- 插入部分字段的数据
insert into grand (gid) values (1);

-- 插入部分字段
insert into grand (gid,gname) values (1,"大二");

-- 一次性插入多条数据
INSERT into grand (gid,gname) VALUE (2,"大2"),(3,"大3"),(4,"大4"),(5,"大5");

-- 
INSERT into grand(gname,other) VALUES ("大2","大二学生");

2 删

-- 删除全部数据
DELETE from grand

-- 删除部分
DELETE from grand where gid=6
-- 删除小于9和大于9.。。。除了9其他都删除
DELETE from grand where gid < 9 or gid > 9

-- TRUNCATE 清空表数据
TRUNCATE TABLE grand

注意：DELETE和TRUNCATE的区别
1.DELETE一条一条删除（效率比较低），TRUNCATE一次性清空（效率比较高）
2.删除的记录可以保存到日志文件中（万一数据误操作可以恢复），TRUNCATE不能恢复数据（类似格式化）
3.DELETE删除后ID仍然自动增长，TRUNCATE清空后ID从1开始

3 改

-- 修改全部gname字段(修改多个字段)
update grand set gname="年段" , other="其他"

-- 修改学时字段，将原本的学时+10
UPDATE grand set xueshi=xueshi+10


-- 字段拼接，gname字段的内容为“年级”+gid+“班级”拼接关键字 CONCAT(str1,str2,...),可以拼接多个字符串
update grand set gname=CONCAT("年级",gid,"班级",xueshi);--会替换原先的内容

-- 修改gname="测试1"  条件 gid=1
update grand set gname="测试1"  where gid=1

-- 修改 gname="测试其他"   条件   gid！=1   gid<>1
UPDATE grand set gname="测试其他" where gid!=1
UPDATE grand set gname="测试不等于" where gid<>1

-- BETWEEN  AND 修改2-4的名称为between
update grand SET gname="BETWEEN" where gid>=2  and gid<=4
update grand SET gname="BETWEEN_and" where  gid BETWEEN 2 and 4

-- or 或
update grand set gname="or"  where gid<2  or  gid>4

4 查

4.1 查一

-- 查询全部数据   *全部字段  from来自于哪张表
select * from t_student

-- 查询部分字段
select uname,kecheng,chengji from t_student

-- 字段取别名 as..注意：as可以省略
select uname as "姓名",kecheng as "课程",chengji as "成绩" from t_student 
select uname "姓名",kecheng "课程",chengji  "成绩" from t_student 

-- DISTINCT去除重复,后面可以跟随1-N个字段，如果写了多个字段会去除多个自动相同的内容
-- 查询姓名会显示所有姓名字段。重复
select uname from t_student

-- 姓名去除重复（重复的只显示一条数据）
select DISTINCT uname from t_student

-- 有学生补考，只是想知道考了几门课程
select uname,kecheng from t_student
select DISTINCT uname,kecheng from t_student



查询课程表（subject）的所有记录，返回如图所示
要求：
返回字段名称使用别称
返回课程名称 (SujectName) 总课时(SubjectHour)
返回10天上完课程的均课时（ClassHour/10）
select * from SUBJECT
select SujectName "课程名称",SubjectHour  "总课时" , SubjectHour/10  "均课时/天" from SUBJECT

-- NULL
-- 表中有一条记录uname为空
select * from t_student where uname=null(错)
select * from t_student where uname is null  (对)
select * from t_student where uname is not null   (对)

-- IN ,在什么里面
SELECT * from t_student where uid=1 or uid=2
SELECT * from t_student where uid in (1,2,3,4)  -- uid值=1或=2或=3或=4.。。。
SELECT * from t_student where uid not in (1,2,3,4)

-- like 
-- 与“%”一起使用，表示匹配0或任意多个字符
-- 与“_”一起使用，表示匹配单个字符
select * from t_student where uname like "张%"   -- %只要是张开头，不管后面有几个字

-- 查询姓名中有张 
select * from t_student where uname like "%张%"  -- 只要姓名中有张，不管前面后面有几个字

-- 查询姓是张。。名字只有一个字
select * from t_student where uname like "张_"  -- 只要是张开头,后面只有一个字

4.2 查二

book 图书表(bid图书编号，bname书名,jiage价格，type类型)

bid	bname	jiage	type
1	西游记	50	1
2	西东游记	60	1
3	南游记	80	1
4	北游记	20	1
5	java	40	2
7	C++	55	2
7	mysql	66	2

booktype 类型（tid类型编号，tname类型名称）

tid	tname
1	小说
2	编程

SELECT * from book

SELECT * from booktype

-- 简单
select * from book,booktype where book.typeid=booktype.tid;

-- 内连接 select * from 表名1 inner join 表名2 on 条件
select * from book INNER JOIN booktype on book.typeid=booktype.tid;


-- 外链接(包括左连接、右连接)
-- 左连接，以左表为主表，只要左表有数据就可
select * from book left JOIN booktype on book.typeid=booktype.tid;

-- 右连接，，以右表为主表，只要右表有数据就可以
select * from book RIGHT JOIN booktype on book.typeid=booktype.tid;

********************************************* 排序和子查询 ************************************************ 
排序：
select * from t_student
-- 升序 asc 默认
select * from t_student ORDER BY chengji asc 
select * from t_student ORDER BY uid asc 
select * from t_student ORDER BY uid
-- 降序desc 
select * from t_student ORDER BY uid DESC 
select * from t_student ORDER BY chengji DESC 
-- limit分页MySQL的关键字  LIMIT 2一个数字（返回几条）    LIMIT m,n --(m偏移量,n返回几条)
SELECT * from t_student LIMIT 3  -- 获取前三条记录
SELECT * from t_student LIMIT 0,3  -- 获取前三条记录,偏移量0
SELECT * from t_student LIMIT 2,3 --   354 (偏移2)
SELECT * from t_student LIMIT 4,3  --(偏移4，不够3条则实际几条取几条)

子查询：
-- 子查询 在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句
-- 查询课程为《java》且分数不小于80分的学生的学号和姓名
学生表（sid name）
成绩表（cid cname fenshu  sid）
SELECT * from student2;
SELECT * from chengji2;

-- 查询成绩表条件（Java 分数>=80）
select sid from chengji2 where cname="java" and chengji>=80  -- 内表，临时数据表

-- 已知sid查询姓名
select  sid,sname from student2 where sid=1 or sid=3

-- 整合步骤
select a.sid,a.sname from student2 as a ,

(select sid from chengji2 where cname="java" and chengji>=80) as b  

where  a.sid=b.sid

********************************************* 统计函数和分组 ************************************************ 

统计函数:

-- count 查询某字段的数据总条数
select count(*) from t_student  -- *效率最低
select count(uid) 总数 from t_student
-- sum 查询某字段的数据的和
select sum(chengji) from t_student
-- avg 查询某字段的数据的平均值
select avg(chengji) from t_student
-- max 查询某字段的数据的最大值
select max(chengji) from t_student
-- min 查询某字段的数据的最小值
select min(chengji) from t_student

-- GROUP BY kecheng根据课程分组
select * from t_student GROUP BY kecheng

-- select 分组的字段名，统计函数(count sum avg ...)

select kecheng from t_student GROUP BY kecheng 
select kecheng "课程",max(chengji) "最高分" ,min(chengji) "最低分",avg(chengji) "平均分",sum(chengji) "总分" from t_student GROUP BY kecheng 

-- 除了分组以外，平均分大于60的才显示
select kecheng "课程",avg(chengji) "平均分" from t_student  GROUP BY kecheng 

select kecheng "课程",avg(chengji) "平均分" from t_student where avg(chengji)>60 GROUP BY kecheng  -- (错)先分组之后才能计算平均分>60

select kecheng "课程",avg(chengji) "平均分" from t_student  GROUP BY kecheng where avg(chengji)>60  -- （错）格式错误

-- 只要是先分组后有条件的搭配 having 

select kecheng "课程",avg(chengji) "平均分" from t_student  GROUP BY kecheng HAVING avg(chengji)>60  (对)

select kecheng "课程",avg(chengji) "平均分" from t_student where gender="男" GROUP BY kecheng HAVING avg(chengji)>60