cls 清屏
数据库基本操作命令
mysqld –install mysql157 需要在mysql安装位置中的bin目录下(如果没有添加环境变量, )必须以管理员权限打开cmd
mysqld –remove mysql157 需要在mysql安装位置中的bin目录下(如果没有添加环境变量)必须以管理员权限打开cmd
services.mcs 不可以在mysql中打开
mysqld --initialize-insecure 初始化产生data文件(insecure是显示空密码)(一次就行)
mysql -uroot -p 可以产生多个客户端(打开多个cmd文件输入这句话)(输入status 查看connection id会发现不同)
status 查看状态
select version(); 查看版本
delimiter: 命令结束符号自定义 \d aaa
quit、exit 退出
一、远程连接
1、(客户端只需要一个mysql.exe文件即可)主机解压 mysql5.7.25.zip /bin/mysql.exe 放到c:根目录下(这个目录可以随便设置)
2、查看虚拟机的ip: cmd ipconfig
3、虚拟机 mysql -uroot -p
mysql> select user,host,authentication_string from mysql.user;
mysql> rename user root@localhost to root@'%';
mysql> select user,host,authentication_string from mysql.user;
4、虚拟机关闭防火墙, 控制面板-》防火墙-> 打开或关闭防火墙
5、主机上cmd执行(在mysql.exe的目录下)mysql -uroot -p –h(虚拟机ip地址)(虚拟机的服务要处于开启状态)
二、创建桥接
windows 7 x64 设置桥接模式 ipconfig
show processlist 显示当前的进程(删除进程用kill (id) 输入status两次会自动重连并分配不同的id)
dir 显示当前目录下的文件
cd:change directory
show databases; 显示数据库
use db_name; 进入数据库
show tables; 显示表当前数据库下的所有表
desc t_name; 显示表中的列
select * from tb_name ;显示所有的行
select * from tb_name\G 当表中数据太多会分行显示出来
select user,host,authentication_string from mysql.user; 进入表格一般用户存在这里
create user zhang@'%' identified by 'zhang' 创建用户zhang密码也是zhang
set password for user@’host’= ‘xxxxxx’; 修改用户密码(需要是root用户才可以改任意用户的密码)
update mysql.user set password=password(‘password’) where clause; 需要重新将授权表加载到内存中,才能生效(flush privileges;)
skip-grant-tables (跳过授权,如果忘记密码在my.ini中输入这句话)
set password=’xxxx’ 只能改自己的
select password(‘zhang’); 查询zhang的密码
mysql –uzhang –p –h129.168.5.128 在客户端输入回车后并将密码输入进行登录(这是一个普通用户用show databases;无法显示根目录下的文件(因为没有权限))
grant select on blog.* to zhang@’%’; 给zhang添加权限让其能够显示blog的所有
revoke select on blog.* form zhang@’%’; 给zhang删除权限让其不能够显示blog的所有
SQL语句分类
DDL(data definition language):数据定义语言 create(创建), drop(删除), alter(改变) 与列相关(针对的组件基本为数据库 数据表...... )
DML(data manipulation language: 数据操作语言 insert、 delete, update, select 增删改查(与行相关)
DCL (data control language):数据控制语言 grant, revoke (授权和撤销授权).....
\? (相当于HELP)
\c 是清除(写错时用(若是三引号则需打个’再加\c))
create database [if not exists] db_name; 创建数据库
show warnings; 显示告警
show charset; 显示支持的字符集
drop database db_name 删除数据库
show engines; 显示引擎没啥用了解一下
drop table t_name; 删除表
ESC 光标删除至最左侧
F7 之前操作过的sql命令列表
insert into t_name(col_name1,col_name2) values(val1,val2); 向表中插入数据
select password(‘xxxx’); 查看密码的编码
prompt xxx> 默认提示符(建议添加一个系统的环境变量:新建一个环境变量变量名:MSQL_PS1变量值:\u@\h[\d]>)
mysql –uroot –p –h192.168.5.128 -e “show databases;use mysql;show tables;”>d:\test.txt 不用进入客户端直接在操作系统中就能对数据库进行操作(>及以后是将输出结果放在test文件中重定向符)
source d:\test.txt 执行test文件中的命令(\.也行)
数据类型:1、字符型
变长字符 varchar()
定长字符 char()
内奸类型 enum(),set()
2、数值型
整形 int
十进制 decimal(10,3)
3、日期型 一定要带引号
当前时间 now()
日期 data: ‘2019-12-02’
时间 time: ’12-34-23’
日期和时间 datetime当前服务器时间
时间戳 timestamp 时区时间
年份 year(2),year(4):两位或四位前者不长用
时间区域 set time_zone = “+9:00”; 时间戳用
4、修饰符
not null 非空
null 空
default 默认值( default ‘unknown’)
unsigned 无符号位
auto_increment 自增长(必须加主键)
primary key 主键(不能不填,不能有重值)
unique key (不能不填,可以重值)
DDl:数据定义语言(CREATE DROP ALTER)
create table [if not exists] tb_name (col1 type1,col2 type2) 创建表
create table t_name as select *from t_name1; 复制表(约束key无法复制过来(通过desc查看))
create table t_name like t_name1; 结构相同但是数据无法复制过来(用inert into t_name select *from t_name1;把数据复制过来)(like后面只能跟单个表名,不能跟查询结果集)
show table status\G 显示当前数据库下所有表的状态
show table status like ‘t_name[%]’ 显示具体某个表的状态信息(或者是模糊查询)
drop table [if exists] t_name; 删除表
alter table t_name rename new_name; 改表名
alter table t_name add col_name col_type; 添加字段
alter table t_name drop col_name; 删除字段
alter table t_name change col_name new_name col_type; 改变字段名
alter table t_name modify col_name col_type; 改变字段属性
alter table t_name add index idx_name(col_name); 添加索引
show index from t_name; 查看表索引
alter table t_name drop index idx_name; 删除索引
DMl:数据操作语言(insert、delete、update、select)
insert into t_name [(col1,col2)] values(v1,v2); 插入数据(可插入多条在后面加,(v3,v4))
delete from t_name; 删除所有行
delete from t_name where clause; 删除对应行
update t_name set clo_name=value where clause; 修改某个值
select col1,col2,col3 from t_name; 查询表中的列
order by convert(sname using gbk) 汉字通过国标卡排序
select col1,col2,... from t_name [where clause] [order by ‘col_name’ [desc]] [limit [m,]n];
字段表示法:
*:所有字段
as:字段别名,col1 as alias1 as可以不写
order by
排序查找(默认升序) 降序接desc
limit
字符限制查找(只显示其中部分内容),可以偏移限制查找。)
Limit 3 三列
limit 0,3 0-3列
Clause where 条件表示:
字段后接比较式(除等于,其他仅局限于数值型)
> , = , < , >= , <= , !=
条件逻辑操作
And 而且
Or 或者
Not 非
between ... and ...(相当于>=和<=之间)
in 在里面
空值比较或匹配
is null (子句内元素为空)
is not null (子句内元素为非空)
like (模糊匹配)
%: 任意长度任意字符
_: 任意单个字符
truncate table t; (会回收空间)(截短的意思)DDL
delete from x1;( 不会回收空间)DML
drop table x1; 直接表这个文件没了DDL
insert into t_name select * from t_name; 自插
DCl:数据控制语言(GRANT REVOKE)
grant all on db_name.t_name to user@’host’; 为用户设置权限
show grants ;查看权限
show grants for user@’host’; 查看指定用户权限(要在root中查看)
revoke all on db_name.t_name from user@’host’; 为用户删除权限
错误日志 在data中god-PC.err
运用虚拟机的快照进行当前状态的保存 虚拟机 快照 拍摄快照 快照管理 点击快照 转到
show variables like ‘log_error’; 显示错误日志的地址
索引: 一般返回数据3%-5%效果最好
create table t_name(index idx_name(col_name)); 创建表格时指定
alter table t_name add\drop index idx_name(col_name) 添加删除索引
drop index idx_name on t_name; 删除索引
explain select * from t_name where col_name= col_value; 显示一个表某行的相关信息
show index from t_name; 显示表中的索引
show keys from t_name; 查看索引
show indexes from t_name; 查看索引
模糊查询时必须用左前缀索引:例如like ‘abc%’
索引使用总结
1.优点:
* 加速查询,条件返回记录数占总行数3%-5%
2.缺点:
* 占用额外磁盘空间
* insert变慢
* update delete 可能变慢,也可能变快(依赖索引定位update delete的行)
3.使用条件:
* 根据业务特性,在经常作为查询条件的列上适当加上索引
* 不要每一列都加索引,因为索引会随着表行数据的变化自动更新(占系统资源)
* MySQL官方建议,每个表都要有一个主键
外键:
alter table t_name add foreign key(col_name) reference r_t_name(col_name) 在表中某一列创建一个外键,参照参照表的某一列,必须要在参照表中的那一列中设置一个主键
show create table t_name\G 显示创建这个表的信息(constraint后面写的是外键)
视图:
1、简化查询
2、提高安全性
create view v_name as t_name; 创建一个视图
show table status\G 显示表的状态(视图后面全是null)
grant privilege on db_name.t_name to user@’host’; 将视图交给某个用户看(保证安全性)
insert into v_name values(v1,v2、、、); 视图与母表(基表)发生变化时会互相发生变化
show create table v_name; 显示创建视图的步骤
DML高级查询:
select col1,... from clause ---> where clause ---> group by ---> having---> order by ---> limit写法顺序
select distinct col_name from t_name; 去重
select col_name [as] ‘alias’ from t_name; 给列添加别名
select (1+5.3)*15; 支持算术运算
select col_name+col1_name from t_name; 支持列相加
col_name between v1 and v2; 范围(或者用in(v1-v2))
count(*) 显示数量
sum(*) 求和
avg(*) 求平均值
max(*) 最大值
min(*) 最小值
select group.concat(col_name) from t_name; 将某一列转换成字符串
group by col_name,col_name1,col_name2 按某多列列来分组
having clause 写在group by 后面
select 1 col_name; 给列赋值
select * from t_name1 alias1 join t_name2 alias2; 多表查
询中的交叉连接:笛卡尔积(最消耗资源,效率极低)
select * from t_name1 alias1 [left/right] join t_name2 alias2 on alias1.col_name1=alias2.col_name2; 多表查询中的等值连接
select a.*,b.id id1,b.name name1 from team a join team b on a.id!=b.id order by a.id; 多表查询中的不等值连接(在足球双循环赛中用)
select a.*,b.id id1,b.name name1 from team a join team b on a.idselect * from t_name where weight>(select col_name from t_name); 子查询
select * from (select * from t_name clause) alias clause; 套表查询(必须给套表起别名)
select col_name1 from t_name1 union select col_name2 from t_name2; 复合查询需要列相同
事物: myisam引擎不支持事物(钱回不来了)
begin;/start transaction;
SQL1
SQL2…
commit;/rollback;
show variables like ‘autocommit’; 显示自动提交变量的状态
set autocommit=0/off; 关闭autocommit(只对当前会话有用)
set global autocommit=0/off; 关闭autocommit(对所有会话有用(当前会话不会改变))
mysql数据备份和恢复:
show variables like‘log_bin’’; 显示二进制日志(需要在my.ini写入log_bin=mysql-bin和 server-id=1两句话,并重新启动服务器,会发现在data出现mysql-bin.0000001的两个文件,不能用文本编辑器查看)
cd \mysql-5.7.25-winx64\data 进入data目录
dir 显示目录
mysqlbinlog mysql-bin.0000001 查看日志
mysqldump –uroot –p --databases db_name db_name1>c:\bak\mybak.txt将数据库拷贝到一个地址(必须在c盘根目录下一定要创建一个文件夹,否则会拒绝访问)(想要远程备份添加-h)
source c:\bak\mybak.txt;如果删除了数据可以用source对备份的数据进行恢复
mysqldump –uroot –p – alll-databases >c:\bak\mybak.txt备份所有的数据库(想要远程备份添加-h)
mysqldump –uroot –p db_name t_name1 t_name2> c:\bak\mybak.txt; 倒入数据库中的某些表(想要远程备份添加-h)
存储过程:(建议在txt中编写)
语法:
\d 标识符修改命令结束符是为了防止;产生的错误
CREATE PROCEDURE proc_name()
BEGIN
....
....
END 标识符
\d ;
调用存储过程call proc_name()
#存储过程,录入班级信息
\d $$
create procedure insert_class(in_cno int, in_cname varchar(50), in_teacher varchar(50))
begin
insert into class values(in_cno,in_cname,in_teacher);
end$$
\d ;
#存储过程,删除班级信息
\d //
create procedure delete_class(in_cno int)
begin
delete from class where cno=in_cno;
end//
\d;
#存储过程,查询班级信息
\d //
create procedure show_stu(in_cno int)
begin
select * from stu where cno=in_cno;
end//
\d;
数值函数:
ABS() 返回数值的绝对值 (案例的数值例如x)
CEIL() 返回不小于x的最小整数
FLOOR() 返回不大于 x 的最大整数(与CEIL的用法刚好相反)
MOD(x,y) 返回数字x除以y后的余数:x mod y 相当于%取模
ROUND(X[,Y]) 将数字X四舍五入到指定的小数位数Y
FLOOR(i + RAND() * (j - i)) 获得一个从i到j之间的随机整数
字符串函数:
日期函数:
YEAR(date) 获得年份
MONTH(date) 获得月份
DAY(date) 获得天
HOUR(date) 获得小时
MINUTE(date) 获得分钟
SECOND(date) 获得秒
NOW() 当前日期和时间
ADDDATE(date,x) 获得x天后的日期
SUBDATE(date,x) 获得x天前的日期
DATEDIFF(date1,date2) 获得2个日期之间的天数
自定义函数:
\d 标识符
CREATE FUNCTION function_name (参数列表)
RETURNS 返回值类型
BEGIN
routine_body (必须要有return)
END 标识符
\d ;
例1:输入生日返回年龄:
\d $$
create function get_age(in_birth date) returns decimal(8,2)
begin
return(select round(datediff(now(),in_birth)/365,2));
end$$
\d;
select sname,birth,get_age(birth) age from stu;
select sname,birth,get_age(birth) age from stu where get_age(birth)>25;
例2:输入班号返回班名
\d $$
create function get_cname1(in_cno int) returns varchar(50)
begin
declare v_cname varchar(50);
select cname into v_cname from class where cno=in_cno;
if v_cname is not null then
return v_cname;
else
return '无班';
end if;
end$$
\d;
触发器:
示例1:删除班级表中1班的数据,级联删除学生表中1班的学生:
\d $$
create trigger t_delete_stu before delete on class for each row
begin
delete from stu where cno=old.cno;
end$$
\d ;
begin;delete from class where cno=1;
示例2:创建用户和黑名单表,
用户表:用户编号,手机号,余额;
黑名单表:用户编号,手机号。
如果话费余额不足10元,把用户编号和其手机加入到黑名单表中;如果用户充值后,余额超过10元,把其信息从黑名单表中移除。
\d $$
create trigger t_oper_blacklist before update on users for each row
begin
if new.balance<10 then
insert into blacklist values(old.id,old.phone);
end if;
if new.balance>=10 then
delete from blacklist where id=old.id;
end if;
end$$
\d;
update users set balance=balance-25 where phone='18520151234';
select * from users;
select * from blacklist;
update users set balance=balance+100 where phone='18520151234';
select * from users;
select * from blacklist;