Learn more about the basic operations of Mysql

1. Table copy

1. Copy the table structure Copy the structure of table hello to table hello3

2. Copy data

a. If the structure of the two tables is the same and you want to copy all columns Data

mysql> insert into hello3 select * from hello;

b. The two table results may be different and you only need to copy the data of some columns

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

2.Index

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

Create an ordinary index: mysql> create index in_name on hello(name); to the table The name column in hello creates an index named in_name.

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 the hello table.

View 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. Alter creates an index (a common way to create an index)

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

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

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); Create a unique index index name for the name of the hello table is the default.

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

If the main table data changes, the view will make corresponding changes from time to time. If the table that the view depends on has an error (is deleted) then the view will also have an error.

Create 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.

4. Built-in functions

Character functions

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

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

2. LCASE(str1) Convert to lower case

mysql> ; select lcase("MYSQL");

3. UCASE(str1) Convert to uppercase

mysql> select UCASE("Mysql");

4. LENGTH(str ) The length of str

Mysql> select length('mysql');

5. LTRIM(Str) removes the preceding spaces

mysql> select LTRIM(' mysql') ;

6. RTRIM(str) remove back-end spaces

mysql> select RTRIM(' mysql ');

7. REPEAT(str,count) repeat count times

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

　8. REPLACE(str,search_str,replcae_str) Replace search_str in str with replac_str

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

　9. SUBSTRING(str,postion,length) Take length characters starting 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) Round up mysql> select ceiling(10.12); Result: 11

3、FLOOR(number) 向下取整       mysql> select ceiling(10.12);   结果：10

4、MAX(column) 获取 最大列

5、MIN(column) 获取最小列

6、SQRT(num) 开平方

7、RAND() 返回0-1之间的随机数值  

日期函数

1、CURDATE() 返回当前日期格式 yyyy-MM-dd

2、CURTIME()返回档期时间 12:11:56

3、NOW()返回当前时间 2017-05-12 21:12:34

4、UNIX_TIMESTAMP(date) 返回当前date的时间戳

5、FROM_UNIXTIME()返回UNIX时间戳的日期值

6、WEEK(date)返回当前时间date为一年中的第几周

7、YEAR(data)返回当前时间date的年份

8、DATEDIFF(expr1,expr2) 返回expr1与expr2之间的天数

五、预处理语句

无变量：

创建预处理语句：mysql> prepare stmt1 from 'select * from hello where id>5';创建一个名为stmt1的预处理语句

执行预处理语句：mysql> execute stmt1;执行stmt1预处理语句

带变量：

创建带参数的预处理语句：mysql> prepare stmt1 from 'select * from hello where id>?'

设置变量：mysql> set @i=6;

执行预处理语句：mysql> execute stmt2 using @i;

删除预处理语句：mysql> drop prepare stmt2;    #mysql> DEALLOCATE PREPARE stmt2;  

注意：每一次执行完EXECUTE时，养成好习惯，须执行DEALLOCATE PREPARE … 语句，这样可以释放执行中使用的所有数据库资源（如游标）。
不仅如此，如果一个session的预处理语句过多，可能会达到max_prepared_stmt_count的上限值。

六、事务处理

mysql默认事务是自动提交的。在做mysql事务处理时请将数据库或者表的ENGINE 设置为InnoDB

将表的存储引擎设置为INNODB：mysql> alter table hello engine=innodb;

设置mysql为非自动提交：mysql> set autocommit=0;

产生事务：mysql> delete from hello where id>7;

事务回滚：mysql> rollback;

事务提交：mysql> commit;

关于事务中的还原点：

创建一个事务：mysql> insert into hello (sex,degree,name) values(1,12312.32,'HHH');

对该事务设置还原点：mysql> savepoint p1;

回滚到指定的还原点：mysql> rollback to p1; 此时事务恢复到p1,也就是p1之后的事务p2 ,p3..这些还原点将失效。

回滚到原始的还原点：mysql> rollback;

七、存储过程

<!-- 创建存储过程 hello1()-->
CREATE PROCEDURE hello1()
    BEGIN
        SET @i=0;
        WHILE @i<100 DO 
        insert INTO hello (sex,degree,name) VALUES(1,@i,CONCAT(&#39;name&#39;,@i));
        SET @i=@i+1;
        END WHILE;
    end;
<!-- 查看存储-->
SHOW PROCEDURE STATUS;
<!-- 查看hello1()存储过程-->
show CREATE PROCEDURE hello1;
<!-- 执行存储过程-->
CALL hello1;

八、重排auto_increment值

mysql中我们的主键id如果设置为主键自增策略，那我们如何清空表，并且恢复自增列id的值。

方式一：使用truncate table tableName； 该方式在清空表的同时恢复auto_increment 的值。

方式二:

　　1、mysql> delete from hello3;  清空表 （该方式效率较低）

　　2、mysql> alter table hello3 auto_increment=1; 恢复auto_increment 的起始值为1

The above is the detailed content of Learn more about the basic operations of Mysql. For more information, please follow other related articles on the PHP Chinese website!