Introduction to MySQL

MySQL is an open source, relational database management system. The word “SQL” in “MySQL” stands for “Structured Query Language,” the most common standardized Language for accessing databases.

This article mainly through the basic MySQL tutorial to learn, summarize and sort out some basic knowledge points. And do some exercises to deepen your understanding of these points. The links attached to the knowledge points are basically the details of the corresponding knowledge points in the official MySQL documentation.

MySQL installation

MySQL > install MySQL from brew

brew install mysql
Copy the code

2. Check the information about the installed MySQL

brew info mysql
Copy the code

Print result:

mysql: Stable 8.0.21 (bottled) Open source relational database management system https://dev.mysql.com/doc/refman/8.0/en/... / usr/local/Cellar/mysql / 8.0.21... We've installed your MySQL database without a root password. To secure it run: mysql_secure_installation MySQL is configured to only allow connections from localhost by default To connect run: mysql -uroot To have launchd start mysql now and restart at login: brew services start mysql Or, if you don't want/need a background service you can just run: mysql.server startCopy the code

Based on the information, perform the following steps.

Add environment variables to ~/.zshrc

Echo 'export PATH = "/ usr/local/Cellar/mysql / 8.0.21 / bin: $PATH"' > > ~ /. ZSHRCCopy the code

Re-execute the ~/.zshrc file:

source r ~/.zshrc
Copy the code

Enter mysql –version on the terminal, and it will print:

Mysql Ver 8.0.21 for OSx10.14 on x86_64 (Homebrew)Copy the code

This is the newly installed MySQL.

3. Run the following statement to start the server:

mysql.server start
Copy the code

4. Run the following command to set the password of user root:

mysql_secure_installation
Copy the code

Set the password according to the prompts. Set the corresponding password strength according to the requirements of the selected password level:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
Copy the code

5. Connect to database:

mysql -uroot -p 
Copy the code

Disconnect from database:

Use Control + D, or type QUIT, or \q.

Download: dev.mysql.com/downloads/w…

Be careful to choose the version of your computer can support the download. Schema is the same as database.

Basic Query Principles

1. A query usually consists of an SQL statement and a semicolon.

2, after the query is issued, mysql will send the query to the server for execution and display the results, and then print mysql> to indicate that it is ready for another query.

Mysql displays the output of the query in table form. The first row contains the labels for each column, and the following rows show the query results. Typically, the column label is the column name retrieved from the database. If you want to retrieve the value of an expression rather than a column of a table, mysql uses the expression itself to mark the column.

Mysql shows how many rows were returned and how long the query took.

Mysql is case insensitive.

You can write multiple statements on a single line. You can also write a statement on more than one line, because a statement ends with a semicolon.

7. If you do not want to execute the query while typing, type \c to cancel the query.

Create and use databases

1. Check the database

Use SHOW to see what databases the server currently has.

show databases;
Copy the code

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
Copy the code

SHOW DATABASES does not display DATABASES for which you do not have permissions.

2. Create a database

(1) Most commonly used statements:

create database [if not exists] database_name;
Copy the code

If not exists is optional. If not exists, the database is created only if the database database_name does not exist. A warning is given when a database with a duplicate name is created:

1 row(s) affected, 1 warning(s): 1007 Can't create database 'db1'; database exists
Copy the code

Mysql > create database with duplicate name

Error Code: 1007. Can't create database 'db1'; database exists
Copy the code

(2) In Unix operating systems, SQL keywords are case insensitive, while database names and table names are case sensitive. On macOS, the database name is also case-insensitive (create database db1; Then execute create database DB1; Errors will be reported).

Select db1 as the current database.

(4) You can also select the database directly when starting the database:

mysql -h host -u user -p db1
Copy the code

(5) More complete declaration database way:

create database [if not exists] database_name
[character set charset_name]
[collate collation_name]
Copy the code

Declare character sets and collate for the new database at creation time. If not, the default character set and collation are used.

3. Check the created database

show create database database_name;
Copy the code

Executing this command prints the following:

mysql> show create database db1;+----------+------------------------------------------------------------------------------------------------------------ -------------------+ | Database | Create Database | +----------+------------------------------------------------------------------------------------------------------------ -------------------+ | db1 | CREATE DATABASE `db1` /*! 40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*! 80016 DEFAULT ENCRYPTION='N' */ | +----------+------------------------------------------------------------------------------------------------------------ -------------------+ 1 row in set (0.00 SEC)Copy the code

The created database db1 uses the default UTF8MB4 character set and the default UTF8MB4_0900_AI_CI collation mode, and does not enable database encryption.

Character Sets, Collations, Unicode, InnoDB data-at-rest Encryption.

4. Access the database

USE to access the database. USE, like QUIT, does not need to end with a semicolon. The USE statement must be written on a single line.

use sys
Copy the code

After executing this sentence, sys is selected as the current database.

5. View the selected database

Using the DATABASE() function:

select database(a);Copy the code

Delete the database

Deleting a database deletes all tables contained in the database as well as the database itself.

drop database [if exists] database_name;
Copy the code

If exists is optional and indicates that the database is deleted only if it exists. Deleting a database that does not exist causes a Warning if exists and an Error if exists is not added.

Since schema and database are synonyms in MySQL, they can be used like this:

drop schema [if exists] database_name;
Copy the code

7. Set permissions

Use GRANT to set permissions.

grant all on db_name.* to 'user_name'@'host_name';
Copy the code

The above statement allows user user_name to access all tables of database DB_name in host_name.

Database engine

Starting with version 5.5, MySQL uses InnoDB as its default storage engine. InnoDB supports transactions, foreign keys, rollback, and more.

Storage Engines.

Create and use tables

1. Create table

Use CREATE TABLE to CREATE a TABLE in a database:

create table [if not exists] table_name ( column_1_definition, column_2_definition, ... , table_constraints )engine = stroage_engine;
Copy the code

Use engine = stroage_engine; Declare the storage engine for the table.

The column definition part is:

column_name data_type(length) [not null] [default value] [auto_increment] column_constraint;
Copy the code

• column_nameIt’s Column’s name.
• data_type(length)Is the data type of the column (Data Types) and optional sizes. Such asVARCHAR(10)Storage length of 10 (10 bytes).
• NOT NULLConstraint This column does not containNULL. Columns may also contain other restrictions, such asCHECK, andUNIQUE.
• DEFAULTA default value is declared for the column.
• AUTO_INCREMENTIndicates that the value of a column is automatically incremented when a new row is inserted into the table. Each table can have a maximum of one tableAUTO_INCREMENTThe column.

After the column list, you can define table restrictions, such as UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY.

① Create a table

create table if not exists table1 (
  a int auto_increment primary key,
  b varchar(100) not null,
  c date
);
Copy the code

Column A is an incremented integer and is the primary key column of the table. The value of column A is unique among all the rows in the table. It is impossible to have rows of column A with a value of 1 at the same time.

② Check the structure of the table

describe table1;
Copy the code

2, modify table

2.1 the new column

(1) Add a column to the table

alter table table_name
  add new_column_name column_definition
  [first | after column_name]
Copy the code

First indicates that it is the first column, and after column_name indicates that it is after a column.

(2) Add multiple columns to a table

alter table table_name
  add new_column_name column_definition
  [first | after column_name],
  ...
  add new_column_name column_definition
  [first | after column_name];
Copy the code

Add two columns D and e to table;

alter table table1
  add d int not null after c,
  add e int after d;
Copy the code

Use the describe the table1; View table structure:

2.2 modify the column

(1) Modify a column

alter table table_name
  modify column_name column_definition
  [first | after column_name]
Copy the code

(2) Modify multiple columns

alter table table_name
  modify column_name column_definition
  [first | after column_name],
  ...
  modify column_name column_definition
  [first | after column_name];
Copy the code

Subsequent ALTER table statements (such as DROP column) are similar to this one, with only one DROP column being dropped, and multiple drop columns being separated by commas.

Alter column E to vARCHAR:

alter table table1
  modify e varchar(100) not null first;
    
describe table1;
Copy the code

2.3 Renaming columns

alter table table_name
  change column original_name new_name column_definition
  [first | after column_name];
Copy the code

Change column original_name new_name column_definition Changes the name and definition of a column.

[first | after column_name] decided to columns in the new name.

Change d to a column named d1 where data is allowed to be null and place it after column A:

alter table table1
  change d d1 int default null after a;

describe table1
Copy the code

2.4 delete columns

alter table table_name
  drop column column_name;
Copy the code

Alter table d1 alter table d1

alter table table1
  drop column d1;

describe table1;
Copy the code

2.5 Renaming a Table

alter table table_name
  rename to new_table_name;
Copy the code

Or:

rename table old_table_name to new_table_name;
Copy the code

Alter table table1 alter table table2

alter table table1
  rename to table2;

show tables;
Copy the code

Rename table2 table3;

rename table table2 to table3;

show tables;
Copy the code

3, drop table

drop table [if exists] table_name;
Copy the code

Select table3 from table3 where table3 = table3

create table table1 like table3;
create table table2 like table3;

show tables;
Copy the code

Mysql > delete tale2 from table3

drop table if exists table2, table3;

show tables;
Copy the code

Delete all data from the table

TRUNCATE TABLE deletes all data in the TABLE. Instead of deleting rows row by row, it deletes and then recreates the TABLE.

truncate [table] table_name;
Copy the code

• If you haveFOREIGN KEYConstraint, thenTRUNCATE TABLEWill fail.
• A delete operation cannot be rolled back.
• If you haveAUTO_INCREMENTColumn, resets its value.
• TRUNCATE TABLEThe number of lines affected by the statement is 0.

Modify the data in MySQL

1. Insert data

INSERT one or more rows into a table using the INSERT statement.

Insert a row into the table:

insert into table_name(column1, column2, ...)
values (value1. value2, value3);
Copy the code

Insert multiple rows into a table:

insert into table_name(column1, column2, ...)
  values 
  (value_11. value_12, value_13),
  ...
  (value_n1. value_n2, value_n3);
Copy the code

The number of columns must be the same as the number of values, and the position of the column must correspond to the position of the value.

Insert two rows into table1:

insert into table1(a, b, c, e)
  values
  (1.'First row'.'2021-05-30'.'Column E data'),
  (2.'Second row'.'2021-05-30'.'e column data 2');

select * from table1;
Copy the code

2. Update data

UPDATE data in a table with an UPDATE statement that changes the values of one or more columns in one or more rows.

update [low_priority] [ignore] table_name
  set
    column_name1 = expression1,
    column_name2 = expression2,
    ...
[where condition];
Copy the code

• setThe clause declares the column to be updated along with the new value.
• whereThe clause declares which columns to update.
• uselow_priorityModifiers delay operations until no other clients are reading data from the table. This operation has a lower priority than any other operation.
• ignoreModifiers allow the statement to be executed even if an error occurs.

Update column E from row 1 to row e:

update table1
    set
    e = 'Data in row 1, column E'
where a = 1;

select * from table1;
Copy the code

3. Delete data

delete from table_name
[where condition];
Copy the code

The WHERE clause is optional, and if the WHERE clause is not declared, the DELETE statement deletes all rows in the table.

The DELETE statement returns the number of rows deleted.

Select * from table1 where a = 2;

delete from table1
where a = 2;

select * from table1;
Copy the code

4. Query data

Use the SELECT statement to query the data.

select select_list
from table_name;
Copy the code

Select_list is a list of columns or expressions to query.

MySQL evaluates the FROM clause first and then the SELECT clause.

Select from -> select.

Select * from table1 select * from table1;

select a from table1;
Copy the code

5. Sort data

Sort data using the ORDER BY modifier.

select select_list
from table_name
order by
    column_1 [asc | desc],... column_n [asc | desc];
Copy the code

ASC indicates ascending, and DESC indicates descending.

If ASC or DESC is not explicitly stated, ORDER BY defaults to descending ORDER.

When sorting through multiple columns, ORDER BY is sorted from top to bottom.

From -> select -> order by

Insert a bit of data into the table:

insert into table1(a, b, c, e)
    values
    (2.'Second row'.'2021-05-30'.'The value of column E'),
    (3.'Third row'.'2021-05-30'.'The value of column E'),
    (4.'Row 4'.'2021-05-30'.'The value of column E'),
    (5.'Fifth row'.'2021-05-30'.'The value of column E');
Copy the code

Select * from descending order (a);

select a, b from table1
order by a desc;
Copy the code

6. Filter data

Use WHERE clauses to filter data.

select select_list
from table_name
where search_condition;
Copy the code

select * from table1
where a >= 3;
Copy the code

Interactive mode and batch mode

1. Interactive mode

Run the mysql -uroot -p command to enter the interactive mode.

mysql> select database();+ -- -- -- -- -- -- -- -- -- -- -- -- + | database () | + -- -- -- -- -- -- -- -- -- -- -- -- + | db1 | + -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)
mysql> 
Copy the code

The following are the meanings of several prompts, except for mysql>, which indicate that there is something unfinished and needs to wait for the next line. For example, ‘> indicates a string containing only opening quotes’, but no closing quotes. Wait for closing quotes to be entered.

Prompt	Meaning
mysql>	You are ready for a new query
->	Wait for the next line of a multi-line query
'>	Wait in single quotes (') begins the next line of completion of the string
">	Wait in double quotation marks (") begins the next line of completion of the string
` ` > `	Wait for the next line of completion of an identifier beginning with (‘)
/ * >	Wait,/ *The next line of completion of the beginning comment

2. Batch processing mode

Put the statement to run in a file and tell mysql to read input from the file. You can do either of the following.

(1) Execute at the terminal:

mysql < file_name
Copy the code

(2) use source or \. Command at mysql prompt:

mysql> source file_name;
mysql> \. file_name
Copy the code

① Add the following content to the 00.sql file:

use db1;
select database(a);Copy the code

(2) :

mysql -uroot -p < 00.sql
Copy the code

Enter your password and click Ok to get the following results:

database()
db1
Copy the code

Mysql -uroot -p enter source 00.sql; Or \.00.sql results in:

mysql> source 00.sql;Database changed + -- -- -- -- -- -- -- -- -- -- -- -- + | Database () | + -- -- -- -- -- -- -- -- -- -- -- -- + | db1 | + -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

mysql> \. 00.sqlDatabase changed + -- -- -- -- -- -- -- -- -- -- -- -- + | Database () | + -- -- -- -- -- -- -- -- -- -- -- -- + | db1 | + -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

other

This article only briefly understand the use of MySQL, many details have not been mentioned, in the future, there will be a separate article based on a topic, such as MySQL queries, MySQL table relationships, MySQL transactions, etc.