Using terminal operation database MySQL

Using terminal to operate MySQL

MySQL installation tutorial reference rookie tutorial: https://www.runoob.com/mysql/mysql-install.html
Content reference website:
https://github.com/hjzCy/sql_node/blob/master/mysql/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0.md

1, Basic operation

cmd entry method under win: mysql -uroot -p

  1. See what databases are available
show databases;
  1. Select database
use databasesName;
  1. View which tables are in the database
show tables;
  1. Data in query table
select * from tableName;
  1. Exit database server
exit;
  1. Create your own database in the database server
create database databaseName;
  1. Create a data table, (create a table named pet)
create TABLE pet(
                   name VARCHAR(20),
                   owner VARCHAR(20),
                   specise VARCHAR(20),
                   sex CHAR(1),
                   brith DATE,
                   death DATE );

matters needing attention:

  • The difference between var() and varchar() is that var() is constant. Even if the stored string does not reach the upper limit of the number in "()", var() will still fill the space with spaces, while varchar() is of variable length. If it does not reach the upper limit in "()", the following spaces will be automatically removed;
  • Do not add "," when defining the last field;
  1. View the schema of the data table
describe tableName;
desc pet;
  1. insert data
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);
  1. Modify data
UPDATE pet SET name = 'squirrel' where owner = 'Diane';
  1. Delete data
DELETE FROM pet where name = 'squirrel';
  1. Delete table
DROP TABLE myorder;

2, Table creation constraint

  1. Primary key constraint
    – make a field non duplicate and non empty to ensure the uniqueness of all data in the table.
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);
  1. composite keys
    – each field in the federated primary key cannot be empty and cannot be repeated with the set federated primary key.
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);
  1. Self increasing constraint
    – the primary key of the self increment constraint is automatically incrementally allocated by the system.
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
  1. Add primary key constraint
    – if you forget to set the primary key, you can also set it through SQL statements (two methods):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
  1. Delete primary key
ALTER TABLE user drop PRIMARY KEY;
  1. Unique primary key
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);
  1. Add unique primary key
    – if a unique table is not set during table creation, it can also be set through SQL statements (two methods):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;
  1. Delete unique primary key
ALTER TABLE user DROP INDEX name;
  1. Add non empty constraint when creating table
    – a constraint field cannot be empty
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);
  1. Remove non empty constraints
ALTER TABLE user MODIFY name VARCHAR(20);

Foreign key constraint
– class

CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

– student form

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    -- there class_id Want and classes Medium id Field
    class_id INT,
    -- express class_id The value of must come from classes Medium id field value
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

– 1. Data values that do not exist in the primary table (parent table) classes cannot be used in the secondary table (child table) students;
– 2. When the records in the primary table are referenced by the secondary table, the primary table cannot be deleted.

3, Three paradigms

  • 1NF
    As long as the field value can continue to be split, it does not meet the first normal form.
    The more detailed the paradigm design is, it may be better for some practical operations, but not all of them are beneficial. It is necessary to set the actual situation of the project.

  • 2NF
    On the premise of meeting the first normal form, other columns must completely depend on the primary key column. Incomplete dependency can only occur in the case of Federated primary keys:

-- Order form
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

In fact, in this order table, product_name depends only on product_id ，customer_name only depends on customer_id . That is, product_name and customer_id doesn't matter, customer_name and product_id also doesn't matter.
This does not satisfy the second paradigm: other columns must completely depend on the primary key column!

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

After splitting, the product in the myorder table_ ID and customer_id is completely dependent on order_id primary key, while other fields in the product and customer tables depend entirely on the primary key. Meet the design of the second paradigm!

  • 3NF
    On the premise of meeting the second normal form, there can be no transfer dependency between other columns except the primary key column.
CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

Customer in table_ The phone may depend on order_id , customer_id columns do not meet the design of the third paradigm: there can be no transitive dependency between other columns.

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

After modification, there is no transfer dependency between other columns. Other columns only depend on the primary key column, which meets the design of the third paradigm!

4, Query exercise

Prepare data
– create database

CREATE DATABASE select_test;

– switch database

USE select_test;

– create student table

CREATE TABLE student (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE, -- birthday
    class VARCHAR(20) -- Class
);

– create teacher table

CREATE TABLE teacher (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE,
    profession VARCHAR(20) NOT NULL, -- title
    department VARCHAR(20) NOT NULL -- department
);

– create a curriculum

CREATE TABLE course (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    t_no VARCHAR(20) NOT NULL, -- Teacher number
    -- Indicates that tno From teacher In table no field value
    FOREIGN KEY(t_no) REFERENCES teacher(no) 
);

– transcript

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL, -- Student number
    c_no VARCHAR(20) NOT NULL, -- Course number
    degree DECIMAL,	-- achievement
    -- Indicates that s_no, c_no From student, course In table no field value
    FOREIGN KEY(s_no) REFERENCES student(no),	
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- set up s_no, c_no Is a federated primary key
    PRIMARY KEY(s_no, c_no)
);

– view all tables

SHOW TABLES;

– add student table data

INSERT INTO student VALUES('101', 'Zeng Hua', 'male', '1977-09-01', '95033');
INSERT INTO student VALUES('102', 'Kuang Ming', 'male', '1975-10-02', '95031');
INSERT INTO student VALUES('103', 'Wang Li', 'female', '1976-01-23', '95033');
INSERT INTO student VALUES('104', 'Li Jun', 'male', '1976-02-20', '95033');
INSERT INTO student VALUES('105', 'Wang Fang', 'female', '1975-02-10', '95031');
INSERT INTO student VALUES('106', 'land force', 'male', '1974-06-03', '95031');
INSERT INTO student VALUES('107', 'Wang NIMA', 'male', '1976-02-20', '95033');
INSERT INTO student VALUES('108', 'Zhang quandan', 'male', '1975-02-10', '95031');
INSERT INTO student VALUES('109', 'Tie Zhu Zhao', 'male', '1974-06-03', '95031');

– add teacher table data

INSERT INTO teacher VALUES('804', 'Li Cheng', 'male', '1958-12-02', 'associate professor', 'Computer Department');
INSERT INTO teacher VALUES('856', 'Xu Zhang', 'male', '1969-03-12', 'lecturer', 'Department of Electronic Engineering');
INSERT INTO teacher VALUES('825', 'Wang Ping', 'female', '1972-05-05', 'assistant', 'Computer Department');
INSERT INTO teacher VALUES('831', 'Bing Liu', 'female', '1977-08-14', 'assistant', 'Department of Electronic Engineering');

– add schedule data

INSERT INTO course VALUES('3-105', 'Introduction to computer', '825');
INSERT INTO course VALUES('3-245', 'operating system', '804');
INSERT INTO course VALUES('6-166', 'digital circuit ', '856');
INSERT INTO course VALUES('9-888', 'Advanced mathematics', '831');

– add transcript data

INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

– view table structure

SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

– query all rows of the student table

SELECT * FROM student;

– query all rows of the name, sex, and class fields in the student table

SELECT name, sex, class FROM student;

– query the non duplicate department column in the teacher table
– department: de duplication query

SELECT DISTINCT department FROM teacher;

– query all rows in the score table with scores between 60-80 (interval query and operator query)
– BETWEEN xx AND xx: query interval, AND means "AND"

SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

– query the row in the score table with a score of 85, 86 or 88
– IN: query multiple values IN the specification

SELECT * FROM score WHERE degree IN (85, 86, 88);

– query all rows of class' 95031 'or gender' female 'in the student table
– or: indicates or relationship

SELECT * FROM student WHERE class = '95031' or sex = 'female';

– query all rows of the student table in descending class order
– DESC: descending, from high to low
– ASC (default): ascending, from low to high

SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

– in c_no ascending and degree descending queries all rows of the score table

SELECT * FROM score ORDER BY c_no ASC, degree DESC;

– query the number of students in class "95031"
– COUNT: Statistics

SELECT COUNT(*) FROM student WHERE class = '95031';

– query the student number and course number with the highest score in the score table (sub query or sorting query).
– (SELECT MAX(degree) FROM score): sub query to calculate the highest score

SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

– sort query
– LIMIT r, n: means to query n pieces of data from row r

SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

Calculate the average score in groups
– AVG: Average

SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';

– GROUP BY: Group Query

SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

Grouping condition and fuzzy query
Query the average score of courses with at least 2 students taking elective courses and beginning with 3 in the score table.

SELECT * FROM score;
-- c_no Course number
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

-- First put c_no, AVG(degree) Query by grouping
SELECT c_no, AVG(degree) FROM score GROUP BY c_no

-- Then find out the courses that at least 2 students take
-- HAVING: Indicates holding
HAVING COUNT(c_no) >= 2

-- And it's a course that starts with 3
-- LIKE Represents a fuzzy query,"%" Is a wildcard, match "3" Any character after.
AND c_no LIKE '3%';

-- Put the front SQL Put the sentences together,
-- Add one after it COUNT(*)，Indicates that the number of each group is also queried.
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';

Keywords: Database MySQL Big Data

Added by nediaz on Sat, 04 Sep 2021 03:22:06 +0300

Popular Keywords

©2024 Programming VIP