DML data manipulation language of MySQL core knowledge

MySQL deep analysis table data addition

Create table

Employee list of a company
create table employee(
	empno int primary key comment 'Employee number',
    ename carchar(20) commenr 'Employee name',
    job carchar(20) commenr 'Employee position',
    mgr int comment 'Employee superior number',
    hiredate date comment 'Date of employment',
    sal decimal(7,2) comment 'salary',
    deptnu int comment 'Department number'
);

Normal insert table data

insert into Table name(Field name) values (Field corresponding value);

insert into employee (empno,ename,job,mgr,hiredate,sal,deptnu) values ('1000','Xiao Ming','manager','10001','2020-01-01','12345.23','10');

insert into Table name values (Field corresponding value);

insert into employee values ('1000','Xiao Ming','manager','10001','2020-01-01','12345.23','10');

Worm replication (copying data from one table to another)

insert into Table name 1 select * from Table name 2;

insert into Table name 1(Field name 1,Field name 2) select Field name 1,Field name 2 from Table name 2;

insert into emp(empno,ename) select empno,enmae from employee;

Create table copy

create table Table name 1 as select Field name 1, field name 2 from Table name 2;

create table emp as select empno,ename from employee;

Insert multiple data at once

insert into Table name (Field name) values (Corresponding value 1),(Corresponding value 2),(Corresponding value 3);

Modification and deletion of MySQL in-depth analysis table data

Modify (update)

update Table name set Field name 1=Field value 1 where Field name=value

update Table name set Field name 1=Field value 1,Field name 2=Field value 2 where Field name=value

delete

delete from Table name where Field name=value

truncate table Table name;
delete from Table name;
drop table Table name;

matters needing attention

1. What would you do before deleting data?
   • The data will be backed up in case of data fallback
2. The common feature of delete, truncate and drop is to delete data. What are their differences?
   • Delete will record the deleted operation so that the data can be rolled back. It will not free up space and will not delete the definition.
   • truncate will not record the deletion operation, will restore the space occupied by the table to the original, and will not delete the definition.
   • drop will delete the whole table and free up the space occupied by the table.
3. Delete speed
   • drop > truncate > delete

Chinese garbled code of mysql core knowledge

• View the character set currently used by mysql: show variables like 'character%';

mysql> show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name 		   | Value 							  |
+--------------------------+----------------------------------+
| character_set_client 	   | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

• character_set_client: the character set of the data requested by the client

• character_set_connection: the character set of the connection between the client and the server

• character_set_database: the character set used by a library in the database server. If it is not specified when building the database, the character set on the configuration will be used by default

• character_set_results: the character set returned to the client (what code is the data read from the database)

• character_set_server: set the default character set specified during server installation.

• character_set_system: system character set (utf8 is the one that cannot be modified)

• character_sets_dir: save path of mysql character set file

• Temporary: set names gbk;

• Permanent: modify the configuration file my Inside CNF

[client]
default-character-set=gbk
 External display

[mysqld]
character_set_server=gbk
 Acting internally, it will act on the default character set when creating the library table

Modify the character set encoding of the library

alter database xiaoxiao default character set gbk;

Modify the character set encoding of the table

alter table employee default character set utf8;