Coding Record

  UPDATE table_A ,  table_U SET A.idno=U.idno  WHERE A.keyid=U.keyid

  查有哪些帳號 SELECT User,Host FROM mysql.user; 查帳號權限 SHOW GRANTS FOR backuper; . 首頁 MySQL安全性 MySQL使用者權限設定 MySQL使用者權限設定 web - 5月 31, 2018 MySQL使用者權限資訊用user、db、host、tables_priv和columns_priv表被儲存在mysql資料庫中。 關於使用者權限的相關指令，說明如下 (1)使用 root 進入 MySQL mysql> mysql -u root -p (2)遠端登入 mysql> mysql -u root -h  remote_host_ip  -p remote_host_ip  指你要登入的遠端MySQL (3)修改使用者密碼 mysql> SET PASSWORD FOR  '目標使用者'@'主機'  = PASSWORD(' 密碼 '); mysql> flush privileges; (4)建立使用者，並給予權限 grant usage on *.* to  'username'@'localhost'  identified by ' yourpassword ' with grant option;  grant all privileges on *.* to  'username'@'localhost'  identified by ' yourpassword '; flush privileges; INSERT INTO user(host,user,password) VALUES(' % ',' username ',password(' userpassword ')); GRANT ALL ON *.* TO  'username'@localhost  IDENTIFIED BY  ' userpassword '  WITH GRANT OPTION; FLUSH PRIVILEGES; GRANT SELECT,I...

檢查排程是否有開啟 SELECT @@event_scheduler; SELECT @@global.event_scheduler; 設定開啟 SET GLOBAL event_scheduler = ON ; How to force an event to execute in MySQL Move all the code within the event into a stored procedure Make the event only call the stored procedure Test the stored procedure with the CALL syntax.

  From the Command Line Use the following command to check the version of your local MySQL server. This command is not specific to any operating system. This command is compatible with all Linux, Windows, and macOS versions running MySQL. mysql -V  Here is the output. # mysql -V mysql Ver 14.14 Distrib 5.7.15, for Win32 (AMD64)   SELECT VERSION Statement Use the  SELECT VERSION()  command in the MySQL database client to check the MySQL version. SELECT version(); Here is the output. MariaDB [***]> SELECT version(); +-----------------+ | version() | +-----------------+ | 10.3.37-MariaDB | +-----------------+ 1 row in set (0.000 sec)

  Create file named mysqldump.cmd with contents: @echo off "c:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe" % * --column-statistics=0

  select curdate()  -- 當前日期 select last_day(curdate()) -- 當月最後一天 select date_add(curdate(),interval -day(curdate())+1 day) ; -- 本月的第一天 select day(curdate()); --取得當月已過了幾天 select date_add(curdate()-day(curdate()) +1 , interval 1 month ); -- 取得下個月的第一天 -- 日期格式

 Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode,  toggle the option in Preferences -> SQL Editor and reconnect. 原來是mysql有個叫SQL_SAFE_UPDATES的變數， 為了資料庫更新操作的安全性，此值預設為ON 所以只要在執行下面的指令即可  SET SQL_SAFE_UPDATES=0;//關閉 //You want do something... UPDATE XXXXXX SET XXXXXX WHERE XXXXXXX; DELETE XXXXXX  WHERE XXXXXXX; SET SQL_SAFE_UPDATES=1;//結束後再開啟 大家可以試試看

 mysql 的指令都要有;結尾 登入mysql mysql -u root -p 查詢有哪些資料庫，注意要有;且不能空白 mysql> show databases; 進入資料庫 mysql> show databases; 顯示資料庫有哪些資料表 my sql> show table s; 顯示資料表的Scheme碼 my sql> describe  table ; 顯示資料表前10筆資料 mysql>select * from table limit 10;  參考: https://www.sqlsplus.com/how-to-list-all-databases-in-mysql/ http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm

下載路徑: https://dev.mysql.com/downloads/mysql/ MSI版本: 有IDE介面 Archive版本:CMD介面 24.5M-簡易版，便於攜帶，安裝後仍會自己抓缺少的檔案 427.6M-完整版 點選-Download 設定帳號密碼 資料表的架構 　 欄位 Column  Feature 　 行 　 　 　 　 Row 　 　 　 　 Data Point