Shulou(Shulou.com)06/01 Report--

Although mysql has been acquired, but as the mainstream database on the market, we still need to learn.

MySQL has three major data types, namely, number, date / time, and string. Many subtypes are divided in more detail in these three categories:

Numeric type

Integers: tinyint, smallint, mediumint, int, bigint

Floating point numbers: float, double, real, decimal

Date and time: date, time, datetime, timestamp, year

String type

String: char, varchar

Text: tinytext, text, mediumtext, longtext

Binary (can be used to store pictures, music, etc.): tinyblob, blob, mediumblob, longblob

Log in to MySQL

When the MySQL service is already running, we can log in to the MySQL database through the client tool that comes with MySQL. First, open a command prompt and enter a name in the following format:

Mysql-h hostname-u user name-p

-h: this command is used to specify the MySQL hostname to which the client wants to log in. This parameter can be omitted from the current machine.

-u: the user name to be logged in

-p: tells the server that a password will be used to log in, and this option can be ignored if the username and password you want to log in is empty.

Mysql-D test-uroot-p is the equivalent of logging into a MySQL server. Use test

Create a new user and authorize

GRANT permissions ON database. Datasheet TO user name @ login host IDENTIFIED BY "password"

GRANT SELECT,UPDATE ON test.* TO qingqing@localhost IDENTIFIED BY '123'

Create the database:

CREATE DATABASE [IF NOT EXISTS] book

Delete database

DROP DATABASE [IF EXISTS] book

Show all databases:

SHOW DATABASES

Use a database:

USE test

View the structure of the table

DESC user

View the table creation statement:

SHOW CREATE TABLE user

Add field ALTER TABLE user ADD age TINYINT

Modify field ALTER TABLE user MODIFY age int

Modify field (also change field name) ALTER TABLE user CHANGE name username varchar (30)

Delete field ALTER TABLE user DROP age

Rename the table name RENAME TABLE user TO user1

The field type explanation of the table:

Automatic Generation and Update of timestamp in mysql

1. Automatic UPDATE and INSERT to the current time:

`ptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

two。 Automatically INSERT to the current time, but not automatically UPDATE.

`ptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

3. You cannot have two fields in a table. The default value is the current time, otherwise there will be an error.

Non-standard data types such as float,double exist in MySQL

Decimal is also available as a standard data type.

The difference is that non-standard types, such as float,double, store approximate values in DB, while Decimal holds numeric values as strings.

Another example: DECIMAL (5jue 3)

1.2345-up to 3 digits after the decimal point, so saving can be automatically rounded to truncate the data.

12.345-OK

123.45-because the decimal part is less than 3 places, it needs to be filled by 0. So the preservation should be 123.450. So the whole number of digits exceeds 5 and cannot be saved.

1.2-the unfilled part of the decimal is filled with 0. Save according to 1.200.

Float (MMagar S) M is the full length and S is the length after the decimal point (excluding the first 0). For examples of imprecision, there are many examples on the network. Copy is as follows:

Mysql > create table T1 (C1 float (10Magne2), c3 decimal (10Magne2))

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into T1 values (9876543.21, 9876543.12)

Query OK, 1 row affected (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.