My little station

1、MySQL The advantages of

• Fast running speed
• Low use cost
• Strong portability
• Applicable to a wide range of users

2、MySQL Operation mechanism of

One SQL sentence , Such as select * from tablename , After coming in from the support interface , After entering the connection pool, do permission 、 Verification and other links , Then determine whether there is a cache , If there is, put the result back directly , Otherwise enter SQL Interface , The query optimizer optimizes before the query , Finally, analyze , Inquire about . And interact with files through the storage engine .

3、MySQL Configuration of

** Port number ：**3306 ** Default character set ：**utf8  take bin Directory write   environment variable  root password

** notes ：**utf8: A kind of Unicode code , Solve the problem of multi byte coding with different characters in the world , Strong commonality my.ini： Record MySQL Configuration information , The configuration content can be modified , Such as the slogan , Character set, etc.

4、 Command line connection MySQL

First check whether the service is started   Properties window  DOS command ：

​ start-up ：net start mysql57

​ stop it ：net stop mysql57

57 Representative is 5.7 Version of , The following do not need to be connected 57

notes ： If the configuration file is modified , Must restart MySQL Service to take effect Command line connection MySQL grammar ：mysql –h Server host address –u user name –p password

l –h Server host address （ If it is local, you can omit this famous sentence ） The password can be omitted , Press enter and enter

5、 System database and user database

System database ： **information_schema:** Some database object information in the main storage system , Such as user table information 、 Field information 、 Permission information 、 Character set information, partition information, etc . **performance_schema:** Main storage database server performance parameters

**mysql:** User rights information for the primary storage system **test:**MySQL Test database automatically created by database management system , Any user can use User database ： User database is a database created by users according to their actual needs . The explanation later in this chapter is mainly aimed at the user database .

6、 Create database

​ set names utf8;  grammar ： create database Database name ** Example ：**create database myschool;

7、 Check the database list

grammar ：show databases;

8、 Select database

grammar ：use Database name ;  notes ： The database must be selected before using data ！

9、 Delete database

grammar ：drop database Database name ;

10、MySQL data type

11、 Create table

grammar ：CREATE TABLE [IF NOT EXISTS] Table name ( Field 1 data type [ Field properties | constraint ][ Indexes ][ notes ], …… Field n data type [ Field properties | constraint ][ Indexes ][ notes ]
)[ Table type ][ Table character set ][ notes ];

example ：

   CREATE TABLE `student`
  (
    `studentNo` INT(4)  PRIMARY KEY auto_increment not null,
        ` name` CHAR(10),
     ……
  );

12、 Field constraints and properties

name keyword explain
Non empty constraint  NOT NULL Field cannot be empty Default constraint  DEFAULT  Give a field a default value Unique constraint  UNIQUE KEY(UK)  The value of the set field is unique Allow null , But there can only be one null value Primary key constraint  PRIMARY KEY(PK)  Set the field as the primary key of the table It can uniquely identify the record of this table Foreign key constraints  FOREIGN KEY(FK)  Used to establish a relationship between two tables Automatic growth  AUTO_INCREMENT  Set the column as a self incrementing field , By default, each self increment 1, Usually used to set the primary key

Primary key ：

 CREATE TABLE student（
      `studentNo` INT(4)  PRIMARY KEY,
      ……）;

notes ：

 CREATE TABLE test (
    `id` int(11) UNSIGNED COMMENT ‘ Number ’
)COMMENT=' The test table ’ ;

Set character set encoding

 CREATE TABLE [IF NOT EXISTS]  Table name （
     # Omit code 
）CHARSET =  Character set name ;

Create a student representation ：

CREATE TABLE `student`(
    `studentNo` INT(4) NOT NULL COMMENT ' Student number ' PRIMARY KEY,
	`loginPwd` VARCHAR(20) NOT NULL COMMENT ' password ',  
	`studentName` VARCHAR(50) NOT NULL COMMENT ' The student's name ',
	`sex` CHAR(2) DEFAULT ' male ' NOT NULL  COMMENT ' Gender ', 
	`gradeId` INT(4)  UNSIGNED COMMENT ' Grade number ', 
	`phone` VARCHAR(50)  COMMENT ' contact number ',
	`address` VARCHAR(255)  DEFAULT ' The address is unknown 'COMMENT ' Address ',
    `bornDate` DATETIME  COMMENT ' time of birth ',
	`email` VARCHAR(50) COMMENT' Email account number ',
	 `identityCard` VARCHAR(18)  UNIQUE KEY COMMENT ' ID number '
) COMMENT=' Student list ';

13、 See the table

See if the table exists

View table definition

notes ： avoid DOS The window is out of order , Executable SET NAMES gbk;

14、 Delete table

** grammar ：**DROP TABLE [IF EXISTS] Table name ; Example ：

notes ： Before deleting the table , First use IF EXISTS Statement to verify that the table exists

15、MySQL Storage engine for

1） The type of storage engine  MyISAM、InnoDB 、Memory、CSV etc. 9 Kind of  2）MyISAM And InnoDB The main difference between types

Experience ：  Applicable occasions   Use MyISAM: No business is needed , Small space , Mainly query and access Use InnoDB: Delete more 、 update operation , High safety , Transaction processing and concurrency control  3） View the current default storage engine  SHOW VARIABLES LIKE ‘storage_engine%’; 4） Modify the storage engine

modify my.ini The configuration file default-storage-engine= InnoDB

5） Set the storage engine for the table   grammar ：CREATE TABLE Table name ( # Omit code )ENGINE= Storage engine ;

16、 The storage location of the data table

1）MyISAM Type table file *.frm： Table structure definition file *.MYD： Data files *.MYI： Index file 2）InnoDB Type table file *.frm： Table structure definition file ibdata1 file Be careful ： Storage location Depending on the operating system , having evidence or referent sources my.ini

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" innodb_data_home_dir="D:/MySQL Datafiles/"

17、MySQL System help

HELP Query content ; HELP contents; HELP Data Types; HELP INT;

Add ：mysql value type Signed in / Unsigned difference

Signed and unsigned , seeing the name of a thing one thinks of its function , Is whether there is a difference between positive and negative : such as 8 For binary , If signed , Need to use 1 Bits represent symbols (1 A negative number ,0 Express positive ), be left over 7 Bits represent data . So the representation range is -127---127( Include -0 and +0). If it's not signed ,8 All bits represent data , So the representation range is 0--256

Add ： mysql Is there any nvarchar nchar

​ Yes , however ,mysql use utf-8 code , The traditional database adopts unicode, One Chinese character needs two unicode Of char, And in the mysql Due to the use of utf-8, So whether Chinese characters or letters , It's all one length char, So you don't have to nvarhcar and varchar 了 , Do it all varchar

SQL senior （ One ）

1、 Modify table

1）# Modify the name of the table  ALTER TABLE The old name of the table RENAME The new name of the table ; 2）# Add fields  ALTER TABLE Table name ADD Field name data type [ attribute ]; 3）# Modify fields  ALTER TABLE Table name CHANGE Original field name new field name data type [ attribute ]; 4）# Delete field  ALTER TABLE Table name DROP Field name ;

2、 Add primary key

grammar ： ALTER TABLE Table name ADD CONSTRAINT Primary key name PRIMARY KEY Table name （ Primary key field ）;

** example ：** Set up grade In the table gradeId The field is the primary key

 alter table `grade` add constraint `pk_grade` primary key `grade`(`gradeId`);

3、 Add foreign keys

grammar ： ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY（ Foreign key field ） REFERENCES Association table name （ Correlation field ）;

** example ：** take student Tabular gradeId Fields and grade Tabular gradeId Field to establish a foreign key Association

alter table `student`  add  constraint fk_student_grade foreign key(`gradeId`) 
references `grade` (`gradeId`);

4、DML sentence -- Insert a single data record

** grammar ：**INSERT INTO Table name [( List of field names )] VALUES ( List of values );

Be careful ：  The field name is optional , If omitted, insert all fields in sequence Multiple lists and values are separated by commas The list of values and the list of field names correspond one by one If you insert some data in the table , Field name list is required   Example ：

INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)
VALUES('123',' Xiao Ping Huang ',1,'13956799999','1996-5-8');

5、DML sentence -- Insert multiple data records

** grammar ：**INSERT INTO New table （ List of field names ）VALUES( List of values 1),( List of values 2),……,( List of values n);  Example ：

 INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`)
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

Experience ： To avoid errors caused by changes in table structure , It is recommended to specify the specific field name when inserting data ！

6、DML sentence —— Insert query results into a new table

CREATE TABLE `phoneList`(
    SELECT `studentName`,`phone`
    FROM `student`);

notes ： If the new table already exists , Will be an error ！

7、DML sentence —— Data update 、 Delete

1） Update data records update Table name set Field 1= value 1, Field 2= value 2,..., Field n= value n [where Conditions ]; 2) Delete data records

DELETE FROM  Table name  [WHERE Conditions ];
 TRUNCATE TABLE  Table name ;
TRUNCATE Statement will reset auto increment column after deletion , Table structure and its fields 、 constraint 、 The index remains unchanged , Execution speed ratio DELETE Fast sentence 

8、DQL sentence

grammar ：

SELECT < List of field names > FROM < Table name or view > [WHERE < Query criteria >] [GROUP BY < The field name of the group >] [HAVING < Conditions >] [ORDER BY < Sorted field name > [ASC or DESC]] Example ：

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo;

9、LIMIT Clause

MySQL Use... In query statements LIMIT Clause limits the result set   grammar ： SELECT < List of field names > FROM < Table name or view > [WHERE < Query criteria >] [GROUP BY < The field name of the group >] [ORDER BY < Sort column names > [ASC or DESC]] [LIMIT [ Position offset , ] Row number ];

Example ：

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo
 LIMIT 4,4; #( From 5 Bar starts to show 4 strip )

notes ： Use LIMIT When clause , Pay attention to the 1 The location of the record is 0！

10、 Common functions —— Aggregate functions

**AVG()** Returns the average value of a field

COUNT()  Returns the number of rows in a field

**MAX()** Returns the maximum value of a field

MIN()  Returns the minimum value of a field

SUM()  Returns the sum of a field

11、 Common functions —— String function

CONCAT(str1,str1...strn) String connection SELECT CONCAT('My','S','QL'); return ：MySQL INSERT(str,pos,len,newstr) String substitution SELECT INSERT( ' This is a SQL Server database ',3,10,'MySQL'); return ： This is a MySQL database LOWER(str) Make the string lowercase SELECT LOWER('MySQL'); return ：mysql UPPER(str) Convert a string to uppercase SELECT UPPER('MySQL'); return ：MYSQL SUBSTRING(str,num,len) String interception SELECT SUBSTRING( 'JavaMySQLOracle',5,5); return ：MySQL

12、 Common functions —— Time date function

CURDATE() Get current date SELECT CURDATE(); return ：2016-08-08 CURTIME() Get the current time SELECT CURTIME(); return ：19:19:26 NOW() Get the current date and time SELECT NOW(); return ：2016-08-08 19:19:26 WEEK(date) Return date date For the week of the year SELECT WEEK(NOW()); YEAR(date) Return date date A year of SELECT YEAR(NOW()); HOUR(time) Return time time The hour value of SELECT HOUR(NOW()); DATEDIFF(date1,date2) Returns the date parameter date1 and date2 The days between SELECT DATEDIFF(NOW(),'2008-8-8'); return ：2881

13、 Common functions —— Mathematical functions

CEIL(x) Returns a number greater than or equal to x Minimum integer of SELECT CEIL(2.3) return ：3 FLOOR(x) Returns a number less than or equal to x Maximum integer for SELECT FLOOR(2.3) return ：2 RAND() return 0~1 Random number between SELECT RAND() return ：0.5525468583708134

14、 Subquery

1） A subquery is nested in SELECT、INSERT、UPDATE or DELETE A query in a statement or other subquery  2） Subquery in WHERE General usage in statements  ** grammar ：**SELECT … FROM surface 1 WHERE Field 1 Comparison operator ( Subquery ) ** notes ：** Combine subqueries with comparison operators , It must be ensured that the value returned by the sub query cannot be more than one   Example ：

SELECT `studentNo`,`studentName`,`sex`,`bornDate`,`address`
 FROM `student` 
 WHERE `bornDate` > (SELECT `bornDate` FROM `student` WHERE `studentName`=' Lisbon ');

15、 Replace table join with subquery

1） Implementation method 1 ： Use table connection

SELECT `studentName` FROM `student` stu 
 INNER JOIN `result` r ON stu.studentNO = r.studentNo     	
 INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo 	
WHERE `studentResult` = 60 AND `subjectName` = 'Logic Java';

2） Implementation method 2 ： Adopt sub query

SELECT `studentName` FROM `student` WHERE `studentNo` = (
SELECT `studentNo` FROM `result` 
INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo 
 WHERE `studentResult`=60 AND `subjectName`='Logic Java'
);

Subquery is more flexible 、 convenient , It is often used as a screening condition for addition, deletion, modification and query , Suitable for manipulating the data of a table Table join is more suitable for viewing data from multiple tables

16、IN Subquery

SELECT `studentName` FROM `student` 
WHERE `studentNo` IN(
    SELECT `studentNo` FROM `result` 
    WHERE `subjectNo` =  (
        SELECT `subjectNo` FROM `subject`
        WHERE `subjectName`='Logic Java'
    )AND `studentResult` = 60  
);

Commonly used IN Substitution is equal to （＝） Subquery of IN Subsequent subqueries can return multiple records

notes ： Not the original ！