Mysql 学习之基础操作

2018-06-17 23:18:57来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

一、表复制

1、复制表结构    将表hello的结构复制一份为表hello3

2、复制数据

a、如果两张表的结构一样且你要复制所有列的数据

mysql> insert into hello3 select * from hello;

b、两张表结可能不一样且你只要复制部分列的数据

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

二、索引

 1、create 创建索引(只能创建普通索引和唯一索引)

  创建普通索引:mysql> create index in_name on hello(name); 给表hello中的name列创建名为 in_name的索引。

  创建唯一索引:mysql> create unique index un_name on hello(name);   给表hello中的name列创建唯一索引名为 un_name的索引。

  查看索引:mysql> show index from hello;   查看表 hello的索引。

  删除索引:mysql> drop index in_name on hello; 删除hello表中名为in_name的索引。

2、alter 创建索引(创建索引的通用方式)

  创建普通索引:mysql> alter table hello add index in_name(name); 给表hello中的name列创建名为 in_name的索引。

  删除(普通/唯一)索引:mysql> alter table hello drop index in_name; 删除表hello中名为in_name的普通索引。

  删除自增:mysql> alter table hello modify id int unsigned not null;删除表hello中 id列(int类型) 的自增特性。

  删除主键索引:mysql> alter table hello drop PRIMARY KEY;

  创建唯一索引:mysql> alter table hello add unique(name); 给hello表的 name创建唯一索引 索引名是默认的。

  创建主键索引:mysql> alter table hello add primary key(id); 给hello表的id字段创建主键索引。

  将主键索引设置为自增:mysql> alter table hello modify id int unsigned not null auto_increment;将hello表中的主键id列设置为自增。

三、视图  

     主表数据的变化,视图会时时做相应的变化。如果视图所依赖的表出现错误(被删除)则视图也会发生错误。

  创建视图:mysql> create view v_hello as select * from hello where id >5;

  删除视图:mysql> drop view v_hello;

  查看视图的创建过程:mysql> show create view v_hello; 查看视图v_hello 的创建过程。

四、内置函数

字符函数

  1、CONCAT(str1,str2,....)  字符链接函数

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

  2、LCASE(str1) 转为小写

    mysql> select lcase("MYSQL");

  3、UCASE(str1) 转大写

    mysql> select UCASE("Mysql");

  4、LENGTH(str) str的长度

    mysql> select length('mysql');

  5、LTRIM(Str) 去除前段空格

    mysql> select LTRIM(' mysql');

  6、RTRIM(str) 去除后端空格

    mysql> select RTRIM(' mysql  ');

  7、REPEAT(str,count) 重复count次

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

  8、REPLACE(str,search_str,replcae_str) 将str中的search_str 替换为replac_str

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

  9、SUBSTRING(str,postion,length) 从str的postion开始取length个字符

    mysql> select substring('mysql',1,2);  从1开始

  10、SPACE(count) 生成count个空格

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

数学函数

1、BIN(decimal number):   将十进制转二进制

2、CEILING(number) 向上取整    mysql> select ceiling(10.12);   结果: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('name',@i));
        SET @i=@i+1;
        END WHILE;
    end;
<!-- 查看存储-->
SHOW PROCEDURE STATUS;
<!-- 查看hello1()存储过程-->
show CREATE PROCEDURE hello1;
<!-- 执行存储过程-->
CALL hello1;

八、触发器

参考:http://www.cnblogs.com/jalja/p/4635087.html(MySql触发器)

九、重排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

 

十、批量操作

1、批量更新    ON DUPLICATE KEY UPDATE:MYSQL主键存在则更新,不存在则插入的解决方案

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(32) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_info
-- ----------------------------
INSERT INTO `user_info` VALUES ('1', '7', '1', '2018-01-19 21:59:15', '2018-01-19 21:59:15');
INSERT INTO `user_info` VALUES ('2', '9', '1', '2018-01-19 21:59:15', '2018-01-19 21:59:15');

demo:

INSERT INTO user_info (
    id,
    password,
    name,
    update_time
)
VALUES
    (1, "A", "zs",NOW()),
    (2, "B", "ls",NOW()) ON DUPLICATE KEY UPDATE 
password =VALUES(password),
name =VALUES(name),
update_time=VALUES(update_time)

结果:

 

 2、批量插入:

INSERT INTO `user_info` (`password`, `name`, `create_time`) 
    VALUES ('111111', 'ww', NOW()), ('222222', 'mz', NOW()); 

 

 

 

注意:SQL语句是有长度限制,在进行数据合并在同一SQL中务必不能超过SQL长度限制,通过max_allowed_packet配置可以修改,默认是1M。所以在做批量开发中sql的长度一定要控制。

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:《MySQL必知必会》学习笔记

下一篇:闰秒导致MySQL服务器的CPU sys过高