mysql-sql基础

mysql中主要包括以下五大类：

整数类型 ：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型 ：FLOAT、DOUBLE、DECIMAL
字符串类型 ：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型 ：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

整型：

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节范围(-128~127)
smallint(m)	2个字节范围(-32768~32767)
mediumint(m)	3个字节范围(-8388608~8388607)
int(m)	4个字节范围(-2147483648~2147483647)
bigint(m)	8个字节范围(+-9.22*10的18次方)

int(m) m 用于指定显示宽度，int(5)表示5位数的宽度。注意显示宽度属性不能控制列可以存储的值范围，显示宽度属性通常由应用程序用于格式化整数值。

浮点型：

数据类型	含义
float(m,d)	单精度浮点型 4字节 m总个数，d小数位
double(m,d)	双精度浮点型 8字节 m总个数，d小数位

浮点型数据类型会有精度丢失的问题，比如小数位设置6位，存入0.45，0.45转换成二进制是个无限循环小数0.01110011100…，无法准确表示，存储的时候会发生精度丢失。

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

定点数：
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值，不会丢失精度。定点数以字符串形式存储。decimal(m,d) 参数m是总个数，d是小数位。
字符串：

MySQL数据类型	含义
char(n)	固定长度，最多255个字节
varchar(n)	可变长度，最多65535个字节
tinytext	可变长度，最多255个字节
text	可变长度，最多65535个字节
mediumtext	可变长度，最多2的24次方-1个字节
longtext	可变长度，最多2的32次方-1个字节

查询速度：char > varchar > text

char：定长，效率高，一般用于固定长度的表单提交数据存储；例如：身份证号，手机号，电话，密码等。char长度不足时，在右边使用空格填充，而varchar值保存时只保存需要的字符数。
varchar：不定长，效率偏低，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。
nvarchar（存储的是Unicode数据类型的字符）不管是一个字符还是一个汉字，都存为2个字节，一般用作中文或者其他语言输入，这样不容易乱码；varchar存储汉字是2个字节，其他字符存为1个字节，varchar适合输入英文和数字。
text：不需要指定存储长度，能用varchar就不用text。

二进制数据(BLOB)：

二进制数据类型可存储任何数据，如图像、多媒体、文档等。BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写；而Blob是以二进制方式存储，不区分大小写。

日期时间类型:

MySQL数据类型	含义
date	日期 ‘2008-12-2’
time	时间 ‘12:25:36’
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

基础sql命令

服务管理

启动服务：service mysqld start

关闭服务：service mysqld stop

启动客户端：mysql -uroot -p -u 后不要有空格（Ubuntu有空格）
表操作

创建表：create table user (id int, name varchar(10))
清空表数据：truncate table user;
重命名表：RENAME TABLE cusmtomers TO cust
更新表：
- 添加列：ALTER TABLE ADD phone char(2)
- 删除列：ALTER TABLE vendors DROP COLUMN vend_phone;
- 更改列：ALTER TABLE vendors MODIFY vend_phone CHAR(16);
查看表结构：desc table_name/select columns from table_name或SHOW CREATE db | SHOW CREATE table：显示创建特定数据库或表的MySQL语句
删除表（truncate、delete与drop）：DROP TABLE cumstomers
- 相同点：
  - truncate和不带where子句的delete、以及drop都会删除表内的数据。
  - drop、truncate都是DDL语句(数据定义语言)，执行后会自动提交。
- 不同点：
  - truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；
  - 速度，一般来说: drop> truncate > delete。

查询

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

示例：

SELECT DISTINCT vend_id FROM products;

#开始位置，行数|返回从第0行开始的5行数据
SELECT prod_name FROM products LIMIT 0, 5; 

# 先按名称排序，再按价格排序 | DESC降序排列，默认ASC升序
SELECT prod_name FROM products ORDER BY prod_name, prod_price DESC;

# =	等于, <>	不等于, !=	不等于, <	小于, <=	小于等于, BETWEEN	两值之间 
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT prod_name FROM products WHERE prod_price IS NULL;

# AND优先级大于OR
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10; 

# IN操作符一般比OR操作符清单执行更快。IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
SELECT prod_name, product_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;
SELECT prod_name, product_price FROM products WHERE vend_id NOT IN (1002, 1003)
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%jet%';

# _ 匹配单个字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_jet_';

# 正则表达式
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

#匹配1或2或3 [^123]取反
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Rely'  ORDER BY prod_name;

#匹配1-5任意一个数字，[a-z]同理
SELECT prod_name FROM products WHERE prod_anem REGEXP '[1-5] Ton';

#转义
SELECT prod_name FROM products WHERE prod_anem REGEXP '\\.';

#?匹配它前面的任何字符出现0次或1次
SELECT prod_name FROM products WHERE prod_anem REGEXP '\\([0-9] sticks?\\)'; 

#[:digit:]匹配任意数字
SELECT prod_name FROM products WHERE prod_anem REGEXP '[[:digit:]]{4}'; 
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

#REGEXP检查返回0或1；此处返回0
SELECT 'hello' REGEXP '[0-9]';

正则表达定位符：

元字符	说明
^	文本开始
$	文本结束
[[:<:]]	词开始
[[:>:]]	词结束

高级查询：

# 将结果按列1分组，并计算每组的数量。
SELECT 列1, COUNT(*) FROM 表名 GROUP BY 列1;

# 在分组后，使用 HAVING 对分组进行进一步筛选
SELECT 列1, COUNT(*) FROM 表名 GROUP BY 列1 HAVING COUNT(*) > 1;

# 内连接
SELECT A.列1, B.列2 FROM 表A INNER JOIN 表B ON A.id = B.a_id;

# 左连接
SELECT A.列1, B.列2 FROM 表A LEFT JOIN 表B ON A.id = B.a_id;

# 子查询
SELECT 列1 FROM 表A WHERE 列2 IN (SELECT 列2 FROM 表B);

视图

视图为虚拟的表。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

应用：

重用SQL语句。
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

限制：

与表一样，视图必须唯一命名
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。
ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖

语法:

CREATE VIEW：创建视图
SHOW CREATE VIEW viewname：查看创建视图的语句
DROP VIEW viewname：删除视图
CREATE ORREPLACE VIEW：更新视图，相当于先用DROP再用`CREATE

存储过程

为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。为什么使用存储过程：

把复杂处理进行封装，简化复杂的操作；
提高性能，存储过程比单独SQL语句更快；

创建：

返回产品平均价格的存储过程：

CREATE PROCEDURE productpricing() # 可以接受参数
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

BEGIN/END 用来限定存储过程体。此段代码仅创建了存储过程，未执行。

调用：

1 2	`CALL productpricing()`

删除：

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。 DROP PROCEDURE productpricing IF EXISTS

参数：

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。接受订单号并返回该订单的金额：

CREATE PROCEDURE ordertotal(
	IN ordernum INT,
	OUT ordersum DECIMAL(8, 2)
)
BEGIN
	SELECT Sum(item_price * quantity)
	FROM orderitems
	WHERE order_num = ordernum
	INTO ordersum;
END;

示例：

获取订单税后金额（订单金额+税收）

CREATE PROCEDURE ordertotal(
	IN onum INT,
	IN taxable BOOLEAN, # 是否计税
	OUT ototal DECIMAL(8, 2)
) COMMENT 'order total, adding tax'
BEGIN
	DECLARE total DECIMAL(8, 2);
	DECLARE taxrate INT DEFAULT 6;

	SELECT Sum(item_price * quanlity)
	FROM orderitems
	WHERE order_num = onum
	INTO total;

	IF taxable THEN
		SELECT total + (total / 100 * taxrate) INTO total;
	END IF;
	-- SELECT total INTO ototal;
END;

调用存储过程：

1 2	`CALL ordertotal(20005, 1, @total); SELECT @total;`

查看：

创建存储过程的 CREATE 语句。

1	`SHOW CREATE PROCEDURE ordertotal;`

获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS LIKE 'ordertotal';

查看存储过程状态：

1	`SHOW PROCEDURE status;`

mysql

#mysql

mysql-sql基础

http://example.com/2025/06/17/mysql-sql语法基础/

作者

ares

发布于

2025年6月17日

许可协议

java内存模型上一篇

reactor模型原理与实现下一篇