(under Mac System) MySQL Getting Started

1, after the installation of MySQL and service startup, System Preferences have started the MySQL service button to see that running is available

2. Access MySQL via terminal

Go to the MySQL path first (the default installation does not configure the environment variable): cd/usr/local/mysql/bin/

Access MySQL:./mysql

　　

See Welcome to the MYSQL said to have logged in, the command entered below are SQL commands, so must be with a semicolon, otherwise prompt SQL statement error.

Exit Mysql:exit;

　　

3. Build database, check database, delete database

Creating a database: Create databases Testmysql;

Query database: show databases;

Delete databases: drop database testmysql;

　　　　

Select database: Use Testmysql;

　　

4. mysql CREATE table

The table Creation command requires:

• Name of the table

• Field name

• Define each field (type, length, etc.)

Syntax: CREATE TABLE table_name (column_name column_type);

Create a table with a command prompt (select database First, use Testmysql;)

5. mysql Delete table

Syntax: DROP TABLE table_name;

Delete the table you just created from the command line: drop table tutorials;

6. mysql Insert Table

Grammar:

INSERT into table_name (field1, Field2,... fieldn)                       VALUES                       (value1, value2,... Valuen);

To insert data from the command line:

7. mysql query form

Grammar:

SELECT field1, Field2,... fieldn table_name1, table_name2 ... [WHERE Clause] [OFFSET M] [LIMIT N]

Querying data from the command line

8. mysql Update table

Grammar:

UPDATE table_name SET field1=new-value1, Field2=new-value2[where Clause]

To update data from the command line:

9. mysql Delete data

Grammar:

DELETE from table_name [WHERE Clause]

To delete data from the command line:

10. mysql table field type

It is important that the fields in the table are correctly defined in the overall database optimization. We should only use the size of the type and field that really needs to be used; If you know that only 2 characters are used, you will not use the 10 character justifies to define a field. These types of fields (or columns), also known as data types, are stored in these fields as data.

MySQL uses many different data types, which are generally divided into three categories: numeric, date, time, and string type.

Numeric data types

MySQL uses all the standard ANSI SQL numeric data types, so these definitions look familiar if you have access to different database systems before learning MySQL. The following is a list of common numeric data types and their descriptions:

• INT-A normal-sized integer that can be signed. If it is signed, it is allowed to range from 2147483648 to 2147483647. If it is unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 bits.

• TINYINT-A very small integer that can be signed. If it is signed, it allows a range from 128 to 127. If it is unsigned, the allowable range is from 0 to 255, you can specify a width of up to 4 digits.

• SMALLINT-A small integer that can be signed. If there are symbols, the allowable range is 32768 to 32767. If unsigned, the allowable range is from 0 to 65535, you can specify a width of up to 5 bits.

• Mediumint-a medium-sized integer that can be signed. If there are symbols, the allowable range is 8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215, you can specify a width of up to 9 bits.

• BIGINT-A large integer that can be signed. If there are symbols, the allowable range is 9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 bits.

• Float (m,d)-unsigned floating-point numbers cannot be used. You can define the display length (M) and the number of decimal digits (D). This is not required, and the default is 10, 2. Where 2 is the number of decimal places, and 10 is the total number of digits (including decimals). Decimal precision can be up to 24 floating-point.

• Double (m,d)-unsigned double-precision floating-point numbers cannot be used. You can define the display length (M) and the number of decimal digits (D). This is not required, the default is 16, 4, where 4 is the number of decimal places. Decimal precision can reach a double of 53 bits. Real is a double synonym.

• DECIMAL (M,D)-the non-compressed floating-point number cannot be unsigned. After unpacking the decimal, each decimal corresponds to one byte. It is necessary to define the display length (M) and the number of decimals (D). Numeric is a synonym for decimal.

Date and Time type

The date and time data types for MySQL include:

• Date-dates in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30, 1973 will be stored as 1973-12-30.

• DateTime-Date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 to 9999-12-31 23:59:59. For example, December 30, 1973 3:30, will be stored as 1973-12-30 15:30:00.

• TIMESTAMP-1970 the time stamp between midnight January 1, at some point in the year 2037. This looks like the preceding datetime format, without just the hyphen between the numbers; December 30, 1973 3:30 P.M. will be stored as 19731230153000 (YYYYMMDDHHMMSS).

• Time-stored in HH:MM:SS format.

• Year (M)-Stores the years in 2-bit or 4-bit numeric format. If the length is specified as 2 (for example, year (2)), the years can be 1970 to 2069 (70?69). If the length is specified as 4, the year range is 1901-2155 and the default length is 4.

String type

Although the numbers and date types are interesting, storing most of the data can be a string format. The following is a list of string data types that are common in MySQL.

• CHAR (M)-a fixed-length string is a length of 1 to 255 characters long (for example: CHAR (5)) and the right space is stored to fill the specified length. The limit length is not required, it defaults to 1.

• VARCHAR (M)-variable-length string is the number of characters between 1 and 255 in length (higher version of MySQL is more than 255); For example: VARCHAR (25). When you create a varchar type field, you must define a length.

• The maximum length of a BLOB or TEXT-field is 65,535 characters. BLOBs are "binary large objects" and are used to store large binary data, like or other types of files. Defined as text field also holds a large amount of data; The difference between the two is that sorting and comparing the data stored on the BLOB is case sensitive, while the text field is case insensitive. You do not specify the length of the blob or text.

• Tinyblob or Tinytext-blob or text column with a maximum length of 255 characters. The length of Tinyblob or tinytext is not specified.

• Mediumblob or Mediumtext-blob or the text column has a maximum length of 16777215 characters. The length of Mediumblob or mediumtext is not specified.

• The Longblob or Longtext-blob or text column has a maximum length of 4294967295 characters. The length of Longblob or longtext is not specified.

• Enum-enumeration, which is a peculiar list of terms. When defining an enum, to create a list of its values, these are the items that must be used for the selection (or it can be null). For example, if you want a field to contain "a" or "B" or "C", you can define the enum as an enum ("A", "B", "C") and only those values (or null) to fill this field.

(under Mac System) MySQL Getting Started