MySQL beginner, MySQL modify, MySQL find, mysql delete, MySQL basic command

Mysql
https://dev.mysql.com/downloads/mysql/

1. Connect MySQL
Format: mysql-h host address-u user name-P user Password

1. Connect to MySQL on this machine.
First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after the prompt you to lose the password. Note that the user name can have a space or no space, but before the password must have no space, or let you re-enter the password.

If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>

2. Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: You can not add a space between the root and the other)

3. Exit MySQL command: Exit (enter)

2. Change the password
Format: Mysqladmin-u username-P Old password password new password

Mysql-u Root MySQL
Set password for [email Protected]=password (' secret ');
Flush privileges; Update permissions

1, add a password to root ab12.
First enter directory Mysql\bin under DOS, and then type the following command
Mysqladmin-u Root-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2, then change the root password to djg345.
Mysqladmin-u root-p ab12 Password djg345

3. Add new users
Note: Unlike above, the following is because it is a command in the MySQL environment, so it is followed by a semicolon as the command terminator

Format: Grant Select on database. * To User name @ login host identified by "password"

1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect to MySQL with the root user, and then type the following command:
Grant Select,insert,update,delete on * * to [[email protected] '%][email protected] '%[/email] ' identified by ' ABC ';

But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, solution see 2.

2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "ABC";

If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "";

Command: Create databases < database name >
Command: Show databases (note: There is a last s)
Command: Drop databases < database name >
Command: Use < database name >
Command:mysql> Select Database (); Select Current Database
Command: Mysql> select version (); Show MySQL version number
Command: Mysql> select Now (); Show Current Time
Command: SELECT dayofmonth (current_date); Display Day
Command: SELECT MONTH (current_date); Show month
Command: SELECT year (current_date); Show year
Command: mysql> Select "Welecome to my blog!"; Display string
Command: Select ((4 * 4)/10) + 25; Calculation
Command: Select CONCAT (F_name, "", L_name) as name from Employee_data where title = ' Marketing Executive '; Stitching strings
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]); Create a data table
Eg: Create a MyClass table
Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));
Command: mysql> desc mystudents View Table structure
Command: DROP table < table name > Delete tables
Command: mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59); Inserting data
Command: Select < Field 1, field 2,...> from < table name > where < expression >
Command: mysql> select * from MyClass; Querying all data
Command: mysql> SELECT * FROM MyClass ORDER by ID limit 0, 2; Limit the first two lines
Command: mysql> delete from MyClass where id=1; Delete data
Command: mysql> update MyClass set name= ' Mary ' where id=1; Update data

Command: ALTER TABLE name add field type other; Add Field
Command: mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '

ALTER DATABASE ' test ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin; Commands to modify database encoding
ALTER TABLE ' menu ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin Modify the encoded command
ALTER TABLE ' menu ' change ' text ' text ' text CHARACTER set UTF8 COLLATE utf8_bin Modify table's encoded command set character to UTF-8

Command: mysql> ALTER TABLE employee ADD primary key (ID); Add primary keyword index;
Alert Table Employees Add collumn birthdate Date [after | first | last name]
Command: mysql> ALTER TABLE name add unique index name (field name);
Command: mysql> ALTER TABLE employee add unique emp_name2 (cardnumber); add UNIQUE constraint index

Delete an index
mysql> ALTER TABLE name DROP INDEX name;
Example: Mysql>alter table employee DROP index emp_name;

Modify the original field name and type:
mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

Command: Rename table name to new table name; Modify the table name;

CREATE TABLE Employees2 SELECT * FROM Employees; Copying tables
CREATE table Employees2 select name, phone from employees; If only a subset of the content is needed
Create temporary table emp_temp SELECT * FROM Employees; To create a temporary table, you have permission

drop [temporary] table [if EXISTS] table_name [, TABLE_NAME, ...]

1. Export the entire database
The export file is present in the Mysql\bin directory by default
Mysqldump-u user name-p database name > exported file name
Mysqldump-u user_name-p123456 database_name > Outfile_name.sql

2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL

3. Export a database structure
Mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql
-D No data –add-drop-table add a drop table before each CREATE statement

4. Export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql

For example, back up the AAA library to file Back_aaa:
[Email protected] root]# Cd/home/data/mysql
[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

7.1 An instance of building a library and a build table 1
Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table

The following is the Insert field
Insert into teacher values (", ' Allen ', ' Dalian One ', ' 1976-10-10′ ');
Insert into teacher values (", ' Jack ', ' Dalian II ', ' 1975-12-23′ ');

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug.
1, you can write the above command as is written in a text file, assuming that school.sql, and then copied to c:\\, and in the DOS state into the directory [Url=file://\\mysql\\bin]\\mysql\\bin[/url], Then type the following command:
Mysql-uroot-p Password < C:\\school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

2, or enter the command line after using mysql> source C:\\school.sql; You can also import the School.sql file into the database.

7.2 An instance of building a library and a build table 2
Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table

The following is the Insert field
Insert into teacher values (' ' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');
Insert into teacher values (' ' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');

Note: In the table under construction
1. Set the ID to a numeric field of length 3: Int (3), and let it automatically add one to each record: auto_increment, and cannot be empty: NOT NULL, and let him be the main field primary key.

2. Set name to a character field of length 10

3. Set address as the character field of length 50, and the default value is Shenzhen.

4. Set year as the Date field.

Data type
DATE YYYY-MM-DD eg:20100810 | 2010-08-10 range 1000-01-01 to 9999-12-31, middle-can not write, can also employ other characters Fudaiti such as, *! (Non-alphanumeric)
DATETIME Yyyy-mm-dd HH:MM:SS eg:20100810153510 | 2010-08-10 15:35:10 Range: 1000-01-01 00:00:00 to 2037-12-31 23:59:59

Year
* Two-bit value 1~99 1~69=>2001-2069, 70~99=>1970~1999
* Four-bit numeric 1901~2155
* Two-digit string "00" ~ "99". Same two-bit value
* Four-digit string with four-bit number

Aliases for bool and Boolean TINYINT (1), Value: 0|1
BIGINT-9 223 372 036 854 775 808~9 223 372 036 854 775 807
INT-2 147 483 648 ~ 2 147 483 647

1. SET NAMES ' UTF8 ';
It is equivalent to the following three-sentence instruction:
SET character_set_client = UTF8;
SET character_set_results = UTF8;
SET character_set_connection = UTF8;

2. Create a database
Mysql> CREATE database name character set UTF8;

3. Create a table
CREATE TABLE ' type ' (
' id ' int (ten) unsigned not NULL auto_increment,
' Flag_deleted ' enum (' Y ', ' n ') character set UTF8 not NULL default ' N ',
' Flag_type ' int (5) Not NULL default ' 0 ',
' type_name ' varchar (character set UTF8 not NULL default ' ',
PRIMARY KEY (' id ')
) DEFAULT Charset=utf8;

4. Modify the database into UTF8.
mysql> ALTER DATABASE name character set UTF8;

5. Modify the table by default with UTF8.
mysql> ALTER TABLE type character set UTF8;

6. Modify the field with UTF8
Mysql> ALTER TABLE type modify type_name varchar () CHARACTER SET UTF8;

Unloading:

1, MySQL deleted, a file can not stay
sudo rm/usr/local/mysql
sudo rm-rf/usr/local/mysql*
2, sudo rm-rf/var/db/receipts/com.mysql.*

If you forget your password, force the modification:
Step1:? Stop MySQL Service
Step2:? Enter terminal input: cd/usr/local/mysql/bin/? Log in to administrator right after you enter the following command to disable the MySQL authentication feature after entering the carriage return./mysqld_safe--skip-grant-tables &: MySQL will restart automatically after carriage return (the status of MySQL in preferences will become running)
Step3. Enter command./mysql. Enter command FLUSH privileges after carriage return, enter command ALTER USER ' root ' @ ' localhost ' identified by ' your new password ';

PS aux | grep mysq*

1) PS A shows all the programs under the current terminal, including other users ' programs.
2) ps-a Show All Programs.
3) PS C lists the program, displays the actual instruction name of each program, and does not include the path, parameter or the indication of the resident service.
4) Ps-e The effect of this parameter is the same as specifying the "A" parameter.
5) When listing the program, PS e displays the environment variables used by each program.
6) PS F Displays the tree structure with ASCII characters, expressing the relationship between the programs.
7) ps-h shows the tree structure, indicating the relationship between the programs.
8) Ps-n shows all the programs except the program under the PS Command Terminal.
9) PS s uses the program signal format to display the program status.
PS S when listing programs, including interrupted sub-program data.
One) ps-t specify the terminal number and list the status of the program belonging to that terminal.
PS U Displays the status of the program in a user-oriented format.
PS x Displays all programs and does not differentiate by terminal.
PS is the process that shows the current state in running, and grep means searching in these, and PS aux is showing all processes and their status.

Modifying the configuration file configuration Character set
sudo cp/usr/local/mysql/support-files/my-medium.cnf/etc/my.cnf

sudo vi/etc/my.cnf

The [Client] section joins:

Default-character-set=utf8

[Mysqld] Partially added:

Character-set-server=utf8

Start MySQL After the modification is complete

This article original, without permission, not reproduced!!

MySQL beginner, MySQL modify, MySQL find, mysql delete, MySQL basic command