- 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 .
- 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.
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;