MySQL学习总结(2) — 数据表管理(增删改查)、使用DQL查询数据(SELECT)
1. 数据管理(增删改查)
-
数据库数据管理方法:
a.通过SQLyog等管理工具管理数据库数据
b. 通过DML语句管理数据库数据。
-
DML语言:
数据库操作语言,用于操作数据库对象中所包含的数据。
一般对数据库的主要操作包括:查询数据库中的信息、向数据库插入新的信息、从数据库删除信息以及修改数据库中的某些信息等。
1.1 添加数据
-
INSERT命令,语法:
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES(值1,值2,值3)
注意:字段或值之间用英文逗号隔开,可同时插入多条数据,values后用逗号隔开。
INSERT INTO student(`name`) VALUES ('zhangsan'); INSERT INTO student(`pwd`) VALUES ('784903'); INSERT INTO student(`sex`) VALUES ('男'); # 一次插入多条数据 INSERT INTO student(`name`) VALUES ('zhangsan'),('lisi');
1.2. 修改数据
-
UPDATE命令,语法:
UPDATE 表名 setcolumn_name = value [,column_name2=value, ...] [WHERE condition];
其中,column_name 为要更改的数据列,value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果,condition 为筛选条件 , 如不指定则修改该表的所有列数据
-
WHERE条件语句:
有条件的从表中筛选数据。
1. 3 筛选数据
-
DELETE命令,语法:
DELETE FROM 表名 [WHERE condition];
condition为筛选条件,如不指定则删除该表的所有列数据。
-
TRUNCATE命令,语法:
TRUNCATE [TABLE] table_name;
用于完全清空表数据,但表结构、索引、约束等不变。
-
DELETE命令和TRUNCATE命令的区别:
相同:都能删除数据 , 不删除表结构 ,
不同:1)TRUNCATE速度更快;2)使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器;3)使用TRUNCATE不会对事务有影响。
-
案例:
创建一个测试表,并使用DELETE命令和TRUNCATE命令删除数据。
# 创建一个测试表 CREATE TABLE `test` ( `id` INT(4) NOT NULL AUTO_INCREMENT, `coll` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 # 插入数据 INSERT INTO test(coll) VALUES('row1'),('row2'),('row3'); # 删除表数据(不带where条件的delete) DELETE FROM test; # 删除表数据(truncate) TRUNCATE TABLE test;
执行步骤:
1.先执行创建表的语句,然后执行插入数据语句后结果。
[外链图片转存失败(img-drd0ouzH-1563805376333)(E:\JAVA\2019-JAVASE-课件\20190629-JAVA-资料\4.png)]
2.执行DELETE 命令删除数据
图5
3.执行INSERT命令重新插入数据
图6
结论:如不指定Where,DELETE 命令则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志。
4.执行TRUNCATE命令删除数据
图5
5.执行INSERT命令重新插入数据
图4
结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志。
2. 使用DQL查询数据
2.1 DQL语言
DQL(Data Query Language 数据查询语言):可用来查询数据库数据,进行简单的单表查询或者多表的复杂查询和嵌套查询。
- 查询数据库数据 , 如SELECT语句
- 简单的单表查询或多表的复杂查询和嵌套查询
- 是数据库语言中最核心,最重要的语句
- 使用频率最高的语句
2.2 SELECT语句
-
SELECT语法:
SELECT [ALL | DISTINCT] {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} FROM table_name [as table_alias] [left | right | inner join table_name2] #联合查询 [WHERE ...] #指定结果需满足的条件 [GROUP BY ...] #指定结果按照哪几个字段来分组 [HAVING] #过滤分组的记录必须满足的次要条件 [ORDER BY ...] #指定查询记录按一个或多个条件排序 [LIMIT {[offset,]row_count | row_countOFFSET offset}]; # 指定查询的记录从哪条至哪条 #其中[ ]为可选,{}为必选 * 通配符,查询表中所有的数据列结果
总结:
#1.[ALL | DISTINCT] 全部/去重复 【+】 #2.选择字段可以是表、列,通过 as 取别名 【+】 #3.从哪个表选择,通过 as 取别名 #4.连接查询 (左连接,右连接,内连接) 【+】 #5.where 条件句子 【+】 #6.GROUP BY 通过什么列分组,返回每个列的数量 #7.HAVING 次条件子句 #8.ORDER BY 通过什么列进行排序,生序ASC,降序DESE #9.Limit startRows,PageRows 【+】
【注意】:顺序不能错。
-
指定查询字段:
查询表结果时 , 可指定查询结果的数据列。
查询表中所有的数据列结果 , 采用 " * " 符号。如:
# 查询student表中的所有信息 select * from student; # 查询student表中的指定列(学号,姓名) select StudentNo,StudentName from student;
-
取别名 as:
AS关键字可以给数据列取一个新的别名,在联合查询中方便区别不同表的相同的列名
#查询student表中的指定列(学号,姓名),并将列名显示为学号,姓名 select StudentNo as '学号',StudentName as '姓名' from student;
-
去重复 distinct:
distinct可以将查询返回的记录结果中的重复记录去掉,即当返回的某些列的值相同时,只返回一条记录
# 查询studenNo并去除重复学号的学生 select distinct StudentNo from student;
-
条件匹配WHERE:
WHERE用于检索数据表中符合条件的记录。
搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假。
逻辑操作符 AND(可写为&&) OR(可写为||) NOT(可写为!)
# 查询考试成绩在95-100之间的学生学号和成绩 SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100; # 查询学号为1009的同学以外的其他学生的学号和成绩 SELECT studentno,studentresult FROM result WHERE NOT studentno=1009;
-
排序 ORDER BY:
- 升序 ASC
- 降序 DESC
#查询学员及所属的年级(学号,学生姓名,年级名) 按学号升序 select StudentNo,StudentName,GradeName from student as s inner join grade as g on s.GradeId=g.GradeID order by StudentNo ASC
-
分页 :
语法SELECT * FROM table LIMIT 起始页码,但也显示数量。
#查询学员及所属的年级(学号,学生姓名,年级名) 按学号升序,从第6条数据开始,显示十条查询结果。 select StudentNo,StudentName,GradeName from student as s inner join grade as g on s.GradeId=g.GradeID order by StudentNo ASC limit 6,10;
2.3 模糊查询:
-
between and
#查询科目8成绩在90分到100分之间的同学的学号 select StudentNo from result where SubjectNo=8 and StudentResult between 90 and 100;
-
like
需要结合通配符使用
# 查询所欲李姓同学的学号及姓名 SELECT studentno,studentname FROM student WHERE studentname LIKE '李%'; # 查询李姓且名字只有两个字的同学的学号及姓名 SELECT studentno,studentname FROM student WHERE studentname LIKE '李_'; # 查询李姓且名字只有三个字的同学的学号及姓名 SELECT studentno,studentname FROM student WHERE studentname LIKE '李_'; # 查询姓名中含有文字的同学的学号及姓名(文字出现的位置不限) SELECT studentno,studentname FROM student WHERE studentname LIKE '%文%';
-
in
# 查询address为北京,南京,河南洛阳的学生 SELECT studentno,studentname,address FROM student WHERE address IN ('北京','南京','河南洛阳'); #查询科目8成绩在90分到100分之间的同学的姓名(使用嵌套连接查询) select StudentName from student where StudentNo in(select StudentNo from result where SubjectNo=8 and StudentResult between 90 and 100);
-
null
#查询出生日期没有填写的同学(注意,不能用=null判断,要使用is null判断) SELECT studentname FROM student WHERE BornDate IS NULL; #查询出生日期填写了的同学 SELECT studentname FROM student WHERE BornDate IS NOT NULL;
o=8 and StudentResult between 90 and 100);
- **null**
#查询出生日期没有填写的同学(注意,不能用=null判断,要使用is null判断)
SELECT studentname FROM student
WHERE BornDate IS NULL;
#查询出生日期填写了的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
【注意】**空字符串''并不等于NUll**