Datawhale September team learning task01 environment setup, initial database
datawhaleSQL Tutorial Connections: https://github.com/datawhalechina/wonderful-sql
1. Environmental Setup
1.MySQL 8.0 Installation
Installed in July
Two blogs have been posted before:👉
2.Connect MySQL and execute SQL queries
First burst of mind
Actually, there is already navicat, but when you see the recommended jetbrain company datagrip in the tutorial, you just want to try it out...
Jetbrain's offer has actually been certified in a student mailbox, but on a page on the jetbrain website, it was found that you can apply for the GitHub Student Developer Kit...
And then it's such a waste of a day...
Apply for that developer package
I tried to upload my Student Card, Admission Notice and Student Credit Web Membership Certificate separately, but failed
Finally, I thought about bringing a few photos together...
Not yet passed. My mind is broken.
Installed is toolbox, where the datagrip downloaded is the latest version...
Then it's different from the datagrip version used in most of the online tutorials...
Or go to the command line first
Second burst of mind
- By impression, this computer - > Administration - > Services and Applications - > Services
Then open the command line and start fooling😭
net start mysql
System error 5 occurred. Access denied.
And then we found it Solution
Run as an administrator and enter that
Find yourself really foolish...😅…Didn't you start it manually on this computer?
Search through a tutorial and then suspect your computer is broken countless times...
And every time you enter your own password is correct!
In order to verify whether your password is wrong, I also went to navicat to connect a wave, can connect it!
And then suddenly I noticed...that there was less-h localhost
mysql -h localhost -u root -p
😅😅😅…Today the brain is really😅
Try basic commands
Getting down to business
555...It's still connected up front
Connect with datagrip
First you've been tossing around for a long time, then follow this Course Slightly connected...
Forgot to record the tread pit😓😓😓
2. First Identity Database
This is not really an initial database. I learned mongodb non-relational databases before
- A database is a collection of data that is stored and processed by a computer for efficient access.
- This data collection is called a Database (DB)
- The computer system used to manage a database is called a Database Management System (DBMS)
- The RDBMS you will learn this time refers to relational database management system, the full name Relational Database Management System
- RDBMS is the foundation of SQL
- Data in an RDBMS is stored in a database object called a table.
- A table is a collection of related data items that consist of columns and rows.
1. Beginning with SQL
Introduction to SQL
- SQL (Structured Query Language: Structured Query Language) is used to manage relational database management systems (RDBMS). The scope of SQL includes data insertion, query, update and deletion, database schema creation and modification, and data access control.
SQL Basic Writing Rules
The SQL statement should end with a semicolon
SQL case insensitive
- The win system is not case sensitive by default for table and field names (linux/mac sensitive)
2. Important SQL statements
(1) Create a database (CREATE DATABASE)
CREATE DATABASE shop;
(2) Create tables (CREATE TABLE)
Create a commodity table:
CREATE TABLE product (product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER, purchase_price INTEGER, regist_date DATE, PRIMARY KEY (product_id));
Only half-corner letters, numbers, and underscores () can be used as names for databases, tables, and columns
Name must begin with a half-corner letter
- Each column of a table created by a database requires a specified data type, and each column cannot store data of a different data type than the column specifies
- Basic data types
- Store Integer
- Stores a fixed-length string
- When the length of a string stored in a column is less than the maximum length, half-space is used to make up for it, which is generally not used because it wastes storage space
- Stores a fixed-length string
- Store variable length strings
- Storage Date (Year, Month, Day)
- Function to restrict or append conditions to data stored in columns
- NOT NULL Non-null Constraint
- The column must enter data
- PRIMARY KEY Primary Key Constraint
- The column is the unique value through which data for a particular row can be retrieved
- NOT NULL Non-null Constraint
(3) Update and deletion of tables
Delete table syntax
DROP TABLE <Table Name>
Delete product table
Show me first
DROP TABLE product;
ALTER TABLE statement to add columns
ALTER TABLE < Table Name > ADD COLUMN < Definition of columns >;
- Add a product_name_pinyin column that can store 100-bit variable-length strings
ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
- Well, first restore the product table you just deleted
Delete column ALTER TABLE statement
ALTER TABLE < Table Name > DROP COLUMN < Column Name >;
- Delete the product_name_pinyin column
ALTER TABLE product DROP COLUMN product_name_pinyin;
The ALTER TABLE statement, like the DROP TABLE statement, cannot be recovered after execution. Misadded columns can be deleted by the ALTER TABLE statement, or the table can be deleted and recreated after all tables have been deleted
Empty table contents
TRUNCATE TABLE <Table Name>;
- truncate cleans up data faster than drop / delete
When using update, be aware of adding where conditions, otherwise all rows will be modified according to the statement
Columns can also be updated to NULL with UPDATE
- NULL Empty
- Simply write the value to the right of the assignment expression directly as NULL
The UPDATE statement can also use NULL as a value, just like the INSERT statement. However, only columns that do not have NOT NULL and primary key constraints set can be emptied as NULL. If the columns with the above constraints are updated to NULL, an error will occur, which is the same as the INSERT statement.
The SET clause of the UPDATE statement supports multiple columns as update objects at the same time
It would be a little cumbersome if you didn't combine them like this
(4) Insert data into tables (INSERT)
Start by creating a table called productins as an example
CREATE TABLE productins (product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id));
Here DEFAULT is the initial default
INSERT INTO <Table Name> (Column 1, Column 2, Column 3, ......) VALUES (Value 1, Value 2, Value 3, ......);
When INSERT a full column of a table, the list of columns after the table name can be omitted. The value of the VALUES clause is assigned to each column by default in left-to-right order
-- Include column list INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', 'Pressure cooker', 'Kitchenware', 6800, 5000, '2009-01-15'); -- Omit list of columns INSERT INTO productins VALUES ('0005', 'Pressure cooker', 'Kitchenware', 6800, 5000, '2009-01-15');
- Note that INSERT INTO is not INFO
- Note that the constraint setting PRIMARY KEY cannot be repeated
In principle, executing an INSERT statement once inserts one row of data. When inserting multiple rows, it is usually necessary to loop through the INSERT statements a corresponding number of times. In fact, many RDBMS s support inserting multiple rows of data at once.
-- Multiline INSERT ( DB2,SQL,SQL Server, PostgreSQL and MySQL Multiline Insertion) INSERT INTO productins VALUES ('0002', 'Punch', 'Office Supplies', 500, 320, '2009-09-11'), ('0003', 'motion T Pension', 'clothes', 4000, 2800, NULL), ('0004', 'Kitchen knife', 'Kitchenware', 3000, 2800, '2009-09-20');
SELECT * FROM Table Name;
When you want to assign NULL values to a column in an INSERT statement, you can write NULL directly into the list of values in the VALUES clause. Columns that want to insert NULL must not have NOT NULL constraints set
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0010', 'Fork', 'Kitchenware', 500, NULL, '2009-09-20');
Look at the table
Data can be copied from other tables
For example, copy data from productins into products
INSERT INTO product (product_id,product_name,product_type,sale_price,regist_date) SELECT product_id, product_name,product_type,sale_price,regist_date FROM productins;
- At this point product is not an empty table
CREATE TABLE Addressbook (regist_no INTEGER NOT NULL, name VARCHAR(128) NOT NULL, address VARCHAR(256) NOT NULL, tel_no CHAR(10), mail_address CHAR(20), PRIMARY KEY (regist_no));
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
You can view the structure of a table using DESC
DROP TABLE Addressbook;
Just think of one of the most clumsy ways to add tables...