使用 ORDER BY 对重复记录进行排序。让我们首先创建一个表 -mysql> create table DemoTable788 ( FirstName varchar(100), Score int ); Query OK, 0 rows affected (1.89 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable788 values('Chris', 78); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable788 values('Robert', 67); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable788 values('Chris', 98); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable788 values('Chris', 56); Query OK, 1 row affected (0.15 sec) mysql> insert into ... 阅读更多

让我们首先创建一个表 -mysql> create table DemoTable787 ( Score1 int, Score2 int, Name varchar(100) ); Query OK, 0 rows affected (0.84 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable787 values(34, 56, 'Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable787 values(73, 86, 'Robert'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable787 values(90, 99, 'David'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable787 values(80, 89, 'Adam'); Query OK, 1 row affected (0.21 sec)显示所有记录 ... 阅读更多

要将列值显示为 CSV，请使用 GROUP_CONCAT()。让我们首先创建一个表 -mysql> create table DemoTable786 (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentName varchar(100) )   AUTO_INCREMENT=101; Query OK, 0 rows affected (0.70 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable786(StudentName) values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable786(StudentName) values('Robert'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable786(StudentName) values('Mike'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable786(StudentName) values('Sam'); Query OK, 1 row affected (0.12 sec)显示表中的所有记录 ... 阅读更多

对于特定一周，使用 DAYOFWEEK()。让我们首先创建一个表 -mysql> create table DemoTable785 ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(100), ShoppingDate date ); Query OK, 0 rows affected (0.61 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable785(CustomerName, ShoppingDate) values('Chris', '2019-07-03'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable785(CustomerName, ShoppingDate) values('Robert', '2019-07-01'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable785(CustomerName, ShoppingDate) values('David', '2019-07-06'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable785(CustomerName, ShoppingDate) ... 阅读更多

要忽略排序列值时的引号，请使用 ORDER BY TRIM()。让我们首先创建一个表 -mysql> create table DemoTable784 ( Message varchar(100) ); Query OK, 0 rows affected (0.50 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable784 values('Good'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable784 values('\"This is not a Message\"'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable784 values('Bye'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable784 values('Hello'); Query OK, 1 row affected (0.15 sec)使用 ... 阅读更多

让我们首先创建一个表 -mysql> create table DemoTable783 ( FirstName varchar(100) ); Query OK, 0 rows affected (0.65 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable783 values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable783 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable783 values('Robert'); Query OK, 1 ... 阅读更多

为此，请使用 GROUP BY HAVING 子句。让我们首先创建一个表 -mysql> create table DemoTable782 ( Name varchar(100), Score int ); Query OK, 0 rows affected (1.18 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable782 values('John', 156); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable782 values('Carol', 250); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable782 values('Bob', 140); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable782 values('John', 126); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable782 values('John', ... 阅读更多

让我们首先创建一个表 -mysql> create table DemoTable781 ( Name varchar(100) ); Query OK, 0 rows affected (0.66 sec)使用 insert 命令在表中插入一些记录 -mysql> insert into DemoTable781 values(''); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable781 values('Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable781 values(''); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable781 values(null); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable781 values(null); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable781 values(''); Query OK, 1 ... 阅读更多

要排除某些列名，请使用 NOT IN。让我们首先创建一个表 -mysql> create table DemoTable780 (    CustomerId int,    CustomerName varchar(100),    CustomerAge int, CustomerCountryName varchar(100), isMarried tinyint(1) ); Query OK, 0 rows affected (0.47 sec)这是排除结果的查询 -mysql> select group_concat(column_name) from `information_schema`.`COLUMNS` m where table_schema = 'web' and table_name = 'DemoTable780' and column_name not in ('CustomerId','CustomerCountryName') group by table_schema,table_name;这将产生以下输出 -+------------------------------------+ | group_concat(column_name) | +------------------------------------+ | CustomerName,CustomerAge,isMarried | +------------------------------------+ 1 row in set (0.01 sec)

由于列值“ADD”已经是保留字，因此需要在ADD周围使用反引号，例如`ADD`。让我们看一个示例并创建一个表：mysql> create table DemoTable779 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, `ADD` int ); Query OK, 0 rows affected (0.47 sec)使用insert命令在表中插入一些记录：mysql> insert into DemoTable779(`ADD`) values(54); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable779(`ADD`) values(89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable779(`ADD`) values(66); Query OK, 1 row affected ... 阅读更多