Introduction to MySQL
1. Basic concepts of database
Database: DataBase is abbreviated as DB
Role: Warehouse for permanent storage and management of data
2. Common databases
A.MySQL Simple Free Database
B.Oracle Large Charge Database
C. SQL Server Microsoft C and. Net
D.SQLite Embedded Database Android System
3. Installation and Unloading of MySQL Software
A. Installation (References according to) Note: Path does not have Chinese, remember your password, recommend password 123
B. Unload (need to delete data under hidden folders) C: ProgramData MySQL
4. Operation of MySQL Start Service
A. Manual startup
a. Computer - > Management - > Services and Applications - > Services
b. Task Manager - > Services
B. Common CMD Small Black Window
Instruction: servers. MS C "Microsoft: ms, control: c"
C. Administrator CMD Small Black Window
a. Open the service net start mysql
b. Close the service net stop MySQL
5. Login and exit operations of MySQL
A. login
a. Log in to your own
Mysql-u root-p password
b. Log in to someone else (check remote access when someone else installs it) (native ip address written here)
Mode 1: mysql-h 127.0.0.1-u root-p password
Mode 2: mysql --host=127.0.0.1 --user=root --password = = password
B. exit
a. exit
b. quit
6. Important files of MySQL
A. Installation Files
my.ini can modify the encoding problem (for example, it can solve the problem of Chinese scrambling)
B. Data files
Folder - ----> Database
excel file - --> table
A lot of data - > table records
The general meaning of SQL
1. The Meaning of SQL
SQL is a language for operating relational databases. (Rules for operating a database)
2. General Syntax of SQL
A.SQL statement ends with a semicolon (semicolon: English semicolon)
B.SQL statements are case-insensitive.
C. notes
a. Single-line comments
Mode 1: Single-line comment, note that there are spaces in the middle
Way 2: # This is a comment, MySQL-specific comment
b. Multi-line comments
/* This is a multi-line comment, just like Java.*/
3. Classification of SQL Statements
A. DDL operations on databases and tables
B. DML Add, Delete, Change Table Records in Database
C. Table records in DQL query database
D. Operation of DCL Client Authorized Database (Understanding)
MySQL's DDL (Operating databases and tables, adding, deleting, checking, using)
I.Operation of database:[increase,Delete,change,check,Use]
1,Operation of creating database
A.Basic creation operation
CREATE DATABASE Database name;
B.Integrated Creation Operations(Judgment of existence,Specified Character Set"There will be no Chinese scrambling problem")
CREATE DATABASE IF NOT EXISTS Database name CHARACTER SET Specific coding;
2,Operation of querying database
A.Display all databases
SHOW DATABASES;
B.Show the creation statement of the database(Implicit role:View the Character Set of the Database)
SHOW CREATE DATABASE Database name;
3,Modification of database operations
A.Modifying the Character Set of the Database
ALTER DATABASE Database name CHARACTER SET Specific coding;
4,Operation of deleting database
A.Basic deletion operations
DROP DATABASE Database name;
B.Judgment of existence,Existence is deletion operation
DROP DATABASE IF EXISTS Database name;
5,Operation using database
A.Using databases
USE Database name;
B.Query the database in use
SELECT DATABASE();
II.Table operation,Delete,change,Check]
1,Operation of Query Table
A.Query all table information in the database
SHOW TABLES;
B.Query the structure of a specific table(Information for each field)
DESC Table name;
2,Operation of creating tables
A.Basic Creation Format
CREATE TABLE Table name (Column Name 1 Type 1,Column Name 2 Type 2,List 3 Type 3);
B.Operation of replicating tables
CREATE TABLE New table name LIKE Original table;
C.Common data types
a.Integer type int
b.Decimal type double(4,1) 4 Represents four numbers,1 Represents the first bit after the exact decimal point.
c.String type varchar(24) 24 Represents a maximum capacity of 24 characters
d.Date type date Manual insertion of data into the database is required
e.timestamp timestamp Automatically add the current system time
3,Delete tables
A.Basic deletion operations
DROP TABLE Table name;
B.Judgment of existence,Existence is deletion operation
DROP TABLE IF EXISTS Table name;
4,Operation of modifying tables
A.Modify the table name
ALTER TABLE Name of original table RENAME TO New table name;
B.Modify the Character Set of the Table
ALTER TABLE Table name CHARACTER SET Character Set Coding;
C.alter table rename column
ALTER TABLE Table name CHANGE Old Column Name New Column Name New Column Data Type;
ALTER TABLE Table name MODIFY The column name to be modified is a new data type;
D.Add a row
ALTER TABLE Table name ADD Column name data type;
E.Delete one column
ALTER TABLE Table name DROP Column name;
DML of MySQL (operation table record, add, delete and change)
1. Additional Statements for Table Records
A. Adding data for specified columns
INSERT INTO table name (column name 1, column name 2, column name 3) VALUES (value 1, value 2, value 3);
B. Add data for all columns
INSERT INTO table name VALUES (value 1, value 2, value 3, value 4, value 5);
C. Notes
In addition to the number type (integer and decimal), all other things are caused by quotation marks (single and double quotation marks can be used).
2. Delete statement of table record
A. Delete certain specified records
DELETE FROM table name [WHERE condition];
B. Delete the entire table
DelETE FROM table name; -- Deleting the entire table (row by row) is inefficient
b. TRUNCATE TABLE Table Name; -- Delete the entire table (delete the table first, then build the table) with high efficiency [recommended use]
3. Modified statement of table record
A. Operation of Common Modified Statements (with Conditions)
UPDATE table name SET column name 1 = value 1, column name 2 = value 2 [WHERE condition];
B. Unusual modification statement operations (modifying all cases)
UPDATE table name SET column name 1 = value 1, column name 2 = value 2;
DQL of MySQL (operation table record, check)
1,Basic query
A.Query operations for multiple fields
a.Universal writing
SELECT * FROM Table name;
b.Designated Column Writing
SELECT Column name 1,Column name 2,Column name 3 FROM Table name;
B.Remove duplicate query operations
SELECT DISTINCT Column names FROM Table name;
C.Operations of Four Operational Computing Columns
SELECT IFNULL (Column name 1,0) + Column name 2 FROM Table name;
D.Operation of aliasing query results
SELECT Column name 1 AS Alias 1,List 2 Alias 2 FROM Table name;
2,Conditional query WHERE Later Format Writing
A.and &&
a.Writing 1: SELECT * FROM Table name WHERE age>20 && age<30;
b.Writing 2: SELECT * FROM Table name WHERE age>20 AND age<30;
c.Writing 3: SELECT * FROM Table name WHERE age BETWEEN 20 AND 30;
B.or ||
a.Writing 1: SELECT * FROM Table name WHERE age=20 || age=30;
b.Writing 2: SELECT * FROM Table name WHERE age=20 OR age=30;
c.Writing 3: SELECT * FROM Table name WHERE age IN(20,30); -- Age 20 or 30
C.wrong !
a.Writing 1: SELECT * FROM Table name WHERE address IS NULL; -- The place of residence is Null
b.Writing 2: SELECT * FROM Table name WHERE address IS NOT NULL; -- The place of residence is not Null
3,Fuzzy query WHERE LIKE
A.Two placeholders
a. '_' Underline,Represents a character
b. '%' Percent sign,Represents 0 or more characters
B.case
a. Query surname'plum'
SELECT * FROM hero WHERE name LIKE 'plum%';
b. Query surname'Zhao'There are only two words.
SELECT * FROM hero WHERE name LIKE 'Zhao_';
c. The second word of the query is'writing'Of
SELECT * FROM hero WHERE name LIKE '_writing%';
d. Queries only need to have'Grandchildren'Word
SELECT * FROM hero WHERE name LIKE '%Grandchildren%';