nbk

组合 TIMEDIFF 和 TIME_TO SEC 和 SEC_TO_TIME

select SEC_TO_TIME(TIME_TO_SEC(timediff('09:00:00','08:00:00')) / 3)

| SEC_TO_TIME(TIME_TO_SEC(timediff('09:00:00','08:00:00')) / 3) |
| :------------------------------------------------------------ |
| 00:20:00.0000                                                 |

db<>不停摆弄 here

奥武夫向大家展示了一段未被借出的视频

CREATE VIEW `Video not rented` AS 
SELECT  loan.period, loan.v_id, COUNT(*) AS amount
FROM loan 
GROUP BY loan.v_id,loan.period
HAVING  amount = 0;

你可以用它来获得最后一个周期

CREATE VIEW `Video not rented` AS 
SELECT  MAX(loan.period), loan.v_id, COUNT(*) AS amount
FROM loan 
GROUP BY loan.v_id
HAVING  amount = 0;

您可以使用CASE HWEN来确定这两个组。

价格中间有1英镑

SELECT * FROM product
ORDER By CASE WHEN `price` BETWEEN 10 AND 15 Then 1 
ELSE 2 END ASC, `price` ASC

Title  | price
:----- | ----:
Prod 1 |    10
Prod 4 |    11
Prod 5 |    12
Prod 3 |    15
Prod 2 |     8
Prod 6 |    20
Prod 7 |    24

db<>不停摆弄 here

编辑我的。ini(cnf)

加上

[mysqld]
default-time-zone = "+03:00"

所以即使服务器重启,3时区也会改变

或者你需要更高的权利来做这件事

sudo mysql -e "SET GLOBAL time_zone = ‘+3:00’;"

你可以用。文件夹必须具有写入权限,如果使用--secure file priv,则会出现错误1250,必须启用它

SELECT r1.*,r2.* 
FROM role_permission  r1 
     INNER JOIN role r2 ON r1.roleID = r2.roleID 
INTO DUMPFILE '/tmp/world';

这是一样的,你需要 join 桌子

SELECT SUM(cost) FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id WHERE colour='blue';

| SUM(cost) |
| --------: |
|        80 |

db<>不停摆弄 here

你的KSON_包含有点。

Json在大多数情况下都很难处理,因此您应该考虑切换到规范化设计

CREATE TABLE produit__parure
    (`id_parure` int, `ids_produits_parure` json)
;
    
INSERT INTO produit__parure
    (`id_parure`, `ids_produits_parure`)
VALUES
    ('21', '["34809", "34823", "34813"]'),
    ('22', '["35703", "35854", "35877"]')
;

CREATE TABLE produit
    (`id_product` int, `other columns` varchar(3))
;
    
INSERT INTO produit
    (`id_product`, `other columns`)
VALUES
    (34809, '...'),
    (34810, '...')

;

SELECT JSON_CONTAINS(`ids_produits_parure`, CONCAT('"','34809' ,'"'), '$') FROM produit__parure

| JSON_CONTAINS(`ids_produits_parure`, CONCAT('"','34809' ,'"'), '$') |
| ------------------------------------------------------------------: |
|                                                                   1 |
|                                                                   0 |

SELECT p.id_product, pp.* FROM produit p left JOIN produit__parure pp on
JSON_CONTAINS(pp.ids_produits_parure, CONCAT('"',p.id_product,'"'),'$')
where id_product=34809

id_product | id_parure | ids_produits_parure                                   
---------: | --------: | :-----------------------------------------------------
     34809 |        21 | 5b223334383039222c20223334383233222c20223334383133225d

db<>不停摆弄 here

您可以为用户_id1合并选择朋友_id和成员_id

SELECT
user_id, token
FROM tokens 
WHERE user_id IN (SELECT friend_id FROM committee WHERE user_id = 1 
                 UNION SELECT member_id FROM committee WHERE user_id = 1)

user_id | token    
------: | :--------
      2 | xxkakdasd
      3 | 123kdkfs 
      4 | hjj32    

db<>不停摆弄 here

旧值在更新之前,例如使用old。抽奖开始日期

通过使用例如NEW获得的更新值。抽奖开始日期

CREATE 
    TRIGGER  `Update Raffle Ticket Pro`
 AFTER UPDATE ON `O1cUxE_jet_cct_raffles` FOR EACH ROW 
    UPDATE O1cUxE_rafflepro_tickets_setting 
      SET raffle_dates_from = NEW.raffle_start_date 
      WHERE O1cUxE_rafflepro_tickets_setting.category_id = NEW.raffle_name

行是mysql中的保留字

对文本使用记号

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
set @i = 0;
select @i := @i+1 as 'Row',Target,StartDate,EndDate
from (
         select target,
                StartDate,
                EndDate
         from t_learning_note
         where 1 = 1
           and target= @TargetSn
         order by StartDate desc
     )
COMMIT;

正如Akina指出的,这在mysql中运行,但可能会导致问题。

所以你也可以用像

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
set @i = 0;
select @i := @i+1 as `Row`,Target,StartDate,EndDate
from (
         select target,
                StartDate,
                EndDate
         from t_learning_note
         where 1 = 1
           and target= @TargetSn
         order by StartDate desc
     )
COMMIT;

用这个

SELECT *  
FROM tablename  
#WHERE work_complete = 'Yes' 
INTO OUTFILE 'C:\file path for the file to be exported to\file_name.csv'   
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

我使用了一些其他语法,并删除了mysql不喜欢的

对结果使用此查询

SELECT GROUP_CONCAT(CONCAT(`username` , '-',`id`) SEPARATOR ',')
FROm table1;

结果

GROUP_CONCAT(CONCAT(`username` , '-',`id`) SEPARATOR ',')
rap-1,dafy-2,kay-3 .

示例PHP代码wuth PDO

<?php
  try {
    $pdo = new PDO('mysql:host=localhost;dbname=databasename', 'username', 'password');
  } catch (PDOException $e) {
    echo 'Verbindung fehlgeschlagen: ' . $e->getMessage();
  }

  $sql = "SELECT GROUP_CONCAT(CONCAT(`username` , '-',`id`) SEPARATOR ',') user FROM Table1";
  foreach ($pdo->query($sql) as $row) {
   echo $row['user']."<br />";
  }
  $pdo ->close(); 
?>

安装mysql连接器 https://dev.mysql.com/downloads/connector/cpp/

https://dev.mysql.com/doc/connector-cpp/8.0/en/

尤其是 https://dev.mysql.com/doc/connector-cpp/8.0/en/connector-cpp-apps-windows-notes.html

C:\程序文件(x86)\ mysql \ mysql连接器c++ 8 \LIb2

例如,您必须将nchar更改为varchar。

ALTER TABLE `best_bpgallery`.`k96qw_bpgallery_images` 
CHANGE COLUMN `language` `language` VARCHAR(7) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL DEFAULT '*' ;

nchar不支持charecter集

create语句应该如下

CREATE TABLE orders (
  id_orders INT NOT NULL
, date DATE NOT NULL
, id_client INT
, CONSTRAINT fk_client FOREIGN KEY (id_client)
  REFERENCES client(id_client) );

比较两个相同的表,并检查两个表的emailAddress相同的所有情况。

如果ID相同,则忽略该行。

如果 ID不同 必须有身份证 比第一次出现的ID大 ,此行将被删除。