Mysql学习

基础命令

mysql -uroot -p -P3306 -h127.0.0.1  　　  # 会提示输入密码
mysql -u root -p -h 127.0.0.1 sampdb     # 会提示输出密码，登录后默认使用sampdb数据库
mysql < my.sql　　# 执行sql文件（为登录）
SOURCE my.sql　　 # 执行sql文件（已登录）

quit    # 退出

CREATE DATABASE sampdb;　　　# 创建sampdb数据库
SHOW DATABASES;　　　　　　　　# 显示所有数据库
SELECT DATABASE();　　　　　　# 查询当前使用的数据库
USE sampdb;　　　　　　　　　　# 指定使用sampdb数据库 

# 创建表user
CREATE TABLE `user` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

DESCRIBE `user`;　　　　　　# 展示创建表的详细信息
DESCRIBE `user` 'name'    # 只展示name字段
DESC `user`;　　　　　　　　 # 展示创建表的详细信息
SHOW COLUMNS FROM `user`; # 展示创建表的详细信息
SHOW FIELDS FROM `user`;  # 展示创建表的详细信息

SHOW TABLES;　　　　　　　　 # 显示所有表

SELECT * FROM `user`;　　　　# 查询

INSERT INTO `user` VALUES('1', 'zhang1', '11', '11@sampdb.com');　　# 按顺序插入一条数据（所有字段）
INSERT INTO `user` VALUES ('2', 'zhang2', '12', '12@sampdb.com'),('3', 'zhang3', '13', '13@sampdb.com');　　# 按顺序插入多条数据（所有字段）
INSERT INTO `user` (id, `name`, age, email) VALUES ('4', 'zhang4', '14', '14@sampdb.com');　　# 按指定字段插入一条数据
INSERT INTO `user` (id, `name`, age, email) VALUES ('5', 'zhang5', '15', '15@sampdb.com'),('6', 'zhang6', '16', '16@sampdb.com'); # 按指定字段插入多条数据
INSERT INTO `user` SET id='7', `name`='zhang7', age='17', email='17@sampdb.com';　　# 使用set添加指定字段

 数据库模式：

　　1、STRICT_ALL_TABLES和STRICT_TRANS_TABLES：严格模式，拒绝“坏”数据值

　　2、TRADITIONAL：复合模式（类似严格模式），处理”坏“数据更接近于传统SQL服务器

　　3、ANSI_QUOTES：识别双引号为【标识符引用字符】

　　4、PIPES_AS_CONCAT：识别“||”为标准的SQL【字符串合并操作符】

　　5、ANSI：复合模式（ANSI_QUOTES，PIPES_AS_CONCAT和另外几种模式值），更接近于标准的SQL

SET sql_mode = 'TRADITIONAL'           # 设置回话级SQL模式
SET GLOBAL sql_mode = 'TRADITIONAL'    # 设置全局级SQL模式  

SELECT @@SESSION.sql_mode;            # 查看会话级SQL模式
SELECT @@GLOBAL.sql_mode;             # 查看全局级SQL模式

数据库字符集：

SHOW CHARACTER SET LIKE 'latin%';    # 查看字符集种类
SHOW VARIABLES LIKE 'character\_set\_%';    # 查看当前MySQL服务器字符集
SHOW VARIABLES LIKE 'collation\_%';    # 查看当前MySQL服务器排序方式

CREATE DATABASE sampdb CHARACTER SET utf8 COLLATE  utf_danish_ci;    # 创建数据库时设置字符集
CREATE TABLE tb_samp (...)  CHARACTER SET utf8 COLLATE  utf_danish_ci;    # 创建数据表时设置字符集
c_samp CHAR(10) CHARACTER SET utf8 COLLATE  utf_danish_ci;     # 创建属性时设置字符集

数据库存储引擎：

　　1、ARCHIVE：用于数据存档（数据插入后不能修改）

　　2、BLACKHOLE：写操作是删除数据，读操作是返回空白记录

　　3、CSV：存储数据以逗号（，） 作分隔符

　　4、EXAMPLE：存根存储引擎

　　5、Falcon：事务处理

　　6、FEDERATED：访问远程数据表

　　7、InnoDB：具备外键，事务处理

　　8、MEMORY：基于内存

　　9、MERGE：管理有多个MyISAM数据表构成的数据表集合

　　10、MyISAM：默认

　　11、NDB：MySQL Cluster专用存储引擎

注：这些信息位于information_schema.ENGINES表中

SHOW ENGINES;    # 查看MySQL服务器的存储引擎
ALTER TABLE tb_name ENGINE = engine_name;　　# 转换表引擎

数据库操作语法：

USE db_name;    # 选定数据库语法
CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation];    # 创建数据库语法
DROP DATABASE db_name;    # 删除数据库语法
ALTER DATABASE [db_name] [CHARACTER SET charset] [COLLATE collation];    # 变更数据库语法

数据表操作语法：

格式文件：每创建一个数据表，MySQL就会创建一个文件来保存该数据表的格式（.frm）

数据文件：

存储引擎	硬盘上的文件
MyISAM	.MYD(数据)、.MYI(索引)
MERGE	.MRG(由MyISAM数据表的名字构成的清单)
InnoDB	.ibd(数据和索引)
ARCHIVE	.ARZ(数据)、.ARM(元数据)
CSV	.CSV(数据)、.CSM(元数据)

CREATE [IF NOT EXISTS] [TEMPORARY] tb_name (
    column_name column_type [NULL|NOT NULL] [DEFAULT default_data] [COMMENT comment_info]
)ENGINE=engine_name DEFAULT CHARSET=charset;    # 创建表语法

CREATE [IF NOT EXISTS] [TEMPORARY] TABLE tb_name_1 LIKE tb_name;　　# 创建一个原始表的空白副本
INSERT INTO tb_name_1 SELECT * FRAM tb_name;　　# 从tb_name填充tb_name_1表

CREATE [IF NOT EXISTS] [TEMPORARY] TABLE tb_name_2 SELECT * FRAM tb_name;　　# 创建一个表容纳SELECT查询结果

DROP [TEMPORARY] TABLE [IF EXISTS] tb_name[,tb_name...];　　# 删除表 

ALTER TABLE tb_name ADD INDEX index_name (index_columns);    # 添加索引
ALTER TABLE tb_name ADD UNIQUE index_name (index_columns);    # 添加唯一性
ALTER TABLE tb_name ADD PRIMARY KEY (index_columns);    # 添加主键
ALTER TABLE tb_name ADD FULLTEXT index_name (index_columns);    # 添加全量索引
ALTER TABLE tb_name ADD SPATIAL index_name (index_columns);    #  添加空间索引

CREATE INDEX index_name ON tb_name (index_columns);
CREATE UNIQUE INDEX index_name ON tb_name (index_columns);
CREATE FULLTEXT INDEX index_name ON tb_name (index_columns);
CREATE SPATIAL INDEX index_name ON tb_name (index_columns);

CREATE TABLE tb_name(
    INDEX index_name (index_columns),
    UNIQUE index_name (index_columns),
    PRIMARY KEY (index_columns),
    FULLTEXT index_name (index_columns),
    SPATIAL index_name (index_columns)
);

CREATE TABLE tb_name (
    i INT NOT NULL PRIMARY KEY,
    j CHAR(10) NOT NULL UNIQUE
);
CREATE TABLE tb_name (
    i INT NOT NULL,
    j CHAR(10) NOT NULL,
    PRIMARY KEY (i),
    UNIQUE (j)
);

CREATE TABLE tb_name (
    name CHAR(30) NOT NULL,
    address BINARY(60) NOT NULL,
    INDEX (name(10)),
    INDEX (address(15))
)    # 字符前缀索引

DROP INDEX index_name ON tb_name;    # 删除索引
ALTER TABLE tb_name DROP INDEX index_name;    # 删除索引

ALTER TABLE tb_name MODIFY i MEDIUMINT UNSIGNED;　　# 修改属性类型
ALTER TABLE tb_name CHANGE i k MEDIUMINT UNSIGNED;　　# 修改属性i为k，并变更数据类型

ALTER TABLE tb_name ENGINE = engine_name;　　# 修改表引擎
ALTER TABLE tb_name RENAME TO new_tb_name;　　# 修改表名称
RENAME TABLE tb_name TO new_tb_name[,tb TO new_tb...];　　# 修改表名称

CREATE VIEW v_name AS SELECT * FROM tb_name;　　# 创建视图（使用方式和表一样）

 事务：

事务问题：

　　1、脏读：读取其他事务未提交数据。

　　2、不可重复读：两次读取不一致（其他事务修改数据）

　　3、幻读：两次读取数据条目不一致（其他事务添加数据）

事务隔离级别：

　　1、READ UNCOMMITED：允许读取其他事务未提交数据

　　2、READ COMMITED：只允许读取其他事务已提交数据

　　3、REPEATABLE READ：两次读取一致，允许其他事务插入或修改（InnoDB）

　　4、SERIALIZABLE：两次读取一致，不允许其他事务操作

SELECT @@tx_isolation；　　# 查看事务隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL level;    # 设置全局级事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL level;    设置回话级事务隔离级别
SET TRANSACTION ISOLATION LEVEL level;    设置事务级事务隔离级别（适用于下次事务）

LOCK TABLES tb_name_1 WRITE, tb_name_2 READ;　　# 给tb_name_1添加写锁，给tb_name_2添加读锁
UNLOCK TABLES;　　# 释放前面LOCK所有锁

存储程序：

存储过程：

# 创建存储过程
CREATE PROCEDURE greetings()
BEGIN
    #Routine body goes here...
    DECLARE user_name CHAR(77) CHARACTER SET utf8;
    SET user_name = (SELECT CURRENT_USER());
    IF INSTR(user_name, '@') > 0 THEN
        SET user_name = SUBSTRING(user_name, '@', 1);
    END IF;
    IF user_name = '' THEN
        SET user_name = 'earthling';
    END IF;
    SELECT CONCAT('Greetins, ', user_name, '!') AS greeting;
END;

CALL greetings();    # 调用储存过程

存储函数：

# 创建存储函数
CREATE FUNCTION delimiter(p_year INT) RETURNS INT READS SQL DATA
BEGIN
    #Routine body goes here...
    RETURN (SELECT COUNT(*) FROM `user` WHERE age < p_year);
END;

SELECT delimiter(20) AS year_count;    # 调用存储函数

 触发器：

# 创建触发器  trigger_stmt：触发器语法部分
# NEW.col_name:引用由INSERT或UPDATE处理的新数据行的数据列
# OLD.col_name:引用由DELETE或UPDATE处理的旧数据行的数据列
CREATE TRIGGRT trigger_name 
    {BEFORE | AFTER} 
    {INSERT | UPDATE | DELETE} 
    ON tb_name
    FROM EACH ROW trigger_stmt;

事件：

# 启动mysql前启动事件调度器
[mysqld]
event_scheduler=ON

SHOW VARIABLES LIKE 'event_scheduler';    # 查看时间调度器状态
SET GLOBAL event_scheduler = OFF;    # 停止事件调度器（SUPER权限）0
SET GLOBAL event_scheduler = ON;    # 启动事件调度器（SUPER权限）1

# 创建事件
CREATE EVENT expire_web_session ON SCHEDULE EVERY 4 HOUR
DO
    DELETE FORM web_session WHERE last_visit < CURRENT_TIMESTAMP - INTERVAL 1 DAY;