Basic Mysql learning and mysql Learning

Basic Mysql learning and mysql Learning
I. Table Replication

1. Copy the table structure and copy the hello table structure as hello3.

2. Copy Data

A. If the two tables have the same structure and you want to copy the data of all columns

Mysql> insert into hello3 select * from hello;

B. The two tables may have different knots. You only need to copy the data of some columns.

Mysql> insert into hello3 (name, sex, degree) select name, sex, degree from hello;

Ii. Indexing

1. create an index (only common and unique indexes can be created)

Create a common index: mysql> create index in_name on hello (name); create an index named in_name for the name column in table hello.

Create a unique index: mysql> create unique index un_name on hello (name); create a unique index named un_name for the name column in table hello.

View the index: mysql> show index from hello; view the index of table hello.

Delete index: mysql> drop index in_name on hello; Delete the index named in_name in the hello table.

2. Create an index using alter (the general method for creating an index)

Create a common index: mysql> alter table hello add index in_name (name); Create an index named in_name for the name column in table hello.

Delete (normal/unique) indexes: mysql> alter table hello drop index in_name; Delete the normal index named in_name in table hello.

Delete auto-increment: mysql> alter table hello modify id int unsigned not null; Delete the auto-increment feature of the id column (int type) in table hello.

Delete the primary key index: mysql> alter table hello drop primary key;

Create a unique index: mysql> alter table hello add unique (name); the default index name is used to create a unique index for the name of the hello table.

Create a primary key index: mysql> alter table hello add primary key (id); Create a primary key index for the id field of the hello table.

Set the primary key index to auto-increment: mysql> alter table hello modify id int unsigned not null auto_increment; set the primary key id column in the hello table to auto-increment.

3. View

When the data in the master table changes, the view changes from time to time. If the table on which the view depends has an error (Deleted), the view will also have an error.

Create a view: mysql> create view v_hello as select * from hello where id> 5;

Delete view: mysql> drop view v_hello;

View the creation process of the view: mysql> show create view v_hello; view the creation process of the view v_hello.

Iv. built-in functions

Character Functions

1. CONCAT (str1, str2,...) character link function

Mysql> select concat ('A', 'B ');

2. Convert LCASE (str1) to lowercase

Mysql> select lcase ("MYSQL ");

3. Convert UCASE (str1) to uppercase

Mysql> select UCASE ("Mysql ");

4. LENGTH (str) str LENGTH

Mysql> select length ('mysql ');

5. LTRIM (Str) removes leading Spaces

Mysql> select LTRIM ('mysql ');

6. Remove backend spaces in RTRIM (str)

Mysql> select RTRIM ('mysql ');

7. REPEAT (str, count) repeated count times

Mysql> select repeat ('mysql', 2 );

8. REPLACE (str, search_str, replcae_str) to replac_str

Mysql> select REPLACE ('mysql', 'M', 'M ');

9. SUBSTRING (str, postion, length) the length starts from the postion of str.

Mysql> select substring ('mysql', 1, 2); Starting from 1

10. SPACE (count) generates count Spaces

Mysql> select concat (space (3), 'mysql ');

Mathematical functions

1. BIN (decimal number): Convert decimal to binary.

2. CEILING (number) rounded up to mysql> select ceiling (10.12). Result: 11

3. FLOOR (number) rounded down to mysql> select ceiling (10.12); Result: 10

4. MAX (column) to obtain the maximum column

5. MIN (column) to obtain the smallest column

6. SQRT (num) Square

7. RAND () returns a random value between 0 and 1.

Date Functions

1. CURDATE () returns the current date in the format of yyyy-MM-dd.

2. CURTIME () returns the schedule time 12:11:56

3. NOW () returns the current time 21:12:34

4. UNIX_TIMESTAMP (date) returns the timestamp of the current date.

5. FROM_UNIXTIME () returns the date value of the UNIX Timestamp

6. WEEK (date) returns the WEEK number of the year from date.

7. YEAR (data) returns the YEAR of the current date.

8. DATEDIFF (expr1, expr2) returns the number of days between expr1 and expr2.

5. Pre-processing statements

 

No variable:

Create a pre-processing statement: mysql> prepare stmt1 from 'select * from hello where id> 5'; create a pre-processing statement named stmt1

Execute the pre-processing statement: mysql> execute stmt1; execute the stmt1 pre-processing statement

With variables:

Create a preprocessing statement with parameters: mysql> prepare stmt1 from 'select * from hello where id>? '

Set the variable: mysql> set @ I = 6;

Run the pre-processing statement: mysql> execute stmt2 using @ I;

 

Delete the pre-processing statement: mysql> drop prepare stmt2; # mysql> deallocate prepare stmt2;

 

Note: When EXECUTE is executed every time, you must execute deallocate prepare... Statement to release all database resources (such as cursors) used during execution ).
In addition, if a session has too many preprocessing statements, it may reach the upper limit of max_prepared_stmt_count.

Vi. Transaction Processing

Mysql automatically commits transactions by default. Set the ENGINE of the database or table to InnoDB during mysql transaction processing.

Set the table storage engine to INNODB: mysql> alter table hello engine = innodb;

Set non-automatic mysql submission: mysql> set autocommit = 0;

Transaction generation: mysql> delete from hello where id> 7;

Transaction rollback: mysql> rollback;

Transaction commit: mysql> commit;

Restore Point in the transaction:

Create a transaction: mysql> insert into hello (sex, degree, name) values (1, 12312.32, 'hhh ');

Set the Restore point for the transaction: mysql> savepoint p1;

Rollback to the specified restore point: mysql> rollback to p1; at this time, the transaction is restored to p1, that is, the p2 and p3.. recovery points after p1 will become invalid.

Roll back to the original restore point: mysql> rollback;

VII. Stored Procedure

<! -- CREATE Stored PROCEDURE hello1 () --> create procedure hello1 () begin set @ I = 0; WHILE @ I <100 DO insert INTO hello (sex, degree, name) VALUES (1, @ I, CONCAT ('name', @ I); SET @ I = @ I + 1; END WHILE; end; <! -- View storage --> show procedure status; <! -- View the hello1 () stored PROCEDURE --> show create procedure hello1; <! -- Execute the stored procedure --> CALL hello1;

8. triggers

See: http://www.cnblogs.com/jalja/p/4635087.html (MySql triggers)

9. rearranging the auto_increment Value

In mysql, if we set the primary key id to the primary key auto-increment policy, how can we clear the table and restore the id value of the auto-increment column.

Method 1: Use truncate table tableName. This method restores the auto_increment value while clearing the table.

Method 2:

1. mysql> delete from hello3; clear the table (this method is inefficient)

2. mysql> alter table hello3 auto_increment = 1; the starting value for restoring auto_increment is 1.