注: []代表的是可以不写,不是直接写[]格式,写的时候是要去掉[]
数据表的操作
增
create table 数据表名 (字段名1 类型 [字段属性...], 字段名2 类型 [字段属性...], ...) [charset=字符集] [engine=表类型];
// 如:
create table table1 (id int,name varchar(10),sex char(1) ) [charset=字符集] [engine=表类型];
删
drop table 表名;
改(修改表就是 对字段增删改查+改表名+改字符集)
改表名
alter table 表名 rename 新表名;
改字符集
alter table 表名 charset=新字符集;
字段增删改查
见下面
查
查所有表
show tables;
查看某表结构
desc 表名;
查看某表的创建语句
show create table 表名;
字段的操作(修改数据表)
增
alter table 表名 add 字段名 字段类型 [字段属性] [after 已有某字段名]; ('after 已有某字段名'可以是'first', 在某字段的后,在表最前)
删
alter table 表名 drop 删的字段名;
改
alter table 表名 change 旧字段名 新字段名 字段类型 [字段属性...];
// 若不修改名,只改其他内容:
alter table 表名 modify 旧字段名 字段类型 [字段属性...];
数据操作(初)
增
// 字段与数据要一一对应
insert into 表名 (字段1, 字段2, ...) values (数据1, 数据2, ...);
// 若数据与所有字段对应:
insert into 表名 values (数据1, 数据2, ...);
删
delete from 表名 [where 条件];
// 如:
delete from table1 where id>6 and order_id = 4;
改
update 表名 set 字段名1 = 新值1, 字段名2 = 新值2, ... [where 条件];
// 注: where条件不写将会所有数据都改掉, 一般不这么做
// 如:
update table1 set content = '新的内容' where id=3;
查
select 字段名1, 字段名2, ... from 表名 [where 条件];
// 注: * 代表所有字段名; where不写就是取出所有数据;
// 如:
select pro_id, pro_name from product where pro_id<10 and price>5000;
高级操作
高级查询(查)
语法概述
select 子句
[from子句]
[where子句]
[group by子句]
[having子句]
[order by子句]
[limit子句];
查询结果数据及select选项
查询'固定数据'
select 'abc'; // 就是以'abc'为表头查询
可计算
select 1+2, 1+round(); //round是系统函数
使用别别名 as
select user_name as un, user_pass as pwd from user;
消除重复行 disctinct (查出的结果两及以上数据完全一样时,只显示一个)
select disctinct 字段1, 字段2, ... from 表名;
where子句
select ... from 表名 where 条件;
// 如:
where id > 10;
where age - 18 >= 0;
where id < 10 and age >= 19;
where year % 4 = 0 and year % 5 != 0 || year % 400 = 0;
mysql运算符
// 算数运算符:
+ - * / %
// 比较运算符:
= > >= < <=
不相等: <> 或 !=
// 逻辑运算符:
与: && 或 and
或: || 或 or
非: ! 或 not
其他特殊运算符
like 模糊查找
% : 表示 任意个数 的 任意字符
where name like '余%';
_ : 表示 一个 的 任意字符
where name like '_志勇';
between 范围限定,(两值之间,包含两值)
where id between 2 and 4;
// 相当于:
where id >= 1 and id <= 4;
可选值 in
where id in (1, 3, 5); // id满足其中之一则找出;
是否存在 is
// 只有两种写法:
where content is null;
where content is not null;
group by 以给定的字段进行分组
group by 字段1, 字段2, ... ;
// 如:
select * from table1 group by pinpai; //按pinpai这个列进行分组
// 多条件分组
select * from table1 group by pinpai, chandi; // 按pinpai,chandi进行分组
聚合函数(分组不是会将重复的重叠吗, 这个就是重叠的规则)
count 计数值,原始数据的行数,一般count(*)
max 最大值
min 最小值
avg 平均值
sum 总和值
// 列: 以chandi为第一列以此为分组的,以chandi、数量、平均价为表头的表
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi;
// 产地是北京的平均价
select avg(price) from table1 where chadi = '北京'
having对group by的结果进行筛选
// 平均价>5000
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi having 平均价 > 5000;
order by排序
// asc升序(默认) desc降序
order by 字段1 [asc或desc], 字段2 [asc 或 desc],...
// 如:
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi having 平均价 > 5000 order by 数量 asc 平均价 desc;
limit 取多少行(通常用于翻页)
// 注: 起始行号是从0开始不是id, 且默认为0
limit [起始行号,] 行数
// 用于翻页的公式,pageSize是每页条数,n表示'第n页':
limit (n-1)*pageSize, pageSize;
// 列,第一页,显示10条:
select chandi, count(*) as 数量, avg(price) as 平均价 from table1 group by chandi having 平均价 > 5000 order by 数量 asc 平均价 desc limit (1-1)*10, 10;
高级插入(增)
同时插入多行数据
insert into 表名 (字段1, 字段2, ...) values (数据1, 数据2, ...), (数据1, 数据2, ...), ...;
插入查询到的结果(插入的就是select语法)
insert into 表名 (字段1, 字段2, ...) select ...;
set语法插入,用 = 一一对应的插入
insert into 表名 set 字段1 = 值1, 字段2 = 值2, ...;
插入时主键冲突解决方法
忽略ignore (终止插入)
insert ignore into 表名 (字段...) values (值...);
替换replace (新的替换旧的)
insert replace into 表名 (字段...) values (值...);
更新
// 若冲突了,就只改规定的字段
insert into 表名 (字段...) values (值...) on duplicate key update 字段名 = 新的值;
高级删除(删)
指定顺序删除指定数量
delete from 表名 where ... [order by 字段名...] [limit 数量n];
清空truncate (恢复'初始状态')
相对于delete from ,这个truncate的自增状态会重新开始
truncate 表名;
高级更新(改)
update 表名 set 字段1=值1, 字段2=值2, ... where ... [order by 字段名...] [limit 数量n];
联合查询union(纵向连接)
union distinct: 消除重复(默认,写union就是);
union all: 保留所有;
// 如,注应有相同列数:
select id, a, b, c from table1
union
select id2, a2, b2, c2 from table2
...
[order by 字段...] [limit ...]; // 和正常查询的一样操作,只不过是两个表变成了一个
连接查询join(横向连接)
select ... from 表1 [连接方式] join 表2 [on连接条件] where ...;
连接方式分为:
交叉连接
内连接
外连接(左右外连接)
交叉连接 cross(通常价值不大)
select ... from 表1 [cross] join 表2;
内连接inner join(常用)
select ... from 表1 [inner] join 表2 on 连接条件;
// 如找出table12中id相同的并列出左右组合后的表:
select * from table1 inner join table2 on table1.id = table2.id;
左外连接left outer join(保证左边数据能够显示出,右边为添加的数据)
select tab1.name, tab2.age from 表1 as tab1 left [outer] join 表2 as tab2 on 连接条件;
右外连接right outer join(和左位置换下)
select ... from 表1 right [outer] join 表2 on连接条件;
自连接(自己和自己连接)
select ... from 表1 as a [连接形式] join 表1 as b on 字段名1 = 字段名2;
子查询(就是拿查询语句去对比语句)
select * from 表1 where price>=(一个子查询语句);
标量子查询(子查询的结果是 单个值)
// 查价格大于产地是北京的平均价的表
select * from table1 where price > (
select avg(price) from table1 where chadi = '北京'
);
列子查询(子查询的结果是 一列)
// 如:
select * from table1 where chandi in(
select pinpai as '品牌' from table1 where chandi = '北京'
);
行子查询(子查询结果是 一行)
where row(字段1, 字段2, ...) = (行子查询)
表子查询(子查询结果是 多行多列)
select ... from (表子查询) as tab1
exists子查询(有数据返回true,否则返回false)
where exists (任何子查询)
子查询的关键字
in: 其中每个都过一遍
any: 满足其中一个
all: 满足其中所有
// 如:
select * from table1 where chadi = '北京' and price > any(
select price from table1 where chandi = '深圳'
)
