• System environment MAC OS 10.10
• MySQL version, 5.7.9

Installing mysql on Mac is very simple. Download the installation package from the official website and double-click the installation. There are several places to pay attention to

When installing mysql with DMG package on Mac, a root password will be randomly assigned in the last step after installation. Remember the password. After installation, log in with root, but the password is expired. By default, the valid period of mysql password is 360 days and needs to be changed again. For details, see Password Expiration Policy .

The basic SQL statements mainly come from SQL in 10 Minutes, Sams Teach Yourself This book, this book provides practice forms, Table content download

Basic concept

• Database: save an organized database.
• Table: a resulting list of specific types of data.

The data stored in a table is a type of data or a list of data

• Schema is about the layout and characteristics of databases and tables
• Column is a field in a table, so the table is not composed of one or more columns.

The last way to understand columns is to imagine a network in which each column stores a specific information.

• The data type allowed by the data type. Each category has a corresponding data type, which is used to limit the types allowed in the column
• Row: the data in the table is stored by row. Imagine the table as a network, with vertical columns and horizontal rows.
• Primary key: each row in the table should have a column that can uniquely identify itself. In a column, the value that can uniquely identify each row in the table. The design table should always be designed with a primary key. The conditions set to hide the primary key are:
  • Any two lines have the same value
  • Each row must have a primary key value
  • The value in the primary key column cannot be modified or updated
  • Primary key values cannot be reused.

Sample table

Create a sample table for practice. There are 5 tables in total. The content and purpose of the table

• Manage suppliers
• Manage product catalogs
• Manage customer list
• Enter customer order

Create a new database

mysql> CREATE DATABASE SQL_Learning;
Query OK, 1 row affected (0.01 sec)

Create a new Vendors table to store suppliers selling products.

Switch to the database first

mysql> USE SQL_Learning;
Database changed

mysql> CREATE TABLE Vendors
    -> (
    ->   vend_id      char(10) NOT NULL ,
    ->   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
    -> );
Query OK, 0 rows affected (0.08 sec)

• NOT NULL means that the value cannot be empty
• char(10) holds a fixed length string, where 10 strings are specified
• vend_id primary key

The Products table contains a catalog of Products

mysql> CREATE TABLE Products
    -> (
    ->   prod_id    char(10)      NOT NULL ,
    ->   vend_id    char(10)      NOT NULL ,
    ->   prod_name  char(255)     NOT NULL ,
    ->   prod_price decimal(8,2)  NOT NULL ,
    ->   prod_desc  text          NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

• prod_id primary key
• vend_id foreign key, associated with supplier ID
• decimal(8,2) is a DOUBLE type stored as a string, allowing fixed decimal points.

Create Customers table

mysql> CREATE TABLE Customers
    -> (
    ->   cust_id      char(10)  NOT NULL ,
    ->   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
    -> );
Query OK, 0 rows affected (0.05 sec)

• cust_id primary key

Create the Orders table, one product per row

mysql> CREATE TABLE Orders
    -> (
    ->   order_num  int      NOT NULL ,
    ->   order_date datetime NOT NULL ,
    ->   cust_id    char(10) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

• order_num primary key
• cust_id foreign key, associated with customer ID

The OrderItems table stores the actual items for each order.

mysql> 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
    -> );
Query OK, 0 rows affected (0.02 sec)

• order_num order number, which is associated with the order in the Orders table_ num
• order_item order item number,
• prod_id product ID
• Quantity product quantity
• item_price product price

Create primary key

ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);

Create a foreign key to point to the PRIMARY KEY of another table

ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);

• Put the order in the OrderItems table_ Num is associated with order in the Orders table_ num

ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);

• Add prod in the OrderItems table_ ID is associated with prod in the Products table_ id

ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

• Put cust in the Orders table_ ID is associated with cust in the Customers table_ id

ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

• Put the vend in the Products table_ ID is associated with the vend in Vendors_ id ```

Table insert table data directly downloads table data. You can insert a table into a table in batches.

This article code file

-- create database if not exists database_name charset 'Coding method, generally utf8';
create database if not exists SQL_Learning charset 'utf8';
-- use database_name(Database name)
use SQL_Learning;
-- drop table (if exists Does it exist) table name;
drop table if exists Vendors;

create table Vendors(
    Vendors_id char(10) not null primary key ,
    Vendors_name char(50) not null ,
    Vendors_address char(50) null,
    Vendors_city char(50) null ,
    Vendors_state char(5) null ,
    Vendors_zip char(10) null ,
    Vendors_country char(50) null
);

select * from Vendors;
-- create table if not exists Table_name(Table_Char_id type not null/null  another setting);
create table if not exists Products(
    Prod_id char(10) not null primary key ,
    Vendors_id char(10) not null ,
    Prod_name char(255) not null ,
    Prod_price decimal(8,2) not null default 0,
    Prod_desc text
);
-- grammar:alter table Table name add constraint FK_ID foreign key(Your foreign key field name) REFERENCES Appearance table name(The primary key field name of the corresponding table);
alter table Products add constraint FOREIGN KEY(Vendors_id) REFERENCES Vendors(Vendors_id);
-- show table info
select *
from Products;