原文地址 ：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); -- 输入查看的行数
185
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
