This article mainly tells you about the actual configuration method of the free installation version of MySQL, as well as a detailed description of its download URL. I hope you will gain some benefits.
This article mainly tells you about the actual configuration method of the free installation version of MySQL, as well as a detailed description of its download URL. I hope you will gain some benefits.
The following articles mainly describe the actual configuration method of the MySQL installation-free version. the installation-free version can be downloaded from the relevant website. after the download, it can be decompressed to c: \ MySQL gets his arbitrary location, decompress it, in the directory, find the my-huge.ini, my-large.ini, my-medium.ini configuration file.
Open for configuration:
The code is as follows:
[MySQLd]
# Set basedir to your installation path
Basedir = c:/MySQL
# Set datadir to the location of your data directory
Datadir = c:/mydata/data
In the MySQL installation-free configuration method, we need to set the MySQL character set, gbk or UTF-8.
The code is as follows:
# Set character set
Default-character-set = gbk
# Set character collation
Default-collation = gbk_chinese_ci
Save and save it as my. ini
Finally, choose start> run.
The code is as follows:
C: \ MySQL \ bin \> MySQLd -- install MySQL -- defaults-file = C: \ my. ini
C: \ MySQL \ bin \> net start MySQL
OK. the default account is root and the password is empty.
The above content is an introduction to the configuration method of the free installation version of MySQL. I hope you will have some gains.
The following is a reference of other netizens:
Using mysql-noinstall-5.1.6 (win32) as an example
1> extract the compressed file mysql-noinstall-5.1.6-alpha-win32.zip to a directory, set MYSQL_HOME in the environment variable, and add % MYSQL_HOME % \ bin
To path.
2> create the my. ini configuration file. the content is as follows:
[Mysqld]
# Set basedir to the mysql installation path
Basedir =... \ mysql
Datadir =... \ mysql \ data
Put the my. ini file under the system file
The XP system is in the C: \ windows directory, and the 2000 system is in the C: \ winnt directory.
3> start and stop mysql
Mysqld-nt.exe --install(install Windows service, uninstall using mysqld-nt.exe -- remove)
Start: net start mysql
Stop: net stop mysql
4> connect to mysql
Format: mysql-h host address-u user name-p user password
Example 1: Connect to mysql on the local machine
Mysql-uroot-p
Example 2: Connect to mysql on the remote host
Mysql-h110.110.110.110-uroot-pabcd123
5> exit mysql command
Exit (press enter)
Or
Quit (press enter)
6> Change password
Format: mysqladmin-u username-p old password new password
For example, add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command
Mysqladmin-uroot password ab12
Note: Because the root account does not have a password at the beginning, the old-p password can be omitted.
2. Example 2: change the root password to djg345.
Mysqladmin-uroot-pab12 password djg345
The following is a command in the mysql environment, with a semicolon as the command Terminator
7> add new users
Format: grant select on database. * to username @ login host identified by "password ";
Example 1: add a user named "test1" with the password "abc" so that the user can log on to any host and
Query, insert, modify, and delete permissions. First, use the root user to connect to mysql, and then type the following command:
Grant select, insert, update, delete on *. * to test1 @ "%" Identified by "abc ";
Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost and access the database
Mydb query, insert, modify, and delete operations (localhost refers to the local host, that is, mysql data
The host where the database is located.
Access the database only through the web page on the mysql host.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by "abc ";
8> Display the database list
Show databases;
9> Display Data tables in the database
Use mysql; // database name
Show tables;
10> display the data table structure
Describe table name;
11> database creation
Create database name;
12> create a table
Use database name;
Create table name (field setting list );
13> delete databases and tables
Drop database name;
Drop table name;
14> clear records in the table
Delete from table name;
15> Display records in the table
Select * from table name;
Example:
Drop database if exists school; // delete if SCHOOL exists
Create database school; // create a database SCHOOL
Use school; // open the SCHOOL library
Create table teacher // create table TEACHER
(
Id int (3) auto_increment not null primary key,
Name char (10) not null,
Address varchar (50) default 'shenzhen ',
Year date
); // Table creation ends
// Insert fields as follows
Insert into teacher values ('', 'glengang ', 'Shenzhen Zhongyi', '2017-10-10 ');
Insert into teacher values ('', 'Jack', 'Shenzhen Zhongyi ', '2017-12-23 ');
Note: Table creation
(1) set the ID to a numeric field with a length of 3: int (3), and automatically add auto_increment to each record,
Cannot be blank: not null, and make it the primary key.
(2) set NAME to a 10-character Field
(3) set ADDRESS to a 50-character field, and the default value is Shenzhen. What is the difference between varchar and char?
It will only be said later.
(4) set YEAR as the date field.
If you type the preceding command at the mysql prompt, debugging is not convenient. You can run the preceding command
It is written as is to a text file, assuming it is school. SQL, then copied to c:, and enter the Directory in DOS status
Mysql in, and then type the following command:
Mysql-uroot-p password <c: school. SQL
If it succeeds, no display is displayed for a blank row. if there is an error, a prompt is displayed. (The above command has been debugged, you
You only need to remove the // annotation ).
16> transfer text data to the database
1. text data should conform to the format: field data is separated by the tab key, and null value is used instead. Example:
3 rose:
4 mike: Shenzhen No. 1,-12-23
2. data import command load data local infile "file name" into table name;
Note: You 'd better copy the file to the mysql in directory and use the use command first? Why? /P>
17> export and import data
1. export a table
Mysqldump -- opt school> school. SQL
Note: Back up all the tables in the school database to the school. SQL file. school. SQL is a text file,
Select the file name. open the file to see if you have any new discoveries.
Mysqldump -- opt school teacher student> school. teacher. student. SQL
Note: back up the teacher and student tables in the school database to school. teacher. student. SQL.
.
2. import tables
Mysql
Mysql> create database school;
Mysql> use school;
Mysql> source school. SQL;
(Or change school. SQL to school. teacher. SQL/school. teacher. student. SQL)
3. export the database
Mysqldump -- databases db1 db2> db1.db2. SQL
Note: Back up database dbl and db2 to the db1.db2. SQL file. db1.db2. SQL is a text file with a file name.
Choose, open it, and you will see new discoveries.
(For example:
Mysqldump-h host-u user-p pass -- databases dbname> file. dump
Import the database dbname on the host named "user" and "password pass" to file. dump .)
4. import database
Mysql <db1.db2. SQL
5. copy the database
Mysqldump -- all-databases> all-databases. SQL
Note: backing up all databases to a all-databases. SQL file, a all-databases. SQL is a text file,
File name.
6. import database
Mysql
Mysql> drop database;
Mysql> drop database B;
Mysql> drop database c;
...
Mysql> source all-databases. SQL; (or exit mysql after exiting mysql <all-databases. SQL)
18> create a full Super user who can connect to the server from anywhere, but must use a password something to do this
Grant all privileges on *. * TO monty @ "%" identified by 'something' with grant option;
19> delete authorization
Revoke all privileges on *. * from root @ "% ";
USE mysql;
DELETE from user WHERE User = "root" and Host = "% ";
Flush privileges;
20> Create a user custom to log on to weiqiong.com on a specific client and access the specific database bankaccount.
Mysql> grant select, INSERT, UPDATE, DELETE, CREATE, drop on bankaccount .*
TO custom@weiqiong.com identified by 'stupid ';
21> rename a table
Alter table t1 RENAME t2;
22> Change columns
To change column a from INTEGER to tinyint not null (same name ),
Change column B from CHAR (10) to CHAR (20), rename it, and change from B to c:
Alter table t2 MODIFY a tinyint not null, CHANGE B c CHAR (20 );
23> add columns
Add a new TIMESTAMP column named d:
Alter table t2 ADD d TIMESTAMP;
24> add an index on column d and set column a as the primary key.
Alter table t2 add index (d), add primary key ();
25> delete a column
Alter table t2 drop column c;
26> delete record
DELETE from t1 where c> 10;
27> Change rows
UPDATE t1 SET user = weiqiong, password = weiqiong;
28> create an index
Create an index using the first 10 characters of the name column:
Create index part_of_name ON customer (name (10 ));
29> set the MySQL Chinese character set (MySQL displays Chinese normally)
1. configure the server, modify the my. ini file, store records using the Chinese character set, and sort and compare records in Chinese.
[Mysqld]
# Set character set
Default-character-set = gbk
# Set character collation
Default-collation = gbk_chinese_ci
2. if you want to use the mysql command line on the server in the Chinese environment, change the default mysql character set in the my. ini file.
[Mysql]
# Set character set
Default-character-set = gbk
We also recommend a graphic interface tool navicat that is compatible with Chinese characters.
