MySQL总结之SQL语句的应用
创建一个表：
mysql> CREATE TABLE students (
-> sno CHAR(10) PRIMARY KEY,
-> sname CHAR(8) NOT NULL,
-> ssex CHAR(1) NOT NULL CHECK(ssex = 'F' OR ssex = 'M'),
-> sage INT NOT NULL,
-> sdept CHAR(20) DEFAULT 'Computer'
-> )
-> ;
MySQl所支持的字符类型：
修改表结构：
mysql> ALTER TABLE students CHANGE sage sage INT UNSIGNED;
mysql> ALTER TABLE students ADD Grade1 INT NOT NULL DEFAULT '1';
删除一个字段：
mysql> ALTER TABLE students DROP Grade1 ;
表重命名：
mysql> ALTER TABLE students RENAME students2;
数据库存储引擎类型：
存储引擎：相当于一个插件，MySQL跟存储在磁盘上的文件进行交互的接口。
*************************** 1. row ***************************
Engine: InnoDB（行级别锁 粒度比较小）
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: MyISAM （默认引擎，不支持事务，表级别锁）
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
8 rows in set (0.00 sec)
改变表的类型（表所使用的存储引擎的类型）：
mysql> ALTER TABLE students2 ENGINE=innodb;
查看表的存储引擎：
mysql> use information_schema
Database changed
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE FROM tables WHERE TABLE_NAME='students2';
truncate table 清空表 重置各种计数器。
备份：
mysqldump -uroot -p --database db_name > /root/dbname.sql
MySQL架构：
InnoDB： 支持事务，有行级别锁，存储粒度小
MyISM： 不支持事务，表级别锁
Archive： 归档存储引擎，存储大量记录，能够对数据进行压缩另存。只支持数据挖
掘，不支持索引；
Federated： 联合，可以将多个表连接成一个，方便，实现跨数据库/服务器连接表；
Merge： 将同一个数据库上的表连接起来，实现表分区。连接的表数据形式要一样，
可以实现将巨大的表切开，然后将切开的表做连接，不影响使用并且也加速了操作；
Memory： 内存数据库，数据的操作都在内存中实现
Blackhole： 在中继时的复制时，只记录但是不存储数据
CSV： 使用纯文本文件存储数据，实现不兼容跨数据库转储
NOB（Cluster） 运行在内存中，快速在集群节点之间实现数据交互
查看MySQl系统变量值：
mysql> select @@max_heap_table_size;
SQL语句的熟练应用：
mysql> create database mydb;
mysql> use mydb
mysql> create table stu (
-> name char(6) not null,
-> age tinyint unsigned not null,
-> gender char(1),
-> primary key (name,gender)
-> );
mysql> alter table stu modify gender char(1) not null default 'm';
mysql> alter table stu add course tinyint unsigned default '2';
mysql> CREATE TABLE course(
-> id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> course VARCHAR(255) NOT NULL DEFAULT 'Chinese',
-> startdate DATE NOT NULL DEFAULT '2011-01-01'
-> );
定义外键，先改存储引擎：
mysql> ALTER TABLE stu ENGINE=INNODB;
mysql> ALTER TABLE course ENGINE=INNODB;
mysql> ALTER TABLE stu ADD FOREIGN KEY(course) references course(id);
PS：如果创建一个已经存在的表会报错。使用 "if not exists" 关键字，在创建表的时候如果已经存在则不创建。
mysql> show warnings
显示警告信息的内容
checksum 1 通过已经存在的校验码来检查表中的数据是否有错误。
comment 添加注释信息
mysql>create table if not exists stu2 (......) comment 'Students'info.' ;
delay_key_write 跟索引相关联，延迟索引更新，可以提高数据库性能。
根据已经存在的表来创建一个新表：
通过选取一些字段来创建一个新表
mysql> create table course2 select * from course where id <100;
创建一个跟原来表结构一摸一样的表：
mysql> create table course3 like course;
向表中添加数据：
mysql> insert into course(course,startdate) values ('Maths','2011-01-01');
批量插入：
mysql> insert into course(course) values ('English'),('Computer'),('Music');
清空表：
mysql> truncate table course；
添加唯一键约束：
mysql> alter table course add unique key(course);
通过select来添加数据：
mysql> insert into course3 select * from course;
使用set为某个字段设值：
mysql> insert into course set course='PE';
修改表中数据：
mysql> update course set id=id+1 where id=5;
删除一行数据：
mysql> delete from course where id=6;
查询：
使用别名：
mysql> select course as COURSE from course;
MySQL操作符：
mysql> select * from course where id between 1 and 3;
mysql> select * from course where id in (1,2,3);
mysql> select * from course where course like 'M%';
mysql> select * from course where course like 'M_sic';
去除重复行：
mysql> select distinct startdate from course;
排序，默认升序：
mysql> select * from course order by id;
mysql> select * from course order by id desc;
分组：
mysql> select * from course group by startdate;
对分组后的结果再进行过滤：
mysql> select startdate from course group by startdate having count(*)>1;
对结果集做条目限制：
mysql> select * from course order by id limit 2;
mysql> select * from course order by id limit 2,2; //**后面的“2”表示偏移量
多表查询：
mysql> (select * from course3 order by id limit 2) union (select * from course3 order by id desc limit 2);
两个表：
内连接：即等值连接，根据两个表的对应列值相等的原则进行连接。连接条件的形式：“主键=外键”，即一个表的主键值与另一个表的外键值相等的原则进行连接。
mysql> select * from stu,course where stu.course=course.id;
外连接：分为左外连接和右外连接。外连接不仅包含满足条件的行，还包括其中某个表中不满足连接条件的行。
mysql> select * from stu left join course on stu.course=course.id;
mysql> select * from stu right join course on stu.course=course.id;
mysql> select * from stu right join course on stu.course=course.id where stu.gender='m' or course.id=4;
mysql> select s1.name as HOME ,s2.name as CUSTOMER from stu as s1 inner join stu as s2 where s1.name <> s2.name;
子查询：
非相关子查询：外查询的查询结果依赖于子查询的结果。
mysql> select * from stu where course =(select id from course where course='Maths');
mysql> select s1.name from (select * from stu where gender='m') as s1 where s1.course=2;
mysql> select course from course where id in (select course from stu where gender='f');
相关子查询：子查询的执行依赖于外查询。执行从外查询开始，只有外查询把值传给内查询后，内查询才能执行。
mysql> select name from stu where exists (select * from stu where age>20);
//** exists 条件为真，就将外查询的结果输出出来。
PS：EXISTS 的子查询只测试子查询的结果集是否为空，因此在子查询中指定列名是没有意义的，所以在有EXISTS的子查询中，其列名序列通常都用“*”表示。
EXISTS后的子查询中必须加入外查询所使用的表与内查询所用的表之间的连接条件。
mysql> select * from stu where exists (select * from course where course.id=stu.course and course.course='Maths');
视图：虚表
mysql> create view students as select * from stu;
视图可以更新。
mysql> create view test as select name,course from stu where course is not null with check option;
索引：
PS：MySQL将存储数据的文件划分为页面。
为stu创建索引：
mysql> create index stu_age_index on stu(age) using btree;
mysql> show create table stu;
事务（transaction）：一组操作的组合。
（ACID标准）四种约束： 原子性 作为一个整体，不可分割
一致性 事务必须完成全部操作，结果必须保持一致
隔离性 事务之间不能互相干扰
持久性 可以永久保持
事务隔离级别： 读未提交 Read uncommitted |
读提交 Read committed |
重读 Repeatable read |
串行化 Serialable 级别依次增高
PS：InnoDB支持事务，MyISAM不支持事务。
mysql> start transaction; //** 事务开始
……
mysql> savepoint S1;
……
mysql> savepoint S2;
mysql> rollback to savepoint S1; //** 回滚
mysql> commit; //** 提交
锁：防止事务之间的有毒性交互的机制，当用户对数据库进行并发访问时，为了确保书屋完整性和数据库一致性，需要使用锁，它是实现数据库并发控制的主要手段。
mysql>lock table stu read;
........
mysql>unlock table stu read;
查看全局变量：
mysql> show global variables like '%auto%';
修改全局变量：
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
练习：查询年龄大于平均年龄的学生的名字：
mysql> select name from stu where age > (select avg(age) from stu);
存储过程（procedure）：在数据库中定义子程序，这种程序块称之为存储过程。不同用户和应用程序之间共享，并可实现程序的优化和重用。使用 CALL procedure_name(message)来调用，返回结果集或者标量。
mysql> \d //
mysql> create procedure select_stu()
-> begin
-> select * from stu;
-> end //
Query OK, 0 rows affected (0.08 sec)
mysql> \d ;
mysql> call select_stu();
mysql> drop procedure select_stu;
Query OK, 0 rows affected (0.04 sec)
在过程中使用IF嵌套：
mysql> \d //
mysql> create procedure what_is_today()
-> begin
-> if dayofweek(now()) between 2 and 6 then
-> select 'Today is a weekday.' as what_is_today;
-> else select 'Today is weekend!';
-> end if;
-> end //
Query OK, 0 rows affected (0.01 sec)
mysql> \d ;
mysql> call what_is_today();
存储函数：存储在数据库中的代码块，可以把值返回到调用程序。定义过程与存储过程相似，返回值只能是标量，不能返回结果集。使用SELECT function_name() 来调用。
PS :存储例程：包括存储过程和存储函数。
触发器(trigger)是一些过程，与表关系密切，用户保护表中的数据。当一个基表被修改（INSERT，UPDATE或者DELETE）时，触发器自动执行，通过触发器可实现多个表间数据的一致性和完整性。
MySQL 支持六种触发器：INSERT，UPDATE，DELETE执行前后。
mysql> create trigger flight_ai
-> after insert on stu
-> for each row
-> insert into log (byuser,note,eventtime)
-> values (current_user(),'Record added:stu',now());
Query OK, 0 rows affected (0.04 sec)
调度事件：定时执行一些语句的功能模块，在时间满足的时候自动执行。
练习：为stu新增一个字段，允许为空。为每一个人定义一个alive状态 ENUM 枚举
mysql> alter table stu add alive enum('Y','N') not null default 'Y';
mysql> update stu set alive='N' where name='jim' or name='lucy';
使用存储过程显示stu表里边所有alive为N的用户的名字
mysql> \d //
mysql> create procedure select_alive()
-> begin
-> select * from stu where alive='N';
-> end //
mysql> \d ;
mysql> call select_alive();
