-- start mysql server
mysqld_safe --user=root

-- shutdown the server
mysqladmin -u root shutdown

-- connect to mysql server
mysql -h localhost -uroot -p;

create database example;

show databases;

-- show current database
select database();

drop database example;

-- show all engines
show engines \G

-- 

mysql> ? contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql> ? Data Types
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

mysql> ? int
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

mysql> ? show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [NONBLOCKING]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.7/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/show.html


mysql> ? show create database
Name: 'SHOW CREATE DATABASE'
Description:
Syntax:
SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

Shows the CREATE DATABASE statement that creates the named database. If
the SHOW statement includes an IF NOT EXISTS clause, the output too
includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE
DATABASE.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-create-database.html

Examples:
mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

mysql> SHOW CREATE SCHEMA test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */
                 

create table student (
    num int(10) primary key,
    name varchar(20) not null,
    sex varchar(4),
    birthday datetime,
    address varchar(50)
);

create table grade (
    id int(10) primary key auto_increment,
    course varchar(10) not null,
    s_num int(10) not null,
    grade varchar(4),
    constraint s_sum foreign key(s_num) references student(num)
);

alter table grade modify course varchar(20);

alter table grade modify s_num int(10) after id;

desc grade;

alter table grade change grade score varchar(4);

alter table grade drop foreign key s_sum;

show create table grade \G

alter table grade engine=myisam;

alter table student drop address;

alter table student add phone int(10);

alter table grade rename gradeInfo;

alter table gradeInfo add constraint s_num foreign key(s_num) references student(num);

create database job;

-- 创建表时创建索引

CREATE TABLE user (
    userid int(10) NOT NULL AUTO_INCREMENT,
    username varchar(20) NOT NULL,
    passwd varchar(20) NOT NULL,
    info text,
    PRIMARY KEY (userid),
    unique index index_uid(userid desc),
    index index_user(username, passwd),
    fulltext index index_info(info)
);

CREATE TABLE information (
    id int(10) NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    sex varchar(4) NOT NULL,
    birthday date DEFAULT NULL,
    address varchar(50) DEFAULT NULL,
    tel varchar(20) DEFAULT NULL,
    pic blob,
    PRIMARY KEY (id)
);

create index index_name on information(name(10));

create index index_bir on information(birthday, address);

-- 查看索引在查询中的使用情况
explain select * from information where birthday='' \G

alter table information add unique index index_id(id asc);

drop index index_user on user;

drop index index_name on information;

CREATE TABLE work_info (
  id int(10) NOT NULL,
  name varchar(20) NOT NULL,
  sex varchar(4) NOT NULL,
  age int(5) DEFAULT NULL,
  address varchar(50) DEFAULT NULL,
  tel varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
);

insert into work_info values
(1, 'zhang san', M, 18, 'beijing haidian'  , 1234567)
(2, 'li si'    , M, 22, 'beijing changping', 2345678)
(3, 'wang wu'  , F, 17, 'hunan yongzhou'   , 3456789)
(4, 'zhao liu' , F, 25, 'liaoning buxin'   , 4567890);

create algorithm=merge view info_view(id, name, sex, address)
as select id, name, sex, address from work_info where age > 20
with local check option;
-- ALGORITHM有三个值：MERGE、TEMPTABLE或UNDEFINCED，默认值是UNDEFINED
-- MERGE：将查询视图的语句与视图的定义语句合并处理
-- TEMPTABLE：视图查询的结果保存到临时表，而后在该临时表基础上执行查询视图的语句，对于视图的查询条件会在临时表上进行，包括传统的WHERE子句，以及像MERGE方式无法支持的DISTINCT、GROUP BY、HAVING等
-- UNDEFINED：由MySQL选择使用哪种算法，一般会首选MERGE，因为MERGE更有效率，而且TEMPTABLE不支持更新
-- with check option 是在通过view来更新表的时候进行约束，当更新表的结果能被视图查询出来则更新表的操作成功，否则失败
-- WITH LOCAL/cascade CHECK OPTION：当视图是根据另一个视图定义的时，LOCAL和CASCADED关键字决定了检查测试的范围。LOCAL关键字对CHECK OPTION进行了限制，使其仅作用在定义的视图上，CASCADED会对将进行评估的基表进行检查

select * from info_view;

create or replace algorithm=merge view info_view(id, name, sex, address)
as select id, name, sex, address from work_info where age < 20
with local check option;

update info_view set sex='M' where id=3;

drop view if exists info_view cascade;

show create view info_view \G;

CREATE TABLE product (
    id int(10) primary key,
    name varchar(20) not null,
    functiono varchar(50),
    company varchar(20) not null,
    address varchar(50)
);

create table operate (
    op_id int(10) primary key auto_increment,
    op_type varchar(20) not null,
    op_time time not null
);

CREATE TRIGGER product_bf_insert 
BEFORE INSERT ON product FOR EACH ROW
insert into operate values(null, 'before insert', current_time());

create trigger product_af_update
after update on product for each row
insert into operate values(null, 'after update', current_time());

create trigger product_af_del
after delete on product for each row
insert into operate values(null, 'after delete', current_time());

delimiter @

create trigger product_bf_del
before delete on product for each row
begin
insert into operate values(null, 'before delete', current_time());
end@

delimiter ;

insert into product
values(1,'p1','f1','c1','a1');

update product set name='p2';

drop trigger product_bf_insert;

drop trigger product_af_update;

drop trigger product_af_del;

show triggers \G

select * from information_schema.triggers \G

create table student (
    id int(10) primary key,
    name varchar(20) not null,
    sex varchar(4),
    birth year,
    department varchar(20) not null,
    address varchar(50)
);

create table score (
    id int(10) primary key auto_increment,
    stu_id int(10) not null,
    c_name varchar(10),
    grade int(10)
);

insert into student values(901, 'zhanglaoda', 'male', 1985, 'computer science', 'beijing haidian');
insert into student values(902, 'zhanglaoer', 'male', 1986, 'chinese', 'beijing haidian');
insert into student values(903, 'zhangsan', 'f', 1990, 'chinese', 'hunan yongzhou');
insert into student values(904, 'lisi', 'male', 1990, 'english', 'liaoning buxin');
insert into student values(905, 'wangwu', 'f', 1991, 'english', 'fujian xiamen');
insert into student values(906, 'wangliu', 'male', 1988, 'computer science', 'hunan hengyang');

insert into score values(1, 901, 'computer', 98);
insert into score values(2, 901, 'english', 80);
insert into score values(3, 902, 'computer', 65);
insert into score values(4, 902, 'chinese', 88);
insert into score values(5, 903, 'chinese', 95);
insert into score values(6, 904, 'computer', 70);
insert into score values(7, 904, 'english', 92);
insert into score values(8, 905, 'english', 94);
insert into score values(9, 906, 'computer', 90);
insert into score values(10, 906, 'english', 85);

select * from student;

-- limit offset, rows 第一个记录的偏移(0开始)和行数
select * from student limit 1, 3;

select id, name, department from student;

select * from student where department in ('computer science', 'english');

select * from student where year(now()) - birth between 18 and 22;

select department, count(department) from student group by department;

-- with rollup会增加一个count(department)的统计
select department, count(department) from student group by department with rollup;

-- having会对聚合后的数据再筛选，而where是对聚合前的数据筛选
select department, count(department) from student group by department having count(department)>1;

select c_name, max(grade) from score group by c_name;

select c_name, grade from score left join student as stu on stu_id = stu.id where name = 'lisi';

select * from student left join score on student.id = score.stu_id;

select stu_id, sum(grade) from score group by stu_id;

select c_name, avg(grade) from score group by c_name;

select * from student where id in (select stu_id from score where c_name = 'computer' and grade < 95);

select * from student where id in (select stu_id from score where c_name = 'computer' and stu_id in (select stu_id from score where c_name = 'english'));

select grade from score where c_name='computer' order by grade desc;

-- union会去重，union all不去重
select id from student union select stu_id from score;

select distinct id from student;

-- like语法的通配符包括：%(匹配任意字符串), _(匹配任意字符), [], [^]
select name, department, c_name, grade from student as stu left join score as s on stu.id=s.stu_id where name regexp '^(zhang|wang)';

select name, year(now()) - birth as age, department, c_name, grade from student as stu left join score as s on stu.id=s.stu_id where address regexp '^hunan';

create table food (
    id int(10) primary key auto_increment,
    name varchar(20) not null,
    company varchar(30) not null,
    price float,
    produce_time year,
    validity_time int(4),
    address varchar(50)
);

insert into food
values(null, 'AA cookie', 'AA factory', 2.5, 2008, 3, 'beijing'),
(null, 'CC Milk', 'CC factory', 3.5, 2009, 1, 'hebei'),
(null, 'EE jelly', 'EE factory', 1.5, 2007, 2, 'beijing'),
(null, 'FF coffee', 'FF factory', 20, 2002, 5, 'tianjin'),
(null, 'GG sugar', 'GG factory', 14, 2003, 3, 'guangdong');

insert into food(name, company, price, produce_time, validity_time, address)
	select name, company, price, produce_time, validity_time, address from food;

update food
set address='neimenggu', price=3.2
where company='CC factory';

delete from food where year(now()) - produce_time > validity_time;

-- 同时更新多个表
update emp a, dept b set a.sal=a.sal*b.deptno, b.deptname=a.ename where a.deptno=b.deptno

delete from food where address='beijing';

-- 同时删除多个表
delete a, b from emp a, dept b where a.deptno=b.deptno and a.deptno=3

-- 1-100随机整数

mysql> select round(rand()*100), floor(rand()*100), ceil(rand()*100);
+-------------------+-------------------+------------------+
| round(rand()*100) | floor(rand()*100) | ceil(rand()*100) |
+-------------------+-------------------+------------------+
|                66 |                39 |               98 |
+-------------------+-------------------+------------------+
1 row in set (0.00 sec)


-- 圆周率及其正余弦，对数

mysql> select pi(), sin(pi()), cos(pi()), log(pi());
+----------+------------------------+-----------+--------------------+
| pi()     | sin(pi())              | cos(pi()) | log(pi())          |
+----------+------------------------+-----------+--------------------+
| 3.141593 | 1.2246467991473532e-16 |        -1 | 1.1447298858494002 |
+----------+------------------------+-----------+--------------------+
1 row in set (0.00 sec)

mysql> select month(now())
    -> ;
+--------------+
| month(now()) |
+--------------+
|            5 |
+--------------+

mysql> select date_format(now(), '%M, %D, %Y');
+----------------------------------+
| date_format(now(), '%M, %D, %Y') |
+----------------------------------+
| May, 25th, 2017                  |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select now() current, date_add(now(), INTERVAL 31 day) after31days, date_add(now(), INTERVAL '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2017-05-25 13:59:19 | 2017-06-25 13:59:19 | 2018-07-25 13:59:19    |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

mysql> select now() current, date_add(now(), INTERVAL -31 day) before31days, date_add(now(), INTERVAL '-1_-2' year_month) before_oneyear_twomonth;
+---------------------+---------------------+-------------------------+
| current             | before31days        | before_oneyear_twomonth |
+---------------------+---------------------+-------------------------+
| 2017-05-25 14:00:29 | 2017-04-24 14:00:29 | 2016-03-25 14:00:29     |
+---------------------+---------------------+-------------------------+
1 row in set (0.00 sec)

create table food (
    id int(10) primary key auto_increment,
    name varchar(20) not null,
    company varchar(30) not null,
    price float,
    produce_time year,
    validity_time int(4),
    address varchar(50)
);

insert into food
values(null, 'AA cookie', 'AA factory', 2.5, 2008, 3, 'beijing'),
(null, 'CC Milk', 'CC factory', 3.5, 2009, 1, 'hebei'),
(null, 'EE jelly', 'EE factory', 1.5, 2007, 2, 'beijing'),
(null, 'FF coffee', 'FF factory', 20, 2002, 5, 'tianjin'),
(null, 'GG sugar', 'GG factory', 14, 2003, 3, 'guangdong');

delimiter @@

-- be careful the ';' at each statement
-- 局部变量，光标，条件，处理器都是通过Declare声明的，它们必须出现在存储过程的头部，它们之间有先后顺序要求，变量和条件必须出现在前面，然后是光标，最后是处理器
create procedure food_price_count(in price_info1 float, in price_info2 float, out count int)
	reads sql data
	begin
		# local variable must be declared first in block
		declare temp float;

		# cursor must be declared after local variable
		declare match_price cursor for select price from food;
		
		# handler must be declared after cursor
		declare exit handler for not found close match_price;
		
		# declare and set a user variable, user variable begin with @
		set @sum=0;

		# select into also can set variable
		select count(*) into count from food where price>price_info1 and price<price_info2;

		open match_price;

		while count>0 do
			fetch match_price into temp;

			if temp>price_info1 and temp<price_info2 then set @sum=@sum+temp;
			end if;
			
			set count = count - 1;
			
		end while;

		close match_price;
		
		select count(*) into count from food where price>price_info1 and price<price_info2;
		
	end;
@@

delimiter ;

show create procedure food_price_count \G

show procedure status like 'food_price_count' \G

delimiter @

create function food_price_count(price_info1 float, price_info2 float)
	returns int
	reads sql data
	begin
		return (select count(*) from food where price>price_info1 and price<price_info2);
	end;
@

delimiter ;

create table test(
	id1 varchar(10),
	create_time datetime
);

-- insert record every 5s
create event test_event_1
on schedule
every 5 second
do
	insert into test.test(id1, create_time) values('test', now());
	
show events \G

-- check whether enable event scheduler
show variables like '%scheduler%';

-- enable event scheduler
set global event_scheduler = 1;

-- show demaond process
show processlist \G;

-- clear test every 1 minute
create event trunc_test
on schedule
every 1 minute
do
	truncate table test;

-- disable event
alter event test_event_1 disable;

drop event test_event_1; 

mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(d datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values('2007-04-31');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values('2007-04-31');
ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd' at row 1

select * from test.student into outfile '/tmp/data' fields terminated by '\、' optionally enclosed by '\"' lines starting by '\>' terminated by '\r\n';

load data infile '/tmp/file' into table student fields terminated by '\、' optionally enclosed by '\"' lines starting by '\>' terminated by '\r\n'

[mysqld]
log-bin=/tmp/b_log

[mysqld]
log-error=/tmp/err_log

[mysqld]
log=/tmp/search_log

[mysqld]
log-slow-queries=/tmp/slow_queries_log
long_query_time=1

use information_schema;

-- gennerate commands of droping database test1's tables whose names contain 'tmp' prefix
select concat('drop table test1.', table_name, ';') from tables where table_schema='test1' and table_name like 'tmp%';

-- gennerate commands of changing all MyISAM table to InnoDB in the test1 database
select concat('alter table test1.', table_name, 'engine=innodb;') from tables where table_schema='test1' and engine='MyISAM';