MySQL (2) Getting Started with SQL statements

1. Import of data

Create a database;

Select version ();//view current database version (if database upgrade, add-S, do not refactor the original data, only the system tables are refactored, then the database version may not be the current version);

Create database name;//Creating database

show databases;//display all databases, see if the table has been imported

Use the database you just created;

Source table to import (file name);//Import Table

Show tables;//Displays the table under the library

Desc the original table name (not the file name);//Display the structure of the table

Select * from table name;//view table contents

2. Creation and deletion of databases and tables

Creation of a database

Create database name;//Creating database

Deletion of the database

drop database name;

Table

Create Table Table name (

Field 1 modifier (range, this is all there is scope)

）

Delete a table

Drop table name;

3. Database and Table queries

Database

Show databases; query all the tables

Show databases like '%es% ';//fuzzy search filter;

Select Database (); View which databases are currently in;

Table

Use test;//Using Library

Show tables;//display all tables under this library

Show tables like '%es% ';//Fuzzy Lookup of tables with ES fields under database test;

Show create table table name;//view how tables are created

Exit of 4.mysql

Quit, exit, CTRL +C

5.select statements

Select Field 1, Field 2 ... from table name//query table All fields 1, field 2 ... Information

Select Field 1, Field 2 ... from table name where condition;//query table for qualifying field 1, Field 2 ... Information

For example, select name, age from employee where name> 35;//query table for age greater than 35 for name age information;

Some of the following actions are available for select

Arithmetic operators

+,-,*,/,%

For example:

Select Salary*12 as ' annual salary ' from employees;//as output displays aliases, which can be omitted and not written, other operators are similar.

Select Salary*12 as ' annual salary ' from employees where salary>10000*3;

Relational operators

==,>,</>=,<=,!=,between A and B

Example: Select salary from Employees where salary>10000; other similar;

Select salary from employees where salary between-and10000;

Sort

oder by "Number/Field name" "Asc/desc", Number/Field name "" Asc/desc "

For example: Select name from Employees ORDER by 1 ASC;//1 represents the first field

Select name salary from Employees order by 1 Asc;2 desc//according to that ascending, salary descending;

Is null, in, not, like

Select name from employees where name in (' Zhang San ', ' John Doe ');//Is Zhang San or John Doe

Select name from employees where name is null;//null means nothing

Select name from employees where name is like '%s_s% ';//Fuzzy Lookup,% for one or more characters, _ for one character

Select name from employees where name is not in (' Zhang San ', ' John Doe ');//Not Zhang San or John Doe

Select name from employees where name was not null;//means not null

Data processing functions

Trim,upper,lower,substr (field to be processed, subscript, length), Length,round,rand,ifnull (field, if empty default value);

MySQL (2) Getting Started with SQL statements