MySQL必知必会学习笔记

我不是女神ヾ 2021-11-15 12:24 567阅读 0赞

--------------------

**说明：**

*  数据库是引用MySQL必知必会的创建代码
 *  先通过代码新建一个新的数据库，然后逐步通过代码来理解每一个指令的内涵！！

--------------------

### 文章目录 ###

*   *  1. 创建数据库
     *  2. 创建表
     *  3. 定义外键
     *  4. 插入数据
     *  5. 学习笔记

## 1. 创建数据库 ##

# 创建数据库
    CREATE DATABASE MySqlMustToKnow;

## 2. 创建表 ##

# 创建表customers
    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;
    
    
    # 表orderitems
    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;
    
    
    # 创建表orders
    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;
    
    
    # 创建表products
    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;
    
    
    # 创建表vendors
    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;
    
    
    # 创建表productnotes
    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;

## 3. 定义外键 ##

`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);`

## 4. 插入数据 ##

# 插入Populate customers table
    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');
    
    
    	# Populate vendors table
    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');
    
    
    	# Populate products table
    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');
    
    
    	# Populate orders table
    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);
    
    
    	# Populate orderitems table
    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);
    
    	# Populate productnotes table
    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.'
    );

## 5. 学习笔记 ##

# 1.检索数据
    	# 1. 检索单个列、多个列、所有列
    	SELECT prod_name FROM products;
    	SELECT prod_id,prod_name FROM products;
    	SELECT * FROM products;
    	# 2.检索不同行 distinct
    	SELECT vend_id FROM products;
    	SELECT DISTINCT vend_id FROM products;
    	# 3. 限制结果 limit
    	SELECT prod_name FROM products;
    	SELECT prod_name FROM products LIMIT 1 , 3;
    	# 4. 完全限定名 
    	SELECT prod_name FROM products;
    	SELECT products.prod_name FROM products;
    	
    # 2. 排序检索数据
    	# 1. 排序order by 
    	SELECT prod_name FROM products;
    		# 正序
    	SELECT prod_name FROM products ORDER BY prod_name;
    		# 反序
    	SELECT prod_name FROM products ORDER BY prod_name DESC;
    # 3. 过滤数据
    	# 1. where
    	SELECT prod_name,prod_price FROM products WHERE prod_price = 2.50;
    	# > < <> != >= <= between null
    	SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 1 AND 10 ORDER BY prod_price;
    # 4. 数据过滤
    	# not
    	# and
    	# or
    	# and 的 计算优先级 大于 or 
    	# IN
    	SELECT prod_name,prod_price,prod_id FROM products WHERE  vend_id IN (1002,1003) ORDER BY prod_id;
    	
    # 5. 通配符过滤
    	# LIKE操作符
    		# % 任意字符出现任意次数 不能匹配null
    		# _ 单个字符
    		
    # 6. 
    	# 正则表达式
    	# 计算字段
    	# 处理函数
    
    # 7. 分组数据 
    	# 1. 数据分组 group by
    	SELECT vend_id,COUNT(*) AS num_rods FROM products GROUP BY vend_id;
    	# 2. 过滤分组 having
    	SELECT vend_id,COUNT(*) AS num_rods FROM products GROUP BY vend_id HAVING num_rods > 5;
    # 8. 子查询 联结查询
    	SELECT vend_name,prod_name,prod_price FROM vendors,products 
    		WHERE	vendors.vend_id = products.vend_id ORDER BY vend_name;
    # 9. 高级联结
    	# 1. 使用别名
    	SELECT cust_name,cust_contact FROM customers AS c , orders AS o, orderitems AS oi
    	WHERE c.`cust_id` = o.cust_id
    		AND oi.`order_num` = o.order_num
    		AND prod_id = 'TNT2';
    # 10. 组合查询
    	# 创建组合查询 UNION
    	SELECT * FROM products;
    	SELECT prod_id FROM products
    	UNION
    	SELECT prod_price FROM products;
    	
    # 11. 插入数据
    	# 创建数据表
    	CREATE TABLE testInsert(
    	test_name VARCHAR(10) NOT NULL,
    	test_id INT NOT NULL AUTO_INCREMENT,
    	test_sex VARCHAR(10) NOT NULL,
    	PRIMARY KEY(test_id)
    	)ENGINE=INNODB;
    	# 删除数据表
    	DROP TABLE customers;	
    	# 插入完整的行
    	INSERT INTO testInsert VALUES('ddd',33,'aa');
    	INSERT INTO testInsert VALUES('cds',01,'boy');
    	INSERT INTO testInsert VALUES('mysql',02,'girl');
    	# 插入一部分数据
    	# 修改表名
    	ALTER TABLE testInsert RENAME new_name;
    
    	# 修改字段类型
    	ALTER TABLE new_name MODIFY test_sex VARCHAR(10);
    
    	# 修改字段名
    	ALTER TABLE new_name CHANGE test_sex age INT;
    
    	# 删除字段
    	ALTER TABLE new_name DROP test_sex;
    
    	# 修改字段排序
    	ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST 字段2;
    	# 注释：其中字段1指要修改位置的字段，数据类型值字段1的数据类型，FIRST 为可选参数 ，指将字段1修改为表的第一字段，ALTRE 字段2是值将‘字段1’插入到‘字段2’后面
    
    	# 插入多行数据
    	INSERT INTO new_name (test_name,test_id)
    	VALUES('a',23);