Basic Database Operations

update user set password=password('123456')where user='root'; Modify password Flush Privileges; Update show databases; Use dbname; Open database show tables; Describe user table describe user table describe user table describe user table describe user Mysql > create database name; mysql > create database name; Create database use databasename; Select databaseexit; Exit the Mysql? Command keyword: Ask for help -- indicates a commentCopy the code

Create database:

CREATE DATABASE Specifies the DATABASE name// Create database

Copy the code

Delete database:

drop database [ifExists] Database name;Copy the code

Check the database:

show databases;

Copy the code

Using a database:

Use Database name;Copy the code

The operation of the table

Pseudo code:

create table [if notExists] 'table name' ('Field name 1'Column type [attribute][index][comment],'Field name 2'Column type [attribute][index][comment],'Field name n'Column type [attribute][index][comment])[table type][Table Character set][comment]Copy the code

Example:

CREATE TABLE 'grade' (' gradeid 'INT') CREATE TABLE 'grade' (' gradeid 'INT')10) NOT NULL AUTO_INCREMENT COMMENT 'grade ID',
	`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name'. PRIMARY KEY (' gradeID ') ENGINE=INNODB DEFAULT CHARSET= UTF8 PRIMARY KEY (' gradeID ') ENGINE=INNODB DEFAULT CHARSET= UTF8 PRIMARY KEY (' gradeID ') ENGINE=INNODB DEFAULT CHARSET= UTF8 PRIMARY KEY (' gradeID ') ENGINE=INNODB DEFAULT CHARSET= UTF8 PRIMARY KEY (' gradeID ') ENGINE=INNODB DEFAULT CHARSET= UTF8 PRIMARY KEY (' gradeID ') ENGINE=INNODB DEFAULT CHARSET= UTF8 `Student` ( `Sno` INT(4) NOT NULL COMMENT 'student id',
	`Sname` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'name',
	`Ssex` TINYINT(1) DEFAULT '1' COMMENT 'gender',
	`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
	`phoneNum` VARCHAR(50) NOT NULL COMMENT 'mobile phone',
	`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
	`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
	`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'Id Number',
	PRIMARY KEY (`studentno`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
	(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

Copy the code

Primary key:

PRIMARY KEY (' field name ')

Copy the code

Foreign keys:

KEY FK_ 'field name' (' field name ')Copy the code

** Foreign key constraint: ** Adding a constraint to a foreign key field executes a reference to the same field name in another table

CONSTRAINT 'FK_ Field name'FOREIGN KEY (' field name ')REFERENCES 'Another table name'(' Name of another table field ')

Copy the code

Set non-empty:

NOT NULL

Copy the code

Since the increase:

AUTO_INCREMENT

Copy the code

Remark:

COMMENT 'Remarks'Copy the code

Default value:

DEFAULT 'Default'

Copy the code

Set index:

ENGINE=InnoD

Copy the code

Set the default character encoding:

DEFAULT CHARSET=utf8

Copy the code

Alter table name:

ALTER TABLE RENAME a new TABLE nameCopy the code

Add fields:

ALTER TABLE ALTER TABLE name ADD TABLE nameCopy the code

Modify field:

ALTER TABLE ALTER TABLE name ALTER TABLE name column type [attribute] ALTER TABLE name ALTER TABLE name column type [attribute]Copy the code

Delete field:

ALTER TABLE TABLE name DROP Field nameCopy the code

Delete predicate method:

DROP TABLE [IF EXISTS] Specifies the name of the TABLECopy the code

Add data

The INSERT command

Grammar:

INSERT INTO < table name >[(< field1>, < field2>, < field3>,...). ]  VALUES('1'.'value 2'.Value '3')

Copy the code

Example 1:

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES(1001.'Joe'.'male'.'Information Engineering'.18)

Copy the code

Example 2:

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES(1001.'Joe'.'male'.'Information Engineering'.18).(1002.'bill'.'male'.'Information Engineering'.19)

Copy the code

Delete the data

The DELETE command

TRUNCATE command

Clear table data completely, but table structure, index, constraint and so on remain unchanged;

Grammar:

DELETE FROM < table name > [WHERE < condition >];Copy the code

Example:

DELETE
FROM Student
WHERE Sno = 10001;

Copy the code

Modify the data

The update command

Grammar:

UPDATE <表名> SET <表名> =< expression > [, <表名> =< expression >]... [WHERE < condition >];Copy the code

Example 1:

UPDATE Student SET Sdept = 'computer',Sage =23
WHERE Sno = 1001

Copy the code

Example 2: Multiple conditions

UPDATE Student SET Sdept = 'computer',Sage =23WHERE Sname = 's3'andSsex = 'male'Copy the code

Where condition statement:

The operator	meaning	usage
=	Is equal to the	Sage = 18
< > and! =	Is not equal to	Sage ! = 0
>	Is greater than	Sage > 1
<	Less than	Sage < 100
> =	Greater than or equal to	Sage >= 1
< =	Less than or equal to	Sage <= 100
Sage <= 100	Between a certain range	BETWEEN 1 AND 100
AND	and	Sage >0 AND Sage<101
OR	or	Sage > 0 OR Sage < 101

Query data

The select command

Simple syntax:

Select * from Student select * from StudentCopy the code

Specify field query:

Select Sno,Sname from StudentCopy the code

Alias: you can give it to….. Field alias; You can also go to…. Table names

Selcet Sno,Sname as name from StudentCopy the code

Function Concat(< string >,< list name >) : concatenates strings

select CONCAT('Name:',Sname)From Student -- query result name: zhang SANCopy the code

DISTINCT Deduplication: deletes duplicate data

SELECT * FROM result where (SELECT * FROM result); SELECT studentno FROM result; SELECT DISTINCT studentno FROM result; DISTINCT removes duplicates (default: ALL)Copy the code

Where condition statement

Operator name	grammar	describe
AND or &&	A AND B or a && B	The result is true only if the logic and theta are true at the same time
OR	a OR b	Logical or, as long as one is true, the result is true
Or NOT!	NOT a or! a	If the operand is false, the result is true!

Example:

-- query test scores in95- 100.SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100;

SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100; - in addition to1000SELECT studentno,studentresult FROM result WHERE studentno! =1000; -- Use NOT SELECT studentno,studentresult FROM result WHERE NOT studentno=1000;

Copy the code

Fuzzy query: The comparison operator

Operator name	Operator name	describe
IS NULL	a IS NULL	If the operator is NULL, the result is true
IS NOT NULL	a IS NOT NULL	If the operator is not NULL, the result is true
BETWEEN	a BETWEEN b AND c	If a ranges between B and C, the result is true
LIKE	a LIKE b	SQL mode matching, if A matches B, the result is true
IN	A IN (a1, a2, a3,……)	If a is equal to a1,a2….. , the result is true

Example:

LIKE the use of

= = = = = = = = = = = = = = = = =0SELECT Sno,Sname FROM Student WHERE Sname LIKE SELECT Sno,Sname FROM Student WHERE Sname LIKE'a %'; SELECT Sno,Sname FROM Student WHERE studentname LIKE SELECT Sno,Sname FROM Student WHERE studentname LIKE'a _'; SELECT Sno,Sname FROM Student WHERE Sname LIKE; SELECT Sno,Sname FROM Student WHERE Sname LIKE'a __';

Copy the code

IN the use of

-- Query the student id as1000.1001.1002SELECT Sno, SELECT Sno,Sname FROM Student
WHERE Sno IN (1000.1001.1002); SELECT Sno,Sname, Sno, Sno, Sno, Sno, Sno, Snoaddress FROM Student
WHERE address IN ('guangzhou'.'shenzhen');

Copy the code

The use of the NULL

SELECT Sname FROM Student WHERE sdate ISNULL; SELECT Sname FROM Student WHERE BornDate IS NOTNULL; SELECT Sname FROM Student WHERE Sname = (SELECT Sname FROM Student WHERE Sname = (SELECT Sname FROM Student WHERE Address=)'' OR Address IS NULL;

Copy the code

Multi-table query

JOIN the connection:

Operator name	describe
INNER JOIN	Intersection between two tables
LEFT JOIN	Intersection between two tables, and left table concatenation
RIGHT JOIN	Intersection between two tables, and to the right table concatenation

Inner join queries the intersection of the result sets in two tables outer join queries the union of the result sets in two tables Left join uses the left table as the reference, matches the right table one by one, and returns the records in the left table, and fills the right table with NULL Right join uses the right table as the benchmark, and matches the left table one by one. If no match is found, the records of the right table are returned, and the left table is filled with NULL

Select * from Student (Student number, Student name, subject number, score); select * from Student (Student number, Student name); select * from result (Student name, subject number, score);

SELECT s.Sno,Sname,subjectno,StudentResult
FROM Student s
INNER JOIN result r
ON r.Sno = s.Sno

Copy the code

- the RIGHT connection (also can realize) SELECT s.S no, Sname, subjectno, StudentResult FROM student "s RIGHT JOIN result r ON r.S no = s.S noCopy the code

- equivalent to connect the SELECT s.S no, Sname, subjectno, StudentResult FROM Student s, WHERE r.S no result r = s.S noCopy the code

- LEFT connection (query to all students, not to test the also found out) SELECT s.S no, Sname, subjectno, StudentResult FROM Student s LEFT the JOIN result r ON r.S no = s.S noCopy the code

- check the lack of test students (LEFT connection application scenario) SELECT s.S no, Sname, subjectno, StudentResult FROM Student s LEFT the JOIN result r ON r.S no = s.S no WHERE  StudentResult ISNULL

Copy the code

- consider: query took part in the examination of Student information (Student id, Student's name, course name, score) SELECT s.S no, Sname, Sname, StudentResult FROM Student s INNER JOIN result ON r.S no = r  s.Sno INNER JOIN `subject` sub ON sub.Sno = r.SnoCopy the code

Self join query

CREATE TABLE 'category' (' categoryid 'INT(' categoryid'))10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'theme id',
`pid` INT(10) NOT NULL COMMENT 'parent id',
`categoryName` VARCHAR(50) NOT NULL COMMENT 'Subject name',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9DEFAULT CHARSET= utF8 INSERT INTO 'category' (' categoryID ', 'pid', 'categoryName') VALUES('2'.'1'.'Information Technology'),
('3'.'1'.'Software Development'),
('4'.'3'.'Database'),
('5'.'1'.'Art Design'),
('6'.'3'.'web development'),
('7'.'5'.'ps technology'),
('8'.'2'.'Office Information');

Copy the code

Example operation:

SELECT a.categoryName AS SELECT a.categoryName AS SELECT a.categoryName AS SELECT a.categoryName AS SELECT a.categoryName AS SELECT a.categoryName AS SELECT a.categoryName AS SELECT a.categoryName AS SELECT a.categoryName AS'Father column',b.categoryName AS 'Subcolumn'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

Copy the code

- consider: query took part in the examination of student information (student id, student's name, course name, score) SELECT s.s tudentno, studentname, subjectname, StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON sub.subjectno = r.subjectnoCopy the code

SELECT studentno AS studentNo, studentName AS studentname,gradename AS grade FROM student s INNER JOIN grade SELECT studentno AS studentNo, studentName AS studentname,gradename AS grade FROM student s INNER JOIN grade g ON s.`GradeId` = g.`GradeID`Copy the code

-- Query SUBJECT and grade (SUBJECT name,gradename) SELECT SubjectName AS SUBJECT name, Gradename AS gradename FROM SUBJECT sub INNER JOIN Grade G ON sub.gradeID  = g.gradeidCopy the code

Query the database structure- 1All the exam results (student id student's name Course name Grades) SELECT s.s tudentno, studentname, subjectname, StudentResult FROM student s INNER JOIN result ON r r.studentno = s.studentno INNER JOIN `subject` sub ON r.subjectno = sub.subjectno WHERE subjectname='Database structure-1'

Copy the code

The subquery

What is a subquery? In the WHERE condition clause of the query statement, another query statement is nested. The nested query can be composed of multiple subqueries, and the solution is from inside to outside. The results returned by subqueries are usually collections, so it is recommended to use the IN keyword.

Operation example:

Query the database structure- 1All the exam results (student id, course number, grade), and performance descending order, method one: use the connection query SELECT studentno, r.s ubjectno, StudentResult FROM result r INNER JOIN ` subject `  sub ON r.`SubjectNo`=sub.`SubjectNo` WHERE subjectname ='Database structure-1'ORDER BY studentresult DESC; - method 2: use a subquery (execution sequence: FROM inside and outside), the SELECT studentno subjectno, StudentResult FROM the result the WHERE subjectno = (SELECT subjectno the FROM `subject` WHERE subjectname ='Database structure-1'
)
ORDER BY studentresult DESC;

Copy the code

-- The query course is Advanced Mathematics2 -And the score is not less than80SELECT s.studententno,studentname FROM student s INNER JOIN result r ON s. 'StudentNo' = SELECT s.studentno,studentname FROM student s INNER JOIN result r ON s r.`StudentNo` INNER JOIN `subject` sub ON sub.`SubjectNo` = r.`SubjectNo` WHERE subjectname ='Higher Mathematics -2' AND StudentResult>=80-- Method 2: Use join query + subquery -- score not less than80SELECT r.studententno,studentname FROM student s INNER JOIN result r ON S. 'StudentNo' = R. 'StudentNo' WHERE StudentResult>=80

Copy the code

SELECT studentno, studentno, studentno, studentno, studentno, studentno, studentnostudentname FROM student WHERE studentno IN(
	SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
		SELECT subjectno FROM `subject` WHERE subjectname = 'Higher Mathematics -2'))

Copy the code

Commonly used functions

Data function:

SELECT ABS(- 8 -); /* absolute value */
SELECT CEILING(9.4); /* Round up */
SELECT FLOOR(9.4); /* Round down */
SELECT RAND(a); Return a random number between 0 and 1 */
SELECT SIGN(0); /* Sign function: negative numbers return -1, positive numbers return 1,0 returns 0*/

Copy the code

String functions:

SELECT CHAR_LENGTH('I'm learning Mysql'); Return the number of characters in the string */
SELECT CONCAT('我'.'love'.'process'); /* Merges strings. Arguments can have more than one */
SELECT INSERT('I love programming HelloWorld'.1.2.'Super in love'); /* Replace the string, starting from a certain position to replace a certain length */
SELECT LOWER('Study'); / * lowercase * /
SELECT UPPER('Study'); / * capital * /
SELECT LEFT('hello,world'.5); /* Intercept */ from the left
SELECT RIGHT('hello,world'.5); /* Intercept */ from the right
SELECT REPLACE('Persistence leads to success'.'stick to'.'努力'); /* Replace the string */
SELECT SUBSTR('Persistence leads to success'.4.6); /* Intercept the string, start and length */
SELECT REVERSE('Persistence leads to success'); SELECT REPLACE(studentname,' studentname ',' studentname ') AS new name FROM student WHERE studentname LIKE 'studentname ';Copy the code

Date and time functions:

SELECT CURRENT_DATE(a); /* Get the current date */
SELECT CURDATE(a); /* Get the current date */
SELECT NOW(a); /* Get the current date and time */
SELECT LOCALTIME(a); /* Get the current date and time */
SELECT SYSDATE(a); /* Get the current date and time */-- Get date, month, hour, minute, secondSELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

Copy the code

Aggregation function

The name of the function	describe
COUNT()	Return the total number of records that meet the Select condition. For example, Select count(*)
SUM()	Returns a numeric field or expression column for statistics. Returns the sum of a column.
AVG()	Usually for numeric fields or expression columns, returns the average value of a column
MAX()	Statistics can be made for numeric fields, character fields, or expression columns, returning the maximum value.
MIN()	Statistics can be made for numeric fields, character fields, or expression columns, returning the minimum value.

Example:

Prerequisite: SELECT subjectName,AVG(StudentResult) AS average score,MAX(StudentResult) AS maximum MIN(StudentResult) AS MIN FROM result AS R INNER JOIN 'subject' AS s ON R.subjectno = S.subjectno GROUP BY R.subjectno HAVING average score >80;

Copy the code

[Mysql database]