数据库的基本操作
数据库的操作：
#增加数据库
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
as
select 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;
//子连接方式的操作数据记录数远小于内连接方式
转载请注明原文地址: https://ju.6miu.com/read-8878.html
