sql语句整理

• 数据库的增删改查
  1. 创建数据库
     1. create database 数据库名称 [character set 字符集 collate 字符集校对];
     2. create database demo  character set utf8 collate  utf8_bin;
  2. 查看数据库
     1. 查看所有数据库: show databases;
     2. 查看某个数据库的定义信息:show create database 数据库名.
     3. 查看当前正在使用的数据库信息:select database();
  3. 删除数据库
     1. Drop database basename;
  4. 修改数据库
     1. Alter database basename character set 新字符集 collate 校对规则;
  5. 切换数据库
     1. User databasename;
• 表的常见及增删查改
  1. 创建表

Ø        语法:

 create table 表名 (

    字段名 类型(长度) 约束,

    字段名 类型(长度) 约束,

    字段名 类型(长度) 约束

);

2. 查看表
   1. Show tables;
3. 查看表结构:
   1. Show tables;
4. 表的删除
   1. Drop table tablename;
5. 修改表添加列
   1. Alter table tname add newcolumn 类型()约束;
   2. e.g alter table tname add image varchar(20) unique;
6. 删除列
   1. Alter table name drop 列名;
7. 修改表的列名
   1. Alter table student modiy name varchar(20) not null;

8. 变更表的列名
   1. Alter table 表名 change 旧列名 新列名 类型(长度)约束;
   2. Alter table employee change image eimage varchar(60);
9. 修改表名
   1. Rename  table employee to user;
   2. Rename table 旧表名 to 新表名;
10. 修改表的字符集
    1. Alter table 表名 character set 字符集;
11. 添加外键
    1. Foreign key(key_id)references user_info(user_id) on delete cascade on update cascade;

• 表记录的增删改
  1. 添加
     1. Inser into 表名(列名1,列名2,列名3)values(值1,值2,值3);
     2. Inset into 表名 values(全部字段,没有null);
  2. 修改记录
     1. Update 表 set 列名=值,列名=值 where 条件;
     2. 如果没有条件,所有行的两个字段都会更改;
  3. 删除
     1. Delete from taname where 条件;
     2. 不加条件则删除所有的列;

* 区别:

    * truncate table删除表的记录：将整个表删除掉,重新创建一个新的表.truncate属于DDL.

    * delete from删除表的记录：一条一条进行删除. delete属于DML。

        * 事务管理只能作用在DML语句上.如果再一个事务中使用delete删除所有记录，可以找回.

• 基本查询
  1. 查询语句：

                      select [distinct] *|列名 from 表 [where 条件];

Ø        Ø        查询所有记录:

* select * from exam;

Ø        查询这个班级人的姓名和英语成绩:

* select name,english from exam;

Ø        查询英语成绩,将重复英语成绩去掉:

* select distinct english from exam;

Ø        查询李四的学生成绩:

select * from exam where name='李四';

Ø        查询名称叫李四并且英语成绩大于90的

select * from exam where name='李四' and english >90;

Ø        将成绩+10分进行显示:

select name ,english+10,chinese+10 ,math+10 from exam;

Ø        显示这个人的名称和对应总成绩的分数:

select name,english+chinese+math from exam;

Ø        使用as起别名,as可以省略.

select name , english+chinese+math as sum from exam;

• 条件查询

Ø        where语句后面可以加：

条件的关键字：

= , > , >= , <, <= , <>

like中可以使用占位符: _ 和 % ：下划线匹配一个字符, %:可以匹配任意多个字符.

* like ‘张％’;  like ‘张_’; like‘%明’; like ‘%明%’;

in 后跟着一组值.

* id in (1,2,3)

           and or not

• 排序查询

Ø        order by对数据进行排序.默认升序. （asc升序,desc降序）

* 查询所有学生的信息,并且按语文成绩进行排序.

    * select * from exam orderby chinese;

* 查询所有学生的信息,并且按语文成绩进行降序排序.

    * select * from exam orderby chinese desc;

* 查询学生的信息,按照英语成绩降序排序，如果英语成绩相同,按照语文降序.

    * select * from exam orderby english desc, chinese desc;

* 查询姓李的学生的信息,同时按照英语升序排序.

    * select * from exam wherename like '李%' order by english asc;

• 聚合函数

Ø        sum()

Ø        count()

Ø        max()

Ø        min()

Ø        avg()

* 查询每个学生总成绩:

    * selectname,(english+chinese+math) from exam;

* 统计所有学生的总分:

    * selectsum(english+chinese+math) from exam;   --ifnull(english,0)

    * selectsum(english)+sum(chinese)+sum(math) from exam;

* 统计学生的个数:

    * select count(*) fromexam;

* 统计英语成绩的最高分:

    * select max(english) fromexam;

* 统计语文成绩的最低分:

    * select min(chinese) fromexam;

* 统计英语成绩平均分:

    * select avg(english) fromexam;

• 分组

Ø        groupby   后面条件过滤使用having;

创建一个订单详情的表:

* 统计订单中的每类商品所购买的个数：

    * SELECT product,COUNT(*)FROM orderitem GROUP BY product;

* 统计订单中的每类商品所花的金额:

    * SELECTproduct,SUM(price) FROM orderitem GROUP BY product;

* 统计订单中的每类商品所花总金额大于2000信息.

    * SELECTproduct,SUM(price) FROM orderitem GROUP BY product HAVING SUM(price) > 2000;

 

* 统计订单中名称有电子的商品并且所花金额大于1500同时按照价格降序排序：

    * SELECTproduct,SUM(price) FROM orderitem WHERE product LIKE '电%' GROUP BY productHAVING SUM(price) > 1500 ORDER BY SUM(price) DESC;

【SQL的查询语句的总结】

顺序:        s...f...w...g...h...o...;

 

• union all 和 union

Union all:连接两个子句,轴向连接,包含重复项;

Union:连接两个子句,轴向连接,会过滤掉重复项,并按字段排序,效率低,一般确认没有重复项,并不需要排序的时候,使用unionall;

e.g:select * from student union all select * from sc;

• 从一个表中查询出来后插入到另一张表中

            INSERT INTO course(Cname) SELECT Sname FROMstudent;

  注意:字段需要对应;

• 多表设计
  1. 一对多
     1. 在多方创建一个字段,将这个字段作为外键指向一方的主键;
  2. 多对多
     1. 中间表,两个字段分别作为外键指向个自的主键;

 

Ø        一对一的关系的建表原则：

*唯一外键对应：假设一对一的双方是一对多的关系.在多的一方创建外键执向一的一方的主键.需要在外键上添加一个unique约束.

*主键对应：将一对一的双方的主键建立映射.

• 多表的查询的SQL

Ø        多表的查询的方式：

* 交叉连接：

    * select * from A,B;   --- 获得的是两个表的笛卡尔积.

* 内连接: inner join -- inner 可以省略

    * 显式内连接：select * from Ainner join B on 条件;

        * SELECT * FROMcustomer c INNER JOIN orders o ON c.cid = o.cid;

    * 隐式内连接：select * from A,Bwhere 条件;

        * SELECT * FROMcustomer c ,orders o WHERE c.cid = o.cid;

* 外连接：outer join -- outer 可以省略

    * 左外连接：left outerjoin  -- select * from A left outer joinB on 条件;

        * SELECT * FROMcustomer c LEFT OUTER JOIN orders o ON c.cid = o.cid;

    * 右外连接：right outer join --select * from A right outer join B on 条件;

        * SELECT * FROMcustomer c RIGHT OUTER JOIN orders o ON c.cid = o.cid;

1. 左外连接和隐式连接的区别在于,使用左外连接时,右侧表没有的项目,会显示null,而隐式连接不会有这一行;

• 多表查询的子查询

Ø        一个SQL语句查询的过程中需要依赖另一个查询语句.

SELECT * FROM customer c,orders o WHERE c.cid = o.cid AND  c.cid IN (SELECT cid FROM orders WHERE addrLIKE '海淀%');