1.数据库存储数据的特点
数据存放到表中，然后表再放到库中
一个库中可以有多张表，每张表具有唯一的表名用来标识自己
表中有一个或多个列，列又称为字段，相当于java中的属性
表中每一行数据，相当于java中的对象
2.数据库基础概述
l所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库;
l为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据;
l数据库服务器、数据库和表的关系如图所示：
一 一 一 一 一 一
创建
MYSQL
DB
DB
表
表
表
数据库常见概念
DB：数据库，存储数据的容器
DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言
第二章 MySQL应用--数据库
创建数据库
CREATE DATABASE [IF NOT EXISTS] <数据库名> [[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>]; [ ]中的内容是可选的。语法说明如下： <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。 IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。 [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。 [DEFAULT] COLLATE：指定字符集的默认校对规则。 校对规则名: utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin
•MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。后面我们会单独讲解 MySQL 的字符集和校对规则。
实例1：最简单的创建 MySQL 数据库的语句
在 MySQL 中创建一个名为 test_db 的数据库。在 MySQL 命令行客户端输入 SQL 语句CREATE DATABASE test_db;即可创建一个数据库，输入的 SQL 语句与执行结果如下。
mysql> CREATE DATABASE test_db; Query OK, 1 row affected (0.12 sec);
“Query OK, 1 row affected (0.12 sec);”提示中，“Query OK”表示上面的命令执行成功，“1 row affected”表示操作只影响了数据库中一行的记录，“0.12 sec”则记录了操作执行的时间。
若再次输入CREATE DATABASE test_db;语句，则系统会给出错误提示信息，如下所示：
mysql> CREATE DATABASE test_db; ERROR 1007 (HY000): Can't create database 'test_db'; database exists
提示不能创建“test_db”数据库，数据库已存在。MySQL 不允许在同一系统下创建两个相同名称的数据库。
可以加上IF NOT EXISTS从句，就可以避免类似错误，如下所示：
mysql> CREATE DATABASE IF NOT EXISTS test_db; Query OK, 1 row affected (0.12 sec)
实例2：创建 MySQL 数据库时指定字符集和校对规则
使用 MySQL 命令行工具创建一个测试数据库，命名为 test_db_char，指定其默认字符集为 utf8，默认校对规则为 utf8_chinese_ci（简体中文，不区分大小写），输入的 SQL 语句与执行结果如下所示：
CREATE DATABASE IF NOT EXISTS text_bd_char DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci //Query OK, 1 row affected (0.03 sec)
这时，可以使用SHOW CREATE DATABASE查看 test_db_char 数据库的定义声明，发现该数据库的指定字符集为 utf8，运行结果如下所示：
mysql> SHOW CREATE DATABASE text_bd_char; +--------------+-----------------------------------------------------+ | Database | Create Database | +--------------+-----------------------------------------------------+ | test_db_char | CREATE DATABASE `text_bd_char` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ +--------------+-----------------------------------------------------+ 1 row in set (0.00 sec)
“1 row in set (0.00 sec)”表示集合中有 1 行信息，处理时间为 0.00秒。时间为 0.00 秒并不代表没有花费时间，而是时间非常短，小于 0.01 秒。
-
CREATE TABLE `table1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1', `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;
COLLATE是用来做什么的？
使用phpmyadmin的开发可能会非常眼熟，因为其中的中文表头已经给出了答案： 
所谓utf8_unicode_ci，其实是用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。
各种COLLATE的区别
COLLATE通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认值为utf8mb4_general_ci。
这里顺便讲个题外话，mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。
很多COLLATE都带有ci字样，这是Case Insensitive的缩写，即大小写无关，也就是说”A”和”a”在排序和比较的时候是一视同仁的。selection * from table1 where field1=”a”同样可以把field1为”A”的值选出来。与此同时，对于那些cs后缀的COLLATE，则是Case Sensitive，即大小写敏感的。
在mysql中使用show collation指令可以查看到mysql所支持的所有COLLATE。以utf8mb4为例，该编码所支持的所有COLLATE如下图所示。 
mysql中和utf8mb4相关的所有COLLATE
图中我们能看到很多国家的语言自己的排序规则。在国内比较常用的是utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin这三个。我们来探究一下这三个的区别：
首先utf8mb4_bin的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的。
而utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。对于我们开发的国内使用的系统来说，随便选哪个都行。只是对于某些西方国家的字母来说，utf8mb4_unicode_ci会比utf8mb4_general_ci更符合他们的语言习惯一些，general是mysql一个比较老的标准了。例如，德语字母“ß”，在utf8mb4_unicode_ci中是等价于”ss”两个字母的（这是符合德国人习惯的做法），而在utf8mb4_general_ci中，它却和字母“s”等价。不过，这两种编码的那些微小的区别，对于正常的开发来说，很难感知到。本身我们也很少直接用文字字段去排序，退一步说，即使这个字母排错了一两个，真的能给系统带来灾难性后果么？从网上找的各种帖子讨论来说，更多人推荐使用utf8mb4_unicode_ci，但是对于使用了默认值的系统，也并没有非常排斥，并不认为有什么大问题。结论：推荐使用utf8mb4_unicode_ci，对于已经用了utf8mb4_general_ci的系统，也没有必要花时间改造。
另外需要注意的一点是，从mysql 8.0开始，mysql默认的CHARSET已经不再是Latin1了，改为了utf8mb4（参考链接MySQL :: MySQL 8.0 Reference Manual :: 10.5 Configuring Application Character Set and Collation），并且默认的COLLATE也改为了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci大体上就是unicode的进一步细分，0900指代unicode比较算法的编号（ Unicode Collation Algorithm version），ai表示accent insensitive（发音无关），例如e, è, é, ê 和 ë是一视同仁的。
COLLATE设置级别及其优先级
设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。 库级别设置COLLATE的语句如下：
CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。
表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：
CREATE TABLE ( …… ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 12345
如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。
列级别的设置，则在CREATE TABLE中声明列的时候指定，例如
CREATE TABLE ( `field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '', …… ) …… 1234
如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。
最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：
SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1; SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci; 12
如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置.也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。
以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。
修改、备份、恢复数据库
ALTER DATABASE db_name [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name
l1、utf8_unicode_ci 和 utf8_general_ci 对中、英文来说没有实质的差别； 2、utf8_general_ci 校对速度快，但准确度稍差； 3、utf8_unicode_ci 准确度高，但校对速度稍慢； 4、如果应用有德语、法语或者俄语，请一定使用utf8_unicode_ci；否则 utf8_general_ci 就够了，到现在也没发现问题； 5、通常情况下 utf8_general_ci 的准确性就够用了，很多程序源码中用的也是 utf8_general_ci；
练习
查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;
/**/ SHOW CREATE DATABASE text_bd_char /*修改数据库*/ ALTER DATABASE text_bd_char CHARACTER set utf8
第二章 数据表
创建表
CREATE TABLE table_name ( field1 datatype DEFAULT NULL, field2 datatype COMMENT NULL, field3 datatype, )character set 字符集 collate 校对规则 field：指定列名 datatype：指定列类型 DEFAULT NULL 默认值 COMMENT NULL 注释
注意：
1.创建表前，要先使用use db语句使用库。
2.创建表时，要根据需要保存的数据创建相应的列，并根据数据定义相应的列类型，实现添加自增长语句,主键字段后加auto_increment(只适用MySQL) 。
练习
user对象：
user{
id int
name string
password string
birthday date
}
create table user ( id num, name varchar, password varchar, brithday datetime, ) character set utf8 collate utf8mb4_unicode_ci
MySQL常用数据类型
MySQL中常用的数据类型
| 数据类型 | 类型举例 |
|---|---|
| 整型 | TINYINT,SMALLINT,INT.... |
| 浮点型 | FLOAT,DOUBLE |
| 定点数类型 | DECIMAL |
| 日期时间类型 | DATE,TIME,YEAR,DATETIMA... |
| 文本字符串类型 | CHAR(M),VARCHAR(M),TEXT... |
| 枚举类型 | ENUE |
整型
整数类型一共有 5 种，包括 TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT （INTEGER）和 BIGINT 。这5种类型的区别就是他们的 取值范围不同 。
整型类型取值范围表
| 整数类型 | 字节 | 有符号的取值范围 | 无符号的取值范围 |
|---|---|---|---|
| TINYINT | 1 | -128~127 | 0~255 |
| SMALLINT | 2 | -32768~2767 | 0~655535 |
| MEDIUMINT | 3 | -8388608~388607 | 0~16777215 |
| INT、INTEGER | 4 | -2147483648~2147483647 | 0~4294967295 |
| BIGINT | 8 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
整型声明
原始整型的声明：
CREATE TABLE t_date_type( id TINYINT, age INT )
在声明整型的时候，我们还可以为其添加一些属性来修饰，以达到某些限制。
整型属性
整型常见的属性：
| MYSQL关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含HULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用于整数类型 |
| UNSIGINED | 无符号，声明了此关键字，此字段最小值为0，即：非负数。则如果在声明表字段时，明确知道该字段非负数，则添加此字段。如id |
| CHARACTER SET NAME | 指定一个字符集 |
举例：带有属性的整型类型的声明：
*属性 UNSIGNED*
UNSIGNED:无符号类型，声明了此关键字，此字段最小值为0，即：非负数。则如果在声明表字段时，明确知道该字段非负数，则添加此字段。如id。
CREATE TABLE t_date_type( age INT UNSIGNED //为字段添加属性 )
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int unsigned | Yes | (Null) |
整型的选择
由于MySQL中整型类型很多，如何选择哪一个呢？下面给出大概使用范围：
TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
INT 、 INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，
用得最多。比如商品编号。
BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、
大型门户网站点击量等。
在实际生成中，需要考虑存储空间和可靠性，整型数据类型有不同的取值存储范围，取值范围字节数小的节省空间毋庸置疑，但是如果一味追求节省空间，将整型类型定义小了，导致使用存储时超出了存储范围，引起系统故障就得不偿失了，所以要权衡利弊，用内存空间可以换来可靠性。
浮点型
浮点数的特点是可以 处理小数， 你可以把整数看成小数的一个特例。因此浮点数使用的范围要比整型更广些，MySQL支持的浮点数类型为：FLOAT , DOUBLE , REAL。
FLOAT 表示 单精度 浮点数；
DOUBLE 表示 双精度 浮点数；
两者取值范围
| 整数类型 | 字节 | 有符号的取值范围 | 无符号的取值范围 |
|---|---|---|---|
| FLOAT | 4 | (-3.402823466 E+38,-1.175494351 E-38),0(1.175494351 E-38,3.402823466 E+38) | 0,(1.175494351 E-38,3.402823466 E+38) |
| DOUBLE | 8 | (-1.797631348623157E+308,-2.2250738585072014E-308),0,(-2.2250738585072014E-308,-1.797631348623157E+308) | 0,(-2.2250738585072014E-308,-1.797631348623157E+308) |
如果声明时使用REAL，那么默认就是 DOUBLE类型。如果你把 SQL 模式设定为：REAL_AS_FLOAT ”，那 么，MySQL 就认为
REAL 是 FLOAT 。启用 “REAL_AS_FLOAT”的方式：
*SET sql_mode = "REAL_AS_FLOAT";*
需要注意：
从 MySQL 8.0.17 开始， FLOAT(M,D) 和 DOUBLE(M,D) 用法在官方文档中已经明确不推荐使用 ，将来可 能被移除。另外，关于浮点型FLOAT 和 DOUBLE 的 UNSIGNED（无符号） 也不推荐使用了，将来也可能被移除。将由(DECIMAL)定点数替代。
*浮点型精度偏差说明*
CREATE TABLE test_float( num FLOAT ); INSERT INTO test_float VALUES(0.25),(0.44),(0.41); #求和，结果应该为1.1 SELECT SUM(num) FROM test_float;#1.0999999940395355（存在精度偏差） ********************** CREATE TABLE test_double( num DOUBLE ) INSERT INTO test_double VALUES(0.25),(0.44),(0.41) SELECT SUM(num) FROM test_double#1.0999999999999999
结果显然有误差，浮点类型那是因为底层存储二级制时，浮点数据无法用一个二进制数来精确表达，只能在取值允许的范围内进行四舍五入。
因此我们要避免使用"="号来判断两数是否相等。要想使用精度更高的，应该使用DECIMAL。
定点数类型
MySQL中定点数就只有一种：DECIMAL。
定义：DECIMAL(M,D) ,其中：D称为精度，M称为标度。
0<=M<=65 , 0<=D<=30 。
例如，定义 DECIMAL （5,2）的类型，表示该列取值范围是 -999.99~999.99 。
DECIMAL（M，D）有效范围由M和D决定。最大取值范围和DOUBLE一样。
DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。
浮点数和定点数的区别
\1. 浮点数 相对于定点数的优点是在 长度一定 的情况下， 浮点类型取值范围大 ，但是 不精准 ，适用 于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动 力学等）。
\2. 定点数 类型取值 范围相对小 ，但是 精准 ，没有误差，适合于对精度要求极高的场景 （比如涉 及金额计算的场景）。
时间日期类型
时间日期类型是很常用的一个类型，MySQL有多种表示日期和时间的数据类型。
不同的版本可能有所差异，MySQL8.0版本支持的日期和时间。
类型主要有：DATE类型、TIME类型、DATETIME类型和YEAR类型。
DATE： 类型通常用来表示年、月、日。
TIME： 类型通常用来表示时、分、秒。
DATETIME ： 类型通常用来表示年、月、日、时、分、秒。
YEAR： 类型通常用来表示年。
DATE类型
CREATE TABLE test_date(
h_date DATE
);
INSERT INTO test_date()
VALUES('2022-02-02'),('2022-02-02'),('22-02-02'),('70-02-02'),('220202'),('700202');
SELECT * FROM test_date;
| h_date |
|---|
| 2022-02-02 |
| 2022-02-02 |
| 2022--02-02 |
| 1970-02-02 |
| 2022-02-02 |
| 1970-02-02 |
DATE 类型 表示日期 ，没有时间部分，格式为 YYYY - MM - DD ，其中， YYYY 表示年份， MM 表示月份， DD 表示日期。
DATE最小取值为 1000-01-01 ，最大取值为 9999-12-03。
1.在插入数据时，可以用 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
2.以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期。
此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份 取值为00到69 时，会被 转化为2000到2069 ；当年份 取值为70到99时 ，会被 转化为1970到1999。
**使用 CURRENT_DATE() ，会返回当前的年月日。
| CURRENT_DATE() |
|---|
| 2022-02-02 |
TIME类型
CREATE TABLE test_time(
cr_time TIME
);
INSERT INTO test_time
VALUES('10:28:56'),('1 10:28:56'),('28:56'),('1 28:56'),('1 28'),('56');
SELECT * FROM test_time;
| cr_time |
|---|
| 10:28:56 |
| 34:28:56 |
| 38:56:00 |
| 56:56:00 |
| 52:00:00 |
| 00:00:56 |
TIME类型用来表示时间，不包含日期部分。可以使用 “HH:MM:SS” 格式来表示 TIME 类型。
向TIME类型的字段插入数据时，也可以使用几种不同的格式：
1.可以使用带有冒号的字符串，比如' D HH:MM:SS' 、 ' HH:MM:SS ' 、 ' HH:MM ' 、 ' D HH:MM ' 、 ' D HH ' 或 ' SS '格式。其中 D表示天数 ，其最小值为 0 ，最大值为 34 。
2.当输入D HH:MM:SS时 ，D会被转化为小时，计算格式为 D*24+HH。
3.当仅输入两位数时，表示秒数。
DATETIME类型
CREATE TABLE test_datetime(
dt DATETIME
);
INSERT INTO test_datetime
VALUES('2022-09-27 10:09:45')
SELECT * FROM test_datetime;
#可以使用DATE，TIME时间格式进行匹配
| dt |
|---|
| 2022-09-27 10:09:45 |
DATETIME在格式上为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:S。
以 YYYY - MM - DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入 DATETIME 类型的字段时， 最小值为1000-01-01 00:00:00 ，最大值为 9999-12-03 23:59:59 。
使用NOW()方法 会输出年月日时分秒。
YEAR类型
CREATE TABLE test_year(
y YEAR
);
INSERT INTO test_year
VALUES('2022'),('69'),('70'),(0),('0');
SELECT * FROM test_year;
| y |
|---|
| 2022 |
| 2069 |
| 1970 |
| 0000 |
| 2000 |
YEAR类型用来表示年份。在添加时，YEAR有以下几种存储格式：
1.以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
2.以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
当取值为01 到 69 时，表示 2001 到 2069 ；
当取值为70 到 99 时，表示 1970 到 1999 ；
当取值整数的0 或 00 添加的话，那么是 0000 年；
当取值是日期/ 字符串的 '0' 添加的话，是 2000 年。
文本字符串
MySQL 中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等类型。
文本字符类型 值的长度 长度范围 占用的存储空间 CHAR(M) M 0<=M<=255 M个字节 VARCHAR(M) M 0<=M<=65535 M+1个字节 TINYTEXT L 0<=L<=255 L+2个字节 TEXT L 0<=L<=65535 L+2个字节 MEDIUMTEXT L 0<=L<=16777215 L+3个字节 LONGTEXT L 0<=L<=4294967295 L+4个字节 ENUM L 0<=L<=65535 1或2个字节 SET L 0<=L<=64 1,2,3,4或8个字节
CHAR与VARCHAR类型
CHAR 和 VARCHAR 类型都可以存储比较短的字符串。
char 固定长度的字符 比较耗费空间 效率高 M可以省略，默认为1
varchar 可变长度的字符 比较节省空间 效率低
字符串（文本）类型 特点 长度 长度范围 占用的存储空间 CHAR(M) 固定长度 M 0<=M<=255 M个字节 VARCHAR(M) 可变长度 M 0<=M<=65535 （实际长度+1）个字节
*CHAR类型*
CREATE TABLE test_char( c1 CHAR, c2 CHAR(5) ) DESC test_char;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| c1 | char(5) | yes | |||
| c2 | char(1) | yes |
如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
CREATE TABLE test_text(
t TEXT
);
INSERT INTO test_text
VALUES('a ');
SELECT CHAR_LENGTH(t) FROM test_text;
*VARCHAR类型*
CREATE TABLE test_varchar(
v VARCHAR(25)
);
INSERT INTO test_varchar
VALUES('a ');
SELECT CHAR_LENGTH(v) FROM test_varchar;#输出为3
VARCHAR(M) 定义时， 必须指定 长度 M ，否则报错
检索VARCHAR类型 的字段数据时，会 保留 数据尾部的 空格 。 VARCHAR 类型的字段所占用的存储空间为字符串实际长度加1 个字节。
CHAR和VARCHAR对比
| 类型 | 特点 | 空间上 | 时间上 | 使用场景 |
|---|---|---|---|---|
| CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大，速度要求高 |
| VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非CHAR的情况 |
TEXT类型
CREATE TABLE test_text(
t TEXT
);
INSERT INTO test_text
VALUES('a ');
SELECT CHAR_LENGTH(t) FROM test_text;
在 MySQL 中， TEXT 用来 保存文本类型的字符串 ，总共包含 4 种类型，分别为 TINYTEXT 、 TEXT 、MEDIUMTEXT 和 LONGTEXT 类型。
在向 TEXT 类型的字段保存和查询数据时，系统 自动按照实际长度存储 ，不需要预先定义长度。这一点和VARCHAR类型相同。
四种TEXT类型
| 文本字符类型 | 特定 | 值的长度 | 长度范围 | 占用的存储空间 |
|---|---|---|---|---|
| TINYTEXT | 小文本，可变长度 | L | 0<=L<=255 | L+2个字节 |
| TEXT | 文本，可变长度 | L | 0<=L<=65535 | L+2个字节 |
| MEDIUMTEXT | 中等文本，可变长度 | L | 0<=L<=16777215 | L+3个字节 |
| LONGTEXT | 大文本，可变长度 | L | 0<=L<=4294967295 | L+4个字节 |
*注意：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键*
枚举类型(ENUM)
CREATE TABLE test_enum(
e ENUM('春','夏','秋','冬')
);#通过ENUM指定范围
INSERT INTO test_enum
VALUES('天');#报错：Data truncated for column 'e' at row 1
INSERT INTO test_enum
VALUES('春'),('1'),(2);#可以由索引添加，索引从1开始 这里索引1代表春，索引2代表夏
SELECT * FROM test_enum; #输出：春，春，夏
类型也叫作枚举类型， ENUM 类型的 取值范围需要在定义字段时进行指定 。 设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值 。
文本字符类型 值的长度 长度范围 占用的存储空间 ENUM L 0<=L<=65535 1或2个字节
说明:
1.当 ENUM 类型包含 1 ～ 255 个成员时，需要 1个字节 的存储空间；
2.当 ENUM 类型包含 256 ～ 65535 个成员时，需要 2个字节的 存储空间。
3.ENUM 类型的 成员个数 的 上限为65535 个。
VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。
timestamp**和datetime类型 ** 说明：用来保存**日期类型 **
| 分类 | 取值范围 | 时区影响 |
|---|---|---|
| timestamp | 1970/01/01--2038/01/19 | 受时区影响 |
| datetime | 1000/01/01--9999/12/31 | 不受时区影响，反映插入时的当地时区 |
Now函数的返回值是一个日期时间值，以“YYYY-MM-DD HH:MM:SS”格式返回。其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。这个返回值的类型是datetime类型。
创建表 两个字段类型分别为timestamp，datetime
入库now()
CREATE TABLE test_date( d1 TIMESTAMP, d2 datetime )
查看表数据内容
查看时区 show variables like 'time_zone'
修改时区 set time_zone='+9:00'
创建表练习
创建一个员工表
| 字段 | 属性 | 说明 |
|---|---|---|
| id | 字符型 | 主键 |
| name | 字符型 | 姓名 |
| sex | 字符型 | 性别 |
| brithday | 日期型 | 生日 |
| entry_date | 日期型 | 入职时间 |
| job | 字符型 | 岗位 |
| salary | 小数型 | 工资 |
| resume | 大文本型 | 摘要 |
CREATE TABLE user( id CHAR PRIMARY KEY, name VARCHAR(20), sex CHAR(5), brithday TIMESTAMP, entry_date TIMESTAMP, job VARCHAR(20), salary DOUBLE(8,2), resume TEXT )
修改表
使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.
#新增列 ALTER TABLE table_name ADD column_name column_definition [FIRST|AFTER existing_column]; #ble_name是要修改的表名 #column_name是要添加的列名 #column_definition是要添加的列的数据类型和约束 #[FIRST|AFTER existing_column]是可选项，用来指定新列要添加到的位置，如果不指定，则默认添加到最后 #修改列的数据类型 ALTER TABLE table_name MODIFY COLUMN column_name new_column_definition; #table_name是要修改的表名 #column_name是要修改的列名 #new_column_definition是要修改的列的字段类型和约束。 #修改列名 ALTER TABLE table_name CHANGE old_column_name new_column_name new_column_definition; #table_name是要修改的表名 #old_column_name是要修改的旧列名 #new_column_name是要修改的新列名 #new_column_definition是要修改的列的数据类型和约束。 #一定要带类型和约束 #删除列 ALTER TABLE table_name DROP COLUMN column_name; #table_name是要修改的表名 #column_name是要删除的列名。
修改表的名称：Rename table 表名 to 新表名
修改表的字符集：alter table student character set utf8;
•练习
–在上面员工表的基础上增加一个image列。
–修改job列，使其长度为60。
–删除sex列。
–表名改为user。
–修改表的字符集为utf-8
–列名name修改为username
#在上面员工表的基础上增加一个image列。 ALTER TABLE user ADD ( image VARCHAR(90) ); #修改job列，使其长度为60。 ALTER TABLE user MODIFY COLUMN job VARCHAR(60); #删除sex列 ALTER TABLE USER DROP sex; #改表明为user RENAME TABLE user TO user; #修改表的字符集为utf-8 ALTER TABLE user CHARACTER SET utf8; #列名name修改为username ALTER TABLE user CHANGE name username VARCHAR(20); #返回见表语句 SHOW CREATE TABLE user; #查看表数据内容 DESC user;
数据库CRUD语句
Insert语句 (增加数据)
INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
插入的数据应与字段的数据类型相同。
数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在**单引号中。**
插入空值，不指定或对应字段插入(null)
练习
练习：使用insert语句向表中插入三个员工的信息。
| 字段名 | 字段类型 |
|---|---|
| id | 整形 |
| name | 字符串型 |
| sex | 字符或整数类型 |
| birthday | 日期型 |
| salary | 浮点型 |
| entry_date | 日期型 |
| resume | 大文本型 |
INSERT INTO user
(id,username,sex,brithday,entry_date,job,salary,resume)
VALUES
('1',"谢谢","男","2000-06-05","2023-06-05","正式工",22222.22,"暂无"),
("2","谢谢nan","男","2000-06-05","2023-06-05","正式工",22222.22,"暂无"),
("3","谢谢","男","2000-06-05","2023-06-05","正式工",22222.22,"暂无")
注意：字符和日期要包含在单引号中。
•Update语句 (更新数据)
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
update 表名 set 字段名=字段名+1000; #自增1000
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
练习
练习：在上面创建的employee表中修改表中的纪录。
要求
将所有员工薪水修改为5000元。
将姓名为’zs’的员工薪水修改为3000元。
将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。
将wu的薪水在原有基础上增加1000元。
UPDATE user set salary =5000; UPDATE user set salary =3000 where username="谢谢"; UPDATE user set salary =4000,job='ccc' where username="谢谢2" UPDATE user set salary= salary+3000 where username="谢谢nan"
•Delete语句 (删除数据)
delete from tbl_name
[WHERE where_definition]
如果不使用where子句，将删除表中所有数据。
Delete语句不能删除某一列的值（可使用update）
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。
删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。
练习
•删除表中名称为’zs’的记录。
•删除表中所有记录。
INSERT INTO user
(id,username,sex,brithday,entry_date,job,salary,resume)
VALUES
('4',"zs","男","2000-06-05","2023-06-05","正式工",22222.22,"暂无");
DELETE FROM USER where username ='zs';
•Select语句 (查找数据)
SELECT [DISTINCT] *|{column1, column2. column3..}
FROM table;
Select 指定查询哪些列的数据。
column指定列名。
*号代表查询所有列。
From指定查询哪张表。
DISTINCT可选，指显示结果时，是否剔除重复数据
练习
学生表
| 字段 | 属性 | 说明 |
|---|---|---|
| stu_id | 字符型 | 主键 |
| stu_name | 字符型 | 姓名 |
| stu_sex | 字符型 | 性别 |
| stu_ch_score | 数值型 | 语文成绩 |
| stu_ma_sore | 数值型 | 数学成绩 |
| stu_en_score | 数值型 | 英语 |
查询表中所有学生的信息。
查询表中所有学生的姓名和对应的英语成绩。
过滤表中重复数据。
create table student(
stu_id VARCHAR(20) PRIMARY KEY,
stu_name VARCHAR(20),
stu_sex char(5),
stu_ch_score INT,
stu_ma_score INT,
stu_en_score INT
)
insert INTO student (stu_id,stu_name,stu_sex,stu_ch_score,stu_ma_score,stu_en_score)
VALUES
('1','张三','男', 90,80,70),
('2','张2','男', 90,80,70),
('3','张4','男', 90,80,70)
select * from student;
select st_en_score from student;
select stu_name,stu_en_score from student;
select distinct stu_name,stu_en_score from student;
select username,sex,id from user;
2.查询特定的类还有别名
特定的类
SELECT *|{column1｜expression, column2｜expression，..}
FROM table;
别名
SELECT column as 别名 from 表名;
select distinct stu_name as '名称',stu_en_score as '英文' from student;
练习
在所有学生分数上加10分特长分。
统计每个学生的总分。
使用别名表示学生分数。
select stu_ch_score+10 as stu_ch_score from student; select stu_name as '名称', stu_ch_score+stu_ma_score+stu_en_score as '总分' from student;
使用where子句，进行过滤查询。练习：
查询姓名为wu的学生成绩
查询英语成绩大于90分的同学
查询总分大于200分的所有同学
select stu_name as '名称',stu_en_score from student where stu_name ='wu'; select stu_name as '名称',stu_en_score from student where stu_en_score>90; select stu_name as '名称',stu_ch_score+stu_ma_score+stu_en_score as '总分' from student where (stu_ch_score+stu_ma_score+stu_en_score) >200
运算符
| 比较运算符 | > < <= >= = <> | 大于、小于、大于(小于)等于、不等于 |
|---|---|---|
| 比较运算符 | BETWEEN ...AND... | 显示在某一区间的值 |
| 比较运算符 | IN(set**)** | 显示在in列表中的值，例：in(100,200) |
| 比较运算符 | LIKE ‘**张pattern’** | 模糊查询 |
| 比较运算符 | IS NULL | 判断是否为空 |
| 逻辑运算符 | and | 多个条件同时成立 |
| 逻辑运算符 | or | 多个条件任一成立 |
| 逻辑运算符 | not | 不成立，例：where not(salary>100); |
Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;
练习
查询英语分数在 80－90之间的同学。
查询数学分数为89,90,91的同学。
查询所有姓李的学生成绩。
查询数学分>80，语文分>80的同学。
select stu_name from student where 80<=stu_en_socre and stu_en_socre<=90; select stu_name from student where stu_ma_score in (89,90,91); select stu_name from student where stu_name like '李%'; select stu_name from student where stu_ma_score>80 and stu_ch_score >80; #英语成绩70~110 select stu_name as '名称',stu_en_score from student where stu_en_score between 70 and 110;
order by 排序
SELECT column1, column2. column3..
FROM table;
order by column asc|desc
Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。
Asc 升序(可省略)、Desc 降序
ORDER BY 子句应位于SELECT语句的结尾。limit除外
支持单个字段、多个字段、表达式、函数、别名
顺序：from、where、select、order by
SELECT column1, column2. column3..
FROM table;
order by column asc|desc
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('1', '张三', '男', '47', '87', '77');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('2', '张2', '男', '122', '98', '58');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('3', '张4', '男', '110', '87', '78');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('4', '谢谢', '男', '112', '90', '76');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('5', '是的', '女', '120', '110', '111');
1 张三 男 47 87 77 2 张2 男 122 98 58 3 张4 男 110 87 78 4 谢谢 男 112 90 76 5 是的 女 120 110 111
练习
1、对数学成绩排序后输出。
2、对总分排序后输出，然后再按从高到低的顺序输出
3、对姓李的学生成绩排序输出
#对数学成绩排序后输出 select * from student ORDER BY stu_ma_score; #对总分排序后输出，然后再按从高到低的顺序输出 # 1.总分排序输出 select stu_id, stu_name as '名称',stu_en_score as '英文' ,stu_ch_score as '中文' ,stu_ma_score as '数学' , stu_ch_score+stu_ma_score+stu_en_score as '总分' from student ORDER BY (stu_ch_score+stu_ma_score+stu_en_score); # 2.排序后输出，然后再按从高到低的顺序输出 使用order by 子句对结果集进行排序：Asc: 升序(默认), Desc: 降序 select stu_id, stu_name as '名称',stu_en_score as '英文' ,stu_ch_score as '中文' ,stu_ma_score as '数学' , stu_ch_score+stu_ma_score+stu_en_score as '总分' from student ORDER BY (stu_ch_score+stu_ma_score+stu_en_score) DESC; #对姓李的学生成绩排序输出 #这里排序英语 select stu_id, stu_name as '名称',stu_en_score as '英文' ,stu_ch_score as '中文' ,stu_ma_score as '数学' , stu_ch_score+stu_ma_score+stu_en_score as '总分' from student where stu_name LIKE "张%" ORDER BY stu_en_score DESC;
1 张三 男 47 87 77 3 张4 男 110 87 78 4 谢谢 男 112 90 76 2 张2 男 122 98 58 5 是的 女 120 110 111
英语排序
#对数学成绩排序后输出 select * from student ORDER BY stu_en_score;
2 张2 男 122 98 58 4 谢谢 男 112 90 76 1 张三 男 47 87 77 3 张4 男 110 87 78 5 是的 女 120 110 111
语文排序
#对数学成绩排序后输出 select * from student ORDER BY stu_ch_score;
1 张三 男 47 87 77 3 张4 男 110 87 78 4 谢谢 男 112 90 76 5 是的 女 120 110 111 2 张2 男 122 98 58
分组group by
GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。 分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。分组函数忽略空值
group by的本质就是将某些列分组，将分组后的每组数据进行函数运算，如sum、max、avg、count等，得出每组最终结果；
展示：1）所有分组的列都需要展示出来 2）函数运算结果展示出来；
由于语句本质如此，语法见下述描述；
语法：
SELECT column1, column2. column3.. FROM table;
group by column
设置测试表的结构和数据
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` varchar(20) NOT NULL,
`stu_name` varchar(20) DEFAULT NULL,
`stu_sex` char(5) DEFAULT NULL,
`stu_ch_score` int(11) DEFAULT NULL,
`stu_ma_score` int(11) DEFAULT NULL,
`stu_en_score` int(11) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('1', '张三', '男', '47', '87', '77');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('2', '张2', '男', '122', '98', '58');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('3', '张4', '男', '110', '87', '78');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('4', '谢谢', '男', '112', '90', '76');
INSERT INTO 'student' ('stu_id', 'stu_name', 'stu_sex', 'stu_ch_score', 'stu_ma_score', 'stu_en_score') VALUES ('5', '是的', '女', '120', '110', '111');
-
group by+列名，代表根据此列进行分组；
-
group by（列）之后，如 group by stu_sex，SELECT * from student GROUP BY stu_sex;根据性别分组，得到2组数据
-
1组 女，共1行数据
-
2组 男，共4行数据，
-
因为只有两组数据，显示出的结果只有2行，切是根据表的collate默认排序获取两组行首的数据返回到结果中，
-
-
-
-
group by语句一般都同时使用函数sum、max、avg、count等，意为根据分组进行函数计算；两者（group by与函数）一般成对出现；
-
group by其后应该为select的所有的列，除非某些列比较特殊；
-
也可不使用group by，此时查询结果为所有数据；
转换为简易版个人总结的规律：
1）先判断是否需要使用group by
2）如果需要，再判断哪些列需要group by
create table t_order(id int primary key,product varchar(20),price float(8,2)); insert into t_order values(1,'xiaomi', 1000); insert into t_order values(2,'xiaomi',1100); insert into t_order values(3,'huawei',2200); insert into t_order values(4,'apple',8200); insert into t_order values(5,'huawei',2400); insert into t_order values(6,'huawei',2600);
练习：对订单表中商品归类后，显示每一类商品的总价
SELECT product,SUM(price) FROM t_order GROUP BY product;
使用having 子句过滤（相当于分组之后的值进行where）
SELECT column1, column2. column3..
FROM table;
group by column having ...
练习：查询购买了几类商品，并且每类总价大于3000的商品
SELECT product,sum(price) FROM t_order GROUP BY product HAVING sum(price)>3000;
合计函数 count
Count(列名)返回某一列，行的总数
满足列条件的总得行数 比如卖了多少商品
Select count(*)|count(列名) from tablename
[WHERE where_definition]
l练习：
卖了多少商品
SELECT product,count(product) FROM t_order GROUP BY product ;
表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` varchar(20) NOT NULL,
`stu_name` varchar(20) DEFAULT NULL,
`stu_sex` char(5) DEFAULT NULL,
`stu_ch_score` int(11) DEFAULT NULL,
`stu_class` int(11) NOT NULL,
`stu_ma_score` int(11) DEFAULT NULL,
`stu_en_score` int(11) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '男', '47', '1', '87', '77');
INSERT INTO `student` VALUES ('2', '张2', '男', '122', '1', '98', '58');
INSERT INTO `student` VALUES ('3', '张4', '男', '110', '2', '87', '78');
INSERT INTO `student` VALUES ('4', '谢谢', '男', '112', '2', '90', '76');
INSERT INTO `student` VALUES ('5', '是的', '女', '120', '1', '110', '111');
统计数学成绩大于90的学生有多少个？
SELECT count(stu_ma_score) FROM student where stu_ma_score >90;
结果：2（98，110两个数学成绩）
统计总分大于250的人数有多少？
-
# 1.总分大于250的 SELECT * FROM student where (stu_ma_score+stu_ch_score+stu_en_score) >250; #统计总分大于250的人数 SELECT count(*) FROM student where (stu_ma_score+stu_ch_score+stu_en_score) >250; #返回4
统计一个班级数学总成绩？
SELECT stu_class as '班级', sum(stu_ma_score) as '数学总成绩' FROM student GROUP BY stu_class;
l统计一个班级语文、英语、数学各科的总成绩
SELECT stu_class as '班级', sum(stu_ch_score) as '语文总成绩',sum(stu_ma_score) as '数学总成绩',sum(stu_en_score) as '英语总成绩' FROM student GROUP BY stu_class;
l统计一个班级语文、英语、数学的成绩总和
SELECT stu_class as '班级', sum(stu_ch_score+stu_ma_score+stu_en_score) as '总成绩' FROM student GROUP BY stu_class;
l统计一个班级语文成绩平均分
SELECT stu_class as '班级', avg(stu_ch_score) as '语文平均分', avg(stu_ma_score) as '数学平均分', avg(stu_en_score) as '英语平均分' FROM student GROUP BY stu_class;
l注意：sum仅对数值起作用，否则会报错。
l注意：对多列求和，“，”号不能少。
合计函数－SUM
Sum函数返回满足where条件的行的和
Select sum(列名)｛,sum(列名)…｝ from tablename
[WHERE where_definition]
l练习：
l求一个班级数学平均分？
SELECT stu_class as '班级', avg(stu_ch_score) as '语文平均分', avg(stu_ma_score) as '数学平均分', avg(stu_en_score) as '英语平均分' FROM student GROUP BY stu_class;
求一个班级总分平均分
SELECT stu_class as '班级', avg(stu_ch_score) as '语文平均分', avg(stu_ma_score) as '数学平均分', avg(stu_en_score) as '英语平均分' , avg(stu_ch_score+stu_ma_score+stu_en_score) as '总平均分' FROM student GROUP BY stu_class;
合计函数－AVG
AVG函数返回满足where条件的一列的平均值
Select sum(列名)｛,sum(列名)…｝ from tablename
[WHERE where_definition]
l练习：
合计函数－MAX/MIN
Max/min函数返回满足where条件的一列的最大/最小值
Select max(列名) from tablename
[WHERE where_definition]
l练习：求班级最高分和最低分（数值范围在统计中特别有用）
SELECT stu_class as '班级', avg(stu_ch_score) as '语文平均分', avg(stu_ma_score) as '数学平均分', avg(stu_en_score) as '英语平均分' , avg(stu_ch_score+stu_ma_score+stu_en_score) as '总平均分', max(stu_ch_score) as '语文最高分', max(stu_ma_score) as '数学最高分', max(stu_en_score) as '英语最高分' , min(stu_ch_score) as '语文最低分', min(stu_ma_score) as '数学最低分', min(stu_en_score) as '英语最低分' FROM student GROUP BY stu_class;
合计函数－upper/lower
upper/lower函数将文本转换为大写/小写形式
Select upper/lower(列名) from tablename
[WHERE where_definition]
l练习：
合计函数－SUM
Sum函数返回满足where条件的行的和
Select sum(列名)｛,sum(列名)…｝ from tablename
[WHERE where_definition]
练习：
字符串相关函数
| CHARSET(str)/charset | 返回字串字符集 |
|---|---|
| CONCAT (string2 [,... ])/concat | 连接字串 |
| INSTR (string ,substring )/instr | 返回**substring在string中出现的位置,没有返回0** |
| UCASE (string2 ) /upper | 转换成大写 |
| LCASE (string2 ) /lower | 转换成小写 |
| LEFT (string2 ,length )/left | 从**string2中的左边起取length个字符** |
| LENGTH (string )/length | string**长度** |
| REPLACE (str ,search_str ,**replace_str** ) | 在**str中用replace_str替换search_str** |
| lpad(str1,length,str2) | 用指定的字符str2实现左填充指定长度 |
| rpad**(str1,length,str2)** | 用指定的字符str2实现右填充指定长度 |
| SUBSTRING (str , position [,length ]) | 从**str的position开始,取length个字符** |
| LTRIM (string2 ) /RTRIM (string2 )/ trim | 去除前端空格或后端空格 |
数学相关函数
| ABS (number2 ) | 绝对值 |
|---|---|
| ROUND (decimal_number[,INT] ) | 四舍五入 逗号后数值表示保留几位小数 |
| CEILING (number2 )/**ing** | 向上取整 |
| FLOOR (number2 ) | 向下取整 |
| TRUNCATE(**decimal_number,number)** | 截取小数点后**number位** |
| FORMAT (number,decimal_places ) | 保留小数位数 |
| HEX (DecimalNumber ) | 转十六进制 |
| LEAST (number , number2 [,..]) | 求最小值 |
| MOD (numerator ,denominator ) | 求余 |
| RAND([seed]) | RAND([seed]) |
时间日期相关函数
| ADDTIME (date2 ,**time_interval** ) | 将**time_interval加到date2** | select addtime(‘02:30:30’,‘01:01:01’); |
|---|---|---|
| CURRENT_DATE ( ) | 当前日期 | select CURRENT_DATE (); 00:31:46 |
| CURRENT_TIME ( ) | 当前时间 | select CURRENT_DATE(); 2023-06-16 |
| CURRENT_TIMESTAMP ( ) | 当前时间戳 | select CURRENT_TIMESTAMP (); 2023-06-16 00:31:46 |
| DATE (datetime ) | 返回**datetime的日期部分** | select date('2023-06-16 00:31:46'); 2023-06-16 |
| DATE_ADD (date2 , INTERVAL d_value d_type ) | 在**date2中加上日期或时间** | |
| DATE_SUB (date2 , INTERVAL d_value d_type ) | 在**date2上减去一个时间** | |
| DATEDIFF (date1 ,date2 ) | 两个日期差 | select DATEDIFF (CURRENT_TIMESTAMP(),'2023-05-16') as '时间差';#返回的是天数差，可以有负数 |
| NOW ( ) | 当前系统日期**+时间** | |
| YEAR|Month|DATE (datetime ) | 年月日 | select year ('2023-06-16 00:31:46');#2023 select month ('2023-06-16 00:31:46');6 |
示例：select addtime(‘02:30:30’,‘01:01:01’); 注意：字符串、时间日期的引号问题
select date_add(entry_date,INTERVAL 2 year) from student;／／增加两年
select addtime(time,‘1 1-1 10:09:09’) from student; ／／时间戳上增加，注意年后没有-
select date(now());#2023-06-16 select DATE(CURRENT_DATE());#2023-06-16 select DATE(CURRENT_TIMESTAMP());#2023-06-16 select CURRENT_DATE();#2023-06-16 select CURRENT_TIMESTAMP();#2023-06-16 00:31:46 select now();#2023-06-16 00:35:25 select DATE_ADD(CURRENT_DATE(),INTERVAL 2 year); select DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL 2 year); select DATE_ADD(now(),INTERVAL 2 year); select DATE_sub(now(),INTERVAL 2 MONTH); select DATE_sub(now(),INTERVAL 2 MONTH); select DATE_sub(now(),INTERVAL 2 DAY);# HOUR MINTUE SECOND
连接
内连接：inner join
左连接：left join
右连接：right join
oracle可以直接使用full join来完成全连接，而mysql则需要借助union。
使用学生表与成绩表来演示Mysql中的各种连接查找
学生表的建表语句如下：
CREATE TABLE student( id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增序号', st_id int(11) DEFAULT NULL COMMENT '学生id', st_name varchar(255) DEFAULT NULL COMMENT '学生姓名', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB;
成绩表的建表语句如下：
CREATE TABLE score( id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', st_id int(11) DEFAULT NULL COMMENT '学生id', subject varchar(255) DEFAULT NULL COMMENT '学科名称', grade int(11) DEFAULT NULL COMMENT '学科成绩', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB ;
初始数据如下：
INSERT INTO student (st_id,st_name) VALUES (1001,'张三'), (1002,'李四'), (1003,'王五'); INSERT INTO score (st_id,subject,grade) VALUES (1001,'语文',80), (1001,'数学',90), (1002,'语文',70), (1002,'语文',75);
二、内连接
按照关联字段取出两个表中的记录，保留的是两个表的交集。
-
找到两张表共有的字段，这里 a表和b表共有字段st_id，
-
内连接使用的是 inner join
-
select a.st_id,a.st_name,b.grade
-
from a表 #主表是a表，即主要数据来源是a表
-
inner join b表 on /共有字段/a.st_id=b.st_id;
例如：
1.st.id
2查询（）
SELECT
student.st_id,
student.st_name,
score.subject,
score.grade
FROM
student
INNER JOIN score ON student.st_id = score.st_id;
执行结果：
对于关联字段st_id，左表与右表都有1001与1002。
三、左连接
按照关联字段取出两个表中的记录，保留左表所有的记录，以及满足连接条件的右表记录，右表中不满足连接条件的会被置为null。
-
同内连接
-
找到两张表共有的字段，这里 a表和b表共有字段st_id，
-
内连接使用的是 inner join
-
select a.st_id,a.st_name,b.grade
-
from a表 #主表是a表，即主要数据来源是a表
-
left join b表 on /共有字段/a.st_id=b.st_id;
例如：
SELECT
student.st_id,
student.st_name,
score.subject,
score.grade
FROM
student
LEFT JOIN score ON student.st_id = score.st_id;
执行结果：
对于关联字段st_id，展示左表所有的记录。由于右表缺少1003，则1003这行的subject与grade的值被置为null。
四、右连接
按照关联字段取出两个表中的记录，保留右表所有的记录，以及满足连接条件的左表记录，左表中不满足连接条件的会被置为null。正好与左连接相反。
-
同内连接，与左连接相反
-
找到两张表共有的字段，这里 a表和b表共有字段st_id，
-
内连接使用的是 left join
-
select a.st_id,a.st_name,b.grade
-
from a表 #主表是b表，即主要数据来源是on字段后面的b表
-
right join b表 on /共有字段/a.st_id=b.st_id;
例如：
SELECT
student.st_id,
student.st_name,
score.subject,
score.grade
FROM
student
right JOIN score ON student.st_id = score.st_id;
执行结果：
对于关联字段st_id，展示右表所有的记录。由于左表缺少1005，即执行结果的最后一行的st_id与st_name的值被置为null。
五、全连接
按照关联字段取出两个表中的记录，保留左右两表中所有的记录，不满足连接条件的均被置为null。
oracle：
-
同内连接，
-
找到两张表共有的字段，这里 a表和b表共有字段st_id，
-
内连接使用的是 left join
-
select a.st_id,a.st_name,b.grade
-
from a表 #主表是a表，即主要数据来源是a表
-
full join b表 on /共有字段/a.st_id=b.st_id;
mysql：
当然，oracle可以直接使用full join来完成全连接，而mysql则需要借助union。
例如：
select student.st_id,student.st_name,score.subject,score.grade from student left join score on student.st_id=score.st_id union select student.st_id,student.st_name,score.subject,score.grade from student right join score on student.st_id=score.st_id;
执行结果：
可以看到，已经取出了两个表中的所有数据。
六、获取交集以外的部分
按照关联字段取出两个表中的记录，保留交集以外的数据。去除交集相当于全连接-内连接。
例如：
select student.st_id,student.st_name,score.subject,score.grade from student left join score on student.st_id=score.st_id where score.st_id is null union select student.st_id,student.st_name,score.subject,score.grade from student right join score on student.st_id=score.st_id where student.st_id is null;
执行结果：
if case
if**函数：类似if else**
select if(10<5,'A','B');
case**函数：**case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...
else 要显示的值n或语句n；
end；
case 要判断的字段或表达式
when 条件1 then 要显示的值1或语句1；
when 条件1 then 要显示的值2或语句2；
...
else 要显示的值n或语句n；
end；
Tip:定义表的约束
定义主键约束 primary key:不允许为空，不允许重复 删除主键：alter table tablename drop primary key ; 定义主键自动增长 auto_increment 定义唯一约束 unique 定义非空约束 not null 定义外键约束 constraint ordersid_FK foreign key(ordersid) references orders(id),
Tip**：mysql中文乱码**
方法：修改my.ini 在安装文件夹中把my-dafault.ini改成my.ini 位置：[mysqld] port=3306 Character_set_server = utf8 修改完后，重启mysql的服务
回顾
①mysql优点 1、开源、免费、成本低 2、性能高、移植性也好 3、体积小，便于安装
②数据库的好处 1、可以持久化数据到本地 2、结构化查询
mysql如何实现按行求和
不用sum,直接用表达式 SELECT 列1+列2 AS 别名 FROM 表
案例：
这个案例我先建立一个学生成绩的数据表，然后再求出每一个学生的成绩总额。
CREATE TABLE student( id INT, NAME VARCHAR(20), chinese INT, english INT, math INT ); INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'行哥',89,78,90); INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'潘金莲',67,53,95); INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'凤姐',87,78,77); INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'旺财',88,98,92); INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'白小黑',82,84,67); INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'白小黄',55,85,45); INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'范蹦蹦',75,65,30);
SELECT id,NAME,chinese+english+math AS t_score FROM student; 结果如下图：
4980
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
