Beginner MySQL Basics Note--the first day

I first into the blog park, the initial blog, in the next time will be a little bit to improve the level of their blog, as well as the layout of the blog.

Today, I learned the basic knowledge of MySQL database, I believe that there will be a lot of information about MySQL online, so I do not copy and paste here, here to leave some of my own in the beginning of MySQL some notes, for the needs of students to reference. The following is absolutely no copy, just a few of the bloggers own summary.

field Type

• Number: Int,decimal (decimal)

Eg:decimal (5,2): total of 5 digits, fractional part with 2 bits

• Character: char (the stored character is fixed), varchar (the stored character is variable), text (large text)

Eg:char (8) 8 characters, less fill space, varchar (8) Automatic intercept

• Date: Datatime
• Boolean: Bit

Eg:bit (8) means that 8 bits are saved, and the default one is not written.

Constraints　　　

• Primary key: Primary key
• Non-empty: NOT NULL
• Unique: Unique
• Defaults: Default
• FOREIGN key: FOREIGN key

Terminal　　 (end with a semicolon)

• Terminal Connection Server Mysql-uroot-p
• Launched Mysql:exit,quit,ctrl+c
• View version: Select version ();
• Show Current time: Select Now ();
• Link remote server: mysql-hip address-uroot-p

(If you modify the port, write in the back, if not changed, is the original 3306 no tube)

-H	Write the host address to connect to later
-U	Write the host name to connect to later
-P	Write password after carriage return

Database Operations

• Creating database: Create database name Charset=utf8;
• Delete the database: drop database name;
• Switch database: use database name;
• View the currently selected databases: Select database ();
• Show existing database: show databases;
• See which tables are in the database: show tables;

table operations in the database

• CREATE TABLE: Creat table name (column and type) auto_increment indicates autogrow

Eg:creat Table Students (
ID int auto_increment PRIMARY key,
Name varchar (TEN) NOT NULL
）；

　　The default in the above is to remove the parentheses, the space instead

• View table structure: DESC table name; (describe)
• Modify table: ALTER TABLE table name add|change|drop column name type;

Eg:alter table students add birthday datatime;
* Note: The column name cannot be modified when the column is modified, only the type can be modified

• Delete tables: drop table table name
• Change table name: Rename table name to new table name
• View common statements for a table: Show create table ' table name ';

Data manipulation

• Query: SELECT * from table name;
• Add Data:

Add all columns: INSERT into table name values (... ）
Eg:insert into students values (0, ' Caocao ', 1, ' 1999-2-3 ', 0); * Note: 0 of which does not necessarily produce 0, but is internally generated automatically, and the 0 written here is to occupy the position.
Default insert: INSERT into table name (column 1, ... Values (value 1, ...). ）
Insert more than one data: INSERT into table name values (...) ）（... ）... ；
OR: INSERT into table name (column 1, ...). ) VALUES (... ）（... ）... ；

• Modified: Update table name set column 1= value 1,...where condition

* Note: Where can be an ID sequence, not write will cause all rows to be modified

• Delete: Delete from table name where condition (physical deletion, once deleted is unrecoverable)

* Comparison: logical deletion, in fact, is equivalent to the modification and then filter.
Eg:delete from students where id=3; (physical delete)
Updata students set isdelete=1 where id=3;
SELECT * from students where isdelete=0; (tombstone)

Backup and Recovery

• Backup: First go to the MySQL library directory and run the backup command.

Mysqldump-uroot-p database name > Destination path and file name (. sql file)

Enter the MySQL password as prompted.

• Recovery:

Mysqldump-uroot-p New database name < backed-up file (. sql file)

Beginner MySQL Basics Note--the first day