用ORM操作MySQL

发布时间:2019-09-17  栏目:NoSQL  评论:0 Comments

  Linux安装MySQL

  https://www.cnblogs.com/kuaizifeng/p/8884842.html

实现

if __name__ == '__main__':
    main()

  2.日期和岁月管理函数

  注:日期和时间函数依照https://blog.csdn.net/qinshijangshan/article/details/72874667整理

 函数  说明
 NOW()、SYSDATE()、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP 获取当前日期和时间
 CURDATE(), CURRENT_DATE 获取当前日期
 CURTIME(), CURRENT_TIME 获取当前时间
 DATE、YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND、MICROSECOND 获取指定日期和时间的日期、年、季度、月、周、日、小时、分钟、秒、毫秒数
 WEEKOFYEAR、DAYOFYEAR、DAYOFMONTH、DAYOFWEEK、LAST_DAY 获取指定日期和时间的年周索引、年天索引、月天索引、周天索引,最后一天的日期
MONTHNAME、 DAYNAME 获取指定日期和时间的英文月名、英文天名
DATE_ADD、DATE_SUB 指定日期按指定参数进行加减运算
PERIOD_ADD、PERIOD_DIFF 指定日期加、减多少个月
TIMEDIFF 指定日期和时间相差多少个时间
TIMESTAMPDIFF 指定日期/时间或日期时间的差值
TO_DAYS、FROM_DAYS 日期和月数的相互转换函数
TIME_TO_SEC、SEC_TO_TIME 时间和秒数的相互转换函数
STR_TO_DATE、DATE_FORMAT 字符串/日期时间格式转换成新的格式
TIME_FORMAT 时间格式转换你成新的格式
MAKEDATE、MAKETIME 拼凑日期/时间
UNIX_TIMESTAMP、FROM_UNIXTIME 日期时间和unix时间戳的相互转化

   用例:

-- 获取当前日期和时间,日期指的是年月日,时间指的是时分秒
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP;
-- 分别获取当前日期和时间
SELECT CURDATE(), CURRENT_DATE, CURTIME(), CURRENT_TIME;
-- 分别获取日期时间、年、季度、月、周、日、时、分、秒
SELECT DATE(SYSDATE()), YEAR(SYSDATE()), QUARTER(SYSDATE()), MONTH(SYSDATE()), WEEK(SYSDATE()), DAY(SYSDATE()), HOUR(SYSDATE()), MINUTE(SYSDATE()), SECOND(SYSDATE()), MICROSECOND(SYSDATE());
-- 获取指定索引
SELECT WEEKOFYEAR(SYSDATE()), DAYOFYEAR(SYSDATE()), DAYOFMONTH(SYSDATE()), DAYOFWEEK(SYSDATE()), LAST_DAY(SYSDATE());
-- 获取月和周的英文名称
SELECT MONTHNAME(SYSDATE()), DAYNAME(SYSDATE());

-- DATE加,第一个参数是指定的日期和时间,第二个参数是间隔和单位
SELECT DATE_ADD(now(), INTERVAL 1 YEAR), DATE_ADD(now(), INTERVAL 2 MONTH), DATE_ADD(now(), INTERVAL 1000 SECOND);
-- DATE减,与DATE加参数相同
SELECT DATE_SUB(now(), INTERVAL 1 YEAR), DATE_SUB(now(), INTERVAL 2 MONTH), DATE_SUB(now(), INTERVAL 1000 SECOND);
-- 日期的加减运算
SELECT PERIOD_ADD(201808, 2), PERIOD_ADD(1808, 2),PERIOD_ADD(DATE_FORMAT(SYSDATE(), '%Y%m'), 2), PERIOD_DIFF(201808, 201004), PERIOD_DIFF(1808, 1004); 
-- 时间差计算
SELECT TIMEDIFF('2018-08-06', '2018-08-5');-- 不支持日期
SELECT TIMEDIFF('19:00:00', '17:00:00'), TIMEDIFF('2018-08-6 9:30:30', '2018-08-5 17:00:00');
-- 更便捷的日期/时间差值计算,第一个参数是要计算的字段,其值为第三个日期时间减去第二个日期时间
SELECT TIMESTAMPDIFF(DAY, '2018-08-5 17:00:00', '2018-08-8 9:30:30'), TIMESTAMPDIFF(DAY, '2018-08-5', '2018-08-8');
SELECT TIMESTAMPDIFF(SECOND, '17:00:00', '19:30:30');-- 不支持单独时间计算
-- 日期和天数的相互转换
SELECT TO_DAYS(SYSDATE()), TO_DAYS('2018-8-8'), FROM_DAYS(737279);
-- 时间和秒数的相互转换
SELECT TIME_TO_SEC(SYSDATE()), TIME_TO_SEC('12:00:00'), SEC_TO_TIME(43200);
-- 字符串格式化;字符串格式化成日期只能要按照字符串的写法改写成标准日期时间字符串
SELECT STR_TO_DATE('2018.08.6 9:30:30', '%Y.%m.%d %H:%i:%s');
-- 日期时间字符串可以随便更改或获取字段
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%Y%m');-- 获取年月的组合字符串
SELECT DATE_FORMAT('2018-08-06 09:30:30', '%H%i%s');-- 获取时分秒的组合字符串
SELECT DATE_FORMAT(SYSDATE(), '%Y年%m月%d日 %H时哈哈%i分嘿嘿%d秒呵呵');-- 重新格式化
-- 时间格式化只能格式化时间
SELECT TIME_FORMAT('2018-08-06 09:30:30', '%Y年%m月%d日 %H时%i分%d秒');
-- 只对'09:30:30'进行格式化,日期全部为00
SELECT TIME_FORMAT('09:30:30', '%H时%i分%d秒');
-- MAKEDATE根据数字组合成日期(以天数换算),MAKETIME根据数字组合成时间
SELECT MAKEDATE(2018, 9);-- 结果是'2018-01-09'而不是'2018-09-01'
SELECT MAKEDATE(2018, 220);-- 结果是'2018-08-08'
SELECT MAKETIME(19,30,30);-- 与日期相反,支持三个参数拼接而不支持两个参数换算
-- 日期时间和unix时间的相互转换
SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP());

工具的企图

  1.创建表

-- 创建表语法:CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...);
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), salary FLOAT(8,2) UNSIGNED);
-- 查看表结构
DESC users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;

数据库的增、删、改、查

概念叁个新类OrmTest

class OrmTest(object):
    def __init__(self):
        self.session = Session()
    #增加一条记录 
    def add_one(self):
        new_obj = User(
         title='标题',
         content='内容',
         types='网易'
        )
        self.session.add(new_obj)
        self.session.commit()
        return new_obj

    #查询一条记录 
    def get_one(self):
        return self.session.query(User).get(1)
    #查询多条记录
    def get_more(self):
        return self.session.query(User).filter_by(is_valid=True)

    def update_data(self, pk):
        #修改全部记录
        data_list = self.session.query(User).filter_by(is_valid=True)
        for item in data_list:
            #将所有内容的is_valid全改为0
            item.is_valid = 0
            self.session.add(item)
        self.session.commit()
        # new_obj = self.session.query(User).get(pk)    
        # if new_obj:
            # #修改一条记录
            # new_obj.is_valid = 1
            # self.session.add(new_obj)
            # self.session.commit()
            # return True
        # return False

    def delete_data(self, pk):
        #删除记录
        new_obj = self.session.query(User).get(pk)
        self.session.delete(new_obj)
        self.session.commit()

  2.日期和时间项目

类型 大小 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/’838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4

1970-01-01 00:00:00/2038

结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07

YYYYMMDD HHMMSS 混合日期和时间值,时间戳

初叶化连接数据库

#从sqlalchemy中导入
# coding=gbk
from sqlalchemy import create_engine
from sqlalchemy import Column,Integer,String,DateTime,Boolean,Table,MetaData
#建立数据库连接,笔者连接的是数据库news
engine = create_engine('mysql://root:yourpasswd@127.0.0.1:3306/news')
#获取元数据
metadata = MetaData(engine)
#定义表中各项属性
news_0 = Table('news_0',metadata,Column('id',Integer,primary_key=True),Column('title',String(200),nullable=False),Column('content',String(2000),nullable=False),Column('types',String(10),nullable=False),Column('image',String(300), ),Column('author',String(20), ),Column('view_count',Integer),Column('created_at',DateTime),Column('is_valid',Boolean))
metadata.create_all()

   2、数据管理函数

概念主函数

def main():
    obj = OrmTest()

    #添加记录 
    rest = obj.add_one()
    print(rest.id)

    #查询一条记录
    # rest = obj.get_one()
    # if rest:
        # print('ID:{0}=>{1}'.format(rest.id, rest.title))
    # else:
        # print('Not exist.')

    #查询多条记录 
    # rest = obj.get_more()
    # print(rest.count())
    # for new_obj in rest:
        # print('ID:{0}=>{1}'.format(new_obj.id, new_obj.title))

    #修改记录
    # print(obj.update_data(1))

    #删除记录
    #print(obj.delete_data(1))

  4.修改表结构

创办会话

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

  3.数据库系统和数据库管理类别:

  数据库系统由数据库、数据库管理种类、应用开采工具构成。

  数据库管理种类(DataBase Management System,
DBMS)是用来定义、管理和护卫数据的软件。它是数据库系统的基本点组成都部队分。数据库处理种类经过SQL语言来保管数据库中的数据。

ORM Object Relactional Mapping对象关系映射。
SQLAIchemy是两个数额库ORM框架。

  2.数据库范式:

  数据库范式即数据库应该遵从的条条框框。近期关周全据库最常用的七种范式分别是:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、BCN范式(BCNF)。

  第一范式:无重复的域。即数据库表的每一列都以不可分割的原子数据项,并非晤面、数组、记录等非原子数据项。

  第二范式:数据库表中的具备列都必需依据于主键,那意味贰个表只描述一件业务。

  第三范式:表中的每一列只与主键直接相关并非直接相关。

  BCN范式:Boyce-Codd,不允许出现有主键的一片段被主键另一片段照旧另外部分决定。即二个表中只好有贰个主键。

  举个例子(摘自其余博客)表明BCN:

  假若商旅管理涉及表为StorehouseManage(饭店ID, 存款和储蓄物品ID, 管理员ID,
数量),且有多少个大班只在三个仓库工作;三个货仓能够储存两种物料。这些数据库表中留存如下决定涉及:

    (饭店ID, 存款和储蓄物品ID) →(管理员ID, 数量)

    (助理馆员ID, 存款和储蓄货品ID) → (宾馆ID, 数量)

  所以,(仓库ID, 存款和储蓄货色ID)和(管理员ID,
存款和储蓄货色ID)都以StorehouseManage的候选关键字,表中的独一非关键字段为多少,它是相符第三范式的。可是,由于存在如下决定涉及:

    (仓库ID) → (管理员ID)

    (管理员ID) → (仓库ID)

  即存在重大字段决定第一字段的事态,所以其不合乎BCNF范式。

申明映射

当使用ORM时,配置进程首先描述大家要拍卖的多寡库表,然后通过定义大家团结互助的类映射到这几个表。在今世的SQLAlchemy中,那多个职分平日一同施行,使用称为Declarative的种类,它同意大家创设富含指令的类来说述它们将在映射到的莫过于数据库表。

from sqlalchemy.ext.declarative import declarative_base
#创建对象基类
Base = declarative_base(engine)

现今大家有了三个“基础”,大家能够依据它定义大肆数量的映射类。大家将从一个名称为news_0的表初阶,它将使用我们的应用程序存款和储蓄最后顾客的记录。被调用的新类News将是我们映射此表的类。在类中,大家定义了要映射到的表的详细消息,包含id,标题,内容,体系等:

class User(Base):
    #表的名称
    __tablename__='news_0'
    #表的结构
    id = Column(Integer,primary_key=True)
    title = Column(String(200),nullable=False)
    content = Column(String(2000),nullable=False)
    types = Column(String(10),nullable=False)
    image = Column(String(300), )
    author = Column(String(20), )
    view_count = Column(Integer)
    created_at = Column(DateTime)
    is_valid = Column(Boolean)

  2.约束标准

  约束是为着保障数据的完整性和一致性,约束类型包涵:

 键名  类型
PRIMARY KEY 主键约束
UNIQUE KEY 唯一约束
NOT NULL 非空约束
UNSIGNED 无符号约束
DEFAULT 默认约束
FOREIGN KEY 外键约束

 

  1.询问关键字 SELECT FROM

-- 查询单列
SELECT prod_name FROM products;
-- 查询多列
SELECT prod_id, prod_name, prod_price FROM products;
-- 查询所有列
SELECT * FROM products;

  常见数据库

  商业数据库:大篆的Oracle、IBM的DB2、微软的Access和SQL
Server。开源数据库:PostgreSQL、MySQL。

四、MySQL查询操作

  3.过滤关键字 where + 操作符

  where子句操作符表:

 操作符  说明
 =  等于
 <>  不等于
 !=  不等于
 <  小于
 <=  小于等于
 !<  不小于
 >  大于
 >=  大于等于
 !>  不大于
BETWEEN 在指定的两个值之间
IS NULL 为null值
AND、OR、NOT、IN 组合查询
LIKE 通配符过滤

  用例:

-- 匹配查询
SELECT prod_price, prod_name FROM products where prod_price = 3.49;
-- 不匹配查询
SELECT prod_price, prod_name FROM products where prod_price <> 10;
-- 范围查询
SELECT prod_price, prod_name FROM products where prod_price BETWEEN 5 AND 10; 
-- 空值查询
SELECT prod_name FROM products where prod_price IS NULL;
-- 组合查询AND
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id='DLL01' AND prod_price <= 4;
-- 组合查询OR
SELECT prod_name, prod_price FROM products WHERE vend_id='DLL01' OR vend_id = 'BRS01';
-- 组合查询AND和OR
SELECT prod_name, prod_price FROM products WHERE vend_id='DLL01' OR vend_id = 'BRS01' AND prod_price <= 4;
-- 组合查询IN
SELECT prod_name, prod_price FROM products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;
-- 组合查询NOT
SELECT prod_name, prod_price FROM products WHERE vend_id NOT vend_id='DLL01';
-- 通配符过滤
SELECT prod_name FROM products WHERE prod_name LIKE '%TNT%';

  1、查询数据

  2.更新和删除数据 UPDARE DEL

  UPDATE能够:更新表中一定行,更新表中全体行。在未曾where子句时,UPDATE会更新具备记录。

-- UPDATE总以要更新的表的名字开始,以SET指明一个或多个要更新的字段,以WHERE指定要更新的记录
UPDATE customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '10005';
UPDATE customers SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyloand.com' WHERE cust_id = '10006';
-- 设置NULL来删除某个列的值
UPDATE customers SET cust_email = NULL WHERE cust_id = '10005';

-- DELETE是删除列
DELETE FROM customers WHERE cust_id = '10006';

  DELETE用于按行删除记录,它自个儿不会修改表结构。

  注:在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT举办测量检验,以管教它过滤的是的确要删减的记录。

  2.修改列定义和列名

-- 修改列名,要重新声明列的数据类型和约束条件
ALTER TABLE users CHANGE name username varchar(10) NOT NULL;
-- 修改列定义
ALTER TABLE users MODIFY id SMALLINT NOT NULL FIRST;
-- 修改表名
ALTER TABLE users RENAME TO tb;

 

三、MySQL表操作

  1.GROUP BY

  - GROUP BY子句能够分包自由数目标列。

  - GROUP BY会在最后分明的分组上拓宽集中。

  - GROUP
BY子句列出的每种列都必需是研究列或有效的表达式(但不能够是聚合函数)。

  - 除聚合函数外,SELECT语句中的种种列都必得在GROUP BY子句中提交。

  -
若是分组列中享有NULL值,则NULL将作为一个分组再次来到;要是列中有多行NULL值,它们将分成一组。

  - GROUP BY子句必须出现在WHERE子句字后,O传祺DEOdyssey BY子句在此以前。

  一般在利用GROUP BY子句时,应该也给出O中华VDER
BY子句,以有限支撑数据正确排序。

  1.大背头项目标积攒和范围

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

  3.数值管理函数

函数  说明
ABS() 返回数字表达式的绝对值。
ACOS() 返回数字表达式的反余弦值。如果值是不在范围-1到1,则返回NULL。
ASIN() 返回数字表达式的反正弦。返回NULL,如果值不在范围-1到1
ATAN() 返回数字表达式的反正切。
ATAN2() 返回传递给它的两个变量的反正切。
BIT_AND() 返回按位AND运算表达中的所有位。
BIT_COUNT() 返回传递给它的二进制值的字符串表示。
BIT_OR() 返回传递表达的所有位的位或。
CEIL() 返回最小的整数值但不能比传递的数字表达式小
CEILING() 返回最小的整数值但不能比传递的数字表达式小
CONV() 表达式从一个基数到另一个基数转换的数字。
COS() 返回传递数字表达式的余弦值。数字表达式应该用弧度表示。
COT() 返回传递数字表达式的余切。
DEGREES() 返回数字表达式从弧度转换为度。
EXP() 返回数值表达式的自然对数(E)为基数的幂。
FLOOR() 返回最大整数值但不能大于通过表达式数值。
FORMAT() 返回数字表达式舍入到小数位数。
GREATEST() 返回输入表达式的最大值。
INTERVAL() 需要多个表达式exp1, exp2和exp3等..如果为exp1小于exp2返回0,如果为exp1小于exp3返回1等。
LEAST() 给两个或两个以上时,返回所有输入的最小值。
LOG() 返回通过数字表达式的自然对数。
LOG10() 返回传递表达的基数为10对数的数值。
MOD() 返回表达式由另一个表达式除以剩余部分。
OCT() 返回通过数字表达式的八进制值的字符串表示。如果传递值为NULL,返回NULL。
PI() 返回圆周率的值
POW() 返回一个表达式到另一个表达的次方值
POWER() 返回一个表达式到另一个表达的次方值
RADIANS() 返回传递表达从度转换为弧度值
ROUND() 返回数字表达式四舍五入到整数。可用于舍入表达式为小数点数值
SIN() 返回给定的数字表达的正弦值(弧度)
SQRT() 返回数字表达式的非负平方根
STD() 返回数字表达式的标准偏差
STDDEV() 返回数字表达式的标准偏差
TAN() 返回以弧度表示数值表达式的正切值。
TRUNCATE() 返回exp1小数位数字截断到exp2。如果exp2为0,则结果将没有小数点。

  4.SQL语言:

  SQL(Structured Query Language)语言的全称是结构化查询语言。它归纳:

    - 数据库定义语言(Data Definition Language, DDL)

    - 数据操作语言(Data Manipulation Language, DML)

    - 数据调整语言(Data Control Language, DCL)

一、基本概念

  4、关键字顺序

关键字(子句) 说明
 是否必须使用
 SELECT  要返回的列或表达式  是
 FROM  从中检索数据的表  仅在从表选择数据时使用
 WHERE  行级过滤  否
 GROUP BY  分组说明  仅在按组计算聚集时使用
 HAVING  组级过滤  否
 ORDER BY  指定排序字段和熟顺序  否

   在达成SQL语句时,通用格式为:

SELECT *columns* FROM *tables* WHERE *condition* GROUP BY *columns* HAVING *condition* ORDER BY *columns* LIMIT *start*, *offset*;

  实际实践的相继为:

FROM *tables*
WHERE *condition*
GROUP BY *columns*
HAVING *condition*
SELECT *columns*
ORDER BY *columns*
LIMIT *start*, *offset*

# 注:数据表来自https://www.cnblogs.com/sanjun/p/8274469.html。

图片 1图片 2

CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;

CREATE TABLE products
(
prod_id char(10) NOT NULL,
vend_id int NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL ,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);


INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');

INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

数据表

 

  2.HAVING

  HAVING援助具有WHERE操作符。它与WHERE最主要的区分是,HAVING对GROUP
BY分组后的数目开展过滤,而where在GROUP BY分组前集体新表时打开过滤。

  用例:

-- GROUP BY 与 HAVINNG
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
-- GROUP BY 与 ORDER BY
SELECT order_num, COUNT(*) AS items FROM orderitems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

  4.聚合函数

函数  说明
 AVG  返回某列的平均值
 COUNT  返回某类的行数
 MAX  返回某列的最大值
 MIN  返回某列的最小值
 SUM  返回某列值之和

  用例:

-- AVG
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;-- 计算唯一值列表平均值
-- COUNT
SELECT COUNT(*) AS num_cust FROM customers;-- 对所有行技数
SELECT COUNT(cust_email) as num_cust FROM customers;-- 只对具有电子邮寄地址的客户计数(除去null)
-- SUM
SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;
-- 组合
SELECT COUNT(*) AS nun_items, MIN(prod_price) AS price_min, MAX(prod_price) AS pric_max, AVG(prod_price) AS price_ag FROM products;

  3、分组关键字 GROUP BY 和HAVING

  1.插入数据 INSERT INTO

-- 插入一条新的数据
/* INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('10006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL); */
-- 按表列字段的顺序插入数据时,列字段可省略
INSERT INTO customers VALUES('10006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
-- 将一张表插入到原来的表
/* INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)  SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email FROM cust_new WHERE cust_id NOT IN (SELECT cust_id FROM customers); */
-- 复制表
CREATE TABLE custcopy AS SELECT * FROM customers;

  注意:

  - 任何SELECT选项和子句都足以采取,包蕴WHERE和GROUP BY。

  - 能够统一多少个表实践插入数据操作。

  - 不管从多少个表中检索数据,数据都不得不插入到单个表中。

  版本分类

  依据操作系统:Windows版,UNIX版,Linux版,MacOS版;根据开辟处境:Alpha、Beta、Gamma与Available(GA)。

  阿尔法暗中提示那是贰个以彰显新特征为指标的版本,存在非常多的不平稳因素,还有可能会向代码中加多新新本性

  beta
今后的beta版、宣布版或制品公布中,全数API、外界可视结商谈SQL命令列均不再改换,不再向代码中增添影响代码稳固性的新特点。Gamma比Beta版更加高档。

  GA如果未有后缀,则暗中表示那是三个大多数气象下可用版本大概是成品版本。.
GA releases则是稳定版。

  优势:MySQL开放源码、跨平台性、价格优势、作用壮大且使用方便。

   3.开立表及约束标准

  3.字符串类型

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYTEXT 0-255字节 短文本字符串
TEXT 0-65 535字节 长文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
TINYBLOB  0-255字节  不超过 255 个字符的二进制字符串
BLOB 0-65 535字节 二进制形式的长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据

二、MySQL数据类型(摘自新手教程:http://www.runoob.com/mysql/mysql-data-types.html)

  MySQL支持多样类型,大约可以分为三类:数值、日期/时间和字符串(字符)类型。

  2.排序关键字 OENVISIONDE宝马7系 BY

  查询的数目假诺不排序,一般是以它在底层表中现身的次第展现。假诺不分明规定排序依次,则不应当若是检索出的多少的相继有含义。

-- 单排
SELECT prod_name FROM products ORDER BY prod_name;
-- 多排
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
-- 按列位置排
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3; 注,它只能根据已选择字段的相对位置排序
-- 指定排序方向
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

  5.MySQL数据库版本和优势:

  1.增加和删除列

-- 修改数据表
-- 添加列语法: ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
ALTER TABLE users ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10 AFTER name;
-- 删除列语法: ALTER TABLE tbl_name DROP [COLUMN] col_name
ALTER TABLE users DROP age;
-- 混合操作
ALTER TABLE users ADD gender VARCHAR(10) NOT NULL DEFAULT "male", ADD address VARCHAR(32) NOT NULL,DROP salary;

  1.数据库:

  数据库(DataBase)正是叁个囤积数据的货仓,为了便于数据的存放和管制,它将数据依据一定的法规存款和储蓄在磁盘上。通过数据库处理体系,能够使得的团伙和管理存储在数据库中的数据。数据库是数码管理软件。数据存款和储蓄分为多少个级次:人工管理阶段、文件系统阶段和数据库系统阶段。

  1.文本管理函数

 函数 说明
 LEFT 返回字符串左边的字符
 LENGTH 返回字符串的长度
 LOWER  返回字符串的小写
 LTRIM 去掉字符串左边的空格
 RIGHT 返回字符串右边的字符
 RTRIM 去掉字符串右边的空格
UPPER 返回字符串的大写

   - 用例: 

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

留下评论

网站地图xml地图