MySQL Frequently Used Command (I)
SQL is a structured query language, it is a database language used to operate RDBMS, current relational databases support the use of SQL language, that is, you can use SQL to operate oracle, sql server, mysql, sqlite and all other relational databases
SQL clause mainly includes DQL, DML, TPL, DCL, DDL, CCL[, in which DQL and DML are most important for web developer.
Data integrity
- A database is a complete business unit and can contain multiple tables in which data is stored.
- In order to store the data more accurately and to ensure that the data is correct and valid, some mandatory validation can be added to the table when it is created, including the type of data type and constraints.
Data Type
Commonly used data types are as follows:
- Integer: int, bit
- fractional: decimal
- String: varchar, char
- Date and time: date, time, datetime
- Enumerated types (enum)
Specially described types are as follows:
- decimal means a floating point number, e.g. decimal(5,2) means a total of 5 digits, with 2 decimal places.
- char means a fixed-length string, e.g. char(3), with a space for 'ab ’ if ‘ab’ is filled in.
- varchar means a variable length string, such as varchar(3), which stores ab when filled with ‘ab’.
- The string text indicates that large text is stored, and is recommended when the characters are greater than 4000.
- For files such as images, audio and video, they are not stored in the database but are uploaded to some server and the path where this file is stored is then stored in a table.
Constraints
- primary key: the order in which the primary key is physically stored
- not null: this field is not allowed to be filled with a null value
- unique: the value of this field is not allowed to be duplicated default: the default value is used when the value is not filled in, if it is filled in, it will prevail
- foreign key: constraint on the relationship field, when filling the value for the relationship field, it will check if the value exists in the related table, if it exists, the filling will be successful, if not, the filling will fail and an exception will be thrown
Numerical type
Type | Byte Size | Signed Range | Unsigned Range |
---|---|---|---|
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
INT/INTEGER | 4 | -2147483648 ~2147483647 | 0 ~ 4294967295 |
BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~18446744073709551615 |
String
Type | Byte Size | Example |
---|---|---|
CHAR | 0-255 | Type:char(3) input ‘ab’, actually stored as 'ab ', input ‘abcd’ actually stored as ‘abc’ |
VARCHAR | 0-255 | Type: varchar(3) lose ‘ab’, actually stored as ‘ab’, enter ‘abcd’, actually stored as ‘abc’ |
TEXT | 0-65535 | Large text |
Date and time type
Type | Byte Size | Example |
---|---|---|
DATE | 4 | ‘2020-01-01’ |
TIME | 3 | ‘12:29:59’ |
DATETIME | 8 | ‘2020-01-01 12:29:59’ |
YEAR | 1 | ‘2017’ |
TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC |
Database basic operation
1. Connect to Database
mysql -uroot -p <password>
2. Quit Login
quit or exit
3. Check Out Version, Display Current Time
select version();
select now();
4. Basic Operation
# check out databases
show database;
# check out tables
show tables;
# create a table
create table if not exists <table_name> (
<col1> <datatype> <costraint>,
...
)
# deomo 1
create table if not exists areas (
area_id varchar(6) primary key not null,
area_name varchar(20) not null,
province_id varchar(6)
);
# demo 2
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(10)
);
# demo 3
create table students(
id int unsigned primary key auto_increment not null, name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('男','女','人妖','保密'),
cls_id int unsigned default 0
)
# modify a table
# add a field
alter table <table_name> add <col_name> <data_type>;
# modify a field in a table (rename)
alter table <table_name> change <origin_col_name> <new_col_name> <data_type> <constraint>;
# modify a field in a table (no rename)
alter table <table_name> modify <col_name> <data_type> <constraint>;
# remove a field
alter table <table_name> drop <col_name>;
# remove a table
drop table <table_name>;
# show the clause of creating a table
show create table <table_name>;