day08-mysql表操作回顾(工作重点)

1【插入记录】

语法

* insert into 表名 (列名,列名,...) values (值1,值2,...);           ---插入指定列的值

* insert into 表名 values (值1,值2,...);                                 ---插入所有列的值

注意事项：

* 列的类型如果是字符串或者日期,写值的时候使用单引号将值引起来.

 

插入中文：mysql命令行cmd中文乱码的解决办法.

***** 插入中文问题的解决：

* show variables like '%character%';     查看字符集的参数.

***** 找到MYSQL的安装路径/my.ini文件：修改client的编码.

如果计算机权限很高的的话需要把配置文件复制到桌面,修改好后再替换掉路径下的源文件.

**** 重新加载mysql的配置文件：

* 命令行中输入services.msc

* 停止mysql的服务,重新启动mysql服务.

* 执行之前的SQL语句.

 

2.【修改记录】

* update 表 set 列名=值,列名=值 [where 条件];

 

3.【删除记录】

语法：

* delete from 表 [where 条件];

删除表中的所有记录truncate table表名  和 delete from表  区别?

* 区别:

    *truncate table 删除表的记录：将整个表删除掉,重新创建一个新的表(从ID编号中体现出).truncate属于DDL.

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

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

开启事务后才能回滚:

Start transaction ;

Delete from employee ;

Select * from employee ;

Rollback ;

Select * from employee ;

4.【基本查询】

查询语句：

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

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

* select distinct english from exam;

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

select name , english+chinese+math as sumfrom exam;

 

5.【条件查询】

where语句后面可以加：

条件的关键字：

= , > , >= , <, <= , <>  and ,or ,not

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

*in 后跟着一组值.

Select * from exam where id in (1,2,3) ;

 

6.【排序查询】

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

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

    *select * from exam order by english desc, chinese desc;

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

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

 

7.【聚合函数】

sum()

count()

max()

min()

avg()

* 统计所有学生的总分:

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

横着加(遇到null停止该行的相加,只影响当前行)

解决遇到null停止加的尴尬:Select sum(ifnull(english, 0)+chinese +math) from exam ;

    *select sum(english)+sum(chinese)+sum(math) from exam; 竖着加

这二者在下面这张表下的结果在不加ifnull时会有所不同.

* 统计学生的个数:

    *select count(*) from exam;

* 统计英语成绩的最高分(如果某人english为null,则不计入人数.)

    *select max(english) from exam;

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

    *select min(chinese) from exam;

* 统计英语成绩平均分:

    *select avg(english) from exam;

求一个班级总分平均分

SELECT AVG(IFNULL(chinese,0)+IFNULL(math,0)+IFNULL(english,0)) AS总平均分 FROM exam ;

求班级最高分和最低分（数值范围在统计中特别有用）

SELECT MAX( IFNULL(chinese,0) + IFNULL(math,0) + IFNULL(english,0)) AS maximum, MIN(IFNULL(chinese,0) + IFNULL(math,0) + IFNULL(english,0) )minmum FROM exam;

8.【分组】

group by

创建一个订单详情的表:

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

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

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

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

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

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

 

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

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

 

9.【SQL的查询语句的总结】

顺序:      select...from...where...group...having...order...;

 

【数据库的多表设计】

【多表的设计】

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

* 在多的一方创建一个字段,这个字段作为外键指向一的一方的主键.

多对多的关系的建表原则：

*　创建一个第三种表，中间表中至少需要两个字段分别作为外键指向多对多双方的各自的主键.

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

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

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

***** 约束：用来保证数据的完整性.

* 多表约束：外键约束！！！

***** 给orders表中的cid添加外键约束.

Create table orders(

       cidint ,

       foreignkey(cid) references customer(cid)

) ;   外链到customer表的cid字段

 

* alter table orders add foreign key (cid) references customer(cid);

注意:

添加外键约束后,在执行delete from customer where cid = 2删除customer表中的用户就无法删除了,要先删除orders表中cid=2的记录, 才能删除customer表中的cid=2的用户.

10.【多表的查询的SQL】

多表的查询的方式：

* 交叉连接：

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

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

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

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

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

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

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

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

       * SELECT * FROM customer 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;

查询右边表的全部和左右两张表的交集

 

11.【多表查询的子查询】

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

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

 

12.【多表练习】

按客户名称统计订单的个数.

SELECT c.cname,COUNT(*) FROM customer c,orderso WHERE c.cid = o.cid GROUP BY c.cname;