mysql操作

mysql

1、数据库

show databases;

create database db_name charset=utf8;

drop database db_name;

use db_name;

2、数据表结构

show tables;

desc tb_name;

create table tb_name(字段名 数据类型 可选约束条件, 字段名 数据类型 可选约束条件);

eg: cteate table tb_name(id int unsigned auto_increment primary key not null, name varchar(10) not null);

alter table tb_name add age int not null;

alter table tb_name change name new_name varchar(11);

alter table tb_name modify age int not null;

alter table tb_name drop age;

drop table tb_name;

show create table tb_name;

3、数据表数据

select * from tb_name;

select 字段1,字段2 from tb_name;
select distinct 字段名 from tb_name;

insert into tb_name values (val1,val2),(val1,val2);

insert into tb_name(字段1,字段2,字段3) values (val1,val2,val3);

drop from tb_name where 条件;

update tb_name set 字段1=val1,字段2=val2 where 条件;
查看
select * from tb_name as 别名
select tb_name.字段名, tb_name.字段名 from tb_name;
select 别名.字段名, 别名.字段名 from tb_name as 别名;
条件
比较运算符 > < = != …
逻辑运算符 and or not
模糊查询 like %匹配多个字符 _匹配一个字符
范围查询 连续 between … and … 非连续 in ()
空判断 is null is not null
排序 order by desc 降序 asc升序
分页 limit x,y
聚合函数 min max count sum avg
分组 group by,
group by + group_concat()
group by + 聚合
group by + having（用来过滤分组结果）
eg: select gender,count() from students group by gender having count()>2;
group by + with rollup (用来汇总)
eg:select gender,count() from students group by gender with rollup;
±-------±---------+
| gender | count() |
±-------±---------+
| 男 | 5
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
| NULL | 14 |
±-------±---------+

连接：