MYSQL

MySQL基本操作命令汇总，主要包括查询数据、修改数据、数据库命令

数据库

• MySQL
  • 查询数据
    • 基本查询
      • SELECT * FROM <表名>
        • 使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。
    • 条件查询
      • SELECT * FROM <表名> WHERE <条件表达式>
        • (1) 条件表达式,可以用 AND 表达满足条件1并且满足条件2 SELECT * FROM students WHERE score >= 80 AND gender = 'M'; (2)是 OR ，表示满足条件1或者满足条件2。 SELECT * FROM students WHERE score >= 80 OR gender = 'M'; (3)是NOT ，表示“不符合该条件”的记录 SELECT * FROM students WHERE NOT class_id = 2; (4)要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生： SELECT * FROM students WHERE (score 90) AND gender = 'M'; 逻辑优先级： NOT>AND>OR 加上括号可以改变优先级 常用的条件表达式： 条件 表示 相等 = 大于 > 小于 大于或相等 >= 小于或相等 不相等 相似 LIKE name LIKE 'ab%' name LIKE '%bc%' %表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd'
    • 投影查询
      • SELECT 列1, 列2, 列3 FROM <表名>
        • 投影查询:在查询时，我们希望返回某些指定列的数据，而不是所有列的数据，返回结果集中仅包含指定列,并且结果集的列的顺序和原表可以不一样。 给指定列取别名： 使用投影查询，并将列名重命名： SELECT id, score points, name FROM students;
      • SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...
    • 排序
      • 我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。
      • ORDER BY
        • ORDER BY 排序： 1）默认排序ASC（升序） 2）DESC（降序） 如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序： SELECT id, name, gender, score FROM students ORDER BY score DESC, gender; ⭐如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。 优先级： WHERE>ORDER BY
    • 分组
      • GROUP BY
        • SELECT COUNT(*) num FROM students GROUP BY class_id; 执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。
    • 分页查询
      • LIMIT <M> OFFSET <N>
    • 聚合查询
      • 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：
      • COUNT()
        • COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。 通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果。设置一个结果集列名为num： SELECT COUNT(*) num FROM students; COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等： SELECT COUNT(*) boys FROM students WHERE gender = 'M';
      • SUM()
      • AVG()
        • SELECT AVG(score) average FROM students WHERE gender = 'M';
      • MAX()
      • MIN()
    • 多表查询
      • 多表查询又称笛卡儿查询 例如，同时从students表和classes表的“乘积”，即查询数据，可以这么写： SELECT * FROM students, classes; 这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。 多表查询： 行数=查询表数行数之极 列数=查询表数列数之和
      • SELECT * FROM <表1> <表2>
        • 多表查询的结果集中会有相同列名，这样会不好区分；可以通过给结果集中相同列名的列“设置别名”来加以区分。 方法1：表名.列名 别名 SELECT students.id sid, students.name, students.gender, students.score, classes.id cid, classes.name cname FROM students, classes; 方法2：使用 FROM , SELECT s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c; 多表查询也是可以添加WHERE条件的，我们来试试： SELECT s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c WHERE s.gender = 'M' AND c.id = 1;
    • 连接查询
      • 连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。 JOIN查询仍然可以使用WHERE条件和ORDER BY排序。
      • 内连接
        • INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。
        • SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>
          • INNER JOIN查询的写法是： 1)先确定主表，仍然使用FROM 的语法； 2)再确定需要连接的表，使用INNER JOIN 的语法； 3)然后确定连接条件，使用ON ，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接； 4)可选：加上WHERE子句、ORDER BY等子句。 使用别名不是必须的，但可以更好地简化查询语句。 SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s INNER JOIN classes c ON s.class_id = c.id;
      • 外连接
        • 左连接(左外连接)
          • LEFT OUTER JOIN则返回左表都存在的行。 SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s LEFT OUTER JOIN classes c ON s.class_id = c.id;
          • SELECT ... FROM <表1> LEFT OUTER JOIN <表2> ON <条件...>
        • 右连接(右外连接)
          • RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。 SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s RIGHT OUTER JOIN classes c ON s.class_id = c.id;
          • SELECT ... FROM <表1> RIGHT OUTER JOIN <表2> ON <条件...>
  • 修改数据
    • INSERT
      • INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
        • 注意到我们并没有列出id字段，也没有列出id字段对应的值，这是因为id字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。 要注意，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。也就是说，可以写INSERT INTO students (score, gender, name, class_id) ...，但是对应的VALUES就得变成(80, 'M', '大牛', 2)。 还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值： INSERT INTO students (class_id, name, gender, score) VALUES (1, '大宝', 'M', 87), (2, '二宝', 'M', 81);
    • UPDATE
      • UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
        • 例如，我们想更新students表id=1的记录的name和score这两个字段，先写出UPDATE students SET name='大牛', score=66，然后在WHERE子句中写出需要更新的行的筛选条件id=1： -- 更新id=1的记录 UPDATE students SET name='大牛', score=66 WHERE id=1; -- 查询并观察结果: SELECT * FROM students WHERE id=1; 注意到UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录： -- 更新id=5,6,7的记录 UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id; -- 查询并观察结果: SELECT * FROM students; 在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分： -- 更新score UPDATE students SET score=score+10 WHERE score -- 查询并观察结果: SELECT * FROM students; 在使用MySQL这类真正的关系数据库时，UPDATE语句会返回更新的行数以及WHERE条件匹配的行数
    • DELETE
      • DELETE FROM <表名> WHERE ...;
        • DELETE语句可以删除一条或多条记录； DELETE FROM students WHERE id>=5 AND id -- 查询并观察结果: SELECT * FROM students; 和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据： DELETE FROM students; 在使用MySQL这类真正的关系数据库时，DELETE语句也会返回删除的行数以及WHERE条件匹配的行数。
    • DROP
      • DROP DATABASE<数据库名>;
      • DROP TABLE <表名> ;
  • 数据库命令
    • 数据库
      • 创建
        • CREATE DATABASE <数据库命>;
      • 切换当前数据库
        • USE <数据库命>;
      • 删除
        • DROP DATABASE <数据库命>;
    • 表
      • 创建
        • CREATE TABLE table_name (column_name column_type);
      • 删除
        • DROP TABLE table_name ;
      • 显示
        • SHOW TABLES;
      • 修改
        • ALTER TABLE <表名> [修改选项]
          • ALTER TABLE [修改选项] 修改选项的语法格式如下： { ADD COLUMN | CHANGE COLUMN | ALTER COLUMN { SET DEFAULT | DROP DEFAULT } | MODIFY COLUMN | DROP COLUMN | RENAME TO }
    • 退出MySQL
      • exit