MySQL创表

Wesley13
• 阅读 522

创建表book

create table t_book(
  id int unsigned auto_increment primary key,
  bookName varchar(255) default null,
  price decimal(6,2) default null,
  author varchar(255) default null,
  bookTypeId int(11) default null
); 
insert into t_book values(1, '追风筝的人', '29.00', '胡赛尼', 1);
insert into t_book values(2, '计算机', '45.00', 'Harold', 2);
insert into t_book values(3, '代码大全', '128.00', '史蒂夫', 2);
insert into t_book values(4, '哥德尔 ', '88.00', '侯世达', 3);
insert into t_book values(5, '呵呵', '4.50', '[中]老子', 4);

create table t_bookType(
  id int(11) auto_increment not null primary key,
  bookTypeName varchar(10) default null
);
insert into t_bookType values(1, '小说类');
insert into t_bookType values(2, '编程类');
insert into t_bookType values(3, '哲学类');
insert into t_bookType values(5, '艺术类');

分页查询: limit

# limit 后的参数:第一个表示 起始位置(下标从0开始),第二个参数是显示的个数(表示本次基于起始位置显示多少个)
select * from t_hero limit 0,3

# 结合where条件:只显示state不为null 的结果
select * from t_hero where state is not null limit 0,20

+----+-----------------------------------------------------+--------+----------------------------------------------------------+------------+----+--------------+
| id | bookName                                            | price  | author                                                   | bookTypeId | id | bookTypeName |
+----+-----------------------------------------------------+--------+----------------------------------------------------------+------------+----+--------------+
|  1 | 追风筝的人                                          |  29.00 | [美] 卡勒德·胡赛尼                                       |          1 |  1 | 小说类       |
|  2 | 计算机程序的构造和解释 : 原书第2版                  |  45.00 | [美]Harold Abelson、Gerald Jay Sussman、Julie Sussman    |          2 |  2 | 编程类       |
|  3 | 代码大全(第2版)                                   | 128.00 | [美] 史蒂夫·迈克康奈尔                                   |          2 |  2 | 编程类       |
|  4 | 哥德尔、艾舍尔、巴赫 : 集异璧之大成                 |  88.00 | [美] 侯世达                                              |          3 |  3 | 哲学类       |
|  5 | 道德经                                              |   4.50 | [中]老子                                                 |          4 |  4 | 艺术类       |
+----+-----------------------------------------------------+--------+----------------------------------------------------------+------------+----+--------------+


- - - - - - - 连接查询: 表示多个表之间的关联查询

1. 内连接:INNER JOIN ON
#连接查询:表示查询所有数据的非NULL部分
#默认是内连接
SELECT *
FROM t_book, t_bookType
WHERE t_book.bookTypeId = t_bookType.id;

select * from t_book, t_bookType where t_book.bookTypeId = t_bookType.id;


# 隐式连接中


# 给每张表起别名用:as
SELECT tb.bookName, tb.price, tby.bookTypeName
FROM t_book as tb, t_bookType as tby
WHERE tb.bookTypeId = tby.id;


# 显式连接
SELECT tb.bookName, tb.price, tby.bookTypeName
FROM t_book as tb
INNER JOIN t_bookType as tby
ON tb.bookTypeId = tby.id;

交集: 属于A集合,也 属于B集合 的数据, A & B
并集: 属于A集合 和 属于B 的数据的全部集合, A | B(重复数据只保留一个)


2. 左连接 : LEFT JOIN    ON
# 两张表连接查询,无论二者是否有数据缺失,一定会把左边的表数据全部显示出来
SELECT *
FROM t_book
LEFT JOIN t_bookType
ON t_book.bookTypeId = t_bookType.id

SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
FROM t_book as tb
LEFT JOIN t_bookType as tby
ON tb.bookTypeId = tby.id


3. 右连接: RIGHT JOIN    ON
# 两张表连接查询,无论二者是否有数据缺失,一定会把右边的表数据全部显示出来
SELECT *
FROM t_book
RIGHT JOIN t_bookType
ON t_book.bookTypeId = t_bookType.id

SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
FROM t_book as tb
RIGHT JOIN t_bookType as tby
ON tb.bookTypeId = tby.id


4. 全连接: UNION :
# 两张表连接查询,无论二者是否有数据缺失,一定会都显示表数据全部显示出来

SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
FROM t_book as tb
LEFT JOIN t_bookType as tby
ON tb.bookTypeId = tby.id
UNION
SELECT tb.bookName, tb.price, tb.author, tby.bookTypeName
FROM t_book as tb
RIGHT JOIN t_bookType as tby
ON tb.bookTypeId = tby.id


SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>30;



- - - -  子查询

1. 子查询: IN
#在  select 语句的返回值的集合里里查找,复合集合里的数据的部分,才会匹配成功。
SELECT *
FROM t_book
WHERE bookTypeId
IN (SELECT id FROM t_bookType);


2. 子查询: NOT IN:
# 和  in相反,不在集合里的数据,才会匹配成功
SELECT *
FROM t_book
WHERE bookTypeId
NOT IN (SELECT id FROM t_bookType);


3. 子查询:EXISTS 返回Bool值,如果子查询语句里查询成功,则返回True,否则返回False
SELECT *
FROM t_book
WHERE EXISTS
(SELECT * FROM t_bookType);

4. 子查询:NOT EXISTS: 对子查询语句返回的结果取反。

SELECT *
FROM t_book
WHERE NOT EXISTS
(SELECT * FROM t_bookType);

自关联 查询

文件名: areas.sql

创建数据库

mysqlcreate database china charset utf8;

切换数据库

mysqluse china;

创建表

create table areas (
  aid int(11) primary key,
  atitle varchar(20) default null,
  pid int(11) default null
);

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

mysqlsource ./Desktop/areas.sql

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

    SELECT city.*   
    FROM areas as city 
    INNER JOIN areas as province
    ON city.pid = province.aid  
    WHERE province.atitle='河北省' "


    SELECT city.*
    FROM areas as city 
    INNER JOIN areas as province   
    ON city.pid = province.aid  
    WHERE province.atitle='内蒙古自治区';

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

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

select dis.*         
FROM areas as dis    
INNER JOIN areas as city 
ON dis.pid=city.aid       
WHERE city.atitle = "保定市" 

事务

事务(Transaction)是并发控制的基本单位。
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,
它是一个不可分割的工作单位。
例如,银行转帐工作:从一个帐号扣款并使另一个帐号增款,
这两个操作要么都执行,要么都不执行。
所以,应该把他们看成一个事务。
事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性

为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景
例如:
A用户和B用户是银行的储户,
现在A要给B转账500元,那么需要做以下几件事:

检查A的账户余额>500元;
A账户扣除500元;
B账户增加500元;
正常的流程走下来,A账户扣了500,B账户加了500,皆大欢喜。
那如果A账户扣了钱之后,系统出故障了呢?
A白白损失了500,而B也没有收到本该属于他的500。
以上的案例中,隐藏着一个前提条件:
A扣钱和B加钱,要么同时成功,要么同时失败。
事务的需求就在于此

事务四大特性(简称ACID)
原子性(Atomicity):
事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行

一致性(Consistency):
几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致

隔离性(Isolation):
事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的

持久性(Durability):
对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障

事务命令
要求:表的引擎类型必须是innodb类型才可以使用事务,这是mysql中表的默认引擎
查看表的创建语句,可以看到engine=innodb

show create table students;
开启事务:
执行insert、update、delete语句时,变更会维护到本地缓存中,而不维护到物理表中
begin;

提交事务:将缓存中的数据变更维护到物理表中
commit;

回滚事务:放弃缓存中变更的数据
rollback;

示例1:提交
步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
终端1
select * from students;
终端2:开启事务,插入数据
begin;
insert into students(sname) values('张飞');
步骤2
终端1:查询数据,发现并没有新增的数据
select * from students;
步骤3
终端2:完成提交
commit;
终端1:查询,发现有新增的数据
select * from students;
示例2:回滚
步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表
终端1
select * from students;
终端2
begin;
insert into students(sname) values('张飞');
步骤2
终端1:查询数据,发现并没有新增的数据
select * from students;
步骤3
终端2:完成回滚
rollback;
终端1:查询数据,发现没有新增的数据
select * from students;

mysql与Python交互

# coding=utf-8
import MySQLdb

def insert_data():
    # 指定一个SQL语句
    # 插入数据
    #sql = "INSERT INTO t_book(bookName, price, author, bookTypeId) VALUES ('三体1', 55.60, '[中]刘慈欣', 8)"
    # 更新数据
    #sql = "UPDATE t_book set price = 100.00 where bookTypeId=8"
    # 删除数据
    sql = "DELETE FROM t_book where bookName like '%三体%'"
    try:
        # 创建MySQL数据库连接对象
        conn = MySQLdb.connect(
            host = "localhost", # 指定数据库所在的ip
            port = 3306,       # 指定数据库的端口号
            user = 'root',      # 指定登录数据库的用户
            passwd = 'mysql', # 登录数据库的用户密码
            db = 'db_book',     # 指定的操作的数据库名称
            charset = 'utf8'    # 指定字符集,和数据库字符集相同
        )

        # 创建数据库操作游标,可以用来执行SQL语句
        cursor = conn.cursor()
        # 使用游标执行SQL语句
        cursor.execute(sql)
        # 因为在MySQL和Python交互,都是事务,所以要用commit()提交事务
        conn.commit()
    except Exception, e:
        print e
        # 如果出错,则回滚之前的操作
        conn.rollback()
    finally:
        # 先关闭游标
        cursor.close()
        # 再关闭数据库连接
        conn.close()
        print "LOG: 执行结束!"


if __name__ == "__main__":
    insert_data()

fetchall()执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回

fetchone()执行查询语句时,获取查询结果集的第一个行数据,返回一个元组

reCount=cur.execute('select * from admin')#返回本次操作影响的记录数  
print reCount  
data=cur.fetchall()#获得  'select * from admin'语句 返回的结果集  
print data  
cur.scroll(0,mode='absolute')#光标回到初始位置0处(绝对定位)  
print cur.fetchone()#此时,光标位置为0;返回位置为0的记录  
print cur.fetchone()#返回位置为1的记录  
cur.scroll(1,mode='absolute')#光标回到1处(绝对定位)  
print cur.fetchone()#返回位置为1的记录  
cur.scroll(-2,mode='relative')#光标回退2步(相对定位)  
print cur.fetchmany(3)#从光标所在的位置(此时,光标位于0处),连续读取3条记录  
cur.scroll(1,mode='relative')#光标前进1步(相对定位)  
print cur.fetchone()#返回光标(位于4处)指向的那一条记录 

# coding=utf-8
import MySQLdb

def main():
    sql = "select *  from t_hero;"
    try:
        conn = MySQLdb.connect(
            host = "localhost", 
            port = 3306,       
            user = 'root',     
            passwd = 'mysql', 
            db = 'db_sanguo',     
            charset = 'utf8'  
            )

        cursor = conn.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        for i in data:
            if list(i)[0] == 6:
                print list(i)[0],list(i)[1]
        # cursor.scroll(6,mode='absolute')
        # cursor.scroll(2,mode='relative')
        # wer = cursor.fetchone()
        # print list(wer)[0],list(wer)[1],list(wer)[2],list(wer)[3]
        # cursor.scroll(-2,mode='relative')
        # print cursor.fetchmany(3)
        # cur.scroll(1,mode='relative')

        
        conn.commit()


    except Exception, e:
        print e
        # 如果出错,则回滚之前的操作
        conn.rollback()
    finally:
        # 先关闭游标
        cursor.close()
        # 再关闭数据库连接
        conn.close()
        print "LOG: 执行结束!"



if __name__ == "__main__":
    main()
 

传参的方式

# coding=utf-8
import MySQLdb

def insert_data():
    try:
        # 创建MySQL数据库连接对象
        conn = MySQLdb.connect(
            host = "localhost", # 指定数据库所在的ip
            port = 3306,       # 指定数据库的端口号
            user = 'root',      # 指定登录数据库的用户
            passwd = 'mysql', # 登录数据库的用户密码
            db = 'db_sanguo',     # 指定的操作的数据库名称
            charset = 'utf8'    # 指定字符集,和数据库字符集相同
        )
        name = raw_input("请输入英雄的大名:")
        age = raw_input("请输入英雄的年龄:")
        wer = [name,age]

        # 创建数据库操作游标,可以用来执行SQL语句
        cursor = conn.cursor()
        # 使用游标执行SQL语句
        qw = cursor.execute("insert into t_hero(name,age) values(%s,%s)",wer)
        print qw
        # 因为在MySQL和Python交互,都是事务,所以要用commit()提交事务
        if qw = 1:
          conn.commit()
    except Exception, e:
        print e
        # 如果出错,则回滚之前的操作
        conn.rollback()
    finally:
        # 先关闭游标
        cursor.close()
        # 再关闭数据库连接
        conn.close()
        print "执行结束!ok"


if __name__ == "__main__":
    insert_data()

私有化

# coding = utf-8

class Test():
  def __init__(self):
      self.num = 10
      self.__string = "hello world"

  def __PrivateFunc(self):
      print self.num
      print self._string

  def PublicFunc(self):
      print self.num
      print self._string

if __name__ == "__main__":
    t = Test()
    #t.PublicFunc()
    #print t.num

    #t.__PrivateFunc()
    print t._string
点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
5个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这