mysql表数据、_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 notnull;#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 BETWEEN10000 AND 20000;

SELECT name,salary FROM employee

WHERE salary NOT BETWEEN10000 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__';

where具体用法

二、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;#按照岗位分组，并查看每个组有多少人

group by 用法

三、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}';

进阶——多表查询