Mysql login & amp; exit, create & amp; Delete & amp; select database, basic data type, create & amp; Delete table, mysql Login

Source: Internet
Author: User
Tags mysql login

Mysql login & Exit, create & Delete & select database, basic data type, create & Delete table, mysql Login

Log on to the database and run the following logon command:

shell>mysql [-h host] -u user -p [-D database]Enter password: 

-H specifies a remote host, which is not required to log on to the local database.-D specifies the database to be selected after login. If no database is specified, no database is selected. After entering the database, there will be a mysql> prompt before each command. All commands in mysql end with a semicolon. If the line feed is not completed before a command is entered, a "->" prompt is displayed.

Database exit command:

mysql>exit

Or:

mysql>quit

You can leave no extra points after the two.

Command for creating a database in mysql:

mysql>create database database_name;

Command for deleting a database in mysql:

mysql>drop database database_name;

To explicitly use (select) A database command:

mysql>use database_name;

Each time you use a database, you must use it explicitly. It is also a use command.

Before creating a database table, you must first understand the mysql data type. Mysql has three types of data: numeric, time & date, and string. This part is translated from the first link.

Value Type:

  • Int, normal integer value, divided into signed and unsigned, range:-2147483648 to 2147483647 and 0
    4294967295
  • Tinyint, a super small integer value, which can be signed or unsigned in the range of-128 to 128 and 0 to 255, respectively.
  • Smallint, small integer value, divided into signed and unsigned, the range is-32768 to 32768 and 0 to 65535 respectively
  • Mediumint, a medium-sized integer value, which can be signed or unsigned in the range of-8388608 to 8388608 and 0
    16777215
  • Bignit, a large integer value, divided into signed and unsigned, the range is-9223372036854775808
    9223372036854775808 and 0 to 18446744073709551615
  • Float (m, d), float type value, cannot be unsigned. You can set the total length of the display to m-digit numbers and d-decimal places. The default values are 10 and 2.
  • Double (m, d), double-precision floating point value, cannot be unsigned. You can set the total length of the display to m-digit numbers and d-decimal places. The default values are 16 and 4.
  • Decimal (m, d), an unwrapped decimal. It cannot be unsigned. Each decimal point corresponds to a byte, and the total length of m digits and d decimal places must be clearly defined. Numeric and decimal are synonyms.

Time & date type:

  • Date, yyyy-mm-dd format, range: 1000-01-01 to 9999-12-31
  • Datetime, yyyy-mm-dd hh: mm: ss format, range: 1000-01-01 00:00:00 to 9999-12-31
    23:59:59
  • Timestamp, which ranges from 00:00:00, January 1, January 1, 1970 to a certain time on January 1, 2037. The format is similar to datetime, but there is no hyphen. For example, at, January 1, December 30, 1973, the value corresponding to 19731230153000 (yyyymmddhhmmss)
  • Time, hh: mm: ss format.
  • Year (m), two or four-digit format. If two digits are specified, such as year (2), the data can be stored from January 1, 1970 to January 1, 2069 (70-69 ). When four digits are specified, the data can be stored from January 1, 1901 to January 1, 2155. The default value is four digits.

String type:

  • Char (m), fixed length string, length from 1 to 255, left aligned and right filled, default length is 1
  • Varchar (m), a variable-length string ranging from 1 to 255. The definition must specify a length. In fact, from the explanation on the official website (the second link), the maximum length can be 5.03 from version 65535 and later. When the length is less than or equal to 255, an additional byte is used to store the length value. If the length exceeds 255, an additional two bytes are used to store the length value.
Value Char (4) Storage Requirements Varchar (4) Storage Requirements
" '' 4 bytes " 1 byte
'AB' 'AB' 4 bytes 'AB' 3 bytes
'Abcd' 'Abcd' 4 bytes 'Abcd' 5 bytes
'Abcdefg' 'Abcd' 4 bytes 'Abcd' 5 bytes
  • Blob and text, which can store up to 65535 bytes. Blob stores data as binary arrays, which can store images, sounds, and other data. text stores data as characters. You do not need to specify the length for blob and text. For more information, see the third link.
  • Tinyblob and tinytext are stored in a maximum of 255 bytes. The remaining features are consistent with those of blob/text.
    Mediumblob and mediumtext are stored in a maximum of 16777215 bytes. Other features are consistent with blob/text.
  • Longblob and longtext are stored in a maximum of 4294967295 bytes. The remaining features are consistent with those of blob/text.
  • Enum, enumeration type, which can contain a maximum of 65535 elements. Apart from listing strings, enum can be NULL. If an invalid string is inserted, it will be replaced by an empty (") string. For more information about enumeration types, see article 4.

Command for creating a table in the database:

mysql>create table table_name (cloumn_name colume_type, colunm_name colunm_type ...);

In addition to data types, you can also add some attributes such as not null, auto_increment, and default. You can also specify the primary key, set the database engine, and set the character set. For example:

mysql>create table hotel (        -> `id` int unsinged not null auto_increment,        -> `default test` int default 0,        -> `num` char(4) not null,        -> `price` varchar(5) not null,        -> `position` varchar(30) not null,        -> `describe` text,        -> `available` enum('y', 'n'),        -> primary key(`id`));

Pay attention to the following points:

  • 'The quotation marks are the buttons Under Esc. In a mysql statement, if the table name or attribute field is the same as the system keyword when creating a table, or the name contains a blank character, you can enclose the name using backquotes, mysql only retains the content in the back quotes. At the same time, note that none of the quotation marks is correct, because mysql considers the quotation marks as the start character of the field, which does not comply with naming rules.
  • The text type cannot have default values. If the default value is required, you can specify the default value after the field.
  • Use primary key (column_name, column) to set the primary key. Separate multiple columns with commas.

To delete a table from a database:

mysql>drop table table_name;

To avoid statement errors even if no table exists, run the following command:

mysql>drop table if exists table_name;

Reference link:
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.