MySQL创表

创建表book

1create table t_book(
2  id int unsigned auto_increment primary key,
3  bookName varchar(255) default null,
4  price decimal(6,2) default null,
5  author varchar(255) default null,
6  bookTypeId int(11) default null
7); 
8insert into t_book values(1, '追风筝的人', '29.00', '胡赛尼', 1);
9insert into t_book values(2, '计算机', '45.00', 'Harold', 2);
10insert into t_book values(3, '代码大全', '128.00', '史蒂夫', 2);
11insert into t_book values(4, '哥德尔 ', '88.00', '侯世达', 3);
12insert into t_book values(5, '呵呵', '4.50', '[中]老子', 4);
13
14create table t_bookType(
15  id int(11) auto_increment not null primary key,
16  bookTypeName varchar(10) default null
17);
18insert into t_bookType values(1, '小说类');
19insert into t_bookType values(2, '编程类');
20insert into t_bookType values(3, '哲学类');
21insert into t_bookType values(5, '艺术类');

分页查询： limit

1# limit 后的参数：第一个表示 起始位置(下标从0开始)，第二个参数是显示的个数(表示本次基于起始位置显示多少个)
2select * from t_hero limit 0,3
3
4# 结合where条件：只显示state不为null 的结果
5select * from t_hero where state is not null limit 0,20
6
7+----+-----------------------------------------------------+--------+----------------------------------------------------------+------------+----+--------------+
8| id | bookName                                            | price  | author                                                   | bookTypeId | id | bookTypeName |
9+----+-----------------------------------------------------+--------+----------------------------------------------------------+------------+----+--------------+
10|  1 | 追风筝的人                                          |  29.00 | [美] 卡勒德·胡赛尼                                       |          1 |  1 | 小说类       |
11|  2 | 计算机程序的构造和解释 : 原书第2版                  |  45.00 | [美]Harold Abelson、Gerald Jay Sussman、Julie Sussman    |          2 |  2 | 编程类       |
12|  3 | 代码大全（第2版）                                   | 128.00 | [美] 史蒂夫·迈克康奈尔                                   |          2 |  2 | 编程类       |
13|  4 | 哥德尔、艾舍尔、巴赫 : 集异璧之大成                 |  88.00 | [美] 侯世达                                              |          3 |  3 | 哲学类       |
14|  5 | 道德经                                              |   4.50 | [中]老子                                                 |          4 |  4 | 艺术类       |
15+----+-----------------------------------------------------+--------+----------------------------------------------------------+------------+----+--------------+
16
17
18- - - - - - - 连接查询： 表示多个表之间的关联查询
19
201. 内连接：INNER JOIN ON
21#连接查询：表示查询所有数据的非NULL部分
22#默认是内连接
23SELECT *
24FROM t_book, t_bookType
25WHERE t_book.bookTypeId = t_bookType.id;
26
27select * from t_book, t_bookType where t_book.bookTypeId = t_bookType.id;
28
29
30# 隐式连接中
31
32
33# 给每张表起别名用：as
34SELECT tb.bookName, tb.price, tby.bookTypeName
35FROM t_book as tb, t_bookType as tby
36WHERE tb.bookTypeId = tby.id;
37
38
39# 显式连接
40SELECT tb.bookName, tb.price, tby.bookTypeName
41FROM t_book as tb
42INNER JOIN t_bookType as tby
43ON tb.bookTypeId = tby.id;
44
45交集： 属于A集合，也 属于B集合 的数据， A & B
46并集： 属于A集合 和 属于B 的数据的全部集合， A | B（重复数据只保留一个）
47
48
492. 左连接 ： LEFT JOIN    ON
50# 两张表连接查询，无论二者是否有数据缺失，一定会把左边的表数据全部显示出来
51SELECT *
52FROM t_book
53LEFT JOIN t_bookType
54ON t_book.bookTypeId = t_bookType.id
55
56SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
57FROM t_book as tb
58LEFT JOIN t_bookType as tby
59ON tb.bookTypeId = tby.id
60
61
623. 右连接： RIGHT JOIN    ON
63# 两张表连接查询，无论二者是否有数据缺失，一定会把右边的表数据全部显示出来
64SELECT *
65FROM t_book
66RIGHT JOIN t_bookType
67ON t_book.bookTypeId = t_bookType.id
68
69SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
70FROM t_book as tb
71RIGHT JOIN t_bookType as tby
72ON tb.bookTypeId = tby.id
73
74
754. 全连接： UNION ：
76# 两张表连接查询，无论二者是否有数据缺失，一定会都显示表数据全部显示出来
77
78SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
79FROM t_book as tb
80LEFT JOIN t_bookType as tby
81ON tb.bookTypeId = tby.id
82UNION
83SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
84FROM t_book as tb
85RIGHT JOIN t_bookType as tby
86ON tb.bookTypeId = tby.id
87
88
89SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>30;
90
91
92
93- - - -  子查询
94
951. 子查询： IN
96#在  select 语句的返回值的集合里里查找，复合集合里的数据的部分，才会匹配成功。
97SELECT *
98FROM t_book
99WHERE bookTypeId
100IN (SELECT id FROM t_bookType);
101
102
1032. 子查询： NOT IN：
104#　和  in相反，不在集合里的数据，才会匹配成功
105SELECT *
106FROM t_book
107WHERE bookTypeId
108NOT IN (SELECT id FROM t_bookType);
109
110
1113. 子查询：EXISTS 返回Bool值，如果子查询语句里查询成功，则返回True，否则返回False
112SELECT *
113FROM t_book
114WHERE EXISTS
115(SELECT * FROM t_bookType);
116
1174. 子查询：NOT EXISTS： 对子查询语句返回的结果取反。
118
119SELECT *
120FROM t_book
121WHERE NOT EXISTS
122(SELECT * FROM t_bookType);

自关联 查询

文件名： areas.sql

创建数据库

mysqlcreate database china charset utf8;

切换数据库

mysqluse china;

创建表

1create table areas (
2  aid int(11) primary key,
3  atitle varchar(20) default null,
4  pid int(11) default null
5);

执行指定sql文件的数据插入

mysqlsource ./Desktop/areas.sql

查找 河北省 下面的所有城市

1    SELECT city.*   
2    FROM areas as city 
3    INNER JOIN areas as province
4    ON city.pid = province.aid  
5    WHERE province.atitle='河北省' "
6
7
8    SELECT city.*
9    FROM areas as city 
10    INNER JOIN areas as province   
11    ON city.pid = province.aid  
12    WHERE province.atitle='内蒙古自治区';

查找 保定市 下面的所有区县

1select dis.*          
2FROM areas as dis     # 别名1：表示区县
3INNER JOIN areas as city  # 别名2：表示城市
4ON dis.pid=city.aid        #内连接条件：区县的pid 和 城市 aid 相同
5WHERE city.atitle = "保定市"  # 查询结果条件：城市名为 "保定市"
6
7select dis.*         
8FROM areas as dis    
9INNER JOIN areas as city 
10ON dis.pid=city.aid       
11WHERE city.atitle = "保定市" 

事务

1事务(Transaction)是并发控制的基本单位。
2所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，
3它是一个不可分割的工作单位。
4例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，
5这两个操作要么都执行，要么都不执行。
6所以，应该把他们看成一个事务。
7事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性
8
9为什么要有事务
10事务广泛的运用于订单系统、银行系统等多种场景
11例如：
12A用户和B用户是银行的储户，
13现在A要给B转账500元，那么需要做以下几件事：
14
15检查A的账户余额>500元；
16A账户扣除500元；
17B账户增加500元；
18正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。
19那如果A账户扣了钱之后，系统出故障了呢？
20A白白损失了500，而B也没有收到本该属于他的500。
21以上的案例中，隐藏着一个前提条件：
22A扣钱和B加钱，要么同时成功，要么同时失败。
23事务的需求就在于此
24
25事务四大特性(简称ACID)
26原子性(Atomicity)：
27事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
28
29一致性(Consistency)：
30几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
31
32隔离性(Isolation)：
33事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
34
35持久性(Durability)：
36对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
37
38事务命令
39要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql中表的默认引擎
40查看表的创建语句，可以看到engine=innodb
41
42show create table students;
43开启事务：
44执行insert、update、delete语句时，变更会维护到本地缓存中，而不维护到物理表中
45begin;
46
47提交事务：将缓存中的数据变更维护到物理表中
48commit;
49
50回滚事务：放弃缓存中变更的数据
51rollback;
52
53示例1：提交
54步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表
55终端1
56select * from students;
57终端2：开启事务，插入数据
58begin;
59insert into students(sname) values('张飞');
60步骤2
61终端1：查询数据，发现并没有新增的数据
62select * from students;
63步骤3
64终端2：完成提交
65commit;
66终端1：查询，发现有新增的数据
67select * from students;
68示例2：回滚
69步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表
70终端1
71select * from students;
72终端2
73begin;
74insert into students(sname) values('张飞');
75步骤2
76终端1：查询数据，发现并没有新增的数据
77select * from students;
78步骤3
79终端2：完成回滚
80rollback;
81终端1：查询数据，发现没有新增的数据
82select * from students;

mysql与Python交互

1# coding=utf-8
2import MySQLdb
3
4def insert_data():
5    # 指定一个SQL语句
6    # 插入数据
7    #sql = "INSERT INTO t_book(bookName, price, author, bookTypeId) VALUES ('三体1', 55.60, '[中]刘慈欣', 8)"
8    # 更新数据
9    #sql = "UPDATE t_book set price = 100.00 where bookTypeId=8"
10    # 删除数据
11    sql = "DELETE FROM t_book where bookName like '%三体%'"
12    try:
13        # 创建MySQL数据库连接对象
14        conn = MySQLdb.connect(
15            host = "localhost", # 指定数据库所在的ip
16            port = 3306,       # 指定数据库的端口号
17            user = 'root',      # 指定登录数据库的用户
18            passwd = 'mysql', # 登录数据库的用户密码
19            db = 'db_book',     # 指定的操作的数据库名称
20            charset = 'utf8'    # 指定字符集，和数据库字符集相同
21        )
22
23        # 创建数据库操作游标，可以用来执行SQL语句
24        cursor = conn.cursor()
25        # 使用游标执行SQL语句
26        cursor.execute(sql)
27        # 因为在MySQL和Python交互，都是事务，所以要用commit()提交事务
28        conn.commit()
29    except Exception, e:
30        print e
31        # 如果出错，则回滚之前的操作
32        conn.rollback()
33    finally:
34        # 先关闭游标
35        cursor.close()
36        # 再关闭数据库连接
37        conn.close()
38        print "LOG: 执行结束!"
39
40
41if __name__ == "__main__":
42    insert_data()
43
44fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
45
46fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
47
48reCount=cur.execute('select * from admin')#返回本次操作影响的记录数  
49print reCount  
50data=cur.fetchall()#获得  'select * from admin'语句 返回的结果集  
51print data  
52cur.scroll(0,mode='absolute')#光标回到初始位置0处(绝对定位)  
53print cur.fetchone()#此时，光标位置为0;返回位置为0的记录  
54print cur.fetchone()#返回位置为1的记录  
55cur.scroll(1,mode='absolute')#光标回到1处(绝对定位)  
56print cur.fetchone()#返回位置为1的记录  
57cur.scroll(-2,mode='relative')#光标回退2步(相对定位)  
58print cur.fetchmany(3)#从光标所在的位置(此时，光标位于0处)，连续读取3条记录  
59cur.scroll(1,mode='relative')#光标前进1步(相对定位)  
60print cur.fetchone()#返回光标(位于4处)指向的那一条记录 
61
62# coding=utf-8
63import MySQLdb
64
65def main():
66    sql = "select *  from t_hero;"
67    try:
68        conn = MySQLdb.connect(
69            host = "localhost", 
70            port = 3306,       
71            user = 'root',     
72            passwd = 'mysql', 
73            db = 'db_sanguo',     
74            charset = 'utf8'  
75            )
76
77        cursor = conn.cursor()
78        cursor.execute(sql)
79        data = cursor.fetchall()
80        for i in data:
81            if list(i)[0] == 6:
82                print list(i)[0],list(i)[1]
83        # cursor.scroll(6,mode='absolute')
84        # cursor.scroll(2,mode='relative')
85        # wer = cursor.fetchone()
86        # print list(wer)[0],list(wer)[1],list(wer)[2],list(wer)[3]
87        # cursor.scroll(-2,mode='relative')
88        # print cursor.fetchmany(3)
89        # cur.scroll(1,mode='relative')
90
91        
92        conn.commit()
93
94
95    except Exception, e:
96        print e
97        # 如果出错，则回滚之前的操作
98        conn.rollback()
99    finally:
100        # 先关闭游标
101        cursor.close()
102        # 再关闭数据库连接
103        conn.close()
104        print "LOG: 执行结束!"
105
106
107
108if __name__ == "__main__":
109    main()
110 

传参的方式

1# coding=utf-8
2import MySQLdb
3
4def insert_data():
5    try:
6        # 创建MySQL数据库连接对象
7        conn = MySQLdb.connect(
8            host = "localhost", # 指定数据库所在的ip
9            port = 3306,       # 指定数据库的端口号
10            user = 'root',      # 指定登录数据库的用户
11            passwd = 'mysql', # 登录数据库的用户密码
12            db = 'db_sanguo',     # 指定的操作的数据库名称
13            charset = 'utf8'    # 指定字符集，和数据库字符集相同
14        )
15        name = raw_input("请输入英雄的大名:")
16        age = raw_input("请输入英雄的年龄:")
17        wer = [name,age]
18
19        # 创建数据库操作游标，可以用来执行SQL语句
20        cursor = conn.cursor()
21        # 使用游标执行SQL语句
22        qw = cursor.execute("insert into t_hero(name,age) values(%s,%s)",wer)
23        print qw
24        # 因为在MySQL和Python交互，都是事务，所以要用commit()提交事务
25        if qw = 1:
26          conn.commit()
27    except Exception, e:
28        print e
29        # 如果出错，则回滚之前的操作
30        conn.rollback()
31    finally:
32        # 先关闭游标
33        cursor.close()
34        # 再关闭数据库连接
35        conn.close()
36        print "执行结束!ok"
37
38
39if __name__ == "__main__":
40    insert_data()

私有化

1# coding = utf-8
2
3class Test():
4  def __init__(self):
5      self.num = 10
6      self.__string = "hello world"
7
8  def __PrivateFunc(self):
9      print self.num
10      print self._string
11
12  def PublicFunc(self):
13      print self.num
14      print self._string
15
16if __name__ == "__main__":
17    t = Test()
18    #t.PublicFunc()
19    #print t.num
20
21    #t.__PrivateFunc()
22    print t._string