MYSQL 使用SQL语句全览

gyts62

　　创建一个表：
　　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>>

　　mysql>>

　　删除一个字段：

　　mysql>>　　表重命名：

　　mysql>>　　数据库存储引擎类型：
　　存储引擎：相当于一个插件，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>　　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>>　　查看表的存储引擎：
　　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>>

　　mysql>>　　mysql> CREATE TABLE course(

　　->>　　-> course VARCHAR(255) NOT NULL DEFAULT 'Chinese',
　　-> startdate DATE NOT NULL DEFAULT '2011-01-01'
　　-> );

　　定义外键，先改存储引擎：

　　mysql>>
　　mysql>>
　　mysql>>　　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>

　　创建一个跟原来表结构一摸一样的表：
　　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>>

　　通过select来添加数据：
　　mysql> insert into course3 select * from course;

　　使用set为某个字段设值：
　　mysql> insert into course set course='PE';

　　修改表中数据：

　　mysql> update course set>

　　删除一行数据：

　　mysql> delete from course where>　　查询：
　　使用别名：
　　mysql> select course as COURSE from course;

　　MySQL操作符：

　　mysql> select * from course where>

　　mysql> select * from course where>　　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>

　　mysql> select * from course order by>

　　分组：
　　mysql> select * from course group by startdate;
　　对分组后的结果再进行过滤：
　　mysql> select startdate from course group by startdate having count(*)>1;

　　对结果集做条目限制：

　　mysql> select * from course order by>

　　mysql> select * from course order by>

　　多表查询：

　　mysql> (select * from course3 order by>

　　两个表：

　　内连接：即等值连接，根据两个表的对应列值相等的原则进行连接。连接条件的形式：“主键=外键”，即一个表的主键值与另一个表的外键值相等的原则进行连接。
　　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>

　　mysql> select s1.name from (select * from stu where gender='m') as s1 where s1.course=2;

　　mysql> select course from course where>

　　相关子查询：子查询的执行依赖于外查询。执行从外查询开始，只有外查询把值传给内查询后，内查询才能执行。
　　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>>

　　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();