Linux MySql

安装：
yum install mysql-server ; mysql-devel

show databases;


权限
具体操作步骤：
关闭mysql:
# service mysqld stop
然后:
# mysqld_safe --skip-grant-tables
启动mysql:
# service mysqld start
mysql -u root
mysql> use mysql
mysql> UPDATE user SET Password=PASSWORD('xxx') WHERE user='root';
mysql> flush privileges;
mysql>\q
mysql -u root -p
输入密码：

开机启动
cp /usr/local/mysql/share/mysql/mysqld.server /etc/rc.d/init.d/mysqld

cd /etc/rc.d/init.d

chkconfig --add mysqld

ntsysv检查Mysql是否已经加载到服务启动


修改root密码
/usr/bin/mysqladmin -u root password '123456'

创建用户
create user 'mogile'@'localhost' identified by '123456' ；
修改权限
grant usage on *.* to 'mogile'@localhost identified by '123456' with MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0
MAX_USER_CONNECTIONS 0;

GRANT ALL PRIVILEGES ON mogile.* TO 'mogile'@'localhost' WITH GRANT OPTION;

从文件中读取执行脚本
mysql> source database.sql;

表的克隆:
CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

触发器:

CREATE TRIGGER ai_auction AFTER INSERT ON auction
FOR EACH ROW
BEGIN
  INSERT INTO auction_log (action, id , ts, item, bid)
  VALUES ( 'create', NEW.id, NOW(), NEW.item, NEW.bid );
END;

CREATE TRIGGER au_auction AFTER UPDATE ON auction
FOR EACH ROW
BEGIN 
  INSERT INTO aution_log ( action, id, ts, item, bid)
  VALUES( 'update' , NEW.id , NOW(), NEW.item , NEW.bid);
END;

CREATE TRIGGER ad_aution AFTER DELETE ON auction
FOR EACH ROW
BEGIN
  INSERT INTO aution_log ( action, id, ts, item, bid)
  VALUES( 'delete', OLD.id , OLD.ts, OLD.item, OLD.bid);
END;

NEW新行，OLD表示旧行



统计：
1.计算描述统计

SELECT COUNT(score) AS n ,
SUM(score) AS sum ,
MIN(score) AS minimum ,
MAX(score) AS maximum ,
AVG(score) AS mean ,
STDDEV_SAMP(score) AS 'std. dev .' ,  --标准差
VAR_SAMP(score) AS 'variance'         --标准差的平方
FROM testscore;

2. 分组统计

SELECT COUNT(score) AS n ,
SUM(score) AS sum ,
MIN(score) AS minimum ,
MAX(score) AS maximum ,
AVG(score) AS mean ,
STDDEV_SAMP(score) AS 'std. dev .' ,  --标准差
VAR_SAMP(score) AS 'variance'         --标准差的平方
FROM testscore
GROUP BY age,sex;

3. 产生概率分布

--个数分布
SELECT score, COUNT(score) AS occurrence
FROM testscore GROUP BY score;

--百分数分布
SELECT @n := COUNT(score) FROM testscore;
SELECT score, ( COUNT( score ) * 100 ) /@n AS percent
FROM testscore GROUP BY score;

--状图分布
SELECT score, REPEAT( '*' ,COUNT( score ) ) AS occurrences
FROM testscore  GROUP BY score;

4. 计算缺失值。

SELECT COUNT(*) AS 'n (total)',
COUNT( score ) AS 'n (nonmissing)',
COUNT(*) - COUNT(score) AS 'n ( missing ) ',
((COUNT(*) - COUNT(score)) * 100) /COUNT( * ) AS '% missing'
FROM t;

----------- 毫无来由的分割线 ---------------
处理NULL

SELECT * FROM taxpayer WHERE id = NULL;

SELECT * FROM taxpayer WHERE id != NULL;

SELECT * FROM taxpayer WHERE id IS NULL;

SELECT *　FROM taxpayer WHERE id IS NOT NULL;

SELECT name, IF(id IS NULL, 'unkown', id ) AS 'id' FROM taxpayer;