Log in to the database using the login command:
shell>mysql[-h host]-uuser-p[-D database]Enterpassword
-h Specifies a remote host, login local database can not use,-D is used to specify the database selected after login, if no database is specified, the database is not selected. After entering the database, there will be a mysql> prompt before each command. Commands in MySQL are strictly terminated with a semicolon (;) as a command. If a line is wrapped before a command is entered, a prompt appears.
command to exit the database:
mysql>exit
Or:
mysql>quit
These two bars can be followed without semicolons.
To create a database in MySQL command:
mysql>create database database_name;
To delete a database in MySQL command:
mysql>drop database database_name;
To explicitly use (select) a command for a database:
mysql>usedatabase_name;
Each time you use a database, you must explicitly choose to use it, as well as using the use command.
Before you create a database table, you need to understand the MySQL data type. MySQL has three main types of data: numeric type, time & date type, String type. This part of the excerpt is translated from the first link.
Numeric type:
- int, the normal integer value, is divided into signed and unsigned, ranging from 2147483648 to 2147483647 and 0 to
4294967295
- tinyint, ultra-small integer values, divided into signed and unsigned, ranging from 128 to 128 and 0 to 255
- smallint, small integer values, divided into signed and unsigned, ranging from 32768 to 32768 and 0 to 65535, respectively
- Mediumint, medium integer value, divided into signed and unsigned, ranging from 8388608 to 8388608 and 0 to
16777215
- Bignit, large integer value, divided into signed and unsigned, the range is 9223372036854775808 to
9223372036854775808 and 0 to 18446744073709551615
- Float (M, d), floating-point value, cannot be unsigned. Can be set to display the total length of m digits and D decimal places. By default, they are 10 and 2, respectively.
- Double (m,d), dual-precision floating-point numeric value, cannot be unsigned. Can be set to display the total length of m digits and D decimal places. By default, they are 16 and 4, respectively.
- Decimal (M,d), an unpackaged decimal number that cannot be unsigned. Each decimal number corresponds to one byte, and the total length m digits and D decimal numbers need to be clearly defined. Numeric and decimal are synonyms.
Time & Date Type:
- DATE,YYYY-MM-DD format, range from 1000-01-01 to 9999-12-31
- DATETIME,YYYY-MM-DD hh:mm:ss format, range from 1000-01-01 00:00:00 to 9999-12-31
23:59:59
- timestamp, which ranges from January 1, 1970 0:0 0 seconds to a time in 2037, formatted like datetime, but without hyphens. For example December 30, 1973 15:30 0 seconds corresponds to 19731230153000 (YYYYMMDDHHMMSS)
- TIME,HH:MM:SS format storage.
- Year (m), 2-bit or 4-bit format. When you specify 2 bits, such as year (2), you can store 1970 through 2069 (70-69). When you specify 4-bit, you can store 1901 through 2155, the default 4-bit length.
String type:
- char (m), fixed-length string, length from 1 to 255, left-aligned right padding, default length 1
- varchar (m), variable-length string, length from 1 to 255, the definition must specify a length. Actually from the official website explanation (the second link), from version 5.03 and later, the maximum length can be to 65535. When the length is less than or equal to 255, an extra byte is used to store the length value, longer than 255, and an additional 2 bytes for the length value.
| value |
char (4) |
Storage Requirements |
varchar (4) |
Storage Requirements |
| ” |
’ ‘ |
4 bytes |
” |
1 bytes |
| ' AB ' |
' AB ' |
4 bytes |
' AB ' |
3 bytes |
| ' ABCD ' |
' ABCD ' |
4 bytes |
' ABCD ' |
5 bytes |
| ' ABCDEFG ' |
' ABCD ' |
4 bytes |
' ABCD ' |
5 bytes |
- Blob and text, with a maximum storage of 65,535 bytes. A BLOB stores data as a binary array, storing data such as pictures, sounds, and so on, and text still stores the data as characters. You do not need to specify a length for blobs and text. Refer to the third link for details.
- Tinyblob and Tinytext, with a maximum storage of 255 bytes, with the remaining features consistent with Blob/text
Mediumblob and Mediumtext, with a maximum storage of 16,777,215 bytes, with the remaining features consistent with Blob/text
- Longblob and Longtext, with a maximum storage of 4,294,967,295 bytes, with the remaining features consistent with Blob/text
- Enum, enum type, enumeration can have up to 65,535 elements, enum-type field in addition to the enumeration string, can be null, if you insert an illegal string, the Empty (") string instead. For more information about enumeration types, see fourth link
commands for creating tables in the database:
...);
In addition to the data types, column items can also add some other properties, such as NOT NULL, auto_increment, default, and so on. You can also specify primary keys, set up the database engine, set character sets, and so on. For example:
Mysql>create Table Hotel ( -`ID' Int unsinged not NULLAuto_increment, -` default test' intdefault 0, -`Num' Char (4) not NULL, -` Price' VarChar (5) not NULL, -`position' VarChar ( -) not NULL, -`describe' Text, -`available`enum(' y ',' n '), -Primary KEY ('ID`));
There are a few points to note:
- ' Call the anti-quote, which is the key under ESC. In a MySQL statement, if the table name or attribute field is the same as the System keyword when the table is created, or if the middle name includes white space characters, you can enclose the name in inverted quotation marks, and MySQL retains only the contents in the inverted quotation mark. Also note that it is incorrect to enclose any other quotation marks, because MySQL considers the quotation marks to be the starting character of the field, which does not conform to the naming convention.
- The text type cannot have a default value. The default value can be specified after the field with the defaults.
- Use primary key (column_name, column) to set the primary key, separated by commas between multiple column items.
To delete a table in the database command:
mysql>drop table table_name;
You can use the following command if you want to make sure that even if no table exists, there is no statement error:
mysql>dropif exists table_name;
Reference Links:
Http://www.tutorialspoint.com/mysql/mysql-data-types.htm
Https://dev.mysql.com/doc/refman/5.0/en/char.html
Https://dev.mysql.com/doc/refman/5.0/en/blob.html
Https://dev.mysql.com/doc/refman/5.0/en/enum.html
Mysql Login & Exit, create & Delete & Select database, base data type, create & Delete table
