Database BASICS (II)-Navicat applications, database navicat

Database BASICS (II)-Navicat applications, database navicat

I. MySQL Data Type

1. numeric type

SMALLINT: 2 bytes

INT: 4 bytes // age int (10)

INTEGER: synonym for INT

BIGINT: 8 bytes

FLOAT: 4 bytes

DOUBLE: 8 bytes // score float (10, 2)

2. String (character) Type

CHAR: Fixed Length string sex char (2) // you can evaluate the length CHAR_LENGTH ('sss ')

VARCHAR: Variable Length string name varchar (20)

Although VARCHAR is more flexible to use, from the perspective of the overall system performance, CHAR data processing speed is faster, sometimes even more than 50% of the VARCHAR processing speed.

3. date/time type

DATE: YYYY-MM-DD

DATETIME: YYYY-MM-DD HH: MM: SS

TIMESTAMP: YYYY-MM-DD HH: MM: SS // The TIMESTAMP column automatically records the date and time when used for INSERT or UPDATE operations.

TIME: HH: MM: SS

YEAR: YYYY

4. Miscellaneous

BLOB binary data, up to 4 GB

When the TEXT and BLOB types require more than 255 fields, MySQL provides two types: TEXT and BLOB. They all have different subtypes based on the size of the stored data. These large data types are used to store binary data types such as text blocks or images and audio files.

The TEXT and BLOB types differ in classification and comparison. BLOB type is case sensitive, while TEXT is case insensitive. The size modifier is not used for various BLOB and TEXT subtypes. Values that are larger than the maximum range supported by the specified type are automatically truncated.

II. Application of Navicat

Baidu description about Navicat:

Navicat is a fast, reliable, and inexpensive database management tool designed to simplify database management and reduce system management costs. It is designed to meet the needs of database administrators, developers and small and medium-sized enterprises. Navicat is built on an intuitive GUI, allowing you to create, organize, access, and share information in a secure and simple manner.

(That is, Navicat is a database management tool. It is better for MySQL than MySQL. However, without MySQL, Navicat will lose its meaning)

Iii. SQL introduction // (Structured Query Language)

1.An SQL statement is a special statement used to "talk" with a DBMS. Different DBMS recognizes the SQL syntax.

2. single quotation marks are used for strings in SQL statements.

3. SQL statements are case-insensitive. Insensitive means SQL keywords, string values, or case-sensitive.

4. SQL

DDL (Data Definition Language) Create Table, Drop Table, Alter Table, etc. are DDL,

DML (data operation language) Select, Insert, Update, Delete, etc. belong to DML

DCL (Data Control Language) controls access permission, access permission GRANT, REVOKE, etc. belong to DCL

4. create and manage tables

Create database mydatabase1 // create a database

Drop database mydatabase1; // delete a database

Show databases; // display all databases

Use mydatabase1; // specify the default database

Show tables; // display all tables

Describe userInfo; // displays the table structure

Show create table userInfo // display the table creation statement

(Appendix: update userInfo set id = 3 where id = 5 is acceptable, that is, in mysql, the value of the primary key can be updated)

create table userInfo (                id int(10) primary key  auto_increment,                name varchar(20),                sex char(2),                score float(4,2)            );

Note: It is necessary to add a primary key. The subsequent auto_increment can support Automatic addition of order.

5. Table deletion Statement (Remember, interview)

1. drop table userInfo // delete a table

(The difference between using it to delete data and the following: for example, if there are 6 Original data records and the primary key auto-increment (1-6), after you run the drop table userInfo command, add a piece of data to it, and the primary key is 7 .)

2. truncate table userInfo // Delete table data

(Fast, the auto-increment primary key is restarted, and the table structure is still in progress. For example, if there are 6 Original data records and the primary key is auto-incrementing (1-6), you execute truncate table userInfo and then add a data record to it. The primary key is 1 .)

6. Table modification statements (also need to be noted down)

Alter table student add birth date;

Alter table student change birth birthday date;

Alter table student modify sex char (4) not null;

Alter table student modify sex char (2) after birthday;

Alter table student rename as stu;

Alter table stu drop birthday;