要从多个表插入记录，请使用 INSERT INTO SELECT 语句。在这里，我们将从 2 个表插入记录。让我们首先创建一个表 −mysql> create table DemoTable1943 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1943 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1943 values('Robert'); Query OK, 1 row affected (0.00 sec)使用 select 语句显示表中的所有记录 −mysql> select * from DemoTable1943;这将产生以下输出 −+--------+ | Name | +--------+ | Chris ... 阅读更多

为此，请使用正则表达式。语法如下 −select * from information_schema.schemata WHERE SCHEMA_NAME REGEXP '^yourValue_+[A-Z]';让我们创建一些数据库 −mysql> create database bank_APP1; Query OK, 1 row affected (0.00 sec) mysql> create database bank_APP2; Query OK, 1 row affected (0.00 sec) mysql> create database bank_APP3; Query OK, 1 row affected (0.00 sec)以下是获取所有名称中某个词后带有大写字母的数据库的查询 −mysql> select * from information_schema.schemata WHERE SCHEMA_NAME REGEXP '^bank_+[A-Z]';这将产生以下输出 −+--------------+-------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+-------------+----------------------------+------------------------+----------+ | def ... 阅读更多

为此，请使用 COUNT(*) 和 GROUP BY 子句。让我们首先创建一个表 −mysql> create table DemoTable1942 ( Value int ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1942 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1942 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1942 values(3); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1942 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1942 values(3); Query OK, 1 row affected (0.00 ... 阅读更多

要返回计数最高的字段，请使用 ORDER BY COUNT(*)。让我们首先创建一个表 −mysql> create table DemoTable1940 ( FirstName varchar(20) ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1940 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Adam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Chris'); Query OK, 1 row ... 阅读更多

为此，您可以使用 LEFT()。让我们首先创建一个表 −mysql> create table DemoTable1939 ( FullName varchar(20) ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1939 values('Adam Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Robert Downey, Jr.'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Sylvester Stallone'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Chris Hemsworth'); Query OK, 1 row affected (0.00 sec)使用 select 语句显示表中的所有记录 −mysql> ... 阅读更多

要显示所有字段，请使用 table_schema 设置数据库，并使用 table_name 设置特定表，如下面的语法所示 −select column_name as anyAliasName from information_schema.columns where table_schema=database() and table_name=’yourTableName’\G让我们首先创建一个表 −mysql> create table DemoTable1938 ( StudentId int, StudentName varchar(20), StudentAge int, StudentCountryName varchar(20), StudentMobileNumber bigint ); Query OK, 0 rows affected (0.00 sec)以下是显示表所有字段的查询 −mysql> select column_name as ALL_FIELDS from information_schema.columns where table_schema=database() and table_name='DemoTable1938'\G这将产生以下输出 −*************************** 1. row *************************** ALL_FIELDS: StudentId ... 阅读更多

为此，请使用带有 ORDER BY 的 CASE 语句。让我们首先创建一个表 −mysql> create table DemoTable1937 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1937 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values('Adam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values('John'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values(''); Query OK, 1 row affected (0.00 sec) ... 阅读更多

要执行多次插入，语法如下 −insert into yourTableName(yourColumnName1, yourColumnName2, yourColumnName3, ..N) select yourValue1 as yourColumnName1, yourValue2 as yourColumnName2, yourValue3 as yourColumnName3, ......N union select yourValue1 as yourColumnName1, yourValue2 as yourColumnName2, yourValue3 as yourColumnName3, ......N . . N为了理解上述语法，让我们创建一个表 −mysql> create table DemoTable1936 ( StudentId int, StudentName varchar(20), StudentCountryName varchar(20) ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1936(StudentId, StudentName, StudentCountryName) select 1001 as StudentId, 'Chris' as StudentName, 'US' ... 阅读更多

为此，请使用 RLIKE 并按以下语法过滤记录 &Minus;select * from yourTableName where yourColumnName rlike 'yourValue1|yourValue2';让我们首先创建一个表 −mysql> create table DemoTable1935 ( Subject varchar(20) ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1935 values('MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1935 values('Python'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1935 values('MongoDB'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1935 values('SQL Server'); Query OK, 1 row affected (0.00 ... 阅读更多

要获取平均值，请使用 AVG() 并将其与 DISTINCT 一起使用以从不同的记录中计算。让我们首先创建一个表 −mysql> create table DemoTable1934 ( StudentName varchar(20), StudentMarks int ); Query OK, 0 rows affected (0.00 sec)使用 insert 命令在表中插入一些记录 −mysql> insert into DemoTable1934 values('Chris', 56); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1934 values('Chris', 56); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1934 values('David', 78); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1934 values('David', 78); Query OK, 1 row affected ... 阅读更多