mysql – 谢民皆的个人分享

mysql 数据库维护

数据库维护

analyze table info,info2;//检查表键是否正确
check table info, info2;//针对多种问题进行检查
help//查看帮助

analyze table info,info2;//检查表键是否正确

check table info, info2;//针对多种问题进行检查

help//查看帮助

mysql 登陆及常用命令

登陆及常用命令

启动，关闭，检查状态
mysql.server start
mysql.server stop
mysql.server status
登陆
mysql -uroot -p
修改密码(新密码123)
ALTER USER 'root'@'localhost' IDENTIFIED by '123';

常用命令
show databases;
use test//切换到test库
select database();//查看选择的数据库
select version();//查看的数据库版本
select now();//查看时间
select user();//查看当前用户

创建、修改和删除数据库
create database (if not exists) test1 (character set utf8);
alter database (if not exists) test1 (character set **);
rename database test1 to test2;
drop database (if exists) test1;

启动，关闭，检查状态

mysql.server start

mysql.server stop

mysql.server status

登陆

mysql -uroot -p

修改密码(新密码123)

ALTER USER 'root'@'localhost' IDENTIFIED by '123';

常用命令

show databases;

use test//切换到test库

select database();//查看选择的数据库

select version();//查看的数据库版本

select now();//查看时间

select user();//查看当前用户

创建、修改和删除数据库

create database (if not exists) test1 (character set utf8);

alter database (if not exists) test1 (character set **);

rename database test1 to test2;

drop database (if exists) test1;

mysql 数据类型

数据类型

TINYINT -128 - 127
INT 2147*****
BIGINT 2^63
FLOAT 4个字节
DOUBLE 8个字节
 
YEAR 1901-2155 yyyy
TIME hh:mm:dd
DATE yyyy-mm-dd
DATETIME yyyy-mm-dd hh:mm:dd
TIMESTAMP 
字符型
CHAR 0-255
VARCHAR //变长
TINYTEXT
TEXT
LONGTEXT
ENUM('男'，'女')

TINYINT -128 - 127

INT 2147*****

BIGINT 2^63

FLOAT 4个字节

DOUBLE 8个字节

YEAR 1901-2155 yyyy

TIME hh:mm:dd

DATE yyyy-mm-dd

DATETIME yyyy-mm-dd hh:mm:dd

TIMESTAMP

字符型

CHAR 0-255

VARCHAR //变长

TINYTEXT

TEXT

LONGTEXT

ENUM('男'，'女')

mysql 表结构

表结构

查看
show tables (from database1);
show colunms from table1;
desc table1;
show create table table1;

创建
create table table_name (
    列名 类型 约束;
    列名 类型 约束;
);
例：
create table student (
    name varchar(10);
    age  tinyint;
);
添加列,改列名，改列类型，改列位置，删除列
alter table table1 add colunm1 类型 (after colunm2//指定位置，不指定默认最后一列);
alter table table1 change colunm1 colum2 类型;
alter table table1 modify colunm1 类型2;
alter table table1 modify colunm1 类型 after colunm2;
alter table table1 drop colunm1;


删除表，修改表名
dorp table table1, table2;
rename table info to info2;
alter table table1 rename table2;


表分区
show plugins//查看是否只是表分区
例子
create table student (
    name varchar(10);
    age  tinyint;
    partition by range(age) (
    partition p1 less than (40)
    )
);
检索p1内的数据
select * from table2 partiton(p1);

查看

show tables (from database1);

show colunms from table1;

desc table1;

show create table table1;

创建

create table table_name (

列名类型约束;

);

例：

create table student (

name varchar(10);

age tinyint;

);

添加列,改列名，改列类型，改列位置，删除列

alter table table1 add colunm1 类型 (after colunm2//指定位置，不指定默认最后一列);

alter table table1 change colunm1 colum2 类型;

alter table table1 modify colunm1 类型2;

alter table table1 modify colunm1 类型 after colunm2;

alter table table1 drop colunm1;

删除表，修改表名

dorp table table1, table2;

rename table info to info2;

alter table table1 rename table2;

表分区

show plugins//查看是否只是表分区

例子

create table student (

name varchar(10);

age tinyint;

partition by range(age) (

partition p1 less than (40)

)

);

检索p1内的数据

select * from table2 partiton(p1);

mysql 数据更新插入增加删除

数据

1.插入完整数据
insert into 表名(列名) values(`````);//不指定列名默认插入完整数据
例insert into info(name,id) values ('张三'，201601);
例insert into info values ('张三'，201601);

插入部分数据
insert into 表名(列名) values(`````);//
例insert into info(name) values ('张三');

插入多条记录
insert into 表名(列名) values(`````),(````),(`````);//括号里面数据，各个数据用','隔开;

输入查询数据
insert into 表名1 select * from 表名2 where id>5;

2.数据更新
update table1 set 列名=·· (where ``` )//没有where则默认所有行
例子
update info set name = '张三',id = id + 100 (where id>2016001);

3.删除
delete from table1 (where`````);
例子
delete from table1 (where id = 2016);

清空表
truncate table tabel

1.插入完整数据

insert into 表名(列名) values(`````);//不指定列名默认插入完整数据

例insert into info(name,id) values ('张三'，201601);

例insert into info values ('张三'，201601);

插入部分数据

insert into 表名(列名) values(`````);//

例insert into info(name) values ('张三');

插入多条记录

insert into 表名(列名) values(`````),(````),(`````);//括号里面数据，各个数据用','隔开;

输入查询数据

insert into 表名1 select * from 表名2 where id>5;

2.数据更新

update table1 set 列名=·· (where ``` )//没有where则默认所有行

例子

update info set name = '张三',id = id + 100 (where id>2016001);

3.删除

delete from table1 (where`````);

例子

delete from table1 (where id = 2016);

清空表

truncate table tabel

mysql 用户与权限管理

用户与权限管理

创建和删除用户{
    create user xmj;
    drop user xmj;
    重命名 rename user xmj to jmx;
    密码设置 set password for xmj = password('xmj123');
}
权限的赋予与删除{
    grant all on student.* to xmj;//赋予xmj在student库中所有表的所有权限(*.*代表所有库的所有表)
    revoke select on info from xmj;//
    alter insert update//等权限也可以
}
权限的查看{
    show grants for xmj;
}
数据库角色{
    create role r1;//创建角色
    grant select, insert on student.info to r1;//给角色赋予权限
    grant r1 to xmj;//给用户赋予角色的权限
}

创建和删除用户{

create user xmj;

drop user xmj;

重命名 rename user xmj to jmx;

密码设置 set password for xmj = password('xmj123');

}

权限的赋予与删除{

grant all on student.* to xmj;//赋予xmj在student库中所有表的所有权限(*.*代表所有库的所有表)

revoke select on info from xmj;//

alter insert update//等权限也可以

}

权限的查看{

show grants for xmj;

}

数据库角色{

create role r1;//创建角色

grant select, insert on student.info to r1;//给角色赋予权限

grant r1 to xmj;//给用户赋予角色的权限

}

mysql 查询 select 视图连接拼接分组组合查询

查询 select 视图连接拼接分组组合查询

1.查询
select 列名 from 表名 where;
select * from table1(where id = 5);
distinct//去重;
select distinct id from table1;

查询并分组
select count(*) from table1 (where id > 5);//统计个数,where 限定条件
select id,count(*) from table1 group by id;//按照id分组

having 分组
select id from info group by id having count(id)>1;

查询后排序//不指定则默认升序
select * from info order by id asc, name desc;//id升序，id相同按照name降序

limit限制
select * from info limit 3;//前三行
select * from info limit 4,5;//第四行开始的五行
select name,count(*) from info group by name desc limit 1;

2.where {
    id > 5;
    id = 5;
    运算符 
    = 等于
    <>不等于
    != 不等于
    <
    >
    <=
    >=
    between and//between 5 and 6;
    is null;//检查空值，where id is null;
    and , or// 链接多个运算符，and 的运算级别比or高,课余括号连用
    //例如where id > 5 and ( name < 9 or age > 8);
    in, not in 
    //id in(5,8),   id not in (5,8);匹配
    
}
3.like通配符{
    %   匹配0，1，多个任意字符
    _   只能匹配1个字符    
}
4.regexp正则表达式{

    where name regexp '.三';
    | 匹配几个字符串其中之一//例如'.pple|.dog|.cat';
    
    对于元字符，需要转义系列， 例如\\  \.  \[  \]  \+  \*  \?
    
    []  匹配多个字符的其中一个
        例如  [abcd]pple  [Aa]pple
        匹   配区间
        例如  [a-z]pple   [0-9]pple   [A-Z]pple [0-9a-zA-Z]
    [^0-9]//出0-9以外的字符
    
    [[:alnum:]]
    [[:alpha:]]
    [[:digit:]]
    [[:lower:]]
    [[:uppper:]]
            
    . 匹配任何字符
    +   匹配一个或多个字符
    *   匹配0个或多个字符
    ？   匹配0或1个字符
    {}  匹配重复次数
        例如  a{0,4}, 最少出现0次，最多4次
        a{3,}, 最少出现4次
        a{3}    匹配3次
        
}

1.查询

select 列名 from 表名 where;

select * from table1(where id = 5);

distinct//去重;

select distinct id from table1;

查询并分组

select count(*) from table1 (where id > 5);//统计个数,where 限定条件

select id,count(*) from table1 group by id;//按照id分组

having 分组

select id from info group by id having count(id)>1;

查询后排序//不指定则默认升序

select * from info order by id asc, name desc;//id升序，id相同按照name降序

limit限制

select * from info limit 3;//前三行

select * from info limit 4,5;//第四行开始的五行

select name,count(*) from info group by name desc limit 1;

2.where {

id > 5;

id = 5;

运算符

= 等于

<>不等于

!= 不等于

between and//between 5 and 6;

is null;//检查空值，where id is null;

and , or// 链接多个运算符，and 的运算级别比or高,课余括号连用

//例如where id > 5 and ( name < 9 or age > 8);

in, not in

//id in(5,8), id not in (5,8);匹配

}

3.like通配符{

% 匹配0，1，多个任意字符

_ 只能匹配1个字符

}

4.regexp正则表达式{

where name regexp '.三';

| 匹配几个字符串其中之一//例如'.pple|.dog|.cat';

对于元字符，需要转义系列，例如\\ \. \[ \] \+ \* \?

[] 匹配多个字符的其中一个

例如 [abcd]pple [Aa]pple

匹配区间

例如 [a-z]pple [0-9]pple [A-Z]pple [0-9a-zA-Z]

[^0-9]//出0-9以外的字符

[[:alnum:]]

[[:alpha:]]

[[:digit:]]

[[:lower:]]

[[:uppper:]]

. 匹配任何字符

+ 匹配一个或多个字符

* 匹配0个或多个字符

？匹配0或1个字符

{} 匹配重复次数

例如 a{0,4}, 最少出现0次，最多4次

a{3,}, 最少出现4次

a{3} 匹配3次

}

创建计算字段

1.拼接字段 {
    select concat (name , '年龄为:', age ) from info;//拼接姓名与年龄
    select concat (name , '年龄为:', age )  as new_colunm from info; //为这个拼接列起个名
｝
2.计算字段{
    + - * / 加减乘除都可用
    select age * age  from info;//计算字段
    select age * age as new_colunm from info ;//为这个字段列起个名字
｝
3.rtrim()//去掉末尾空格。例如rtrim(id);

1.拼接字段 {

select concat (name , '年龄为:', age ) from info;//拼接姓名与年龄

select concat (name , '年龄为:', age ) as new_colunm from info; //为这个拼接列起个名

｝

2.计算字段{

+ - * / 加减乘除都可用

select age * age from info;//计算字段

select age * age as new_colunm from info ;//为这个字段列起个名字

｝

3.rtrim()//去掉末尾空格。例如rtrim(id);

分组查询

select id, count(id), avg(id) from info group id;//按照id分组，并显示每组的数量和平均值

where 按照行过滤，having按照组过滤
having{
    select id , count(*) , max(id)  from info group by id having count(*) > 2;
}
where + having{
        select id , count(*) , max(id)  from info where age > 90 group by id having count(*) > 2;//where把年龄90岁下的过滤了，having不能检测到90岁以下的了，count比不用where少
        
}
分组的同时可以排序{
        select id , count(*) , max(id)  from info group by id having count(*) > 2 order by id ;
}

select id, count(id), avg(id) from info group id;//按照id分组，并显示每组的数量和平均值

where 按照行过滤，having按照组过滤

having{

select id , count(*) , max(id) from info group by id having count(*) > 2;

}

where + having{

select id , count(*) , max(id) from info where age > 90 group by id having count(*) > 2;//where把年龄90岁下的过滤了，having不能检测到90岁以下的了，count比不用where少

}

分组的同时可以排序{

select id , count(*) , max(id) from info group by id having count(*) > 2 order by id ;

}

子查询和表联结

想查询的数据列不在同一张表上{
    info中有 id, name
    info2中有 id, age
    查询张三的年龄{
        select age from info2 where id = (select id from info where name = '张三');
        select age from info2 where id in (select id from info where name = '张三');
        如果子查询的结果可能不唯一，可以用in//not in 也是可以适当用的
    ｝
    想查张三的年龄，姓名和id
    select id, name, (select age from info2 where info.id = info2.id) where id = '张三';
    //对于用重复的列名要用表名限定
    select id, name, age form info, info2 where info.id = info2.id;
    select info.id, info.name, info2.age form info, info2 where info.id = info2.id;
    //从笛卡尔积中过滤
    内连接{
         select id, name, age form info inner join info2 on info.id = info2.id;
         //与where作用相同，从笛卡尔积中过滤
    ｝
    
    子查询和联结表从多个表中的笛卡尔积中过滤，与单表过滤语法类似
    
    
    自联结{
        想知道一个表中，某个学生的辅导员的其他学生的年龄
        select age from info where fudaoyuan = (select fudaoyuan where name = '张三');
        select p1.age from info as p1, info as p2 where p1.fudaoyuan = p2.fudaoyuan and p2.name = '张三';//吧一张表看成两个表，起个名字，从笛卡尔积中过滤；
    }
    

}

想查询的数据列不在同一张表上{

info中有 id, name

info2中有 id, age

查询张三的年龄{

select age from info2 where id = (select id from info where name = '张三');

select age from info2 where id in (select id from info where name = '张三');

如果子查询的结果可能不唯一，可以用in//not in 也是可以适当用的

｝

想查张三的年龄，姓名和id

select id, name, (select age from info2 where info.id = info2.id) where id = '张三';

//对于用重复的列名要用表名限定

select id, name, age form info, info2 where info.id = info2.id;

select info.id, info.name, info2.age form info, info2 where info.id = info2.id;

//从笛卡尔积中过滤

内连接{

select id, name, age form info inner join info2 on info.id = info2.id;

//与where作用相同，从笛卡尔积中过滤

｝

子查询和联结表从多个表中的笛卡尔积中过滤，与单表过滤语法类似

自联结{

想知道一个表中，某个学生的辅导员的其他学生的年龄

select age from info where fudaoyuan = (select fudaoyuan where name = '张三');

select p1.age from info as p1, info as p2 where p1.fudaoyuan = p2.fudaoyuan and p2.name = '张三';//吧一张表看成两个表，起个名字，从笛卡尔积中过滤；

}

组合查询

union{union会过滤重复的行,不想过滤用union all
    把两个查询语句放一起，查询结果放在一个表中
    select * from info
    union
    select * from info2;
}

union{union会过滤重复的行,不想过滤用union all

把两个查询语句放一起，查询结果放在一个表中

select * from info

union

select * from info2;

}

视图

创建视图{
    creat view 视图名字 as select ................
    例如 create view test as select id, age2 from info, info2;
    本次操作结果是把数据添加到一个视图里(这是一张特殊的表)
    查看具体情况    desc test
    查看创建语句  show creat table test
    查看创建语句  show create view test 
    show tables 则会显示所有的表和视图
    更改视图名字 rename table test to test1;
    
    总之视图是一张表，特殊的表，视图继承自表；
}

创建视图{

creat view 视图名字 as select ................

例如 create view test as select id, age2 from info, info2;

本次操作结果是把数据添加到一个视图里(这是一张特殊的表)

查看具体情况 desc test

查看创建语句 show creat table test

查看创建语句 show create view test

show tables 则会显示所有的表和视图

更改视图名字 rename table test to test1;

总之视图是一张表，特殊的表，视图继承自表；

}

mysql 登陆及常用命令

登陆及常用命令

启动，关闭，检查状态
mysql.server start
mysql.server stop
mysql.server status
登陆
mysql -uroot -p
修改密码(新密码123)
ALTER USER 'root'@'localhost' IDENTIFIED by '123';

常用命令
show databases;
use test//切换到test库
select database();//查看选择的数据库
select version();//查看的数据库版本
select now();//查看时间
select user();//查看当前用户

创建、修改和删除数据库
create database (if not exists) test1 (character set utf8);
alter database (if not exists) test1 (character set **);
rename database test1 to test2;
drop database (if exists) test1;

启动，关闭，检查状态

mysql.server start

mysql.server stop

mysql.server status

登陆

mysql -uroot -p

修改密码(新密码123)

ALTER USER 'root'@'localhost' IDENTIFIED by '123';

常用命令

show databases;

use test//切换到test库

select database();//查看选择的数据库

select version();//查看的数据库版本

select now();//查看时间

select user();//查看当前用户

创建、修改和删除数据库

create database (if not exists) test1 (character set utf8);

alter database (if not exists) test1 (character set **);

rename database test1 to test2;

drop database (if exists) test1;

mysql 约束

[ ] 表示内容是可选填，不填则默认是数据库自动分配名字

1在创建表时后面直接指定约束
2在创建表时最后面直接指定约束
3通过alter增加或删除约束modify
主键和唯一可以用drop ky_id;
唯一约束用key/index
默认约束用set 


1.主键约束
单字段主键
create table student (
 &nbsp;  name varchar(10) primary key;
 &nbsp;  age  tinyint;
);
alter table table1 modify name varchar(10);//取消主键约束;
alter table table1 modify name varchar(10) [constraint key1] primary key;//添加主键约束;
alter table table1 add primary key (name);

create table student (
 &nbsp;  name varchar(10) prime key;
 &nbsp;  age  tinyint;
 &nbsp;  [constraint ky_id1] primary key (name);
);
多字段主键
create table student (
 &nbsp;  name varchar(10) prime key;
 &nbsp;  age  tinyint;
 &nbsp;  [constraint ky_id1] primary key (name，age);
);

删除、添加主键
alter table table1 add primary key (name);
alter table table1 drop primary key;


2.唯一约束
create table student (
 &nbsp;  name varchar(10) unique;
 &nbsp;  age  tinyint;
);
alter table table1 modify name varchar(10);//取消唯一约束;
alter table table1 modify name varchar(10) unique;//添加唯一约束;

删除、添加唯一约束
alter table table1 add [constraint ky_id1] unique (name);
alter table table1 drop key/index 唯一约束名;

3.非空约束
create table student (
 &nbsp;  name varchar(10) not null;
 &nbsp;  age  tinyint;
);
alter table table1 modify name varchar(10);//取消非空约束;
alter table table1 modify name varchar(10) not null;//添加非空约束;

4.默认约束
create table student (
 &nbsp;  name varchar(10) default '张三';
 &nbsp;  age  tinyint;
);
alter table table1 modify colunm1 set default '张三';//添加
alter table table1 modify colunm1 //取消
alter table table1 modify colunm1 drop default '张三';//取消
;

5.外键约束(on delete cascade)

create table student (
 &nbsp;  name varchar(10) default '张三';
 &nbsp;  age  tinyint;
 &nbsp;  [constraint k_id1] foreign key (name) references table2 (colunm1);
);
alter table table1 add [constraint k_id1] foreign key (name) references table2 (colunm1);
alter table table1 drop foreign key  约束名;


自增
aotu_increment=2;//默认增长值为1;可在初始化时设置，modify时修改(增加，取消);

设置自增数值后可修改
alter table table1 auto_increment = 5;

1在创建表时后面直接指定约束

2在创建表时最后面直接指定约束

3通过alter增加或删除约束modify

主键和唯一可以用drop ky_id;

唯一约束用key/index

默认约束用set

1.主键约束

单字段主键

create table student (

  name varchar(10) primary key;

  age tinyint;

);

alter table table1 modify name varchar(10);//取消主键约束;

alter table table1 modify name varchar(10) [constraint key1] primary key;//添加主键约束;

alter table table1 add primary key (name);

create table student (

  name varchar(10) prime key;

  age tinyint;

  [constraint ky_id1] primary key (name);

);

多字段主键

create table student (

  name varchar(10) prime key;

  age tinyint;

  [constraint ky_id1] primary key (name，age);

);

删除、添加主键

alter table table1 add primary key (name);

alter table table1 drop primary key;

2.唯一约束

create table student (

  name varchar(10) unique;

  age tinyint;

);

alter table table1 modify name varchar(10);//取消唯一约束;

alter table table1 modify name varchar(10) unique;//添加唯一约束;

删除、添加唯一约束

alter table table1 add [constraint ky_id1] unique (name);

alter table table1 drop key/index 唯一约束名;

3.非空约束

create table student (

  name varchar(10) not null;

  age tinyint;

);

alter table table1 modify name varchar(10);//取消非空约束;

alter table table1 modify name varchar(10) not null;//添加非空约束;

4.默认约束

create table student (

  name varchar(10) default '张三';

  age tinyint;

);

alter table table1 modify colunm1 set default '张三';//添加

alter table table1 modify colunm1 //取消

alter table table1 modify colunm1 drop default '张三';//取消

;

5.外键约束(on delete cascade)

create table student (

  name varchar(10) default '张三';

  age tinyint;

  [constraint k_id1] foreign key (name) references table2 (colunm1);

);

alter table table1 add [constraint k_id1] foreign key (name) references table2 (colunm1);

alter table table1 drop foreign key 约束名;

自增

aotu_increment=2;//默认增长值为1;可在初始化时设置，modify时修改(增加，取消);

设置自增数值后可修改

alter table table1 auto_increment = 5;

mysql 使用函数

字符串函数{
    1.upper
    2.lower
    3.length()
    4.left()
    5.right()
    6.rtrim()
    例如select upper(name) from info;//把名字换成大写展示
}
数学函数{
    avg();
    count();
    max();
    min();
    sum();
    例如select sum(age*age) from info;
    select min(id), max(id), sum(id), count(id) from info, avg(age);//汇总
}

字符串函数{

1.upper

2.lower

3.length()

4.left()

5.right()

6.rtrim()

例如select upper(name) from info;//把名字换成大写展示

}

数学函数{

avg();

count();

max();

min();

sum();

例如select sum(age*age) from info;

select min(id), max(id), sum(id), count(id) from info, avg(age);//汇总

}