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

This article mainly introduces the basic operation of MySQL, hoping to supplement and update some knowledge, if you have any other questions you need to know, you can continue to follow my updated article in the industry information.

Last time, I talked about some basic operations of MySQL database. Today, I will share with you some command structures and types of MySQL.

The noun MySQL explains:

DB:DataBase

Database: a collection of data organized and stored in storage based on a data model

DBMS

Database management system: for manipulation and management

DBS

CVM with database and database management system

The database is managed using the sql command: (structured query language)

MySQL instructions: change and environment switch, depending on the status, exit and other controls

SQL directive: database definition, query, manipulation, authorization statement

Note:

Case insensitive (passwords, except for variable values)

Classification:

DDL data definition language

DML data manipulation language

DCL data control language

DTL data transaction language

# # basic operating commands:

Create database library name; # create library

Show databases; # View existing libraries

Drop database library name; # delete library

Use library name; # switch library

Show tables; # View existing tables in the library

Select database (); # View the current library

Select * from library name. Table name; # View table contents

Desc library name, table name # View table structure

# # Table creation command:

Create table library name. Table name (field name character type

Field name numeric type

.

);

Example: create table dc.xx (name char (5), nianling int)

Name of the library. Table name (name text type (5), second column name number type)

Insert into library name. Table name values (values list) one parenthesis line # insert table record

Example: insert into gamedb.stu values ("jim", 21), ("tom", 29)

Name of the library. Indicates (values list,), (values list)

Delete from library name. Table name # Delete table record

Drop table Table name # Delete Table

Database type: divided into integer type, floating point type, character type, date and time type, enumeration type.

Integer (integer)

According to the range type of stored values, there are small integers and micro integers.

Tinyint 1 byte-128 "127 0" 255 unsigned

Smallint 2 bytes-32768 bytes 32767 0bytes 65535

Mediumint 3 bytes-2

Int

Bigint

Generally speaking, the basic use of int in the integer type is sufficient, depending on personal preference.

Floating point type, divided into single precision and double precision

Float single-precision floating-point numbers (nrecom)

Double double precision floating point numbers (npencil m)

N means: total digits

M represents the number of decimal places

Character type: text with fixed length and variable length

Char varchar blob/text

Fixed length: maximum length is 255 bytes

Variable length: allocate storage space according to the actual size of the data

Large text: used when the number of characters is greater than 65535

Date time type:

Year 2017 YYYY

Date DATE, month, day, 20171220 YYYYMMDD

Time time hour minute second 155145 HHMMSS

Date and time:

Datetime year, month, day, hour, second YYYYMMDDHHMMSS

Timestamp year, month, day, hour, second YYYYMMDDHHMMSS

Example:

Mysql > create table dc.cc (

-> name char (10)

-> nianling tinyint

-> nian year

-> shijian time

-> shengri date

-> yuehui datetime

->)

Atetime/timestamp differences:

When a bit is assigned to a timestamp field, it is automatically assigned at the current system time, while the DA field is assigned the bit NULL by default

Use the time function to assign values to fields of date-time type:

Now () year () month () day () date () time ()

Example:

Insert into dc.cc values ("tom", 19LY (now ()), time (now ()), date (now ()), now ())

Enumeration type: the value of a field can only be selected within the range of enumerations

Field name enum (values list) Radio valu

Field name set (values list) multiple selection

Set field constraints: function limits how to assign values to fields.

Null allowed to be empty, default location

Empty not null is not allowed

Note # name cannot be set to empty by default.

Example: mysql > create table T2 (name char (10) not null, age tinyint default 23, sex enum ("my", "you") not

Name type (10) is not empty, age type is not empty 23, gender radio value ("I", "you") is not

Null default "my")

The empty default value "my")

Table structure adjustment::

Format:

Mysql > alter table table name to perform actions (what to do)

Mysql > alter table table name to perform the action;} # add a new field, which is last by default.

Alter table q3 add stu_id int first

Table name before adding field name type

Add field name type (width) constraint,}

Add field name type (width) constraint first; # the front of all fields

Add field name type (width) constraint after field name; # what field is added after

Alter table drop field name; # Delete field

Alter table q3 drop stuid

Table name field name

Alter table modify field type (width) constraint # modify field type

Alter table change original field name new field name type (width) constraint # modify field name

Alter table original table name rename new table name; # modify table name

Comprehensive examples:

Mysql > create table zonghe.banji (

Name of the library. Table name (

-> xingming char (5)

Item file type (parameter)

-> nianling int

Numeric type

-> xingbie enum ("nan", "nv")

Enumerated type

-> rexue datetime

Time type

-> xuefei float (5pc2)

Floating point type

->)

Read the above about the basic operation of MySQL analysis, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.