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

1
mysql -uroot -p <password>

2. Quit Login

quit or exit

3. Check Out Version, Display Current Time

1
2
select version();
select now();

4. Basic Operation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 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>;