Table of Contents
Mysql Document
https://dev.mysql.com/doc/refman/5.7/en/
https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-definition.html
如何打开MySQL中root账户的远程登录
安装完成mysql数据，也不想创建其他用户了，直接用root吧，可是其他机器无法远程访问
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";
flush privileges;
第一句中"%"表示任何主机都可以远程登录到该服务器上访问。如果要限制只有某台机器可以访问，将其换成相应的IP即可，如： GRANT ALL PRIVILEGES ON . TO root@"172.168.193.25" IDENTIFIED BY "root"; 第二句表示从mysql数据库的grant表中重新加载权限数据。因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -
可以看到绑定的地址为127.0.0.1。正常应该是0.0.0.0 修改/etc/mysql/my.cnf，需要root用户权限。找到文件中的：
bind-address = 127.0.0.1
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN -
tcp 0 0 192.168.1.105:3306 192.168.1.100:51240 ESTABLISHED -
mysql> select version(),current_date;
select version(),current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 5.7.13 | 2017-02-26 |
+-----------+--------------+
1 row in set (0.02 sec)
mysql创建数据库指定字符集
GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql -u root -p
SOURCE C:/temp/sakila-db/sakila-schema.sql;
SOURCE C:/temp/sakila-db/sakila-data.sql;
CREATE DATABASE menagerie;
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
GRANT ALL ON sakila.* TO 'hvitssm'@'%';
安装
https://dev.mysql.com/downloads/repo/apt/
https://repo.mysql.com//mysql-apt-config_0.8.7-1_all.deb https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/#apt-repo-fresh-install https://dev.mysql.com/doc/refman/5.7/en/server-administration.html
sudo apt-get update
sudo apt-get install mysql-server
sudo service mysql status
sudo service mysql stop
sudo service mysql start
连接
mysql -h host -u user -p
查询
SELECT VERSION(), CURRENT_DATE;
select version(), current_date;
SeLeCt vErSiOn(), current_DATE;
SELECT SIN(PI()/4), (4+1)*5;
SELECT VERSION(); SELECT NOW();
SELECT USER()
SHOW DATABASES;
USE test
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
创建数据库
CREATE DATABASE menagerie;
USE menagerie
mysql -h host -u user -p menagerie
创建表
SHOW TABLES;
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
SHOW TABLES;
DESCRIBE pet;
加载数据
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
使用批处理模式
mysql < batch-file
mysql -e "source batch-file"
mysql -h host -u user -p < batch-file
mysql < batch-file | more
mysql < batch-file > mysql.out
source filename;
\. filename
安装之后
#以root身份在终端登陆，必须
mysqladmin -u root -p password root
如果是mariadb,创建其他用户，并使用其他用户登陆
查询所有表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE';
查询所有视图
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='VIEW';
查看所有函数
SELECT name from mysql.proc where type='function';
查询所有存储过程
SELECT name from mysql.proc where type='procedure'
查询所有触发器
SELECT * FROM information_schema.`TRIGGERS`;
select trigger_name from information_schema.`TRIGGERS`;
创建触发器
语法
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
-> FOR EACH ROW PRECEDES ins_sum
-> SET
-> @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
-> @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;//
mysql> delimiter ;
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
delimiter ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);
SELECT * FROM test1;
SELECT * FROM test2
SELECT * FROM test3;
SELECT * FROM test4;
pointinfo 表结构
desc pointinfo;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| POINTID | text | YES | | NULL | |
| EARTHID | text | YES | | NULL | |
| POINTLOCATION | text | YES | | NULL | |
| POINTLON | double | YES | | NULL | |
| POINTLAT | double | YES | | NULL | |
| POINTNAME | varchar(50) | YES | | NULL | |
| POINTTIME | datetime | YES | | NULL | |
| POINTGROUP | varchar(50) | YES | | NULL | |
| POINTPERSON | varchar(50) | YES | | NULL | |
| POINTINTENSITY | varchar(5) | YES | | NULL | |
| POINTCONTENT | text | YES | | NULL | |
| MSGTEL | varchar(11) | YES | | NULL | |
| MSGCONTENT | text | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
13 rows in set (0.01 sec)
elt日志表
CREATE TABLE etllog(etllog_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
table_name text,
table_id text,
table_action int);
对于pointinfo表，创建触发器
mysql -uroot -h192.168.3.180 -Ddb20170303 -p
delimiter //
CREATE TRIGGER tbi_pointinfo BEFORE INSERT ON pointinfo
FOR EACH ROW
BEGIN
insert into etllog(table_name,table_id,table_action)
values('pointinfo',new.pointid,1);
END;//
delimiter ;;
insert into pointinfo(pointid,pointlocation) values('TTest001','这是一个测试地址');
delimiter //
CREATE TRIGGER tbu_pointinfo BEFORE update ON pointinfo
FOR EACH ROW
BEGIN
insert into etllog(table_name,table_id,table_action)
values('pointinfo',old.pointid,2);
END;//
delimiter ;
insert into pointinfo(pointid,pointlocation) values('TTest002','这是一个测试地址');
update pointinfo set pointlocation='这是另一个测试' where pointid='TTest002';
delimiter //
CREATE TRIGGER tbd_pointinfo BEFORE delete ON pointinfo
FOR EACH ROW
BEGIN
insert into etllog(table_name,table_id,table_action)
values('pointinfo',old.pointid,-1);
END;//
delimiter ;;
insert into pointinfo(pointid,pointlocation) values('DTTest003','这是一个测试地址');
delete from pointinfo where pointid='DTTest003';
delete from pointinfo where pointid like '%Test%';
delete from etllog;
insert into pointinfo(pointid,pointlocation) values('TTest001','这是一个测试地址');
insert into pointinfo(pointid,pointlocation) values('TTest002','这是一个测试地址');
update pointinfo set pointlocation='这是另一个测试' where pointid='TTest002';
insert into pointinfo(pointid,pointlocation) values('DTTest003','这是一个测试地址');
delete from pointinfo where pointid='DTTest003';
select * from etllog;
删除触发器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
储存过程for
Table: film
Create Table: CREATE TABLE `film` (
`film_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` text,
`release_year` year(4) DEFAULT NULL,
`language_id` tinyint(3) unsigned NOT NULL,
`original_language_id` tinyint(3) unsigned DEFAULT NULL,
`rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
`rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`zd01` text,
`zd02` text,
`zd03` text,
`zd04` text,
`zd05` text,
`zd06` text,
`zd08` text,
`zd09` text,
`zd07` text,
`zd10` text,
`zd11` text,
`zd12` text,
`zd13` text,
`zd14` text,
`zd15` text,
`zd16` text,
`zd17` text,
`zd18` text,
`zd19` text,
`zd20` text,
PRIMARY KEY (`film_id`)
) ENGINE=InnoDB AUTO_INCREMENT=75536 DEFAULT CHARSET=utf8;
drop procedure if exists p_for_loop;
DELIMITER &&
create procedure p_for_loop()
begin
declare i int;
set i = 1;
loop_example : loop
select concat('index -> ', i);
insert into film(title,description,zd01,zd02,zd03,zd04,zd05,zd06,zd07,zd08,zd09,zd10,zd11,zd12,zd13,zd14,zd15,zd16,zd17,zd18,zd19,zd20)
values('测试速度'||i,concat('测试速度',i),'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i,'测试速度'||i);
set i = i + 1;
if i > 10000 then
leave loop_example;
end if;
end loop;
end &&
DELIMITER ;
call p_for_loop();
mysqldump: Error: Binlogging on server not active
log_bin=mysql-bin
server-id = 1