数据库的基本操作

数据库的基本操作

---

数据库的操作：

#增加数据库

create database database_name;

#查看数据库

show databases;

use database_name;

#删除数据库

drop database database_name;

引擎：

#查看存储引擎

show engines;

表的操作：

#增加表（先指定在那个数据库下use database_name）

create table table_name(

id int(10),

name varchar(20),

birth date

);

#查看表定义(describe=desc)

desc table_name;

#查看表详细定义

show create table table_name;(\G)

#用查询语句查看表内的数据

select * from table_name；

#修改表名

alter table old_table_name rename new_table_name;

#增加字段

alter table tab_name add detail varchar(80) after ...;

#删除字段

alter table tab_name drop detail;

#修改字段数据类型

alter table tab_name modify detail int(10);

#修改字段位置

alter table tab_name modify detail int(10) after.../first;

#修改字段名称

alter table tab_name change detail detailer varchar(20);

#添加表的约束

create table table_name(

id int(20) primary key auto_increment,

name char(20) not null,

age int(10) default 20,

address varchar(80),

class varchar(20),

constraint uk_address unique(address)

/constraint fk_class foregin key(class) references othertable_name(class_id)

);

索引的操作：

#创建唯一索引

create table table_name(

id int(10),

name char(10),

age int(8),

unique index index_id(id)

);

#创建全文索引

create table table_name(

id int not null,

name char(30) not null,

info varchar(255),

fulltext index FullTxtIdx(info)

)engine=MyISM;

只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部（前缀）索引。

#在已经存在的表中创建索引

create index index_id

    on table_name(info);

或alter table table_name 

     add index index_id(info);

#在已经建立的表中创建多列索引

alter table table_name

    add index index_MuitiIdx(id，name，age);

由结果可以看到，id、name和age字段上已经成功建立了一个名为MultiIdx的组合索引。

组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如这里由id、name和age 3个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：（id, name, age）、（id,name）或者id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用索引查询。

在table_name表中，查询id和name字段，使用EXPLAIN语句查看索引的使用情况：

 explain select * from table_name where id=1 AND name='joe' \G

#校验索引是否创建成功

show create table table_name\G

#校验索引是否被使用#

explain

   select * from table_name where id=1\G

#查询语句

select * from table_name where id=1\G

#删除索引

drop index index_id on table_name;

视图：

#创建view数据库

create database view;     //创建view数据库

use view;          //使用view数据库

#创建视图

create view view_name    

   as select * from book;   //创建名为view_name的视图，封装了查询语句

select * from view_name;    //查询视图，将视图当表一样执行查询语句

#创建各种视图

create view view1

   asselect name

      from t_student order by id desc;  //降序排列显示

select * from view1;            

create view view2

   as select count(name)

      from t_student;     //聚合（sum、min、max、count等函数）

select * from view2;

#表内连接查询

create view view4 

   as select s.name

     from t_student as s,t_group as g

         where s.group_id=g.id and g.id=2;

select * from view4;

#表外连接

create view view5

    as select s.name 

       from t_student as s left join t_group as g on s.group_id=g.id

          where g.id=2;

select * from view5;

#记录联合（union/union all）

create view view6

   as 

      select id,name from t_student

      union all

      select id,name from t_group;

select * from view6;

#查看视图（在选择该视图数据库后use view）

#查看视图名（show tables）

show tables；

#查看视图详细信息(包括数据库里的所有表和视图)

show table status

   from view

       like "view_name" \G    //可不要

#查看视图定义信息

show create view view_name;

#查看视图设计信息

desc view_name;

#用查询语句查看视图内的数据

select * from view_name；

#利用系统表information_schema查看视图信息

use information_schema;

select * from views

   where table_name='view_name';

#修改视图(create or replace)

create or replace view view_name

   as select * from table_name;

select * from view_name;

或（alter）

alter view view_name

   as select * from table_name;

select * from view_name;

#删除视图

drop view view_name,view2;

#操作表内数据

#利用视图更新数据

如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

当视图来自多个基本表时，不允许添加、删除数据。

#增加数据

insert into view_name  (id,name) values (1,'yx'),(2,'wq');

#删除数据

delete from view_name

    where id=1;

#更新数据

update view_name

      set name='wq'

           where id=1;

使用MySQL执行update或delete的时候报错：Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

在使用mysql执行update的时候，如果不是用主键（primary key）当where语句，会报如下错误，使用主键用于where语句中正常。

修改数据库模式：

SET SQL_SAFE_UPDATES = 0;

如果想要提高数据库安全等级，可以在恢复回原有的设置，执行命令：SET SQL_SAFE_UPDATES = 1;

例子：创建视图来自两个表

create table student(

s_id int,

name varchar(40)

);

create table stu_info(

s_id int,

glass varchar(40),

addr varchar(90)

);

insert into student values(1,'wq'),(2,'yx'),(3,'wxq');

insert into stu_info values(1,'1ban','yuhuan'),(2,'2ban','linhai'),(3,'3ban','taizhou');

create view stu_glass(id,name,glass) 

  as

    select student.s_id,student.name,stu_info.glass

       from student,stu_info

         where student.s_id=stu_info.s_id;

select * from stu_glass;

触发器：

#创建触发器

create trigger tri_name

     before/after insert

           on table_name for each row

                 insert into ...

#多条执行语句

delimiter  $$

create trigger tri_name

       before/after insert

            on table_name for each row

                   begin

                       insert into...;

                    ...

                    end

                    $$

先执行上面语句，再执行最后一行(delimiter和；间有空格）

delimiter ;

#查看触发器

show triggers \G

通过系统表查看触发器

select * from triggers where trigger_name ='...' \G

#删除触发器

drop trigger tri_name;

数据操作：

#插入数据

insert into t_name (id,name) values(1,'yx');

#插入多条记录

insert into t_name values(......),(......),(.......);

#插入查询结果

insert into t_name(id,name)

        select id,name

              from t_name2;

#删除表内数据

delete from t_name;    //所有数据

delete from t_name

          where id=1;

#更新表内特定数据记录

update t_name

       set name='yx'

        where id=1;

#表内查询记录

select * from t_name；

select distinct job from t_name;   //避免重复数据查询

select name sal*12 as yearsalary from t_name;    //实现四则运算并修改字段

select concat(name,'的年薪为：',sal*12)yearsalary from t_name;   //设置固定格式显示

where id=1 or id=2 or id=3 or id=4 or id=5;

等价于where id in(1,2,3,4,5);

#模糊查询

select name from t_name

          where not name like '_A%';     //第二个字符不是A

          where name like '%A%';    //包含A的所有字符

order by id asc（desc），name desc;     //默认升序asc

limit 5;    //限制数据条数

limit 5,5;    //从第6条数据开始显示5条数据

#统计函数

#统计数量（namber、average、sumvalue、maxval、minval为字段名称）

select count(*) number from t_name; 

select count(loc) number from t_name where not loc='shanghai';  //条件记录条数

#统计平均值

select avg(comm) average from t_name (where...);

#统计求和

select sum(comm) sumvalue from t_name (where...);

#统计最大值、最小值

select max(sal) maxval, min(sal) minval from t_dept;

#统计分组

select id,group_concat(name) names from t_name group by id;   //设置固定格式

#多表数据记录查询（不建议，建议用子查询）

内连接（inner join...on)

select e.dname empoyeename,e.job, l.dname loadername

   from t_employee e inner join t_employee l           //取别名

       on e.mgr=l.empno; 

inner join。。。on=from。。。where

外连接(left/right/full [outer] join...on)

select e.dname,e.job,l.dname loadername

      from t_employee e left join t_employee l

         on e.MGR=l.empno;             //按e建立连接，即使e.mgr对应为空，也要显示出来

select e.empno,e.dname,e.job,d.dname,d.loc

      from  t_dept d right join t_employee e

         on e.deptno=d.deptno;

#合并查询

select * from t_name1

union （all）

select * from t_name2；

#子查询

select * from t_employee 

    where sal>(

       select sal from t_employee where dname='wq');

#多列子查询

select dname,sal,job  from t_employee 

    where (sal,job)=(

       select sal,job from t_employee where dname='wq');

(in)

select * from t_employee

   where deptno not in(

      select deptno from t_dept);

(any)

select dname,sal

   from t_employee

      where sal>any(

          select sal from t_employee where job='manager');

(all)

(exists)

select d.deptno,d.dname 

    from t_dept d

       where exists(               //当（）里存在时

          select * from t_employee where deptno=d.deptno);  

#多行多列子查询

（内连接方式）

select d.deptno,d.dname,d.loc,count(e.empno) number,avg(e.sal) average

    from t_employee e inner join t_dept d

       on e.deptno=d.deptno

        group by d.deptno desc,d.dname,d.loc;

select count(*) number from t_employee e ,t_dept d;

（子连接方式）

select d.deptno,d.dname,d.loc,number,average

    from t_dept d inner join(

        select deptno,count(empno) number,avg(sal) average

           from t_employee

             group by deptno desc) e

    on d.deptno=e.deptno;

select count(*)number 

   from t_dept d,(select deptno,count(empno) number,avg(sal) average

      from t_employee

        group by deptno) e;

//子连接方式的操作数据记录数远小于内连接方式