MySQL preliminary understanding, simple single table additions and deletions to change

What is a database?
A repository of data, essentially a file system that encapsulates algorithms and files
Storage mode of previous data
Phase 1: Collection array variables
Cons: Data is stored in memory and cannot be persisted for data storage

Phase 2:io stream combines files. txt. xml ....
Advantage: Data is stored on disk and data can be persisted for storage
Cons: When data is manipulated, the algorithm must be implemented on its own, but a considerable part of the algorithm implements repetition

Phase 3: Database
Pros: Use specialized programs to encapsulate the algorithm part
Database vs. IO comparison: algorithm + File Store data
Database structure:
Algorithm section:. exe program under the Lib folder
File Storage section: Store the Data folder as files and folders
Table structure:
Database----JavaBean Package
Table-----A Class
Table row-----Each JavaBean object (specific class instance)
Table data----each field (property)

Specific implementation of the database
MySQL----Open Source free small database 6.x and start charging
Large database for Oracle---charges
SQLite----The database used by the mobile side, Android uses
SQL Server----Medium Database C #. NET Common

MySQL (Install)

Start, shut down services
net start MySQL
net stop mysql
login mysql
way 1:mysql-uroot-proot
Way 2:mysql--h Ost=ip--user=root--password=root (for remote login)

Database operations: Additions and deletions Change
Database operation dimension has 3 kinds (database,table structure, table data) Each has a more specific way of doing it

A:database and additions and deletions change
Add: Create database name;
Create database name character set character set;
Delete: drop database name;
Change: Rename operation is not necessary, easy to cause data loss
Check: show databases;
Show create database name; (Query to create the database, encode the information)
Select Database (); View the database currently in use
B: Table structure (provided that you enter a database)
Use library name; Switching to a database
Add: Create TABLE table name (
Int (can not specify, one) varchar (must specify length) Double date (unspecified length) timestamp
Field Name type (length) [constraint],
Field Name type (length) [constraint]
)//Create a new file
Delete: drop table name; deleting files
Check: show tables; List all tables under the current library
DESC table name; View the description of the current table, display fields and field-related information
Show create table table name; View the build statement that contains some table information, such as the encoding set
Expand:
Select Database ();
Change: (Learn)
1: Table name
Rename table name to the new name;
2: Field increment
ALTER TABLE name add new field name data type (length) [];
3: Field Delete
ALTER TABLE name drop field name;
4: Change the field to 1-----change data type only
ALTER TABLE name modify field name new type;
Change the field name and data type
ALTER TABLE table name change field name new name new type;

C: Table Data
1: Inserting data into the table
Insert Syntax:
Insert into Table name values (field value 1, field value 2,...); field values must correspond to fields declared in the table
Insert into Table name values (field value 1, field value 2,...), (field value 1, field value 2,...),...; Insert more than one record at a time
Insert into table name (specified field) VALUES (field corresponding value); Inserts a value for the field specified in the table, not specified without inserting

Attention:
1: Inserted data must be strictly consistent with the data type of the field declaration
2: Inserted data length must <= the length of the field declaration
3: If a column is specified, there is no specified column, the default value is NULL
You can also insert a null value directly in a column
4: Data other than the numeric type must be caused by "" or ", the recommended use of '

2: Table Data deletion
Delete from table name where condition; can use = to determine the condition
Attention:
1.delete from table name; Clear table Record
2.truncate table name; Clear table Record
Difference: (ID since growth)
Delete, truncate delete the table related files, and then create a new

3: Table Data modification:
Update table name Set field = new value where condition;

4: Table data Check!!!
Syntax 1:select * from table name;
Syntax 2:select * FROM table name where condition;
Comparison operators: = > < >= <=! = <>
Between and is similar to >= and <=
In (value 1, value 2) is similar to = or = (in high efficiency)
Like//Fuzzy query
The following formula:%----0 or more characters
_----An underscore corresponds to one character

Null (special value)
Determine if NULL---is NULL
is isn't null similar to not field name is null (the former is high efficiency)
Syntax 3:
SELECT * from table name;
Select field 1, Field 2,... from table name; Specify field
Select field 1 as xxx from table name; alias
SELECT DISTINCT xxx from table name; Go heavy

Garbled:
Set names GBK; (temporary)
Set CHARACTER_SET_CLIENT=GBK;
Set CHARACTER_SET_CONNECTION=GBK;
Set CHARACTER_SET_RESULTS=GBK;
or change the My.ini configuration file

DDL: Data Definition language
DML: Data Manipulation language
DCL: Data Control Language
DQL: Data Query Language

MySQL preliminary understanding, simple single table additions and deletions to change