(1) 查看當前庫所有mysql用戶：

SELECT HOST,USER FROM mysql.user;

(2)創建一個新用戶，密碼自己定：

CREATE USER 'sjdb'@'localhost' IDENTIFIED BY 'password';

(3)給新用戶增加增刪改查權限：

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO sjdb@"localhost" IDENTIFIED BY "password"

(4)如果需要更多權限，作如下設置：

GRANT ALL PRIVILEGES ON *.* TO 'sjdb'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

(5)提交設置使及時生效：

FLUSH?? PRIVILEGES;

(6)查看新用戶狀態：

SELECT USER FROM mysql.user WHERE USER='sjdb';

(7)創建新數據庫：

CREATE DATABASE bd_jrdb;

(8)給新建用戶對賦予對新創建數據庫的操作權限：

GRANT ALL PRIVILEGES ON bd_jrdb.* TO sjdb@localhost IDENTIFIED BY 'password';

(9)在新庫中創建新的庫表SMTCN_EXCH_SECU：

CREATE TABLE `SMTCN_EXCH_SECU` (

`ID` INT NOT NULL,

`INFO_SOUR` VARCHAR(200) CHARACTER SET utf8 ,

`PUB_DT` DATETIME,

`SECU_ID` INT NOT NULL,

`BGN_DT` DATETIME NOT NULL,

`END_DT` DATETIME NOT NULL,

`IS_VALID` VARCHAR(1) CHARACTER SET utf8? NOT NULL,

`TYP_CODEII` INT NOT NULL,

`ENT_TIME` DATETIME NOT NULL,

`UPD_TIME` DATETIME NOT NULL,

`GRD_TIME` DATETIME NOT NULL,

`RS_ID` VARCHAR(20) CHARACTER SET utf8? NOT NULL,

`REC_ID` VARCHAR(50) CHARACTER SET utf8 ,

PRIMARY KEY (`ID`),

INDEX `IDX_SMTCN_EXCH_SECU` (`SECU_ID`, `BGN_DT`),

INDEX `IDX_SMTCN_EXCH_SECU_RID` (`REC_ID`),

INDEX `IDX_SMTCN_EXCH_SECU_UPDTIME` (`UPD_TIME`)

) ENGINE=InnoDB;

(10)給新表SMTCN_EXCH_SECU插入數據：

INSERT INTO `SMTCN_EXCH_SECU` VALUES(637223500, NULL, NULL, 14725, '2010-03-31', '4000-12-31', '1', 1001, '2012-11-18 08:30:03', '2015-10-16 12:50:03', '2015-03-26 11:14:53', 'JY', '325373671907');

(11)刪除表SMTCN_EXCH_SECU中secu_id在12175到52178區間的數據：

DELETE FROM? WHERE? secu_id? BETWEEN 12175 AND 52178;

(12)在表SMTCN_EXCH_SECU選出secu_id, end_dt并按secu_id降序排序：

SELECT secu_id, end_dt FROM SMTCN_EXCH_SECU ORDER BY secu_id DESC;

(13)在表SMTCN_EXCH_SECU選出secu_id, end_dt并按secu_id升序排序：

SELECT secu_id, end_dt FROM SMTCN_EXCH_SECU ORDER BY secu_id ASC;

(14)統計bd_jrdb數據庫中所有表的數量：

SELECT count(*) TABLES, table_schema FROM information_schema.TABLES where table_schema = 'bd_jrdb' GROUP BY table_schema;

(15)列出bd_jrdb數據庫中所有表的名稱：

SELECT table_name FROM information_schema.tables WHERE table_schema='bd_jrdb';

(16)取消jrdb用戶對數據庫的操作權限

REVOKE ALL PRIVILEGES ON *.* FROM sjdb@localhost;

(17)刪除sjdb用戶

DELETE FROM mysql.user WHERE USER='sjdb' AND HOST='localhost';

(18)查看bd_jrdb數據庫字符集：

SHOW VARIABLES LIKE 'character_set_%';

(19) 查找表SMTCN_EXCH_SECU中開始時間和結束時間大于2015年，更新時間大于2016年的數據

SELECT CONCAT(id, " ", secu_id) FROM? SMTCN_EXCH_SECU WHERE BEG_DT/END_DT > 2015 AND? UPD_TIME>2016;

(20)鎖定數據表，避免在備份過程中，表被更新

LOCK TABLES READ SMTCN_EXCH_SECU;

(21)導出備份數據：

SELECT * INTO OUTFILE SMTCN_EXCH_SECU.bak’ FROM SMTCN_EXCH_SECU;

(22)解鎖表：

UNLOCK TABLES;

(23)統計一張表有多少條數據：

SELECT COUNT(*) FROM? SMTCN_EXCH_SECU;

總結

以上是生活随笔為你收集整理的mysql 开户机构_mysql开户、权限设置、建库流程及常用操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。