数据库操作入门

浏览数：23 / 时间：2015年06月12日

mysql 版本:5.5  InnoDB 引擎         mysql5.1 都是myIAM 引擎
简介：sql结构话查询语言 :是一种对关系数据库中的数据进行定义和操作的语言方法，是大多数关系型数据库支持的工业标准；是一种数据库查询和程序设计语言，用于存取、查询、跟新、管理数据库系统。
sql 分类：
*DQL 数据查询语言；作用表格获取数据，确定数据怎么在应用程序给出；关键字select，其它DQL常用的保留字有WHERE ,ORDER, GROUP BY ,和HAVING;
DML 数据库操作语言，其语句包括动词INSERT,UPDATE和DELETE,也称为动作查询语言。
TPL 事物处理语言TPL（银行数据库需要）
DCL 数据控制语言        grant
DDL 数据定义语言       CREATE TABLE 或DROP TABLE
*CCL 指针控制语言      DECLARE CURSOR

1、使用帮助：

范例：help drop database

#这里可以没有；号

2、查看数据库当前的用户：
select user();

#（）表示系统的函数；

3、查看系统中所有的用户：
select host,user from mysql.user;

4、查看数据库的版本：

select version();

5、显示数据库字符集：

SHOW CHARACTER SET;

6、设定字符集：

set names gbk;

7、查看当前的时间：
select now();

8、显示所有数据库：

show databases;

9、显示当前数据库：

select database();

       #显示某一个可以使用like或where：
       范例:show databases where "name";
         show databases like "name";

show databases like ‘oldboy%‘; %所有

10、创建数据库：

create database name;

8、查看建库语句:

show create database name;

show create database name\G;

8、删除数据库：

drop database name;

9、连接切换数据库：

use name;

10、查看当前数据库的表：
show tables;

11、查看指定库中的表：
show tables from name;

#显示字符集，默认是latin1，支持中文没有问题。网页、程序的字符集要和数据库的字符集相匹配，包括客户端、服务端、java程序；

14、建表：（create table关键字）

create table <表名>（ <字段名1><类型1>，<字段名n><类型n> ）；

人工写法建表语句范例：

            create table student(
        id int(4) not null,
        name char(20) not null,
        age tinyint(2)  NOT NULL default ‘0‘,
        dept varchar(16)  default NULL
        );

mysql生成建表语法范例：

        student | CREATE TABLE `student` (
      `id` int(4) NOT NULL,                                               #学号列，数字类型，长度不为空值；
      `name` char(20) NOT NULL,                                   #名字列，定义字符类型，长度20，不为空值；
      `age` tinyint(2) NOT NULL DEFAULT ‘0‘,                #年龄列，很小的数字类型，长度为2，不为空；
      `dept` varchar(16) DEFAULT NULL                         #系别列，变长字符类型，长度为16，默认为空；
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1     #引擎和字符集，默认引擎=InnoDB，字符集，继承库的latin1

常用的数值类型：INT 正常大小整数类型（正常大小整数类型）；CHAR第一字符串类型（存储是，总是用空格填满到指定的长度）；VARCHAR型：变长字符串类型；

15、查看建结构：

desc student;
show columns from student;

16、查看以建表语句：

show create table student\G;

#发送命令给mysqlserver垂直显示；

17、删除表：

drop tables student;

delete from test where id=2;

#指定条件删除；

18、索引：

主键索引：内容唯一，每一个表只能有一个主键索引，查询数据库按照主键查询是最快的，一般数字ID列,唯一区别表的记录这个字段；（MUL普通索引。PRI主键索引）

建表时，可以增加索引主键，都要在表对应的列上创建，可以是多列也可以是多列，：

create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) NOT NULL default ‘0‘,
dept varchar(16) default NULL,
primary key(id),
KEY index_name (name)
);

#primary key(id)表示对id列创建主键；KEY index_name (name)name列创建普通引；AUTO_INCREMENT表示递增；

建表后通过alter命令添加主键索引（主键列不能重复创建，必须先删除原先的配置）

删除主键：alter table student drop index index_name;

---------

添加主键：

建表语句：

create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) NOT NULL default ‘0‘,
dept varchar(16) default NULL,
KEY index_name (name)
);

添加语句：alter table student change id id int primary key auto_increment;

查看：desc student;

普通索引分为唯一索引（表的类希望是唯一值的时候，但不是主键。对表内容的约束条件）和不唯一索引；为的是查询更快。

注查看上述： KEY index_name (name)

帮助信息：index_col_name:
col_name [(length)] [ASC | DESC]

删除索引:alter table student drop index index_name;

添加普通索引:alter table student add index index_name(name);

其它列上创建索引： alter table student add index index_age(age);

当遇到表中较大的列时，列内容前n个字符所在所有内容中已经接近唯一时，这时可以对前n个字符创建索引，而无需对整个列建立索引，这样可以节省创建索引占用的系统空间，以及降低读取和跟新维护索引所消耗的系统资源。

对字段的前n个字符创建普通索引语法：

create index index_dept on student(dept(8));

#index_dept可选；on student 在哪个表；dept(8)在那一列前八个字符创建索引；

查看：desc student;

查看索引：

show index from student\G;

#Index_type: BTREE 索引类型，默认BTREE； Sub_part: 8 长度为8；Column_name: dept dept列；

Key_name: index_dept索引名字；Non_unique: 1 不是唯一索引（0是唯一，）；Key_name: PRIMARY （主键索引）

为表的多个字段创建联合索引：

如果查询的条件是多列是，可以为多个查询的列创建联合索引，甚至，可以为多列的前n个字符列创建联合索引： create index ind_name_dept on student(name,dept);

查看： show index from student\G;

注意： Key_name: ind_name_dept
Seq_in_index: 2

删除索引：drop index index_name_dept on student; （也可以alter）

对一个表多个列的前几个字符创建索引：

create index int_name_dept on student(name(8),dept(10));

提示：按条件列查询数据时，联合索引是有前缀生效特性的。

index(a,b,c)仅a，ab，abc三个查询条件列可以走索引。b,bc,ac,c等无法使用索引了，尽量把最常用作为查询条件的列，放在第一位；

创建唯一索引（非主键）对表内容的约束条件，不可以重复，

删除: drop index index_name on student ;

创建索引： create unique index uni_inde_name on student(name);

查看：desc student; （观察UNI）

附：索引虽然可以加快查询速度，但是不要把所有的列都建立索引，因为索引不但占用系统空间，而且更新数据库是还需要维护索引数据的，索引是双刃剑，并不是越多越好；例如：数十到几百行的小表上无需建立索引，跟新频繁，读取比较少的表要少建立索引；索引一定要在where passwdord=..后的条件列上，而不是select后的选择数据的列上，尽量选择在唯一值多的大表上建立索引。最后注意：索引占用系统空间，选择性的添加；维护索引，消耗系统资源；

创建主键索引：alter table student change id id int primary key auto_increment;

删除主键索引：alter table student drop primary key;

创建普通索引：alter table student add index index_name(name（8）);

#根据列的前n个字符创建索引： create index ind_name_dept on student(dept（8）);

#根据多个列创建联合索引： create index ind_name_dept on student(name,dept);

#根据多个列的前n个字符创建联合索引： create index ind_name_dept on student(name（8）,dept（10）);

删除普通索引：alter table student drop index index_dept;

或： drop index index_dept on student;

创建唯一索引：create unique index uni_inde_name on student(name);

基本索引：

1、要在表的列（字段）上创建索引。
2、索引会加快查询速度，但是会影响跟新速度，因为要维护索引数据；
3、索引并不是越多越好，要在频繁查询的where后的条件上创建索引。
4、小表或重复值很多的列上可以不建立索引，要在大表以及重复值少的条件上创建索引。

19、数据插入:

(1)命令语法：insert into <表名> [(<字段名>[,..<字段名n>])]values（值1）[（值n）]

(2)新建test测试表：

CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ;

（3）按规矩指定所有列名，并且每列都插入值：insert into test(id,name) values(1,‘kong‘);

由于id列为自增，所以只在name插入值： insert into test(name) values(‘kong‘);

不指定列插入：insert into test values(3,‘kongkong‘); #字符串使用引号；

                    批量插入：insert into test values(4,‘kongkong‘),(5,‘kong2‘);
                    #在没有指定列的情况下，给值要指定值，字符串需加引号；
                    删除数据后：delete from test;批量插入：INSERT INTO `test` VALUES (1,‘oldboy‘),(2,‘oldgirl‘),(3,‘inca‘),(4,‘zuma‘);
                    查看：select * from test;
                    #使用insert 批量插入，提高效率，不要循环插入；

20、查询数据：

命令语法：select <字段去，字段2，..>from<表名>where<表达式> #关键字大小写可以随便大小写；
查看tset 中数据：select * from test;
指定条件查询：select * from test limit 2; 或者：select * from test limit 0,2;
#查询前两个
select id,name from test where id=1;
select id,name from test where id="kong";
#字符串查询加引号；不用*查询数据；

多个条件查询： select * from test where id=3 and name="inca"; 或者or
                              select * from test where id<3 and id >1;
                                select * from test order by id; #默认升序，注意指定列；
                                select * from test order by id asc; #升序
                                select * from test order by id desc; #降序

多表查询暂时不会；

21、explain 查询select 查询语句的执行计划：
         查看test表结构： desc test;
         explain查看：explain select * from test where name="zuma"\G;
        #select_type#简单表类型；type:类型；possible_keys:可能走的索引，NULL为没有；key_len索引长度； rows扫描行数
         创建索引：create index index_name on test(name);
        查看索引：explain select * from test where name="zuma"\G;

22、update修改表中的数据：
        命令语法：update 表名 set 字段=新值，.. where条件（where此处必须指定条件，否则完蛋）
        范例：update test set   name="kong"   where id=3;
        查看：select * from test ;
        如果不指定where修改数据 update test set   name="kong" ; 后果将全部修改掉！
        为了防止没有指定where造成灭顶之灾登录时可以使用-U ，登录，利用别名的功能：alias mysql="mysql -U"
       加入到 vi /etc/profile，可以防止这个入口；

23、truncate清空表中所有内容
         范例：truncate table test;
         #truncate相当于直接清空mysql 物理文件， delete 逻辑删除，按行删；
         程序里不使用delete ，用update 替代；（状态值标记）

24、增删改表的字段：

命令语法：alter table 表名 add 字段类型其它

范例：增加性别列sex

alter table test add sex char(4);

1、增加1个字段：
ALTER TABLE `etiantian` ADD `FIRSTPHOTO_URL` varchar(255) default NULL COMMENT ‘第一张图片URL‘
2、增2个字段：
ALTER TABLE `basic` ADD `adhtml_top` varchar(1024) default NULL COMMENT ‘顶部广告html‘ ,
                                           ADD `adhtml_right` varchar(1024) default NULL COMMENT ‘右侧广告html‘ ;
3、改变字段：
alter table ett_ambiguity change ambiguity_state ambiguity_state tinyint comment ‘状态，默认1=正常，0=失效‘;
ALTER TABLE `ett_photo`
MODIFY COLUMN `PHOTO_DESCRIPTION` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘描述‘ AFTER PHOTO_TITLE`;
4、修改字段类型：
mysql> alter table test modify age char(4) after name;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
5、修改字段名称
mysql> alter table test change age oldboyage char(4) after name;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
#以上未操作；