Python:SQL

MYSQL基本使用

以下列出了使用Mysql数据库过程中常用的命令：

USE 数据库名 :选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。
SHOW TABLES: #显示指定数据库的所有表，使用该命令前需要使用 use命令来选择要操作的数据库。
SHOW COLUMNS FROM 数据表: #显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
create database testdb charset “utf8”; #创建一个叫testdb的数据库，且让其支持中文
drop database testdb; #删除数据库
SHOW INDEX FROM tablename 数据表:显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
desc tablename ；显示表结构

启动mysql

sudo /etc/init.d/mysql start/stop/restart

mysql用户设置

使用root用户进入数据库

cmustard@cmustard:~$ mysql -uroot -p
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.09 sec)

添加新的用户

在mysql数据库中添加新的用户并且设置他的权限为select，insert，update
使用grant语法

grant select,update,insert on *.* to test@localhost identified by '123456';

或者可以为指定的数据库添加一个新用户
为test数据库创建一个新用户

grant select,update,insert on test.* to guest@localhost identified by '123456';

用test用户进入数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

创建数据库

mysql> create database notes charset 'utf8';
Query OK, 1 row affected (0.00 sec)

创建一个表

create table stu(
   id INT NOT NULL AUTO_INCREMENT,
   name CHAR(32) NOT NULL,
   age  INT NOT NULL,
   register_date DATE,
   PRIMARY KEY ( stu_id )
);

mysql> show tables;
+-----------------+
| Tables_in_notes |
+-----------------+
| stu             |
+-----------------+
1 row in set (0.00 sec)

mysql> desc stu;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| name           | varchar(32) | NO   |     | NULL    |                |
| age            | int(11)     | NO   |     | NULL    |                |
| resgister_date | date        | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> 

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

数据操作

插入

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

mysql> insert into stu(name,age,resgister_date) values('cmustard',22,current_timestamp);
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from stu;
+----+----------+-----+----------------+
| id | name     | age | resgister_date |
+----+----------+-----+----------------+
|  1 | cmustard |  22 | 2016-10-17     |
+----+----------+-----+----------------+

删除

DELETE FROM table_name [WHERE Clause]

mysql> delete from stu where id=2;     

更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

mysql> update stu set age=10 where id =3;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu;
+----+----------+-----+----------------+
| id | name     | age | resgister_date |
+----+----------+-----+----------------+
|  3 | cmustard |  10 | 2016-10-17     |
+----+----------+-----+----------------+
1 row in set (0.00 sec)

查询

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
你可以使用 LIMIT 属性来设定返回的记录数。

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

从0开始计数

mysql> select * from stu;
+----+----------+-----+----------------+
| id | name     | age | resgister_date |
+----+----------+-----+----------------+
|  3 | cmustard |  10 | 2016-10-17     |
|  4 | v1       |  23 | 2016-10-17     |
|  5 | v3       |  33 | 2016-10-17     |
+----+----------+-----+----------------+
3 rows in set (0.00 sec)

使用 LIMIT 属性来设定返回的记录数
mysql> select * from stu limit 2;
+----+----------+-----+----------------+
| id | name     | age | resgister_date |
+----+----------+-----+----------------+
|  3 | cmustard |  10 | 2016-10-17     |
|  4 | v1       |  23 | 2016-10-17     |
+----+----------+-----+----------------+

limit后面跟的是1条数据，offset后面是从第2条开始读取
mysql> select * from stu limit 1 offset 2;
+----+------+-----+----------------+
| id | name | age | resgister_date |
+----+------+-----+----------------+
|  5 | v3   |  33 | 2016-10-17     |
+----+------+-----+----------------+

limit后面是从第2条开始读，读取1条信息。
mysql> select * from stu limit 2,1
    -> ;
+----+------+-----+----------------+
| id | name | age | resgister_date |
+----+------+-----+----------------+
|  5 | v3   |  33 | 2016-10-17     |
+----+------+-----+----------------+
1 row in set (0.00 sec)

MySQL LIKE 子句

ELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

mysql> select * from stu where name like 'v%'; 以v开头的
+----+------+-----+----------------+
| id | name | age | resgister_date |
+----+------+-----+----------------+
|  4 | v1   |  23 | 2016-10-17     |
|  5 | v3   |  33 | 2016-10-17     |
+----+------+-----+----------------+
2 rows in set (0.00 sec)

MySQL 排序

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

找出name以v开头的行，并按照id的降序排列输出
mysql> select * from stu where name like 'v%' order by id DESC; 
+----+------+-----+----------------+
| id | name | age | resgister_date |
+----+------+-----+----------------+
|  5 | v3   |  33 | 2016-10-17     |
|  4 | v1   |  23 | 2016-10-17     |
+----+------+-----+----------------+
2 rows in set (0.00 sec)

MySQL GROUP BY 语句　　

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> select * from stu;
+--------+------+-----+---------------+
| stu_id | name | age | register_date |
+--------+------+-----+---------------+
|      1 | v1   |  22 | 2016-10-17    |
|      2 | v2   |  24 | 2016-10-17    |
|      3 | v3   |  23 | 2016-10-17    |
|      4 | v2   |  25 | 2016-10-17    |
|      5 | v1   |  25 | 2016-10-17    |
|      6 | v1   |  23 | 2016-10-17    |
+--------+------+-----+---------------+
6 rows in set (0.00 sec)

使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录
mysql> select name,count(*) from stu group by name;
+------+----------+
| name | count(*) |
+------+----------+
| v1   |        3 |
| v2   |        2 |
| v3   |        1 |
+------+----------+
3 rows in set (0.00 sec)


#使用 WITH ROLLUP
mysql> select name,sum(age) as age_count from stu group by name with rollup;
+------+-----------+
| name | age_count |
+------+-----------+
| v1   |        70 |
| v2   |        49 |
| v3   |        23 |
| NULL |       142 |
+------+-----------+
4 rows in set (0.00 sec)

其中记录 NULL 表示所有人的登录次数。<br>
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

MySQL ALTER命令

删除，添加或修改表字段

删除和添加表字段

alter table stu drop register_date; #从stu表删除register_date   字段
alter table stu add phone int(11) not null ＃添加phone字段

修改表结构

可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如，把字段 name 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE tablename MODIFY name CHAR(10);

修改表名

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

主键与外键

mysql> create table class(
    -> id  int not null primary key,
    -> name char(16));
Query OK, 0 rows affected (0.02 sec)


CREATE TABLE `student2` (
  `id` int(11) NOT NULL,
  `name` char(16) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_class_key` (`class_id`),
  CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
)

此时如果class 表中不存在id 1,student表也插入不了，这就叫外键约束
mysql> insert into student2(id,name,class_id) values(1,'alex', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))



mysql> insert into class(id,name) values(1,"linux");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student2(id,name,class_id) values(1,'alex', 1);
Query OK, 1 row affected (0.00 sec)


＃如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的
mysql> delete from class where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`student2`, CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

Mysql连接（left join,right join,inner join,full join）

待续。。。

触发器

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

基本语法

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

BEGIN … END 详解

BEGIN
[statement_list]
END

其中，statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。
而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$： DELIMITER$
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。

数据库表结构

假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;

MySQL 中使用 DECLARE 来定义一局部变量，该变量只能在 BEGIN … END 复合语句中使用，并且应该定义在复合语句的开头.

查看触发器

SHOW TRIGGERS

删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

存储过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;

执行存储过程

call p1()

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

有参数存储过程

-- 创建存储过程
delimiter \\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;

    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;

    set i3 = i3 + 100;

end\\
delimiter ;

-- 执行存储过程
DECLARE @t1 INT default 3;
DECLARE @t2 INT;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

有参数存储过程

删除存储过程

drop procedure proc_name;

执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)

pymysql执行存储过程

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

函数

MySQL中提供了许多内置函数，例如：

CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接（自定义连接符）
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

    CONV(N,from_base,to_base)
        进制转换
        例如：
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

    LOWER(str)
        变小写

    UPPER(str)
        变大写

    LTRIM(str)
        返回字符串 str ，其引导空格字符被删除。
    RTRIM(str)
        返回字符串 str ，结尾空格字符被删去。
    SUBSTRING(str,pos,len)
        获取字符串子序列

    LOCATE(substr,str,pos)
        获取子序列索引位置

    REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL，则返回 NULL 。
    REPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ，顺序和字符顺序相反。
    RIGHT(str,len)
        从字符串str 开始，返回从后边开始len个字符组成的子序列

    SPACE(N)
        返回一个由N空格组成的字符串。

    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

        mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'

部分内置函数

自定义函数

delimiter \\
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

删除函数

drop function func_name;

执行函数

# 获取返回值
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;


# 在查询中使用
select f1(11,nid) ,name from tb2;

事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

事务的存储过程

delimiter \\
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 

  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 

  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 

  -- SUCCESS 
  set p_return_code = 0; 

  END\\
delimiter ;

执行存储过程

DECLARE @i TINYINT;
call p1(@i);
select @i;

索引

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
MySQL中常见索引有：

• 普通索引
• 唯一索引
• 主键索引
• 组合索引