"Preach Wisdom Podcast Zhengzhou Campus" database MySQL notes detailed

1th. Database 1.1 Database Overview
l What is a database
The database is the warehouse that stores the data, its essence is a file system, the data is stored in the specific format, the user can add, modify, delete and query the data in the database.
l What is a database management system
Database Management System,dbms: Refers to a large-scale software that operates and manages databases, which is used to establish, use and maintain databases, to manage and control the database uniformly, and to ensure the security and integrity of the database. The user accesses the data in the tables in the database through the database management system.
L Common Database management system
MYSQL: Open source free database, small database. It has been acquired by Oracle. The mysql6.x version also starts charging.
Oracle: A large database of charges, Oracle Company products. Oracle acquires Sun and acquires MySQL.
DB2:IBM Company's database products, fees. Often used in the banking system.
Sqlserver:microsoft a medium-sized database for company charges. Languages such as C #,. NET are often used.
SyBase: Has faded from the historical stage. Provides a very professional data modeling tool for PowerDesigner.
SQLite: A small embedded database, applied to the mobile phone side.
Java-related databases: Mysql,oracle.
The MySQL database is used here. There can be multiple databases in MySQL, and the database is where the data is actually stored.
The relationship between database and database management system

1.2 Database tables
The database stores data in a table as an organizational unit.
Tables are similar to our Java classes, and each field has a corresponding data type.
Then using our familiar Java program to compare with relational data, we will find the following correspondence.
Class----------Table
----------a field in a table in a property class
Object----------Record
1.3 Table Data
Depending on the type of data specified in the table field, we can fill in the data in one of the tables, and each piece of data in the table resembles an instance object of the class. The information in one row of a table is what we call a record.

L table records correspondence to Java class objects

2nd MySQL Database 2.1 mysql Installation
L Installation
Refer to the MySQL installation diagram. doc

After installation, MySQL provides us with data storage capabilities in the form of Windows services. To turn services on and off: Right-click My computer → manage → services → You can find the MySQL service turned on or stopped.

You can also start and stop the MySQL service in a DOS window by command (you must run the cmd Command window with admin)

2.2 Log in to MySQL database
MySQL is a database that requires account name password login, used after login, it provides a default root account, use the password set at the time of installation to log in.
Format 1:cmd> mysql–u user name –p password
Example: Mysql-uroot–proot

Format 2:cmd> mysql--host=ip address--user= user name--password= password
Example: MySQL--host=127.0.0.1--user=root--password=root

2.3 SQLyog (MySQL graphical development tool)
L Installation:
The SQLyog software provided is free of installation and can be used directly
L Use:
Enter the user name, password, click the Connect button, to access the MySQL database to operate

In the Query window, enter the SQL code, select the SQL code you want to execute, press the F8 key to run, or press the Execute button to run.

3rd Chapter SQL statements
The database does not know the Java language, but we also want to interact with the database, we need to use the database to understand the language SQL statement, it is the code of the database.
Structured Query Language (structured query Language), called SQL, is a database query and programming language for accessing data and querying, updating, and managing relational database systems.
SQL statements are required to create a database, create a data table, and add a strip of data to a data table.
3.1 SQL statements
L SQL Classification:
n Data definition language: For short, DDL (data definition Language), used to define database objects: Databases, tables, columns, and so on. Keywords: create,alter,drop, etc.
n Data manipulation Language: for short, DML (data Manipulation Language), which is used to update the records of tables in the database. Keywords: insert,delete,update, etc.
N Data Control Language: Language, the DCL, is used to define access and security levels for a database, and to create users.
N Data Query Language: abbreviated to DQL, data query Language, which is used to query the records of tables in the database. Keywords: select,from,where, etc.
3.2 SQL General syntax
L SQL statements can be written one or more lines, ending with a semicolon
l Use spaces and indents to enhance the readability of statements
The SQL statement for the MySQL database is case-insensitive and is recommended to use uppercase, for example: SELECT * from user.
L can also use/**/to complete annotations
The types of data that we often use in MySQL are as follows

The detailed data types are as follows (not recommended for detailed reading!) )
Classification
Type name
Description
Integer type
TinyInt
A very small integer.
smallint
A small integer
Mediumint
A medium-sized integer
Int (integer)
An integer of normal size
Decimal type
Float
Single-precision floating-point number
Double
Double-precision floating-point number
Decimal (M,D)
Compress strict fixed-point numbers
Date type
Year
YYYY 1901~2155
Time
hh:mm:ss-838:59:59~838:59:59
Date
YYYY-MM-DD 1000-01-01~9999-12-3
Datetime
Yyyy-mm-dd HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
Timestamp
Yyyy-mm-dd HH:MM:SS 1970~01~01 00:00:01 utc~2038-01-19 03:14:07UTC
Text, binary type
CHAR (M)
M is an integer between 0~255
VARCHAR (M)
M is an integer between 0~65535
Tinyblob
Allowable length 0~255 bytes
Blob
Allowable length 0~65535 bytes
Mediumblob
Allowable length 0~167772150 bytes
Longblob
Allowable length 0~4294967295 bytes
Tinytext
Allowable length 0~255 bytes
TEXT
Allowable length 0~65535 bytes
Mediumtext
Allowable length 0~167772150 bytes
Longtext
Allowable length 0~4294967295 bytes
VARBINARY (M)
A variable-length byte string that allows a length of 0~m bytes
BINARY (M)
A fixed-length byte string that allows a length of 0~m bytes
3.3 Database operations:
L Create a database
Format:

• Create database name;
• Create database name character set character set;
  For example:
  The encoding of the data in the #创建数据库 database takes the default encoding specified when the database is installed UTF8
  CREATE DATABASE day21_1;
  #创建数据库 and specify the encoding of the data in the database
  CREATE DATABASE day21_2 CHARACTER SET UTF8;
  
  L View Database
  To view all databases in the database MySQL server:
  show databases;
  To view information about the definition of a database:
  Show create database name;
  For example:
  Show CREATE Database day21_1;
  L Delete a database
  drop database name;
  For example:
  Drop database day21_2;
  L Other Database Operations commands
  To switch databases:
  Use database name;
  For example:
  Use day21_1;
  L View the database in use:
  Select Database ();
  3.4 Table Structure-related statements 3.4.1 creating tables
  L Format:
  CREATE TABLE Table name (
  Field Name type (length) constraint,
  Field Name type (length) constraint
  );
  For example:
  # # #创建分类表
  CREATE TABLE Sort (
  Sid INT, #分类ID
  Sname VARCHAR (#分类名称)
  );
  3.4.2 PRIMARY KEY constraints
  The primary key is the field that identifies the current record. It is characterized by non-null, unique. In development, the primary key does not have any meaning, but is used to identify the current record.
  Format:
  L 1. Create the primary key when creating the table, and add primary key after the field.
  CREATE TABLE TableName (
  ID int PRIMARY KEY,
  .......
  )

L 2. Create a primary key when creating a table, specifying the primary key at the end of the table creation
CREATE TABLE TableName (
ID int,
.......，
Primary KEY (ID)
)
L 3. Delete primary key: ALTER TABLE name drop PRIMARY key;
ALTER TABLE sort drop PRIMARY key;
L 4. Primary key autogrow: The general primary key is a self-growing field and does not need to be specified.
Implement add self-growth statement, primary key field plus auto_increment (MySQL only)
For example:
# # #创建分类表
CREATE TABLE Sort (
Sid INT PRIMARY KEY auto_increment, #分类ID
Sname VARCHAR (#分类名称)
);

Other constraints: Other constraints such as foreign keys, unique, non-empty, etc., will be in the employment class detailed introduction.
3.4.3 View Table
L View all tables in the database:
Format: show tables;
L View Table structure:
Format: DESC table name;
For example: desc sort;
3.4.4 Deleting a table
L Format: drop table name;
Example: drop table sort;
3.4.5 Modify Table Structure format:
L ALTER TABLE name add column name type (length) constraint;
Effect: Modifies the table to add columns.
For example:
#1, add a new field for the classification table to describe varchar (20)
ALTER TABLE Sort ADD sdesc VARCHAR (20);
L ALTER TABLE name modify column name type (length) constraint;
Effect: Modifies the type length and constraints of a table modified column.
For example:
#2, modify the Category name field for the classification table, type varchar (50) Add constraint NOT NULL
ALTER TABLE Sort MODIFY sname VARCHAR () not NULL;
L ALTER TABLE name change old column name new column name type (length) constraint;
Effect: Modifies the table to modify the column name.
For example:
#3, replace the category name field for the classification table with Snamesname varchar (30)
ALTER TABLE Sort Change sname snamename VARCHAR (30);
L ALTER TABLE name drop column name;
Effect: Modifies a table to delete a column.
For example:
#4, delete the Snamename column in the classification table
ALTER TABLE sort DROP snamename;
L Rename table name to new name;
Role: Modify table name
For example:
#5, name the category list sort
RENAME TABLE sort to category;
L ALTER TABLE name character set character set;
Role: Modifying the character set of a table
For example:
#6, modify the Coding Table of category table to GBK
ALTER TABLE category CHARACTER SET GBK;
3.4.6 Insert Table record:
L Syntax:
Insert into table (column name 1, column name 2, column name 3:) VALUES (value 1, value 2, value 3:); --Insert some columns into the table
Insert into table values (value 1, value 2, value 3 ...); --Insert all columns into the table
L Note:
The data you insert should be the same as the data type of the field
The size of the data should be within the length range of the column
The data locations listed in values must correspond to the arranged positions of the columns being joined.
In addition to numeric types, the values of the other field types must be enclosed in quotation marks.
If you want to insert a null value, you can not write a segment, or insert NULL.
For auto-growing columns, insert a null value directly when the operation is in action.
L For example:
INSERT into sort (sid,sname) VALUES (' s001 ', ' electrical ');
INSERT into sort (sid,sname) VALUES (' s002 ', ' costumes ');
INSERT into sort VALUES (' s003 ', ' cosmetics ');
INSERT into sort VALUES (' s004 ', ' books ');

3.4.7 Update table records:
The data that is used to modify the specified condition to modify the specified column of the record that satisfies the condition to the specified value
L Syntax:
Update table name SET field name = value, field name = value;
Update table name SET field name = value, field name = value where condition;
L Note:
The type of the U column name is the same as the modified value.
You can not exceed the maximum length when you modify it.
The U value if it is a string or the date needs to be added '.
For example:
#1, modify the value in the specified sname field to a daily necessities
UPDATE sort SET sname= ' daily necessities ';
#2, change the sname in the records of Sid s002 to daily necessities
UPDATE sort SET sname= ' commodity ' WHERE sid= ' s002 ';
UPDATE sort SET sname= ' commodity ' WHERE sid= ' s003 ';
3.4.8 Deleting records: Delete
L Syntax:
Delete from table name [where condition];
Or
TRUNCATE TABLE name;
L Interview Questions:
Delete all records in the table using the delete from table name; Or use the TRUNCATE table name;
Delete method: Delete One piece deletes, does not empty the Auto_increment record number.
Truncate directly delete the table, re-build the table, Auto_increment will be set to zero, new start.
For example:
DELETE from sort WHERE sname= ' daily necessities ';
#表数据清空
DELETE from sort;
3.5 DOS Operation data garbled solution
We will error when we operate Chinese on DOS command line
Insert into User (Username,password) VALUES (' Zhang San ', ' 123 ');
ERROR 1366 (HY000): Incorrect string value: ' \xd5\xc5\xc8\xfd ' for column ' username ' at row 1
Cause: Because the problem with MySQL's client code is UTF8, and the system's CMD window code is GBK
Solution (Temporary solution): Modify the MySQL client code.
Show variables like ' character% '; View all MySQL codes

The encoding settings related to the client in the diagram:
Client connetion result and clients related
Database server system and server-side related
L Modify the client code to GBK.
Set CHARACTER_SET_RESULTS=GBK; /set names GBK;
The above operation, only for the current window has an effect, if the server is disabled. If you want to permanently modify it, use the following method:
l have my.ini file in MySQL installation directory
DEFAULT-CHARACTER-SET=GBK Client encoding Settings
Character-set-server=utf8 server-side encoding settings
Note: Modify the finish profile to restart the service
4th Chapter SQL query statement
Query statements, which are used most frequently in development, use the "Zhangwu" billing form here.
L Create a statement of accounts:
CREATE TABLE Zhangwu (
ID INT PRIMARY KEY auto_increment,--Billing ID
Name VARCHAR (200),--Accounting name
Money DOUBLE,--Amount
);
L Insert Table Record:
INSERT into Zhangwu (Id,name,money) VALUES (1, ' eating expenses ', 247);
INSERT into Zhangwu (Id,name,money) VALUES (2, ' Payroll income ', 12345);
INSERT into Zhangwu (Id,name,money) VALUES (3, ' costume expense ', 1000);
INSERT into Zhangwu (Id,name,money) VALUES (4, ' eating expenses ', 325);
INSERT into Zhangwu (Id,name,money) VALUES (5, ' stock income ', 8000);
INSERT into Zhangwu (Id,name,money) VALUES (6, Play Mahjong expense, 8000);
INSERT into Zhangwu (Id,name,money) VALUES (7,null,5000);
4.1 Syntax for query:
L Query specified field information
Select field 1, Field 2,... from table name;
For example:
Select Id,name from Zhangwu;
L query all fields in a table
Selectfrom table name;
For example:
SelectFrom Zhangwu;
Note: Use the"It can be used in practice and learning, and is not recommended in practical development. Reason, the field information to query is not clear, if the number of fields, it will cause the query speed is very slow.
l distinct for removing duplicate records
Select distinct field from table name;
For example:
Select distinct money from Zhangwu;
L alias query, using the AS keyword, as can be omitted.
Aliases can be used to set aliases to fields in a table. When query statements are complex, using aliases can be a great convenience.
Table alias Format:
SelectFrom table name as alias;
Or
Selectalias from table name;
Column alias format:
Select field name as alias from table name;
Or
Select field name alias from table name;
For example
Table Aliases:
SelectFrom Zhangwu as ZW;
Column Aliases:
Select Money as M from Zhangwu;
Or
Select Money m from Zhangwu;
We can operate on the columns directly in the operation of the SQL statement.
For example, the amount of all accounts + $10000 is displayed.
Select pname,price+10000 from Product;
4.2 Item Query
The WHERE statement table condition is filtered. Satisfies the condition operation, does not satisfy the non-operation, is used for the data query and the modification.
Format: Select field from table name where condition;
The type of while condition is as follows:
Comparison operators

< <= >= = <>
Greater than, less than, greater than (less than) equals, not equal to
Between ... And ...
Values displayed in an interval (with a head with a tail)
In (set)
Values displayed in the In list, example: in (100,200)
Like wildcard characters
Fuzzy query with two wildcard characters in a like statement:
% is used to match multiple characters;name like ' a% ';
Used to match a character. Example firstname like ' a’;
Is NULL
Determines whether the empty
is null; Judged to be empty
is not null; The judgment is not empty
logical operators
and
Multiple conditions set up at the same time
Or
A number of conditions are set
Not
Not established, example: where not (salary>100);
For example:
Check all meal expense records
SELECTFrom zhangwu WHERE name = ' eating expenses ';
Find out more than 1000 of the amount of information
SELECTFrom Zhangwu WHERE Money >1000;
Check out the accounting information for the amount between 2000-5000
SELECTFrom Zhangwu WHERE money >=2000 and Money <=5000;
Or
SELECTFrom Zhangwu WHERE money between and 5000;
Check that the amount is 1000 or 5000 or 3500 of the product information
SELECTFrom Zhangwu WHERE money =1000 or money =5000 or money = 3500;
Or
SELECTFrom Zhangwu WHERE money in (1000,5000,3500);
Query the transfers service name contains accounting information for "expenses".
SELECTFrom Zhangwu WHERE name "% expenditure%";
Query transfers business name is not five words of accounting information
SELECTFrom GJPLedger WHERE ldesc like "____"; --Five underscores _
Query transfers service name is not NULL account information
SELECTFrom Zhangwu WHERE name was not NULL;
SELECTFrom Zhangwu WHERE not (name is NULL);

"Preach Wisdom Podcast Zhengzhou Campus" database MySQL notes detailed