小白学SQL（2）

2018-12-16 本文已影响4人 Lykit01

之前都在网上看各种sql总结教程，虽然很简明，但是对概念解释的不多，这次看小组资料里的《mysql必知必会》，感觉知识系统了很多，看专著还是很好的。
这两天看完了《mysql必知必会》看的时候做了点笔记，解决了在书中遇到的一些问题，这里一并发出来，大家学习过程中如果有同样的疑问也可以参考一下我的解答。另外推荐用印象笔记，随学随记，搜索很方便，比word方便多了。

一、细节问题

1.distinct

警告：不能部分使用DISTINCT DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。例如，你指定SELECT DISTINCT vend_id, prod_price，除非指定的两列完全相同，否则所有的行都会被检索出来；

2.Access不支持注释；

3.为正确返回格式化的数据，必须去掉这些空格。这可以使用SQL的去掉右边空格RTRIM()、去掉左边空格LTRIM()、去掉两边空格TRIM()函数来完成；

4.order by 必须在where子句之后

5.rank 在5.7版本中就是关键字了，建议用rk；

6.导入数据库\脚本

source C:/work/testdb.sql

注意：a.路径中不要有中文；
b.用/而不是用windows默认的\；
c.结尾可以不要；号；
d.后缀名不一定要是sql，txt也可以。

7.注释，三种写法

#我是注释
/*我是注释*/
-- 我是注释

注意--后有一个空格

8.select语句汇总

SELECT
    column_1, column_2, ...
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

9.创建表

create table 'students'('id' bigint(20) NOT null auto_increment,'class_id' bigint(20)not null,'name' varchar(100) not null,'gender' varchar(1) not null,'score' int(11) not null,primary key('id'));

这么写会报错，要把引号全部去掉；即

create table students(id bigint(20) not null auto_increment,class_id bigint(20)not null,name varchar(100) not null,gender varchar(1) not null,score int(11) not null,primary key(id));

输入show create table students；会发现系统给列名加了引号

mysql> show create table students;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                           |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

10数据类型decimal(a,b)

a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。
DECIMAL数据类型用于要求非常高的精确度的计算中，这些类型允许指定数值的精确度和计数方法作为选择参数。精确度在这里是指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的个数。例如，语句DECIMAL (5,2)规定了存储的值将不会超过5位数字，开且小数点后面有2位数字。

11子句查询

select a.id as id,b.name as name
from (select id...) a,(select name...) b
where ...

其中a,b为from里面含的子句生成的新表的别名（alias）

12查询结果附加汇总

加上rollup关键字可以得到汇总数目：

select vend_id,count(*) as num_prods from products group by vend_id with rollup;

13自然连接

自然连接是关系R和S在所有公共属性（common attribute）上的等接（Equijoin）。但在得到的结果中公共属性只保留一次，其余删除。自然连接是去掉重复列的等值连接。
自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。对于每种连接类型（除了交叉连接外），均可指定NATURAL。下面给出例子。
例如：

SELECT * FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;

14更新视图

可以先用drop再用create，也可以直接用create or replace view。注意不是用replace view，就是用‘create or replace view’这四个词。
一般应该将视图用于检索，而不是用于更新。
视图可以用show tables来查看，和table混在一起，但是删除时不能用drop table，而是要用drop view。

15储存过程（procedure）

类似于函数。默认的mysql的语句分隔符为';'，mysql命令行实用程序也使用';'作为语句分隔符，而procedure内部本身要用分隔符；，就会与procedure这句话的结尾分隔符；冲突，即一个mysql语句中出现两个；，执行起来会出错，办法就是临时改变命令行实用程序的语句分隔符：

delimiter //

create procedure productpricing()
begin
    select avg(prod_price) as priceaverage
    from products;
end//

delimiter ;

最后还要改回去。
call productpricing();一般就可以执行了。
如果储存过程有传出参数（out）时参数的话：call productpricing(@pricehigh,@pricelow,@priceaverage)是把参数的引用名（变量名）传进去，后面的语句可以使用这些引用名，如：select @pricehigh,@pricelow,@priceaverage;

create procedure ordertotal(in onumber int,out ototal decimal(8,2))begin select sum(item_price*quantity) from orderitems where order_num=onumber into ototal;end //

call ordertotal(20005,@total)；
select @total；

在命令行里写比较长的语句不方便，可以在txt里写，然后导入（source）就可以了。

16触发器（trigger）

《mysql必知必会》P182的下列代码会报错：

create trigger neworder after insert on orders for each row select new.order_num;
ERROR 1415 (0A000): Not allowed to return a result set from a trigger

我们现在用的mysql版本已经不是书中的版本了。现在触发器不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的的过程。通俗的说就是不能在一个触发器中调用其他存储过程或者触发器。
解决办法

create trigger neworder after insert on orders for each row select new.order_num into @neworder;
insert into orders(order_date,cust_id) values(now(),10001);
select @neworder;#显示插入后的变化，注意，插入1后，又插入2，2的值会覆盖掉1，也就是说@neworder中只显示最近的一次插入，如果没有插入就select @neworder，默认是空值。

@neworder是临时变量
下面这些是可以的。

create table archive_orders like orders;
delimiter //
create trigger deleteorder before delete on orders for each row
begin
insert into archive_orders(order_num,order_date,cust_id) values(old.order_num,old.order_date,old.cust_id);
end //
delimiter ;
create trigger updatevendor before update on vendors for each row set new.vend_state=upper(new.vend_state);

二、用python连接数据库

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
首先最基础的就是pip install pymysql了
基础教程可以看：www.runoob.com/python3/python3-mysql.html
这里列几点注意事项：

1.connect

db=pymysql.connect('localhost','user','password','testdb')

第四个参数是可选参数，可以不加，可以在之后的python语句中创建database

2.delimiter

sql='''insert into employee(
        first_name,last_name,age,sex,income)
        values('Hue','Zhang',22,'m',0);'''

这里写sql语句时，句尾可以加;也可以不加，注意要加的我话也是英文状态下的;
但是python中超过3个引号相连会报错，这种情况还是加上;

'''update employee set age=age+1 where sex='m';'''

3.commit

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

执行有更改的代码后一定要commit，不然mysql是不会执行的。

三、学习计划

下面是更新后的计划

12-16更新计划
如果大家有印象笔记账号，欢迎加我，一起共享笔记！我的账号是3275803255@qq.com
简书代码块的符号是三个英文的重音符号```，之前一直以为是三个单引号'''呢o(╥﹏╥)o

立个近期小目标：正在leetcode上做数据库的题，做完了整理一下发上来！