Hello World - MySQL
type: channel
link: https://www.ronie.ren/index.php/tag/MySQL/
items: MySQL 权限管理
items: MySQL 视图
items: MySQL 触发器
items: MySQL 存储过程和函数
items: MySQL 插入更新删除
items: MySQL 子查询
items: MySQL 连接查询
items: MySQL 数学函数
items: MySQL集合函数
items: MySQL查询语句

MySQL 权限管理
type: item
date: 2022-10-16T03:38:00+00:00
description: MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db脚本初始化。存储账户权限信息的表主要有user、db、host、tables_priv、columns_priv和procs_priv。权限表user表user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。mysql> desc user; +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(255) | 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 | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_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 | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_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 | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | N…

MySQL 视图
type: item
date: 2022-10-14T02:26:00+00:00
description: 视图简介：视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动反映到视图中。优点：简单化：看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。安全性：通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响。创建视图创建视图使用CREATE VIEW语句，语法：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGEI TEMPTABLE} ] VIEW view_ name [ (column_ list) ] AS SELECT statement [WITH [CASCADED | LOCAL] CHECK OPTION]。CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；ALGORITHM表示视图选择的算法，UNDEFINED表示MySQL将自动选择算法；MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句。view_name为视图的名称，column_list为属性列；SELECT_statement表示SELECT语句；WITH [CASCADED | LOCAL]，CASCADED与LOCAL为可选参数，CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可。CHECK OPTION参数表示视图在更新时保证在视图的权限范围之内。mysql> create table user( -> id int primary key auto_increment, -> name varchar(150) -> ); Query OK, 0 rows affected (0.01 sec) mysql> create table user_info( -> user_id int, -> department varchar(250), -> age int, -> sex enum('male', 'female') -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into user (name) values ('ronie'), ('jack'), ('kelly'), ('lucy'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into user_info values -> (1, 'IT', 23, 'male'), -> (2, 'art', 34, 'male'), -> (3, 'sale', 33, 'female'), -> (4, 'IT', 24, 'female'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+-------+ | id | name | +----+-------+ | 1 | ronie | | 2 | jack | | 3 | kelly | | 4 | lucy | +----+-------+ 4 rows in set (0.00 sec) mysql> select * from user_info; +---------+------------+------+--------+ | user_id | department | age | sex | +---------+------------+------+--------+ | 1 | IT | 23 | male | | 2 | art | 34 | male | | …

MySQL 触发器
type: item
date: 2022-10-13T12:17:00+00:00
description: 触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATAE和DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MySQL自动调用。触发器的创建及使用语法：CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt。触发器程序可以使用BEGIN和END作为开始和结束，中间包含多条语句trigger_name表示触发器名称，用户自行指定trigger_time表示触发时机，可以指定为before或after；trigger_event表示触发事件，包括INSERT、UPDATE和DELETE；tbl_name表示建立触发器的表名，即在哪张表上建立触发器；trigger_stmt是触发器执行语句。可以使用BEGIN和END作为开始和结束，中间包含多条语句。mysql> create trigger total_age before insert on user for each row set @a=@a+new.age; Query OK, 0 rows affected (0.01 sec) mysql> select sum(age) into @a from user; Query OK, 1 row affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 97 | +------+ 1 row in set (0.00 sec) mysql> insert into user (name, email, age) values ('baby', 'baby@163.com', 20); Query OK, 1 row affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 117 | +------+ 1 row in set (0.00 sec) mysql> 查看触发器查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。SHOW TRIGGERS语句查看触发器信息mysql> show triggers \G; *************************** 1. row *************************** Trigger: total_age Event: INSERT Table: user Statement: set @a=@a+new.age Timing: BEFORE Created: 2022-10-13 20:00:30.02 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified mysql> Trigger表示触发器的名称；Event表示激活触发器的事件；Table表示激活触发器的操作对象表；Timing表示触发器触发的时间；Statement表示触发器执行的操作在triggers表中查看触发器信息在MySQL中，所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过查询命令SELECT查看。命令：SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;mysql> select * from information_schema.triggers where trigger_name like 'total_%' \G; *************************** 1. row *********…

MySQL 存储过程和函数
type: item
date: 2022-10-12T12:53:00+00:00
description: 初始化数据库表mysql> create table user( -> id int primary key auto_increment, -> name varchar(250), -> email varchar(100), -> age tinyint -> ); Query OK, 0 rows affected (0.02 sec) mysql> insert into user (name, email, age) values -> ('ronie', 'ronie@163.com', 20), -> ('jack', 'jack@163.com', 34), -> ('jerry', 'jerry@163.com', 22), -> ('lucy', 'lucy@163.com', 21); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+-------+---------------+------+ | id | name | email | age | +----+-------+---------------+------+ | 1 | ronie | ronie@163.com | 20 | | 2 | jack | jack@163.com | 34 | | 3 | jerry | jerry@163.com | 22 | | 4 | lucy | lucy@163.com | 21 | +----+-------+---------------+------+ 4 rows in set (0.00 sec) 存储过程和函数存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。创建存储过程和函数创建存储过程语法：CREATE PROCEDURE sp_name ([proc_parameter]) [characteristics ...] routine_body。proc_parameter为指定存储过程的参数列表，[ IN | OUT | INOUT ] param_name type，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。characteristics指定存储过程的特性，取值：LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。[NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。COMMENT 'string'：注释信息，可以用来描述存储过程或函数。-- DELIMITER //语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号;。 -- 为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以END //结束存储过程。 -- 存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。 mysql> DELIMITER // mysql> create …

MySQL 插入更新删除
type: item
date: 2022-10-10T12:27:00+00:00
description: 创建表mysql> create table user( -> id int primary key auto_increment, -> name varchar(250), -> article text, -> create_time datetime -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc user; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(250) | YES | | NULL | | | article | text | YES | | NULL | | | create_time | datetime | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> create table user2( -> id int primary key auto_increment, -> name varchar(250), -> email varchar(200), -> sex enum('male', 'female') -> ); Query OK, 0 rows affected (0.01 sec) mysql> desc user2; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(250) | YES | | NULL | | | email | varchar(200) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) 插入数据使用基本的INSERT语句使用基本的INSERT语句插入数据要求指定表名称和插入到新记录中的值。语法：INSERT INTO table_name (column_list) VALUES (value_list);。INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开。使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息:Records：表明插入的记录条数。Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。Warnings：表明有问题的数据值，例如发生数据类型转换。若没有指定的插入列表，值列表为每一个字段列指定插入值，并且这些值的顺序必须和person表中字段定义的顺序相同。如果某些字段没有指定插入值，MySQL将插入该字段定义时的默认值。mysql> insert into user (name, article, create_time) values -> ('ronie', 'When all else is lost the future still remains.', now()), -> ('kelly', 'Keep on going never give up.', now()), -> ('jack', 'Nurture passes nature.', now()), -> ('jerry', 'The reason why a great man is great is that he …

MySQL 子查询
type: item
date: 2022-10-10T04:54:00+00:00
description: ANY、SOME关键字ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。mysql> create table nums( -> num int -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into nums values (2), (4), (5), (1), (7); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from nums; +------+ | num | +------+ | 2 | | 4 | | 5 | | 1 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> create table nums2( -> num int -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into nums2 values (2), (9), (5), (8), (7); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from nums2; +------+ | num | +------+ | 2 | | 9 | | 5 | | 8 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> select num from nums where num < any(select num from nums2); +------+ | num | +------+ | 2 | | 4 | | 5 | | 1 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> select num from nums where num < some(select num from nums2); +------+ | num | +------+ | 2 | | 4 | | 5 | | 1 | | 7 | +------+ 5 rows in set (0.00 sec) mysql> select num from nums where num > some(select num from nums2); +------+ | num | +------+ | 4 | | 5 | | 7 | +------+ 3 rows in set (0.00 sec)ALL关键字ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。mysql> select num from nums where num < all(select num from nums2); +------+ | num | +------+ | 1 | +------+ 1 row in set (0.01 sec) mysql> select num from nums where num > all(select num from nums2); Empty set (0.00 sec) EXISTS关键字EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。mysql> select * from user where exists(select id from orders where id=3); Empty set (0.00 sec) mysql> select * from user where exists(select id from orders where id=2); +----+-------+----------+--------+ | id | name | order_id | sex | +----+-------+----------+--------+ | 1 | ronie | 1 | male | | 2 | kelly | 2 | female | | 3 | mike | NULL | male | +----+-------+---------…

MySQL 连接查询
type: item
date: 2022-10-10T04:21:00+00:00
description: 初始化表mysql> create table user( -> id int primary key auto_increment, -> name varchar(200), -> order_id int, -> sex enum('male', 'female') -> ); Query OK, 0 rows affected (0.01 sec) mysql> create table orders( -> id int primary key auto_increment, -> info varchar(250), -> create_time datetime -> ); Query OK, 0 rows affected (0.01 sec) mysql> desc user; +----------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(200) | YES | | NULL | | | order_id | int | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +----------+-----------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> desc orders; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | info | varchar(250) | YES | | NULL | | | create_time | datetime | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into user (name, order_id, sex) values -> ('ronie', 1, 'male'), -> ('kelly', 2, 'female'), -> ('mike', null, 'male'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into orders (info, create_time) values -> ('fish', current_date()), -> ('meat', current_date()); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from user; +----+-------+----------+--------+ | id | name | order_id | sex | +----+-------+----------+--------+ | 1 | ronie | 1 | male | | 2 | kelly | 2 | female | | 3 | mike | NULL | male | +----+-------+----------+--------+ 3 rows in set (0.00 sec) mysql> select * from orders; +----+------+---------------------+ | id | …

MySQL 数学函数
type: item
date: 2022-10-09T10:38:00+00:00
description: 数学函数绝对值函数ABS(x)mysql> select abs(-1), abs(-23), abs(34); +---------+----------+---------+ | abs(-1) | abs(-23) | abs(34) | +---------+----------+---------+ | 1 | 23 | 34 | +---------+----------+---------+ 1 row in set (0.00 sec)圆周率的函数PI()mysql> select pi(); +----------+ | pi() | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec) 平方根函数SQRT(x)mysql> select sqrt(4), sqrt(5), sqrt(36), sqrt(-4); +---------+------------------+----------+----------+ | sqrt(4) | sqrt(5) | sqrt(36) | sqrt(-4) | +---------+------------------+----------+----------+ | 2 | 2.23606797749979 | 6 | NULL | +---------+------------------+----------+----------+ 1 row in set (0.00 sec) 求余函数MOD(x,y)mysql> select mod(5,3), mod(34, 7), mod(-34, -4), mod(-24, 3); +----------+------------+--------------+-------------+ | mod(5,3) | mod(34, 7) | mod(-34, -4) | mod(-24, 3) | +----------+------------+--------------+-------------+ | 2 | 6 | -2 | 0 | +----------+------------+--------------+-------------+ 1 row in set (0.00 sec)获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)，CEIL(x)和CEILING(x)的意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。mysql> select ceil(1.5), ceiling(2.3), floor(5.5); +-----------+--------------+------------+ | ceil(1.5) | ceiling(2.3) | floor(5.5) | +-----------+--------------+------------+ | 2 | 3 | 5 | +-----------+--------------+------------+ 1 row in set (0.00 sec) mysql> select ceil(-1.5), ceiling(-2.3), floor(-5.5); +------------+---------------+-------------+ | ceil(-1.5) | ceiling(-2.3) | floor(-5.5) | +------------+---------------+-------------+ | -1 | -2 | -6 | +------------+---------------+-------------+ 1 row in set (0.00 sec)获取随机数的函数RAND()和RAND(x)，当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。mysql> select rand(), rand(), rand(); +----------------------+---------------------+--------------------+ | rand() | rand() | rand() | +----------------------+---------------------+--------------------+ | 0.009510553450799132 | 0.13407438167750313 | 0.6418402787687632 | +-----…

MySQL集合函数
type: item
date: 2022-10-09T07:56:00+00:00
description: 创建新表并插入新数据mysql> create table user( -> id int primary key auto_increment, -> name varchar(100) not null, -> age tinyint, -> email varchar(30), -> gender enum('male', 'female') -> ); Query OK, 0 rows affected (0.01 sec) mysql> desc user; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint | YES | | NULL | | | email | varchar(30) | YES | | NULL | | | gender | enum('male','female') | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> insert into user (name, age, email, gender) values -> ('ronie', 18, '123@163.com', 'male'), -> ('jack', 22, '456@163.com', 'male'), -> ('lucy', 20, '789@163.com', 'female'), -> ('mike', 30, '147@163.com', 'male'), -> ('kelly', 28, '258@163.com', 'female'), Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into user (name, gender) values ('jerry', 'male'); Query OK, 1 row affected (0.00 sec) mysql> insert into user (name, age, gender) values ('tom', 20, 'male'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+-------+------+-------------+--------+ | id | name | age | email | gender | +----+-------+------+-------------+--------+ | 1 | ronie | 18 | 123@163.com | male | | 2 | jack | 22 | 456@163.com | male | | 3 | lucy | 20 | 789@163.com | female | | 4 | mike | 30 | 147@163.com | male | | 5 | kelly | 28 | 258@163.com | female | | 6 | jerry | NULL | NULL | male | | 7 | tom | 20 | NULL | male | +----+-------+------+-------------+--------+ 7 rows in set (0.00 sec) MySQL集合函数COUNT()函数COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) …

MySQL查询语句
type: item
date: 2022-10-09T06:40:00+00:00
description: 查询语句语法：SELECT {* | 字段列表} [ FROM <表1> <表2> [ WHERE <表达式>] [ GROUP BY <group by definition>] [ HAVING <expression> [{<operator> <expression>} ...]] [ ORDER BY <order by definition>v] [ LIMIT [<offset>], <row count>] ];{* | <字段列表>}包含星号通配符和字段列表，表示查询的字段。其中，字段列表至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不加逗号。FROM <表1>,<表2>...，表1和表2表示查询数据的来源，可以是单个或者多个。WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。ORDER BY <字段>，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）、降序（DESC）。LIMIT [,] ，该子句告诉MySQL每次显示查询出来的数据条数。创建表mysql> create table food( -> id int primary key auto_increment, -> name varchar(100), -> price float default 0, -> produce_by varchar(100) not null, -> taste enum('good', 'normal', 'bad') -> ); Query OK, 0 rows affected (0.02 sec) mysql> desc food; +------------+-----------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | price | float | YES | | 0 | | | produce_by | varchar(100) | NO | | NULL | | | taste | enum('good','normal','bad') | YES | | NULL | | +------------+-----------------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)插入数据mysql> insert into food (name, price, produce_by, taste) values -> ('breed', 10, 'ronie', 'good'), -> ('noodles', 5, 'jack', 'normal'), -> ('chicken', 15, 'kelly', 'good'), -> ('fish', 12, 'lucy', 'bad'), -> ('rice', 3, 'jerry', 'normal'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 单表查询查询所有字段：在SELECT语句中使用星号（*）通配符查询所有字段。语法：SELECT * FROM 表名;。使用星号（*）通配符时，将返回所有列，列按照定义表时候的顺序显示。mysql> select * from food; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 1 | breed | 10 | ronie | good | | 2 | noodles | 5 | jack | normal | | 3 | chicken | 15 | kelly…