Learn MySQL (next)

Concrete example 22, MySQL alter command

We need to use the MySQL alter command when we need to modify the data table names or modify the field of the data tables.

1. If you need to modify the name of the data table, you can use the RENAME clause in the ALTER table statement. The following example renames the data table Old_tablename to New_tablename:

mysql> ALTER TABLE old_tablename RENAME to New_tablename;

2, delete, add or modify table fields, such as the following command uses the ALTER command and the drop clause to delete the Table_title field that created the table:

mysql> ALTER TABLE blog_table_test  DROP table_title;

In MySQL, add a column to the data table using the ADD clause, and the following instance adds the Table_title field to the table blog_table_test and defines the data type:

mysql> ALTER TABLE blog_table_test ADD table_title INT;

Modify the field type and name, as the following command uses modify in the ALTER command to change the type of the field Table_title to CHAR (10), you can execute the following command:

ysql> ALTER TABLE blog_table_test MODIFY table_title CHAR (10);

Use the change clause, followed by the changed keyword, followed by the name of the field you want to modify, and then specify the new field name and type. Try the following example:

mysql> ALTER TABLE blog_table_test change Oldfield Newfield Newtype;

3. ALTER TABLE affects Null values and default values, and when you modify a field, you can specify whether to include a value or whether to set a default value. The following example specifies that the field table_title is not NULL and the default value is 100.

mysql> ALTER TABLE Blog_table_test
     -MODIFY table_title BIGINT not NULL DEFAULT 100;

You can also use ALTER to modify the default value of a field, instance:

Mysql> alter TABLE blog_table_test alter Table_title SET DEFAULT 1000;

4. Use the ALTER command to add and remove primary keys. When you add a primary key index, you need to make sure that the primary key is not empty by default (not NULL). Instance:

mysql> ALTER TABLE blog_table_test MODIFY table_id INT not NULL;
mysql> ALTER TABLE blog_table_test ADD PRIMARY KEY (table_id);

5. Use the ALTER command to add and remove indexes, including four ways to add indexes to data tables (about indexes are described below): 1, ALTER TABLE tbl_name add PRIMARY key (column_list): The statement adds a primary key, This means that the index value must be unique and cannot be null. 2. ALTER TABLE tbl_name ADD unique index_name (column_list): This statement creates an indexed value that must be unique (except for NULL, NULL may occur more than once). 3, ALTER TABLE tbl_name Add index index_name (column_list): Add a normal index, the index value can appear multiple times. 4. ALTER TABLE tbl_name ADD fulltext index_name (column_list): This statement specifies that the index is fulltext for full-text indexing.

23. MySQL Index

The index is also a table that holds the primary key and index fields and points to the records of the entity table. It is divided into single-column indexes: An index consists of only single columns. and composite indexes: An index contains multiple columns.

Indexes can greatly improve the retrieval speed of MySQL, and of course there are flaws, too much use of the index will cause abuse, will reduce the speed of the Update table, and the index will occupy disk space index file.

1, the creation of ordinary index, the most basic index, it has no restrictions. It is created in the following ways:

To create an index:

If it is a Char,varchar type, length can be less than the actual length of the field, and length must be specified if it is a blob and text type.

Modify table structure (add index):

ALTER table TableName ADD INDEX indexname (columnName)

Specify directly when creating the table:

CREATE TABLE Blog_table_test (
ID INT not NULL,
Username VARCHAR (+) not NULL,
INDEX [IndexName] (username (length))
);

2. Unique index: The value of the indexed column must be unique, but a null value is allowed. If it is a composite index, the combination of column values must be unique. It is created in the following ways:

To create an index:

To modify a table structure:

ALTER table Blog_table_test ADD UNIQUE [IndexName] (username (length))

Specify directly when creating the table:

reate TABLE Blog_table_test (
ID INT not NULL,
Username VARCHAR (+) not NULL,
UNIQUE [IndexName] (username (length))
);  

3. Syntax for deleting indexes:

DROP INDEX [IndexName] on blog_table_test;

4. Display index Information:

Mysql> SHOW INDEX from table_name; \g

24. MySQL Temp Table

The MySQL temp table is only visible on the current connection, and if you use PHP scripts to create a MySQL temp table, the temporary table will be automatically destroyed whenever the PHP script executes. If you use the MySQL client program to connect to the MySQL database server to create a temporary table, the temporary table will be destroyed only when the client program is closed, and of course it can be destroyed manually.

The statement that creates the temporary table is basically the same as the statement that creates the data table, except that the Cerate table statement is added to the middle of the temporary.

25. MySQL Copy Table

To copy a table: 1. Use the Show create TABLE command to get the Creation data table (Show create TABLE TableName \g;) Statement that contains the structure, index, and so on of the original data table. 2, after copying the name of the data table, paste in the MySQL console execution, at this time a cloned table has been established. 3, copy the contents of the table, you can use INSERT into ... SELECT statement to implement. The third part of the operation statement is as follows:

Mysql> INSERT into new_table (runoob_id,
                            Runoob_title,
                            Runoob_author,
                            submission_date)
    SELECT Runoob_id,runoob_title,
            runoob_author,submission_date
    From Blog_table_test;

26. MySQL Meta-data

In the MySQL command prompt, we can easily get the above server information. But if you use a scripting language such as Perl or PHP, you need to invoke a specific interface function to get it. For example, in PHP, you can use the Mysqli_affected_rows () function to get the number of records affected by a query statement.

$result _id = mysqli_query ($conn _id, $query);
# If the query fails to return
$count = ($result _id mysqli_affected_rows ($conn _id): 0);
Print ("$count data is affected \ n");

The following instances output all databases on the MySQL server:

&lt?php
$dbhost = ' localhost:3306 ';  MySQL Server host address
$dbuser = ' root ';            MySQL User name
$dbpass = ' 123 ';          MySQL User name password
$conn = Mysqli_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
    Die (' Connection failed: '. Mysqli_error ($conn));
}
Set the encoding to prevent Chinese garbled characters
$db _list = mysqli_query ($conn, ' SHOW DATABASES ');
while ($db = Mysqli_fetch_object ($db _list))
{
  Echo $db->database. "
";
}
Mysqli_close ($conn);
? &gt

The following command statements can be used at the command prompt in MySQL, or in scripts such as PHP scripts. 1. SELECT version (): Server version information. 2. SELECT database (): The current name (or return null). 3, SELECT User (): Current user name. 4. SHOW Status: Server state. 5, SHOW VARIABLES: Server configuration variables.

27. MySQL Sequence Use

The MySQL sequence is a set of integers: 1, 2, 3, ..., because a data table can only have one field self-increment primary key, if you want to implement other fields also automatically increase, you can use the MySQL sequence to implement.

1. The simplest way to use a sequence in Auto_increment:mysql is to use MySQL auto_increment to define the column. Auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1.

2. Get auto_increment value: In MySQL client you can use the last_insert_id () function in SQL to get the value of the last inserted table in the self-increment column. Instead, PHP uses the mysql_insert_id () function to get the value of the Auto_increment column in the inserted SQL statement that executes:

mysql_query ("INSERT into Blog_table_test (name,date,origin)
VALUES (' moth ', ' 2001-09-14 ', ' windowsill '), $conn _id);
$seq = mysql_insert_id ($conn _id);

3. Reset sequence: If you delete multiple records from a datasheet and want to rearrange the auto_increment columns of the remaining data, you can do so by removing the self-contained columns and then adding them again. However, the operation is very careful, if the deletion of a new record added, there may be data chaos. The operation is as follows:

mysql> ALTER TABLE insect DROP filed;
mysql> ALTER TABLE Insect
    -ADD filed INT UNSIGNED not NULL auto_increment first,
    ADD PRIMARY KEY (filed);

4. Set the start value of the sequence: In general, the start value of the sequence is 1, but if you need to specify a start value of 100, then we can do so by the following statement:

mysql> CREATE TABLE Blog_table_test
    (
    -table_id INT UNSIGNED not NULL auto_increment,
    PRIMARY KEY (table_id),
    
    -Table_date date not NULL,
    -Table_origin VARCHAR (+) not NULL
) Engine=innodb auto_increment=100 Charset=utf8;

Or:

mysql> ALTER TABLE t auto_increment = 100;

28. MySQL handles duplicate data

There are no indexes and primary keys in the table, the table allows multiple duplicate records, if you want to set the table field Field_one,filed_two data cannot be duplicated, you can set the double primary key mode to set the uniqueness of the data, if you set a double primary key, then the default value of the key can not be null, Can be set to not NULL. For example:

CREATE TABLE Blog_table_test
(
   Field_one CHAR () not NULL,
   Field_two CHAR () not NULL,
   PRIMARY KEY (Field_one, Field_two)
);

If we set a unique index, the SQL statement will fail to execute successfully and throw an error when inserting duplicate data. The difference between insert IGNORE into and insert into is that insert IGNORE ignores data that already exists in the database, inserts new data if the database has no data, and skips the data if there is data. This preserves the data that already exists in the database for the purpose of inserting data into the gap.

Insert IGNORE into when data is inserted, after the uniqueness of the record is set, if you insert duplicate data, no error is returned, only as a warning. Replace into into if there are primary or unique records, delete them first. Insert a new record again.

Another way to set the data's uniqueness is to add a unique index.

1. Statistical data duplication

Mysql> SELECT COUNT (*) as repetitions, Field_one, field_two
    From table_name
    GROUP by Field_one, Field_two
    have repetitions > 1;

To query for duplicate values, do the following: 1. Determine which column contains values that may be duplicated. 2. In the column selection list, use the columns listed by COUNT (*). 3. The columns listed in the GROUP BY clause. 4. The HAVING clause sets the number of repetitions greater than 1.

2. Filter duplicate data

Read non-repeating data you can use the DISTINCT keyword in a SELECT statement to filter for duplicate data:

Mysql> SELECT DISTINCT Field_one, Field_two
    From Blog_table_test;

You can also use GROUP by to read data that is not duplicated in a data table:

Mysql> SELECT Field_one, Field_two
    From Blog_table_test
    GROUP by (Field_one, field_two);

3. Delete duplicate data

You can add index (index) and Primay key (primary key) in the datasheet to remove duplicate records from the table:

mysql> ALTER IGNORE TABLE blod_table_test
    ADD PRIMARY KEY (Field_one, field_two);

You can use the following SQL statement to delete duplicate records in a table:

mysql> CREATE TABLE blog_table SELECT field_one, Field_two
    From                  Blog_table_test;
                      GROUP by (Lfield_one, field_two);
mysql> DROP TABLE blog_table_test;
mysql> ALTER TABLE blog_table RENAME to Blog_table_test;

29. MySQL and SQL Injection

SQL injection, by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually achieves a malicious SQL command that deceives the server. We should never trust the user input, must be determined that the user input data is not safe, all need to the user input data filtering processing. For example, user input is not filtered in the following example:

$name = "Qadir"; DELETE from users; ";
Mysqli_query ($conn, "select * from users WHERE name= ' {$name} '");

In the above injection statement, we did not filter the $name variables, we inserted the SQL statements we did not need, and all the data in the users table would be deleted. Mysqli_query () in PHP is not allowed to execute multiple SQL statements, but SQLite and PostgreSQL can execute multiple SQL statements at the same time, so we need to rigorously validate the data of these users.

To prevent SQL injection, we need to note: 1, never trust the user's input. The user's input can be verified by regular expressions, or by limiting the length, by converting the single quotation mark and the double "-". 2. Never use dynamically assembled SQL, you can use parameterized SQL or directly use stored procedures for data query access. 3. Never use a database connection with administrator rights, and use a separate limited database connection for each app. 4. Do not store confidential information directly, encrypt or hash out passwords and sensitive information. 5, the application of the exception information should give as few hints as possible, preferably using a custom error message to the original error message packaging. 6, SQL injection detection method generally take the aid software or website platform to detect, software generally use SQL injection detection Tool Jsky, website platform has billion think website security platform detection tools. Mdcsoft scan and so on. The use of mdcsoft-ips can effectively protect against SQL injection, XSS attacks and so on.

1. Prevent SQL injection: In scripting languages, such as Perl and PHP, you can escape the data entered by the user to prevent SQL injection. PHP's MySQL extension provides the mysqli_real_escape_string () function to escape special input characters. Cases:

if (GET_MAGIC_QUOTES_GPC ())
{
  $name = Stripslashes ($name);
}
$name = mysqli_real_escape_string ($conn, $name);
Mysqli_query ($conn, "select * from users WHERE name= ' {$name} '");

2, like statements in the injection: IKE query, if the user entered the value of "_" and "%", then the user would have just wanted to query "Abcd_", query results have "abcd_", "ABCDE", "ABCDF" and so on In PHP script we can use the addcslashes () function to handle the above situation, for example:

$sub = Addcslashes (mysqli_real_escape_string ($conn, "%something_"), "%_");
$sub = = \%something\_
Mysqli_query ($conn, "select * from messages WHERE subject like ' {$sub}% '");

The Addcslashes () function adds a backslash before the specified character. It uses the syntax format: addcslashes (string,characters). Two parameters in string: Required. Specifies the string to check. Characters: Optional. Specifies the range of characters or characters affected by addcslashes ().

30. MySQL Export Data

1, MySQL can use the Select ... into outfile statement to simply export the data to a text file and set the data output to the specified format through the command options, the following instance is exported in CSV format:

Mysql> SELECT * from passwd to OUTFILE '/tmp/tutorials.txt '
    --TERMINATED by ', ' enclosed by ' "'
    -LINES TERMINATED by ' \ r \ n ';

SELECT ... The into OUTFILE statement has the following properties: 1. Write data from one database to a file, using Select ... Into OUTFILE, read the file back to the database, using load DATA INFILE. 2. Elect ... Into OUTFILE ' file_name ' form select to write the selected line to a file. The file is created on the server host, so you must have file permissions to use this syntax. 3. The output cannot be an existing file. Prevents file data from being tampered with. 4, need to have a login server account to retrieve files. otherwise SELECT ... Into OUTFILE does not play any role. 5. In Unix, the file is readable after it is created, and the permissions are owned by the MySQL server.

2. Export the table as raw data: mysqldump is a utility that MySQL uses to store databases. It primarily produces a SQL script that contains the commands necessary to recreate the database from scratch. Exporting data using mysqldump requires the--TAB option to specify the directory specified by the export file, which must be writable. For example, export the data table Blog_table_test to the/catalogname directory:

$ mysqldump-u root-p--no-create-info \
            --tab=/catalogname Blog Blog_table_test
Password ******

3, export the SQL format data to the specified file, the following example:

$ mysqldump-u root-p Blog blog_table_test > Test.txt
Password ******

If you need to export data for an entire database, you can use the following command:

$ mysqldump-u root-p Blog > Database_test.txt
Password ******

If you need to back up all databases, you can use the following command:

$ mysqldump-u root-p--all-databases > Database_test.txt
Password ******

4. Copy data tables and databases to other hosts: If you need to copy the data to a different MySQL server, you can specify the database name and the data table in the Mysqldump command, and if you fully back up the database, you do not need to use a specific table name.

If you need to import a backed-up database into a MySQL server, you can use the following command (you need to confirm that the database was created):

$ mysql-u root-p database_name < test.txt
Password * * * *

You can also use the following command to import the exported data directly to a remote server, but make sure that the two servers are interlinked and can be accessed from one another:

$ mysqldump-u root-p database_name \
       | MYSQL-H other-host.com database_name

31. MySQL Import Data

1. Import data using LOAD data, the following instance will read the file Test.txt from the current directory and insert the data from the file into the Blog_table_test table in the current database:

mysql> LOAD DATA LOCAL INFILE ' test.txt ' into TABLE blog_table_test;

If you specify a local keyword, it indicates that the file is read by path from the client host. If not specified, the file reads the file on the server by path.

We can explicitly indicate the delimiter and end-of-line tags of the column values in the load data statement, but the default tags are locators and line breaks

The syntax for the fields and LINES clauses of the two commands is the same. Two clauses are optional, but if two are specified at the same time, the fields clause must appear before the LINES clause.

If the user specifies a fields clause, its clauses (TERMINATED by, [optionally] enclosed by and escaped by) are also optional, but the user must specify at least one of them.

LOAD data is inserted by default in the order of the columns in the data file, and if the columns in the data file are inconsistent with the columns in the inserted table, you need to specify the order of the columns. For example, the column order in the data file is A,b,c, but the column order of the inserted table is b,c,a, the data import syntax is as follows:

mysql> LOAD DATA LOCAL INFILE ' test.txt '
    Into TABLE Blog_table_test (b, C, a);

2. Import data using Mysqlimport: The Mysqlimport client provides a command-line interface for the load data INFILEQL statement. Most options for mysqlimport correspond directly to the load DATA infile clause.

To import data from the file Test.txt into the Blog_table_test data table, you can use the following command:

$ mysqlimport-u root-p--local database_name test.txt
Password * * * *

The Ysqlimport command can specify options for setting the specified format, and the command statement format is as follows:

$ mysqlimport-u root-p--local--fields-terminated-by= ":" \
   --lines-terminated-by= "\ r \ n"  database_name test.txt
Password * * * *

Use the--columns option in the Mysqlimport statement to set the order of the columns:

$ mysqlimport-u root-p--local--columns=b,c,a \
    database_name Test.txt
Password * * * *

32. Precautions

1, MySQL under Windows database name, table name, column name, alias are not case-sensitive.

2. Numeric type the number following the parentheses is only the width and is not related to the storage range.

3. The ORDER by,group by,distinct field needs to be added after the index.

4, do not use foreign keys, high concurrency prone to deadlock and other problems

To learn more about MySQL, click: Learn MySQL (ON)

affirm

The author is also learning the front-end soon, editing the purpose of this article, on the one hand is to share and learn with everyone, on the other hand is to better understand the content of it. If there are any mistakes in the text, we welcome you to correct the criticism, and we would like to make progress together in the debate of the problem. The more intense, the deeper.

Learn MySQL (next)