深入浅出MySQL(4)-----常见数据操作

1. 添加记录

  1. 指定字段名称
INSERT [INTO] tbl_name [(col_name1,col_name2,...)] {VALUE|VALUES}(VALUES...);
  1. 不指定字段名称
INSERT tbl_name VALUE(value1,value2,...);

需要按照建表时的字段顺序给每一个字段赋值

  1. 列出指定字段
INSERT tbl_name(字段名称1,字段名称2,...) VALUES(值1,值2,...);
  1. INSERT ... SET的形式
INSERT tbl_name SET 字段名称1=值1,字段名称2=值2,...;
  1. INSERT ... SELECT
INSERT tbl_name[(字段名称...)] SELECT 字段名称1,字段名称2,... FROM tbl_name [WHERE 条件];
  1. 一次添加多条记录
INSERT tbl_name[(字段名称...)] VALUES(值1,值2,...),
(值1,值2,...),
(值1,值2,...)

2. 修改记录

UPDATE tbl_name SET 字段名称1=值1,字段名称2=值2 [WHERE 条件];

如果不加条件，整个表中的记录都会被更新

3. 删除记录

  1. 普通删除

    DELETE FROM tbl_name [WHERE 条件];

  2. 如果不添加条件，表中所有记录都会被删除

  3. DELETE清空数据表的时候不会重置AUTO_INCREMENT的值，但是可以通过ALTER语句将其置为1

    ALTER TABLE tbl_name AUTO_INCREMENT = 值x;

  4. 彻底清空数据表

    TRUNCATE [TABLE] tbl_name;
    • 清空表中所有记录
    • 会重置AUTO_INCREMENT的值

4.查询记录

SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]

  1. 查询表中所有记录

    SELECT * FROM tbl_name;

    *表示所有字段

  2. 指定字段的信息

    SELECT 字段名称1,字段名称2,... FROM tbl_name;

  3. 使用库名.表名的形式，不用打开此数据库

    SELECT 字段名称1,字段名称2,... FROM db_name.tbl_name;

  4. 给字段起别名，建议写上 AS

    SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;

  5. 给数据表起别名

    SELECT 字段名称1,字段名称2,... FROM tbl_name [AS] 别名;

  6. 表名.字段名的形式

    SELECT tbl_name.col_name,... FROM tbl_name;

  7. WHERE 条件

    会筛选出所有符合条件的记录

    • 比较运算符：

      > >= <= != <> <=>
注意 <=> 和 = 的区别
<=> 可以检测出NULL值

    • IS [NOT] NULL：检测值是否为NULL 或者 NOT NULL

    • 指定范围

      [NOT] BETWEEN ... AND

    • 指定集合

      [NOT] IN (值1,值2,...)

    • 逻辑运算符

      AND 逻辑与
OR  逻辑或

    • 匹配字符

      [NOT] LIKE
% 任意长度的字符串
_ 任意一个字符

      SELECT id,username,age FROM user1
WHERE username like '%in%';
id|username|age
1 |king    |23

      查询出叫张xx的人，使用'_'

      SELECT id,username,age FROM user1
WHERE username like '张__';
id|username|age
1 |张三丰   |23

  8. GROUP BY 分组

    1. 把值相同放到一个组中，最终查询出的结果只会显示组中一条记录

    2. 分组配合GROUP_CONCAT()查看组中某个字段的详细信息

      SELECT GROUP_CONCAT(username),age,sex FROM user1 GROUP BY sex;
GROUP_CONCAAT(username)|age|sex
吴亦凡,张三,张四,王五,赵六 |23 |男
章子怡,刘嘉玲,奶茶妹      |18 |女

    3. 配合聚合函数使用

      聚合函数名 统计记录总数
      COUNT() 统计记录总数
      COUNT(字段名称) 字段中为NULL的值，不会被统计进来
      COUNT（*） 会同时统计NULL值
      SUM（） 求和
      MAX（） 求最大值
      MIN（） 求最小值
      AVG（） 求平均值

    4. 配合WITH ROLLUP 关键词使用：会在记录末尾添加一条记录，是上面所有记录的总和

    5. HAVING字句对分组结果进行二次筛选（WHERE是一次筛选）

  9. ORDER BY 排序：ORDER BY 字段名称 ASC|DESC / ORDER BY rand() 随机排序

    按照多个字段排序
SELECT id,username,age
FROM user1
ORDER BY age ASC, id ASC;

  10. LIMIT 限制结果集合显示条数

    1. LIMIT值：显示结果集的前几条记录
    2. LIMIT offset,row_count：从offset开始，显示几条记录，offset从0开始。

5. 多表查询

  1. 笛卡尔积的形式

    表1：4条 / 表2：6条 / total：24条

  2. 内连接的形式：用的比较多

    • 相当于两个集合取交集，只取都符合条件的

    • 查询两个表中符合连接条件的记录

      SELECT 字段名称1,... FROM tbl_name1
INNER JOIN tbl_name2
ON 条件

  3. 外连接

    • 左外连接：以左表为主

      SELECT 字段名称1,... FROM tbl_name1
LEFT [OUTER] JOIN tbl_name2
ON 条件;

      先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替

    • 右外连接：以右表为主

      SELECT 字段名称1,... FROM tbl_name1
RIGHT [OUTER] JOIN tbl_name2
ON 条件;

      先显示右表中的全部记录，再去左表中查询复合条件的记录，不符合的以NULL代替

    • 全外连接：full outer join 全部都取出来

6. 外键约束

只有InnoDB存储引擎支持外键

  1. 创建外键

  • 建表时候指定外键

    [CONSTRAINT 外键名称]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
    • 子表的外键字段和主表的外键字段类型要相似;
      • 如果是数值型要求一致,并且无符号也要一致
      • 如果是字符型，要求类型一致，长度可以不同
    • 如果外键字段没有创建索引，MySQL会自动帮我们添加索引
    • 子表的外键关联的必须是父表的主键
    • 外键约束的参照操作
      1. CASCADE：从附表删除或更新，子表也跟着删除或者更新，级联的操作
      2. SET NULL：从附表删除或者更新记录，并且设置子表的外键列为NULL
      3. NO ACTION | RESTRICT：拒绝对父表做更新或者删除操作

  • 动态添加外键

    • 动态添加外键

      ALTER TABLE tbl_name
ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
      • 动态添加外键之前表中的记录一定都是合法的记录，没有脏值，否则外键添加不成功

    • 动态删除外键

      ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name;

7. 特殊形式的查询

  1. 子查询

    • 通过SELECT字段

      SELECT 字段名称 FROM tbl_name WHERE col_name = (SELECT col_name FROM tbl_name);

    • 内层语句查询的结果可以作为外层语句查询的条件

    • 由IN引发的子查询

      SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);

    • 由比较运算符引出子查询

      -- 查询出成绩最优
SELECT id,username,score FROM stu
WHERE score >= (SELECT score FROM level where id = 1);

    • 由EXIST引发的子查询:返回bool类型的结果,子查询为true才执行前面的语句

      SELECT depName from del
WHERE EXISTS (SELECT depName FROM dep WHERE id=10);

    • 三个关键字ANY SOME ALL

      运算符\关键字 ANY SOME ALL
      >、>= 最小值 最小值 最大值
      <、<= 最大值 最大值 最小值
      = 任意值 任意值
      <>、!= 任意值 
      SELECT * FROM stu
WHERE score >= ANY(SELECT score FROM level);
or
WHERE score >= SOME(SELECT score FROM level);
or
WHERE score >= ALL(SELECT score FROM level);

    • INSERT ... SELECT 的形式

      INSERT user2 SELECT id,username FROM user1;

    • CREATE ... SELECT 的形式

      CREATE TABLE user1(
	id int UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20)
)SELECT id,username FROM emp;

    • CREATE TABLE tbl_name LIKE tbl_name 的形式创建一个表(新表中没有数据)

  2. 联合查询

    • UNION

      SELECT 字段名称1,字段名称2... FROM tbl_name1
UNION
SELECT 字段名称3,字段名称4... FROM tbl_name2;

    • UNION ALL

      SELECT 字段名称1,字段名称2... FROM tbl_name1 
UNION ALL
SELECT 字段名称3,字段名称4... FROM tbl_name2;

    • UNION ALL 是简单的合并，UNION 会去掉表中重复记录

  3. 自身连接查询

    • 无限级分类的实现形式