1.1.基本sql语句

###### NULL（NOT NULL）：表示字段是否可以为空； ###### DEFUALT：指定字段的默认值； ###### AUTO_INCREMENT：设置字段为自增，只有整型类型的字段才能设置自增。自增默认从1开始，每个表只能有一个自增字段； ###### UNIQUE KEY：唯一性约束； ###### PRIMARY KEY：主键约束； ###### COMMENT：注释字段； ###### 外键定义：外键约束 

PRIMARY KEY(字段名1，字段名2，…，字段名n) ##### CREATE TABLE SCarInfo ##### ( gdID INT,uID INT,scNum INT, ##### PRIMARY KEY(gdID,uID) -- 定义复合主键 ##### ); 

##### CREATE TABLE users ( ##### uID int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID', ##### uName varchar(30) NOT NULL UNIQUE, ##### uPwd varchar(30) NOT NULL, ##### uSex ENUM('男','女') DEFAULT '男' ##### ); 

#### CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名); #### CREATE TABLE GoodsType ##### 主表:( tID INT PRIMARY KEY ##### tName VARCHAR(30) NOT NULL ##### ); 设置tid为外键约束 ##### 从表：CREATE TABLE Goods ##### ( gdID INT PRIMARY KEY AUTO_INCREMENT, -- 标识该字段为主键且自增 ##### tID INT NOT NULL, ##### gdCode VARCHAR(30) NOT NULL UNIQUE, ##### gdName VARCHAR(30) NOT NULL, ##### gdPrice DECIMAL(8,2), ##### CONSTRAINT FK_tID FOREIGN KEY (tID) REFERENCES GoodsType (tID) ##### ); 

##### CONSTRAINT 外键名 FOREIGN KEY(外键字段名) ##### REFERENCES 主表名(主键字段名) ##### [ON UPDATE { CASCADE | SET NULL | NO ACTION | RESTRICT }] ##### [ON DELETE { CASCADE | SET NULL | NO ACTION | RESTRICT }] ###### CASCADE：指定在更新和删除操作表中记录时，如果该值被其他表引用，则级联更新或删除从表中相应的记录。 ###### SET NULL：更新和删除操作表记录时，从表中相关记录对应的值设置为NULL。 ###### NO ACTION：不进行任何操作 ###### RESTRICT：拒绝主表更新或修改外键的关联列。 ###### CREATE TABLE Goods ###### ( gdID INT PRIMARY KEY AUTO_INCREMENT, -- 标识该字段为主键且自增 ###### tID INT NOT NULL,gdCode VARCHAR(30) NOT NULL UNIQUE, ###### gdName VARCHAR(30) NOT NULL,gdPrice DECIMAL(8,2), ###### CONSTRAINT FK_tID FOREIGN KEY (tID) REFERENCES GoodsType (tID) ###### ON UPDATE CASCADE ON DELETE CASCADE ###### ); 

select *from 表名；

select gdcode,gdname,gdprice,gdsaleqty from goods;

select gdcode,gdname,gdprice,gdsaleqty,gdhot,gdcity from goods;

select gdname,gdsaleqty*gdprice from goods;
获取用户年龄：
select uname 用户名,year(now())-year(ubirth) 年龄 from users;

select gdname as 商品名称,gdprice as 价格, gdcity 城市 from goods;

select uname 用户名 from users where uid=8;
select uname,uphone,ubirth from users where ubirth>=’2000-1-1′;

and,or ,not
select uname,uphone,ubirth from users where ubirth>=’2000-1-1′ and usex=’男’;
select gdname,gdprice from goods where not(gdprice>50);
select gdname,gdprice,gdcity from goods where (gdcity=’长沙’ or gdcity=’西安’) and gdprice<=50;

select gdname from goods where gdprice (not) between 100 and 500;

select gdname,gdcity from goods where gdcity （not）in(‘长沙’,’西安’,’上海’);

select uname,usex,uphone from users where uname like ‘李%’;
select uname,usex,uphone from users where uname like ‘_湘%’;

select gdcode,gdname,gdprice from goods where gdname like ‘华为P9_%’;
select gdcode,gdname,gdprice from goods where gdname like ‘华为P9|%’ escape’|’;
select gdcode,gdname,gdprice from goods where gdname like ‘华为P9/%’ escape’/’;
select gdcode,gdname,gdprice from goods where gdname like 一.选择列
1.查询所有列
select*from 表名；

2.查询指定列
select gdcode,gdname,gdprice,gdsaleqty from goods;
3.查询指定列(自定义顺序)
select gdcode,gdname,gdprice,gdsaleqty,gdhot,gdcity from goods;
4.计算列值
select gdname,gdsaleqty*gdprice from goods;
获取用户年龄：
select uname 用户名,year(now())-year(ubirth) 年龄 from users;
5.为查询表中的列指定标题(as可省略)
select gdname as 商品名称,gdprice as 价格, gdcity 城市 from goods;
二.选择行
select uname 用户名 from users where uid=8;
select uname,uphone,ubirth from users where ubirth>=’2000-1-1′;
逻辑运算符
and,or ,not
select uname,uphone,ubirth from users where ubirth>=’2000-1-1′ and usex=’男’;
select gdname,gdprice from goods where not(gdprice>50);
select gdname,gdprice,gdcity from goods where (gdcity=’长沙’ or gdcity=’西安’) and gdprice<=50;
between-and运算符
select gdname from goods where gdprice (not) between 100 and 500;
in运算符：(指定条件范围)
select gdname,gdcity from goods where gdcity （not）in(‘长沙’,’西安’,’上海’);
Like运算符
select uname,usex,uphone from users where uname like ‘李%’;
select uname,usex,uphone from users where uname like ‘湘%’;
默认转义字符：“\” ；escape自定义转义字符
select gdcode,gdname,gdprice from goods where gdname like ‘华为P9_%’;
select gdcode,gdname,gdprice from goods where gdname like ‘华为P9|%’ escape’|’;
select gdcode,gdname,gdprice from goods where gdname like ‘华为P9/%’ escape’/’;
select gdcode,gdname,gdprice from goods where gdname like ‘华为P9?%’ escape’?’;

select uname,usex,uphone from users where uphone regexp ‘5$’;
select uname,usex,uphone from users where uphone regexp ‘^1[678]’;
is null 运算符(判断是否为空)
select uname,usex from users where uimage is (not) null;

select gdcity,gdname from goods where gdprice<200;
select distinct gdcity from goods where gdprice<200;

select gdcode,gdname,gdprice from goods where tid=1 order by gdprice asc;
多个排序规则
select gdcode,gdname,gdprice,gdsaleqty from goods where tid=1 order by gdsaleqty desc,gdprice;

select gdcode,gdname,gdprice,gdsaleqty from goods limit 2;

sum 返回组中所有值的和 avg 返回组中各值的平均值

max 返回组中的最大值 min 返回组中的最小值

count 返回组中的项数

group_concat 返回一个字符串结果，该结果由分组中的值连接组合而成

sum/avg/max/min ([all | distinct] 列名|常量|表达式)

select max(gdprice) from goods;

select sum(gdsaleqty) from goods;

distinct指定count返回唯一非空值的数量(消除重复集)；* 指定应该计算所有行并返回表中行的总数

select count(*) from users;

查询orders表，显示购买过商品的用户人数：

select count()

//根据城市分组

select uid,uname,usex,ucity from users group by ucity;

//统计各城市的用户人数

select ucity,count(*) from users group by ucity;

group by和group_concat一起使用：
select ucity,group_concat(uname) as unames from users group by ucity;
select ucity,group_concat(uname) as unames from users group by ucity;
select ucity,count() from users group by ucity having count()>=3;
select ucity,count() from users group by ucity having ucity=’上海’;
select ucity,count() from users where ucity=’上海’ group by ucity;

select tname,gdcode,gdname,gdprice from goodstype g join goods a on g.tid=a.tid where tname=’服饰’;
select uname,sum(ototal) from users u join orders o on u.uid=o.uid where uname=’段湘林’;

select gdname,gdprice,scnum from user u join scar s on u.uid=s.uid join goods g on g.gdid=s.gdid where uname=’段湘林’;

select s.uname,s.uphone,s.ucity from users s join users t on s.ucity=t.ucity where t.uname=’蔡准’;
（子查询）select uname,uphone from users where ucity=(select ucity from users where uname=’蔡准’);

select s.uid,uname,ototal from users s left join orders t on s.uid=t.uid;

select s.uid,uname,count(t.uid) from orders t right join users s on s.uid=t.uid group by s.uid;

select uid,uname,gdid,gdname from users cross join goods;

select uid,uname,usex from user where uid=1 union select uid,uname,usex from user where uid=2;
select tid,gdname,gdprice from goods where tid=1 union select tid,gdname,gdprice from goods where tid=2 order by gdprice desc limit 3;

select gdid,gdname,gdprice,gdsaleqty from goods where tid=(select tid from goodstype where tname=’服饰’);

select devalution,odtime from orderdetail where gdid=(select gdid from goods where gdname=’LED小台灯’);
select uname,uphone from users where ucity=(select ucity from users where uname=’蔡准’);
select gdcode,gdname,gdprice,gdsaleqty from goods where gdsaleqty<(select gdsaleqty from goods where gdname=’LED小台灯’);

查询已购物的会员信息，包括用户名，性别，年龄和注册时间

select uname,usex,ubirth,uregtime from users where uid in (selec uid from orders); 

查询比服饰类某一商品价格高的商品信息，包括商品编号，商品名称和商品价格

select gdcode,gdname,gdprice from goods where gdprice>any (select gdprice from goods where (tid=(select tid from goodstype where tname='服饰'))); 

查询价格比服饰类商品都高的商品信息，包括商品编号，商品名称和商品价格

select gdcode,gdname,gdprice from goods where gdprice>all (select gdprice from goods where (tid=(select tid from goodstype where tname='服饰'))); 

查询年龄了在20-30之间的用户名，性别和年龄

select*from (select uname,usex,year(now())-year(ubirth) from users) as temptb where uage between 20 and 30; 

查询已购物的会员信息，用户名，性别，出生年月和注册时间

select uname,usex,ubirth,uregtime from users where exists(select*from orders where users.uid=orders.uid); 

相关子查询还尅嵌套在select子句的目标列中，通过子查询计算出关联数据的目标列

插询2016年11月17日评价了商户的用户，列出用户名，被评价的商品名和评价时间

select (select uname from users where uid=(select uid from orders where oeders.oid=od.oid)) as '用户名',(select gdname from goods where goods.gdid=odid) as '商品名', odtime as '评价时间' from orderdetail as od where date_format(odtime,'%Y-%m-%d')='2016-11-07'; 

1.查询结果集作为插入的数据源

创建商品历史表，并将库存量小于10且上架时间超过一年的商品下架处理，并将这些商品添加到goodshistory

----创家居goodshistory，复制goods表结构-------- create table goodshistory like goods; -----将满足条件的商品插入到goodshistory表中------ insert into goodshistory select*from goods where gdquantity<10 and year(now())-year(gdaddtime)>=1; 

2.子查询用于修给数据

统计订单详情表中的评价书，将商品销售量为200以上，且评价大于1的商品设置为热销商品(gdhot属性值改为1)

update goods set gdhot =1 where gdsaleqty>=200;

3.子查询用于删除数据

将已下架的商品从商品表中删除，其中下架的商品是指已将存放在历史表中的商品

select from goods where gdcode in (select gdcode from goodshistory); 

1.show create table newgoods;
2.show index from newgoods;
show keys from newgoods;

1.alter table newgoods drop index ix_gdcode;
2.drop index ix_fullixord on orderdetail;(表名)

创建名为view_ugd的视图，用来显示用户购买的商品信息，包括用户名，商品名称，购买数量以及商品价格

create view view_gud(用户名,商品名称,购买数量,商品价格) as select a.uname,gdname,b.scnum,c.gdprice
from users a join scar b on a.uid=b.uid join goods c on c.gdid=b.gdid;
create view view_users as select uname,upwd,usex,ubirth,uphone from users;

视图属性：
show table status like ‘view_users’;
视图结构：
desc view_users;
视图语句：
show create view view_gud;

create or replace view view_users(姓名,电话) as select uname,uphone from users;
alter view view_users(姓名,电话) as select uname,uphone from users;

drop view if exists 视图名 【resteict|cascade】//restrict:表示不能级联删除 cascade：表示级联删除

update view_users set 电话=’154622488200′ where 姓名=’蔡准’;

insert into viewusers values(‘周鹏’,’123′,’男’,’123′,’1999-09-1′);
delete from viewusers where uname=’周鹏’;

原文链接：https://www.cnblogs.com/zh93/p/12500063.html