MySql

最新推荐文章于 2022-08-18 18:12:45 发布
最新推荐文章于 2022-08-18 18:12:45 发布
阅读量450 收藏 5
点赞数 2
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45454852/article/details/115334811
版权

1.MySQL

2.安装

3.连接和退出

C:\Users\yanyu>mysql -u root -p
Enter password: ******
mysql> exit

4.创建数据库

CREATE DATABASE 数据库名;

mysql> create DATABASE RUNOOB;

5.删除数据库

drop database <数据库名>;

mysql> drop database RUNOOB;

6.选择数据库

mysql> use RUNOOB;
Database changed

7.数据类型

  • 定长和变长字符串 CHAR(N) VARCHAR(MAX)
  • 定长和变长二进制串 BINARY(N) VARBINARY(MAX)
  • 整型数 INT SMALLINT
  • 浮点数 FLOAT DOUBLE
  • 日期型 DATE
  • 时间型 TIME
  • 时标 TIMESTAMP

https://www.runoob.com/mysql/mysql-data-types.html

第二章 创建数据表

定义

CREATE TABLE table_name (
	column_name1 column_type [列级约束条件],
	column_name2 column_type [列级约束条件],
	...
)[表级约束性条件];

例子

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
);

完整性约束

  • 实体完整性
    主码约束: PRIMARY KEY
  • 参照完整性
    外码约束: FOREIGN KEY
  • 用户自定义完整性
    唯一性约束: UNIQUE
    非空值约束: NOT NULL

主码定义

  1. 在列出关系模式属性时,在属性及其类型后加上保留字 : PRIMARY KEY.
CREATE TABLE 学生(
	 学号 VARCHAR(20) PRIMARY KEY,
	 姓名 VARCHAR(20) NOT NULL,
	 性别 VARCHAR(1) NOT NULL,
	 学院代码 VARCHAR(20) NOT NULL
);
  1. 在列出所有关系模式后,再附加一个声明:
    PRIMARY KEY ( 属性1, 属性2 , …)
CREATE TABLE 学习(
	学号 VARCHAR(20),
	课程号 VARCHAR(20),
	成绩 int,
	PRIMARY KEY(学号,课程号)
);

外码定义

  1. 如果外部关键字只有一个,可以直接在属性后面加 REFERENCES <表名> (<属性>).
  2. 在CREATE TABLE 语句的属性列表后面增加一个或几个外部关键字,
    FOREIGN KEY (<属性1>) REFERENCE <表名>(<属性>) .
为学生选课表建立外码：

CREATE  TABLE  学习
     ( 学号  CHAR(8) REFERENCES 学生(学号) ,
       课程号 CHAR(8) REFERENCES 课程(课程号),
       成绩   SMALLINT,
       PRIMARY  KEY (学号, 课程号));

26

CREATE  TABLE  学习
     ( 学号  CHAR(8) ,
       课程号 CHAR(8) ,
       成绩   SMALLINT,
       PRIMARY  KEY (学号, 课程号),
    FOREIGN KEY (学号)  REFERENCES 学生(学号),
    FOREIGN KEY (课程号)  REFERENCES 课程(课程号));

基本表的修改和删除

  • <表名>：要修改的基本表
  • ADD子句：增加新列和新的完整性约束条件
  • DROP子句：删除指定的列或完整性约束条件
  • ALTER子句：用于修改列名或数据类型

增加属性列

ALTER TABLE <表名> ADD <新列名> <数据类型> [ 完整性约束 ]

增加约束
可以是主码、外码、取值唯一等常用的列级约束。

	ALTER TABLE <表名> ADD <完整性约束名> 
//为学习表SC的学号列增加一个外码约束。
	ALTER TABLE 学习 ADD FOREIGN KEY(学号)
                    REFERENCES 学生(学号);
//说明：指定A的外码时，必须B的主码确定，前提要求学生表中已经指定学号为主码 
    ALTER TABLE 学生 ADD UNIQUE(姓名);

删除属性列

// 删除学生表中“入学日期”属性列。

       ALTER TABLE 学生  DROP  入学日期；

//注意：若一个属性被说明为NOT NULL，则不允
//许修改或删除。

删除约束

//删除学生表中的主码约束
	  ALTER TABLE 学生 DROP PRIMARY KEY( 主码1，主码2，...);
	  ALTER TABLE 学习 DROP FOREIGN KEY(学号) REFERENCES 学生(学号);

删除基本表

  DROP TABLE <表名>[RESTRICT|CASCADE];
//RESTRICT：受限删除
//CASCADE: 级联删除

修改属性列的类型

//将入学年份的数据类型改为半字长整数。
	ALTER TABLE 学生 MODIFY 出生年份 SMALLINT;
//注：修改原有的列定义有可能会破坏已有数据

//假设简历表中已经存在一列编号，类型为INT，
//将编号的数据类型改为自动增长型。
 ALTER TABLE 简历 MODIFY 编号 AUTO_INCREMENT;
//注：如果简历表原先有数据，那么修改后会从下一条
记录开始自动增长

索引的建立与删除

聚集索引
表中的记录按照某个搜索码指定的顺序排序，那么该搜索码对应的索引称为聚集索引。聚集索引也称为主索引，每个表只能有一个聚集索引，但是索引对应的元素可以不唯一，例如新华字典。
非聚集索引
搜索码指定的顺序与表中记录的物理顺序不同的索引称为非聚集索引。每个表中可以有多个非聚集索引。

用字典去理解，聚集索引就是物理顺序和聚集索引顺序一致。就像拼音。
非聚集索引就是按笔画查询，笔画顺序和字典实际排序规则不一致。

索引的定义

//语句格式
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>     ON <表
名>(<列名>[<次序>][,<列名>[<次序>] ]…)；
  • 用<表名>指定要建索引的基本表名字
  • 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
  • 用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值ASC
  • UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
  • CLUSTER表示要建立的索引是聚集索引

唯一值索引

  • 对于已含重复值的属性列不能建UNIQUE索引
  • 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束

聚集索引

  • 建立聚集索引后，基表中数据会按指定的属性值升序或降序存放。
//在学生表的姓名列上建立一个聚集索引，而且学生表中的记录将按照姓名升序存放。
CREATE CLUSTER INDEX Stusname ON
 学生(姓名 ASC)；

索引的删除
删除索引时，系统会从数据字典中删去有关该索引的描述。

	DROP INDEX <索引名>;

//删除学生表的Stusname索引。
	DROP INDEX Stusname;

索引使用技巧

  • 对于常用的小型表来说，使用索引不会使性能有任何提高。
  • 不要在memo、note型字段或者大型字段上创建索引。
  • 不要对经常需要更新或修改的字段创建索引。
  • 索引列中有较多不同的数据时索引会使性能有极大的提高。
  • 当查询要返回的数据很少时，索引可以优化查询。
  • 索引可以提高数据的返回速度，但是它使得数据的更新操作变慢。
  • 索引会占用数据库的空间。

基本表插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );



mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)

第二章 单表查询数据

SELECT语句

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> 
[ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；
  • 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
  • SELECT 命令可以读取一条或者多条记录。
  • 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
  • 你可以使用 WHERE 语句来包含任何条件。
  • DISTINCT 剔除重复元素。默认全部。

WHERE 子句

描述

类似if。

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
  • 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以使用 AND 或者 OR 指定一个或多个条件。
  • WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
  • WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
  • 尽量用主键作为where条件查询。

操作符

在这里插入图片描述
判断属性列值

  • =
  • <> !=

比较属性列值

  • <=
  • >=
  • <
  • >
判断属性列空值

IS NULL
IS NOT NULL
不能用=NULL 代替

判断属性列中在范围内的元组

IN
//使用谓词：IN <值表>,  NOT IN <值表> <值表>：用逗号分隔的一组取值
//查询信电学院 、理学院和计算机学院的学生的学号，姓名和学院。

SELECT 学号, 姓名, 学院
FROM  学生 
WHERE 学院 IN ('信电', '理学院', '计算机');
BETWEEN AND

判断属性列值是否在这个范围

BETWEEN AND
NOT BETWEEN … AND …

//查询出生年份在1996到1998年之间（包括1996和1998年）的学生的姓名、性别、学院和出生年份。
SELECT 姓名, 性别, 学院, 出生年份
FROM  学生 
WHERE 出生年份 BETWEEN 1996  AND 1998;
//等价于
SELECT 姓名, 性别, 学院, 出生年份
FROM  学生 
WHERE 出生年份 >= 1996  AND    
            出生年份 <= 1998;

匹配模板：固定字符串或含通配符的字符串

LIKE
 LIKE  '<匹配串>'  [ESCAPE '<转义字符>']
 姓名 LIKE '赵%';

//<匹配串>：指定匹配模板
  • % 代表任意长度字符串
  • _ 代表任意字符
  • ESCAPE 或者 \ (C++ 类似’"\")

集函数

5类主要集函数

  • 计数
    COUNT（ [ DISTINCT | ALL ] *）
    COUNT（ [ DISTINCT | ALL ] <列名>）
//查询学生总人数。
SELECT COUNT(*)  AS  总人数  
FROM 学生;
  • 计算总和
    SUM（ [ DISTINCT | ALL ] <列名>）
  • 计算平均值
    AVG（ [ DISTINCT | ALL ] <列名>）
//查询计算机学院学生的平均年龄。
SELECT AVG(year(now())-出生年份)  AS 平均年龄
FROM 学生
WHERE 学院 ='计算机';
  • 求最大值
    MAX（[ DISTINCT | ALL] <列名>
//查询学习180101号课程的学生最高分数。
SELECT MAX(成绩)  AS  最高分 
FROM 学习 
WHERE 课程号='180101';
  • 求最小值
    MIN（[ DISTINCT | ALL ] <列名>
  • DISTINCT短语：在计算时要取消指定列中的重复值
  • ALL短语：不取消重复值
  • ALL为缺省值

GROUP BY 语句

描述
GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

//查询各个课程号相应的选课人数。
mysql> select 课程号,count(学号)
    -> from 学习
    -> group by  课程号;
+-----------+---------------+
| 课程号    | count(学号)   |
+-----------+---------------+
| C1        |             4 |
| C3        |            16 |
| C4        |             7 |
| C5        |             8 |
| C6        |             3 |
| C2        |             3 |
+-----------+---------------+
6 rows in set (0.00 sec)

HAVING 筛选最终字句

使用HAVING子句筛选最终输出结果

//查询学号在091501~091506之间至少选修了3门课程的学生的学号和选修课程的课程数。
SELECT 学号,COUNT（课程号）AS 选课数
FROM 学习 
WHERE 学号 BETWEEN  ‘091501’ AND  ‘091506’
GROUP BY 学号
HAVING  COUNT(课程号)>=3;
  • 只有满足HAVING短语指定条件的组才输出
  • HAVING短语与WHERE子句的区别：作用对象不同
  • WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组

ORDER BY

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
  • 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
  • 你可以添加 WHERE…LIKE 子句来设置条件。
mysql> use RUNOOB;
Database changed
mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_tbl ORDER BY submission_date DESC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)

查询语句的执行顺序

  1. FROM子句
  2. WHERE子句
  3. GROUP BY子句
  4. GROUP BY子句
  5. HAVING子句
  6. ORDER BY子句
  7. SELECT子句

第三章 多表查询

连接的使用

多表查询的基础是笛卡尔积，笛卡尔积是通过连接来实现的。

等值连接

  • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
    在这里插入图片描述
mysql> use RUNOOB;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 学习 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

INNER JOIN/JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count
    -> FROM runoob_tbl a
    -> INNER JOIN tcount_tbl b
    -> ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+
4 rows in set (0.00 sec)

替代方式

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count
    -> FROM runoob_tbl a, tcount_tbl b
    -> WHERE a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+
4 rows in set (0.00 sec)

左连接

  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
    在这里插入图片描述
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count
    -> FROM runoob_tbl a
    -> LEFT JOIN tcount_tbl b
    -> ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|         5 | FK            |         NULL |
+-----------+---------------+--------------+
5 rows in set (0.00 sec)

右连接

  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
    在这里插入图片描述
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count
    -> FROM runoob_tbl a
    -> RIGHT JOIN tcount_tbl b
    -> ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         2 | 菜鸟教程      |           10 |
|         1 | 菜鸟教程      |           10 |
|         4 | RUNOOB.COM    |           20 |
|         3 | RUNOOB.COM    |           20 |
|      NULL | NULL          |           22 |
+-----------+---------------+--------------+
5 rows in set (0.00 sec)

带有IN谓词的子查询

//查询没有选修数据库原理课程的学生的学号和姓名。
SELECT 学号，姓名      
FROM    学生                        
WHERE 学号 NOT IN
   (SELECT 学号 
    FROM    学习 
    WHERE  课程号 IN
        (SELECT 课程号  
         FROM 课程     
         WHERE 课程名= ‘数据库原理’));

带有ANY:任意一个

//查询其他学院中比计算机学院某个学生年龄
小的学生名单。
    SELECT 姓名
    FROM 学生	 
    WHERE year(now())-出生年份< ANY  
           (SELECT  year(now())-出生年份
            FROM   学生 
            WHERE 学院='计算机' )
      AND 学院 <> '计算机' ; 
ANY或ALL谓词的子查询(续)


//用集函数MAX等价实现：
    SELECT 姓名
    FROM 学生	 
    WHERE year(now())-出生年份<
           (SELECT  MAX(year(now())-出生年份)
            FROM   学生 
            WHERE 学院='计算机' )
      AND 学院 <> '计算机' ;

带有ALL：所有

//查询其他学院中比计算机学院所有学生年龄都小的学生名单。
    SELECT 姓名
    FROM 学生	 
    WHERE year(now())-出生年份< ALL  
           (SELECT  year(now())-出生年份
            FROM   学生 
            WHERE 学院='计算机' )
      AND 学院 <> '计算机' ; 

//用集函数MIN等价实现：
    SELECT 姓名
    FROM 学生	 
    WHERE year(now())-出生年份<
           (SELECT  MIN(year(now())-出生年份)
            FROM   学生 
            WHERE 学院='计算机' )
      AND 学院 <> '计算机' ;

EXISTS NOT EXISTS

  • 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；
  • 然后再取外层表的下一个元组；
  • 重复这一过程，直至外层表全部检查完为止

EXISTS 或者NOT EXISTS
作为一个完整条件，需要直接在exists 内部直接判断，外层的表里的数据拿到内层exists（）验证，返回布尔值决定外层数据去留。

UNION 操作符（并运算）

描述

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

语法

MySQL UNION 操作符语法格式：

(SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions])

UNION [ALL | DISTINCT]

(SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];)

  • expression1, expression2, … expression_n: 要检索的列。

  • tables: 要检索的数据表。

  • WHERE conditions: 可选， 检索条件。

  • DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

  • ALL: 可选，返回所有结果集，包含重复数据。

mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)

交运算

形式与并运算基本一致，但是有些数据库不支持。

()
INTERSECT
()

差运算

一般是用
NOT IN
NOT EXISTS

除运算

SELECT  姓名	
FROM    学生
WHERE NOT EXISTS
(
	 (
	 SELECT  课程号
     FROM    课程
     WHERE  课程号 NOT IN
      					(
      					SELECT DISTINCT 课程号
       					FROM 学习
       					WHERE 学号=学生.学号
       					)   
      )
 );




SELECT 姓名	
FROM 学生
WHERE NOT EXISTS
(   
	SELECT  *
	FROM    课程  
    WHERE NOT EXISTS
        ( 
        	SELECT   *
            FROM      学习
            WHERE    学习.学号=学生.学号
                	AND    学习.课程号=课程.课程号
        )   
);

UPDATE 更新

描述

更新表中数据。

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
  • 你可以同时更新一个或多个字段。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在一个单独表中同时更新数据。
mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
Query OK, 1 rows affected (0.01 sec)
 
mysql> SELECT * from runoob_tbl WHERE runoob_id=3;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3         | 学习 C++   | RUNOOB.COM    | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
1 rows in set (0.01 sec)

mysql> use RUNOOB;
Database changed
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
Query OK, 1 row affected (0.23 sec)

DELETE 语句

描述

删除表格。

DELETE FROM table_name [WHERE Clause]
  • 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
  • 你可以在 WHERE 子句中指定任何条件
  • 您可以在单个表中一次性删除记录。

例子

一、利用脚本文件导入学生管理系统相关的表，结构如下
学院（学院代码，学院名称）
学生（学号，姓名，性别，学院代码）
教师（教师号，教师姓名，学院代码）
课程（课程号，课程名，学时）
学习（学号，课程号，教师号，成绩）
开课（教师号，课程号）

二、题目
1．求选修了老师“王刚”开课课程且成绩在90分以上的学生姓名、课程名称和成绩；（姓名，课程名，成绩）

SELECT DISTINCT `姓名`,`课程名`,`成绩`
FROM `学生` JOIN `学习` ON `学生`.`学号`=`学习`.`学号` JOIN `教师` ON `教师`.`教师号`=`学习`.`教师号` JOIN `课程` ON `课程`.`课程号`=`学习`.`课程号`
WHERE `教师`.`教师名`='王刚' AND `成绩`>90;

2．求选修了“王刚”老师所授全部课程的学生姓名和学院名称；（姓名，学院名称）

SELECT `姓名`,`学院名称` FROM `学生`,`学院` 
WHERE `学生`.`学院代码`=`学院`.`学院代码`
AND  NOT EXISTS 
(
	SELECT `课程号`FROM `教师`JOIN`授课`ON `教师`.`教师号`=`授课`.`教师号`
	WHERE `教师名`='王刚'
	AND `课程号` NOT IN (
	SELECT `课程号`FROM `学习`
	WHERE `学习`.`学号`=`学生`.`学号`
	
	)
);

3．求没有选修课程“软件工程”的学生学号和姓名；（学生学号，姓名）

SELECT DISTINCT `学生`.`学号` ,`姓名` FROM `学生`
WHERE EXISTS(
SELECT `课程`.`课程号`FROM `课程`
	WHERE `课程名`='软件工程'
	AND `课程号` NOT IN(
	SELECT `学习`.`课程号`FROM `学习` 
	WHERE `学生`.`学号`=`学习`.`学号`
	)
)

4．求至少选修了两门课程的学生学号；（学号）

SELECT 学生.`学号`  ,`课程号`FROM `学习`JOIN`学生` ON `学习`.`学号`=`学生`.`学号`
GROUP BY `学号`
HAVING COUNT(`课程号`)>2;

5．求课程“经济学”不及格学生姓名和考试成绩；（姓名，成绩）

SELECT `姓名`,`成绩` FROM `学生` JOIN `学习`ON `学生`.`学号`=`学习`.`学号`
JOIN `课程`ON 学习.`课程号`=`课程`.`课程号`
WHERE `学习`.`成绩`<60
AND `课程`.`课程名`='经济学'
ORDER BY `成绩`;

6．求至少选修了与学号“T06” 同学选修的课程相同的学生学号；（学号）

SELECT `学号` FROM `学生`
WHERE NOT EXISTS(
SELECT DISTINCT `课程号`FROM `学习`
WHERE `学习`.`学号`='T06'
AND `学习`.`课程号` NOT IN (
SELECT DISTINCT `课程号` FROM `学习`
WHERE `学习`.`学号`=`学生`.`学号`
)
)

7．求至少选修了“C3，C4”两门课程的学生姓名和学院名称；（姓名，学院名称）

SELECT `姓名` ,`学院名称` FROM `学生`JOIN`学院` ON `学生`.`学院代码`=`学院`.`学院代码`
WHERE NOT EXISTS(
SELECT DISTINCT `课程号`FROM `课程`
WHERE `课程`.`课程名`='C3' OR `课程`.`课程名`='C4' NOT IN(
SELECT DISTINCT `课程号` FROM `学习`
WHERE `学习`.`学号`=`学生`.`学号`
)
);

8．查询“王石”同学没有选修的课程号和课程名； （课程号，课程名）

SELECT `课程号`,`课程名`FROM `课程` 
WHERE  `课程号` NOT IN (
SELECT DISTINCT `课程号`FROM `学习` JOIN `学生` ON `学习`.`学号`=`学生`.`学号`
WHERE `姓名`='王石'

);

9．查询没有被任何学生选修的课程的课程号；（课程号）

SELECT `课程号` FROM `课程`
WHERE `课程号`NOT IN (
SELECT `课程号`FROM `学习`
);

10．求选修了全部课程的学生姓名；（姓名）

SELECT `姓名` FROM `学生`
WHERE NOT EXISTS(
SELECT `课程号`FROM `课程`
WHERE `课程号` NOT IN (
SELECT `课程号` FROM `学习`
WHERE `学习`.`学号`=`学生`.`学号`
)
);

11．查询各学院课程“经济学”的平均分，并按照成绩从高到低的顺序排列；（学院名称，平均分）

SELECT `学院名称` ,count(`成绩`) AS `平均分` FROM `学生` JOIN `学习` ON `学生`.`学号`=`学习`.`学号`JOIN `学院` ON `学生`.`学院代码`=`学院`.`学院代码`
GROUP BY `学院名称`
ORDER BY `成绩` DESC

12．查询选修课程“经济学”的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列；（姓名，学院名称，成绩）

SELECT`姓名`,`学院名称`,`成绩` FROM `学生`,`学院` AS a,`学习` AS b
	WHERE `学生`.`学号`=`b`.`学号`
	AND `学生`.`学院代码`=`a`.`学院代码`
	AND EXISTS  (
	SELECT DISTINCT `课程号` FROM `课程` 
	WHERE `课程名`='经济学'
	AND `课程号`=b.`课程号`
	
);

13．求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师；（课程号，课程名，教师姓名）

SELECT `课程名`,`课程名`, `教师名` FROM `课程` JOIN `授课` ON `授课`.`课程号`=`课程`.`课程号` JOIN `教师`ON `授课`.`教师号`=`教师`.`教师号`
WHERE `学时` BETWEEN 30 AND 40;

14．检索选修课程“经济学”的最高分学生的姓名；（姓名）

SELECT `姓名`FROM `学生`,`学习`,`课程`
WHERE 学生.`学号`=`学习`.`学号`
AND `学习`.`课程号`=`课程`.`课程号`
AND `课程`.`课程名`='经济学'
ORDER BY `成绩` DESC 
LIMIT 0,1;

15．查询选课人数超过5人的课程的课程号及课程名；（课程号，课程名）

SELECT `学习`.`课程号`,`课程名`FROM `课程`,`学习`
WHERE `课程`.`课程号`=`学习`.`课程号`
GROUP BY `学习`.`课程号`
HAVING COUNT(*)>5;



show variables like'character_set_%';
set character_set_server=utf8;
set character_set_database=utf8;

create database exp;
use exp;

create table 教师 
 (教师号  char(8) primary key,
  教师名  char(8) not null,
  学院代码  char(10)) default char set ='utf8';

create table 课程
( 课程号 char(8)  primary key,
  课程名 char(20) not null,
  学时   int) default char set ='utf8';
  
  create table 学生
  ( 学号 char(8) primary key,
    姓名 char(10) not null,
    性别 char(4) not null,
    学院代码 char(10)) default char set ='utf8';

create table 授课
( 教师号 char(8) references 教师(教师号),
  课程号 char(8) references 课程(课程号),
  primary key (教师号, 课程号)) default char set ='utf8';
  
  
create table 学院
( 学院代码 char(10) primary key,
  学院名称 char(30) not null) default char set ='utf8';
  

create table 学习
( 学号  char(8) references 学生(学号),
  课程号 char(8) references 课程(课程号),
  教师号 char(8) references 教师(教师号),
  成绩 int ,
  primary key  (学号,课程号,教师号)) default char set ='utf8';

insert into 教师 values('1','李梦','100');
insert into 教师 values('2','张亮','101');
insert into 教师 values('3','王刚','102');
insert into 教师 values('4','赵广','103');
insert into 教师 values('5','孙露','104');

insert into 课程 values('C1','数据库','40');
insert into 课程 values('C2','软件工程','32');
insert into 课程 values('C3','经济学','32');
insert into 课程 values('C4','古代汉语','64');
insert into 课程 values('C5','信号与系统','72');
insert into 课程 values('C6','材料力学','56');
insert into 课程 values('C7','通信原理','72');


insert into 学生 values('T01','王娜','女','100');
insert into 学生 values('T02','李倩','女','100');
insert into 学生 values('T03','张皓','男','100');
insert into 学生 values('T04','李静','女','101');
insert into 学生 values('T05','周小米','女','101');
insert into 学生 values('T06','王石','男','102');
insert into 学生 values('T07','谢楠','女','102');
insert into 学生 values('T08','蒋明','男','102');
insert into 学生 values('T09','陈冲','男','102');
insert into 学生 values('T10','赵琳','女','102');
insert into 学生 values('T11','韩寒','女','103');
insert into 学生 values('T12','吴亮','男','103');
insert into 学生 values('T13','刘波','男','103');
insert into 学生 values('T14','张斌','男','104');
insert into 学生 values('T15','周通','男','104');
insert into 学生 values('T16','张航','男','100');

insert into 学习 values('T01','C1','1',88);
insert into 学习 values('T01','C3','3',54);
insert into 学习 values('T01','C4','3',69);
insert into 学习 values('T01','C5','3',85);
insert into 学习 values('T02','C3','3',74);
insert into 学习 values('T02','C4','3',63);
insert into 学习 values('T02','C5','3',66);
insert into 学习 values('T02','C6','4',77);
insert into 学习 values('T03','C1','1',95);
insert into 学习 values('T03','C2','2',92);
insert into 学习 values('T03','C3','3',92);
insert into 学习 values('T03','C4','3',91);
insert into 学习 values('T03','C5','3',87);
insert into 学习 values('T04','C1','1',83);
insert into 学习 values('T04','C3','3',64);
insert into 学习 values('T04','C5','3',62);
insert into 学习 values('T05','C3','3',83);
insert into 学习 values('T05','C4','3',78);
insert into 学习 values('T05','C6','4',78);
insert into 学习 values('T06','C3','3',66);
insert into 学习 values('T06','C5','5',33);
insert into 学习 values('T07','C3','3',69);
insert into 学习 values('T07','C4','3',56);
insert into 学习 values('T08','C3','3',64);
insert into 学习 values('T08','C5','5',65);
insert into 学习 values('T09','C2','2',64);
insert into 学习 values('T09','C3','3',45);
insert into 学习 values('T10','C3','3',46);
insert into 学习 values('T10','C4','3',56);
insert into 学习 values('T11','C3','3',75);
insert into 学习 values('T12','C3','3',86);
insert into 学习 values('T13','C3','3',67);
insert into 学习 values('T14','C3','3',65);
insert into 学习 values('T15','C3','3',68);
insert into 学习 values('T15','C5','3',56);
insert into 学习 values('T16','C1','1',91);
insert into 学习 values('T16','C2','2',89);
insert into 学习 values('T16','C3','3',94);
insert into 学习 values('T16','C4','3',76);
insert into 学习 values('T16','C5','5',81);
insert into 学习 values('T16','C6','4',82);


insert into 学院 values('100','计算机');
insert into 学院 values('101','机电');
insert into 学院 values('102','管理');
insert into 学院 values('103','材料');
insert into 学院 values('104','文法');

insert into 授课 values('1','C1');
insert into 授课 values('2','C2');
insert into 授课 values('3','C3');
insert into 授课 values('3','C4');
insert into 授课 values('3','C5');
insert into 授课 values('4','C6');
insert into 授课 values('5','C5');
insert into 授课 values('5','C6');
insert into 授课 values('5','C7');
烟雨平生、赵
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql外码内码定义_Java 字符编码
weixin_33954881的博客
02-08 552
原标题：Java 字符编码 作者介绍王云静，Java 开发工程师，2018 年 7 月加入去哪儿网，目前在目的地 - 呼叫中心。曾获得过 ACM 亚洲区域赛铜牌。-----基本概念 字符集字符编码字符编码(英语：Character encoding)也称字集码，是把字符集中的字符编码为指定集合中某一对象(例如：比特模式、自然数序列、8 位组或者电脉冲)，以便文本在计算机中存储和通过通信网络的传递。...
Mysql外码
flying_coding的博客
10-24 3663
明确几个定义:码，主码，外码。 码:唯一标识实体的属性集。 主码:多个候选码中选择其中一个为主码。 外码:属性或属性组X不是关系模式R的码（既不是主码也不是候选码），但X是另一个关系模式的码，则称X是R的外部码，也称外码。 看外码定义，有两个要求，一是不能是本关系的码，还必须是另一个关系的码。 回到开头的码的定义，就知道了，外码不能是码，否则两个关系模式就成一个关系模式了，没有分开的必要了。这就是问题答案了。 但是，外码可以为主属性！即，可为主码的部分属性集。这点要注意！当然此时的外码不能为空。 ...
MySQL中的外键（foreign key）
热门推荐
wgchen
08-18 3万+
MySQL中的外键（foreign key）
SQL service数据表 定义约束
Half-up的博客
11-27 3944
SQL service数据表 定义约束 主码约束 在SSMS工具中定义主键的方法为： 设计器可视化 sql语句也可以定义主键 在建表的时候 后面写个 primary key(Cno), 或者 Cno char(10) not null primary key, 定义外键约束 右键–关系 在上面添加关系 在“选定的关系”列表框中列出了系统提供的默认关系名称（这里是：FK_Course_Course），名称格式为FK_< tablename>_< tablename>，其中t
SQL中怎么创建外键和删除外键
汪雯琦的博客
03-29 5200
文章目录1.什么是外键约束2.对于已经存在的字段添加外键约束3.在创建数据表时设置外键约束4. 删除外键约束（1）获取外键约束名称5. 小结 1.什么是外键约束 外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性 2.对于已经存在的字段添加外键约束 -- 为cls_id字段添加外键约束 alter table students...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
MySQL学生成绩管理系统设计实验报告
04-28
MySQL学生成绩管理系统设计实验报告MySQL学生成绩管理系统设计实验报告MySQL学生成绩管理系统设计实验报告MySQL学生成绩管理系统设计实验报告MySQL学生成绩管理系统设计实验报告MySQL学生成绩管理系统设计实验报告...
mysql-connector-java-5.1.49
04-06
mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-...
Flink CDC MySQL同步MySQL（一）
07-06
jdbc
win11安装mysql
04-28
win11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin11安装mysqlwin...
mysql创建表里主码和外码_SQL语言创建表时候怎么定义主码和外码
weixin_36052776的博客
01-21 6948
展开全部SQL语言62616964757a686964616fe58685e5aeb931333433623064创建表时候用Primary Key(属性名)定义主码，用Foreign Key(属性名)定义外码。主码是一种唯一关键字，表定义的一部分。一个表的主码可以由多个关键字共同组成，并且主码的列不能包含空值。主码是可选的，并且可在 CREATE TABLE语句中用Primary Key(属性名...
SQL中的外键约束
a1078285909的博客
12-22 961
外键约束 如有两个表 学生表 id 主键约束 名字 性别 成绩表 id 学生id 成绩 给成绩表的学生id加上外键约束 create table 成绩表( id int， 学生id int， 成绩 int， constraint 外键约束名 foreign key （学生id） references 学生表（id） ) 给成绩表添加数据时，学生id的值必须在学生表中存在 如果成绩表中有对应学生表的数据，就不能删除学生表中的数据 ...
mysql定义外键_MySQL定义外键的方法
weixin_40008920的博客
01-18 336
MySQL定义外键的方法是每个学习MySQL的人都需要掌握的知识，下文就对MySQL定义外键的语句写法进行了详细的阐述，供您参考。外键为MySQL带来了诸多的好处，下面就为您介绍MySQL定义外键的语句写法，以及MySQL定义外键过程中出现错误的处理方法，供您参考学习。mysql> CREATE TABLE categories (-> category_id tinyint(3) u...
SQL语句中主键和外键
Zzze0101的博客
05-06 1万+
主键 在创建表时，一般会定义一个主键来唯一标识一组信息，例如保存京东fuliuqingfeng的用户信息及其多个邮寄商品地址： CREATE TABLE user_info( id CHAR(36) PRIMARY KEY, user_name VARCHAR(30) not null, password VARCHAR(30) not null, real_name VARCHA...
mysql数据库中主外键的设置语句
Poor_Richard的博客
07-04 7225
create table tableName( k1 int auto_increment, k2 varchar(50) not null, k3… primary key(k1), foreign key(kn) reference otherTable(ok) ); //此表主键为k1,外键为kn //此表中外键kn参照主键表otherTable的主键ok ...
mysql外键设置sql语句_sql设置外键(设置外键的sql语句)
weixin_33101399的博客
02-01 9853
sql设置外键(设置外键的sql语句)2020-07-24 11:44:03共8个回答sql怎么设置外键创建数据库时就是有主键的创建了主键，但是表之间的关系没有联系，要建数据库关系图只有主键没有外键时不行的。建外键的前提是此外键必须是另外一个表的主键。建外键的步骤：第一步、打开要建外键表的设计器，右击选择“关系”。第二步、然后弹出“外键关系”窗体，我们选择“添加”，然后点击“表和列规范”后面的小按...
mysql外码是什么意思_MySQL中的外键是什么、有什么作用
weixin_39633781的博客
01-18 1480
我的疑问是 "使用外键约束" ，然后我对 "外键" 这个词不是非常理解。查询相关资料都是讲一些术语。说外键的主要作用是：保持数据的一致性、完整性。听得我是一头雾水。关于外键。我有自己的一些理解，可是不晓得是否正确，举个样例来表达我的看法：假如如今须要建立一个表，一个什么样的表呢？一个班级的学生个人信息表：所以在设计的时候。就给表1加入一个外键，这个外键就是表2中的学号字段。那么这样表1就是主表，表...
mysql部署mysql
最新发布
09-09
要在Kubernetes上部署MySQL，可以按照以下步骤进行操作： 1. 创建一个MySQL配置文件（如：mysql-config.yaml），示例内容如下： ```yaml apiVersion: v1 kind: Secret metadata: name: mysql-secret type: Opaque...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值