MySQL学习总结（2） --- 数据表管理（增删改查）、使用DQL查询数据（SELECT）

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**