MySQL First article

CharSet Modify CharSet engine character set modify the character set engines # # Operations folder (library) code as follows: SHOW CREATE database db_name; add CREATE Database DB1 CharSet UTF8; check show databases;show CREATE database DB1; change ALTER DATABASE DB1 charset GBK; Delete drop database db1; #2 action File (table) view table Code: SHOW CREATE table tbl_name; Switch to folder: Use DB1 Add (equivalent to create) creation table T1 (id int,name char) engine=innodb;create table T 2 (ID int,name char) engine (engine) =innodb default (Default) charset (character set) UTF8; specifies what type of id,name This table is, the specified engine, and the character set check show tables; Show CREATE table T1;    View basic information for a table desc t1; #查看表结构改 (equivalent to add) ALTER TABLE T1 add age int; Add the Age field for the reshape ALTER TABLE T1 modify name char (12); Delete the drop table T1; #3 the line of contents (record) of the action file to view the field code: the code is as follows: Show full COLUMNS FR OM tbl_name inserting data insert into DB1.T1 values (1, ' Egon1 '), (2, ' Egon2 '), (3, ' Egon3 '), insert into DB1.T1 (name) VALUES (' Egon1 ') , (' Egon2 '), (' Egon3 '); check select * from T1;select name to T1;select name,id from T1; change (update) Update T1 set name= ' SB ' where id=4; Update T1 set name= ' SB ' where name= ' Alex '; Delete delete from t1 where id=4; #对于清空表Records are available in two ways, but the latter is recommended for delete from t1;truncate T1; #当数据量比较大的情况下, use this method to delete the fast # self-increment idcreate table T5 (ID int primary (primary key) key Auto_increment,name char); CREATE table t4 (ID int NOT null Unique,name char (+)), insert into T5 (name) VALUES (' Egon5 '), (' Egon6 '), (' egon7 '), (' Egon8 '), (' Egon9 '), (' Egon10 '), (' Egon11 '), (' Egon12 '), (' egon13 '); #拷贝表结构create table T7 Select * FROM T5 where 1=2;alter table t7 modify ID int PR   Imary key auto_increment; There are problems with INSERT into T7 (' Egon1 '), (' Egon2 '), (' Egon3 '), (' Egon4 '), (' Egon5 '), (' Egon6 '), (' egon7 '), (' Egon8 '), (' Egon9 '), (' Egon10 '), (' Egon11 '), (' Egon12 '), (' Egon13 ');d elete from T7 where id=1; #删记录update T7 set Name= "; #修改字段对应的值 (This is all columns that modify the Name field) #创建用户create user ' lin ' @ ' localhost ' identified by ' 123 '; #insert, delele,update,select# Level 1: All the fields under all the tables under all libraries, grant SELECT On *. lin1 ' @ ' localhost ' identified by ' 123 '; (the first * represents all libraries, the second * represents all tables) #级别2: To the DB1 library, Under All tables, under all fields (@ before is account, @ is password) grant Select on db1.* to ' lin2 ' @ ' localhost ' identified by ' 123 '; #级别3: All fields under Table Db1.t1, Grant Select on DB1.T1 to ' lin3 ' @ ' localhost ' identified by ' 123 '; #级别4: db1.t1 field under Table Id,name, grant Select (Id,name) on db1.t1 to ' lin4 ' @ ' Localho St ' identified by ' 123 '; Grant Select (Id,name), update (name) on db1.t1 to ' lin5 ' @ ' localhost ' identified by ' 123 '; #修改完权限后 Remember to refresh the permissions flush privileges;

　　

What storage engine is now available in MySQL:
Mysql> show engines;
Look at your MySQL current default storage engine:
Mysql> Show variables like '%storage_engine% ';
1.Myisam is the default storage engine for MySQL and MyISAM is used by default when create creates a new table without specifying the storage engine for the new table. Each myisam is stored as three files on disk. The file name is the same as the table name, and the extension is. frm (store table definition), respectively. MYD (MYData, storing data),. MYI (myindex, storage index). Data files and index files can be placed in different directories, evenly distributed IO, for faster speeds.
The 2.InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.
Modify the default storage engine for MySQL

1. View MySQL storage engine commands,
Enter show engines at the mysql> prompt;
The field Support is: default represents the defaults storage engine
Www.2cto.com
2. Set InnoDB as the default engine:
In configuration file my.cnf, under [mysqld], add
Default-storage-engine=innodb a sentence

3. Restart the MySQL server:
Mysqladmin-u root-p shutdown
or service mysqld Restart log in to the MySQL database


MySQL View and modify the table's storage engine
1 Viewing system-supported storage engines
Show engines;
2 Viewing the storage engine used by the table
Two methods:
A, Show table status from Db_name where name= ' table_name ';
b, show create TABLE table_name;
If the displayed format is not good-looking, you can use \g instead of the end of line semicolon
Some people say that using the second method is not accurate, I tried to shut down the original default InnoDB engine is not able to execute the show CREATE TABLE table_name instruction, because it was built InnoDB table, after the default with the MyISAM engine, Causes the InnoDB table data to not be read correctly.
3 Modifying the Table engine method
ALTER TABLE table_name ENGINE=INNODB;
4 shutting down the InnoDB engine method
Turn off MySQL service: net stop MySQL
Locate the My.ini file in the MySQL installation directory:
Find Default-storage-engine=innodb instead of Default-storage-engine=myisam
Find #skip-innodb instead of Skip-innodb
Start MySQL service: net start MySQL


To view the database encoding:
The code is as follows:
SHOW CREATE DATABASE db_name;

To view the table encoding:
SHOW CREATE TABLE tbl_name;

To view field encodings:
The code is as follows:
SHOW full COLUMNS from Tbl_name

CharSet Character Set

MySQL First article