​

This is the 9th day of my participation in Gwen Challenge

Enabling the SQL Service

You can open the task manager and click on the service, find MQSQL and right click on the service

​

Another way is to enter commands in CMD

Net start mysql -- Start mysql service. Net stop mysql -- Stop mysql serviceCopy the code

Log in to mysql using the cli

CD mysql/bin // You need to mount it to the mysql folder. For details, see the installation path mysql -h hostname -u username -pCopy the code

Basic database operations

CREATE DATABASE mydb; // Create database USE database_name; DROP DATABASE database_name; // Delete the databaseCopy the code

Data table operations

CREATE TABLE student -- CREATE TABLE student (sno char(10) PRIMARY KEY, -- set PRIMARY KEY ssex char(2) DEFAULT 'male ', -- Set the DEFAULT constraint sbirthday date DEFAULT '1992-01-01', -- birth date tuixue tinyint(1) NOT NULL DEFAULT 0, -- set non-null and DEFAULT constraint); DESCRIBE student; ALTER TABLE student ADD class char(10) AFTER ssex; ALTER TABLE sc MODIFY degree char; ALTER TABLE student DROP class; ALTER TABLE a CHANGE name newname date; Alter table A rename name to newname; ALTER TABLE sc RENAME score; Alter table sc alter table scoreCopy the code

Operation of data

INSERT INTO student(sno,sname) VALUES ('2005010104',' sno '),('data','name'); UPDATE sc SET degree=60 WHERE degree<60; DELETE FROM student WHERE sno='2005030301';Copy the code

Query of data

Operator for the WHERE condition

Comparison operator

=, <, >, < =, > =, < > and! =,! <,! >

Range operator

BETWEEN AND,NOT BETWEEN AND

List operator

IN,NOT IN

Character card

LIKE,NOT LIKE

A null value

IS NULL,IS NOT NULL

Logical operator

AND,OR,NOT

Copy the data queried from the SC table to the A table

INSERT INTO a(SNO,AVG_GRADE)
SELECT sno,AVG(degree)
FROM sc
GROUP BY sno
HAVING AVG(degree)>80;
Copy the code

Advanced query

An aggregate function can appear as a column identifier in a target column of a SELECT clause, in a condition of a HAVING clause, or in an ORDER BY clause.

Aggregation function

The specific use

Specific meaning

COUNT

COUNT([DISTINCT|ALL]*)

Statistics the number of tuples

COUNT

COUNT ([DISTINCT | ALL] < name >)

Count the number of values in a column

SUM

The SUM ([DISTINCT | ALL] < name >)

Computes the sum of a column of values (this column must be numeric)

AVG

AVG ([DISTINCT | ALL] < name >)

Calculate the average of a column of values (this column must be numeric)

MAX

MAX ([DISTINCT | ALL] < name >)

Find the maximum value in a column

MIN

MIN ([DISTINCT | ALL] < name >)

Find the minimum in a column of values

SELECT COUNT(*) FROM student; SELECT COUNT(DISTINCT sno) FROM SC; Select * from student where course number is selectedCopy the code

The GROUP BY clause allows you to sort the query results BY one or more column data values, in other words, to summarize the information about the query results to summarize the relevant data.

SELECT ssex,COUNT(*) FROM student GROUP BY ssex;Copy the code

Backup and Restoration

Mysqldump is a useful database backup tool provided by MySQL. The utility is stored in the C:\Program Files\MySQL\MySQLServer 5.5bin folder. When you run Mysqldump, you can back up the database into a text file that actually contains multiple CREATE and INSERT statements that can be used to recreate tables and INSERT data.

Mysqldump -u root -h localhost -p gradeM >d:\bak\gradembak. SQL mysqldump -u root -h localhost -p gradeM >d:\bak\gradembak. Mysqldump -u root -h localhost -p --databases gradem mydb> D: grademdb.sql D :\bak\gradembak. SQL // Restore databaseCopy the code

Content continues to update, like might as well collect first

​