My MySQL Use notes

My MySQL Use notes

Recent use of MySQL under Windows, a simple record of the development of some problems encountered

1. The difference between Char,varchar and text

char (M)	varchar (M)	text
Fixed length	Variable length	Variable length
M bytes, 0 <=m<= 255	l+1 bytes, where l<=m and 0 <=m<= 65535	l+2 bytes, where l< 216
Takes 1 bytes for English (ASCII) characters and 2 bytes for a Chinese character	Takes 2 bytes for each English (ASCII) character and only two bytes for a Chinese character	Store case-insensitive character data, each accounting for 2 bytes

2.MySQL Database Backup

D:\backup.sql

Then follow the prompts to enter the password.

Note: There are two ways to use mysqldump happily
1. Go to the Bin directory under the MySQL installation path and locate the mysqldump executable, cmd command-line window execution
2.[recommended] Add the MySQL bin directory to the system variable path, which can be used anywhere under the command

3. Procedures for Database backup

1. If the table exists, the Drop
2. Creating the table, create
3. Lock-write protection for this table, lock
4. Inserting raw data, insert
5. Done

4.MySQL Execute SQL Script

• No sign-in status

>-h-u-p< D:\backup.sql

• Login status (two types)

mysql>source D:\backup.sql

mysql>\. D:\backup.sql

5. A single quotation mark in the text causes data insertion to fail

MySQL uses two consecutive single quotes "to identify single quotes", or use the transfer character backslash \
So before inserting the data, you should first escape the special characters in the data.

6.MySQL Enter password and flash back, unable to start MySQL service

1. Look at the Computer_name.err log file under MySQL Data directory, which is obviously because Port 3306 is occupied. Error Letter: The following

   [ERROR] Do you already has another MYSQLD server running on port:3306?
   [ERROR] Aborting

2. Locate the process that occupies the port Pid,kill off and start the MySQL service again

>netstat -aon|findstr "3306"3306

the difference between delete,truncate and drop in 7.MySQL

• Delete
  
  • The corresponding data is deleted after the delete operation, but the data can be recovered when the roolback operation is performed
  • This is a quick modification to the data, because the index is not merged when it is deleted
  • Limit,where and order by can be used with the delete
• Truncate
  
  • Truncate delete the table structure and then create a new one, which is much faster than a row of deleted data.
  • Truncate not rollback operation after execution
  • Truncate will delete all data from the table
  • Truncate does not return the number of rows to delete data
• Drop
  
  • The difference between drop and delete is that delete deletes the data in the table but the structure of the table is still there, and drop deletes the table's data and structure.

8. Basic database Operation Syntax

#创建数据库 Create database database_name;#使用指定数据库use database_name; #显示所有表 show tables;#创建表 Create table table_name (ID int unsigend  not null primary key , name ...);#选择 Select *  from table_name where id = ' 1 '#插入insert into table_name (ID,......) Values (' 2 ',......) #设置自增字段的初始值alter table table_name auto_increment =1;#在列id之后插入新列 alter TABLE table_name add column column_name int  after ID;#更新一个字段的多行数据UpdateTableNameSetField= CaseOther_field when 0  Then ' value '         when 1  Then ' value '         when 2  Then ' value '    End whereOther_fieldinch(0,1,2) #修改字段的属性Alter TableTABLE_NAME Modify IDintAuto_increment;#删除表 drop table table_name;

My MySQL Use notes