MySQL primary knowledge (i) basic attribute Chapter

This is the first time I started learning MySQL notes may be sloppy but it's easy to understand, messy dot

What new things will be added at any time in the future

MySQL's basic attribute chapter



The SQL language consists of 4 parts
Data definition Language (DDL) is used to define and manage objects, such as databases, data tables
Example: CREATE, DROP, ALTER, etc.
Data manipulation Language (DML) Operations database objects contain data.
Example: Insert (insert), UPDATE (modify), delete (delete)
Data Query Language (DQL)
For example: SELECT statement
Data Control Language (DCL) is used to control permissions on database object operations
Example: GRANT, COMMIT, rollback, and so on

In a DOS operation, it is used when the end command executes multiple commands.

Show database show databases;
Switch database ues DataOne; (DataOne is the database name)

To enter the database when creating tables and using tables;
The process for creating a database and creating a table in it is:
Create Database NewData charset UTF8;
Use NewData;
CREATE TABLE NewTable (
ID int PRIMARY KEY NOT NULL auto_increment,
Name Char (a) NOT null default ' ',
Sex enum (' Male ', ' female '),
Hobby set (' Sing ', ' swim ', ' die ')
);

When creating a database to set the font encoding to prevent garbled and name the database name can not be used with pure digital symbols and system keywords and because the Linux system is case-sensitive so that the database name preferably lowercase and not more than 64 bits


Desc newtable;
Is the structure of the query table, to see how each of his head is set up just like the ID in the NewTable table. What are the requirements of the respective storage data for the name sex hobby?



Type size range (signed) range (unsigned) use
Tinyint 1 bytes (-128,127) (0,255) Small integer value
SmallInt 2 bytes (-32 768,32 767) (0,65 535) Large integer value
Mediumint 3 bytes (-8 388 608,8 388 607) (0,16 777 215) Large integer value
int or integer 4 bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295) Large integer value
BIGINT 8 Bytes (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) Maximal integer Value

Commonly used is the int tinyint smallint
Setting a reasonable type of shaping can improve the search efficiency of the database and save memory



Decimal This is a characteristic example of a floating-point type:
Create NewTable (
Money Decimal (5,2)
);
Create a table inside the money Max is the 999.99 decimal parameter is 1 total number, 2 has a few decimals this is very common when calculating cash.


String type of data
CHAR 0-255-byte fixed-length string
VARCHAR 0-<65535 bytes (1-2 bytes record length) variable length string
Tinyblob a binary string of 0-255 bytes and no more than 255 characters
Tinytext 0-255 bytes Short text string
BLOB 0-65 535-byte long text data in binary form
Text 0-65 535-byte long text data
Mediumblob 0-16 777 215 bytes Medium-length text data in binary form
Mediumtext 0-16 777 215 bytes Medium length text data
Logngblob 0-4 294 967 295 byte binary form of large text data
Longtext 0-4 294 967 295 byte maximum text data

The point is char varchar text

CHAR (20) fixed occupies 20 bytes out of the partial truncation advantage is that the search efficiency is high disadvantage is the memory
varchar (20) takes up space as the content changes but up to 20 bytes exceeds the truncation advantages and disadvantages and char opposite
Usually char is used instead of varchar because the level of hardware can be sacrificed a little bit of memory in maintaining program execution efficiency
Text is the case with large text data.




The difference between char and int types
char (20) really means that the input cannot be more than 20 words.
Int (3) This parenthesis is used in conjunction with the leading 0 zerofill when you have zerofill this property input 1 display 001 Input 20 display 020 But the number of inputs more than 9,991 can be displayed but there is no leading 0 this affects the input 1000 will show 1000 his cap or root According to the integer type on page 48, limit

Zerofill is a leading zero
Unsigned is to cancel the minus sign
Not null prevents input of empty data
Default defaults to 0
Example:
Create NewTable (
ID int (4) Zerofill unsigned NOT null default 0,
Name Char (a) NOT null default ' '
);

Because the search for null this data type is very unfriendly to the above, the first set is not allowed to empty the default is an empty string is to circumvent the null data type so the search display is not null but really do not show anything


The enum can only select one of the
Set in which you can choose more than the difference between multiple-choice and single-choice
Example:
Create NewTable (
Sex enum (' Male ', ' female '),
Hobby set (' Sing ', ' Basketball ', ' die ')
)
Sex can only be a single choice and hobby may be more choice of course if you enter data that is not in the option, the entire hobby entry fails

Unique unique attribute other same column data cannot be the same
Primary key primary key table can only have one primary key is usually set to the ID primary key cannot contain null
Auto_increment self-increment not to enter him, he will follow the value of the previous data growth and self-increase the best not to set the default, think of the increase in each and every time since the 0 is too contradictory
Example:
Create NewTable (
ID int unique PRIMARY key NOT NULL auto_increment,
Age int NOT NULL
)

Create a table othertable and newtable the same as the table structure
CREATE table othertable like newtable;
Copy data from newtable to Othertable
INSERT INTO othertable select * from newtable;
The combination of the two above is a complete copy of the table information.
CREATE TABLE Othertalbe select * from newtable;

The above note is to see the location of the table to be created and the location of the table to be copied


？ Enter
Bring up all MySQL commands
？ Char Enter
To recall the use of char of course, these instructions are all in English there is a problem or Baidu bar
One of the most useful is to set the word and background color instructions:
COLOR 0 A;
Or
COLOR 0 E;
The two color comparison recommended not satisfied with color+ return to their own tune

Search all information in a table
SELECT * from NewTable;
Fuzzy search
SELECT * from newtable where like '% swim '

Character Set collation rules



Now we can slightly reduce the memory limit because of the hardware upgrade so now the most commonly used universal code UTF8 in the creation of the database is to set the encoding so that the internal files of this library is the default database settings are created when this is the case
Create Database charset UTF8;

GBK encoded iOS under the Windows system is UTF8 encoded
Now the DOS window is the equivalent of a client MySQL is located on the server side
Each time a message is sent to the database under Windows System, the local GBK encoding is first converted to UTF8 encoding and then the MySQL server receives the storage and sends the UTF8 encoded information back to the client to the normal display of GBK, so first declare what encoding is used locally This ensures that the data received locally can be displayed properly like this
Set names UTF8;
This is what the client is required to declare to the server what kind of encoding server is needed at the time of sending it to the corresponding to normal display


Other commands about the character set
View statements that create student tables
Show CREATE TABLE Stu;
See which character sets are supported by the server
Show character set;
View the character set collation, if not specified, to sort by default
Show collation;
viewing system Character Set variables
Show variables like '%character% ';

MySQL primary knowledge (i) basic attribute Chapter