MySQL常用语句

1.数据库

--建立数据库并指定默认字符集
CREATE DATABASE `database_name` DEFAULT CHARACTER
SET utf8 COLLATE utf8_general_ci;

--删除数据库
DROP DATABASE `database_name`;

---

2.用户

--查看所有用户
SELECT * FROM mysql.user;

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

CREATE USER 'user'@'localhost' IDENTIFIED BY '123456'; --仅能从本机登陆，且密码为123456
CREATE USER 'user'@'192.168.1.101' IDENTIFIED BY '123456'; --仅能从192.168.1.101登陆
CREATE USER 'user'@'%' IDENTIFIED BY ''; --可从任意主机登陆，且不需要密码

--删除用户
DROP USER 'username'@'host';

---

3.授权

--授予用户增删改查及建立删除表的权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON databases_name.* to 'username'@'host';

--取消用户增删改查及建立删除表的权限
REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON databases_name.* to 'username'@'host';

---

4.数据表

--建表示例
USE database_name;
SET NAMES utf8;
CREATE TABLE `table_name` (
    `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `var1` char(10) NOT NULL DEFAULT '' COMMENT 'xxxxxx',
    `var2` varchar(30) NOT NULL DEFAULT '' COMMENT 'xxxxxx',
    `var3` tinyint NOT NULL DEFAULT 0 COMMENT 'xxxxxx',
    `var4` bigint unsigned NOT NULL DEFAULT 0 COMMENT 'xxxxxx',
    `var5` decimal(6, 2) unsigned NOT NULL DEFAULT 0 COMMENT 'xxxxxx',
    `var6` date NOT NULL DEFAULT '1000-01-01' COMMENT 'xxxxxx',
    `var7` datetime NOT NULL DEFAULT '1000-01-01' COMMENT 'xxxxxx',
    `var8` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'xxxxxx',
    `var9` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'xxxxxx',
    PRIMARY KEY (`id`),
    KEY `idx_var1` (`var1`),
    UNIQUE KEY `uniq_var2` (`var2`),
    KEY `idx_var3_var4_var8` (`var3`, `var4`, `var8`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='示例表';

--删除表数据及表定义
DROP TABLE `table_name`;

---

5.增删改查

-- 插入
INSERT INTO `table_name` (`var1`, `var2`, `var3`)
VALUES
    ('xx1', 'xx1', 'xx1'),
    ('xx2', 'xx2', 'xx2'),
    ('xx3', 'xx3', 'xx3');

-- 查询
SELECT *
FROM `table_name`;

-- 更新
UPDATE `table_name`
SET var1 = var1 + 1;

-- 删除
DELETE FROM `table_name`;       --删除表中数据(可以where做定向删除,自增列不重置)
TRUNCATE TABLE `table_name`;    --删除表中数据(删除表中所有数据,自增列重置)

--存在更新，不存在添加
INSERT INTO `table_name` (`var1`, `var2`, `var3`)
VALUES
    ('xx1', 'xx2', 1) ON DUPLICATE KEY UPDATE var3 = var3 + 1;