MySQL database basics and SQL language primer

MySQL is a relational database management system developed by the Swedish Mysqlab Company and currently belongs to Oracle products. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (Relationaldatabasemanagementsystem, relational database management system) application software for Web applications.

Installation and configuration of MySQL

Related concepts:

Database: The warehouse of data, where data is stored.

Table: A place where data is stored in a taxonomy, where it is actually saved

Column (field): A specific piece of information, such as: name, age

Row (record): Actual entity information

Primary key: A column that uniquely identifies a row of records

Navicat use of graphical tools:

Navicatformysql is a common MySQL client tool that can be downloaded from the Internet for 30 days of free use.

MySQL data type:

Data type

Occupied bytes

Range of values

tinyint

1 bytes

-128~127

smallint

2 bytes

-32768~32767

Mediumint

3 bytes

-8388608~8388607

Int

4 bytes

Range -2147483648~2147483647

bigint

8 bytes

+-9.22*10 18-Time Square

Float (m,d)

4 bytes

Single-precision floating-point type, m total number, D-Decimal

Double (m,d)

8 bytes

Double-precision floating-point, m total number, D-Decimal

Decimal (M,D)

Decimal is a floating-point number stored as a string

CHAR

0-255 bytes

Fixed length string

VARCHAR

0-255 bytes

Variable length string

Tinyblob

0-255 bytes

A binary string of no more than 255 characters

Tinytext

0-255 bytes

Short text string

Blob

0-65535 bytes

Long text data in binary form

TEXT

0-65535 bytes

Long Text data

Mediumblob

0-16777215 bytes

Medium-length text data in binary form

Mediumtext

0-16777215 bytes

Medium-Length text data

Logngblob

0-4294967295 bytes

Large text data in binary form

Longtext

0-4294967295 bytes

Maximum text data

VARBINARY (M)

A fixed-length byte string that allows a length of 0-m bytes

Length of Value + 1 bytes

BINARY (M)

M

A fixed-length byte string that allows a length of 0-m bytes

SQL language:

SQL full name is a Structured Query Language (structuredquerylanguage). The use of SQL can be database additions and deletions to check and change. The basic core content of the learning database is SQL.

The SQL classification is as follows:

Data definition language (ddl:datadefinitionlanguage)

Data manipulation Language (dml:datamanipulationlanguage)

Data Query Language (dql:dataquerylanguage)

Data Control Language (dcl:datacontrollanguage)

First, DDL

Database Definition Language:

Create a database

CreateDatabase database name;

Working with databases

Use database name;

Deleting a database

Dropdatabase database name;

dropdatabaseifexists database name;

Create a table

CreateTable Table Name (

Column name data type constraints,

...

);

Primary key: PrimaryKey

Auto Growth: auto_increment

Delete a table

droptable table name;

droptableifexists table name;

Practice:

Create a my_db database

In my_db, create student tables that contain numbers (primary key, self-increment), name, age, and address

Second, DML

Data additions

Insertinto table name (column 1, column 2 ...) Values (value 1, value 2 ...)

Example:

--Insert a row of records

Insertintostudent (name,age,gender,address)

Values (' Zhang San ', 20, ' Male ', ' Hubei Wuhan ');

--Inserting multiple rows of records

Insertintostudent (name,age,gender,address)

Values

(' John Doe ', 20, ' Male ', ' Hubei Wuhan '),

(' Li Four ', 22, ' female ', ' Beijing '),

(' Li Xiaoxi ', 25, ' female ', ' Beijing '),

(' Li 44 ', 27, ' Female ', ' Shanghai ');

--inserting data from a table into other tables

Insertintostudent2 (name,age,gender,address)

Selectname,age,gender,addressfromstudent;

Attention:

1. Automatic growth column do not add data

2, the number of columns, type and order to the same value

Data modification

Update table name set column 1= value 1, column 2= value 2where condition

Example:

--Update Li Xiaoxi's address for Chengdu, Sichuan

Updatestudentsetaddress= ' Sichuan Chengdu '

Wherename= ' Li Xiaoxi ';

--Update multiple columns

Updatestudentsetaddress= ' Sichuan Chengdu ', age=18

Wherename= ' Li Xiaoxi ';

Data deletion

Deletefrom table name Where condition

Example:

--Delete id=2 's students

deletefromstudentwhereid=5;

Practice:

Add 5 rows of records to the student table,

The age of the student with the update ID 5 is 22

Delete a student with ID 3

Iv. DQL

Querying all data

Select*from table;

Querying all data for a specified field

Select column 1, column 2from table;

Where Condition Query

Select*from table Where Condition

=,! =, in, Notin, and, or

Example:

--Query all rows for all columns

Select*fromstudent;

--Query some columns

Selectname,addressfromstudent;

--Add a Where Condition Query ID 2 student

select*fromstudentwhereid=2;

--The query ID is not 2

select*fromstudentwhereid!=2;

--Query the address for students in Wuhan, Hubei and Chengdu, Sichuan

select*fromstudentwhereaddress= ' Hubei Wuhan ' oraddress= ' Sichuan Chengdu ';

--Query for men over 20 years of age

Select*fromstudentwhereage>=20andgender= ' man ';

--Query the address for students in Wuhan, Hubei and Chengdu, Sichuan 2

Select*fromstudentwhereaddressin (' Hubei Wuhan ', ' Chengdu, Sichuan ');

--Query address not students in Wuhan, Hubei and Chengdu, Sichuan

Select*fromstudentwhereaddressnotin (' Hubei Wuhan ', ' Chengdu, Sichuan ');

Remove Duplicate data

Distinct keywords

selectdistinct column from table;

Example:

--Query student's address, not repeat

Selectdistinctaddressfromstudent;

--Set alias for column

Selectid number, name, age, gender gender, address fromstudent;

--query for girls aged between 20 and 25

Select*fromstudentwhereage>=20andage<=25andgender= ' Women ';

Select*fromstudentwhereagebetween20and25andgender= ' Women ';

--inquire about the boys in Beijing or Wuhan

Select*fromstudentwhereaddressin (' Beijing ', ' Hubei Wuhan ') andgender= ' female ';

Sort queries

Select*from table by Column DESC\ASC

Example:

--Sort by age ascending

select*fromstudentwheregender= ' Male ' orderbyageasc;

--Descending

Select*fromstudentorderbyagedesc;

Paging Query

LIMITN; Returns a row of length n

Limitn,m; Returns a row from n position with a length of M

SELECT*FROMTB_NAMELIMIT5; display the first 5 records of the result

select*fromtb_namelimit0,5; sector query

Example:

--Sort by age ascending

select*fromstudentwheregender= ' Male ' orderbyageasc;

--Descending

Select*fromstudentorderbyagedesc;

--Limit the number of rows by limiting the first 5 rows

SELECT*FROMSTUDENTLIMIT5;

--Through the Limit Paging query first page No. 0 to 5th Row, the second page 6th line to 10th line ...

select*fromstudentlimit0,5;

select*fromstudentlimit5,5;

select*fromstudentlimit10,5;

Practice:

The oldest male student

The youngest five female students

Aggregation functions

Sum: Sum

Averaging: Avg

Maximum value: Max

Minimum value: Min

Quantity: Count

Example:

Selectsum (col_name) fromtb_name;

Group queries

GroupBy Grouping columns

Where to filter for criteria before grouping

Having a conditional filter after grouping

Where-->groupby--->having

Example:

--Using aggregate functions

Selectsum (age), average age of AVG (ages),

Max age, Minimum age, COUNT (*) number of students fromstudent;

--Number of students in different places

Selectaddress Birthplace, COUNT (*) number of fromstudentgroupbyaddress;

--the number of male and female students

Selectgender Gender, COUNT (*) Number of people, AVG (age) average ages fromstudentgroupbygender;

-A place of origin for more than 5 people

Selectaddress Birthplace, COUNT (*) Number fromstudent

Groupbyaddresshavingcount (*) >=5;

--Age totals more than 200 of the sex

Selectgender sex, sum (age) Total ages fromstudent

Groupbygenderhavingsum (age) >200;

Fuzzy query

Wildcard characters:

% matches any number of characters

_ Match one character

Like keyword

Example:

--Find all the people surnamed Li

Select*fromstudentwherenamelike ' Li% ';

Select*fromstudentwherenamelike '% large ';

Select*fromstudentwherenamelike ' Li _ ';

MySQL database basics and SQL language primer