<Mysql必知必会> ---- 笔记

转载自  https://www.jianshu.com/p/294502893128

挺基础的mysql的书籍，基本上都是如何操作的语法。

第1章 了解SQL

主键(primary key)：能够唯一区分表中每一行。

 没有主键，删除更新特定行变的困难，因为没有安全的方法保证只涉及相关的行。虽然并不是必须，但是大多数都需要。

主键通常定义在表的一列上，但这并不是必需的，也可以一起使用 多个列作为主键。在使用多列作为主键时，上述条件必须应用到构成主 键的所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一）

第2章 MySQL简介

DBMS可分为两类：一类为基于共享文件系统的DBMS(Office Access)，另一类为基 于客户机—服务器的DBMS。

第3章 使用MySQL

# 使用指定的数据库
use database_name;

# 显示所有的数据库
show databases;

# 显示所有数据库中的所有表
show tables;

# 显示指定表的所有列信息
show columns from table_name;
desc table_name;
describe table_name;

# 显示指定的数据库或者数据表的创建SQL语句
SHOW CREATE DATABASE database_name;
SHOW CREATE TABLE table_name;

# 显示mysql服务状态信息
show status;

# 显示授予用户的安全权限 
SHOW GRANTS; 

# 显示错误信息 
SHOW ERRORS; 

# 显示警告信息
SHOW WARNINGs;

第4章 检索数据

# 从指定表中查询所有的列的信息
SELECT prod_name FROM products;

# 从指定表中查询指定列的信息
SELECT prod_name FROM products;

# 从指定表中查询多个列的信息
SELECT prod_id, prod_name, prod_price FROM products;

# 去重(会应用于所有的列, 而不是只有第一列) DISTINCT 参数会只留下不相同的行，查看多个列的话会不同的组合也会留下
SELECT DISTINCT vend_id, prod_price FROM products;

# 限制查询返回的行数(LIMIT)，
SELECT prod_name FROM products LIMIT 5;
# 限制查询返回的行数(二个参数中第一个为起始的行数, 第二个参数为要显示的行数)
SELECT prod_name FROM products LIMIT 5, 5;
# 从第0行开始取5行返回,OFFSET是mysql5中新添加的语法，为的是区分LIMIT后面两个数字。
SELECT prod_name FROM products LIMIT 5 OFFSET 0;

# 全限定表名和列名
SELECT table_name.field FROM database_name.table_name;

返回数据的顺序可能是数据被添加到表中的顺序，也可能不是，总之没有排序的时候顺序没有什么意义。

SQL语句不区分大小写，但是SQL开发人员喜欢对所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。

Mysql中第一行为行0而不是行1，使用LIMIT进行限制的时候需要注意

第5章 排序检索数据

# 排序(默认正序)
SELECT prod_name FROM products ORDER BY prod_name;
# 正序（A-Z）
SELECT prod_name FROM products ORDER BY prod_name ASC;
# 逆序
SELECT prod_name FROM products ORDER BY prod_name DESC;

# 多列排序，会按列的顺序排，先排价格，如果有价格相同的行，这些行再按姓名排 
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name; 

# 只对其前面的列名逆序，最后面的prod_name还按照升序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
# 如果想在多个列进行降序排序，必须对每个列指定DESC关键字
 SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name DESC;

# 限制和排序结合，找出指定列的最大值和最小值（LIMIT要在ORDER BY子句之后） 
SELECT prod_price FROM products ORDER BY prod_price LIMIT 1; 
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺 序将会受到MySQL重用回收存储空间的影响。

子句（clause） SQL语句由子句构成，有些子句是必需的，而 有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句。

ORDER BY子句后面可以跟任意字段。

第6章 过滤数据

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

# WHERE 子句操作符 =, !=, <>, <, <=, >, >=, BETWEEN a AND b （包含a和b）
# 大小写不区分（字符串要用小括号括起来）
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';

# 检测指定列是否包含null值
SELECT prod_name FROM products WHERE prod_price IS NULL;
SELECT cust_id FROM customers WHERE cust_email IS NULL;

第19章 插入数据

# 在指定的表中插入一行数据(INSERT语句一般不会产生输出, 但会返回影响的行数)
# 这种方式不保险, 哪里列的顺序改变了就会出错
INSERT INTO Customers
VALUES
    ( NULL, 'Pep E.LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', 'NULL', 'NULL' );

# 指定列名插入, 即使以后列的顺序改变了也不会出错
INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES
    ( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);


# 插入一行, 只插入指定的列值, 其它为默认值或者NULL
# 没有默认值或者不能为NULl时会报错，并且插入不成功
INSERT INTO customers (cust_name) VALUES ('Jack song');

# 降低INSERT语句的优先级
INSERT LOW_PRIORITY INTO customers ( cust_name )
VALUES
    ( 'Jone Li. Main' );

# 一次插入多行数据 方式一(用分号分隔)
INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country )
VALUES
    ( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA' );
INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country )
VALUES
    ( 'M. Martian', '42 Galaxy Way', 'New York', 'Ny', '11213', 'USA' );


# 一次插入多行数据 方式二(多行数用括号包裹，逗号分隔)
# 此技术可以提高数据库处理的性能,因为MySQL用单条 INSERT 语句处理多个插入比使用多条 INSERT语句快
INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country )
VALUES
    ( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA' ),
    ( 'M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA' );


# INSERT和SELECT结合插入其它表检索出来的数据
INSERT INTO customers ( cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) SELECT
cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country 
FROM
    custnew;

 

第21章 创建和操纵表

# 使用SQL创建新表
CREATE TABLE customers1 (
    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;


# 不允许NULL值
CREATE TABLE orders1 (
    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 orderitems1 (
    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;


# 使用函数获得最后自动生成的id
SELECT LAST_INSERT_ID();


# 创建表时给默认值
CREATE TABLE IF NOT EXISTS orderitems2 (
    order_num int NOT NULL,
    order_item int NOT NULL,
    prod_id char(10) NOT NULL,
    quantity int NOT NULL DEFAULT 1,
    item_price decimal(8, 2) NOT NULL,
    PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


# 更新表结构(添加列)
ALTER TABLE vendors ADD vend_phone CHAR(20);

# 更新表结构(删除列)
ALTER TABLE vendors DROP COLUMN vend_phone;

# 更新表(添加外键)
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 SELECT 语句（关于这条语句的详细介绍，请参阅第19章）从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
-- 检验包含所需数据的新表；
-- 重命名旧表（如果确定，可以删除它）；
-- 用旧表原来的名字重命名新表；
-- 根据需要，重新创建触发器、存储过程、索引和外键。


# 删除表
DROP TABLE orderitems2

# 重命名表
RENAME TABLE customers1 TO customer3;

# 同时重命名多个表
RENAME TABLE customer3 TO customers1, orderitems1 TO orderitems2;

 

 持续更新中.......