MySQL表操作及数据操作

表操作

　　表相当于一个文件，其形式与现实中的表格相同。表中的每条记录都有相应的字段，字段就类似于表格的表头。

表操作详细：　　

#对表进行操作（文件）
#首先要切换到指定库（即文件夹）下：use db1;
#1、增：
create table t1(id int,name char(10));#新建一个表，表中的字段必须表上数据类型，char表示字符串
create table t1(id int,name char(10))engine=innodb,default charset gbk;
#新建一个表，可以指定引擎，不指定的话默认的引擎就是innodb，default是指定其字符编码，若不指定默认为该库的字符编码

#2、添加字段
alter table t1 add age int(3) not null default 22;#添加一个不能为空的age字段，默认值22
alter table t1 add number int(5) not null after name;#在name字段后添加一个number字段
alter table t1 add sex enum('male','female') default 'male' first;#在最前面添加枚举

#3、修改字段：
alter table t1 modify name char(12);#修改name数据类型的字节数为12

#4、增加约束
alter table t1 modify id int not null primary key;#设置为主键
alter table t1 modify id int not null auto_increment;#设置为自增，与上句连用可设置为自增主键
#alter table t1 add primary key(name,sex);#增加复合主键 

#5、删除主键
alter table t1 drop primary key;#因为主键唯一，所以无需指定

#6、删除自增约束
alter table t1 modify id int not null;

#7、删除字段
alter table t1 drop age;#删除age字段

#8、删除表：
drop table t1;

#9、复制表
create table t2 select * from t1 where 1=2;#只拷贝表结构，不拷贝表内容
alter table t2 modify id int primary key auto_increment;#将表修改成自增id

#查：
show tables;#查看所有表
show create table t1;#查看创建的表
desc t1;#查看表结构，比show好用

 

数据操作

　　数据操作是数据库知识里的重中之重，尤其是多表的关联查询操作！

数据操作分为数据的增、删、改、查，其中最常用的是查询操作。先来看比较简单的增删改。

一、增

#增数据的两种形式（增即向表中插入，所以用insert形象的表达）
insert into db1.t1 values(1,'egon1'),(2,'egon2');#按位置传参，可一次性插多条数据
insert into db1.t1 (name) values('egon'),('alex');#指定只传name，id默认为null

　　

二、删

delete from t1 where id=4;#删除指定字段

　　

三、改

update t1 set name='SB' where id=4;#将id为4的字段的name修改为SB
update t1 set name='SB' where name='alex';#将name为alex的字段的name修改为SB
update t1 set name='';#将name的值都修改为空，即清空所有name的内容

　　

四、查

　　查询分为单表查询和多表查询，先介绍单表查询和查询关键字。

首先我们要知道查询的语法

select 字段 from 表名 [条件]

 

简单查询（单表查询）

首先创建一张表

create table t1(
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
);

　　

查询语句

select * from t1;
select id,name,age from t1;
select distinct post from t1;#去重
select name,age*5 from t1;
select name,age as annual_age from t1;#将查询后所得的表以annual_age 为表头
select concat('姓名':name,'薪资':salary)  from t1;#将2项整合输出
select dep_id,group_concat(name) from t1 group by dep_id;#分组显示
select dep_id,count(id) from t1 group by dep_id;#分组显示总数
select dep_id,max(age) from t1 group by dep_id;#分组后显示最大值
select dep_id,avg(age)  from t1 group by dep_id;#分组后显示最小值

　　

查条件的关键字及优先顺序

from          #定位表
where       #按条件进行过滤
group by   #按条件将结果进行分组（如果有聚合函数则再进行聚合），与group_cincat()可一起使用
having       #再次过滤，在聚合后进行
select        #查，得到结果
distinct      #去重
order by    #按条件排序（升序asc、降序desc）
limit           #限制显示条数（limit x,y#从第x开始往后显示y条，x不写时默认为0）

　　

一、where

　　where是最基本的条件筛选，条件中可以用比较运算符、逻辑运算符、between、in、like

#1:单条件查询
    SELECT name FROM employee
        WHERE post='sale';
        
#2:多条件查询
    SELECT name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';

 

二、group by

　　group by 是用来分组的，分组就是将所有记录按照某个相同字段进行归类，分组的依据一般选择字段值相同率较高的字段，需要注意的是分组发生在where之后

　　group by通常与聚合函数通用，如数量count()，最大值max()，最小值min()，平均值avg()，求和sum()

#单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    #注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

#GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

#GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

　　

三、having

　　having为分组后的过滤，与where不同的是having只能发生在group by 之后，可以理解为分组完之后的过滤

select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;

 

四、正则表达式

　　在SQL查询中也支持正则表达，其作用域like相似都是用于模糊匹配，不过正则更细致一些，like则要与%同用

SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';

 

进阶——多表查询

　　多表查询篇幅较长，故单独成篇。地址：http://www.cnblogs.com/zhuminghui/p/8352571.html