MySQL的基础知识

常识

  • SQL 语句对大小写不敏感
  • mysql是多线程
  • % 是通配符代表0或多个字符（如模糊搜索用到的）
  • _ 是通配符代表一个字符
  • [^asdf]或[sdafsf] 通配符代表一个字符（和同正则）
  • 用户变量：用 @+变量名（文字数字字符、‘.’、‘_’和‘$’组成）如@p1=123（用户变量名对大小写不敏感）
  • 系统变量： 全局和会话变量（全局变量GLOBAL影响服务器整体操作。会话变量SESSION影响具体客户端连接的操作）具体设置看手册

注释

注释一些其他SQL数据库采用“–”作为注释开始标志。MySQL服务器采用“#”作为注释起始字符。对于MySQL服务器，也能使用C风格的注释：/该处为注释/。

  • ‘#’字符从行尾。

  • ‘– ’序列到行尾（要有空格）。

  • /* 序列到后面的 */多行注释。

管理

  • mysql是一个命令行客户程序，用于交互式或以批处理模式执行SQL语句。

  • mysqladmin是用于管理功能的客户程序。

  • mysqlcheck执行表维护操作。

  • mysqldump和mysqlhotcopy负责数据库备份。

  • mysqlimport导入数据文件。

  • mysqlshow显示信息数据库和表的相关信息。

常用

命令 含义
SELECT 从数据库表中获取数据
UPDATE 更新数据库表中的数据
DELETE 从数据库表中删除数据
INSERT INTO 向数据库表中插入数据

比较重要

命令 含义
CREATE DATABASE 创建新数据库
ALTER DATABASE 修改数据库
CREATE TABLE 创建新表
ALTER TABLE 变更（改变）数据库表
DROP TABLE 删除表
CREATE INDEX 创建索引（搜索键）
DROP INDEX 删除索引

查看数据库与表使用空间

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name';
select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’DB_Name’ and table_name=’Table_Name’;
SQL

常用demo

-- 查找
SELECT field1,field2 FROM 'tablename'
--插入
INSERT INTO table_name (列1, 列2,...) VALUES ('值1', '值2',....)
-- 更新
UPDATE 表名称 SET 列名称 = '新值' WHERE 列名称 = '某值'
-- 删除
DELETE FROM 表名称 WHERE 列名称 = '值'
-- 模糊查找
SELECT * FROM `tablename` WHERE `field` LIKE '%123456%'
-- 查看表状态
SHOW TABLE STATUS
-- 查看权限
SHOW TABLE GRANTS
-- 修改（再字段后新加字段）
ALTER TABLE `tablename` ADD `type` VARCHAR(255) NOT NULL COMMENT '注释' AFTER `field`
-- 修改（给fieldname增加索引）
ALTER TABLE `tablename` ADD INDEX( `fieldname`)
-- 修改（将表中id设置为主键）
ALTER TABLE `tablename` ADD PRIMARY KEY( `id`)
-- 修改（id改为无符号，非空，自增）
ALTER TABLE `20180703nx_jsks_user` CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id'
-- 修改自增初始值
ALTER TABLE `tablename` AUTO_INCREMENT=100000
-- 清空表
TRUNCATE TABLE `tablename`
-- 创建表
CREATE TABLE 表名称
(
    列名称1 数据类型,
    列名称2 数据类型,
    列名称3 数据类型,
....
)
-- 如1
CREATE TABLE `tablename` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `username` varchar(255) NOT NULL COMMENT '注释1',
  `phone` char(11) NOT NULL COMMENT '注释2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 如2
CREATE TABLE `database`.`tablename` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '自增id' ,
    `username` CHAR NOT NULL COMMENT '姓名' , 
    `phone` CHAR(11) NOT NULL COMMENT '手机号' ,
    `province` VARCHAR(255) NULL DEFAULT NULL COMMENT '省份' , 
    PRIMARY KEY (`id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci COMMENT = '表注释';
SQL

权限

  • 增加权限
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY 'password']
        [, user_name [IDENTIFIED BY 'password'] ...]
    [WITH GRANT OPTION]
-- 如：
select password('你想输入的密码');
GRANT ALL PRIVILEGES ON db.user_table TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD 'yourpassrord' WITH GRANT OPTION;
-- 授权之后要刷新权限
FLUSH PRIVILEGES;
SQL
  • 撤销权限
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]
-- 如：
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
-- 撤销授权后要刷新权限
FLUSH PRIVILEGES;

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
set character_set_results=utf8mb4;
SQL