从零学习Mysql数据库-常见操作1. 数据库类型 1.1 关系型数据库像 MySQL、Oracle、DB2、SQL

1. 数据库类型

1.1 关系型数据库

像 MySQL、Oracle、DB2、SQL Server等。特点：

1. 创建很多二维数据表

2. 数据表之间相互关联，形成一对一、一对多、多对多的关系

3. 可以利用SQL语句在多张表中查询所需数据

4. 支持事务，对数据访问更加的安全

1.2 非关系型数据库

像 MongoDB、Redis、Memcached 等。特点：

   1.英文名：Not Only SQL,简称NoSQL
   
   2.存储数据自由，可以放Json格式的数据
   
   3. 基于Key-Value的对应关系，查询过程中不需要解析SQL，性能更好
   
   4.通常不支持事务，需要手动保证一些原子性的操作

1.3 适用场景

   1. 目前在公司进行后端开发（Node、Java、Go等），还是以关系型数据库为主
   
   2.对爬取大量的数据进行存储时，常用到NoSQL

2. SQL语句

2.1 SQL语句分类

   1. DDL：数据定义语言，可以通过DDL语句对数据库或者表进行创建、删除、修改等操作；
   
   2. DML：数据操作语言，可以通过DML语句对表进行：添加、删除、修改等操作
   
   3. DQL：数据查询语言，可以通过DQL从数据库中查询记录
   
   4. DCL：数据控制语言，对数据库、表格的权限进行相关访问控制操作
   
   学习的重点是DML和DQL

2.2 数据库操作

   1. 查看所有的数据库：SHOW DATABASES
   
   2. 使用某一个数据库：USE dataBaseName
   
   3. 查看当前正在使用的数据库：SELECT DATABASE();
   
   4. 创建数据库：CREATE DATABASE databaseName
   
   //如果不存在就创建
   
   CREATE DATABASE IF NOT EXISTS databaseName
   
   //设置数据库的字符集和排序规则
   
   CREATE DATABASE IF NOT EXISTS dataBaseName DEFAULT CHARACTER 
   SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
   
   5. 删除数据库：
   
   DROP DATABASE databaseName;

   DROP DATABASE IF EXIT databaseName;
   
   6. 修改数据库的字符集和排序规则
   
   ALTER DATABASE bilibili CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

2.2 数据表操作

   1. 查看当前数据库下所有的数据表：SHOW TABLES;
   
   2. 查看某一个表结构：DESC tableName;
   
   3. 创建数据表: 
   
   CREATE TABLE IF NOT EXISTS `users`(

                name VARCHAR(20)

                age INT,

                height DOUBLE

                 );

2.3 SQL数据类型

  1. 数字类型：
  
  TINYINT：1个字节，-2^8~2^8-1
  
  SMALLINT：2个字节，-2^16~2^16-1
  
  MEDIUMINT：3个字节，-2^24~2^24-1
  
  INT：4个字节，-2^32~2^32-1
  
  BIGINT：8个字节，-2^64~2^64-1
  
  Float，double，DESIMAL
      
  DECIMAL(5,2) //总共5位包含2位小数的±数字
  
  2.日期类型
  
  YEAR以YYYY格式显示值：范围 1901到2155，和 0000
  
  DATE类型用于具有日期部分但没有时间部分的值：
  
  DATE以格式YYYY-MM-DD显示值，
  
  支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59;
  
  TIMESTAMP数据类型被用于同时包含日期和时间部分的值：
  
  TIMESTAMP以格式'YYYY-MM-DD hh:mm:ss'显示值；
  
  它的范围是UTC的时间范围：'1970-01-01 00:00:01'到'2038-01-19 03:14:07';
  
  3.字符串类型
  
  CHAR：创建表时为固定长度，长度可以是0到255之间的任何值，在被查询时，会删除后面的空格
  
  VARCHAR：可变长度的字符串，长度可以指定为0到65535之间的值，在被查询时，不会删除后面的空格
  
  BINARY，VARBINARY：用于存储二进制字符串，存储的是字节字符串
  
  BLOB：存储大的二进制类型
  
  TEXT：存储大的字符串类型

2.4 数据表约束

  主键：PRIMARY KEY,一张表中，我们为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不会为空的，这个字段我们通常会将它设置为主键
  
  a. 主键是表中唯一的索引
  
  b. 并且必须是NOT NULL的,不设置,MySQL隐式自动设置NOT NULL
  
  c. 开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键
  
  唯一：UNIQUE,某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用UNIQUE来约束
  
  a. 使用UNIQUE约束的字段在表中必须是不同的
  
  b. UNIQUE 索引允许NULL包含的列具有多个值NULL
  
  不能为空：NOT NULL,某些字段我们要求用户必须插入值，不可以为空
  
  默认值：DEFAULT,某些字段我们希望在没有设置值时给予一个默认值
  
  自动递增：AUTO_INCREMENT,某些字段我们希望不设置值时可以进行递增,比如用户的id

现在我们利用上面的约束创建一张表

  CREATE TABLE IF NOT EXISTS `users`(

            id INT PRIMARY KEY AUTO_INCREMENT,

            name VARCHAR(20) NOT NULL,

            age INT DEFAULT 0,

            telPhone VARCHAR(20) DEFAULT '' UNIQUE NOT NULL

    );

2.5 修改数据表

   修改表名

    ALTER TABLE `moments` RENAME TO `moment`;

    添加一个新的列

    ALTER TABLE `moment` ADD `publishTime` DATETIME;

    ALTER TABLE `moment` ADD `updateTime` DATETIME;

    删除一列数据

    ALTER TABLE `moment` DROP `updateTime`;

    修改列的名称

    ALTER TABLE `moment` CHANGE `publishTime` `publishDate` DATE;

    修改列的数据类型

    ALTER TABLE `moment` MODIFY `id` INT;
    
    插入一条数据
    
    INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) 
    
    VALUES ('iPhone', 'iPhone12只要998', 998.88, '2020-10-10');
    
    删除数据
    
    DELETE FROM `products`; //删除所有数据
    
    DELETE FROM `products` WHERE `title` = 'iPhone';//删除符合条件的数据
    
    修改数据
    
    UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88;//会修改表中所有的数据
    //会修改符合条件的数据
    
    UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88 WHERE `title` = 'iPhone'; 
    
    //如果我们希望修改完数据后，直接可以显示最新的更新时间
    
    ALTER TABLE `products` ADD `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
    
    ON UPDATE CURRENT_TIMESTAMP;

2.6 DQL 语句

就是从一个或者多个表中检索选中的行，格式如下

    SELECT select_expr [, select_expr]...

    [FROM table_references]

    [WHERE where_condition]

    [ORDER BY expr [ASC | DESC]]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [GROUP BY expr]

    [HAVING where_condition]

现在进行实操，准备表，在navicate中粘贴，再把数据传入


    CREATE TABLE IF NOT EXISTS `products` (

    id INT PRIMARY KEY AUTO_INCREMENT,

    brand VARCHAR(20),

    title VARCHAR(100) NOT NULL,

    price DOUBLE NOT NULL,

    score DECIMAL(2,1),

    voteCnt INT,

    url VARCHAR(100),

    pid INT

);

现在已经放入了很多数据：

基本的查询

  查询所有的数据并且显示所有的字段

  SELECT * FROM `products`
  
  查询特定的列title、brand、price

  SELECT title, brand, price FROM `products`
  
  给字段起别名
  
  SELECT title as t, brand as b, price as p FROM `products

WHERE条件查询

   查询价格小于1000的手机
   
   SELECT * FROM `products` WHERE price < 1000; 

   查询价格大于等于2000的手机

   SELECT * FROM `products` WHERE price >= 2000;
   
   查询品牌是华为，并且小于2000元的手机
   
   SELECT * FROM `products` WHERE `brand` = '华为' and `price` < 2000;

   SELECT * FROM `products` WHERE `brand` = '华为' && `price` < 2000;
   
   查询1000到2000的手机（包含1000和2000）

   SELECT * FROM `products` WHERE price BETWEEN 1000 and 2000;
   
   查看括号中的结果

   SELECT * FROM `products` WHERE brand in ('华为', '小米');
   
   like关键字，%表示匹配任意个的任意字符，_表示匹配一个的任意字符
   
   //查询所有以v开头的title

   SELECT * FROM `products` WHERE title LIKE 'v%';

   //查询带M的title

   SELECT * FROM `products` WHERE title LIKE '%M%';

  //查询带M的title必须是第三个字符（注意M前面的下划线写了2个）

  SELECT * FROM `products` WHERE title LIKE '__M%';

查询结果排序

  ORDER BY 排序
  
  ASC：升序排列； DESC：降序排列
  
  SELECT * FROM `products` WHERE brand = '华为' or price < 1000 ORDER BY price ASC;
  
  LIMIT 分页查询 LIMIT 30 OFFSET 0：查询30个，从第0个开始查
  
  SELECT * FROM `products` LIMIT 30 OFFSET 0;
  
  SELECT * FROM `products` LIMIT 30 OFFSET 30; 
  
  可以简写
  
  SELECT * FROM `products` LIMIT 90, 30;

聚合函数：对值集合进行操作的组（集合）函数

  华为手机价格的平均值

  SELECT AVG(price) FROM `products` WHERE brand = '华为';

  计算所有手机的平均分

  SELECT AVG(score) FROM `products`;

  手机中最低和最高分数

  SELECT MAX(score) FROM `products`;

  SELECT MIN(score) FROM `products`;

  计算总投票人数

  SELECT SUM(voteCnt) FROM `products`;

  计算所有条目的数量

  SELECT COUNT(*) FROM `products`;

  华为手机的个数

  SELECT COUNT(*) FROM `products` WHERE brand = '华为'

Group By，事实上聚合函数相当于默认将所有的数据分成了一组，现在我们希望划分多个组，比如按照品牌划分，这样就可一用到Group By

  SELECT brand,

      COUNT(*) as count,

      ROUND(AVG(price),2) as avgPrice,

      MAX(price) as maxPrice,

      MIN(price) as minPrice,

      AVG(score) as avgScore

  FROM `products` GROUP BY brand;

现在就按照品牌分组了

我们也可以用HAVING来给Group By查询到的结果添加一些约束，下面语句是筛选出平均价格在4000以下，并且平均分在7以上的品牌

  SELECT brand,

      COUNT(*) as count,

      ROUND(AVG(price),2) as avgPrice,

      MAX(price) as maxPrice,

      MIN(price) as minPrice,

      AVG(score) as avgScore

      FROM `products` GROUP BY brand

  HAVING avgPrice < 4000 and avgScore > 7;
  
  从语句上看 hHAVING 和 WHERE 都是条件判断，不过是右不同的地方：
  
  1. HAVING 跟在GROUP BY 后面
  
  2. HAVING 可以接聚合函数 WHERE 不行

2.7 多张表的操作

在上面的product表中，对应的品牌还需要包含其他的信息，比如品牌的官网，品牌的世界排名，品牌的市值等等

如果我们直接在商品中去体现品牌相关的信息，会存在一些问题：

products表中应该表示的都是商品相关的数据，应该有另外一张表来表示brand的数据
多个商品使用的品牌是一致时，会存在大量的冗余数据

因此，我们可以将所有的brand的数据单独放在一张表中，执行下面创建brand 表的SQL语句

  CREATE TABLE IF NOT EXISTS `brand`(

  id INT PRIMARY KEY AUTO_INCREMENT,

  name VARCHAR(20) NOT NULL,

  website VARCHAR(100),

  worldRank INT

    );

写入数据

创建外键

创建表添加外键约束，我们需要在创建表的()最后添加如下语句

  FOREIGN KEY (brand_id) REFERENCES brand(id);

如果是表已经创建好，额外添加外键

  ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id);

将products中的brand_id关联到brand中的id的值

  UPDATE `products` SET `brand_id` = 1 WHERE `brand` = '华为';

  UPDATE `products` SET `brand_id` = 4 WHERE `brand` = 'OPPO';

  UPDATE `products` SET `brand_id` = 3 WHERE `brand` = '苹果';

  UPDATE `products` SET `brand_id` = 2 WHERE `brand` = '小米';

外键存在时更新和删除数据

如果products中引用的外键被更新了或者删除了，这个时候会出现什么情况呢？执行如下语句

  UPDATE `brand` SET id = 100 WHERE id = 1;

可以看到会报错的，外键关联不能修改

如何进行更新呢我们可以给更新或者删除时设置几个值：

  `RESTRICT`（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或删除；
    
  `NO ACTION`：和RESTRICT是一致的，是在SQL标准中定义的
    
  `CASCADE`：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话， 更新：那么会更新对应的记录； 删除：那么关联的记录会被一起删除掉
    
  `SET NULL`：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL；

修改外键更新时的动作

查看表结构，找到外键名称

 SHOW CREATE TABLE `products`;

删除之前的外键

 ALTER TABLE `products` DROP FOREIGN KEY products_ibfk_1;

添加新的外键，并且设置新的action

  ALTER TABLE `products` ADD FOREIGN KEY (brand_id)
                         REFERENCES brand
                         ON UPDATE CASCADE ON DELETE CASCADE;

现在测试下修改了 brand表的id，products表的brand_id是否同时修改

  UPDATE `brand` SET id = 100 WHERE id = 1;

2.8 多表查询

我们希望查询到产品的同时，显示对应的品牌相关的信息，因为数据是存放在两张表中，所以这个时候就需要进行多表查询

如果我们直接通过查询语句希望在多张表中查询到数据，这个时候是什么效果呢

   SELECT * FROM `products`, `brand`;

得到的时 products数据总条数 * brand数据总条数，叫笛卡尔乘积

事实上，查出来的很多数据时没有意义的，我们可以用where来筛选

   SELECT * FROM `products`, `brand` WHERE `products`.brand_id = `brand`.id;

很显然，如果我们希望获得某些特定的数据也很艰难，此时可以用SQL JOIN操作

左连接：我们希望获取到的是左边所有的数据（以左表为主）

无论左边的表是否有对应的brand_id的值对应右边表的id，左边的数据都会被查询出来

  SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id;

  SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id

  WHERE brand.id IS NULL;

右连接：我们希望获取到的是右边所有的数据（以右表为主）

无论右边的表是否有id值对应左边表的id，右边的数据都会被查询出来

  SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id;

  SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id

  WHERE brand.id IS NULL;

内连接：内连接是表示左边的表和右边的表都有对应的数据关联

内连接有其他的写法：CROSS JOIN或者 JOIN都可以

  SELECT * FROM `products` INNER JOIN `brand` ON `products`.brand_id = `brand`.id;

全连接：SQL规范中全连接是使用FULL JOIN，但是MySQL中并没有对它的支持，我们需要使用 UNION 来实现

  (SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id)

  UNION

  (SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id);
  

  (SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id WHERE `brand`.id IS NULL)

  UNION
  
  (SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id WHERE `products`.id IS NULL);

下面准备一个多对多的案例练习，比如学生可以选择多门课程，一个课程可以被多个学生选择

先建立2张表，一张学生表，一张课程表

 CREATE TABLE IF NOT EXISTS `students`(

 id INT PRIMARY KEY AUTO_INCREMENT,

 name VARCHAR(20) NOT NULL,

 age INT

 );
 
 CREATE TABLE IF NOT EXISTS `courses`(

  id INT PRIMARY KEY AUTO_INCREMENT,

  name VARCHAR(20) NOT NULL,

  price DOUBLE NOT NULL

  );

往表中插入数据

 INSERT INTO `students` (name, age) VALUES('why', 18);

 INSERT INTO `students` (name, age) VALUES('tom', 22);

 INSERT INTO `students` (name, age) VALUES('lilei', 25);

 INSERT INTO `students` (name, age) VALUES('lucy', 16);

 INSERT INTO `students` (name, age) VALUES('lily', 20);

 INSERT INTO `courses` (name, price) VALUES ('英语', 100);

 INSERT INTO `courses` (name, price) VALUES ('语文', 666);

 INSERT INTO `courses` (name, price) VALUES ('数学', 888);

 INSERT INTO `courses` (name, price) VALUES ('历史', 80);

我们需要一个关系表来记录两张表的数据关系

 CREATE TABLE IF NOT EXISTS `students_select_courses`(

    id INT PRIMARY KEY AUTO_INCREMENT,

    student_id INT NOT NULL,

    course_id INT NOT NULL,

    FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,

    FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE

 );
 
  why 选修了 英文和数学

  INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);

  INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);

  lilei选修了 语文和数学和历史

  INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);

  INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);

  INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);

问题1.查询所有的学生选择的所有课程

  SELECT
  
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
  
  FROM `students` stu
  
  JOIN `students_select_courses` ssc
  
  ON stu.id = ssc.student_id
  
  JOIN `courses` cs
  
  ON ssc.course_id = cs.id;

问题2.查询所有学生选课情况

  SELECT
  
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
  
  FROM `students` stu
  
  LEFT JOIN `students_select_courses` ssc
  
  ON stu.id = ssc.student_id
  
  LEFT JOIN `courses` cs
  
  ON ssc.course_id = cs.id;

问题3.查询单个学生选课情况

  why同学选择了哪些课程
  
  SELECT
  
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
  
  FROM `students` stu
  
  LEFT JOIN `students_select_courses` ssc
  
  ON stu.id = ssc.student_id
  
  LEFT JOIN `courses` cs
  
  ON ssc.course_id = cs.id
  
  WHERE stu.id = 1;

问题4.哪些学生没有选课

  SELECT
  
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
  
  FROM `students` stu
  
  LEFT JOIN `students_select_courses` ssc
  
  ON stu.id = ssc.student_id
  
  LEFT JOIN `courses` cs
  
  ON ssc.course_id = cs.id
  
  WHERE cs.id IS NULL;

问题5.哪些课程没有被学生选择

  SELECT
  
  stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
  
  FROM `students` stu
  
  RIGHT JOIN `students_select_courses` ssc
  
  ON stu.id = ssc.student_id
  
  RIGHT JOIN `courses` cs
  
  ON ssc.course_id = cs.id
  
  WHERE stu.id IS NULL;

都为null 说明课程都被选了。