MySQL executes commands for external sql script files

A sql script is a sql statement that contains one or more sql commands. We can put these sql scripts in a text file (which we call a "sql script file") and then execute this sql script file through related commands.

1. Create a sql script file containing sql commands

Create a new text document in the root directory of drive D and rename it to day01.sql (you can choose the name yourself, preferably without Chinese characters or special symbols, and ending with .sql)

The day01.sql file contains a series of SQL statements, each of which ends with ;. The file content is as follows:

create table emps (
	empno int(8) primary key,
	ename varchar(20),
	job varchar(20),
	mgr int(8),
	hiredate date,
	sal double(11,2),
	comm double(11,2),
	deptno int(8)
);
  
insert into emps values(1,'张三','司令',0,sysdate(),18000.0,3000.0,1);
insert into emps values(2,'李四','Sales',1,sysdate(),7000.0,5000.0,1);
insert into emps values(3,'王五','销售',1,sysdate(),8000.0,2000.0,1);
insert into emps values(4,'马六','市场',1,sysdate(),6000.0,0,1);
insert into emps values(5,'Saturday','Market',1,sysdate(),5000.0,0,1);
insert into emps values(6,'冯八','市场',1,sysdate(),4000.0,0,1);
commit; 

It should be noted that you cannot directly create a sequence CREATE SEQUENCE in MySQL. You need to create a table to store the sequence, then manually insert a piece of data, and finally customize a function to handle the value to be increased.

The above SQL statements contain Chinese characters, so before executing the SQL script file, you need to check what character set your current MySQL uses. There are four levels of default character set settings in MySQL: server level, database level, and table level. Finally, there is the character set setting at the field level. Note that the first three are default settings and do not indicate that your fields will eventually use this character set. Therefore, we recommend using show create table tableName ; or show full fields from tableName; .

The character set settings for the connection environment in MySQL include Client, connection, and results. Through these parameters, MySQL knows what character set your client tool uses and what character set the result set should be. In this way, MySQL will do the necessary translation. Once these parameters are incorrect, it will naturally lead to conversion errors in the string during the transmission process. Basically 99% of the garbled characters are caused by this.

Current connection system parameters show variables like 'char%';

Exit mysql and use the net stop mysql command to stop the mysql service

Find your MySQL installation directory, find the my.ini file, and open it with Notepad

Reopen the CMD console, enter net start mysql and press Enter to start the mysql service. After successful startup, enter mysql and use the jsd170101 database

2. Execute SQL script files (three methods are introduced)

Method 1:

C:\Users\Administrator>mysql -uroot -p123456 -Djsd170101<D:\day01.sql
//mysql -u account -p password -D database name < sql file absolute path

Method 2:

mysql> source D:\day01.sql
//Execute source D:\day01.sql in mysql console

Method 3:

mysql> \. D:\day01.sql
// \. D:\day01.sql

This is the end of this article about MySQL commands for executing external sql script files. For more information about MySQL executing external sql, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!