MySql 5.1 数据库脚本语言

说明：<1> 能够以大小写输入关键词。
       <2> 能将mysql用作一个简单的计算器。例如：SELECT SIN(PI()/4), (4+1)*5;
       <3> mysql接受自由格式的输入：它收集输入行但直到看见分号才执行。
(1) 用户操作
     <1> 创建用户：
CREATE USER 'vivian'@'localhost' IDENTIFIED BY '123456';

     <2> 删除用户：
         DROP USER 'vivian'@'localhost';

     <3> 重命名用户：
         RENAME USER 'vivian'@'localhost' to 'liuhuifang'@'localhost';
    
     <4> 密码设置
         SET PASSWORD FOR 'vivian'@'localhost' = PASSWORD('111111');

(2) 权限操作
     <1> 授予权限：
         GRANT ALL ON *.* TO 'vivian'@'localhost';(Global privileges)
         GRANT ALL ON notebook.* TO 'vivian'@'localhost' WITH GRANT OPTION;(Database privileges,used often)
         GRANT SELECT, INSERT ON notebook.t_city TO 'vivian'@'localhost';(Table privileges)
         GRANT SELECT (city_name), INSERT (city_id,country_id) ON notebook.t_city TO 'vivian'@'localhost';(Column privileges)
         GRANT CREATE ROUTINE ON notebook.* TO 'vivian'@'localhost';(Routine privileges)

     <2> 撤销权限：
         REVOKE ALL ON *.* FROM 'vivian'@'localhost';(Global privileges)
REVOKE ALL ON notebook.*, GRANT OPTION FROM 'vivian'@'localhost' ;(Database privileges,used often)
         REVOKE SELECT, INSERT ON notebook.t_city FROM 'vivian'@'localhost';(Table privileges)
         REVOKE SELECT (city_name), INSERT (city_id,country_id) ON notebook.t_city FROM 'vivian'@'localhost';(Column privileges)
         REVOKE CREATE ROUTINE ON notebook.* FROM 'vivian'@'localhost';(Routine privileges)

(3) 表空间操作
     <1> 创建表空间： 
CREATE TABLESPACE notebookspace
     ADD DATAFILE 'notebook_file'
                             USE LOGFILE GROUP notebook_group
                             ENGINE 'InnoDB';
     <2> 删除表空间：
         DROP TABLESPACE notebookspace
                             ENGINE 'InnoDB';

     <3> 修改表空间：
         ALTER TABLESPACE notebookspace
     DROP DATAFILE 'notebook_file'
     ENGINE 'InnoDB';

(4) 数据库操作
     说明：
          查询 CHARACTER SET：SHOW CHARACTER SET ;
          查询 COLLATION：SHOW COLLATION ;

     <1> 创建数据库：
         CREATE DATABASE if not exists notebook;

     <2> 删除数据库：
         DROP DATABASE if exists notebook;

     <3> 修改数据库：
         ALTER DATABASE notebook CHARACTER SET = utf8
                                  COLLATE = utf8_general_ci;

(5) 表操作
     说明：
         查询当前数据库中表的信息：show tables;
         查询当前表结构：describe t_city;

     <1> 创建表
         CREATE TABLE t_city                                    --城市表                                 
(
  city_id int NOT NULL,
  country_id int NOT NULL,
  city_name varchar(50),
  city_temp_lo int,                             --最低气温    
  city_temp_hi int,                             --最高气温   
  city_prcp double(10,5),                       --降水量
  city_date date,
  CONSTRAINT t_city_pkey PRIMARY KEY (city_id)
)
         and
CREATE TABLE t_country                                 --国家表
       (
  country_id int NOT NULL,
  country_name varchar(50),
  CONSTRAINT t_country_pkey PRIMARY KEY (country_id)
       )
      
     <2> 删除表
         DROP TABLE t_city ;
DROP TABLE t_country ;

     <3> 修改表
         [1] 增加/删除 列
             增加列：ALTER TABLE t_city add city_date date;
             删除列：ALTER TABLE t_city drop city_date;

         [2] 添加/删除 外键约束
             增加外键约束：
             ALTER TABLE t_city add constraint t_country_fkey foreign key (country_id) references t_country(country_id);
             删除外键约束：
             ALTER TABLE t_city drop foreign key t_country_fkey;

     <4> 增，删，改功能
         增：
         insert into t_country values (1,'China');
insert into t_country values (2,'USA');
         insert into t_country values (3,'UK');
insert into t_city values(1,1,'BeiJing',12,24,5.1,'2009-12-01');
         insert into t_city values(2,2,'NewYork',13,24,11.1,'2009-12-01');
         insert into t_city values(3,3,'London',13,22,2.1,'2009-12-01');
         删：
delete from t_city where city_id = 2;
         delete from t_country where country_id = 2;
         改：
         update t_city set city_prcp = city_prcp+1 where city_id=1;

     <5> 查询
         [1] 一般查询：
     select * from t_city;
     select city_name from  t_city,t_country where t_city.country_id = t_country.country_id;
     SELECT city_name, (city_temp_lo+city_temp_hi)/2 AS city_temp_avg, city_date FROM t_city order by city_name;

[2] 交叉连接：
     SELECT * FROM t_city city CROSS JOIN t_country country;
     SELECT * FROM t_city city CROSS JOIN t_country country where city.country_id = country.country_id;

[3] 条件连接:
     内连接：
     SELECT * FROM t_country t1 INNER JOIN t_city t2 ON t1.country_id = t2.country_id;
     SELECT * FROM t_country t1 INNER JOIN t_city t2 using (country_id);

     左外连接：
             SELECT * FROM t_country t1 LEFT JOIN t_city t2 ON t1.country_id = t2.country_id;
     SELECT * FROM t_country t1 LEFT JOIN t_city t2 using (country_id);
     SELECT * FROM t_country t1 NATURAL LEFT JOIN t_city t2;

     右外连接：
             SELECT * FROM t_city t2 RIGHT JOIN t_country t1 ON t1.country_id = t2.country_id;
             SELECT * FROM t_city t2 RIGHT JOIN t_country t1 using (country_id);
     SELECT * FROM t_city t2 NATURAL RIGHT JOIN t_country t1;

[4] 嵌套查询：
     SELECT city_name,city_temp_lo FROM t_city WHERE city_temp_lo in (12,13,14);
     SELECT city_name FROM t_city WHERE country_id in (select country_id from t_country);
     SELECT city_name FROM t_city WHERE EXISTS (SELECT country_id FROM t_country WHERE country_id = t_city.country_id);
        
[5] GROUP BY 和 HAVING 子句:
     SELECT city_name,city_date FROM t_city GROUP BY city_name,city_date;
     SELECT city_name FROM t_city GROUP BY city_name;
     SELECT city_name,sum(city_prcp) FROM t_city GROUP BY city_name having city_name = 'BeiJing';

[6] 组合查询：
     select country_name from t_country where country_id =1
     union
     select country_name from t_country where country_id =5
     order by country_name limit 1;

[7] LIMIT 和 OFFSET:
     select city_id,city_name from t_city order by city_id limit 2 offset 1 ;

(6) 视图操作
     <1> 创建视图：
CREATE OR REPLACE VIEW v_cityName AS
   SELECT city_name
   FROM t_city;

     <2> 删除视图：
DROP VIEW if exists v_cityName;

     <3> 修改视图:
         ALTER VIEW v_cityName AS
    SELECT t1.city_name,t2.country_name
    FROM t_city t1,t_country t2
    where t1.country_id = t2.country_id;

     <3> 查询视图：
SELECT * FROM v_cityName;

(7) 函数和存储过程操作
     <1> 创建函数和存储过程：
         [1] CREATE PROCEDURE simpleproc (OUT param1 INT)
             BEGIN
             SELECT COUNT(*) INTO param1 FROM t;
     END
       
     CALL simpleproc(@a);
     SELECT @a;

[2] CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
             RETURN CONCAT('Hello, ',s,'!');

             SELECT hello('world');

     <2> 删除函数和存储过程：
         DROP PROCEDURE IF EXISTS simpleproc;
         DROP FUNCTION IF EXISTS hello;

     <3> 修改函数：
         ALTER FUNCTION hello CONTAINS SQL;
         ALTER FUNCTION hello SQL SECURITY DEFINER;

     <4> 修改存储过程：
         ALTER PROCEDURE simpleproc CONTAINS SQL;
         ALTER PROCEDURE simpleproc SQL SECURITY DEFINER;

(8) 触发器操作
     <1> 创建触发器：
         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
);

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;

INSERT INTO test1 VALUES
         (1), (3), (1), (7), (1), (8), (4), (4);

     <2> 删除触发器：
         DROP TRIGGER IF EXISTS testref;

(9) 索引操作
     <1> 创建索引：
         CREATE INDEX cityID_idx ON t_city (city_id);
         CREATE INDEX cityidname_idx ON t_city (city_id,city_name);

     <2> 删除索引：
         DROP INDEX cityID_idx ON t_city;
DROP INDEX cityidname_idx ON t_city;