MySQL Common commands

1.# Start/Stop service
NET Start/stop service Name

2.# Connection Database
Mysql-u Root-proot

3.# Creating a Database
CREATE database name;

4.# View all databases
SHOW DATABASES;

5.# using the database
Use database name;

6.# Deleting a database
DROP database name;

7.# Creating a table (selecting a database before creating a table)
CREATE TABLE Student (studentno int (4) Primary key,name varchar (20));

8.# View all tables under the current database
SHOW TABLES;

9.# Adding a PRIMARY KEY constraint
CREATE TABLE Student (Studentno INT (4) PRIMARY KEY);

10.# Adding comments
CREATE table Test (ID INT (one) UNSIGNED COMMENT ' number ') comment= ' Test table ';

11.# View Table Structure
DESC table name;

12.# Deleting a table
DROP table name;

13.# Modifying table names
ALTER table Old table name rename[to] new table name;

14.# Adding fields
ALTER Table name ADD field Data Type property | constraint;

15.# modifying fields
ALTER Table name change original field name new field name data type attribute | constraint;

16.# Delete a field
ALTER table name DROP field name;

17.# Adding a primary key
ALTER table name ADD CONSTRAINT PRIMARY Key name PRIMARY key table name (primary key field);

18.# adding foreign keys
ALTER table name ADD CONSTRAINT foreign key name FOREIGN key (foreign key field) REFERENCES associated table name (associated field);

19.# MySQL system help
What help is needed;

20.# View the storage engine for the current database
SHOW VARIABLES like ' storage_engine% ';

21.# inserting a piece of data
INSERT into table name [(List of field names)] Values (value lists);

22.# inserting more than one data
INSERT into new Table (field list) VALUES (Value List 1), (Value List 2),......, (value list n);

23.# to place query results in a new table
CREATE Table New Table (SELECT field 1, Field 2 ...). From the original table);

24.# Query All results
SELECT * from table name;

25.# Modifying data
UPDATE table name SET field 1= value 1, field 2= value 2,..., segment n= value n [WHERE condition];

26.# Deleting data
TRUNCATE table name; #TRUNCATE语句删除后将重置自增列, the table structure and its fields, constraints, and indexes remain unchanged and execute faster than the DELETE statement
DELETE from table name [where condition];

27.# Querying all data
SELECT * from table name;

28.# Query Partial columns
SELECT column 1, column 2,... from table name [where condition] order by according to which column to sort [ASC/DESC];

29.# to list aliases
SELECT Studentno as student number, studentname as student's name, address as student addresses from student WHERE address <> ' Henan Xinxiang ';

30.# Querying empty rows
SELECT studentname from Student WHERE email is NULL;

31.# LIMIT clause limits result set
List of SELECT < field names >
From < table name or view >
[WHERE < query conditions;]
[GROUP by < Group field name;]
[ORDER BY < sorted column name >[ASC or DESC]]
[LIMIT [position offset,] number of rows];

32.# Sub-query
SELECT ... From table 1 WHERE field 1 comparison operator (subquery)
Using subqueries and comparison operators together, you must ensure that the subquery returns no more than one value

33.# in sub-query
SELECT Studentname from Student
WHERE Studentno in (
SELECT Studentno from result
WHERE Subjectno = (
SELECT Subjectno from subject
WHERE subjectname = ' Logic Java '
) and studentresult = 60);

35.# not in sub-query
SELECT Studentname from Student
WHERE Studentno Not IN (
SELECT Studentno from result
WHERE Subjectno = (
SELECT Subjectno from subject
WHERE subjectname = ' Logic Java '
) and studentresult = 60);

36.# exists sub-query
Select ... from table name where exists ();
The subquery returns the result, exists returns TRUE, executes the outer query, the subquery returns no results, exists returns false, and the outer query does not execute

37.# sorting after grouping
SELECT sex as gender, Gradeid as grade number, COUNT (*) as number from student
GROUP by Sex,gradeid
ORDER by Gradeid;

38.# grouping after filtering
SELECT * FROM student WHERE studentno>1
GROUP by Studentno
Having studentno>2;

39.# Internal Connection Query
SELECT Sub.subjectname,result.studentno,result.result from Sub,result
WHERE Sub.subjectno=result.subjectno;

SELECT S.subjectname,r.studentno,r.result from Sub S INNER JOIN result R
On (S.subjectno = R.subjectno);

#左外连接查询.
SELECT S.subjectname,r.result,r.studentno from Sub as S
Left JOIN result as R on s.subjectno = R.subjectno;

#右外连接查询.
SELECT S.subjectname,r.result,r.studentno from Sub as S
Right JOIN result as R on s.subjectno = R.subjectno;

MySQL Common commands