经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库运维 » MySQL » 查看文章
MySQL基础操作
来源：cnblogs　　作者：sfencs　　时间：2018/10/18 8:53:08　　对本文有异议

数据库级别

1.显示数据库

  1. show databases;

2.创建数据库

  1. create database 数据库名;

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

  1. use 数据库名;

4.用户管理

创建用户：

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

删除用户：

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

修改用户：

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

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

也可以

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

修改密码：

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

5.用户授权

查看用户权限：

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

授予用户权限：

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

撤销用户权限：

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

权限类型：

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

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

  1. 用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
  2. 用户名@%             用户可以再任意IP下访问(默认IP地址为%)
  4. 数据库名.*           数据库中的所有
  5. *.*                所有数据库

表级别

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

  1. show tables;

2.创建表

  1. create table 表名(
  2.     列名  类型 , 
  3.     列名  类型  
  4. )ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

  1. 是否为空
  2. not null 
  3. null
  5. 设置每列的默认值
  6. defalut 1
  8. 设置列的自增
  9. auto_increment
  10. 自增必须设置到索引列中
  11. 自增可以设置起始值和步长
  12. set id auto_increment_increment=2;--起始值
  13. set id auto_increment_offset=2;--步长
  15. 主键
  16. primary key

    外键
    将表的一列与另一张表的一列相关联，使这一列的内容只能是相关联的那一列的内容
  1. constraint 外键名 foreign key (本表的某列) references color(其他表的某列);

    举例
  1. create table student(
  2.                 sid int not null auto_increment primary key,
  3.                 course_id int not null defalut 1,
  4.                 constraint fk_sc foreign key (course_id) references course(cid)
  5.             );

 3.删除表

  1. drop table 表名;

4.清空表

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

5.修改表

  1. 添加列：alter table 表名 add 列名 类型;
  2. 删除列：alter table 表名 drop column 列名;
  3. 修改列：
  4.         alter table 表名 modify column 列名 类型;  --修改类型
  5.         alter table 表名 change 原列名 新列名 类型; 
  6.   
  7. 添加主键：
  8.         alter table 表名 add primary key(列名);
  10. 删除主键：
  11.         alter table 表名 drop primary key;
  12.         alter table 表名  modify  列名 类型, drop primary key;
  13.   
  14. 添加外键：alter table 本表 add constraint 外键名称 foreign key 本表外键列 references 引用外键表列;
  15. 删除外键：alter table 表名 drop foreign key 外键名称
  16.   alter table 表名modify 字段名default 默认值
  18. 修改默认值：alter table 表名modify 字段名default 默认值;
  19. 删除默认值：ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT;

6.基本数据类型

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

7.索引

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

普通索引：

  1. 创建表时创建索引
  2. create table name(
  3.     nid int not null ,
  4.     index 索引名 (nid)
  5. );
  7. 添加索引
  8. create index 索引名 on 表名(列名);
  10. 删除索引
  11. drop 索引名 on 表名;
  13. 查看索引
  14. show index from 表名;

唯一索引：

  1. 创建表时创建唯一索引
  2. create table name(
  3.     nid int not null  
  4.     unique 索引名(nid)
  5. );
  7. 添加唯一索引
  8. create unique index 索引名 on 表名(列名);
  10. 删除唯一索引
  11. drop unique index 索引名 on 表名;

主键索引：

  1. 创建表时创建索引
  2. create table name(
  3.     nid int not null auto_increment primary key,
  4.     index 索引名 (nid)
  5. );
  7. 添加主键
  8. alter table 表名 add primary key(列名);
  10. 删除主键
  11. alter table 表名 drop primary key;

组合索引：

将多个列组合成一个索引

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

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

表内容级别

1.增加

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

2.删除

  1. delete from 表
  2. delete from 表 where ......;

3.修改

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

4.查看

  1. select * from 表;
  2. select 列名,列名,列名 from 表 where 条件;

5.选择的条件方式

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

 6.排序

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

7.分组

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

8.表的连接

  1. 不符合条件的不会显示
  2. select  A.name, B.name
  3.     from A,B
  4.     Where A.nid = B.nid;
  6. 以左边的为基础，左边的表都显示，若B中没有对应关系的列则显示为null
  7. select  A.name, B.name
  8.     from A left join B
  9.     on A.nid = B.nid;
  11. 以右边的为基础，左边的表都显示，若A中没有对应关系的列则显示为null
  12. select  A.name, B.name
  13.     from A right join B
  14.     on A.nid = B.nid;
  16. 两个表互相约束，和上两个方法类似，但把有null的行都去掉了
  17. select A.name, B.name
  18.     from A inner join B
  19.     on A.nid = B.nid

9.组合

  1. 去除重复
  2. SELECT column_name(s) FROM table1
  3. UNION
  4. SELECT column_name(s) FROM table2;
  6. 不去除重复
  7. SELECT column_name(s) FROM table1
  8. UNION ALL
  9. SELECT column_name(s) FROM table2;
  11. 组合后的列名为第一个select语句中的列名

10.视图

  1. 创建视图
  2. CREATE VIEW 视图名 AS sql语句
  3. CREATE VIEW view_name AS
  4. SELECT column_name(s)
  5. FROM table_name
  6. WHERE condition;
  8. 删除视图
  9. DROP VIEW 视图名
  11. 修改视图
  12. 将视图名换一个表示的视图
  13. ALTER VIEW 视图名称 AS SQL语句;
  14. 无法通过对视图的更新来达成对真实表的更新
  16. 查询视图
  17. 可将视图看作一个表来查询

11.存储过程

创建存储过程：

  1. delimiter //    --将语句终止符;改为//
  2. create procedure p1()
  3. BEGIN
  4.     select * from table1;
  5. END//
  6. delimiter ;    --将语句终止符改回;

调用存储过程

  1. call p1();

有参数的存储过程

  1. delimiter \create procedure p1(
  2.     in i int,          --in表示传入变量，out表示传出变量，inout表示既传入又传出变量
  3.     inout j int,
  4.     out o int
  5. )
  6. BEGIN  
  7.     set i3 = i+o;
  8. end\delimiter ;
  10. set @i1=1;       --变量加@表示用户变量，不加表示局部变量
  11. set @i2=2;
  12. call p1(2,@i1,@i2);
  13. select @i1,@i2;

删除存储过程

  1. drop procedure 存储过程名;

12.函数

内置函数：

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

自定义函数：

  1. delimiter \create function func(
  2.     i1 int,
  3.     i2 int)
  4. returns int
  5. BEGIN
  6.     declare num int;   --必须用declare申明
  7.     set num = i1 + i2;
  8.     return(num);
  9. END \delimiter ;

删除函数：

  1. drop function 函数名;

使用函数

  1. select name from table1 where nid=func(nid);
  3. 查看返回值
  4. declare @i int;
  5. select func(2) into @i;  --将一个表的信息复制到另一个表
  6. select @i;

 13.事务

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

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

执行事务

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

事务回滚

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

事务与存储过程结合使用

  1. delimiter \create PROCEDURE p1(
  2.     out p_return  int
  3. )
  4. BEGIN 
  5.   DECLARE exit handler for sqlexception 
  6.   BEGIN 
  7.     -- ERROR 
  8.     set p_return = 1; 
  9.     rollback; 
  10.   END; 
  11.  
  12.   DECLARE exit handler for sqlwarning 
  13.   BEGIN 
  14.     -- WARNING 
  15.     set p_return = 2; 
  16.     rollback; 
  17.   END; 
  18. START TRANSACTION;

    DELETE from name_table where name='aaa';

    insert into name_table(name)values('sfencs');
  1. COMMIT; 
    -- SUCCESS  
    set p_return = 0;
         END\\ 
    delimiter ;

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

14.触发器

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

创建触发器：

  1. 插入前
  2. CREATE TRIGGER 触发器名 BEFORE INSERT ON table1 FOR EACH ROW
  3. BEGIN
  4.     ...
  5. END
  7. 插入后
  8. CREATE TRIGGER 触发器名 AFTER INSERT ON table1 FOR EACH ROW
  9. BEGIN
  10.     ...
  11. END

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

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

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

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

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

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

删除触发器：

  1. DROP TRIGGER 触发器名;

 15.sql中的if语句

  1.  if 条件 THEN
  2.         语句1;
  3.     ELSEIF 条件 THEN
  4.         语句2;
  5.     ELSE
  6.         语句3;
  7.     END IF;

16.sql中的循环语句

  1. WHILE 条件 DO
  2.        语句;
  3.     END WHILE ;

或

  1. repeat
  2.         语句;
  3.         until 条件
  4.     end repeat;

或

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

 

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MySQL热门文章

  • 大项目之网上书城（九）——订单Demo
  • CentOS7下MySQL服务启动失败原因及解决方法
  • MySQL从删库到跑路_高级（六）——索引
  • 55 道MySQL基础题
  • MySQL与PHP的基础与应用专题之自连接
  • 树形结构的菜单表设计与查询
  • MYSQL语句
  • 《高性能MySQL（第3版）》PDF免费下载
  • 《MySQL必知必会》知识汇总二
  • 【必知必会的MySQL知识】④DCL语言

    • MySQL推荐文章

  • 如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）
  • MySQL中为什么要使用索引合并(Index Merge)？
  • MySQL派生表合并优化的原理和实现
  • 使用EF 连接 数据库 SQLserver、MySql 实现 CodeFirst
  • 升级到 MySQL 8.4，MySQL 启动报错：io_setup() failed with EAGAIN
  • TiKV 源码分析之 PointGet
  • 异构数据源同步之数据同步 → DataX 使用细节
  • 一文搞懂 MySQL 日志
  • 浅析MySQL 8.0直方图原理
  • 分布式任务调度内的 MySQL 分页查询优化

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号