MySQL common sense and basic operations

MySQL field type
1. int[(M)]

Normal size integer type

2. double[(m,d)] [Zerofill]

Normal size (double precision) floating-point number type

3. DATE

Date type. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '. MySQL displays date values in ' YYYY-MM-DD ' format, but allows you to assign values to date columns using strings or numbers

4. CHAR (M)

Fixed-length string type, when stored, always fills the right to the specified length with a space

5. BLOB TEXT

Blob or text type with a maximum length of 65535 (2^16-1) characters.

6. VARCHAR

Variable-length string type.

1: Show the database

Show Databas,

2: The currently selected database,

Mysql> SELECT DATABASE ();
+------------+
| DATABASE () |
+------------+
| Test;
+------------+
| DATABASE () |
+------------+
| Test |
+------------+

Table information contained in the current database:

Mysql> show TABLES;
+---------------------+
| Tables in Test
Mysql> show TABLES;
+---------------------+
| Tables in Test |
+---------------------+
| Mytable1 |
| Mytable2 |
+---------------------+

Get table structure

mysql> desc Mytable1;
+---------+-------------+------+-----+---------+-------+
| Field mysql> desc mytable1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| S1 | varchar (20) | YES |　　　 NULL | |
+---------------------+------+-----+---------+-------+



5. Import Database Tables
(1) Create. sql file

(2) First create a library such as Auction.c:mysqlbin>mysqladmin-u root-p creat auction, prompts for a password, and then successfully created.

(2) Import Auction.sql file

C:mysqlbin>mysql-u Root-p Auction < Auction.sql.

By doing this, you can create a database auction and one of the table auction.

6. Modify Database

(1) Add fields to the MySQL table:

ALTER TABLE dbname add column userid int (one) NOT null primary key auto_increment;

In this way, a field userid is added to the table dbname, and the type is int (11).

7. Authorization for MySQL Database

Mysql>grant Select,insert,delete,create,drop

On *.* (or test.*/user.*/.)

To User name @localhost

Identified by ' password ';

Such as: Create a new user account so that you can access the database, you need to do the following:

Mysql> Grant Usage

-> on test.*
-> to Testuser@localhost;
Query OK, 0 rows affected (0.15 sec)

Since then, a new user is created: testuser, this user can only connect to the database from localhost and connect to the test database. Next, we must specify what testuser this user can do:

mysql> GRANT Select, INSERT, Delete,update
-> on test.*
-> to Testuser@localhost;
Query OK, 0 rows Affected (0.00 sec)

This action enables TestUser to perform Select,insert and delete and update query operations in each table in the test database. Now we end the operation and exit the MySQL client program:

Mysql> exit
Bye