MySQL data type/attributes/additions and deletions (14)

MySQL data type

• Date type

Date

The date data type is responsible for storing day information (1000-01-01 to 9999-12-31)
You can use numeric and string insertions (20180809 or "2018-08-09") to use separators other than numbers or letters

datetime

The datetime data type is responsible for storing a combination of date and time information (1000-01-01 00:00:00 to 9999-12-31 23:59:59)
You can use numeric and string insertions (20180809122556 or "2018-08-09 12:25:56")

time

The time data type is responsible for storing temporal information ( -838:59:59 through 838:59:59)

Timestame

Timestame automatically gets the current date and time

• Numeric data type

bool and Boolean

BOOL and Boolean are just aliases for tinyint (1)

tinyint

The tinyint data type is the integer range of MySQL rank fifth (unsigned value: 0~255 signed value: -128~127)

smallint

The smallint data type is the integer range of MySQL rank fourth (unsigned value: 0~65535 signed value: -32768~32767)

Mediumint

The Mediumint data type is the third integer range of MySQL rank (unsigned value: 0~16777215 signed value: -8388608~8388607)

int

The int data type is the second integer range of MySQL rank (unsigned value: 0~4294967295 signed value: -2147483648~2147483647)

bigint

The bigint data type is the first integer range of MySQL rank (unsigned value: 0~18446744073709511615 signed value: -9233372036854755808~9233372036854755807)

Decimal

The decimal data type is a floating-point number stored as a string (unsigned value: 2.2250738585072014e-308~1.7976931348623157e+308 signed value: -1.7976931348623157e+308~ 2.2250738585072014E-308)

Double

The double data type is a dual-precision floating-point number (unsigned value: 2.2250738585072014e-308~1.7976931348623157e+308 signed value: -1.7976931348623157e+308~ 2.2250738585072014E-308)

float

Float data type is a single-precision floating-point number (unsigned value: 1.175494351e-38~3.402823466e+38 signed value: -3.402823466e+38~-1.175494351e-38)

Float Type example: Double (m,d) M stores the number of values, the number of bits in D decimals

• String data type

Char

The Char data type provides a fixed length for MySQL, supports a maximum of 255 characters, and if the inserted characters are less than the length specified, the remainder is filled with spaces

varchar

The varchar data type is a variable length of MySQL, supports 65,536 characters in length, and retains a blank part of the trailer

Longblob (longtext non-binary)

Longblob data type is mysql in binary string representation, support length of 4,294,967,259 characters

Mediumblob (mediumtext non-binary)

The Mediumblob data type is a MySQL binary string representation that supports 16,777,215 characters in length

blob (text non-binary)

The BLOB data type is a MySQL binary string representation that supports 65,535 characters in length

Tinyblob (tinytext non-binary)

The Tinyblob data type is a MySQL binary string representation that supports 255 characters in length

MySQL Properties

• Primary key

Primary key, only one occurrence

Create an auto-increment primary key

CREATE Table Xiu (    null  auto_increment)

Create a single field primary key

CREATE TABLE Xiu (    ID varchar (255),    key(ID))

Create a multi-field primary key

CREATE TABLE Xiu (    ID varchar(null, age  varchar (null, Key (ID, age))      

• NOT NULL

Cannot be empty

• Auto_increment

Automatic growth

• Binary

Case sensitive

• Unique

Only one occurrence, and null can occur more than once

• National

Ensure that the default character set is used

• Default

To ensure that no value is available, assign a constant value

• Zerofill

Leading, filled with 0

• Unsigned

Set minus sign

manipulating databases

• View Database

show databases; // View all Databases

• Add a Database

Create Database Xiu; // Add a database named Xiu

• Deleting a database

Drop database Xiu; // to delete a database named Xiu

• Default database

 use Xiu; // default to Xiu database

Manipulating database tables

• Add Table

CREATE Table Xiu (    key auto_increment,// primary key, self-increment    null,   cannot be null    // cannot be null)

• Query table

Show create table Xiu; // Query Xiu Table

• Renaming

Rename table Xiu to Kang; // Renaming the Xiu table to the Kang table

• Delete a table

drop table Xiu; // Delete Xiu Table

Manipulating database Fields

• Add a column

ALTER TABLE XIU add column Kang varchar (10);

• Modify a column

varchar (TEN) null;

• Delete a column

ALTER TABLE Xiu drop birdate;

• Rename a column

ALTER TABLE Kang change age sex varchar (10);

Operation of the data

• Add a piece of data

Insert into Xiu values (1, "name", "Age");

• Specify a field to add a value

Insert into Xiu (name,age) VALUES (' name ', ' age '); // because the primary key is self-increment, you can not add the ID field

• View data

SELECT * from Xiu; // * represents querying all fields, or you can specify field queries

• modifying data

Update Xiu set name= "user" where id=1; // Modify the value of the name field of the Id=1 data to "user"

• Delete data

Delete from Xiu where id = 1; // delete data with ID 1

Add Query

• fields are equal to values

=

(Query the data for the Xiu table, the condition is name= "user")

SELECT * from xiu WHERE name = "User";

• Two conditions are also met

and

(Query the data for the Xiu table while satisfying both conditions name= "user", age=18)

SELECT * from Xiu where name= "user" and age=18;

• Meet any conditions

or(Query the data of the Xiu table, satisfy one of the conditions name= "user", age=18)

SELECT * from Xiu where name= "user" or age=18;

• The field is equal to the value

inch(Query the data of the Xiu table to meet any of these conditions age=24,age=18)

SELECT * from Xiu where age in (24,18);

• The field is not equal to the value

Not in

(Query the Xiu table for data that does not meet any of these conditions age=24,age=18)

SELECT * from Xiu where age is not in (24,18);

• The field satisfies the value, the primary judge is the null value

is(Query the data for Xiu table, condition is age=null)

SELECT * from Xiu where-is null;

• The field does not satisfy the value is not value

(Query the data for Xiu table, condition is age!=null)

SELECT * from Xiu where-is not null;

• The fuzzy query requires a wildcard character

Like

' _ ': denotes the account of one

'% ': represents 0 or more digits

(Query the data for the Xiu table, condition is the second character of the name value is s)

SELECT * from Xiu the Where name like ("_s%");

• To imitate a query, you need a wildcard ' _ '% '

Not-like

(Query the data for the Xiu table, the second character of a name value is not s)

SELECT * from Xiu where name is not a like ("_s%");

• Scope Query

Between

(Query the data for the Xiu table, the range of values for which the condition is age is 10-30)

SELECT * from Xiu where age between and 30;

• Remove Duplicates

Distnct

(Query the Name field of the Xiu table, with the condition that duplicate name values are removed)

Select distinct name from Xiu;

Sort

• Ascending

ORDER BY ... asc

(Query the data of the Xiu table, sorted by age from small to large)

SELECT * from Xiu order by age ASC;

• Descending

ORDER BY ... Desc

(Query the data for the Xiu table, sorted by age from big to small)

SELECT * from Xiu order by age Desc;

InterceptLimit(Query the data of the Xiu table, get the first data starting from the three data)

SELECT * from Xiu limit 0, 3;

Aggregation Functions

• Avg Average
• Max Maximum Value
• Min min value
• Sum and
• Count (empty value not counted) number of data bars

(Gets the number of data in the Name field in the Xiu table)

Count (name) from Xiu;

GroupingGroup bycannot add aggregate function directly after where(Group the Xiu table by the age of the field and get the maximum ID for each group)

Max (ID) from Xiu Group by age;

Filter having(gets the maximum ID for each group, and the ID cannot be 2)

SELECT * from Xiu GROUP BY age has the id! = 2;

• The difference between where and having

Where before group by, the data for each row has been after group by, and the data for each group FOREIGN KEY Constraints  ·········································································· Continue to write in ..... foreign key (from table field) References Main Table name (primary table primary key)query for foreign keysShow create TABLE table namedeletion of foreign keysALTER TABLE Name drop FOREIGN key foreign key nameaddition of foreign keysALTER TABLE name add foreign key (from table field) References Main Table name (primary table primary key) Master Table UpdateOn update Primary Table DeleteOn Deleteallowable CascadeCascade if the primary table is updated or deleted, the corresponding action is also taken from the tableset NULL if the table name is deleted or updated, then it becomes null from the tablerestrict rejecting the associated operation default value for the primary table⊙⊙ AliasesAs nameAs can be omitted⊙⊙ Multi-tableSelect a. field 1,b. field 2 from table name 1 as a, table name 2 as B⊙⊙ Sub-querydivided by Locationwhere typeFrom typeexists type understanding (judging existence)According to the results ofA value: The scalar subquery can be used = < > ! =a column of values: Columns queryin satisfies any one of the conditions in the collectionnot in does not satisfy the conditions in the collection=any equivalent in<any is less than the maximum value in the collection>any is greater than the minimum value in the collection!=any is not equal to the value in the collection=all equals all values in the collection<all less than the minimum value in the collection>all is greater than the maximum value in the collection!=all equivalent to not inRow Valueneed to add (field 1, Field 2 ...) The number of fields is related to the result of the subquery querymulti-row multi-columnyou need to add an alias to the table⊙⊙ built-in functionsMathematical MethodsFloor (x): Returns the integer portion of xround (x, y) is rounded, y, and the number of decimal digits that are retained. stringLength (str): A string is calculated in bytes. substring (str,b,e): B Subscript (starting from 1). B: Start position e: Number of interceptionsubstr (str,x): Intercept from subscript x. Lower (str): Turn lowercaseUpper (str): Turn capitalizationTrim (str): Remove all spacesLTrim (str): Remove left spaceRTrim (str): Remove the right spaceTime and datenow (): Current system timeDate_format (); the formatting of time%Y: Four-digit year%Y: two-digit year%M: English month%M: number of monthsdayname (): Returns the day of the week of the current dateDayOfWeek (): Returns the day ordinal of a week, with a result of 1-7, starting from Sunday. dayofyear (): Returns the number of days of the year. DayOfMonth (): Number of days in one monthsDateDiff (DATE1,DATE2): Calculates the time difference between two dates, in days. ⊙⊙ Connection query: operation of multiple TablesINNER JOIN INNER JOIN (Inne can be omitted)on indicates that the Cartesian set is not generated when connectedleft table name inner join right table name on conditionwhere the representation of Mr. Descartes set in the filteringleft table name inner join right table name where conditionthe using premise has the same field nameleft table name inner join right table name using (field)External links outer join (outer can be omitted) outer joins must add conditionsthe left connection is not successful, the left table data is preserved, and the right table is replaced with a null valueleft Table name outer join right table name on/using conditionthe right connection is not successful, the left table is replaced with a null value, and the right table data is reservedleft Table name outer join right table name on/using conditionfully connected full joinMySQL temporarily does not supportYou can use the (left connection) union (right join) to achieve the same effectNatural Connection Natural JoinThe natural join has the same field, similar to the usingThe natural left join has the same field, similar to the usingNatural right joins have the same fields, similar to the usingDescartes Connectionno conditions added when connectingdata from one table is connected to all data in another table

MySQL data type/attributes/additions and deletions (14)