mysql database management

type	explain
int	Integer (define integer type data)
float	Single precision floating point, 4 bytes and 32 bits, accurate to six decimal places
double	Double precision floating point, 8 bytes, 64 bits
char	A fixed length character type that defines character class data
varchar	Variable length character type
text	text
image	picture
decimal (5,2)	Five valid length numbers, with 2 digits after the decimal point, specify the length array

When the last two decimal digits of decimal are less than 2,5, the decimal will be taken; When there are more than two digits, the third digit after the decimal point is automatically rounded to the second digit

char if the actual length of the stored data is smaller than the specified length, spaces will be filled to the specified length; If the actual length of the stored data is greater than the specified length, the lower version will be intercepted and the higher version will report an error.
char is a fixed length value. If it is insufficient, replace it with a space, while varchar is a variable value. The maximum is the set length value. When it is insufficient, it is the current length.
For example, char(10) and varchar(10) are defined. When 123 is input, the length of char is 10. Although it is insufficient, the insufficient part is supplemented by spaces; The length of varchar is 3.

2, View database structure

1. Enter database

mysql -uroot -p010230
-u User name to connect to the database
-p Followed by the password to connect to the database (no spaces)

2. View database information

show databases;
Remember to add a good ending, most of them SQL Orders must follow;Indicates the end

3. View information about tables in the database

use Database name;
show tables;

4. Display the structure (field) of the data table

describe [database.]Table name   
or
desc [database.]Table name 

It's like we're here Linux The absolute path is the same as the relative path. When you are in the library of the table, you can omit the previous database name (relative path).
When you need to operate on tables in other libraries, you need to add the library name (absolute path) for operation.

III. overview of SQL statements

1. SQL language

The abbreviation of Structured Query Language is Structured Query Language
Standard language for relational database
It is used to maintain the management database
- Including data query, data update, access control, object management and other functions

2. SQL classification

DDL	Data definition language is used to create database objects, such as libraries, tables, indexes, etc
DML	Data manipulation language is used to manage the data in the table
DQL	Data query language is used to find qualified data records from data tables
DCL	Data control language is used to set or change database user or role permissions

III. DDL

1. Create databases and tables

Create database
create database Database name; 

Create data table
create table Table name(Field 1 data type,Field 2 data type,....,[primary key(Primary key name)]);

field	field
type	type
not null	Cannot be empty
default ''	The default is empty
primary key	Primary key (generally select fields that are not duplicate and not empty)

2. Delete the specified data table

use Database name;
drop table Table name;
or
drop table [Database name.]Table name;

3. Delete the specified database

drop database Database name;

IV. data records in DML management table

1. insert new data: insert

insert into Table name(Field 1,Field 2[,...]) values(Value of field 1,Value of field 2,...);

Field, you can use password('password') ，Ability to use encrypted passwords

2. Modify and update the original data: update

update Table name set Field name 1=Field value 1[,Field name 2=Field value 2] [where Conditional expression];

3. Delete unnecessary data: delete

delete from Table name [where Conditional expression];

V. DQL query data record - select

select Field name 1,Field name 2[,...] from Table name [where Conditional expression];

Tips:
select * from Table name;           #Show all
select Field 1, field 2 from Table name;  #Display field 1 and field 2  
select Field 1 from Table name\G;      #Display vertically as a list
select * from Table name info limit 2;   #Show only the first 2 lines
select * from Table name info limit 2,3; #Display the first 3 lines after the second line

6, Modify table name and table structure - alter

1. Modify table name

alter table Old table name rename New table name;

2. Expand table structure (add fields)

alter table Table name add address varchar(50) default 'Unknown address';

3. Modify the field (column) name and add a unique key

alter table Table name change Old column name new column name data type [unique key];

#change can modify all items such as field name, data type and constraint

Unique key: unique, but can be null (null value can only appear once)

The primary key contains some properties of a unique key
 A unique key cannot be a primary key

4. Delete field and primary key

alter table Table name drop Field name;

5. Add field

alter table Table name add Field name;

Summary:

1. Common types: int, char, varchar, decimal

2. Common operation

① DDL is responsible for data structure definition and database object definition

Create libraries and tables
create database Database name; 
create table Table name(Field 1 data type,Field 2 data type,....,[primary key(Primary key name)]);

Delete libraries and tables
drop table [Database name.]Table name;
drop database Database name;

Modify table name
alter table Old table name rename New table name;

Expand table structure (add field)
alter table Table name add address varchar(50) default 'Address unknown';

Modify field (column) name and add unique key
alter table Table name change Old column name new column name data type [unique key];

Delete field and primary key
alter table Table name drop Field name;

Add field
alter table Table name add Field name;

② . data in DML management table

Insert new data
insert into Table name(Field 1,Field 2[,...]) values(Value of field 1,Value of field 2,...);

Update data
update Table name set Field name 1=Field value 1[,Field name 2=Field value 2] [where Conditional expression];

Delete data
delete from Table name [where Conditional expression];

③,DQL

Query data record
select Field name 1,Field name 2[,...] from Table name [where Conditional expression];

3. Precautions

Be careful when modifying the database
When creating and deleting databases and tables, you need to carefully confirm the current database location and learn to use [database name.] data sheet
When modifying the database, when using the where condition, you need to repeatedly confirm whether there are duplicates in the condition selection results
Learn to use limit for some related operations
Know what to try the only key and primary key

Unique key: unique, but can be null (null value can only appear once)
Primary key: unique, but cannot be null
The primary key contains some properties of a unique key
A unique key cannot be a primary key

Keywords: Database MySQL

Added by rhathid on Mon, 14 Feb 2022 18:40:57 +0200

Programming VIP