Basic SQL syntax & MySQL installation

Time:2021-10-17

Database overview

A database is a warehouse for storing data. In the development of application, it is always inseparable from data query, processing and storage. For example, library management system needs to manipulate and store a large amount of data. Before there was no database, we used files to store data, but file storage has many disadvantages, such as low query efficiency, troublesome reading, difficult management, etc. so many troubles can be handed over to the database for management. The database is a software specially developed for data storage management, which can be usedHigher efficiency and security

  • Classification of database

    Different databases manage data in different ways. It can be divided into two categories based on whether it is a relational database or not.
    Relational type:MySQL、ORACAL、DATEBASE、PostgreSQL、SQL server
    Non relational:Redis、MongoDB

  • Relational databaseMySQL

    The main purpose of this article is to master the basic application of MySQL.
    What is a relational database?The tables stored in a relational database are two-dimensional tablesFor example, a student information table contains many items: student number, name, gender and age. 60 students have 60 different student numbers, up to 60 names, gender or male or female. Each item (each column in the table) is a set of values of the same type, and the sets are not randomly combined, not randomly combined with a student number or a name, but meaningful specific combinations, which are used to represent student information one by one, This is the so-called “relationship”. If you don’t want to understand this, just think it’s excel.

MySQL installation

Windows installation MySQL 5.7.17

  1. On MySQL official websitehttp://dev.mysql.com/downloads/mysql/Download the zip installation package (windows (x86, 64 bit), zip archive) above.

  2. After downloading, unzip it and put it in the directory you want to install. For example: D: \ MySQL 5.7 \ mysql-5.7.17-winx64

  3. Create a new my.ini configuration file. The original my-default.ini configuration file is just a template and should not be changed in it. The contents of my.ini are as follows:

[mysql] default-character-set=utf8 
[mysqld] port = 3306 basedir=D:\MySQL5.7\mysql-5.7.17-winx64 datadir=D:\MySQL5.7\mysql-5.7.17- winx64\data max_connections=200 character-set-server=utf8 default-storage-engine=INNODB explicit_defaults_for_timestamp=true
  1. Create an empty data folder under the installation path.

  2. Run CMD as an administrator, enter the bin directory, and executemysqld --initialize-insecure --user=mysqlCommand. Without this step, the service cannot be started after the installation is completed.

  3. Still in the bin directory of the administrator CMD window, executemysqld installCommand installation. After completion, you will be prompted that the installation is successful.

  4. Still in the bin directory of the administrator CMD window, executenet start mysqlCommand to start MySQL service.

  5. Modify the environment variable and add “D: \ mysql-5.7.17-winx64 \ bin”.

  6. In the normal CMD window, enter the bin directory and executemysql -u root -p Command, no password by default, enter.

Installing MySQL on MAC system

Enable homebrew in MAC to install MySQL

  1. Download and install MySQL
    brew install [email protected]
    If you see the following boundary ⾯ it indicates that the download and installation have been successful

  2. mysql start
    mysql.server start

  3. Close MySQL
    mysql.server stop

  4. Login to MySQL
    mysql -u root -p

Basic logical structure of database

The whole MySQL library contains multiple libraries, four of which are built-in core libraries. There are several tables in each library, and each table is similar to an excel table
- MySQL
	-Several libraries
		-Several tables

Basic operation

Get to know MySQL

  1. First, input the command from the terminalmysql -u root -p Log in to MySQL
  2. View library inputshow databases;Statement to view all libraries (all SQL statements end with a semicolon). give the result as follows
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
--You can see four preset libraries, all of which are core libraries. Do not delete them.
  1. Select one of the libraries (for example, MySQL Library)use mysql;
  2. View all tables in the libraryshow tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
	...	...
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
--The number of lines is relatively large, and the middle part is indicated by an ellipsis
  1. select host,user from user;View the host and user field columns for all data in the user table
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
  1. select * from user;View all fields of all data in the user table

Create a new library

create database mydb default charset=utf8;
--After execution, you can see the mydb library by viewing all the libraries. Where default charset = utf8, the character set used by the selected library is UTF-8

Create a new table

--To create a table in which library, you must first select that library, and then use the following statement to create a simple table
create table user(
name varchar(20), 
age int, 
sex char(1) 
)engine=innodb default charset=utf8;
--Note that each field is separated by a comma. There is no comma after the last field!

Adding data to a table

--Add name, age and sex data to the user table
Insert into user (name, age, sex) values ('admin ', 26,' male ');
--The field assignment order can be arranged freely or assigned selectively. If no assignment order is provided, all assignments will be made in the defined order by default
Insert into user values ('zhang San ', 22,' female ');

DML

Data manipulation language

--Data manipulation is the manipulation of data content, including insert, delete and update.

--Insert: in addition to inserting one row of data in the above statement, you can also insert multiple rows of data at one time:
Insert into user values ('zhang San ', 22,' female '), ('admin', 26, 'male'), ('alian ', 16,' male ');

--Delete: carefully control the filtering conditions. If the filtering is not properly controlled, all data will be deleted
Delete from user where name = 'Zhang San';

--Update: also pay attention to the filter conditions when updating. The following statement directly changes the age and gender of the user admin
Update user set age = 23, sex = 'female' where name = 'admin';

DDL

Data definition language

--Data definition refers to the operation of table structure, including table definition and table structure change.

--There are instances above the table creation statement, and the table deletion statement is as follows:
drop table user;

--Add field (column), delete field (column)
alter table user add birthday date;
alter table user drop age;

--Change field. The field name must be changed when using change, but cannot be changed when using modify
alter table user modify name char(50);
alter table user change name u_name char(30);

--Change table engine
Alter table user engine = 'engine';
--For other operations, please refer to the official documents

DQL

Data query language

--In database applications, the most frequent operation is query

--The basic format of single table query. Select which fields, from which table, and where filter criteria
select ... from ... where ...;
--Example: view the names of all users older than 20
select name from user where age > 20;
--Multiple filter conditions can be connected by and and or. Pay attention to the priority of condition combination, and use () to control their combination.

--Nested queries. The result of the previous single table query is also a table, which can be used as the queried table, thus forming a nested query.
select ... from (select ... from ... where ...) where ...;

Statistics, grouping and sorting

Statistics:Many times, we need to further process the data to find out the problemsMaximumminimum valuethe sumaverage valueandcount, use the following function

select max(age),min(age),sum(age),avg(age),count(age) from user;
--The statistical function will count multiple rows of data into one row of data. For example, 100 ages will be counted into one data with the max function, which is the largest data in 100 data.

grouping:In addition to the simple usage above — changing all data statistical operations into a row of data, statistical functions are usually used with grouping statements.

For example, we want to count the average age of men and women respectively.

select sex,avg(age) from user group by sex;
--The semantics of group by sex is to group according to different values of sex. Sex has only two values, 'male' and 'female', and is divided into two groups accordingly. The scope of the statistical function is within groups, and there is no influence between groups.

Sort:When we encounter a need to query transcripts, usually the result we want is a result table in descending order from high to low.

select name,age from user order by age desc;
--Where desc represents descending order. ASC can be arranged in ascending order. The default value is ASC.

Paging query

When the query result has many rows, we can choose to view one of them

select name,age from user limit 3;--  Just look at three
select name,age from user limit 4,3;--  Skip the first 4 and view 3

summary

  1. Install MySQL
  2. Open MySQL
  3. View & operate MySQL

Be familiar with the following keywords

show , databases , use , table

select , from , where , count , min , max , sum , avg , group by , order by , desc

create , database , default , charset , engine , innodb , insert into , alter table , modify , change

What do these codes mean

show databases;

use ...;

select ... from ... where ...;

select max(...),avg(...),... from ... where ... group by ...;

select ... from ... group by ... desc;

select ... from ... limit ...;

create database ... default charset=...;

create table ...(
... ..., 
... ..., 
... ...
)engine=innodb default charset=utf8;

insert into ... values(...),(...),(...);

delete from ... where ...;

update ... set ... where ...;