mysql学习笔记

Mysql笔记记录，日后查看用

CREATE USER 'sampadm'@'localhost' IDENTIFIED BY 'secret';

GRANT ALL ON sampdb.* TO 'sampdbadm'@'localhost';


mysql -h localhost -u sampadm -p


mysql默认的存储引擎MyISAM.


一.基本命令


1.1 查看系统的日期和时间
SELECT NOW(),USER(),VERSION();


1.1.1 重新定义mysql语句的结束分隔符
DELIMITER 新的分隔符.  eg DELIMETER //


1.2 创建数据库
CREATE DATABASE sampdb;


1.2.1 使用ALTER命令修改数据库的属性，还可以修改存储过程
ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...] 




1.3 查看当前使用的数据库
SELECT DATABASE();


1.4 使用数据库
USE sampdb;


1.5 连接数据库时，可以直接指定使用哪个数据库
mysql -h localhost -p -u sampadm sampdb;


1.6 创建数据表
CREATE TABLE president(last_name VARCHAR(15) NOT NULL,first_name VARCHAR(15) NOT NULL,suffix VARCHAR(15) NULL,city VARCHAR(20) NOT NULL,state VARCHAR(2) NOT NULL,birth DATE NOT NULL,death DATE NULL);
CREATE TABLE member(member_id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (member_id),last_name VARCHAR(20) NOT NULL,first_name VARCHAR(20) NOT NULL,suffix VARCHAR(5) NULL,expiration DATE NULL,email VARCHAR(100) NULL,street VARCHAR(50) NULL,city VARCHAR(50) NULL,state VARCHAR(2) NULL,zip VARCHAR(10) NULL,phone VARCHAR(10) NULL,interests VARCHAR(255) NULL);


1.7 查看表的结构
DESCRIBE president;
SHOW president;
EXPLAIN president;


SHOW TABLES;
SHOW DATABASES;


1.8 创建示例表
CREATE TABLE student(name VARCHAR(20) NOT NULL,sex ENUM('F','M') NOT NULL,student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY(student_id))ENGINE = InnoDB;


CREATE TABLE grade_event(date DATE NOT NULL,category ENUM('T','Q') NOT NULL,event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (event_id))ENGINE = InnoDB;


CREATE TABLE score(student_id INT UNSIGNED NOT NULL,event_id INT UNSIGNED NOT NULL,score INT NOT NULL,PRIMARY KEY(event_id,student_id),INDEX(student_id),FOREIGN KEY(event_id) REFERENCES grade_event(event_id),FOREIGN KEY(student_id) REFERENCES student(student_id))ENGINE = InnoDB;


CREATE TABLE absense(student_id INT UNSIGNED NOT NULL,date DATE NOT NULL,PRIMARY KEY(student_id,date),FOREIGN KEY(student_id) REFERENCES student(student_id))ENGINE = InnoDB;


创建临时数据表 CREATE TEMPORARY TABLE tbl_name...;


1.9 插入数据
INSERT INTO student VALUES('Kyle','M',NULL);
INSERT INTO grade_event VALUES('2008-09-03','Q',NULL);


1.10 从文件中读取数据装进mysql
LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member;


使用source insert_absence.sql执行sql语句.


1.11 检索数据
SELECT what to retrieve FROM table or tables WHERE conditions that data must satisfy;
SELECT student_id,date,score,category FROM grade_event INNER JOIN score ON grade_event.event_id = score.event_id WHERE date = '2008-09-23';


1.12 简化mysql连接与查询




二 存储程序与存储函数


创建存储程序是需要数据库的CREATE ROUTINE权限


2.1存储程序


修饰存储过程的参数使用IN,OUT,INOUT;
CREATE PROCEDURE Proc_cID(IN classID INT) BEGIN SELECT * FROM tb_score WHERE cID=classID; END;


CREATE PROCEDURE testProc()
BEGIN
SELECT * FROM information_schema.engines;
END;


调用:CALL testProc();


如果存储过程中需要传入中文参数，这时需要在定义存储过程的时候，在参数后加上character set gbk，不然调用存储过程使用中文参数会出错。如CREATE PROCEDURE userInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)。


2.2 存储函数
CREATE FUNCTION func_name([func_parameter]) returns type [characteristic] routine_body


eg:
CREATE FUNCTION Query_score(classID INT,studentID INT) RETURNS INT RETURN (SELECT grade FROM tb_score WHERE cID=classID AND sID=studentID); 
调用:SELECT Query_score(1,1);


2.3 存储过程和函数的区别:
   1. 存储过程的功能更加复杂，而函数的功能针对性更强；
   2. 存储过程可以返回参数（通过OUT|INOUT），而函数只能返回单一值或者表对象；
   3. 存储过程作为一个独立的部分来执行，而函数可以作为查询语句的一部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字之后；
   4. 存储过程是通过关键字CALL来调用，作为一个独立的执行部分。而存储函数则可作为SELECT语句的一部分调用，嵌入到SQL语句中；
   5. 当存储过程和函数被执行的时候，SQLManager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQLManager就会对存储过程和函数进行编译。


2.4 定义变量
语法格式为：DECLARE var_name[,var_name]...data_type[DEFAULT value];


2.5 为变量赋值
MySQL中使用SET语句为变量赋值，语法格式为：SET var_name=expr[,var_name=expr]...


2.6 流程控制
MySQL中的用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。


三 触发器
触发器是与特定数据表相关联的存储过程


3.1 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt


CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON tbl_name FOR EACH ROW trigger_stmt;


四 事件
事件是一个与时间表相关联的存储程序，时间表用来定义事件发生的时间，次数及何时消失。


开启事件调度器，需要权限
SET GLOBAL event_scheduler = ON;


CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [COMMENT 'comment']
    DO sql_statement;


五 查询


5.1索引可以加快查询速度，但是降低了数据列的插入删除及修改,并且索引要占用磁盘空间
最适合有索引的数据列是那些在WHERE子句中出现的数据列，在联结子句中的数据列或者是ORDER BY GROUP BY.




六 mysql数据目录
如果从源码安装默认数据位置/usr/local/mysql/var,如果rpm安装,则是/var/lib/mysql/
可以在启动mysqld选择数据存储的位置 --datadir=dirname


七 mysql数据库管理
mysql安装后默认有两个数据库mysql,test。mysql超级用户是root.
使用root用户查询mysql.user表，查看初始账户以及它们的口令。


7.1 为mysql用户设置密码
方法一：SET PASSWORD FOR ‘username'@'hostname' = PASSWORD('password');
方法二：直接操作mysql.user权限表。UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';FLUSH PRIVILEGES;(root用户所有的密码都改了)


7.2 查看mysql用户对数据库的访问权限
SHOW GRANT for 'username'@'hostname';


//授权jeecn用户拥有jeecn数据库的所有权限
grant all privileges on jeecnDB.* to jeecn@localhost identified by ‘jeecn’;
flush privileges;


//如果想指定部分权限给一用户，可以这样来写:
mysql>grant select,update on jeecnDB.* to jeecn@localhost identified by ‘jeecn’;
flush privileges;


撤消账户的权限：
REVOKE ALL ON *.* FROM ''@'localhost';


删除用户:
DROP USER 'username'@'hostname';