SQL与关系数据库基本操作

SQL与关系数据库基本操作

文章目录

  • 第一节 SQL概述
    • 一、SQL的发展
    • 二、SQL的特点
    • 三、SQL的组成
  • 第二节 MySQL预备知识
    • 一、MySQL使用基础
    • 二、MySQL中的SQL
      • 1、常量
        • (1)字符串常量
        • (2)数值常量
        • (3)十六进制常量
        • (4)日期时间常量
        • (5)位字段值(了解)
        • (6)布尔值
        • (7)NULL值
      • 2、变量
      • 3、运算符
      • 4、表达式
      • 5、内置函数
  • 第三节 数据定义
    • 一、数据库模式定义
      • 1、创建数据库
      • 2、选择数据库
      • 3、修改数据库
      • 4、删除数据库
      • 5、查看数据库
    • 二、表定义
      • 1、创建表
      • 2、添加字段
      • 3、修改字段
      • 4、删除字段
      • 5、重命名表
      • 6、删除表
      • 7、查看表
      • 8、查看表结构
    • 三、索引定义
      • 1、索引的创建
      • 2、索引的查看
      • 3、索引的删除
  • 第四节 数据更新
    • 一、插入数据
    • 二、删除数据
    • 三、修改数据
  • 第五节 数据查询
    • 一、SELECT语句
    • 二、列的选择与指定
      • 1、选择指定的列
      • 2、定义并使用列的别名
      • 3、替换查询结果集中的数据
      • 4、计算列值
      • 5、聚合函数
    • 三、FROM子句与多表连接查询
      • 1、交叉连接
      • 2、内连接
      • 3、外连接
        • (1)左连接
        • (2)右连接
    • 四、WHERE子句与条件查询
    • 五、GROUP BY子句与分组数据
    • 六、HAVING子句
    • 七、ORDER BY子句
    • 八、LIMIT子句
  • 第六节 视图
    • 一、创建视图
    • 二、删除视图
    • 三、修改视图定义
    • 四、查看视图定义
    • 五、更新视图数据
    • 六、查询视图数据
  • 附上`mydata.sql`


第一节 SQL概述

一、SQL的发展

结构化查询语言(Structured Query Language,简称SQL)是一种介于关系代数和关系演算之间的语言。由于具有语言简洁、方便实用、功能齐全等优点,很快得到推广和应用。随着关系数据库的流行,SQL语言最终发展成为关系数据库的标准语言和数据库领域中一个主流语言。SQL已经被ANSI(美国国家标准化组织)确定为数据库系统的工业标准,它是数据库系统的通用语言。利用SQL,用户可以用几乎相同的语句在不同的数据库系统上执行同样的操作。MySQL利用SQL对数据库进行操作,此外SQL语言不区分大小写

二、SQL的特点

​ 1、SQL不是某个特定数据库供应商专有的语言。几乎所有重要的关系数据库管理系统都支持SQL,所以掌握SQL可以帮助用户与几乎所有的关系数据库进行交互。
​ 2、SQL简单易学。它的语句全都是由具有很强描述性的英语单词所组成,而且这些单词的数目不多。

​ 3、SQL尽管看上去很简单,但它实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作。

三、SQL的组成

​ 1、数据定义语言(Data Definition Language,DDL)

​ 2、数据操纵语言(Data Manipulation Language,DML)

​ 3、数据控制语言(Data Control Language,DCL)

​ 4、嵌入式和动态SQL规则

​ 5、SQL调用和会话规则

第二节 MySQL预备知识

一、MySQL使用基础

​ 1、LAMP(Linux+Apache+ MySQL+PHP/Perl/Python),即使用Linux作为操作系统,Apache作为Web服务器,MySQL作为数据库管理系统,PHP、Perl或Python语言作为服务器端脚本解释器。

​ 2、WAMP(Windows+Apache+MySQL+PHP/Perl/Python),即使用Windows系统,Apache作为Web服务器,MySQL作为数据库管理系统,PHP、Perl或Python语言作为服务器端脚本解释器。

二、MySQL中的SQL

在这里插入图片描述

图4.1 MySQL中的SQL对关系数据库模式的支持

1、常量

(1)字符串常量

​ 字符串是指用单引号或双引号括起来的字符序列,分为ASCII字符串常量(一个字节)和Unicode字符串常量(两个字节)。

​ ASCII例如:‘hello’, ‘How are you!’

​ Unicode字符串常量与ASCII字符串常量相似,但它前面有一个N标志,例如:N’hello’。

mysql> select 'hello';
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.02 sec)mysql> select "hello";
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.03 sec)mysql> select N'hello';
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.04 sec)mysql> select N"hello";
1054 - Unknown column 'N' in 'field list'     -- 使用双引号会报错
mysql> 
(2)数值常量

​ 数值常量可以分为整数常量浮点数常量

​ 整数常量即不带小数点的十进制数,例如:1894,2,+145345234,-2147483648。

​ 浮点数常量是使用小数点的数值常量,例如:5.26,-1.39,101.5E5,0.5E-2。

mysql> select 12345.6789;
+------------+
| 12345.6789 |
+------------+
| 12345.6789 |
+------------+
1 row in set (0.04 sec)mysql> select 12345;
+-------+
| 12345 |
+-------+
| 12345 |
+-------+
1 row in set (0.04 sec)mysql> select 12345e-3;
+----------+
| 12345e-3 |
+----------+
|   12.345 |
+----------+
1 row in set (0.04 sec)mysql> 
(3)十六进制常量

​ MySQL支持十六进制值。一个十六进制值通常指定为一个字符串常量,每对十六进制数字被转换为一个字符,其最前面有一个大写字母“X”或小写字母“x”。在引号中只可以使用数字“0”到“9”及字母“a”到“f”或“A”到“F”。例如:X’41’表示大写字母A。x’4D7953514C’表示字符串MySQL。

mysql> select X'41';
+-------+
| X'41' |
+-------+
| A     |
+-------+
1 row in set (0.02 sec)mysql> select X'4D7953514C';
+---------------+
| X'4D7953514C' |
+---------------+
| MySQL         |
+---------------+
1 row in set (0.03 sec)mysql> 

​ 十六进制数值不区分大小写,其前缀“X”或“x”可以被“0x”取代而且不用引号。及X’41’可以替换为0x41。

41=4*16^1+1*16^0=65

​ 注意:“0x”中x一定要小写。

​ 十六进制值的默认类型是字符串。如果向要确保该值作为数字处理,可以使用CAST(…AS UNSIGNED)。

(4)日期时间常量

​ 日期时间常量:用单引号将表示日期时间的字符串括起来构成。

​ 日期型常量包括年、月、日,数据类型为DATE,表示为"1999-06-17"这样的值。

​ 时间型常量包括小时数、分钟数、秒数及微秒数,数据类型为TIME,表示为“12:30:43.00013”这样的值。

mysql> select '2023-10-04';
+------------+
| 2023-10-04 |
+------------+
| 2023-10-04 |
+------------+
1 row in set (0.02 sec)mysql> select '2023-10-04 11:08:30';
+---------------------+
| 2023-10-04 11:08:30 |
+---------------------+
| 2023-10-04 11:08:30 |
+---------------------+
1 row in set (0.03 sec)mysql> 
(5)位字段值(了解)

​ 可以使用b’value’符号写位字段值。value是一个用0和1写成的二进制值。直接显示b’value’的值可能是一系列特殊的符号。

​ 例如:b’0’显示位空白,b’1’显示位一个图案。

mysql> select b'1';
+------+
| b'1' |
+------+
|     |
+------+
1 row in set (0.04 sec)mysql> select b'111';
+--------+
| b'111' |
+--------+
|       |
+--------+
1 row in set (0.04 sec)mysql> 
(6)布尔值

​ 布尔值只包含两个可能的值:TRUEFALSE。FALSE的数字值为“0”,TRUE的数字值为“1”。

mysql> select true;
+------+
| TRUE |
+------+
|    1 |
+------+
1 row in set (0.03 sec)mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.04 sec)mysql> select false;
+-------+
| FALSE |
+-------+
|     0 |
+-------+
1 row in set (0.03 sec)mysql> 
(7)NULL值

​ NULL值可适用于各种列类型,它通常用来表示“没有值”、“无数据”等意义,并且不同于数字类型的“0”或字符串类型的空字符串。

mysql> select NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.02 sec)mysql> 

2、变量

​ 用户可以在表达式中使用自己定义的变量,这样的变量叫作用户变量

​ 用户可以先在用户变量中保存值,然后在以后引用它这样可以将值从一个语句传递到另一个语句,在使用用户变量前必须定义和初始化。如果使用没有初始化的变量,它的值为NULL。

​ 用户变量前加**@**用来区分列名。

​ 必须在系统变量前加两个@

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)mysql> select @1;
+------+
| @1   |
+------+
| NULL |
+------+
1 row in set (0.03 sec)mysql> select @a+1;
+------+
| @a+1 |
+------+
|    2 |
+------+
1 row in set (0.04 sec)mysql> set @b;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select @b;
+------+
| @b   |
+------+
| NULL |
+------+
1 row in set (0.05 sec)mysql> 

3、运算符

​ (1)算术运算符在两个表达式上执行数学运算,这两个表达式可以是任何数字数据类型。算术运算符有:+(加)、-(减)、*(乘)、/(除)和%(求模)5种运算。

mysql> select @a;
+----+
| @a |
+----+
|  1 |
+----+
1 row in set (0.01 sec)mysql> select @a+5;
+------+
| @a+5 |
+------+
|    6 |
+------+
1 row in set (0.02 sec)-- 加减乘除都一样,重点来看一下求模mysql> select 7%3;
+-----+
| 7%3 |
+-----+
|   1 |
+-----+
1 row in set (0.04 sec)mysql> 

​ (2)位运算符有:&(位与)、|(位或)、^(位异或)、~(位取反)、>>(位右移)、<<(位左移)。

mysql> select 5&1;
+-----+
| 5&1 |
+-----+
|   1 |
+-----+
1 row in set (0.02 sec)mysql> select 5|1;
+-----+
| 5|1 |
+-----+
|   5 |
+-----+
1 row in set (0.03 sec)mysql> select ~1;
+----------------------+
| ~1                   |
+----------------------+
| 18446744073709551614 |
+----------------------+
1 row in set (0.03 sec)mysql> select 5^1;
+-----+
| 5^1 |
+-----+
|   4 |
+-----+
1 row in set (0.05 sec)mysql> select 5>>1;
+------+
| 5>>1 |
+------+
|    2 |
+------+
1 row in set (0.06 sec)mysql> select 5<<1;
+------+
| 5<<1 |
+------+
|   10 |
+------+
1 row in set (0.07 sec)mysql> 

​ (3)比较运算符(又称关系运算符),用于比较两个表达式的值,其运算结果为逻辑值,可以为三种之一1(真)、0(假)及NULL(不能确定)

运算符含义运算符含义
=等于<=小于等于
>大于<>、!=不等于
<小于<=>相等或都等于空
>=大于等于
mysql> select 5>1;
+-----+
| 5>1 |
+-----+
|   1 |
+-----+
1 row in set (0.02 sec)mysql> select 5<1;
+-----+
| 5<1 |
+-----+
|   0 |
+-----+
1 row in set (0.03 sec)mysql> select 5=1;
+-----+
| 5=1 |
+-----+
|   0 |
+-----+
1 row in set (0.04 sec)mysql> select 5!=1;
+------+
| 5!=1 |
+------+
|    1 |
+------+
1 row in set (0.03 sec)mysql> select 5>=1;
+------+
| 5>=1 |
+------+
|    1 |
+------+
1 row in set (0.04 sec)mysql> 

​ (4)逻辑运算符

运算符运算规则运算符运算规则
NOT或!逻辑非OR或||逻辑或
AND或&&逻辑与XOR逻辑异或
mysql> select not (5>=1);
+------------+
| not (5>=1) |
+------------+
|          0 |
+------------+
1 row in set (0.02 sec)mysql> select 5>=1 && 6<=8;
+--------------+
| 5>=1 && 6<=8 |
+--------------+
|            1 |
+--------------+
1 row in set (0.03 sec)mysql> select 5>=1 and 6<=8;
+---------------+
| 5>=1 and 6<=8 |
+---------------+
|             1 |
+---------------+
1 row in set (0.04 sec)mysql> select 5>=1 and 6>8;
+--------------+
| 5>=1 and 6>8 |
+--------------+
|            0 |
+--------------+
1 row in set (0.06 sec)mysql> select 5>=1 or 6>8;
+-------------+
| 5>=1 or 6>8 |
+-------------+
|           1 |
+-------------+
1 row in set (0.06 sec)mysql> select 5>=1 xor 6>8;
+--------------+
| 5>=1 xor 6>8 |
+--------------+
|            1 |
+--------------+
1 row in set (0.06 sec)mysql> 

4、表达式

​ 表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常可以得到一个值。与常量和变量一样,表达式的值也具有某种数据类型,可能的数据类型有字符类型、数值类型、日期时间类型。这样,根据表达式的值的类型,表达式可分为字符型表达式、数值型表达式和日期表达式。

mysql> select 5+5.1;
+-------+
| 5+5.1 |
+-------+
| 10.1  |
+-------+
1 row in set (0.02 sec)mysql> select 5+'A';
+-------+
| 5+'A' |
+-------+
|     5 |
+-------+
1 row in set (0.02 sec)mysql> select 'b'+'A';
+---------+
| 'b'+'A' |
+---------+
|       0 |
+---------+
1 row in set (0.03 sec)mysql> select '2019-09-08'+1;
+----------------+
| '2019-09-08'+1 |
+----------------+
|           2020 |
+----------------+
1 row in set (0.06 sec)mysql> select '2019-09-08 12:00:00'+1;
+-------------------------+
| '2019-09-08 12:00:00'+1 |
+-------------------------+
|                    2020 |
+-------------------------+
1 row in set (0.04 sec)mysql> 

5、内置函数

例如:

函数类型示例
数学函数ABS()、SORT()
聚合函数COUNT()
字符串函数ASCI()、CHAR()
日期和时间函数NOW()、YEAR()
加密函数ENCODE()、ENCRYPT()
控制流程函数IF()、IFNULL()
格式化函数FORMAT()
类型转换函数CAST()
系统信息函数USER()、VERSION()
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.02 sec)mysql> select sqrt(16);
+----------+
| sqrt(16) |
+----------+
|        4 |
+----------+
1 row in set (0.03 sec)mysql> select ascii('A');
+------------+
| ascii('A') |
+------------+
|         65 |
+------------+
1 row in set (0.03 sec)mysql> select char(65);
+----------+
| char(65) |
+----------+
| A        |
+----------+
1 row in set (0.05 sec)mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-10-04 11:41:17 |
+---------------------+
1 row in set (0.02 sec)mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2023 |
+-------------+
1 row in set (0.02 sec)mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|           10 |
+--------------+
1 row in set (0.03 sec)mysql> select day(now());
+------------+
| day(now()) |
+------------+
|          4 |
+------------+
1 row in set (0.04 sec)mysql> select cast(X'41' as unsigned);
+-------------------------+
| cast(X'41' as unsigned) |
+-------------------------+
|                      65 |
+-------------------------+
1 row in set (0.02 sec)mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.04 sec)mysql> 

第三节 数据定义

一、数据库模式定义

1、创建数据库

​ 语法格式:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_nme [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

说明:语句中“[ ]”内为可选项。

db_name:数据库名

IF NO EXISTS:在建数据库前进行判断,只有该数据库目前尚不存在时才执行CREATE DATABASE操作。

DEFAULT:指定默认值

CHARACTER SET:指定数据库字符集(Charset)

charset_name:字符集名称。

COLLATE:指定字符集的校对规则,collation_name为校对规则名。

mysql> create database mydata;
Query OK, 1 row affected (0.01 sec)mysql> create database mydata;
1007 - Can't create database 'mydata'; database exists
mysql> create database IF NOT EXISTS mydata;
Query OK, 1 row affected (0.00 sec)mysql> 

2、选择数据库

USE db_name;
mysql> use mydata;
Database changed
mysql> 

3、修改数据库

​ 语法格式:

ALTER {DATABASE | SCHEMA} [db_name] alter_specification [,alter_specification] ...

其中alter_specification:[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

4、删除数据库

​ 语法格式:

DROP DATABASE [IF EXISTS] db_name

db_name是要删除的数据库名。可以使用IF EXISTS子句以避免删除不存在的数据库时出现MySQL错误信息。

mysql> drop database mydata;
Query OK, 0 rows affected (0.01 sec)mysql> drop database mydata;
1049 - Unknown database 'mydata'
mysql> drop database IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)mysql> 

5、查看数据库

SHOW [DATABASE|SCHEMAS]
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)mysql> 

二、表定义

数值类型
整数类型
浮点类型
INT
FLOAT DOUBLE DECIMAL
日期和时间类型
日期类型
日期时间类型
时间戳类型
DATE YYYY-MM-DD YEAR
DATETIME TIME
TIMESTAMP
字符串类型
固定长度类型
可变长度类型
文本类型
CHAR 最大长度255
VARCHAR 最大长度65535
TEXT

1、创建表

CREATE TABLE [IF NOT EXISTS] tbl_name(字段名1 数据类型[列级完整性约束条件][默认值][,字段名2 数据类型[列级完整性约束条件][默认值]][......][表级完整性约束])[ENGINE=引擎类型];
mysql> create database mydata;
Query OK, 1 row affected (0.00 sec)mysql> use mydata;
Database changed
mysql> create table student(-> Sno char(11) NOT NULL,-> Sname char(10) NOT NULL,-> Sage int-> );
Query OK, 0 rows affected (0.02 sec)-- 或者使用create table student(Sno char(11) NOT NULL,Sname char(10) NOT NULL,Sage int);进行创建mysql> 

2、添加字段

ALTER TABLE tb_name ADD [COLUMN] 新字段名 数据类型 [约束条件] [FIRST | AFTER 已有字段名]
mysql> alter table student add column Sbirthday datetime;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> 

3、修改字段

ALTER TABLE tb_name CHANGE [COLUMN] 原字段 新字段名 数据类型 [约束条件]ALTER TABLE tb_name ALTER [COLUMN] 字段名 {SET|DROP} DEFUALT;ALTER TABLE tb_name MODIFY [COLUMN] 字段名 数据类型 [约束条件] [FIRST | AFTER 已有字段名]
mysql> alter table student change column Sname Sname char(200);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> 

4、删除字段

ALTER TABLE tb_name DROP [COLUMN] 字段名;
mysql> alter table student drop column Sname;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> 

5、重命名表

ALTER TABLE 原表名 RENAME [TO] 新表名 RENAME TABLE 原表名1 TO 新表名1 [,原表名2 TO 新表名2]......;
mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.03 sec)mysql> alter table teacher rename to my_teacher;
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| my_teacher       |
| student          |
+------------------+
2 rows in set (0.05 sec)-- 修改方法2mysql> rename table my_teacher to teacher;
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.04 sec)mysql> 

6、删除表

DROP TABLE [IF EXISTS]1 [,表2]......;
mysql> drop table teacher;
Query OK, 0 rows affected (0.00 sec)mysql> 

7、查看表

SHOW TABLES[{FROM|IN} db_name];
mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| student          |
+------------------+
1 row in set (0.07 sec)mysql> show tables in mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.10 sec)mysql> 

8、查看表结构

SHOW COLUMNS {FROM|IN} tb_name [{FROM|IN} db_name];
mysql> show columns in student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)mysql> show columns in db in mysql;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.15 sec)mysql> 

三、索引定义

​ 可以理解成目录,在查找时有目录查找速度会加快。
在这里插入图片描述

索引
用途划分
普通索引 INDEX
唯一索引 UNIQUE
主键 PRIMARY KEY
全文索引 FULLTEXT 引擎MyISAM
聚簇索引 引擎InnoDB
列级索引
单列索引
组合索引

1、索引的创建

CREATE TABLE tbl_name[col_name data_type][CONSTRAINT index_name][UNIQUE][INDEX|KEY][index_name](index_col_name[length])[ASC|DESC]-- ↓常用↓
CREATE INDEX
CREATE [UNIQUE] INDEX index_name ON tbl_name(col_name[(length)][ASC|DESC],......)
-- ↑常用↑ALTER TABLE
ALTER TABLE tbl_name ADD[UNIQUE|DULLTEXT][INDEX|KEY][index_name](col_name[length][ASC|DESC],......)
mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)mysql> create index index_sage on student (Sage asc);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  | MUL | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.06 sec)mysql> 
-- 通过创建表时创建索引mysql> create table course (-> id int not null auto_increment,-> cname char(50) not null,-> primary key(id),-> index(cname)-> );
Query OK, 0 rows affected (0.01 sec)mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| course           |
| student          |
+------------------+
2 rows in set (0.03 sec)mysql> desc course;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| cname | char(50) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.06 sec)mysql> 
mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  | MUL | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)mysql> alter table student add index index_sbirth(Sbirthday);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  | MUL | NULL    |       |
| Sbirthday | datetime | YES  | MUL | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)mysql> 

2、索引的查看

SHOW {INDEX|INDEXS|KEYS} {FROM|IN} tbl_name [{FROM|IN} db_name]
mysql> show index in student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          1 | index_sage   |            1 | Sage        | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |               |
| student |          1 | index_sbirth |            1 | Sbirthday   | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)mysql> 

在这里插入图片描述

3、索引的删除

DROP INDEX index_name ON tbl_nameALTER TABLE tbl_name DROP INDEX index_name
mysql> drop index index_sage on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index in student;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          1 | index_sbirth |            1 | Sbirthday   | A         | NULL        | NULL     | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.03 sec)mysql> alter table student drop index index_sbirth;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index in student;
Empty setmysql> 

在这里插入图片描述

第四节 数据更新

一、插入数据

-- 重点
INSERT语法格式
INSERT INTO tb_name(column_list)
VALUES(value_list1)[,(value_list2)],
从数据查询中插入数据
INSERT INTO tb_namel(column_listl)
SELECT (column_list2)
FROM tb_name2 WHERE(condition)
注意:column_list1与clolumn_list2必须个数相同且数据类型一一对应
使用INSERT...SET语句插入部分列值数据
INSERT [INTO] tbl-name
SET col_name={expr | DEFAULT},***使用INSERT...SELECT语句插入子查询数据
INSERT[INTO]tbl_name[(col name,**-)]
SELECT ...
mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)mysql> insert into student (Sno,Sage,Sbirthday) values (-> '001',18,'2002-09-08 07:06:05'-> );
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 001 |   18 | 2002-09-08 07:06:05 |
+-----+------+---------------------+
1 row in set (0.05 sec)mysql> 
mysql> insert student set-> Sno='002',-> Sage=19,-> Sbirthday='2002-12-12 11:11:11'-> ;
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 001 |   18 | 2002-09-08 07:06:05 |
| 002 |   19 | 2002-12-12 11:11:11 |
+-----+------+---------------------+
2 rows in set (0.04 sec)mysql> 

二、删除数据

DELETE FROM tb_name [WHERE <CONDITION>];

​ 删除所以记录可以使用

TRUNCATE [TABLE] tb_name

注意:TRUNCATE是删除整个表,重新建一个表,在删除全部数据时候效率高于DELETE,DELETE是逐条删除。TRUNCATE无法恢复,AUTO_INCREMENT计数器重置。

mysql> insert into student (Sno,Sage,Sbirthday) values ('003',18,'2003-11-09 21:38:21');
Query OK, 1 row affected (0.00 sec)mysql> insert into student (Sno,Sage,Sbirthday) values ('004',17,'2002-11-09 04:31:11');
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 001 |   18 | 2002-09-08 07:06:05 |
| 002 |   19 | 2002-12-12 11:11:11 |
| 003 |   18 | 2003-11-09 21:38:21 |
| 004 |   17 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
4 rows in set (0.05 sec)mysql> delete from student where sage=18;
Query OK, 2 rows affected (0.00 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   17 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.03 sec)-- ==============================================================mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| course           |
| student          |
+------------------+
2 rows in set (0.02 sec)mysql> desc course;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| cname | char(50) | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.05 sec)mysql> insert into course (cname) values-> ('语文'),-> ('数学'),-> ('英语'),-> ('政治');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0mysql> select * from course;
+----+-------+
| id | cname |
+----+-------+
|  1 | 语文  |
|  2 | 数学  |
|  3 | 英语  |
|  4 | 政治  |
+----+-------+
4 rows in set (0.04 sec)mysql> delete from course;
Query OK, 4 rows affected (0.00 sec)mysql> select * from course;
Empty set-- 然后新增两条记录,发现id是从5、6开始的
mysql> insert into course (cname) values-> ('语文'),-> ('数学');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from course;
+----+-------+
| id | cname |
+----+-------+
|  5 | 语文  |
|  6 | 数学  |
+----+-------+
2 rows in set (0.04 sec)-- 用truncate计数器重置mysql> truncate course;
Query OK, 0 rows affected (0.00 sec)mysql> select * from course;
Empty setmysql> insert into course (cname) values-> ('语文'),-> ('数学');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from course;
+----+-------+
| id | cname |
+----+-------+
|  1 | 语文  |
|  2 | 数学  |
+----+-------+
2 rows in set (0.04 sec)mysql> 

三、修改数据

UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...][WHERE where_definition]
mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   17 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.02 sec)mysql> update student set sage=20 where Sno='004';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   20 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.04 sec)mysql> 

第五节 数据查询

一、SELECT语句

SELECT [ALL | DISTINCT | DISTINCTROW] select_expr, ... [FROM table_reference [,table_reference]...][WHERE  where_definition]     /*WHERE子句*/[GROUP BY[col_name | expr | position)[ASC | DESC], ...[WITH ROLLUP]]     /*GROUP BY子句*/
[HAVING where_definition]     /*HAVING子句*/
[ORDER BY{col_name | expr | position}[ASC | DESC], ...]     /*ORDER BY子句*/
[LIMIT{[offset,]row_count | row_count OFFSET offset}]     /*LIMIT子句*/
表4.2 SELECT语句中各子句的使用次序及说明
子句说明是否必须使用
SELECT返回的列或表达式
FROM从中检索数据的表仅在从表选择数据时使用
WHERE行级过滤
GROUP BY分组说明仅在从表选择数据时使用
HAVING组级说明
ORDER BY输出排序顺序
LIMIT要检索的行数

二、列的选择与指定

1、选择指定的列

SELECT classNo, department, className From tb_class;-- 选择所有列
SELECT * FORM tb_class;
mysql> desc student;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Sno       | char(11) | NO   |     | NULL    |       |
| Sage      | int(11)  | YES  |     | NULL    |       |
| Sbirthday | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)mysql> select Sbirthday from student;
+---------------------+
| Sbirthday           |
+---------------------+
| 2002-12-12 11:11:11 |
| 2002-11-09 04:31:11 |
+---------------------+
2 rows in set (0.04 sec)mysql> select * from student;
+-----+------+---------------------+
| Sno | Sage | Sbirthday           |
+-----+------+---------------------+
| 002 |   19 | 2002-12-12 11:11:11 |
| 004 |   20 | 2002-11-09 04:31:11 |
+-----+------+---------------------+
2 rows in set (0.06 sec)mysql> 

2、定义并使用列的别名

SELECT studentName as 姓名, sex as 性别, YEAR (NOW())-YEAR(birthday) as 年龄 from tb_student;
mysql> select-> sno as 学号,-> sage as 年龄,-> sbirthday as 出生日期-> from student;
+------+------+---------------------+
| 学号 | 年龄 | 出生日期            |
+------+------+---------------------+
| 002  |   19 | 2002-12-12 11:11:11 |
| 004  |   20 | 2002-11-09 04:31:11 |
+------+------+---------------------+
2 rows in set (0.05 sec)mysql> 

3、替换查询结果集中的数据

CASE
WHEN 条件1 THEN 表达式1WHEN 条件2 THEN 表达式2...
ELSE 表达式
END[AS] column_alias

4、计算列值

​ 使用SELECT语句对列进行查询时,在结果集中可以输出对列值计算后的值,其具体使用方法是将SELECT语句的语法项“select_expr”指定为对应列参与计算的表达式。

mysql> select * from rec;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002 | 001 |   1.2 | 170 |
|  4 | 002 | 002 |   1.5 | 130 |
|  5 | 003 | 001 |   1.2 | 110 |
|  6 | 003 | 002 |   1.5 |  60 |
+----+-----+-----+-------+-----+
6 rows in set (0.04 sec)mysql> select price*num as 销售额,gno,pno from rec;
+--------------------+-----+-----+
| 销售额             | gno | pno |
+--------------------+-----+-----+
| 360.00001430511475 | 001 | 001 |
|                300 | 001 | 002 |
|  204.0000081062317 | 002 | 001 |
|                195 | 002 | 002 |
| 132.00000524520874 | 003 | 001 |
|                 90 | 003 | 002 |
+--------------------+-----+-----+
6 rows in set (0.05 sec)mysql> 

5、聚合函数

表4.3 MySQL中常用聚合函数表
函数名说明
COUNT求组中项数,返回INT类型整数
MAX求最大值
MIN求最小值
SUM返回表达式中所有值的和
AVG求组中值的平均值
STD或STDDEV返回给定表达式中所有值得标准值
VARLANCE返回给定表达式中所有值得方差
GROUP_CONCAT返回由属于一组的列值连接组合而成的结果
BIT_AND逻辑或
BIR_OR逻辑与
BIT_XOR逻辑异或
-- 销售数量超过150的条数
mysql> select count(*) from rec where num>150;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.03 sec)-- 取某一列的最大值
mysql> select max(num*price),gno from rec;
+--------------------+-----+
| max(num*price)     | gno |
+--------------------+-----+
| 360.00001430511475 | 001 |
+--------------------+-----+
1 row in set (0.02 sec)-- 在MySQL5.7版本之后对group by进行了优化。默认启动改进之后的版本启动了ONLY_FULL_GROUP_BY模式。即ONLY_FULL_GROUP_BY是MySQL数据库提供的一个sql_mode,通过这个sql_mode来保证SQL语句“分组求最值”合法性的检查。这种模式采用了与Oracle、DB2等数据库的处理方式。即不允许select target list中出现语义不明确的列。
-- 解决方式:只要有聚合函数sum(),count(),max(),avg()等函数就需要用到group by
-- mysql> select version();
-- +-----------+
-- | version() |
-- +-----------+
-- | 5.5.29    |
-- +-----------+
-- 1 row in set (0.06 sec)mysql> select min(num*price),gno from rec;
+----------------+-----+
| min(num*price) | gno |
+----------------+-----+
|             90 | 001 |
+----------------+-----+
1 row in set (0.02 sec)mysql> select sum(num*price) as 总销售额 from rec;
+--------------------+
| 总销售额           |
+--------------------+
| 1281.0000276565552 |
+--------------------+
1 row in set (0.04 sec)mysql> select avg(num*price) as 平均销售额 from rec;
+--------------------+
| 平均销售额         |
+--------------------+
| 213.50000460942587 |
+--------------------+
1 row in set (0.05 sec)mysql> 

三、FROM子句与多表连接查询

1、交叉连接

2、内连接

FROM table1 [INNER] JOIN table2 ON 连接条件等值连接
FROM table1, [table2] ......
WHERE table1.字段 <比较运算符> table2.字段
-- 准备数据↓↓↓↓↓
mysql> create table gong(-> id int NOT NULL primary key AUTO_INCREMENT,-> gnum char(11) NOT NULL,-> gname varbinary(255) NOT NULL-> );
Query OK, 0 rows affected (0.00 sec)mysql> insert into gong (gnum,gname) values-> ('001','张三'),-> ('002','李四'),-> ('003','王五');
Query OK, 3 rows affected (2.04 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> insert into pro (pnum,pname) values-> ('001','产品1'),-> ('002','产品2');
Query OK, 2 rows affected (2.04 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from gong;
+----+------+-------+
| id | gnum | gname |
+----+------+-------+
|  2 | 001  | 张三  |
|  3 | 002  | 李四  |
|  4 | 003  | 王五  |
+----+------+-------+
3 rows in set (0.03 sec)mysql> select * from pro;
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  1 | 001  | 产品1 |
|  2 | 002  | 产品2 |
+----+------+-------+
2 rows in set (0.04 sec)-- 准备数据完成↑↑↑↑↑mysql> select * from gong,rec;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 张三  |  1 | 001 | 001 |   1.2 | 300 |
|  3 | 002  | 李四  |  1 | 001 | 001 |   1.2 | 300 |
|  4 | 003  | 王五  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 张三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  2 | 001 | 002 |   1.5 | 200 |
|  4 | 003  | 王五  |  2 | 001 | 002 |   1.5 | 200 |
|  2 | 001  | 张三  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  4 | 003  | 王五  |  3 | 002 | 001 |   1.2 | 170 |
|  2 | 001  | 张三  |  4 | 002 | 002 |   1.5 | 130 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  4 | 002 | 002 |   1.5 | 130 |
|  2 | 001  | 张三  |  5 | 003 | 001 |   1.2 | 110 |
|  3 | 002  | 李四  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  2 | 001  | 张三  |  6 | 003 | 002 |   1.5 |  60 |
|  3 | 002  | 李四  |  6 | 003 | 002 |   1.5 |  60 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
18 rows in set (0.10 sec)mysql> select * from gong join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 张三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 张三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
6 rows in set (0.05 sec)mysql> select * from gong,rec where gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 张三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 张三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
6 rows in set (0.10 sec)mysql> 

3、外连接

(1)左连接
FROM table1 [LEFT] JOIN table2 ON 连接条件
(2)右连接
FROM table1 [RIGHT] JOIN table2 ON 连接条件
mysql> select * from gong left join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 张三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 张三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
+----+------+-------+----+-----+-----+-------+-----+
6 rows in set (0.04 sec)-- 我再加一条记录004mysql> insert into rec values (7,'003','001',1.2,600);
Query OK, 1 row affected (2.03 sec)mysql> select * from gong left join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 张三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 张三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
|  4 | 003  | 王五  |  7 | 003 | 001 |   1.2 | 600 |
+----+------+-------+----+-----+-----+-------+-----+
7 rows in set (0.08 sec)-- 这个时候发现004没有,也就是说左连接的时候,在右里面有和它对应的就到一起,否则直接剔除mysql> select * from gong right join rec on gong.gnum=rec.gno;
+----+------+-------+----+-----+-----+-------+-----+
| id | gnum | gname | id | Gno | Pno | price | num |
+----+------+-------+----+-----+-----+-------+-----+
|  2 | 001  | 张三  |  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001  | 张三  |  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002  | 李四  |  3 | 002 | 001 |   1.2 | 170 |
|  3 | 002  | 李四  |  4 | 002 | 002 |   1.5 | 130 |
|  4 | 003  | 王五  |  5 | 003 | 001 |   1.2 | 110 |
|  4 | 003  | 王五  |  6 | 003 | 002 |   1.5 |  60 |
|NULL| NULL | NULL |  7 | 004 | 001 |   1.2 | 600 |
+----+------+-------+----+-----+-----+-------+-----+
7 rows in set (0.09 sec)mysql> 

四、WHERE子句与条件查询

查询条件操作符
比较=,<>,!=,<,<=,>,>=,!<,!> NOT+比较运算表达式
确定范围DETWEEN AND, NOT BETWEEN AND
确定集合IN, NOT IN
字符匹配LIKE, NOT LIKE
空值IS NULL, IS NOT NULL
多重条件AND, OR
mysql> select * from rec where num>200;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  1 | 001 | 001 |   1.2 | 300 |
|  7 | 003 | 001 |   1.2 | 600 |
+----+-----+-----+-------+-----+
2 rows in set (2.08 sec)mysql> select * from rec where num <= 200;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002 | 001 |   1.2 | 170 |
|  4 | 002 | 002 |   1.5 | 130 |
|  5 | 003 | 001 |   1.2 | 110 |
|  6 | 003 | 002 |   1.5 |  60 |
+----+-----+-----+-------+-----+
5 rows in set (0.05 sec)mysql> select * from rec where num between 150 and 300;
+----+-----+-----+-------+-----+
| id | Gno | Pno | price | num |
+----+-----+-----+-------+-----+
|  1 | 001 | 001 |   1.2 | 300 |
|  2 | 001 | 002 |   1.5 | 200 |
|  3 | 002 | 001 |   1.2 | 170 |
+----+-----+-----+-------+-----+
3 rows in set (0.05 sec)mysql> insert into pro (pnum,pname) values ('003','产品3'),('004','产品4');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0-- 查卖出去的mysql> select * from pro;
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  1 | 001  | 产品1 |
|  2 | 002  | 产品2 |
|  3 | 003  | 产品3 |
|  4 | 004  | 产品4 |
+----+------+-------+
4 rows in set (0.08 sec)mysql> select * from pro where pnum in (select distinct pno from rec);
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  1 | 001  | 产品1 |
|  2 | 002  | 产品2 |
+----+------+-------+
2 rows in set (0.10 sec)mysql> select * from pro where pnum not in (select distinct pno from rec);
+----+------+-------+
| id | pnum | pname |
+----+------+-------+
|  3 | 003  | 产品3 |
|  4 | 004  | 产品4 |
+----+------+-------+
2 rows in set (0.04 sec)mysql> insert into gong (gnum,gname) values ('004','赵云'),('005','赵雅芝'),('006','赵曼亿');
Query OK, 3 rows affected (2.02 sec)
Records: 3  Duplicates: 0  Warnings: 0-- 我们在这个表中找姓“赵”的都有谁mysql> select * from gong where gname like '赵%';
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
|  5 | 004  | 赵云   |
|  6 | 005  | 赵雅芝 |
|  7 | 006  | 赵曼亿 |
+----+------+--------+
3 rows in set (0.04 sec)mysql> insert into gong (gnum) values ('007');
Query OK, 1 row affected (0.00 sec)mysql> select * from gong;
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
|  2 | 001  | 张三   |
|  3 | 002  | 李四   |
|  4 | 003  | 王五   |
|  5 | 004  | 赵云   |
|  6 | 005  | 赵雅芝 |
|  7 | 006  | 赵曼亿 |
| 10 | 007  | NULL   |
+----+------+--------+
7 rows in set (0.10 sec)mysql> select * from gong where gname is null;
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
| 10 | 007  | NULL   |
+----+------+--------+
1 rows in set (0.10 sec)mysql> update gong set gname='张飞' where gnum=007;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from gong;
+----+------+--------+
| id | gnum | gname  |
+----+------+--------+
|  2 | 001  | 张三   |
|  3 | 002  | 李四   |
|  4 | 003  | 王五   |
|  5 | 004  | 赵云   |
|  6 | 005  | 赵雅芝 |
|  7 | 006  | 赵曼亿 |
| 10 | 007  | 张飞   |
+----+------+--------+
7 rows in set (0.06 sec)-- 姓“张”的并且卖出货的有谁mysql> select * from gong where gname like '张%' and gnum in (select distinct gnum from rec);
+----+------+-------+
| id | gnum | gname |
+----+------+-------+
|  2 | 001  | 张三  |
+----+------+-------+
1 rows in set (0.04 sec)mysql> 

子查询

IN语法:

WHERE 列名 IN (值列表或者子SELECT[但必须只有一列])

比较运算符:

WHERE 列名 <比较运算符> (值或者子SELECT[但必须只有一值])

EXIST查询

WHERE EXIST (SELECT)

五、GROUP BY子句与分组数据

语法:

[GROUP BY 字段列表][HAVING<条件表达式>]

含义:按照某一列的值相同的分成一组,进行聚合计算

mysql> select (price*num) as 销售额,rec.*from rec;
+--------------------+----+-----+-----+-------+-----+
| 销售额             | id | Gno | Pno | price | num |
+--------------------+----+-----+-----+-------+-----+
| 360.00001430511475 |  1 | 001 | 001 |   1.2 | 300 |
|                300 |  2 | 001 | 002 |   1.5 | 200 |
|  204.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
|                195 |  4 | 002 | 002 |   1.5 | 130 |
| 132.00000524520874 |  5 | 003 | 001 |   1.2 | 110 |
|                 90 |  6 | 003 | 002 |   1.5 |  60 |
|  720.0000286102295 |  7 | 003 | 001 |   1.2 | 600 |
+--------------------+----+-----+-----+-------+-----+
7 rows in set (2.08 sec)mysql> select sum(price*num) as 销售额,rec.* from rec group by gno;
+-------------------+----+-----+-----+-------+-----+
| 销售额            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
3 rows in set (0.05 sec)mysql> 

六、HAVING子句

​ 在SELECT语句中,除了能使用GROUP BY子句分组数据之外,还可以使用HAVING子句来过滤分组,即在结果集中规定包含哪些分组和排除哪些分组。

-- 销售额超过400
mysql> select sum(price*num) as 销售额,rec.* from rec group by gno having 销售额>400;
+-------------------+----+-----+-----+-------+-----+
| 销售额            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
2 rows in set (0.05 sec)mysql> 

七、ORDER BY子句

语法:

[ORDER BY 字段1 [ASC|DESC][,字段2[ASC|DESC]]......
-- 排序
mysql> select sum(price*num) as 销售额,rec.* from rec group by gno having 销售额>400 order by gno desc;
+-------------------+----+-----+-----+-------+-----+
| 销售额            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
+-------------------+----+-----+-----+-------+-----+
2 rows in set (0.04 sec)mysql> 

八、LIMIT子句

LIMIT [位置偏移量,] 行数
mysql> select sum(price*num) as 销售额,rec.* from rec group by gno;
+-------------------+----+-----+-----+-------+-----+
| 销售额            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
3 rows in set (0.04 sec)mysql> select sum(price*num) as 销售额,rec.* from rec group by gno limit 0,3;
+-------------------+----+-----+-----+-------+-----+
| 销售额            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 660.0000143051147 |  1 | 001 | 001 |   1.2 | 300 |
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
3 rows in set (0.06 sec)mysql> select sum(price*num) as 销售额,rec.* from rec group by gno limit 1,3;
+-------------------+----+-----+-----+-------+-----+
| 销售额            | id | Gno | Pno | price | num |
+-------------------+----+-----+-----+-------+-----+
| 399.0000081062317 |  3 | 002 | 001 |   1.2 | 170 |
| 942.0000338554382 |  5 | 003 | 001 |   1.2 | 110 |
+-------------------+----+-----+-----+-------+-----+
2 rows in set (0.08 sec)mysql> 

第六节 视图

​ 视图是从一个或多个表(或视图)导出的表。视图是数据库的用户使用数据库的观点。例如,对于一个学校,其学生的情况存于数据库的一个或多个表中,而作为学校的不同职能部门,所关心的学生数据的内容是不同的。视图是一张虚表。视图一经定义以后,就可以像表一样被查询、修改、删除和更新。

一、创建视图

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]
mysql> create OR REPLACE view v_gname as select gname from gong;
Query OK, 0 rows affected (2.02 sec)mysql> create OR REPLACE view v_rec_gong as select gname,rec.* from gong,rec where gong.gnum=rec.gno;
Query OK, 0 rows affected (0.00 sec)mysql> 

二、删除视图

DROP VIEW [IF EXISTS]view_name [,view_name]...
mysql> drop view IF EXISTS v_gname;
Query OK, 0 rows affected (2.04 sec)mysql> 

三、修改视图定义

语法格式:

ALTER VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

四、查看视图定义

语法格式:

SHOW CREATE VIEW view_name
mysql> show create view v_rec_gong;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View       | Create View                                                                                                                                                                                                                                                                                              | character_set_client | collation_connection |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_rec_gong | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_rec_gong` AS select `gong`.`gname` AS `gname`,`rec`.`id` AS `id`,`rec`.`Gno` AS `Gno`,`rec`.`Pno` AS `Pno`,`rec`.`price` AS `price`,`rec`.`num` AS `num` from (`gong` join `rec`) where (`gong`.`gnum` = `rec`.`Gno`) | utf8mb4              | utf8mb4_general_ci   |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.05 sec)mysql> 

在这里插入图片描述

五、更新视图数据

​ 要通过视图更新基本表数据,必须保证视图是可更新视图,即可以在INSERT,UPDATE或DELETE等语句当中使用它们。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。

强烈不建议更新视图,更新视图风险性非常大,更新不好的话影响到基本表,要用基本表取更新视图。

六、查询视图数据

​ 视图一经定义后,就可以如同查询数据库中的真实表一样,对视图进行数据查询检索,这也是对视图使用最多的一种操作。

mysql> create OR REPLACE view v_game as select gnum,gname from gong;
Query OK, 0 rows affected (0.00 sec)mysql> select * from v_game;
+------+--------+
| gnum | gname  |
+------+--------+
| 001  | 张三   |
| 002  | 李四   |
| 003  | 王五   |
| 004  | 赵云   |
| 005  | 赵雅芝 |
| 006  | 赵曼亿 |
| 007  | 张飞   |
+------+--------+
7 rows in set (0.11 sec)mysql> select * from v_game where gname like '赵%';
+------+--------+
| gnum | gname  |
+------+--------+
| 004  | 赵云   |
| 005  | 赵雅芝 |
| 006  | 赵曼亿 |
+------+--------+
3 rows in set (0.04 sec)mysql> 

附上mydata.sql

/*Navicat Premium Data TransferSource Server         : 00Source Server Type    : MySQLSource Server Version : 50529Source Host           : localhost:3306Source Schema         : mydataTarget Server Type    : MySQLTarget Server Version : 50529File Encoding         : 65001Date: 04/10/2023 18:27:33
*/create database IF NOT EXISTS mydata;
USE mydata;SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (`id` int(11) NOT NULL AUTO_INCREMENT,`cname` char(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `cname`(`cname`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = FIXED;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文');
INSERT INTO `course` VALUES (2, '数学');-- ----------------------------
-- Table structure for gong
-- ----------------------------
DROP TABLE IF EXISTS `gong`;
CREATE TABLE `gong`  (`id` int(11) NOT NULL AUTO_INCREMENT,`gnum` char(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,`gname` varbinary(255) NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of gong
-- ----------------------------
INSERT INTO `gong` VALUES (2, '001', 0xE5BCA0E4B889);
INSERT INTO `gong` VALUES (3, '002', 0xE69D8EE59B9B);
INSERT INTO `gong` VALUES (4, '003', 0xE78E8BE4BA94);
INSERT INTO `gong` VALUES (5, '004', 0xE8B5B5E4BA91);
INSERT INTO `gong` VALUES (6, '005', 0xE8B5B5E99B85E88A9D);
INSERT INTO `gong` VALUES (7, '006', 0xE8B5B5E69BBCE4BABF);
INSERT INTO `gong` VALUES (10, '007', 0xE5BCA0E9A39E);-- ----------------------------
-- Table structure for pro
-- ----------------------------
DROP TABLE IF EXISTS `pro`;
CREATE TABLE `pro`  (`id` int(11) NOT NULL AUTO_INCREMENT,`pnum` char(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,`pname` varbinary(255) NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of pro
-- ----------------------------
INSERT INTO `pro` VALUES (1, '001', 0xE4BAA7E5938131);
INSERT INTO `pro` VALUES (2, '002', 0xE4BAA7E5938132);
INSERT INTO `pro` VALUES (3, '003', 0xE4BAA7E5938133);
INSERT INTO `pro` VALUES (4, '004', 0xE4BAA7E5938134);-- ----------------------------
-- Table structure for rec
-- ----------------------------
DROP TABLE IF EXISTS `rec`;
CREATE TABLE `rec`  (`id` int(11) NOT NULL,`Gno` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,`Pno` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,`price` float NULL DEFAULT NULL,`num` int(11) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = DYNAMIC;-- ----------------------------
-- Records of rec
-- ----------------------------
INSERT INTO `rec` VALUES (1, '001', '001', 1.2, 300);
INSERT INTO `rec` VALUES (2, '001', '002', 1.5, 200);
INSERT INTO `rec` VALUES (3, '002', '001', 1.2, 170);
INSERT INTO `rec` VALUES (4, '002', '002', 1.5, 130);
INSERT INTO `rec` VALUES (5, '003', '001', 1.2, 110);
INSERT INTO `rec` VALUES (6, '003', '002', 1.5, 60);
INSERT INTO `rec` VALUES (7, '003', '001', 1.2, 600);-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (`Sno` char(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,`Sage` int(11) NULL DEFAULT NULL,`Sbirthday` datetime NULL DEFAULT NULL
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = FIXED;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('002', 19, '2002-12-12 11:11:11');
INSERT INTO `student` VALUES ('004', 20, '2002-11-09 04:31:11');-- ----------------------------
-- View structure for v_game
-- ----------------------------
DROP VIEW IF EXISTS `v_game`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_game` AS select gnum,gname from gong ;-- ----------------------------
-- View structure for v_rec_gong
-- ----------------------------
DROP VIEW IF EXISTS `v_rec_gong`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_rec_gong` AS select gname,rec.* from gong,rec where gong.gnum=rec.gno ;SET FOREIGN_KEY_CHECKS = 1;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/149057.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

目标检测算法改进系列之Backbone替换为FocalNet

FocalNet 近些年&#xff0c;Transformers在自然语言处理、图像分类、目标检测和图像分割上均取得了较大的成功&#xff0c;归根结底是自注意力&#xff08;SA &#xff1a;self-attention&#xff09;起到了关键性的作用&#xff0c;因此能够支持输入信息的全局交互。但是由于…

常见的锁策略

目录 一.乐观锁 vs 悲观锁 二.轻量级锁 vs 重量级锁 三.自旋锁 vs 挂起等待锁 四.互斥锁 vs 读写锁 五.可重入锁 vs不可重入锁 一.乐观锁 vs 悲观锁 悲观锁 : 总是假设最坏的情况&#xff0c;每次去拿数据的时候都认为别人会修改&#xff0c;所以每次在拿数据的时候都会上锁…

C++面试题准备

文章目录 一、线程1.什么是进程&#xff0c;线程&#xff0c;彼此有什么区别?2.多进程、多线程的优缺点3.什么时候用进程&#xff0c;什么时候用线程4.多进程、多线程同步&#xff08;通讯&#xff09;的方法5.父进程、子进程的关系以及区别6.什么是进程上下文、中断上下文7.一…

基于SpringBoot的视频网站系统

目录 前言 一、技术栈 二、系统功能介绍 用户信息管理 视频分享管理 视频排名管理 交流论坛管理 留言板管理 三、核心代码 1、登录模块 2、文件上传模块 3、代码封装 前言 使用旧方法对视频信息进行系统化管理已经不再让人们信赖了&#xff0c;把现在的网络信息技术运…

YOLOv8改进算法之添加CA注意力机制

1. CA注意力机制 CA&#xff08;Coordinate Attention&#xff09;注意力机制是一种用于加强深度学习模型对输入数据的空间结构理解的注意力机制。CA 注意力机制的核心思想是引入坐标信息&#xff0c;以便模型可以更好地理解不同位置之间的关系。如下图&#xff1a; 1. 输入特…

C++(STL容器适配器)

前言&#xff1a; 适配器也称配接器&#xff08;adapters&#xff09;在STL组件的灵活组合运用功能上&#xff0c;扮演着轴承、转换器的角色。 《Design Patterns》对adapter的定义如下&#xff1a;将一个class的接口转换为另一个class的接口&#xff0c;使原本因接口不兼容而…

ParagonNTFSforMac_15.5.102中文版最受欢迎的NTFS硬盘格式读取工具

Paragon NTFS for Mac是一款可以为您轻松解决Mac平台上不能识别Windows通用的NTFS文件难题&#xff0c;这是一款强大的Mac读写工具&#xff0c;相信在很多时候&#xff0c;Mac用户需要对NTFS文件的移动硬盘进行写入&#xff0c;但是macOS系统默认是不让写入的&#xff0c;使用小…

【gitlab】本地项目上传gitlab

需求描述 解决方法 下面的截图是gitlab空项目的描述 上传一个本地项目按其中“Push an existing folder”命令即可。 以renren-fast项目为例 # 用git bash 下载renren-fast项目 git clone https://gitee.com/renrenio/renren-fast.git# 在renren-fast的所属目录 打开git ba…

项目进展(八)-编写代码,驱动ADS1285

一、代码 根据芯片的数据手册编写部分驱动&#xff0c;首先看部分引脚的波形&#xff1a; DRDY: CS&#xff1a; 首先在代码初始化时连续写入三个寄存器&#xff1a; void WriteReg(uint8_t startAddr, uint8_t *regData, uint8_t number) {uint8_t i0;// 循环写number1次…

GEE16: 区域日均降水量计算

Precipitation 1. 区域日均降水量计算2. 降水时间序列3. 降水数据年度时间序列对比分析 1. 区域日均降水量计算 今天分析一个计算区域日均降水量的方法&#xff1a; 数据信息&#xff1a;   Climate Hazards Group InfraRed Precipitation with Station data (CHIRPS) is a…

MySQL:数据库的物理备份和恢复-冷备份(3)

介绍 物理备份&#xff1a; 直接复制数据文件进行的备份 优点&#xff1a;不需要其他的工具&#xff0c;直接复制就好&#xff0c;恢复直接复制备份文件即可 缺点&#xff1a;与存储引擎有关&#xff0c;跨平台能力较弱 逻辑备份&#xff1a; 从数据库中导出数据另存而进行的备…

后端面经学习自测(二)

文章目录 1、Http1.1和2.0的区别大概是什么&#xff1f;HTTP & HTTPS 2、HTTP&#xff0c;用户后续的操作&#xff0c;服务端如何知道属于同一个用户cookie & session & token手机验证码登录流程SSO单点登录 3、如果服务端是一个集群机器&#xff1f;4、hashmap是线…

Postman使用实例

Postman使用实例 实体类Emp package com.example.springboot_postman.pojo;import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor;import javax.persistence.*; import j…

map和set

1. 关联式容器 我们接触过STL中的部分容器&#xff0c;比如&#xff1a;vector、list、deque、 forward_list(C11)等&#xff0c;这些容器统称为序列式容器&#xff0c;因为其底层为线性序列的数据结构&#xff0c;里面存储的是元素本身。 那什么是关联式容器&#xff1f;它与…

【AI视野·今日Robot 机器人论文速览 第四十二期】Wed, 27 Sep 2023

AI视野今日CS.Robotics 机器人学论文速览 Wed, 27 Sep 2023 Totally 48 papers &#x1f449;上期速览✈更多精彩请移步主页 Interesting: &#x1f4da;***Tactile Estimation of Extrinsic Contact,基于触觉的外部接触估计与稳定放置 (from 三菱电机) &#x1f4da;充气型…

Pikachu靶场——远程命令执行漏洞(RCE)

文章目录 1. RCE1.1 exec "ping"1.1.1 源代码分析1.1.2 漏洞防御 1.2 exec "eval"1.2.1 源代码分析1.2.2 漏洞防御 1.3 RCE 漏洞防御 1. RCE RCE(remote command/code execute)概述&#xff1a; RCE漏洞&#xff0c;可以让攻击者直接向后台服务器远程注入…

【Java 进阶篇】JDBC数据库连接池Druid详解

在Java应用程序中&#xff0c;与数据库进行交互是一个常见的任务。为了更有效地管理数据库连接并提高性能&#xff0c;数据库连接池是一种常见的解决方案。Druid是一个流行的JDBC数据库连接池&#xff0c;它具有丰富的功能和高性能。本博客将详细介绍Druid连接池&#xff0c;包…

代数科学计算:LiveMath Maker v3.6.0 cRACK

LiveMath Maker 是一个数学计算机程序&#xff0c;您可以使用它来制作(并探索、实验和创建)LiveMath。您和LiveMath Maker制作的任何LiveMath都可以通过互联网与世界共享。 用LiveMath Maker 来进行数学研究&#xff0c;函数学习&#xff0c;深入探索、实验和创建你的LiveMath…

C++11新特性(语法糖,新容器)

距离C11版本发布已经过去那么多年了&#xff0c;为什么还称为新特性呢&#xff1f;因为笔者前面探讨的内容&#xff0c;除了auto&#xff0c;范围for这些常用的&#xff0c;基本上是用着C98的内容&#xff0c;虽说C11已经发布很多年&#xff0c;却是目前被使用最广泛的版本。因…

数据库的备份与恢复

数据备份的重要性 备份的主要目的是灾难恢复。 在生产环境中&#xff0c;数据的安全性至关重要。 任何数据的丢失都可能产生严重的后果。 造成数据丢失的原因&#xff1a; 程序错误人为操作错误运算错误磁盘故障灾难&#xff08;如火灾、地震&#xff09;和盗窃 数据库备份…