基本的SQL语言

创建

创建数据库,创建表

create database test;

create table user(
id int not null primary key,
name varchar(32) not null,
password varchar(32) not null);

 

可以用desc查看创建的表的结构

desc user;

 

增

alter添加字段

alter table user add sex int;

 

insert into添加数据

insert into user (id,name,password) values 
(001,'a','123'),
(002,'b','qwe'),
(003,'c','zxc');

 

删

delete删除数据

delete from user where id=003;            #删除指定行

delete from user;                               #删除表

 

alter删除字段

alter table user drop sex;

 

改

alter修改字段

alter table usermodify password int;

 

update修改数据

update user
set name='xiaoming',password='123456'
where id=001;

 

查

基本查询

select * from user where id=001;

 

in查询

select * from user where id in (001,003);

 

between and查询

select * from user where id [not] between 001 and 003;

 

distinct查询,过滤重复值

select distinct password from user;

 

like查询

select * from user where name='a%';        #以a开头

select * from user where name='%a';        #以a结尾

select * from user where name='%a%';      #包含a

select * from user where name='xiao_ing'   #单个字符空缺匹配

 

and和or查询

select * from user where id<2 and password='123456' or password='qwer';
#and优先级高于or

 

高级函数

函数名	作用
count()	得到列行数
sum()	得到列值总和
avg()	得到列平均值
max()	得到列最大值
min()	得到列最小值

 

 

 

 

 

 

 

 

对查询结果排序order by

select * from user order by name[asc | desc];　　#asc升序 \ desc降序

 

分组查询group by

select * from user group by password [having];        #查询每个分组中的一条记录(having相当于where）

 

限制查询结果limit

select * from user limit 3;        #限制输出行数

 

为表、字段取名

select * from user as u1;

select name as na,password as pa from u1;