MySQL basic commands

Mysql Notes

I. BASIC DATABASE operations

1.mysql-h Localhost-u Root-p

2. At the end of each input, the wrong input \c cancel this input and start the new input.

3.auto_increment starts from 1 by default.

4. View all databases.

show databases;

5. Use a database.

Use dbname;

6. View the current database to say that there is a table.

SHOW TABLES;

Two. Basic operation of the data sheet

1. Using PRIMARY KEY constraints

(1). Single field primary key

①. Specify the primary key while defining the column.

CREATE TABLE Tb_emp

(

ID Int (one) primary key,

Name varchar (25),

DeptID Int (11),

Salary float

);

②. Specify a primary key after all columns have been defined.

CREATE TABLE Tb_emp

(

ID Int (11),

Name varchar (25),

DeptID Int (11),

Salary float,

Primary KEY (ID)

);

(2). Multi-field Federated primary key (the primary key is composed of multiple field unions)

CREATE TABLE Tb_emp

(

ID Int (11),

Name varchar (25),

DeptID Int (11),

Salary float,

Primary KEY (Id,deptid)

);

2. using FOREIGN KEY constraints

[constraint< foreign key name;] FOREIGN KEY Field Name 1 [, Field name 2,...]

REFERENCES < Main Table name > Primary key column [, primary key column 2,]

Foreign key name is the name of the defined foreign KEY constraint, a table cannot have a foreign key with the same name, and the field name represents the field column where the Word table needs to add a foreign key constraint; The primary table name is the name of the table on which the foreign key of the Word table depends;

3. using non-null constraints

A non-null constraint means that the value of a field cannot be empty.

4. use of uniqueness constraints

A uniqueness constraint requires that the column be unique, allowed to be empty, but only with a null value, and a uniqueness constraint ensures that no duplicate values occur in one or more columns.

(1). Specify a unique constraint directly after the column is defined.

CREATE TABLE Tb_dept

(

ID Int (one) primary key,

Name varchar (unique),

Location varchar (50)

);

(2). Specify a unique constraint after all columns have been defined.

Grammar rules : [CONSTRAINT < constraint name;] UNIQUE (< field name >)

CREATE TABLE Tb_dept

(

ID Int (one) primary key,

Name varchar (22),

Location varchar (50),

CONSTRAINT STH UNIQUE (name)

);

Unique and primary key differences: A table can have multiple fields declared unique, but only one primary key declaration, the column declared as primary key does not allow null values , However, a field declared as UNIQUE allows for a null value (NULL) to exist.

5. using Default Constraints

The default constraint specifies the default value for a column. such as male students more, gender can be the default male. If you do not assign a value to this field when inserting a new record, the system will automatically assign the value to male.

Syntax rules: Field name data type default defaults

Cases:

CREATE TABLE Tb_emp

(

ID Int (one) primary key,

Name varchar (+) is not NULL,

DeptID Int (one) default 1111,

Salary float

);

6. Set the property value of the table to automatically increase

Mysql , the initial value of Auto_increment is 1, and the field value is automatically added 1 for each new record.

Syntax rules: Field name data type Auto_increment

CREATE TABLE Tb_emp

(

ID Int (one) primary key auto_increment,

Name varchar (+) is not NULL,

DeptID Int (11),

Salary float

);

7. View the data table structure

View Table BASIC structure Statements Describe/desc

Syntax rule: DESCRIBE table name

or abbreviated as: DESC table name

8. View Table Detail structure Statements SHOE CREATE table

Syntax format:

SHOW CREATE TABLE Table name \g

If you do not add the ' \g ' parameter, the displayed result can be very confusing, plus the parameter ' \g ', which makes the display result more intuitive and easy to view.

9. Modify the data sheet

Modifying a table refers to the structure of modifying a data table that already exists in the database.

①. modifying table names

ALTER TABLE old table name rename new table name;

Prior to this, you can pass SHOW TABLES; View all existing tables in the current database.

②. Modifying the data type of a field

Syntax rules:

ALTER TABLE Table name Modify field name data type;

Before that, you can view the table structure by using the DESC table name.

Example: ALTER TABLE TB_DEPT1 modify name varchar (30);

modifying field names

Syntax rules:

ALTER TABLE Table name change old field name new data type new field name;

Example: ALTER TABLE TB_DEPT1 change location loc varchar (50);

Change You can also just modify the data type to achieve and modify the same effect by setting the new field name and the old field name in the SQL statement to the same name, changing the data type only.

One by one . Adding fields

ALTER TABLE Table name add new field name data type;

Delete a field

ALTER TABLE Table Name drop field name;

Modifying the arrangement position of a field

ALTER TABLE Table name Modify field 1 data type first|after field 2;

①. Modifying a field to be the first field in a table

ALTER TABLE TB_DEPT1 modify Column1 varchar (a) first;

②. After modifying a field to a specified column in a table

ALTER TABLE TB_DEPT1 modify Column1 varchar (a) after location;

Change the storage engine for a table

Syntax rules:

ALTER TABLE table name engine= the changed storage engine name;

View supported engine names through show ENGINES;

Delete a foreign KEY constraint for a table

ALTER TABLE table Name drop FOREIGN key foreign KEY constraint name;

Delete a data table

①. Deleting tables that are not associated

drop table [if exists] table 1, table 2 ...;

Example: Drop table if exists tb_dept;

②. Deleting a primary table that is associated with another table

Three. Data types and operators

1. integer type

CREATE TABLE Tb_emp1

(

ID Int (11),

Name varchar (25),

DeptID Int (11),

Salary float

);

The ID field has a data type of int (11), noting the subsequent number 11, which represents the display width specified by the data type, specifying the number of digits in the numeric value that can be displayed. The display width and the value range of the data type are irrelevant. The display width only indicates the maximum number of digits that can be displayed by MySQL, which is filled by spaces when the number of digits is less than the specified width, and if a value greater than the display width is inserted, the value can still be inserted and displayed if it does not exceed the range of values for that type of integer.

2. Floating- point type and fixed-point number type

The number of floats and fixed points in MySQL is used to represent decimals. There are two kinds of floating-point types: single-precision floating-point types (float) and double-precision floating-point types (double). There is only one fixed-point type: DECIMAL. Both floating-point and fixed-point types can be represented by (M,n), where M is called precision, which represents the total number of digits; N is called the scale, which represents the number of decimal places.

3. Date and Time type

Year

Time

Datetime

TIMESTAMP

4. text string type

char (M)

VARCHAR (M)

Tinytext

TEXT

Mediumtext

Longtext

Enum

SET

①.char and varchar types

char (M) is a fixed-length string that specifies the length of the string column when defined. Fills the right side of the space to reach the specified width when saving. m represents the column length, and the range of M is 0~255 characters. For example, char (4) defines a fixed-length string column with a maximum number of characters of 4, and when the char value is retrieved, the trailing space is removed.

varchar (M) represents a variable-length string with M representing the maximum column length. The range of M is the maximum actual length of the 0~65535.varchar is determined by the longest row size and the character set used, and the actual space occupied by the string is added 1. For example, varchar (50) defines a string with a maximum length of 50. If the inserted string is only 10 characters, the actual stored string is 10 characters and a string ending character. VarChar remains in the trailing spaces at the time the value is saved and retrieved.

②.text type

The text column holds non-binary strings , such as article content, comments, and so on. trailing spaces are not deleted when you save or query the value of the text column. The TEXT type is divided into 4 types: Tinytext,text,mediumtext, and

Longtext. Different text types have different storage spaces and data lengths.

③.enum type

④.set type

⑤. Binary string types

The binaries in MySQL are:

Bit,binary,varbinary,tinynlob,blob,mediumblob and Longblob.

2. selection of data types

①. Integers and floating-point numbers

For floating-point data columns, the values that are deposited are rounded up for the decimal digits defined by the column. For example, if the value of a column has a range of 1~99999, Mediumint unsigned is the best type if you use integers, and float is used if you need to store decimals.

Floating-point types include float and double types. The double type has a higher precision than the float type, so if you require high storage precision, you should choose the double type.

②. Floating-point and fixed-point numbers

The advantage of floating-point float,double relative to fixed-point decimal is that floating-point numbers can represent a larger range of data in a certain length of time, but because floating-point numbers are prone to errors, it is recommended to use decimal to store them when the accuracy requirement is high. Decimal is stored as a string in MySQL, and is used to define data with higher accuracy requirements such as currency.

③. Date and Time type

If you only need to record the time, you only need to type in the date.

If you only need to record years, use the year type.

If you need to record both the date and time, you can use the datetime and timestamp types. Because the value range of the timestamp column is less than the value range of the DateTime, it is best to use DateTime for a date with a large storage range.

Timestamp also has a property that DateTime does not have. By default, when a record is inserted but the column value is not specified timestamp, MySQL sets the timestamp column to the current time. Therefore, timestamp is convenient when inserting records while inserting the current time, and the other timestamp is more efficient in space than DateTime.

The characteristics and choice between ④.char and varchar

Char is a fixed length, and varchar is a variable-length character;

Char automatically removes trailing spaces from the inserted data, and varchar does not delete trailing spaces.

For INNODB storage engines: Use variable-length data columns because the INNODB data table has a storage format that is not fixed-length and variable-length, so using char is not necessarily better than using varchar, but because varchar is stored in the actual length, it is more space-saving, so the disk I /O and the total data storage is better.

⑤.enum and set

⑥.blob and text

BLOBs are binary strings, text is non-binary strings, and both can hold large volumes of information. BLOBs primarily store pictures, audio information, and so on. Text can only store plain text files.

Four. mysql function

1. Date and Time functions

①. Getting the current date function

The Curdate () and current_date () functions work the same.

②. Getting the current time function

Current_timestamp (), localtime (), now () and Sysdate () 4 functions have the same effect, returning the current date and time values.

③.unix time stamp function

④. Functions that return UTC dates and functions that return UTC time

utc_date () The function returns the current UTC (World standard Time) Date value.

utc_time () returns the current UTC time value.

⑤. Get the month's function month (date) and MonthName (date)

MONTH (date) the function returns the month that corresponds to date.

MONTHNAME (date) The function returns the full English name of the month for the date.

⑥. Get the function of the week dayname (d), DAYOFWEEK (d) and weekday (d)

Dayname (d) The function returns the English name of the weekday that corresponds to D, such as Sunday,monday.

DAYOFWEEK (d) The function returns the index of the week corresponding to D, 1 for Sunday, and 2 for Monday.

WEEKDAY (d) returns the working day index for D, 0 for Monday, and 1 for Tuesday.

⑦. Functions for cycle Weeks Week (d) and WeekOfYear (d)

WEEK (d) The calculated Date D is the week ordinal of the year.

WeekOfYear (d) calculates the week ordinal of a day in a year.

⑧. Functions to get the number of days DayOfYear (d) and DayOfMonth (d)

DayOfYear (d) The function returns D is the day ordinal of a year.

DayOfMonth (d) The function return d is the day ordinal of the one month.

D as a date.

MySQL basic commands