1, Basic command line operations

mysql -u username - p password   ；--- Connect to database

flush privileges ；   Refresh permissions

show databases ； --- View all databases

mysql > use   Database name--- Switch database

mysql> show   tables ；--- Displays information for all tables in the database

MySQL > describe table name   ；---- Displays the details of the table

create database database name; Create a database

Exit -- exit the connection

--Single line note

/**Multiline comment*/

2, MYSQL language

There are four languages in MYSQL:

DDL (data   definition language

DML (data   Database Manipulation Language

DCL (data control language) database control language

DQL (data query language) database query language

We use the most query languages in our work and focus on learning.

1. Operation database

Create database:

CREATE DATABASE [IF NOT EXISTS] teacher;

Delete database:

DROP DATABASE [IF EXISTS] teacher;

Use database:

use teacher；

View database:

SHOW DATABASES; -- View all databases

2. Create database tables

Format:

CREATE TABLE [IF NOT EXISTS] ` table name `(
    ` Field name ` column type [attribute]   [index]   [note],
    ` Field name ` column type [attribute]   [index]   [note],
    ` Field name ` column type [attribute]   [index]   [note],
    ...
)[table type] [character set setting] [note]

Note:

--In English ()   The names and fields of the table shall be enclosed with ` ` as far as possible
-- AUTO_INCREMENT self increment
--Strings are enclosed in single quotes
--All statements are followed by English commas, and the last one is not
--PRIMARY KEY: a table generally has only one unique PRIMARY KEY
--Comment after COMMIT

Common commands:

SHOW CREATE DATABASE `cgb1111` ; -- View the statement that created the database
SHOW CREATE TABLE `teacher`; -- View the definition statement of student data table

DESC `teacher`; -- View table structure

3. Modify and delete data table fields

Modification:

-- Modify table name

ALTER TABLE `Original table name` RENAME AS `New table name`;
ALTER TABLE `teacher` RENAME AS `teacher1`;

-- Fields of new table:

 ALTER TABLE `Table name` ADD Field column properties
ALTER TABLE `teacher1` ADD age INT(3);

-- Modify table fields (rename, modify constraints!):       
-- ALTER TABLE `Table name` MODIFY Field column properties; 
-- ALTER TABLE `Table name` CHANGE Original field name and current field column attribute;
ALTER TABLE `teacher1` MODIFY age VARCHAR(3); -- Modify constraints

ALTER TABLE `teacher1` CHANGE age age1 INT(3);-- Field rename

-- Delete table fields:
ALTER TABLE `teacher1` DROP age1;

--Delete table:

DROP TABLE IF EXISTS teacher1;

4.DML: database operation language: add, delete, change

Insert: insert language

-- INSERT INTO `Table name`(`Field 1`,`Field 2`,`Field 3`...) VALUES('Value 1','Value 2','Value 3'...);

INSERT INTO `grade`(`name`) VALUES('Senior');
INSERT INTO `student`(`name`,`birthday`,`id_grade`) VALUES('Zhang San','1995-11-20','1');
-- Generally, when writing insert statements, we must correspond the data and fields one by one.

-- Insert multiple
-- INSERT INTO `Table name`(`Field 1`,`Field 2`...) VALUES('Value 1','Value 2'...),('Value 1','Value 2'...)...;

INSERT INTO `grade`(`name`) VALUES('Freshman'),('Sophomore');
INSERT INTO `student`(`name`,`birthday`,`id_grade`) VALUES('Li Si','1994-11-20','2'),('Wang Wu','1995-11-20','1');

matters needing attention:

~Fields are separated by commas

~Fields can be omitted, but the following values must correspond to the table fields one by one

~Multiple pieces of data can be inserted at the same time. The values after values need to be separated by commas. VALUES(), ()

updata: modify statements

-- Modify teacher name
UPDATE `teacher` SET NAME='zyy' WHERE id='1';

-- Locate data through multiple conditions
UPDATE `student` SET NAME='hehe' WHERE NAME='zyy' AND id_grade='2';

-- If no condition is specified, all tables will be changed
UPDATE `student` SET NAME='all';

-- grammar
-- UPDATE Table name SET Listing=value[,Listing=value,Listing=value,Listing=value...] [WHERE condition]

matters needing attention:

~Criteria, filter criteria. If not specified, all columns will be modified

~The attributes of multiple settings are separated by English commas

Delete: delete statement

-- Delete data (avoid writing like this, all data will be deleted)

DELETE FROM `student`;
-- Delete specified data

DELETE FROM `student` WHERE id=1; 

--Syntax: `delete from Table name [where condition]`

truncate: delete statement

-- Empty table
TRUNCATE `student`;

--Function: completely empty a database table, and the target structure and index constraints will not change!

The difference between delete and tuncate

Same point: data can be deleted without deleting table structure

Different:

~truncate resets the auto increment column and the counter will return to zero

~truncate does not affect transactions

-- test delete and truncate difference
CREATE TABLE `test`(
  `id` INT(4) NOT NULL AUTO_INCREMENT,
  `coll` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- It will not affect self increment
DELETE FROM `test`;

-- Auto increment to zero
TRUNCATE TABLE `test`;

INSERT INTO `test` (`coll`) VALUES ('1'),('2'),('3'),('4');

5.DQL: database query language: select

~All query operations use select

~It can do simple query and complex query

~Database is the core language and the most important statement

~Most frequently used statements

select 

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- Joint query
    [WHERE ...]  -- Specify the conditions to be met for the results
    [GROUP BY ...]  -- Specify which fields the results are grouped by
    [HAVING]  -- Secondary conditions that must be met to filter grouped records
    [ORDER BY ...]  -- Specifies that query records are sorted by one or more criteria
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  Specify which records to query from

Description: [] represents optional. {} represents a required choice| Indicates or.

First create a table and add data:

DROP DATABASE IF EXISTS `school`;
-- Create a school database
CREATE DATABASE IF NOT EXISTS `school`;
-- use school database
USE `school`;
-- Create student table
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
    `student_no` INT(4) NOT NULL COMMENT 'Student number',
    `login_pwd` VARCHAR(20) DEFAULT NULL,
    `student_name` VARCHAR(20) DEFAULT NULL COMMENT 'Student name',
    `sex` TINYINT(1) DEFAULT NULL COMMENT 'Gender, 0 or 1',
    `grade_id` INT(11) DEFAULT NULL COMMENT 'Grade number',
    `phone` VARCHAR(50) NOT NULL COMMENT 'contact number',
    `address` VARCHAR(255) NOT NULL COMMENT 'address',
    `born_date` DATETIME DEFAULT NULL COMMENT 'time of birth',
    `email` VARCHAR (50) NOT NULL COMMENT 'Email account',
    `identity_card` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
    PRIMARY KEY (`student_no`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- Create grade table
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
  `grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Grade number',
  `grade_name` VARCHAR(50) NOT NULL COMMENT 'Grade name',
   PRIMARY KEY (`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET = utf8;

-- Create chart of accounts
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
  `subject_no`INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Course number',
  `subject_name` VARCHAR(50) DEFAULT NULL COMMENT 'Course name',
  `class_hour` INT(4) DEFAULT NULL COMMENT 'Class hours',
  `grade_id` INT(4) DEFAULT NULL COMMENT 'Grade number',
   PRIMARY KEY (`subject_no`)
)ENGINE = INNODB  DEFAULT CHARSET = utf8;

-- Create grade sheet
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
  `student_no` INT(4) NOT NULL COMMENT 'Student number',
  `subject_no` INT(4) NOT NULL COMMENT 'Course number',
  `exam_date` DATETIME NOT NULL COMMENT 'Test date',
  `student_result` INT (4) NOT NULL COMMENT 'Examination results'
  )ENGINE = INNODB DEFAULT CHARSET = utf8;
  
-- Insert student data and add the rest by yourself. Only 2 rows are added here
INSERT INTO `student` (`student_no`,`login_pwd`,`student_name`,`sex`,`grade_id`,`phone`,`address`,`born_date`,`email`,`identity_card`)
VALUES
(1000,'123456','Zhang Wei',0,2,'13800001234','Chaoyang, Beijing','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','Qiang Zhao',1,3,'13800002222','Shenzhen, Guangdong','1990-1-1','text111@qq.com','123456199001011233');

-- Insert grade data
INSERT INTO `grade` (`grade_id`,`grade_name`) VALUES(1,'Freshman'),(2,'Sophomore'),(3,'Junior'),(4,'Senior'),(5,'Preparatory class');

-- Insert account data
INSERT INTO `subject`(`subject_no`,`subject_name`,`class_hour`,`grade_id`)VALUES
(1,'Advanced mathematics-1',110,1),
(2,'Advanced mathematics-2',110,2),
(3,'Advanced mathematics-3',100,3),
(4,'Advanced mathematics-4',130,4),
(5,'C language-1',110,1),
(6,'C language-2',110,2),
(7,'C language-3',100,3),
(8,'C language-4',130,4),
(9,'Java Programming-1',110,1),
(10,'Java Programming-2',110,2),
(11,'Java Programming-3',100,3),
(12,'Java Programming-4',130,4),
(13,'database structure -1',110,1),
(14,'database structure -2',110,2),
(15,'database structure -3',100,3),
(16,'database structure -4',130,4),
(17,'C#Foundation ', 130,1);

-- Insert score data. Only one group is inserted here, and the others are added by themselves
INSERT INTO `result`(`student_no`,`subject_no`,`exam_date`,`student_result`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

Simple query statement:

 -- Query all students   SELECT field FROM Table name;
  SELECT * FROM student;
  
  -- Query specified fields
  SELECT student_name, student_no FROM student;
  
  -- Alias, give the result a name AS  You can alias fields or tables
  
  SELECT student_name AS 'Student number', student_no AS 'full name' FROM student;
  
  -- function concat(a,b)
  SELECT CONCAT('full name:', student_no)  AS 'New name' FROM student;

  --If the list name is not so well known, we can alias it

De duplication query distinct

  -- Query all test scores

  SELECT * FROM result;

  -- Check which students took the exam

  SELECT `student_no` FROM result;

  -- Duplicate data found, de duplication

  SELECT DISTINCT `student_no` FROM result;

Logical operators in the where clause:

Logical operators in where

operator	grammar	describe
and	a and b	Logic and
or	a or b	Logical or
not	not a	Logical non