<>MySQL Basics

<>MySQL Basic principles and common sentences

<> Common relational databases

* IBM of DB2;
* Oracle Oracle,MySQL;
* Microsoft SQL and Access;
* Sybase of Sybase;
<>MySQL Common storage engines

* InnoDB Storage engine
* MyISAM Storage engine
* MEMORY Storage engine

<> value type

The following is a detailed table of data types ;

<> Composite type

<> Database user management

Ø connect MySQL
​ mysql -h Host address -u user name -p User password
Ø New user
GRANT SELECT on database .* to user name @ Login Host identified by “ password ”
Ø Modify user password
UPDATE mysql.user SET authentication_string = PASSWORD('123456'),
password_expired= 'N' WHERE User = 'shopdb' AND Host = 'localhost';
<> Database operation

Ø view the database
SHOW databases;
Ø Create database
CREATE DATABASE databaseName;
Ø Working with databases
USE databaseName;
Ø Delete database ​
DROP DATABASE databaseName;
<> Table operation

Ø Create table
CREATE TABLE user( id int(10) unsigned not null auto_increment, name varchar(25
), sex varchar(5), age int(10), password varchar(25), primary key(id))engine=
Innodb;
Ø View table structure
DESC tableName
Ø Copy table

​ The first way : Copy table structure , data , Primary key , Indexes
# Copy table structure , Primary key , Indexes , Execute command : CREATE TABLE new_table like old_table; # insert data , Execute command : INSERT
into new_table SELECT * FROM old_table;
​ The second way : Copy table structure , data , Cannot copy primary key , Indexes
# Copy table structure , data , Execute command : CREATE TABLE new_table SELECT * FROM old_table;
# Copy table structure , Do not copy data , Execute command : CREATE TABLE new_table SELECT * FROM old_table WHERE 0;
Ø Temporary and memory tables
# Create temporary table , Command execution :(temporary temporary ) CREATE temporary TABLE tmp1(id int not null);
# Create memory table , Command execution : CREATE TABLE tmp2(id int not null) ENGINE=MEMORY;
<> Data operation

Ø Insert and query

Insert a piece of data into the database , Commands are available :
INSERT INTO Table name ( Field name , Field name ) VALUES( value , value );
Or you can not specify the fields in the table , But the values are inserted in the order of the fields , Commands are available :
INSERT INTO Table name VALUES( value , value );
Query data , Commands are available :
SELECT * FROM Table name ;
Or query the specified field , Commands are available :
SELECT id FROM Table name ;
use WHERE Condition statement to query by condition , Use a column or columns as query criteria , Commands are available :
SELECT * FROM Table name WHERE id=10; SELECT * FROM Table name WHERE id=10 and name=’ Xiao Ming ’;
Ø Modification record

stay MySQL Modify and use keywords in UPDATE, Execute command .
UPDATE Table name SET field = value , field = value WHERE condition UPDATE user SET name=’ Xiao Ming ’,sex=’ male ’ WHERE id = 4
;
Ø Delete record

stay MySQL Can be used to delete data in DELETE keyword , Execute command :
DELETE FROM Table name WHERE condition DELETE FROM user WHERE id=4;
Ø Sort query results

stay MySQL In the database , use ORDER BY Sort , Use keywords ASC Sort in ascending order , Use keywords DESC Sort in descending order ,:

(1)ORDER BY column ASC: Sort in ascending order by a field ,ASC Can be omitted ;
SELECT * FROM user ORDER BY id ASC; # perhaps SELECT * FROM user ORDER BY id;
(2)ORDER BY column DESC: Sort by a field in descending order ,DESC Can not be omitted ;
SELECT * FROM user ORDER BY id DESC;
(3)ORDER BY column1,column2 DESC: Sort descending by multiple fields ;
SELECT * FROM user ORDER BY sex,age DESC;
Ø Group query results

GROUP BY Grouping query results is to group query results by 1 Group one or more fields , Those with the same field value are a group ,GROUP BY Can be used for a single field and multiple fields .
SELECT * FROM user GROUP BY sex;
group_concat( Field name ) Can be used as an output field , After grouping , According to the grouping results , use group_concat() To place a collection of values for a field in each group .
SELECT sex,group_concat(name) FROM user GROUP BY sex;
Ø Set grouping conditions

HAVING Is a conditional expression used to set grouping conditions , Used to specify some conditions after grouping queries to output query results ,WHERE Statement to filter records before aggregation , That is to say, it acts on GROUP
BY and HAVING Before sentence , and HAVING Clause to filter group records after aggregation ,HAVING Only for GROUP BY.
SELECT sex,count(sex) FROM user WHERE age > 15 GROUP BY sex HAVING count(sex)>2
;
Ø Limit the number of queries

LIMIT Is used to limit the number of queries , Commonly used for paging statements ,LIMIT Clause can be used to enforce SELECT Statement returns the specified number of records .LIMIT
Accept one or two numeric parameters , Parameter must be an integer constant .

(1) If only one parameter is given , It represents the maximum number of record rows returned ;
SELECT * FROM user LIMIT 6; # Before retrieval 6 Row record
(2) If two parameters are given , The first parameter specifies the offset of the first return record line , The second parameter specifies the maximum number of record rows to return , The offset of the initial record line is
0( instead of 1);
SELECT * FROM user LIMIT 2,5; # From page 2 Data start , Retrieved 5 Pieces of data
<> Field operation

Ø Set primary key
# When creating tables , use PRIMARY KEY Add primary key : CREATE TABLE tbl_name ([ Field description omitted ...], PRIMARY KEY(
index_col_name)); CREATE TABLE student( id int not null, name varchar(255) not
null, no int not null, primary key(id)) ENGINE=Innodb DEFAULT CHARSET=utf8;
Ø Set composite primary key

A composite primary key is a component consisting of multiple fields , Like the key to a treasure , Often divided into two or more , When two keys are inserted at the same time , To open the door to the treasure , The same is true for composite primary keys , Determine the unique identifier by using multiple fields as a composite primary key .
CREATE TABLE person( id int not null, name varchar(255) not null, job varchar(
255) not null, primary key(id,name)) # Set composite primary key ENGINE=InnoDB DEFAULT CHARSET=utf8;
Ø Add field
# Add a mobile number (phone) New field to user Exterior interior , Data type is string type . ALTER TABLE user add phone varchar(25)
not Null;
Ø Change field type
# You can modify the data type of a table field , Transfer mobile phone number (phone) Change string type to integer (int); ALTER TABLE user modify phone int(25
) not Null;
Ø Field rename

For existing table structures , If you want to rename the fields in the table , Need to use alter table To modify the fields in the table , The format is as follows .
ALTER TABLE < Table name > change < Field name > < Field new name > < Type of field > ALTER TABLE user change phone
telephoneint(25);
<> Basic query syntax

​ MySQL Query using keywords SELECT To query ,SELECT Statement basic syntax format :
SELECT Query content FROM Table name WHERE expression GROUP BY Field name HAVING expression ORDEY BY Field name LIMIT Number of records
Ø Conditional query filtering

Conditional query filter keyword :**AND,OR,IN,NOT IN,IS NULL,IS NOT NULL,BETWEEN AND;** Query and filter by keyword .

​ (1)AND( And ): use AND When querying , The query data must meet the requirements ;
SELECT * FROM user WHERE age = 20 and name=‘kevin’;
​ (2)OR( or ): use OR When querying , The queried data can be queried only if any condition is met ;
SELECT * FROM user WHERE age = 20 or name=‘kevin’;
​ (3)IN( Within range ): use IN When querying , The queried data is within this range ;
SELECT * FROM user WHERE id in (3,5,7);
(4)NOT IN( Out of range ): use NOT IN When querying , The queried data is not within this range ;
SELECT * FROM user WHERE id not in(3,5,7);
(5)IS( Empty ): use IS
NULL When querying , Used to query when a field is empty is
null, Not available "=null", because mysql Medium null Not equal to any other value , It doesn't mean another null, The optimizer will "=null" The query of is filtered out without returning any data ; Used when querying a field that is not empty is
not null.
SELECT * FROM user WHERE name is null;
(6)BETWEEN AND( stay … section ): use BETWEEN AND When querying , The queried data is in this range ;
SELECT * FROM user WHERE age between 10 and 20;
Ø Fuzzy query filtering

​ Fuzzy query filtering using keywords LIKE Make a query ;

(1)LIKE ’ Zhang %’: use LIKE Query the data of this field beginning with sheet ;
SELECT * FROM user WHERE name like ‘ Zhang %’;
(2)LIKE ’ % bright ’: use LIKE Query the data ending in the field ;
SELECT * FROM user WHERE name like ‘% bright ’
(3)LIKE ’ % bright %’: use LIKE Query the data contained in this field ;
SELECT * FROM user WHERE name like ‘% bright %’;
Ø Field control query filtering

​ Field control query filtering can be used DISTINCT Remove repeated filtration ,AS Set alias .

​ (1)DISTINCT: Remove duplicate column values ;
SELECT distinct age FROM user;
(2)AS: You can set column aliases , It can also be omitted AS To set keywords ;
SELECT name as full name FROM user; # perhaps SELECT name full name FROM user;
<> Aggregate function ( Group function )

Ø AVG() function

MySQL Database AVG Function is used to calculate the average value of a column , For example, it can be used in the average score , Average salary , Use of average age and other scenarios .
SELECT avg(age) FROM user;
ØCOUNT() function
# MySQL Database COUNT() The aggregate function is used to calculate the number of records in a table or the number of values in a column , Calculated by SELECT Statement assignment , For example, to get user In the table age > 20
Number of . SELECT count(*) FROM user WHERE age >20;
Ø MAX()/MIN() function

​ MySQL Database MAX() Maximum value in data selected by aggregate function ,MIN() Minimum value in data selected by aggregate function , For example, you can obtain the maximum or minimum value of age .
SELECT max(age) FROM user ; SELECT min(age) FROM user;
ØSUM() function

​ MySQL Database SUM() Aggregate function is used to calculate the sum of a column that meets the condition , For example, you can calculate the sum of ages , Or you can calculate the sum of the boys' ages , Aggregate functions can be aliased .
SELECT sum(age) FROM user ;

Technology
©2019-2020 Toolsou All rights reserved,
C++ of string of compare usage Python Study notes ( one )evo Tool usage problems ——Degenerate covariance rank, Umeyama alignment is not possibleRISC-V_GD32VF103-TIMER0 timer interrupt java Array subscript variable _Java Basic grammar : array be based on stm32 Control four-wheel trolley motor drive ( one ) be based on redis Design of liking function Software engineering career planning mysql Query random data by conditions _MySQL Random query of several qualified records centos7 install RabbitMq