mysql
启动:
service mysqld start
service mysqld restart
service mysqld stop
/usr/bin/mysqld_safe &
mysqladmin -uroot shutdown -p
mariadb: yum安装
systemctl start mariadb
连接:
mysql -u root -p123123
指定数据库连接:mysql -u root -D test -p123123
指定套接字连接:mysql -u root -p -S /var/lib/mysql/mysql.sock
远程主机连接:mysql -u root -p -h 192.168.1.103 -P 3306
连接并执行sql语句:mysql -u root -p123123 -e 'use mysql; select user,host,password from user;'
mysql -uroot -p123123 -e 'create database if not exists testdb; show databases;'
获取帮助:
help
help create
help create database
查看帮助分为哪几类:help contents
查看MySQL变量:
show global variables \G;
show global variables like '%version%' \G;
show global variables where variable_name like '%log%' and value = 'off'
全局变量 会话变量
show session variables \G;
查看单个变量:
select @@global.pid_file;
select @@session.warning_count;
select @@warning_count;
查看状态变量:
show status;
show global status;
show session status;
修改/设定变量值:
set @@session.autocommit=0;
set global var_name = value;
set @@global.var_name = value;
mysql 用户账号格式:
username@host
_:表示任意单个字符
%:表示任意长度的任意字符
eg. root@'10.1.%.%'
查询当前库中的用户:
use mysql
selet user,host,password from user;
创建用户:
create user 'mds'@'localhost';
create user 'mds'@'localhost' identified by '123123';
在授权数据库时,如果对应用户不存在,则会自动创建:
grant all on zsythink.* to zsy@127.0.0.1 identified by 'zsythink';
删除用户:
drop user mds@'localhost';
重命名用户:
rename user OldName to NewName;
使用mysqladmin设置密码:
mysqladmin -u username -p oldpassword password newpassword
使用root用户来设置密码:
set password for 'user'@'localhost' = PASSWOR('newpassword');
在mysql5.6及以前版本可用:
update mysql.user set password = password('密码') where user = '用户名';
在mysql5.7及其以后的版本可用:
update mysql.user set authentication_string = password('密码') where user = '用户名';
找回root密码:
1. 停止mysql进程
2. 使用mysqld_safe --skip-grant-table & 可绕过用户验证启动。
3. 用mysql -uroot 登陆数据库
4. mysql> UPDATE mysql.user SET password=PASSWORD("new password") WHERE user='root';
5. mysql> FLUSH PRIVILEGES;
6. 停止mysql服务,并重启。
授权命令:
GRANT ALL [PRIVILEGES] ON db.tbl TO 'username'@'host' IDENTIFIED BY 'password';
grant all privileges on zsythink.* to zsy@127.0.0.1 identified by 'zsythink';
grant all on zsythink.* to zsy@127.0.0.1 identified by 'zsythink';
给远程用户授权:
grant all on zsythink.* to zsy@127.0.0.1 identified by 'zsythink';
记住使用FLUSH PRIVILEGES 命令刷新。
grant insert,delete,update,select on zsythink.* to zsy@'192.168.%.%';
grant select on hellodb.* to zsy@localhost,zsythink@localhost;
指明授权某个函数test的权限给某个用户:
grant select on hellodb.* to zsy@localhost,zsythink@localhost;
指明授权某个存储过程test的权限给某个用户:
grant execute on procedure zsythink.test to zsy@'192.168.%.%';
当一个用户被创建时,自动获得usage权限,usage权限只能用于登陆数据,不能执行其他操作。
强制远程用户使用ssl建立会话:
grant usage on *.* to 'zsy'@'222.222.222.222' require ssl;
撤销强制使用ssl :
grant usage on *.* to 'zsy'@'222.222.222.222' require none;
查看授权:
show grants for 用户名;
show grants for zsy@localhost;
从数据库的角度查看授权:
select * from mysql.db where Db="你要查看的数据库"
删除授权:
revoke "要移除的权限" on 数据库.表 from 用户@host;
revoke all on word.* from zsy@www.zsythink.net;
创建数据库:
create database testdb;
create database if not exists testdb;
create database if not exists testdb default character set utf16;
查看所有数据库:
show databases;
查看建表语句:
show create database testdb;
查看可用字符集:
show character set;
查看排序方式:
show collation;
查看当前数据库当前连接信息:status
修改数据库一般指修改数据库的字符集和排序规则:
alter database testdb character set utf8;
alter database testdb default character set utf8;
删除数据库:
drop database if exists testdb;
查看表:
show tables;
查看表的具体属性:
show table status\G;
查看某个具体的表的状态:
show table status like 'test' \G;
查看表结构:
desc Table_Name;
查看建表语句:
show create table table_name;
建表:
create table test1 (id int(11) key, name varchar(65) not null comment 'student name ');
create table test1 (id int(11) , name varchar(65) not null comment 'student name ', primary key(id));
create table test2 (id int primary key , name varchar(65) not null comment 'student name ', tid int, foreign key(tid) reference test3(id));
创建表时就创建索引:
create table test4 (id int(11), name varchar(50), primary key(id),
key ind_name(name));
create table students ( id int primary key auto_increment, name varchar(66) not null, age tinyint unsigned, gender enum('f','m') default 'm', index(name) );
复制一个表(只复制表结构):
create table test00 like test11;
复制一个表(只复制表数据):
create table test00 select * from test11;
表选项:
create table tt (id int primary key auto_increment, name varchar(55) not null, index ind_name(name) ) engine=InnoDB auto_increment=2 default charset=utf8;
使用help create table可查看更多表选项。
删除表:
drop table if exists tt,ttt;
修改表名:
alter table test1 rename as test2;
添加字段:
alter table ttt add column age int;
alter table ttt add age int;
添加字段的同时添加约束:
alter table ttt add age int not null default 0;
alter table ttt add column age int not null default 0;
添加字段的同时指定位置:
alter table ttt add id int first;
alter table ttt add column age int after name;
删除字段:
alter table tt drop stuname;
重命名字段:
alter table testtable change name name1 char(5);
修改字段类型:
alter table testtable change age age char(10);
alter table testtable modify age int;
主键:不能为空,不能相同,只能有一个主键,可由多个字段组成
唯一键:可为空,不能相同,可有多个主键,可由多个字段组成
外键:一个表中外键字段所能插入的数据范围,取决于引用的另一个表主键字段上已经存在的数据集合。
检查约束条件:check 自定义的逻辑表达式。
在创建主键,唯一键时会自动创建索引。
添加非空约束:
alter table testtb modify name varchar(100) not null;
删除非空约束:
alter table testtb modify name varchar(100) null;
添加自动增长:
alter table testtb modify id int auto_increment;
alter table testtb change id id int auto_increment;
删除自动增长:
alter table testtb change id id int;
alter table testtb modify id int;
添加主键约束:
alter table testtb4 add primary key(id);
alter table testtb4 add constraint primary key(id);
删除主键约束:
要先删除自动增长,若已被其他字段当做外键,则还需要先删除外键
alter table testtb drop primary key;
添加唯一键:
alter table testtb add unique key(uid);
添加唯一键时起个名称:alter table testtb add unique key(uid);
删除唯一键:
alter table testtb drop index uni_test;
查看约束(所有约束):
select * from information_schema.key_column_usage where table_name='test1';
查看主键:
desc test1
查看表的外键:
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'test1' and REFERENCED_TABLE_NAME is not null;
select REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME
from information_schema.KEY_COLUMN_USAGE
where REFERENCED_TABLE_NAME = 'test2';
添加外键约束:
alter table testtb add column tid int default 0 not null;
alter table testtb add constraint testtb_tid_fk foreign key(tid) references testtb2(id);
删除外键约束:
先查询到外键名称,再删除
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'test4' and REFERENCED_TABLE_NAME is not null;
alter table test4 drop foreign key test_tid_fk;
添加索引:
alter table testtb add index ind_name(name);
create index ind_name on testtb (name(20) desc);
create index ind_name on testtb (name(20));
create index ind_name on testtb (name);
查看索引:
show index from testtb;
创建联合索引:
create index ind_id_name on testtb1 (id,name);
create index ind_id_name on testtb1 (id,name(20));
查看以ind开头的索引:
show index from testtb where key_name like 'ind%';
创建视图前确认有创建视图的权限:
select create_view_priv,select_priv from mysql.user where user='root' and host='localhost';
创建视图:
create view testvi as select * from classes where classid < 3;
create or replace view testvi as select * from classes where classid <= 4;
create view testvi(a,b,c) as select * from classes
指定算法创建视图:
create algorithm=merge view testvi as select name,age from students;
算法有:merge temptable undefined(它自己选择)
删除视图:
drop view testvi;
drop view if exists testvi;
查看视图:
select * from information_schema.views where table_schema='U_DB_NAME';
desc testvi;
select * from testvi;
修改视图:
alter view testvi as select name,age,gender from students;
一般不更新视图中的数据。
insert语句:、
insert into tb1 (name,age) values('tom',33);
insert into tb1 (name,age) values('jerry',22),('naruto',28);
insert into tb1 values (4,'Sasuke',28),(5,'hinata',25);
insert into tbl2 set id=2,name="test",age=18,gender='M';
mysql默认的sqlmode为宽松模式,即使插入的数据不是完全符合类型要求,也有可能插入数据,但是会被截断。
应该讲global.sql_mode变量的值设为TRADITIONAL使用的存储引擎为innodb。
sql_mode常用模式:
ansi:宽松模式
strict_trans_tables:只对事务性表进行严格限制。
strict_all_tables:对所有表严格限制。
traditional:严格模式。
清空表中数据:
delete from tb1;
删除数据:
delete from tb1 where age=22;
delete from tb1 where name rlike '^t.*';
delete from tb1 where age > 30 order by age desc limit 1;
更新数据:
update tb1 set age = 28;
update tb1 set name='luffy' where id=13;
update tb1 set name='luffy',age=25 where id=13;
select语句:
select * from tb1 limit 3;
‘_'表示任意单个字符:
select * from tb1 where name like 't__';
正则表达式:
select * from tb1 where name rlike '^t.*';
select * from tb1 where age in (22,23,24,25);
select * from tb1 where age not in (28,33,43);
select * from tb1 order by age;
select * from tb1 order by age asc;
select * from tb1 order by age desc,name asc;
select distinct age from students;
select name as StuName,age from tb1;
select中的分组与聚合:
select avg(age),gender from students group by gender;
常用聚合函数:
min(col) max(col) avg(col) count(col) sum(col) group_concat(col)
对分组后的信息再次过滤可以使用having关键字。
select classid,avg(age) as avgage from students group by classid having avgage > 25;
select sum(age) from students where age > 19 group by gender;
多表查询:
交叉连接,即没有任何限制条件的连接。“笛卡尔乘积” cross join
select * from t1 cross join t2 cross join t3;
select * from t1,t2,t3;
内连接:inner join
两张表中同时符合某种条件的数据记录的组合。
select * from t1,t2 where t1.t1id = t2.t2id;
自连接:
自连接把同一张表当做两张表连接起来
select * from students s1, students t1 where s1.tid = t1.id;
inner join = join
select * from t1 join t2 on t1.t1id=t2.t2id;
外连接:
左外连接:left outer join
包含左表以及左表和右表公共的。
查属于t1但不属于t2的:
select * from t1 left join t2 on t1id=t2id where t2id is null;
联合查询:
即把多个查询语句的结果集中在一起显示。两个语句查出的字段数量必须相同,否则无法使用union进行联合查询。
select * from t2 union select t3str2 t3str1 from t3;
union all 不合并相同的项。
全连接:
mysql 不支持全连接,但可通过union联合 左连接和右连接实现全连接。
查询缓存:
看看有没有开:show variables like '%query_cache%';
清除缓存:reset query cache;
存储引擎:
myisam:支持表级锁,不支持行级锁,不支持事务,不支持外键约束,支持全文索引,表空间相对小。
innodb:支持表级锁,行级锁,支持事务,支持外键,不支持全文索引,表空间文件相对较大。
show engines;
innodb 与myisam的数据文件:
innodb: 后缀为frm的是存储了表的表结构信息。
后缀为ibd的存放了表的数据信息与索引信息。
myisam:
后缀为frm的存放了表结构信息。
后缀为MYD的存放了数据信息。
后缀为MYI的存放了索引信息。
事务:
ACID
A:atomicity 原子性,要么全成功要么全失败回滚到最初状态。
C:consistency 一致性,总是从一个一致性状态转为另一个一致性状态。
I:isolation 隔离性,一个事务在提交之前所作出的操作能否为其他事务可见,有不同的隔离级别。
D:durability 持久性,事务一旦提交所作出的修改是永久保存。