sql基本语法

1.sql语句命令行基本操作

连接数据库：mysql -h localhost -u root -p pwd;

查看库：show databases;

选库：use dbname;

查看库下的表：show tables;

查看表结构：desc tablename;

 

2.建表

create table tmp (
    id int primary key auto_increment,     //设置主键，自增
    name char(10),
    content varchar(200)
)charset=utf8;

 

3.增

a.添加所有列：

insert into user (uid,name,age) values (1,'lisi',23);

b.添加部分列：

insert into user (uid,name) values (1,'lucy');

c.主键自增插入：

insert into user (name) values ('yumi');

d.插入所有列的简写：

insert into user values ('kimi',25); // 报错 : 列计数不匹配值计数
insert into user values (3,'kimi',25);

e.一次插入多个数据：

insert into user values (5,'test1',44),(6,'test2',23),(7,'test3',18);

注意 :
列与值 , 严格对应 (id 自增列也必须对应 )
数字和字符串的注意点
数字不必加单引号 , 字符串必须加单引号 .
1 次添加多行数据 , 用逗号隔开

 

4.删

对于传统型数据库而言 , 一行就是它的原子型的单位
添加是一行 , 删除也要是一行

delete from user where uid=7;

注意：where子句必须有，不加where是删除所有行！

 

5.改

改哪张表 ?
你需要改哪几列的值 ?
分别改为什么值 ?
在哪些行生效 ?

update user set age=99 where name='yumi';   //例子

update 表名 set                               //语法
列 1 = 新值 1,
列 2 = 新值 2
where expr

注意：要添加where子句，不加where子句是修改所有行！

 

6.查

查哪张表的数据 ?
你要选择哪些列来查询 ?
要选择哪些行 ?

select 列 1, 列 2,... 列 n
from 表名
where expr

 

基础语法：

 

a.查询所有行：

select * from user;

b.查询一行：

select * from user where uid=2;

c.查询多行：

select * from user where uid>=2;

d.查询某几行某几列：

select uid,name from user where uid>=2;

 

进阶-select查询模型：

 

a.数据表中 列 是变量，select后面的 列变量 可以计算：

select age+1 from user where 1;   //取出所有行年龄并+1，where 1是取出所有行

select age - ave_age from user ;   //列与列之间也可以运算，取出个人年龄与平均年龄的差

b.where子句能用的运算符：

比较运算符：<, <=, !=, =, in

逻辑运算符：and, or, not, !, ||, &&

select name from user where age in (4,11);         //取出4岁或者11岁的人名

select name from user where (age=4) or (age=11) ;     //取出4岁或者11岁的人名

select name  from user where age between 10 and 50;       //取出年龄在10-50之间的人名

select name  from user where (age > 10) and (age < 50);    //取出年龄在10-50之间的人名

select name  from user   where age!=3 and age!=11;     //取出年龄不是3岁且不是11岁的人名

select name  from user   where age  not in (3,11);          //取出年龄不是3岁且不是11岁的人名

 

 

======================================================================================

7.统计函数和group分组

a.求表中所有数据总行数：

select count(*) from user ;

b.求某列的平均值：

select avg(age) from user ;

c.求某列的值的总和：

select sum(age) from user ;

d.求某列的最大最小值：

select min(age) from user ;
select max(age) from user ;

e.根据某列的值来分组（group by）：

select  avg(商品价格),栏目ID  from goods group by 栏目ID ;

 

8.having筛选

where是用来筛选表字段的，having是用来筛选结果集的。

例子a.查询比市场价省钱 200 元以上的商品及该商品所省的钱 (where 和 having 分别实现 ）：

select goods_id,shop_price,market_price,(market_price-shop_price) from goods where (market_price-shop_price)>200;    //where实现

select goods_id,shop_price,market_price,(market_price-shop_price) as sheng from goods where 1 having sheng>200;        //having实现

例子b.选出挂科超过2门的人：

select name,sum(score<60) as gk from result group by name having gk>=2;

where 和 having 都存在 ,where 要放在 having 的前面 ;
因为 where 是对内存中的表进行筛选
而 having 是对进一步计算出来的结果集进行再一步筛选

 

9.order by 排序

asc 升序排列 从低到高
desc 是降序排列 . 从高到低

a.根据单列值排序：

select goods_id,goods_name,shop_price from goods order by shop_price;

b.先根据栏目id排序，在栏目内部再根据价格排序：

select goods_id,goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc;

 

10.limit限制取出条目

order by 一般和 limit 配合使用 , 功能才会更强大
limit 有选择性的取出 1 条或多条 ,
limit 限制取出条目
limit offset ,N , 跳过 offset 条 , 取 N 条

（经常配合完成 列表分页 显示）

a.取出价格最高的前三名商品：
按照价格降序排列 -> 取出前三行

select goods_id,goods_name,shop_price from goods order by shop_price desc limit 0,3;

 b.取出点击量是前三名到前五名的商品：

select goods_id,goods_name,shop_price,click_count from goods order by click_count desc limit 2,3;

 

11.子句顺序

当有多个子句时，有严格的顺序，顺序如下：

select 后的子句 where, group by , having,order by , limit

 

12.where型子查询

内层的 select 查询结果 , 放在了外层的查询条件里面 ; 这种查询叫做 where 型子查询

分组查询没个栏目下的最大goods_id

select max(goods_id) from goods group by cat_id;

合并：

select goods_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);

 

13.from型子查询

from 型子查询 :
先 select 查询出结果集看作一张表 , 起一个别名 , 再在外层 select 这张临时表中的内容 ; 现在的版本from子句无视内部排序

select goods_id,goods_name,cat_id from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id

 

14.exists型子查询

exists 存在的意思

查询所有有商品的栏目：

select * from category where exists (select * from goods where category.cat_id=goods.cat_id);

 

======================================================================================

15.内连接查询