mysql-note

发表于 更新于 分类于 Waline：

1. 数据库（DATABASE）的创建、删除、修改和查看

创建数据库：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
	[[DEFAULT] CHARACTER SET <字符集名>] [[DEFAULT] COLLATE <校对规则名>];

示例：

-- 创建一个数据库，命名为 test_db_char，指定其默认字符集为 utf8，默认校对规则为 utf8_chinese_ci（简体中文，不区分大小写）
CREATE DATABASE IF NOT EXISTS test_db_char
	DEFAULT CHARACTER SET utf8
	DEFAULT COLLATE utf8_chinese_ci;

删除数据库：

DROP DATABASE [ IF EXISTS ] <数据库名>

示例：DROP DATABASE IF EXISTS test_db_del;

修改数据库：

ALTER DATABASE [数据库名] { [ DEFAULT ] CHARACTER SET <字符集名> |
	[ DEFAULT ] COLLATE <校对规则名>}

示例：

-- 将数据库 test_db_char 的指定字符集修改为 gb2312，默认校对规则修改为 utf8_unicode_ci
ALTER DATABASE test_db_char
	DEFAULT CHARACTER SET gb2312
	DEFAULT COLLATE gb2312_chinese_ci;

查看数据库：

SHOW DATABASES [LIKE '数据库名'];

2. 数据类型

  • 数字类型
    • 整数: TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
    • 浮点数: FLOAT 和 DOUBLE
    • 定点数： DECIMAL
  • 日期和时间: YEAR、TIME、DATE、DATETIME 和 TIMESTAMP
  • 字符串类型：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET
  • 二进制(可用来存储图片、音乐等): BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB

数字类型

整型

typeStorageSigned（Unsigned）
TINYINT1 Bytes-128~127（255）
SMALLINT2 Bytes-32768~32767（65535）
MEDIUMINT3 Bytes-8388608~8388607（16777215）
INT4 Bytes-2147483648~2147483647（4294967295）（大约42亿）
BIGINT8 Bytes-9223372036854775808〜9223372036854775807（18446744073709551615）

浮点型和定点型

属性存储空间可做精确计算
FLOAT(M, D)4 bytes否
DOUBLE(M, D)8 bytes否
DECIMAL(M, D)M+2 bytes是

M表示该值的总共长度, D表示小数点后面的长度, M和D又称为精度和标度；

不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理；

如float(7,4), 最大可存入-999.9999, 如果尝试存入999.00009, 则存入结果为999.0001

时间类型

类型名称日期格式日期范围存储需求
YEARYYYY1901 ~ 21551 个字节
TIMEHH:MM:SS-838:59:59 ~ 838:59:593 个字节
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33 个字节
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598 个字节
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC4 个字节

字符串类型

类型长度
CHAR0-255字节
VARCHAR0-65535 字节
TEXT0-65 535字节
  • （1）char: 定长格式，但是长度范围是 0~255. 当你想要储存一个长度不足 255 的字符时，Mysql 会用空格来填充剩下的字符。因此在读取数据时，char 类型的数据要进行处理，把后面的空格去除。
  • （2）varchar: 关于 varchar，有的说最大长度是 255，也有的说是 65535，查阅很多资料后发现是这样的：varchar 类型在 5.0.3 以下的版本中的最大长度限制为 255，而在 5.0.3 及以上的版本中，varchar 数据类型的长度支持到了 65535，也就是说可以存放 65532 个字节（注意是字节而不是字符！！！）的数据（起始位和结束位占去了3个字节），也就是说，在 5.0.3 以下版本中需要使用固定的 TEXT 或 BLOB 格式存放的数据可以在高版本中使用可变长的 varchar 来存放，这样就能有效的减少数据库文件的大小。
  • （3）text: 与 char 和 varchar 不同的是，text 不可以有默认值，其最大长度是 2 的 16 次方-1

    总结起来，有几点：

    • 经常变化的字段用 varchar
    • 知道固定长度的用 char
    • 尽量用 varchar
    • 超过 255 字符的只能用 varchar 或者 text
    • 能用 varchar 的地方不用 text

    该部分引用自菜鸟教程

3.数据表（TABLE）的创建、删除、修改和查看

创建数据表：

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

其中，[表定义选项]的格式为： <列名1> <类型1> [,…] <列名n> <类型n>

示例：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
	`runoob_id` INT UNSIGNED AUTO_INCREMENT,
	`runoob_title` VARCHAR(100) NOT NULL DEFAULT 'RUNOOB_TITLE',
	`runoob_author` VARCHAR(40) NOT NULL DEFAULT 'RUNOOB_AUTHOR',
	`runoob_context` TEXT NULL,
	`submission_date` DATE,
	PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  • PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
  • ENGINE 设置存储引擎，CHARSET 设置编码。

删除数据表：

DROP TABLE [IF EXISTS] <表名> [ , <表名1> , <表名2>] …

示例：DROP TABLE IF EXISTS runoob_tbl

修改数据表：

ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

添加字段：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；

示例：

-- 修改表 tb_emp1 的结构，在表的第一列添加一个 int 类型的字段 col1
ALTER TABLE tb_emp1
	ADD COLUMN col1 INT FIRST;

提示：“FIRST 或 AFTER 已存在的字段名”用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

删除字段：

ALTER TABLE <表名> DROP <字段名>；

示例：

-- 修改表 tb_emp1 的结构，删除 col2 字段
ALTER TABLE tb_emp1
	DROP col2;

修改字段数据类型：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

示例：

-- 修改表 tb_emp1 的结构，将 name 字段的数据类型修改成 VARCHAR(30)
ALTER TABLE tb_emp1
	MODIFY name VARCHAR(30);

修改字段名称：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

示例：

-- 修改表 tb_emp1 的结构，将 col1 字段名称改为 col3，同时将数据类型变为 CHAR(30)
ALTER TABLE tb_emp1
	CHANGE col1 col3 CHAR(30);

如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空

修改表名：

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

示例：

ALTER TABLE tb_emp1
	RENAME TO tb_emp2;

查看数据表结构：

DESC <表名>;

mysql> desc tbl;
+-----------------+------------------+------+-----+---------------+----------------+
| Field           | Type             | Null | Key | Default       | Extra          |
+-----------------+------------------+------+-----+---------------+----------------+
| test            | int(11)          | YES  |     | NULL          |                |
| runoob_id       | int(10) unsigned | NO   | PRI | NULL          | auto_increment |
| runoob_title    | varchar(100)     | NO   |     | RUNOOB_TITLE  |                |
| runoob_author   | varchar(40)      | NO   |     | RUNOOB_AUTHOR |                |
| runoob_context  | text             | YES  |     | NULL          |                |
| submission_date | date             | YES  |     | NULL          |                |
+-----------------+------------------+------+-----+---------------+----------------+

查看表结构：

SHOW CREATE TABLE <表名>;

4.数据表（TABLE）的查询

MySQL SELECT 基本语法

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]
  • {*|<字段列名>}包含星号通配符的字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间要用逗号隔开，最后一个字段后不要加逗号。
  • FROM <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
  • WHERE 子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。
  • GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
  • HAVING <表达式>，该子句用于指定一组行或聚合的过滤条件，通常和GROUP BY一起使用。
  • [ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC）。
  • [LIMIT[，]]，该子句告诉 MySQL 每次显示查询出来的数据条数。

1. 数据检索（SELECT）

-- 检索单个列
SELECT prod_name FROM products;
-- 检索多个列
SELECT prod_id, prod_name, prod_price FROM products;
-- 检索所有列
SELECT * FROM products;
-- 检索不同的行(去重)
SELECT DISTINCT vend_id FROM products;
-- 限制结果(LIMIT)
SELECT prod_name FROM products LIMIT 5;
-- 使用完全限制的表名,列名
SELECT products.prod_name FROM crashcourse.products;

2. 数据排序（ORDER BY）

SELECT 
   select_list
FROM 
   table_name
ORDER BY 
   column1 [ASC|DESC], 
   column2 [ASC|DESC],
   ...;

3. 数据过滤（WHERE）

SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition;
比较运算符说明
=等于
<小于
<=小于等于
>大于
>=大于等于
<=>安全的等于，不会返回 UNKNOWN
<> 或!=不等于
IS NULL 或 ISNULL判断一个值是否为 NULL
IS NOT NULL判断一个值是否不为 NULL
LEAST当有两个或多个参数时，返回最小值
GREATEST当有两个或多个参数时，返回最大值
BETWEEN value1 AND value2判断一个值是否落在两个值之间
IN判断一个值是IN列表中的任意一个值
NOT IN判断一个值不是IN列表中的任意一个值
LIKE通配符匹配
REGEXP正则表达式匹配

4. 数据分组（GROUP BY 和 HAVING）

-- 检索总计订单价格大于等于50的订单号和总计订单的价格，按总计订单价格排序。
-- GROUP BY 子句按订单号分组数据，以便SUM(*)函数能够返回总计订单价格
-- HAVING 子句过滤数据，只返回总计订单价格大于等于50的订单
-- ORDER BY 子句排序输出
SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50;
ORDER BY ordertotal;

HAVING 和 WHERE 的差别

WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。