Datawhale September team learning task01 environment setup, initial database

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

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
    

    Display:

    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?

  • Next step

    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)

grammar

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

Naming Rules
  • 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

data type
  • 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
    • INTERGER
      • Store Integer
    • CHAR
      • 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
    • VCHAR
      • Store variable length strings
    • DATE
      • Storage Date (Year, Month, Day)
Constraint Settings
  • 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

(3) Update and deletion of tables

  • Delete table syntax

    DROP TABLE <Table Name>
    
  • Delete product table

    • Show me first

    • delete

      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
  • Data Update

    • Basic Grammar

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

  • Multi-column update

    • 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

    ​

  • Basic Grammar

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

    Equivalent to:

    ​

  • View Table

    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

3. Exercises

1.1

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

1.2

ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;

  • You can view the structure of a table using DESC

1.3

DROP TABLE Addressbook;

1.4

Just think of one of the most clumsy ways to add tables...


πŸ˜”πŸ˜”πŸ˜”

Tags: Database MySQL

Posted on Tue, 14 Sep 2021 12:37:37 -0400 by fullyscintilla