[大数据]mysql-sql操作

原文地址 ：https://gitee.com/gaohaixiang192/study-node/blob/master/mysql-sql.md

库操作

查看现有库

show databases;

切换到指定库

use sys;

确认当前所在库

select database();

建库

create database testdb;
-- 或
create database testdb character set utf8 collate utf8_bin;

删库

drop database testdb;

表操作

查看 testdb 库中的表

use testdb;
show tables;

查看 testtable 表结构

desc testtable;
-- 或
desc testtable ;
-- 或
desc testdb.testtable;

新建表

create table testtable(
    name char(16) not null,
    password char(48) default '',
    parimary kye(name)
)default charset=utf8;  -- 采用utf8作为字符集

查看表的实际创建指令

show create table testdb.testtable;

删除表

drop table testtable;

各种时间函数的使用

1）使用now()查看当前的日期和时间

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2017-04-02 04:02:42 |
+---------------------+

2）使用sysdate()查看系统日期和时间

mysql> SELECT sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2017-04-02 04:03:21 |
+---------------------+

3）使用curdate()获得当前的日期，不含时间

mysql> SELECT curdate();
+------------+
| curdate()  |
+------------+
| 2017-04-02 |
+------------+

4）使用curtime()获得当前的时间，不含日期

mysql> SELECT curtime();
+-----------+
| curtime() |
+-----------+
| 04:04:55  |
+-----------+

5）分别获取当前日期时间中的年份、月份、日

mysql> SELECT year(now()),month(now()),day(now());
+-------------+--------------+------------+
| year(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
|        2017 |            4 |          2 |
+-------------+--------------+------------+

6）获取系统日期时间中的月份、日

mysql> SELECT month(sysdate()),day(sysdate());
+------------------+----------------+
| month(sysdate()) | day(sysdate()) |
+------------------+----------------+
|                4 |              2 |
+------------------+----------------+

7）获取系统日期时间中的时刻

mysql> SELECT time(sysdate());
+-----------------+
| time(sysdate()) |
+-----------------+
| 04:06:08        |
+-----------------+

修改表结构

建表

create table testdb.testtable(
    id int(4) primary key,
    name varchar(4) not null,
    age int(2) not null
);

添加一个 address 字段

alert table testdb.testtable add address varchar(48);

• 在age之后添加一个gender字段

alert table testdb.testtable add enum('boy','girl') after age;

修改 gender 字段名为 sex，并添加非空约束

alert table testdb.testtable change gender sex enum('boy','girl') not null;

删除字段，删除 sex 字段

alert table testdb.testtable drop sex;

索引创建与删除

建表时指定 index,name 索引字段

create database home;
use home;
create table tea4(
    id char(6) not null,
    name varchar(6) not null,
    age int(3) not null,
    gender enum('boy','girl') default 'boy',
    index(id),index(name)
);

删除表中某个 index 索引字段，删除 name 的 index 索引字段

drop index name on tea4;

在已有表中设置一个 index 索引字段，为 age 字段建立索引，名为 nianling

create index nianling no tea4(age);

查看指定表的索引信息

show index from tea4\G;

创建表时指定 unique 索引字段，

• unique 便是唯一，同一个表可以有多个字段具有唯一性
• 创建tea5表，将id，name建立unique索引，age设置index索引

create table tea5(
    id char(6),
    name varchar(4) not null,
    age int(3) not null,
    unique(id),unique(name),index(age)
);

删除 unique 索引，在已有表中设置 unique 索引字段

• 删除tae5表name字段唯一索引

drop index name on tea5;

• 重新为tea5表的name字段建立unique索引

create unique index name on tea5(name);

建表时设置 primary key 主键索引

• 每个表中主键只有一个
• 可以在某个字段的 约束条件中指定主键，也可以在最后指定

create table biao01(
    id int(4) primary key,
    name vachar(8)
);
-- 或
create table biao02(
    id int(4),
    name varchar(8),
    primary key(id)
);

• 建表时，某个主键字段为int，可以为其设置 auto_increment 自增属性

create table tea6j(
    id int(4) auto_increment,
    name varchar(4) not null,
    age int(2) not null,
    primary key(id)
);

删除现有表的 primary key 主键索引

alert table biao01 drop primary key;

• 若想清除带有自增属性的主键，需要先清除自增属性-修改id列的字段定义

alter table tea6 modify id int(4) not null;
alert table tea6 drop primary key;

为现有表添加 primary key 主键索引

alert table tea6 add primary key(id);

设置外键实现数据同步更行与删除

create table yg(
    yg_id int(4) auto_increment,
    name char(16) not null,
    primary key(yg_id)
);

create table gz(
    gz_id int(4) not null,
    name char(16) not null,
    gz flot(7,2) not null default 0,
    index(name),
    foreign key(gz_id) peferences yg(yg_id)
               on update cascade on delete cascade 
);
insert into yg(name)values('zhangsan'),('lisi');
insert into gz(gz_id, name, gz,) values(1,'zhangsan',12000),(2,'lisi',8000);
-- 验证 update 更新联动，注意gz表中 gz_id 变动,由 1 变成 1234
update yg set yg_id=1234 where name='zhangsan';
slect * from gz where name='zhangshan';
    
-- 验证 delete 删除联动,注意gz表中，name 为 zhangsan 信息被删除
delete from yg where name='zhagnsan';
select * from gz where name='zhangsan';

存储引擎配置

查看存储引擎信息

show engines;

查看默认存储类型

show variables like 'default_storage_engine';

修改默认存储引擎

set default_storage_engine=MyISAM;

修改配置文件，重启使存储引擎生效

vi /etc/my.cnf
[mysqld]
...
default_storage_engine=MyISAM
...

数据的导入导出，

• 将/etc/passwd文件导入数据库

新建userdb库，ueser表，数据导入

create database userdb character set utf8 collate utf8_bin;
CREATE TABLE user(
    username varchar(24) NOT NULL,
    password varchar(48) DEFAULT 'x',
    uid int(5) NOT NULL,
    gid int(5) NOT NULL,
    fullname varchar(48),
    homedir varchar(64) NOT NULL,
    shell varchar(24) NOT NULL
);
--  查看导入数据文件存放的目录,一般为 /var/lib/mysql-files/passwd
show variables like '%secure%';
-- 将/etc/passwd文件复制到该文件下
load data infile '/var/lib/mysql-files/passwd'
    into table userlist
    fileds terminated by ':';
-- 为user表中每条记录田间自动编号
alert table user add sn int(4) auto_increment primary key first;

从数据库中导出查询结果

• 修改导出文件存放的目录

vi /etc/my.cnf
[mysqld]
secure_file_priv='/myload'

重启数据库

• 查看目录，并导出数据

show varable like "secure_file_priv";
select * from userdb.user where uid<100
    into outfile '/myload/user2.txt'
    fields terminated by ':';

操作表数据

创建表

create table testdb.testtable(
    name varchar(12) not null,
    gender enum('boy','girl') default 'boy',
    age int(3) not null 
);

删除表的所有记录

delete from testdb.testtable

插入表数据

insert testdb.testtable values -- 指定表字段的每个值
    ('zhangshan','boy',24),
    ('lixiaolu','girl',25),
    ('wangwu','girl',23);
insert into testdb.testtable(name,age)  -- 只指定记录的部分字段的值
    values('Mike',22);

更新表数据

update testdb.testtable set age=10; -- 更新表记录中age的所有值
update testdb.testtable set age=20  -- 只对满足条件的值更新
    where general='boy';

删除表数据

delete from testdb.testtable where age < 18;    -- 删除满足条件的表记录
delete from testdb.testtable;   -- 删除表中所有的数据

查询及匹配条件

表中插入数据

insert into testdb.testtable values
    ('Jim','girl',24),
    ('Tom','boy',21),
    ('Lily','girl',20),
    ('Jerry','boy',27),
    ('Mike','boy',21)
    ;

常用的统计函数

select count(*) from testdb.testtable;  -- 统计表中数据的数量
select avg(age),max(age),min(age) from testdb.testtable;    -- 平均，最大，最小
select count(gender) from testdb.testtable where gender='boy';  -- 某个项的总数

字段值的数值比较

select * from testdb.testtable where age=21;
select * from testdb.testtable where age>21;
select * from testdb.testtable where age=<21;
select * from testdb.testtable where age between 20 and 24;

多个条件的组合

select * from testdb.testtable where age=<21 and gender='girl';
select * from testdb.testtable where age>21 or gender='girl';
select * from testdb.testtable where name in ('Jim','Tom','Mickey');

使用select做数学计算

select 1234+5678;
select 1234*5678;
select 1234/5678;
select name,age+15 from testdb.testtable;

模糊查询，like

_匹配单个字符，%匹配多个字符

select * from testdb.testtable where name like 'J%';
select * from testdb.testtable where name like 'J__';

使用正则表达式，regexp

select * from testdb.testtable where name regexp '^J.*y$';
-- 等同于
select * from testdb.testtable where name like 'J%y';

select * from testdb.testtable where name regexp '^J|y$';
-- 等同于
select * from testdb.testtable where name like 'J%' or name like '%y';

指定字段排序，默认升序

select * from testdb.testtable group by age;
-- 等同于
select * from testdb.testtable group by age asc;    -- 升序
select * from testdb.testtable group by age desc;    -- 降序

限制输出的条数，limit

select * from testdb.testtable limit 3;
select * from testdb.testtable group by age desc limit 3;

分组查询，group by

select gender,count(gender) from testdb.testtable group by gender;
select gender as '性别',count(gender) as '人数' 
    from testdb.testtable group by gender;

用户授权及撤销

create user 'syncuser'@'%' identified with mysql_native_password by '123456'; -- 创建用户
grant all privileges on *.* to 'syncuser'@'%';  -- 授权
grant select on *.* to 'syncuser'@'%';  -- 只授查询权限
flush privileges; 
show grant for 'syncuser'@'%';  -- 查看授权
revoke all on *.* from 'syncuser'@'%'; -- 撤销授权

视图

查看某个库的所有视图

show tables status where comment='view';

作用

1、提高了重用性；
2、对数据库重构，却不影响程序的运行；
3、提高了安全性能，可以对不同的用户，设定不同的视图；
4、让数据更加清晰。

/*
测试表:user有id，name，age，sex字段
测试表:goods有id，name，price字段
测试表:ug有id，userid，goodsid字段
*/
-- 1、提高了重用性；
-- 原本的sql语句
select a.name as username, 
       b.name as goodsname 
from user as a, 
     goods as b, ug as c 
where a.id=c.userid and c.goodsid=b.id;
-- 创建一个视图
create view other as 
    select a.name as username, 
           b.name as goodsname 
    from user as a, goods as b, ug as c 
    where a.id=c.userid and c.goodsid=b.id;
-- 使用视图，直接代理原来的sql语句
select * from other;

-- 2、对数据库重构，却不影响程序的运行；
/*
测试表:usera有id，name，age字段
测试表:userb有id，name，sex字段
*/
create view user as 
    select a.name,a.age,b.sex 
    from usera as a, userb as b where a.name=b.name;
-- 以上假设name都是唯一的。此时php端使用sql语句：select * from user;
-- 就不会报错什么的。这就实现了更改数据库结构，不更改脚本程序的功能了。

-- 3、提高了安全性能，可以对不同的用户，设定不同的视图；
-- 例如：某用户只能获取user表的name和age数据，不能获取sex数据。则可以这样创建视图。示例如下：
create view other as select a.name, a.age from user as a;
-- 这样的话，使用sql语句：select * from other; 最多就只能获取name和age的数据，其他的数据就获取不了了。

-- 4、让数据更加清晰。
-- 让数据更加清晰。想要什么样的数据，就创建什么样的视图。

视图的基本使用

1、什么是视图：是一种虚拟存在的表
2、内容与真实的表相似，包含一系列带有名称的列和行数据。
3、视图并不在数据库中以存储的数据的形式存在。
4、行和列的数据来自定义视图时查询所引用的基本表，并且在具体引用视图时动态生成。
5、更新视图的数据，就是更新基表的数据
6、更新基表数据，视图的数据也会跟着改变

-- 将/etc/passwd文件导入数据库
-- 建表
create table testdb.user(
    username char(20),
    password char(1),
    uidint(2),
    gid int(2),
    comment char(100),
    homedir char(100),
    shell char(50));
-- 拷贝文件到指定目录
-- cp /et/passwd /var/lib/mysql-files/
-- 将数据导入 testdb.user 表中
load data 
    infile "/var/lib/mysql-files/passwd"  
    into table testdb.user fields terminated 
    by ":"  lines terminated by "\n";
-- 为 testdb.user 表添加行号及主键
alert table testdb.user add id int(2) parimary key auto_increment first;
-- 创建视图1 结构及数据与user表一样
use testdb;
create view userv1 as select * from user;
-- 创建视图2 只有user表shell是/bin/bash 用户信息
create view userv2 as shell from user;
-- 对视图表和基表进行insert update delete 操作
insert into userv1(username,uid) values("jarry",9);
update userv1 set uid=9 where username='adm';
delete from userv1 where uid=9;
-- 删除视图1和视图2
drop view userv1;
drop view userv2;

创建视图完全格式

-- 快速建表 user2 表
create table user2 select username,uid,gid from user limit3;
-- 快速建表 info 表
create table info select username,uid,homedir,shell form user limit 5;
-- 查询 user2.username = info.username 的字段
select * from user2 left join info on user2.username=info.name;

-- 创建视图，关联查询建的视图  默认不允许修改视图字段的值
-- 下面的创建是错误的
-- create view v4 as select * from user2 left join info on user2.username=info.name;
create view v4 as 
    select a.username as ausername,
           b.username as busername,  
           a.uid as auid,
           b.uid as buid  
    from user2 a  left join info b  on  a.username=b.username;
-- 查看视图 v4 的所有信息
select * from v4;
desc v4;

or replace 选项的使用

-- 创建时，若视图已经存在则会替换已经有的视图
create  or  replace view v4 as 
    select a.username as ausername,
           b.username as busername,  
           a.uid as auid,
           b.uid as buid  
    from user2 a  left join info b  on  a.username=b.username;

with local check option

-- local 和 cascaded 关键字决定检查的范围
-- local 仅检查当前视图的限制
-- cascaded 同时要满足基表的限制(默认值)
create table user1 select username,uid,shell from user where uid>=5 and uid <=40;
create view v1 as select username,uid from user1 where uid<=20;
-- 操作超过视图表的条件限制（uid<=20）之后，在视图表里面查看不到，在基表里可以查看到
update v1 set uid=21 where  username="sync";
-- 基表在超过条件限制（uid>=5 and uid <=40),在基表里依然可以查看到
update user1 set uid=41 where  username="ftp";
-- 快速创建一个新表a
create table a select * from user where uid < 10;
-- 不写默认为CASCADED检查自己和a要满足的要求即可
create view v3 as select * from a where uid < 10 with  check option;
update v3 set uid=9 where username="adm";
-- 满足自身v2的要求
create view v2 as select * from v1 where uid >= 5 with local check option;
update v2 set uid=9 where username="sync";

with cascaded check option

create view v5 as select * from v1 where uid >= 5 with cascaded check option;

存储过程

作用

1、增强SQL语句的功能和灵活性；
2、减少网络流量，从而可降低网络负载；
3、提高数据库的访问效率；
4、提高数据库的安全性和数据的完整性；
5、使数据独立，达到解耦的效果。

创建存储过程

delimiter -- 定义定界符
    create procedure say() -- say(),必须带有括号
    begin
    select * from user id<=10;
    end 
delimiter ; -- 把命令的定界符改回来，分号前有空格
call say(); -- 调用存储过程名，在括号里面不写参数时，可以不加括号

查看存储过程

show procedure status ;
-- 或者
select db,name,type from mysql.proc where name="say";

删除存储过程

drop procedure say;

创建一个显示shell是 /bin/bash 的用的 存储过程

delimiter // 
    create procedure pl()
    begin
    select count(username) from user where shell="/bin/bash";
    end 
    // 
delimiter ;
call pl();

存储过程参数的使用

/*
in 输入参数 传递值给存储过程，必须在调用存储过程时指定，在存储过程中修改该参数的值不能；默认类型是in
out 输出参数 该值可在存储过程内部被改变，并可返回
inout 输入/输出参数 调用时指定，并且可被改变和返回
*/
delimiter // 
    create procedure say2(in username char(10))
    begin 
    select username;
    select * from where username=username;
    end 
    // 
delimiter ;
call say2("tom");

-- 创建名为p2的存储过程，可以接收用户输入shell的名字，统计user表中用户输入shell名字的个数
delimiter // 
    create procedure p2(out number int)
    begin 
    select count(username) into @number from user where shell!="/bin/bash";
    select @number ;
    end 
    // 
delimiter ;
call p2(@number)

使用循环结构

算数运算

set @z=1+2;select @z;
set @x=1;set @y=2;set @z=@x*@y;select @z;
set @x=1;set @y=2;set @z=@x-@y;select @z;

/*
declare调用变量不需要@  其他都需要
调用变量时，有@符号的变量 如@x：调用的是用户自定义变量
没有@符号的变量 如x：调用的是存储过程的参数变量
*/
delimiter //
    create procedure say5(in bash char(20), in  nologin char(25), out x int , out y int)
    begin
    declare z int ;
    set z=0;
    select count(username) into  @x from user where shell=bash;
    select count(username) into  @y from user where shell=nologin;
    set z=@x+@y;
    select z;
    end
    //
delimiter ;
call say5("/bin/bash","/sbin/nologin",@x,@y);

-- 顺序结构（if判断）当“条件成立”时执行命令序列,否则，不执行任何操作
delimiter //
    create  procedure say6(in  x int(1) )
    begin
    if x <= 10 then
    select * from user where id <=x;
    end if;
    end
    //
delimiter ;
call say6(1);        -- 条件判断成立，等于1是否成立
call say6(2);

-- 定义名称为p3的存储过程，用户可以自定义显示user表记录的行数，若调用时用户没有输入行数，默认显示第1条记录
delimiter //
create  procedure p3(in  linenum char(10) )
    begin
    if linenum is null then
    set @linenum=1;
    select * from user where id=@linenum;
    else
    select linenum;
    select * from user where id=linenum;
    end if;
    end
    //
delimiter ;
call p3(null);        -- 不输入查看的行数
call p3(3);        -- 输入查看的行数