MySQL command learning (1) _ MySQL

MySQL command learning (1) today, let's take a look at common MySQL commands (the command keywords in MySQL are case insensitive ):

(1) show databases; displays all databases in MySQL

(2) create database dbname; create database dbname in MySQL

For example, we create a database named student_db: create database student_db;

(3) use dbname; use database dbname

For example, we are going to use the student_db database: use student_db;

(4) drop database if exists dbname; delete database dbname without any prompt

For example, we delete the database student_db: drop database if exists student_db;

(5) show tables; display all tables in the database

(6) describe tablename; displays the tablename structure of the table.

For example, we want to know the structure of the student_info table under student_db: describe student_info;

(7) create table if not exists dbname. tablename (filed1, filed2 ,......); Create a table tablename

For example, we want to create the student_info table under student_db:

Create table if not exists student_info (
Stu_id Char (20) not null primary key,
Stu_name VarChar (8) not null,
Stu_sex char (2) not null,
Stu_birthday date not null,
Stu_class varchar (50) not null,
Stu_major Char (8) not null,
Stu_credit Tinyint default 0,
Stu_remark Text null );

Insert a bit of data type here

**************************************** **************************************** **************************************** *

**************************************** **************************************** **************************************** *

MySQL data type

(1) Text type

CHAR (size) stores fixed-length strings (including letters, numbers, and special characters), and specifies the length of a string in parentheses, up to 255 characters

VARCHAR (size) stores variable-length strings (including letters, numbers, and special characters), and specifies the maximum length of a string in parentheses, up to 255 characters

TINYTEXT stores strings with a maximum length of 255 characters

TEXT is a string of up to 65535 characters

BLOB is used for BLOBS (Binary Large Objects). It can store up to 65535 bytes of data.

...

(2) Number type

TINYINT (size)-128 to 127, unsigned 0 to 255, the maximum number specified in parentheses

SMALLINT (size)-32768 to 32767, unsigned 0 to 65535, the maximum number specified in parentheses

MEDIUMINT (size)-8388608 to 8388607, 0 to 16777215 unsigned, the maximum number specified in parentheses

INT (size)-2147483648 to 2147483647, unsigned 0 to 4294967295, the maximum number specified in parentheses

FLOAT (size, d) specifies the maximum number of digits with a floating decimal point. in parameter d, specify the maximum number of digits on the right of the decimal point.

DOUBLE (size, d) is a large number with a floating decimal point. specify the maximum number of digits in parentheses. specify the maximum number of digits on the right of the decimal point in parameter d.

DECIMAL (size, d) is the double type of string storage. it specifies the maximum number of digits in brackets. in the d parameter, specify the maximum number of digits to the right of the DECIMAL point.

...

(3) DATE type

DATE format: the YYYY-MM-DD supports ranges from '2017-01-01 'to '2017-12-31'

TIME format: HH: MM: SS supports the range from '-838: 59: 59' to '2017: 59: 59'

...

**************************************** **************************************** **************************************** *****

**************************************** **************************************** **************************************** *****

(8) insert into tablename values (value1, value2,...); insert a row of data into the tablename table

For example, insert six data entries into the student_info table consecutively:

Insert into student_info
Values ('20170901', 'Liu Ji Yun ', 'male', '2017-11-25', '20170301', 'software engine', 1001101620004 ,'');

Insert into student_info
Values ('20140901', 'Zhang San', 'male', '2017-12-25 ', '20170901', 'software engine', 1001101620005 ,'');

Insert into student_info
Values ('000000', 'Lily', 'male', '2017-1-25 ', '20170101', 'software engine', 1001101620006 ,'');

Insert into student_info
Values ('000000', '王', 'male', '2017-8-25 ', '20170101', 'software engine', 1001101620007 ,'');

Insert into student_info
Values ('000000', 'Zhao Liu', 'male', '2017-9-25 ', '20170301', 'software engine', 1001101620008 ,'');

Insert into student_info
Values ('20140901', 'Wang Juan ', 'female', '2017-9-18 ', '20170901', 'Electronic information', 1001101630009 ,'');

(9) select field1 from tablename; retrieve a single column field1 in the tablename table

For example, in student_info, I retrieve a single column stu_name: select stu_name from student_info;

Select * from tablename; retrieve all columns in the table

For example, I want to retrieve all the records in the student_info table: select * from student_info;

(10) distinct indicates that MySQL returns different values.

For example, if I want to retrieve records with different major values in student_info: select distinct stu_major from student_info;

In terms of the current data, two pieces of data are returned: 'Software project' and 'electronic information'

(11) limit limits the number of data rows returned by MySQL to not greater than n

For example, I want to retrieve the data records of the first three rows in the student_info table: select * from student_info limit 3;

If we want the returned result to start from Row 2, we can write: select * from student_info limit 2 and 3;

(12) order by Takes the names of one or more columns and sorts the output accordingly.

ASC indicates the ascending order, which is used by default.

DESC indicates descending order

For example, if you want to sort the search results in descending order of the birthday values, you can write: select * from student_info order by stu_birthday DESC;