MySQL common sense and basic operations

I. Field Type
1. Int [(m)]

Normal size Integer type

2. Double [(m, D)] [zerofill]

Normal size (Double Precision) floating point numeric type

3. Date

Date type. The supported range is 1000-01-01 to 9999-12-31. MySQL displays date values in YYYY-MM-DD format, but allows you to assign values to the date column using strings or numbers

4. Char (m)

Fixed-length string type. When stored, it always fills the Right to the specified length with spaces

5. Blob text

Blob or text type. The maximum length is 65535 (2 ^ 16-1) characters.

6. varchar

Variable-length string type.

　Ii. Basic operations

1: display the database
　
Show databas,

2: currently selected database,

Mysql> select database ();
++
| Database () |
++
| Test;
++
| Database () |
++
| Test |
++

3: Table information contained in the current database:

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

4. Get the 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 a. SQL File

(2) first generate a database such as auction. C: mysqlbin> mysqladmin-u root-P creat auction. A prompt is displayed, indicating that the password is entered and created successfully.

(2) import the auction. SQL File

C: mysqlbin> mysql-u root-P auction <auction. SQL.

With the preceding operations, you can create a database auction and a table auction.

6. Modify the database

(1) Add fields to the MySQL table:

Alter table dbname add column userid int (11) not null primary key auto_increment;

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

7. MySQL database authorization

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

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

To username @ localhost

Identified by password;

For example, to create a new user account for database access, perform the following operations:

Mysql> grant usage

-> On test .*
-> To testuser @ localhost;
Query OK, 0 rows affected (0.15 Sec)

Then a new user named testuser is created. This user can only connect to the database from localhost and connect to the test database. Next, we must specify the operations that the user testuser can perform:

Mysql> grant select, insert, delete, update
-> On test .*
-> To testuser @ localhost;
Query OK, 0 rows affected (0.00 Sec)

This operation enables testuser to perform select, insert, delete, and update queries on the tables in each test database. Now we end the operation and exit the mysql client program:

Mysql> exit
Bye