您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 基礎 SQL 大全：建庫、建表、增刪改查、權限管理一篇通

編輯：MySQL 綜合教學

資料庫層級操作

-- 顯示所有資料庫
SHOW DATABASES;

-- 建立資料庫
CREATE DATABASE rewin;

-- 刪除資料庫
DROP DATABASE rewin;

-- 選擇資料庫
USE rewin;

資料表層級操作

-- 顯示所有表
SHOW TABLES;

-- 建立表 (MyISAM 引擎)
CREATE TABLE `zhangyan` (
  `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(20) NOT NULL,
  `password` CHAR(32) NOT NULL,
  `time` DATETIME NOT NULL,
  `number` FLOAT(10) NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM;

-- 查看表結構
DESCRIBE zhangyan;

資料操作 (DML)

-- 查詢所有
SELECT * FROM zhangyan;

-- 條件查詢 + 排序
SELECT * FROM zhangyan WHERE username='abc' AND number='1' ORDER BY id DESC;

-- 指定欄位
SELECT username, password FROM zhangyan;

-- 去重
SELECT DISTINCT username FROM zhangyan;

-- 新增
INSERT INTO zhangyan (id, username, password, time, number, content)
VALUES ('', 'abc', '123456', '2007-08-06 14:32:12', '23.41', 'hello world');

-- 更新
UPDATE zhangyan SET content='hello china' WHERE username='abc';

-- 刪除指定列
DELETE FROM zhangyan WHERE id=1;

-- 清空表
DELETE FROM zhangyan;  -- 或 TRUNCATE TABLE zhangyan;

-- 刪除表
DROP TABLE zhangyan;

-- 修改欄位型別
ALTER TABLE zhangyan CHANGE username username CHAR(25);

-- 匯入 SQL 檔
SOURCE ./mysql.sql;

權限管理

-- 建立超級管理員 (任意 IP)
GRANT ALL PRIVILEGES ON *.* TO 'sina'@'%' IDENTIFIED BY 'zhangyan' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 建立具備 DML+DDL 權限，限定 IP 段
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
      CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON *.* TO 'sina'@'192.168.1.%' IDENTIFIED BY 'zhangyan';

-- 僅特定表 DML 權限
GRANT SELECT, INSERT, UPDATE, DELETE ON rewin.zhangyan
TO 'sina'@'192.168.1.24' IDENTIFIED BY 'zhangyan';

-- 特定資料庫完整權限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
      CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON rewin.* TO 'sina'@'%' IDENTIFIED BY 'zhangyan';

-- 刪除使用者
DROP USER 'sina'@'%';