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 持久性，事务一旦提交所作出的修改是永久保存。