mysql数据库操作

创建库操作
http://www.cnblogs.com/linhaifeng/articles/7211690.html
    ---------------------------------
    1、create database homework; 创建数据库）
    2、show create database homework; 查询创建过程
    3、drop database homework; 删除数据库
    4、alter database homework charset utf8; 设置数据库编码格式
    5、use homework;（切换数据库）
    6、desc homework(查看表结构)
    
    
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
对表的操作：http://www.cnblogs.com/linhaifeng/articles/7232894.html#_label1
    创建表语法:create table 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
    );
    #注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

mysql> 

1、 create table t1 查看建表过程
2、desc t1;查看表结构
3、show tables; 查看该数据库下有哪些表
4、show create table t1G; 查看详细的建表过程





=================================================================================================================================================================
    表结构操作(建表之后的操作)

        增加:alter table 表名 add 字段名 字段类型 NOT NULL/first/(after列名);
        删:1、drop table 表名 （删大数据用truncate+表名）
           2、alter table 表名 drop 字段名：删除字段名
        改:1、alter table 表名 modify（改字段类型） 列名 字段类型 /first/(after列名);
           2、alter table 表名 change（改字段名） 旧列名 新列名 字段类型 /first/(after列名);
        查:desc 查看表结构
        
http://www.cnblogs.com/linhaifeng/articles/7232894.html#_label2
示例：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
    a. 删除自增约束
    mysql> alter table student10 modify id int(11) not null; 

    b. 删除主键
    mysql> alter table student10                                 
        -> drop primary key;
        
八 复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;


只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  

完整性约束:
primary key（非空且唯一 ）:哪个字段设置为auto_increment 那么该键就必须设置为primary key(主键)

-- 方式1

CREATE TABLE t1(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

-- 方式2

CREATE TABLE t2(
  id INT NOT NULL,
  name VARCHAR(20)
);



注意：

1、一张表中最多只能有一个主键

2、表中如果没有设置主键，默认设置NOT NULL的字段为主键；此外，表中如果有多个NOT NULL的字段，则按顺序将第一个设置NOT NULL的字段设为主键。

结论：主键一定是非空且唯一，但非空且唯一的字段不一定是主键。

3、主键类型不一定必须是整型

-------------------------------------------------------------------------------------------------------------------------------
复合主键
所谓的复合主键 就是指你表的主键含有一个以上的字段。

如果一列不能唯一区分一个表里的记录时，可以考虑多个列组合起来达到区分表记录的唯一性，形式 

①创建时：
create table sc (
    studentid int,
    courseid int,
    score int,
primary key (studentno,courseid)
);        
②修改时：
alter table tb_name add primary key (字段1,字段2,字段3);

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
    unique（唯一）:create table t1 (name varchar(20) unique);
    实例：CREATE TABLE t5(
  id   INT AUTO_INCREMENT,
  name VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY UK_t5_name (name)
);
    建表之后添加unique（唯一性约束）
    alter table t5 add constraint UK_t5_name unique (name);
    删除
    ALTER TABLE t5 DROP INDEX UK_t5_name;
    
    
    
    
表与表之间的关系:
一对多
    将关联字段设置在多的那张表中
        1、书表 2、出版社表 一个出版社可以出版多本书，根据一对多外键设置在多的那张表里 即书表里有个出版社ID字段。
多对多
    创建第三张关系表:例如 1学生表、2老师表 老师有多个学生，一个学生有多个老师
    1、创建学生表
    2、创建老师表
    3、创建两者之间的关联关系的表。
一对一
1：关联字段为unique约束（唯一）
2：关联字段放在哪张表中均可，只是通常放在正向查询较多的哪张表中
    

查select(重点)  
                语法顺序(不可跌倒)：select 列名 form 表名 + where字句 + group by + having + order by + limit
                
                执行顺序：from--->  where----> group by --------->having --->select ----->discount(去重) order by---->limit
命令行：
    单表: select 列名 form 表名  
                                where 条件+
                                            1、where +关系运算符
                                            2、where +逻辑运算符
                                            3、where +子关键字
                                                    3.1、where+ between ...and...（范围） 
                                                    3.2、where + is null
                                                    3.3、where + in
                                                    3.4、where +not in：select name from emp where name like 'zhang%';
                                                    3.5、where +like + %/_ ：
                                                        3.5.1、where +like + %（以xx开头）
                                                        3.5.2、where +like + %%
                                                        3.5.3、where +like + _（匹配某个字符）
                                                        3.5.4、where +like + and 
                                                        3.5.5、where +like + or 
                                                        3.5.6、where +like + not  %/_
                                                        
                                
                                group by列名(出现:"每一个XX的统计"字样) select +count/max/min/avg(所要统计的字段名) +from 表名 group by +所要分组的条件字段
                                
                                分析:每一个部门的薪水平均值
                                     1.1、出现'每一个'    的字样说明用分组
                                     1.2、'薪水'说明是分组的列字段(selec后)
                                     1.3、说明用的是哪个聚合函数（count/max/min/avg）
                                     1.4、每个部门的平均薪水，查询三个东西：1、每个部门 2、平均、3、薪水
                                     1.5、如果想用第四个及以上的东西就要用having
                                    实例1：select * from emp group by dep(部门)
                                    实例2：select avg(salary) from emp group by dep ;统计每个部门的平均薪水
                                    
                                     2、group by作为输入:查询结果作为后一个查询语句的条件：
                                        2.1、查询每个部门最大年龄员工的姓名
                                        select max(age) from emp group by dep查询的结果给select name from emp where age in 
                                     实例1:select name from emp where age in ( select max(age) from emp group by dep);
                                     
                                    3、group by 与group_concat连用查询分组后 组内的的信息
                                     3.1、查询每个组的员工姓名：（查询每个组的哪些年龄、姓名等：定义是：分组显示每个组内的信息）
                                     实例1:select dep,group_concat(name) from emp group by dep;
                                    
                                having 筛选
                                 分组之后进行过滤
                                 
                                 Oder by 列名 +DESC/ASC
                                    1、DESC:该列名从大往小降序
                                    2、ASC:默认 从小往大的升序
                                    
                                LIMIT 限制条数
                                    1、select * from emp limit 3 :取三条从第一条开始取
                                    2、select * from emp limit 2,5：从第2条开始取，取5条

多表查询之全内左右连接：

1 笛卡尔积
select * from dep,emp;

select * from dep,emp where dep.id = emp.dep_id;

2 内连接inner join（两张表能一一对应的进行显示）
select * from dep inner join emp
    on dep.id = emp.dep_id;

    第一步：找到from dep  emp 两张表（做个笛卡尔积，左边的所有部门对应右边的员工1，左边的所有部门对应右边的员工2，......）
    第二步：执行on 条件 dep.id = emp.dep_id;
    第三步：inner join

3 左连接left join
select * from dep left join emp
    on dep.id = emp.dep_id;

    第一步：找到from dep  emp 两张表（做个笛卡尔积，左边的所有部门对应右边的员工1，左边的所有部门对应右边的员工2，......）
    第二步：执行on 条件 dep.id = emp.dep_id;
    第三步：left join



4 右连接right join
select * from dep right join emp
    on dep.id = emp.dep_id;

    第一步：找到from dep  emp 两张表（做个笛卡尔积，左边的所有部门对应右边的员工1，左边的所有部门对应右边的员工2，......）
    第二步：执行on 条件 dep.id = emp.dep_id;
    第三步：right join

5 全连接full join
select * from dep left join emp
    on dep.id = emp.dep_id
union
select * from dep right join emp
    on dep.id = emp.dep_id;

    第一步：执行这个select * from dep left join emp
    on dep.id = emp.dep_id
    第二步：执行这个select * from dep right join emp
    on dep.id = emp.dep_id
    第三步：执行union

多表查询之执行顺序：

连接查询与子查询是或的关系，子查询查询单个数据比连接查询性能高
#sql关键字的优先级
select distinct 字段1，字段2,... from 左表 left join 右表
    on 链表条件
    where 约束条件
    group by 字段
    having 过滤条件
    order by 排序字段
    limit n;
第一步：from找到左表、右表
第二步：做个笛卡尔积（左表的全部分别对应右表的每一条记录），得到虚拟表1
第三步：根据on 链表条件，找到一一对应
第四步：连接如果是inner join就不用调整利率，如果是left join就把左表的记录全部保留下来
第五步：where 约束条件
第六步：group by 字段
第七步：select distinct 字段1，字段2 出结果
第八步：order by 排序字段
第九步：limit n;