MySQL基础操作

数据库级别

1.显示数据库

show databases;

2.创建数据库

create database 数据库名;

3.使用数据库，进入数据库

use 数据库名;

4.用户管理

创建用户：

create user '用户名'@'IP地址' identified by '密码';

删除用户：

drop user '用户名'@'IP地址';

修改用户：

可以进入MySQL的user表中进行更改

use mysql;
update user set user ='新用户名' where user ='旧用户名';

也可以

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

修改密码：

set password for '用户名'@'IP地址' = Password('新密码');

5.用户授权

查看用户权限：

show grants for '用户'@'IP地址' ;

授予用户权限：

grant  权限 on 数据库.表 to   '用户'@'IP地址';

撤销用户权限：

revoke 权限 on 数据库.表 from '用户'@'IP地址';

权限类型：

CREATE    允许创建新的数据库和表    
DROP            允许删除现有数据库、表和视图    
GRANT OPTION    能够把自己获得的权限转赠给其他用户一个副本    
LOCK TABLES    允许使用显式锁表的语句锁表,前提是你必须有SELECT权限    
REFERENCES    MySQL 5.7.6之前引用特权是未使用的。5.7.6,创建一个外键约束     需要引用父表的特权。    
EVENT    事件特权需要创建、更改、删除或查看事件调度器的事件。    
ALTER            允许修改权限或重命名表    
DELETE    允许从表中或数据库中删除行    
INDEX    允许创建或删除索引    
INSERT    允许在表和数据库中插入行    
SELECT    允许在表和数据库这查询行    
UPDATE    允许在表和数据空中更新行    
CREATE TEMPORARY TABLES    允许创建和使用临时表    
TRIGGER    允许使用触发器相关,前提是你必须拥有这个表的CREATE、DROP、EXECUTE或者Display权限    
CREATE VIEW    允许使用CREATE VIEW语句    
SHOW VIEW    允许使用SHOW VIEW语句    
ALTER ROUTINE    ALTER ROUTINE允许你修改存储过程(过程或函数)前提是你需要在存储过程上拥有ALTER或DROP权限    
CREATE ROUTINE    ALTER ROUTINE    允许你创建存储过程(过程或函数)前提是你需要在存储过程上拥有ALTER或DROP权限    
EXECUTE    允许你执行存储过程(过程或函数)    
FILE        文件读写权限允许你文件在服务器主机上使用LOAD DATA INFILE and SELECT 语句以及LOAD DATA()函数    
CREATE TABLESPACE    允许创建表空间,前提是你需要CREATE、ALTER OR DROP tablespaces 以及属于log file 组
CREATE USER    允许使用ALTER USER, CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES 语句    
PROCESS        允许使用SHOW PROCESSLIST or mysqladmin processlist看到线程属于其他账户,你总能看到自己的线程。PROCESS权限还允许使用SHOW ENGINE语句    
PROXY         
RELOAD    允许使用FLUSH语句,以及mysqladmin命令中对于FLUSH的操作例如:flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.    
REPLICATION CLIENT    是否有权限去请求复制相关主数据库产生的事件的,允许显示的使用SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOGS语句    
REPLICATION SLAVE    是否能够创建连接以复制的方式,连接至数据库服务器端    
SHOW DATABASES    允许使用SHOW DATABASES来查看所有数据库列表,如果没有这个权限则只能查看规定数据库    
SHUTDOWN    允许使用SHUTDOWN语句,mysqladmin shutdown命令,以及mysql_shutdown()API函数    
SUPER    超级特权可以用于终止其他会话或改变服务器运行。属于特别高级别的一类权限    
USAGE    无权限,当您想要创建一个没有权限的用户时，可以指定USAGE    
ALL PRIVILEGES    所有权限: ALL PRIVILEGES,可以简写为ALL            

 对于授权语句中，可以使用通配符，如：

用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
用户名@%             用户可以再任意IP下访问(默认IP地址为%)

数据库名.*           数据库中的所有
*.*                所有数据库

表级别

1.显示当前使用的数据库中所有表：

show tables;

2.创建表

create table 表名(
    列名  类型 , 
    列名  类型  
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在创建表的同时还可以规定:

是否为空
not null 
null

设置每列的默认值
defalut 1

设置列的自增
auto_increment
自增必须设置到索引列中
自增可以设置起始值和步长
set id auto_increment_increment=2;--起始值
set id auto_increment_offset=2;--步长

主键
primary key

外键
将表的一列与另一张表的一列相关联，使这一列的内容只能是相关联的那一列的内容

constraint 外键名 foreign key (本表的某列) references color(其他表的某列);

举例

create table student(
                sid int not null auto_increment primary key,
                course_id int not null defalut 1,
                constraint fk_sc foreign key (course_id) references course(cid)
            );

 3.删除表

drop table 表名;

4.清空表

delete from 表名   --不会删除自增的数量，根据之前的自增序号继续自增
truncate table 表名

5.修改表

添加列：alter table 表名 add 列名 类型;
删除列：alter table 表名 drop column 列名;
修改列：
        alter table 表名 modify column 列名 类型;  --修改类型
        alter table 表名 change 原列名 新列名 类型; 
  
添加主键：
        alter table 表名 add primary key(列名);

删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 类型, drop primary key;
  
添加外键：alter table 本表 add constraint 外键名称 foreign key 本表外键列 references 引用外键表列;
删除外键：alter table 表名 drop foreign key 外键名称
  alter table 表名modify 字段名default 默认值

修改默认值：alter table 表名modify 字段名default 默认值;
删除默认值：ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT;

6.基本数据类型

bit  二进制
tinyint  -128 ～ 127
int   -2147483648 ～ 2147483647
bigint  -9223372036854775808 ～ 9223372036854775807
decimal   精确的小数
float  0  1.175494351E-38 to 3.402823466E+38
double 0 2.2250738585072014E-308 to 1.7976931348623157E+308
char (m)  固定长度的字符串，m为固定长度，最大为255
varchar(m)  非固定长度的字符串，m为最大长度 最大为255
text  保存变长的长字符串，可以最多到65535
mediumtext  最多2**24 − 1
longtext  最多2**32 − 1
enum  枚举类型，如season ENUM('spring', 'summer', autumn', 'winter')
set 集合类型，如myset set('a', 'b', 'c', 'd')，集合类型可以是集合中的组合（'a,b,c'）
date  YYYY-MM-DD
time  HH:MM:SS
year  YYYY
DATETIME  YYYY-MM-DD HH:MM:SS
TIMESTAMP

7.索引

索引可以加速查询，使用B+Tree实现其索引结构。

普通索引：

创建表时创建索引
create table name(
    nid int not null ,
    index 索引名 (nid)
);

添加索引
create index 索引名 on 表名(列名);

删除索引
drop 索引名 on 表名;

查看索引
show index from 表名;

唯一索引：

创建表时创建唯一索引
create table name(
    nid int not null  
    unique 索引名(nid)
);

添加唯一索引
create unique index 索引名 on 表名(列名);

删除唯一索引
drop unique index 索引名 on 表名;

主键索引：

创建表时创建索引
create table name(
    nid int not null auto_increment primary key,
    index 索引名 (nid)
);

添加主键
alter table 表名 add primary key(列名);

删除主键
alter table 表名 drop primary key;

组合索引：

将多个列组合成一个索引

创建索引
create index 索引名 on 表名(列名1，列名2);

组合索引的查询是取左的方式，例如查询  列1 and 列2 ，会使用索引，查询列1会使用索引，查询列2不会使用索引。

表内容级别

1.增加

insert into 表 (列名,列名...) values (值,值,值...);
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);
insert into 表 (列名,列名...) select (列名,列名...) from 表;

2.删除

delete from 表
delete from 表 where ......;

3.修改

update 表 set name ＝ 'a' where 条件;

4.查看

select * from 表;
select 列名,列名,列名 from 表 where 条件;

5.选择的条件方式

可以使用and ,or
between a and b
in 和 not in
where name like 'sfenc_' 以sfenc开头的之后有一个字符的名字
where name like 'sfenc%' 以sfenc开头的之后有多个字符的名字
select * from 表 limit 3;前3行
select * from 表 limit 3,4; 从第三行开始的4行

 6.排序

select * from 表 order by 列 asc;              -- 根据 “列” 从小到大排列
select * from 表 order by 列 desc;             -- 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc;    -- 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

7.分组

select num from 表 group by num;
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid;
select num from 表 group by num having max(id) > 10   --having后的是分组后的条件
除此之外，group by 必须在where之后，order by之前

8.表的连接

不符合条件的不会显示
select  A.name, B.name
    from A,B
    Where A.nid = B.nid;

以左边的为基础，左边的表都显示，若B中没有对应关系的列则显示为null
select  A.name, B.name
    from A left join B
    on A.nid = B.nid;

以右边的为基础，左边的表都显示，若A中没有对应关系的列则显示为null
select  A.name, B.name
    from A right join B
    on A.nid = B.nid;

两个表互相约束，和上两个方法类似，但把有null的行都去掉了
select A.name, B.name
    from A inner join B
    on A.nid = B.nid

9.组合

去除重复
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

不去除重复
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

组合后的列名为第一个select语句中的列名

10.视图

创建视图
CREATE VIEW 视图名 AS sql语句
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

删除视图
DROP VIEW 视图名

修改视图
将视图名换一个表示的视图
ALTER VIEW 视图名称 AS SQL语句;
无法通过对视图的更新来达成对真实表的更新

查询视图
可将视图看作一个表来查询

11.存储过程

创建存储过程：

delimiter //    --将语句终止符;改为//
create procedure p1()
BEGIN
    select * from table1;
END//
delimiter ;    --将语句终止符改回;

调用存储过程

call p1();

有参数的存储过程

delimiter \\
create procedure p1(
    in i int,          --in表示传入变量，out表示传出变量，inout表示既传入又传出变量
    inout j int,
    out o int
)
BEGIN  
    set i3 = i+o;
end\\
delimiter ;

set @i1=1;       --变量加@表示用户变量，不加表示局部变量
set @i2=2;
call p1(2,@i1,@i2);
select @i1,@i2;

删除存储过程

drop procedure 存储过程名;

12.函数

内置函数：

MySQL提供了很多内置函数，具体可参考https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

自定义函数：

delimiter \\
create function func(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;   --必须用declare申明
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

删除函数：

drop function 函数名;

使用函数

select name from table1 where nid=func(nid);

查看返回值
declare @i int;
select func(2) into @i;  --将一个表的信息复制到另一个表
select @i;

 13.事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务就是有多个操作，要么一起执行，要么就都不执行。

执行事务

START TRANSACTION; --也可以用begin
    DELETE from name_table where name='aaa';
    insert into name_table(name)values('sfencs');
  COMMIT; --提交事务

事务回滚

rollback;  --回滚到执行事务之前

事务与存储过程结合使用

delimiter \\
create PROCEDURE p1(
    out p_return  int
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return = 2; 
    rollback; 
  END; 
START TRANSACTION;

DELETE from name_table where name='aaa';

insert into name_table(name)values('sfencs');

COMMIT; 
-- SUCCESS  
set p_return = 0;
 END\\ 
delimiter ;

set @p=0; 
call p1(@p);
select @p;

14.触发器

触发器的用途为用户对表进行增删改的操作的前或后，同时进行另一个操作。

创建触发器：

插入前
CREATE TRIGGER 触发器名 BEFORE INSERT ON table1 FOR EACH ROW
BEGIN
    ...
END

插入后
CREATE TRIGGER 触发器名 AFTER INSERT ON table1 FOR EACH ROW
BEGIN
    ...
END

除此之外还有删除delete前后和更新update前后
这里定义触发器时一般也需要使用delimiter来改变语句结束符号

对于begin和end之间执行的语句，如果想根据想删除，插入，更新顶点数据进行判别，动态的执行相关语句时，那么就要用一种方式来表示数据，使用new和old来表示

当使用insert语句的时候，如果原表中没有数据的话，那么对于插入数据后表来说新插入的那条数据就是new

当使用delete语句的时候，删除的那一条数据相对于删除数据后表的数据来说就是old

当使用update语句的时候，当修改原表数据的时候相对于修改数据后表的数据来说原表中修改的那条数据就是old，而修改数据后表被修改的那条数据就是new

举例：old.name,就是原表中数据中的名字，new.id就是新数据中的id。

删除触发器：

DROP TRIGGER 触发器名;

 15.sql中的if语句

 if 条件 THEN
        语句1;
    ELSEIF 条件 THEN
        语句2;
    ELSE
        语句3;
    END IF;

16.sql中的循环语句

WHILE 条件 DO
       语句;
    END WHILE ;

或

repeat
        语句;
        until 条件
    end repeat;

或

loop_name:loop
       语句;
　　　　语句;
　　　　if 条件 then
　　　　　　iterate  loop_name;  --回到loop开始的位置
　　　　if 条件 THEN 
　　　　　　leave loop_name;  --跳出循环
　　　　end loop;