MySQL Base statement

Personal summary of MySQL base statements (including views, functions, clauses, etc.)

A lot of beginners may be the same as me when I first touch the SQL statement to practice but always forget the statement format, in order to solve this annoying thing so I decided to, as far as possible, all the DDL DML DQL DCL statements including the MySQL function detailed list, for later beginners to learn.

Say no more let's get into the MySQL world

--Enter MySQL
Mysql-u root-p

--Stop MySQL
net stop MySQL

--Open MySQL
net start MySQL

--DDL data definition language create ALTER drop
--DML Data Manipulation Language Update Delete insert
--DQL Data Query Language Select
--DCL Data Control Language SavePoint commit rollback grant revoke

--Create a database
Create DATABASE shop character Set UTF8 collate utf8_general_ci;

--Delete Database
Drop Database shop;

--View Current database link process status
Show Processlist;

--Modifying the database * can only modify the character set or the validation rule *
ALTER DATABASE shop character set GBK collate gbk_general_ci;

--Backing up the database
--Under the default directives
Mysqldump-u root-p Shop>c:/mysql.sql

--Recover Database * Single Library *
①create database Shop1;
②use Shop1;
--under the MySQL command
③source C:/mysql.sql;
④show tables; --* To see if the data is complete *

--Back up a table of the specified database
Mysqldump-u root-p Shop (database name) goods (table name) >c:/mysql_goods.bak

-recovery form * Same as library recovery *
SOURCE C:/mysql_goods.bak;

--Back up multiple libraries at once
Mysqldump-u Root-p-B Shop (Database i) SHOP1 (Database II) > C:/mysql.bak

--Recover Multiple library backups
SOURCE C:/mysql.bak;

--Create a table
CREATE TABLE Goods (
ID int NOT NULL unsigned auto_increment default 0 primary key
Name varchar (+) is not NULL,
class_id Int (6) Zerofill
) Character set UTF8 Engine=myisam;

--enum (enum) set (collection)
CREATE TABLE people (
ID int NOT NULL unsigned auto_increment default 0,
Name varchar (+) is not NULL,
Sex enum (' Male ', ' female ') not NULL,
Hobby set (' Playing ', ' playing games ', ' Running ', ' driving ') NOT NULL
) Character Set UTF8 engine = InnoDB;

--Add data
Insert into people values (1, "Zhang San", "male", "play games, Drive, run");

--set Enum Data Lookup
SELECT * FROM shop where sex= "male";
SELECT * FROM shop where Find_in_set (' Girls ', hobby);

--storing pictures, video and audio files
CREATE TABLE Shop2 (
IMG varchar (+) NOT NULL "/imgs/01.jpg"
) Charset=utf8 Engine=innodb;

--Insert
INSERT into SHOP2 values (' imgs/abc.jpg ');

--Create a student information sheet
CREATE TABLE Stu_info (
ID int unsigned NOT NULL auto_increment default 0,
Name varchar (+) is not NULL,
Sex enum (' Male ', ' female ', ' don't know ') not NULL,
Brithday date NOT NULL,
Entry_day date NOT NULL,
Job varchar (+) NOT NULL,
Salary Decimal (10,2) is not NULL,
Resume text NOT NULL
) Charset=utf8 Engine=innodb;

--********************************** MySQL User management ***************************************
Create user "username" @ "hostname" identified by "password" creates MySQL user
Drop user "username" @ "host name"; Delete User
Set Password = password ("password"); Change the password for yourself
Set password for "user name" @ "hostname" = password ("password"); Change the password for someone else

Grant permission list on library. Table name to "user name" @ "hostname"; Give permission
Revoke permissions list on library. Table name from "User name" @ "hostname" Reclaim permissions
Show grant for "username" @ "host name"; See what permissions you have

--************************************ View ***************************************
Create view view name as SELECT statement Creating views
Drop View name Delete views
Alter VIEW view name as SELECT statement Modify views
--********************************** Table ***************************************
--Modify Table
--Modify the table to add column * After the row after the column *
ALTER TABLE stu_info (table name) add Hobby (new add-on name) set (' Swim ', ' splash ', ' dance ') not null after sex;
--Modify the Delete column of a table
ALTER TABLE stu_info (table name) drop Hobby (column name);
--Modify the table's modified columns
ALTER TABLE stu_info (table name) change hobby (old column name) hob (new column name) set (' Gambling ', ' smoking ') not null;
--Modify the table to increase the primary key
ALTER TABLE stu_info (table name) Add primary key (add column for primary key);
--Modify the Delete primary key of the table
ALTER TABLE stu_info drop PRIMARY key;
--Modify the Delete foreign key of the table
ALTER TABLE Stu_info drop FOREIGN key (foreign key name);
--Modify the table to increase the index
ALTER TABLE Stu_info Add (index/unique) (index name/column name);
--Modify the delete index of the table
ALTER TABLE Stu_info DROP INDEX (index name);
--Modify table's emptying table data
TRUNCATE TABLE stu_info;
--Modify Table name change table
Rename table Stu_info to Stu;
--Modify the table change character set
ALTER TABLE stu_info charset = GBK;

--View table structure
Desc Stu_info;
Show CREATE TABLE Stu_info;

--********************************** Data ***************************************
--Add data
INSERT into Stu_info values (1, "Zhang San", "male", "Eat, Fight", ' 2016-03-04 ', ' 2014-03-04 ', ' can eat can fight to sleep ');
--Modify data
Update stu_info (table name) set name= "John Doe" (modified field/value) where (data changed by condition found) id = 1;
--Delete data
Delete from Stu_info (table name) where (the deleted data column is found by condition) id = 1;
--Querying data
SELECT DISTINCT (repeat) * from Stu_info;

--query data can be calculated with
Select (Salary = salary*3) from goods;
--query data can be aliased
Select (Salary = salary*3) as Sal from goods;
--query data can have functions
Select AVG (Salary) as avg_sal from goods;

--********************************** WHERE clause ***************************************
--Conditional filtering
SELECT * from stu_info where id = 1;
--can use and or not
SELECT * from goods where Sal > ID <4;
SELECT * from goods where sal>500 or Sal <2000;
SELECT * from goods where not (Sal > 100);
--can use between ... and ... Show values for a range
Select Sal from goods where Sal between and 2000;

--********************************** ORDER BY clause ***************************************
--Sort *desc descending ASC Ascending *
SELECT * from Stu Order by Yuwen;

--********************************** GROUP BY clause ***************************************
--Group Statistics * For data after grouping statistics with having filter *
SELECT * from Stu Group by Yuwen;

Select Deptno,avg (SAL) as avg_sal from EMP Group by DEPTNO have avg_sal<2000;

--********************************** like clause ***************************************
--% represents any 0 to more characters
SELECT * from Stu where name is like "Wang%";
--_ denotes any single character
SELECT * from Stu where name is like "_ Three";

--********************************** MySQL function ***************************************

--********************************** aggregation function ***************************************
--count () * Records the number of records that meet the criteria count (*) Statistics all count (column name) does not count null values *
Select COUNT (*) from Stu;
Select COUNT (*) from Stu where yuwen>50;

--sum () * Record sum sum (column name) () cannot be used in * null+ any value is null *
Select SUM (Yuwen) from Stu;

--avg () * Record average () cannot be * *
Select AVG (Yuwen) from Stu;

--max (max)/min (min)
Select Max (Yuwen) from Stu;
Select min (Yuwen) from Stu;

--********************************** Date Function ***************************************

--current_date () * Query current date Display Month Day *
Select Current_date ();
--current_time () * Query the current time when the display is seconds *
Select Current_time ();
--current_timestamp () * Query the current timestamp displays the current day of the month and seconds *
Select Current_timestamp ();
--now () * Gets the current time display month day seconds

--date * Returns the month and day part of DateTime *
Date (datetime)
--date_add (date,interval val Typ) * Add Val,typ to date time is the type of Val that can be the day of the month and the minute *
Date_add (now (), interval);
--date_sub (date,interval val Typ) * Subtract Val on date time, Typ is the type of Val that can be the day of the month and the minute
Date_sub (now (), interval);
--datediff (Date1,date2) *date1 and date2 The difference return value type is days *
DateDiff (now (), Date_sub (now (), interval))

--timediff (Date1,date2) *date1 and Date2 time difference return value what is the number of seconds *
Timediff (now (), Date_sub (now (), Interval minute));
--year/month/day/date (DateTime) * Returns year/month/day/year-month-day *
Year (now ()); Month (now ()); Day (now ()); Date (now ());

--unix_timestamp () * Returns a timestamp of 1970-1-1 0:0:0 to present time *
Select Unix_timestamp ();
--from_unixtime (val, format) * Converts Val to a time format you want Val is the number of seconds, the format is set by the programmer himself%y-%m-%d%h-%i-%s *
Select From_unixtime (Unix_timestamp (), '%y-%m-%d ');

--********************************** string function ***************************************
--charset (str) * return character Set *
Select CharSet (' abc ');
--concat (str str str) * Link String *
Select Concat (Name, "Language score is", Yuwen) from Stu_info;
--ucase (str)/lcase (str) * converted to uppercase/lowercase *
UCase ("abc"); LCase ("ABC");
--length (STR) * Calculates the length unit character of Str *
Length (' abc ')
--replace (STR,STR1,STR2) * Change the str1 inside str into str2*
Replace ("ABC", B,D);
--substring (STR,VAL,VAL2) * intercepts val2 characters val,val2 as integers in str from Val *
SUBSTRING ("ABCDEFG", 2,4);

--********************************** mathematical function ***************************************
--abs (int) * To find the absolute value of an int *
ABS (-90);
--ceiling (num) * Integer for num *
Ceiling (34.1456); 35
--floor (num) * The integer of num is evaluated down *
Floor (34.1456); 34
--format (num,val) * Reserved number of decimal places Val is fractional after keeping several *
Format (78.329,2); 78.33
--mod (NUM1,NUM2) * Seek the remainder of NUM1 and num2 *
MoD (10,3) more than 1
--rand () * Returns a random number range of 0 to 1.0*
Select Floor (rand () *100);

--********************************** Process Control Function ***************************************
--if (EX1,EX2,EX3) * If Ex1 is true then return EX2 otherwise return ex3*
if (0==0,1,2);
--ifnull (EX1.EX2) * If EX1 is not null return EX1 otherwise return ex2*
Ifnull (null,0);
--case when Ex1 then ed1 else Ed2 end *ex1 is conditional, Ed is an expression *
Select Case
When sal<100 then sal*10
else Sal
End

--********************************** Other Functions ***************************************
--user () * View Current User *
Select User ();
--database () * See which database is currently in use *
Select Database ();
--MD5 () * Encryption method *
MD5 ("ABC");
--password * Encryption Method *
Password ("abc");
--mysql_num_fields () * Gets the number of fields in the result set *
--mysql_field_name () * Gets the field name of the specified field in the result *

--********************************** Limit Paging Query ***************************************
--First set the number of bars displayed per page $pagesize = 10;
--At the beginning of the calculation of the display (you want to take out the number of pages of data-1) * per page $pagenow represents the page you want to remove the data
($pagenow-1) * $pagesize, $pagesize
SELECT * FROM EMP limit 8, 4;

--********************************** Query Strengthening--multi-table multi-condition query ***************************************
--
Select AVG (SAL), Deptno
From EMP
Group BY Deptno;

--Things
-Open Things
Start transaction
--Auto commit off
Set Autocommit=false;
--Set Save point
SavePoint Name
--Return
Rollback to name

Reprint please indicate source original blog: https://www.cnblogs.com/we-jack/p/8341120.html

MySQL Base statement