一步一步mysql(三) 之常用操作

1.显示2到4条数据信息

列出mysql.user的user ,host两列数据，而且只显示2到4条数据

mysql> select  user,host from mysql.user limit 2,3; 

注：在mysql中不支持top操作，用limit代替，limit 2,3 是表示“从2条开始的连续3条记录”，limit 10 就相当于top 10了，表示开始的10条记录

2.创建、删除、查看表

CREATE TABLE `cli_log_info` (
`LOG_ID` bigint NOT NULL, #the first symbol is backquote ;
`USER_ID` int NOT NULL,
`FILE_ID` varchar(50) NOT NULL,
`TYPE` tinyint NULL,
`CONTENT` varchar(255) NULL,
`WTIIME` time NULL,
`MARK_PORT` varchar(50) NULL,
PRIMARY KEY (`LOG_ID`) 
);

注：`LOG_ID` 是用的反引号，而不单引号！  当然也可以不用反引号

mysql> show create cli_log_info   # 查看表创建信息

mysql> desc cli_log_info   #查看表结构信息

mysql> drop table cli_log_info   # 删除表

3.修改表属性

ALTER TABLE `cli_log_info` ADD CONSTRAINT `LOG_RESOURCE` FOREIGN KEY (`FILE_ID`) REFERENCES `cli_resource_info` (`FILE_ID`);

4.创建与删除数据库

mysql> show datatbases;

mysql> create database drm_test ;

mysql> use drm_test   #后面不用加分号即可

mysql> drop database drm_test ;

5.在插入数据的时候，自动修改数据

mysql> desc test;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| passwd | varchar(255) | YES  |     | NULL    |                |
| name   | varchar(255) | YES  |     | NULL    |                |
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

test2.sql

delimiter |
create trigger test_proc3 after insert
on test.test for each row
begin
set new.passwd="raotest" ;
end;
|
delimiter ;

mysql> source ~/test2.sql ;
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

应该将after改成 before

参考：http://qdjalone.blog.51cto.com/1222376/885501

6.显示表的状态信息

mysql> show table status ;

mysql>show table status like 'table_name' ;

mysql> show table status \G

注意：“\G”这个标志选项哦，它可以竖着显示信息如：

mysql> show table status like "res_usr_level" \G 
*************************** 1. row ***************************
           Name: res_usr_level
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
   ...................

，这个选项也能用到类似于desc等显示信息语句上；

7.修改表的存储引擎

一般来说默认的是MYISAM,可以用show engines 命令查看存储引擎信息。

如果想把它修改成InnoDB,如：

创建的时候修改

create table user(
  id int not null auto_increment,
  username char(20) not null,
  sex char(2),
  primary key(id)
) engine=InnoDB;

在创建表后再修改：

mysql> alter table table_name engine=InnoDB

8.在shell中执行mysql语句

建立一个shell脚本sql.sh：

#!/bin/bash
HOSTNAME="10.33.28.231"
PORT="3306"
USERNAME="drmtest"
PASSWD="test"

DBNAME="drm_test"
TABLENAME="test"


sql="select *from ${TABLENAME}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWD} ${DBNAME} -e "${sql}";

上面的示例是打印出drm_test.test表中所有的数据

$sh sql.sh

那么其它的几种操作也与之类似

9.查看存储引擎

下面是两种方法：

show table status from db_name where name='table_name';
show create table table_name;

10.取出表的最新的5条的数据

应该用一个字段来存放最后更新时间，然后根据这个来取记录

我可以直接利用已有的ID字段吗?
select * from tbl order by id desc limit 5

不行，你是说更新，不是新插入，不能用ID,因为可能一个ID很小，但是是你新更新的。UPDATE了的
另外，我的语法可能不对，你看楼上的吧，我写的top 5是mssqlserver里的写法

如果有更新过的，就得有一个时间字段了
select * from tbl order by 时间字段 desc limit 5

各位，其实我之所以这么做主要想提高数据库访问速度。因为我的网站首页要分别显示5个数据表中的数据。我想使SQL语句尽量简单些。

那你就在表中加一个修改时间字段
select * from tbl order by 修改时间 desc limit 5

数据库是没有可能的,你可以把5个表都搜出5条最新数据,当然要有时间字段,在比较一下就可以了

11.InnoDB 事务回滚

mysql> set AUTOCOMMIT=0;
Query OK, 0 rows affected (0.01 sec)
mysql> select *from test2;
+------+--------+
| name | passwd |
+------+--------+
| 123  |        |
| 125  |        |
+------+--------+
2 rows in set (0.00 sec)

mysql> insert into test2 values("001","");
Query OK, 1 row affected (0.00 sec)

mysql> select *from test2;
+------+--------+
| name | passwd |
+------+--------+
| 001  |        |
| 123  |        |
| 125  |        |
+------+--------+
3 rows in set (0.00 sec)

mysql> rollback ;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test2;
+------+--------+
| name | passwd |
+------+--------+
| 123  |        |
| 125  |        |
+------+--------+
2 rows in set (0.00 sec)

mysql> insert into test2 values("001","");
Query OK, 1 row affected (0.00 sec)

mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback
    -> 
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test2;
+------+--------+
| name | passwd |
+------+--------+
| 001  |        |
| 123  |        |
| 125  |        |
+------+--------+
3 rows in set (0.00 sec)

mysql> 

12.mysq修改列名

示例：

alter table user_core change oldname  newname varchar(255);