1. The database

  1. concept
    • DataBase(hereinafter referred to asDB)
    • A warehouse for storing and managing data
  2. The characteristics of
    • A file system that persistently stores data
    • Easy to store and manage data
    • The database is operated in a uniform way:SQL
  3. Common database software
    • MySQL
    • Oracle
    • SQL Server
    • DB2

2. MySQL

  1. The installation
    • slightly
  2. uninstall
    1. Find it in the MySQL installation directorymy.inifile
    2. Open this file and copy theDatadir = "C: / ProgramData/MySQL/MySQL Server 5.5 / Data/"(Prepare for step 1)
    3. Uninstall MySQL in your application
    4. Delete step 2C:/ProgramDataIn the directoryMySQLThe folder can be completely uninstalled
  3. configuration
    • MySQL service start

      1. CMD enter services. MSC, open the services window, then start/stop/change the startup mode (automatic/manual), as shown below

      2. Open CMD as an administrator:

        1. The inputnet start mysql: Starts the mysql service
        2. The inputnet stop mysql: Stops the mysql service
    • MySQL login
      • CMD:
        1. Mysql -uroot -p Password
        2. Mysql -h* IP * -uroot -p password
        3. Mysql --host=* IP * --user=root --password= connect target password
    • MySQL exit
      • CMD:
        1. exit
        2. quit
    • MySQL directory structure
      1. Configuration file:my.ini
      2. MySQL installation directory:basedir="..."(in the my.ini file)
      3. MySQL > alter databaseDatadir = "C: / ProgramData/MySQL/MySQL Server 5.5 / Data/"
      4. Database: a folder under the data folder in the MySQL installation directory
      5. Table: files under the database folder
      6. Data: Data in a table

3. SQL

  1. concept
    • Structured Query Language: Structured Query Language
    • Defines rules that operate on all relational databases, but each specific database operates differently
  2. SQL General syntax
    • Each SQL statement can be written on one or more lines, ending with a semicolon
    • You can use Spaces or indents to make your code more readable
    • The SQl statements of the MySQL database are case insensitive, but you are advised to use uppercase keywords
    • annotation
      1. One-line comment:# Comment content(MySQL specific) or-- Comment content
      2. Multi-line comments:/* Comment the content */
  3. Classification of SQL
    1. DDL(Data Definition Language) : Data Definition Language
      • Define database objects: databases, tables, columns, etc
      • Key words:CREATE.DROP.ALTEREtc.
    2. DML(Data Manipulation Language) : Data Manipulation Language
      • Add, delete, and modify the data of the tables in the database
      • Key words:INSERT.DELETE.UPDATEEtc.
    3. DQL(Data Query Language) : Data Query Language
      • Query data (records) from tables in the database
      • Key words:SELECT.WHEREEtc.
    4. DCL(Data Control Language) : Data Control Language
      • Define database access permissions, security levels, and create users
      • Key words:GRANT.REVOKEEtc.

1. DDL: Operates databases and tables

  • CRUD
    • C: Creat
    • R: Retrieve (query)
    • U: Update (modify)
    • D: Delete
  1. Operating database: the CRUD
    • C
      1. Create database:CREATE database Specifies the database name.
      2. Check whether the database exists. If the database does not exist, create a database.CREATE database if not exists Database name;
      3. Create database and specify character set:CREATE database Database name character set;
    • R
      1. Query all database names:SHOW databases;
      2. SQL > select * from character set; SQL > select * from character set;SHOW CREATE database Database name;
    • U
      1. Alter database character setALTER database ALTER database name character set;
    • D
      1. Delete database:DROP database Specifies the database name.
      2. Check whether the database exists. If the database exists, delete it.CREATE database if exists Database name;
    • Using a database
      1. Using a database:USE Database name;
      2. Query current database name:SELECT database();
  2. The operating table: the CRUD
    • C
      1. Grammar: 
        CREATE tableTable name (column name1The data type1And the column name2The data type2. Column name n Data type n);Copy the code

        Note: The last column does not need a comma

      2. The data type
        • int: Integer type
        • Double (number of digits, reserved after the decimal point): decimal type,
        • date: Indicates the date, which contains only yyyy- MM-DD
        • datetime: Indicates the date, including year month day hour minute second YYYY-MM-DD HH: MM :ss
        • timestamp: Indicates the timestamp type, including year, month, day, hour, minute, second, YYYY-MM-DD HH: MM :ss
          • If no value is assigned to this field or the value is null, the current system time is automatically assigned by default
        • Varchar (maximum number of characters): String type
      3. The replication table:CREATE table name like the name of the table to be copied;
    • R
      1. Query the names of all tables in a database:SHOW tables;
      2. Query the structure of a table:Desc table name;
    • U
      1. Alter table name:ALTER table name rename to new table name;
      2. Alter table character set;ALTER table name character set;
      3. Add a column:ALTER table name add column name data type;
      4. Modify column names and data types
        • Modify only the data type:ALTER table table name modify column name New data type;
        • Modify column names and data types:ALTER table table_name change column name new column name new data type;
      5. Delete the columns:ALTER table table_name DROP table_name;
    • D
      1. Delete table:DROP table table name;
      2. Check whether the table exists, then delete:DROP table if exists Specifies the name of the table.

2. DML: Add, delete and modify the data in the table

  1. Add data:Insert into table name (1, 2,... N) values(1, 2... And the value of n);
    • Column names and values need to correspond one to one
    • If no column name is defined after the table name, values are added to all columns by default:Insert into values(1, 2,... And the value of n);
    • Except for numeric columns, all types need to be enclosed in quotes (single or double quotes)
  2. Delete the data:Delete from table name where condition;
    • If no WHERE condition is added, all data in the table is deleted
    • Delete all data from the table
      1. Delete from table name;This operation is not recommended. If n records exist, n records are deleted, which is inefficient
      2. Truncate TABLE name;You are advised to delete the table and then create an empty table
  3. Modify the data:Update table_name set table_name 1= 1, table_name 2= 2,... , column name n= value n where condition;
    • Without the WHERE condition, the data in each row of the table is modified

3. DQL: queries records in the table

  1. grammar 
    selectField listfromThe table listwhereCondition listgroup byThe grouping fieldhavingList of conditions after groupingorder bySorting based onlimitPaging limitedCopy the code
  2. Based on the query
    1. Multiple field queries
      • Query multiple fields:Select 1, 2... From the name of the table;
      • Query all fields:Select * from table_name;
    2. The query result is deduplicated
      • Add before the field that needs to be reweighteddistinct
    3. Computed columns
      • You can use four operations to compute the values of some columns of numeric data type
      • You can useIfnull (expression 1, expression 2)Replace null data in expression 1 with expression 2
    4. names
      • as, can also be omitted
  3. Conditions of the query
    1. The WHERE clause is followed by a condition
    2. The operator
      • >, >=, <, <=, =, <> (! =)
      • between... and...(including left and right boundaries)
      • In (set)
      • like: fuzzy query
        • A placeholder
          • _: A single arbitrary character
          • %: Multiple arbitrary characters
      • is null
      • And (&)
      • Or (| |)
      • Not (!)
  4. Sorting query:Order by 1, order by 2, order by 2...
    • The sorting way
      • asc: Ascending (default)
      • desc: descending
    • Only if sorting by 1 is the same, will we judge sorting by 2
  5. Aggregation function: Vertical calculation of a column of data as a whole
    1. Count (count field): Count the number
      • Fields that are not empty are generally selected for counting, such as primary keys
      • Count (*) : Counts as long as at least one field in the row is not empty
    2. max: Calculate the maximum value
    3. min: Calculates the minimum value
    4. sumCalculation and:
    5. avg: Calculate the average value
    • Aggregate functions are evaluated without consideration of NULL
      • The solution
        1. Select fields whose values are not null for calculation
        2. useIfnull (expression 1, expression 2)Replace null data in expression 1 with expression 2
  6. Grouping query:Group by;
    1. It is meaningless to query a single field after grouping. You are advised to query a group field or aggregate function after grouping
    2. Where having
      1. where: Conditions are specified before grouping. If conditions are not met, no group is performed
      2. havingIf the conditions are not met, the group will not be queried
      3. Aggregate functions cannot be used after WHERE and can be used after HAVING because WHERE is not yet grouped
      • Sample code:SELECT sex , AVG(math),COUNT(id) count FROM student WHERE math > 70 GROUP BY sex HAVING count > 2;
  7. Paging query:Limit Indicates the number of queries per page
    1. Start index = (current page number -1) * number of pages to display per page
    2. Limit is a clause unique to MySQL
  8. Multi-table query
    1. Inner join query
      1. Implicit inline join: Use the WHERE condition to eliminate unwanted data
        • Select field list FROM table name list WHERE Condition list
      2. Explicit inline connection: Use the ON condition to eliminate unwanted data
        • ‘select * from table 1 [inner] join table 2 on condition list
      • Write the list of names first, then write the list of conditions, and finally write the list of fields
    2. External join query
      1. Left outer connection:Select * from 表名1 left [outer] join 表名2 on 表名
        • The query data is the intersection of all the data in the left table and the part that meets the criteria
      2. Right outer connection:Select * from 表名1 right [outer] join 表名2 on 表名
        • The data to be queried is all the data in the right table and the intersection part that meets the conditions
    3. The subquery
      1. Concept: nested queries within a query are called subqueries
      2. classification
        1. The result of a subquery is a single row, single column
          • Subqueries are conditional using the operators (>,>=,<,<=,=,! =) judgment
        2. The subquery result is multiple rows and one column
          • Subqueries are judged by the in operator as conditions
        3. Subquery results are multiple rows and columns
          • The subquery participates in the query as a virtual table

4. DCL: Manage users and rights

  1. Manage users
    1. Query users: 
      USE mysql;
SELECT * FROM USER;
Copy the code
    2. Create a user:CREATE USER 'username '@' username' IDENTIFIED BY 'password ';
    3. Delete a user:DROP USER 'username '@' username ';
    4. Changing a user password:
      1. UPDATE USER SET PASSWORD = PASSWORD(' new PASSWORD ') WHERE USER = 'username ';
      2. SET PASSWORD FOR 'username '@' username' = PASSWORD(' new PASSWORD ');
  2. Rights management
    1. Query permission:SHOW GRANTS on 'userid '@' host name ';
    2. Grant permissions:Grant permission list on database name. Alter table name to 'username '@' hostname ';
      • Grant all privileges on any table in any database:GRANT ALL ON *.* TO 'username '@' hostname ';
    3. Revoking permission:Revoke Permission list on database name. The name of the table fromThe user name.The host name;