MySQL > alter database

1, connect to Mysql

 

Mysql -h host address -u user name -p user password

1. Connect to MYSQL on the local machine. Open the DOS window, enter the directory mysql\bin, type mysql -u root -p, and press Enter to prompt you for your password. Note that the user name may or may not be preceded by a space, but the password must not be preceded by a space, otherwise you will have to re-enter the password.

MYSQL > select * from ‘root’ where ‘root’ = ‘root’;

2. Connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Type the following command: mysql-h110.110.110 -u root -p 123; (Note: the space between u and root can be omitted, as well as other things.)

MYSQL > exit MYSQL

 

2. Change the password

Format: mysqladmin -u username -p Old password password New password

1. Create password ab12 for root. Mysqladmin -u root-password ab12 = mysqladmin -u root-password ab12 = mysqladmin -u root-password

2. Change the password of root to djg345. mysqladmin -u root -p ab12 password djg345

3. Add new users

Note: unlike the above, the following commands are in the MYSQL environment, so they are followed by a semicolon as the end of the command

Grant select on database.* to username@identified by “password”

Mysql > alter database test1; mysql > alter database test1; mysql > alter database test1; First log in to MYSQL as user root and type the following command: Grant, the select, insert, update, and delete on *. * to [email = test1 @ %] test1 @ % [/ email] “Identified by” ABC “;

If someone knows the password for Test1, they can log into your mysql database from any computer on the Internet and do whatever they want with your data. See solution 2.

MYSQL > select * from ‘test2’ where ‘ABC’ = ‘ABC’; select * from ‘mydb’ where ‘ABC’ = ‘ABC’; select * from ‘mydb’ where ‘ABC’ = ‘ABC’; He can’t access the database directly from the Internet, he can only access it through a Web page on the MYSQL host. Grant, the select, insert, update, and delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/ email] identified by “ABC”;

If you don’t want test2 to have a password, you can use another command to erase the password. Grant, the select, insert, update, and delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/ email] identified by “;”

Details:

Permission 1, permission 2… Permission to n for the select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, file, etc. 14 permissions. When permission 1, permission 2… Permission N is replaced by all PRIVILEGES or all, indicating that all privileges are granted to the user. When the database name. Table name is replaced by *.*, the user is granted the permission to operate all tables in all databases on the server. The user address can be localhost, IP address, machine name, or domain name. You can also use ‘%’ to connect from any address. The connection password cannot be empty; otherwise, the creation fails.

Such as:

Mysql > grant the select, insert, update, delete, create, drop on VTDC. The employee to [email protected] identified by ‘123’); To give users from 10.163.225.87 jee distribution database VTDC to select the employee table, insert, update, delete, create, drop operations such as permissions, and set a password for 123.

Mysql >grant all on VTDC.* to [email protected] identified by ‘123’; Grant all operations on VTDC tables to jee from 10.163.225.87 and set password 123.

Mysql >grant all on *.* to [email protected] identified by ‘123’; Grant all operations on all tables on all databases to jee from 10.163.225.87 with password 123.

Mysql >grant all PRIVILEGES on *.* to jee@localhost identified by ‘123’; Assign all operations on all tables on all databases to native user JEE and set password 123.

 

4.1 Creating a Database

Note: Connect to the Mysql server before creating the database

Command: create database < database name >

Mysql > create database XHKDB mysql> create database XHKDB;

Example 2: Create a database and assign users

①CREATE DATABASE DATABASE name;

(2) GRANT the SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON the database name. * TO the database name @ localhost IDENTIFIED BY ‘password’.

SET PASSWORD FOR ‘localhost’ @’localhost’ = OLD_PASSWORD(‘ PASSWORD ‘);

Run three commands in sequence to create the database. Note: The Chinese “password” and “database” are set by users themselves.

 

4.2 Displaying the Database

Note: Change the database default encoding in order to no longer display garbled characters. The following uses GBK code page as an example:

1, modify the configuration files of the MYSQL: my. Modify the default within ini – character set GBK = 2, runtime code changes: (1) the Java code: JDBC: MYSQL: / / localhost: 3306 / test? UseUnicode =true&characterEncoding= GBK; useUnicode=true&characterEncoding= GBK; charset=gb2312″); Int mysql_set_character_set(MYSQL * MYSQL, char * csname); This function is used to set the default character set for the current connection. The string csname specifies a valid character set name. Concatenation becomes the default collation of the character set. This function works like the SET NAMES statement, but it can also SET the value of mysql- > charset, which affects the character SET SET by mysQL_real_escape_string ().

 

4.3 Deleting a Database

Mysql > drop database drop_database; Query OK, 0 rows affected (0.00 SEC)

Mysql > drop database drop_database; ERROR 1008 (HY000): Can’t drop database ‘drop_database’; Database doesn’t exist // An error occurred, the ‘drop_database’ database cannot be deleted, the database does not exist. mysql> drop database if exists drop_database; Query OK, 0 rows affected, 1 warning (0.00 SEC) mysql> create database drop_database; Query OK, 1 row affected (0.00 SEC) mysql> drop database if exists drop_database; Query OK, 0 rows affected (0.00 SEC) Query OK, 0 rows affected (0.00 SEC)

 

4.4 Connecting a Database

Use < database name >

For example, if the XHKDB database exists, try to access it: mysql> use XHKDB; Screen prompt: Database changed

The use statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default database until the end of the paragraph, or until a different USE statement is issued: mysql> USE db1; mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable mysql> USE db2; mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable

Using the USE statement to mark a particular current database does not prevent you from accessing tables in other databases. The following example can access the author table from the DB1 database and the edit table from the DB2 database: mysql> USE DB1; mysql> SELECT author_name,editor_name FROM author,db2.editor -> WHERE author.editor_id = db2.editor.editor_id;

The USE statement is set up for compatibility with Sybase.

Some netizens asked how to quit after the connection. Use the name of the database. If you want to use the name of the database, use the name of the database.

4.5 Selected Database

Mysql > select database();

The SELECT command in MySQL is similar to print or write in other programming languages. You can use it to display a string, a number, the result of a mathematical expression, etc. How to use the SELECT command in MySQL?

MYSQL > select version(); + — — — — — — — — — — — — — — — — — — — — — — — + | version () | + — — — — — — — — — — — — — — — — — — — — — — — + | 6.0.4 – alpha – community | + — — — — — — — — — — — — — — — — — — — — — — — + 1 Row in set (0.02sec)

Mysql > select now(); +———————+ | now() | +———————+ | 2009-09-15 22:35:32 | +———————+ 1 row in set (0.04 SEC)

SELECT DAYOFMONTH(CURRENT_DATE); +————————–+ | DAYOFMONTH(CURRENT_DATE) | +————————–+ | 15 | + — — — — — — — — — — — — — — — — — — — — — — — — — — + 1 row in the set (0.01 SEC)

SELECT MONTH(CURRENT_DATE); + — — — — — — — — — — — — — — — — — — — — — + | the MONTH (CURRENT_DATE) | + — — — — — — — — — — — — — — — — — — — — – + 9 | | + — — — — — — — — — — — — — — — — — — — — – + 1 row in the set (0.00) sec)

SELECT YEAR(CURRENT_DATE); + — — — — — — — — — — — — — — — — — — — — + | YEAR (CURRENT_DATE) | + — — — — — — — — — — — — — — — — — — — — + | 2009 | + — — — — — — — — — — — — — — — — — — — — + 1 row in the set (0.00) sec)

Mysql > SELECT “welecome to my blog!” ; +———————-+ | welecome to my blog! | +———————-+ | welecome to my blog! | + — — — — — — — — — — — — — — — — — — — — — — + 1 row in the set (0.00 SEC)

Select ((4 * 4) / 10) + 25; + — — — — — — — — — — — — — — — — — — — — — — + | (4 * 4)/(10) + 25 | + — — — — — — — — — — — — — — — — — — — — — — + | | + 26.60 — — — — — — — — — — — — — — — — — — — — — — + 1 row in Set (0.00 SEC)

Select CONCAT(f_name, “”, l_name) AS Name from employee_data where title = ‘Marketing Executive’; +—————+ | Name | +—————+ | Monica Sehgal | | Hal Simlai | | Joseph Irvine | +—————+ 3 rows In set (0.00 SEC) Note: CONCAT() is used to concatenate strings of characters. In addition, we use the AS we learned earlier to give the result column ‘CONCAT(f_name, “”, l_name)’ a pseudonym.

 

5.1 Creating data Tables

Command: create table < table name > (< fieldname > 1 type < 1 > [,.. < field name n > < type n >]);

For example, create a table named MyClass,

The field name	Numeric types	The data width	Whether is empty	Whether the primary key	Automatically add	The default value
id	int	4	no	primary key	auto_increment
name	char	20	no
sex	int	4	no			0
degree	double	16	is

mysql> create table MyClass(

> id int(4) not null primary key auto_increment,

> name char(20) not null,

> sex int(4) not null default ‘0’,

> degree double(16,2));

5.2 Deleting a Data Table

Drop table < table name >

Mysql > drop table MyClass;

DROP TABLE Is used to DROP one or more tables. You must have DROP permission for each table. All table data and table definitions are cancelled, so use this statement with care!

Note: For a partitioned TABLE, DROP TABLE permanently cancels the TABLE definition, the partitions, and any data stored in those partitions. DROP TABLE also cancels the partition definition (.PAR) file associated with the dropped TABLE.

The IF EXISTS command is used to prevent errors from occurring for tables that do not exist. When using IF EXISTS, a NOTE is generated for each table that does not exist.

RESTRICT and CASCADE make partitioning easier. Currently, RESTRICT and CASCADE do not work.

5.3 Inserting table Data

Command: insert into < table name > [(1 > < field name [,.. < field name n >])] values (1) [, value (n)]

For example, insert two records into table MyClass that represent the score of Tom (number 1) as 96.45, Joan (number 2) as 82.99, and Wang (number 3) as 96.5. Mysql > insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (2,’Wang’, 96.59);

Note: Insert into can only insert one record into the table at a time.

5.4 Querying Data in a Table

 
1) Query all rows

Select * from MyClass order by id limit 0,2; select * from MyClass order by id limit 0,2;

Select is typically used in conjunction with WHERE to query for more precise and complex data.

5.5 Deleting Data from a table

Command: delete from table name where expression

Mysql > delete from MyClass where id=1;

Below is a comparison of the table before and after deleting data.

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

Select * from Persons where LastName=’Griffin’ select * from Persons where LastName=’Griffin’;

[php] view plain copy

2.    $con = mysql_connect(“localhost”,”peter”,”abc123″);
3. if (! $con)
4.    {
5.       die(‘Could not connect: ‘ . mysql_error());
6.    }
7.    mysql_select_db(“my_db”, $con);
8.    mysql_query(“DELETE FROM Persons WHERE LastName=’Griffin'”); mysql_close($con);
9. ? >

[php] view plain copy

2.    $con = mysql_connect(“localhost”,”peter”,”abc123″);
3. if (! $con)
4.    {
5.       die(‘Could not connect: ‘ . mysql_error());
6.    }
7.    mysql_select_db(“my_db”, $con);
8.    mysql_query(“DELETE FROM Persons WHERE LastName=’Griffin'”); mysql_close($con);
9. ? >

After this delete, the table looks like this:

FirstName	LastName	Age
Glenn	Quagmire	33

5.6 Modifying Data in a Table

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1= exPR1 [, col_name2= exPR2…] [WHERE where_definition] [ORDER BY …] [LIMIT row_count]

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1= exPR1 [, col_name2= exPR2… [WHERE where_definition]

The UPDATE syntax updates columns in an existing table row with new values. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the ORDER specified. The LIMIT clause is used to give a LIMIT that limits the number of rows that can be updated.

5.7 Adding a Field

alter table
add






alter table
add 
int(
) default

Mysql > alter table table_name alter table table_name add index ); Mysql > alter table employee add index emp_name (name);

Mysql > alter table table_name add primary key; Mysql > alter table employee add primary key(id);

Mysql > alter table table_name add unique; Mysql > alter table employee add unique emp_name2(cardnumber);

Mysql > alter table drop index drop index; Mysql >alter table employee drop index emp_name;

ALTER TABLE table_name ADD field_name field_type;

ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

ALTER TABLE table_name DROP field_name;

5.8 Changing table names

Rename table old table name to new table name;

Mysql > rename table MyClass to YouClass;

When you RENAME, you cannot have any locked tables or active transactions. You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table.

If MySQL encounters any errors in a multi-table rename, it will retrograde all renamed tables, returning everything to its original state.

RENAME TABLE was added in MySQL 3.23.23.

6. Back up your database

If you run the command in[url=file://\\mysql\\ \bin]\\mysql\ bin[/url], you can access the bin folder in the mysql directory

Mysqldump -u user_name -p123456 database_name > mysqldump -u user_name -p123456 database_name > outfile_name.sql

2. Export a table mysqldump -u user name -p database name Table name > 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 mysqldump-uroot -p — default-character-set=latin1 — set-charset= GBK — skip-opt database_name > outfile_name.sql

For example, to back up the AAA library to the file back_aaa: [root@test1 root]# CD /home/data/mysql [root@test1 mysql]# mysqldump -u root -p –opt aaa > back_aaa

7.1 An example of database building and table building 1

// insert into teacher values(“,’ Allen ‘,’ 76-10-10 ‘); // Insert into teacher values(“,’ Allen ‘,’ 76-10-10 ‘); Insert into sc values(“,’ jack ‘,’ 2015-12-23 ‘);

If you type the above command at the mysql prompt, you can do the same, but it is not convenient for debugging. [url=file://\\mysql\\ \bin]\\mysql\ bin[/url] [/url] MySQL -uroot -p password < c:\\school. SQL MySQL -uroot -p password < c:\\school. SQL If there are any errors, there will be a prompt.

Mysql > source c:\\school.sql; You can also import the school. SQL file into the database.

7.2 An example of database building and table building 2

/ / insert into the teacher here is inserted into the field values (‘ ‘ ‘ ‘, ‘glchengang’, ‘shenzhen 1’, ‘1976-10-10’); Insert into the teacher values (‘ ‘ ‘ ‘, ‘jack’, ‘shenzhen 1’, ‘1975-12-23’);

Int (3); int(3); int(3); And make it auto-increment each record :auto_increment; Cannot be null :not null; And make it the primary key.

2. Set NAME to a character field of length 10

3. Set ADDRESS to a character field of length 50, with the default value shenzhen.

Set YEAR to the date field.

The original address: www.bieryun.com/3579.html