MySql命令

目录

• 开启关闭
• 登录退出
• 常用语句
  • 1. 去除重复
  • 2. 计算和(以学生表为例)
  • 3. 重命名
  • 4. 排序
  • 5.聚合函数
  • 6.分组查询
  • 7.分页查询
  • 8.约束
  • 9.级联操作
  • 10.多表查询
  • 11.事务
    • 操作
  • 12.事务隔离级别
  • 13. DCL
• 运算符
  • 1.算数运算符
  • 2.比较运算符
  • 3.逻辑运算符
  • 4.位运算符
• 数据库设计
  • 1.多表之间关系
  • 2.范式
• 数据库备份和还原
  • 1.命令行
  • 2.图形化工具
• 数据库操作
  • 1. 查看表空间的名称及大小
  • 2. 查看表空间物理文件的名称及大小
  • 3. 查看回滚段名称及大小
  • 4. 查看控制文件
  • 5. 查看日志文件
  • 6. 查看表空间的使用情况
  • 7. 查看数据库库对象
  • 8. 查看数据库的版本
  • 9. 查看数据库的创建日期和归档方式
  • 10. 捕捉运行很久的SQL
  • 11. 查看数据表的参数信息
  • 12. 查看还没提交的事务
  • 13. 查找object为哪些进程所用
  • 14. 回滚段查看
  • 15. 耗资源的进程（top session）
  • 16. 查看锁（lock）情况
  • 17. 查看等待（wait）情况
  • 18. 查看sga情况
  • 19. 查看catched object
  • 20. 查看V$SQLAREA
  • 21. 查看object分类数量
  • 22. 按用户查看object种类
  • 23. 有关connection的相关信息
    • 1. 查看有哪些用户连接
    • 2.根据v.sid查看对应连接的资源占用等情况
    • 3.根据sid查看对应连接正在运行的sql
  • 24. 查询表空间使用情况
  • 25. 查询表空间的碎片程度
  • 26. 查询有哪些数据库实例在运行

开启关闭

• 开启

net start mysql

• 关闭

net stop mysql

登录退出

• 登录

mysql -uusername -ppassword //(username指用户名，password指密码)   

或者：

mysql -uusername -p     //    回车输入密码

• 退出

exit

或：

quit

• 远程访问

mysql -hip -uusername -ppassword  //(ip连接的ip)

或者

mysql --host=ip --user=username --password=password

常用语句

1. 去除重复

select DISTINCT 字段名 From 表名;

• 要查询的数据都一样才会被去除

2. 计算和(以学生表为例)

SELECT NAME,math+english FROM student;

• 如果有null参与计算，计算结果都是null
  解决方法如下：

SELECT NAME,math+IFNULL(english,0) FROM student;//将有可能为空的字段前加`IFNULL`并用括号括住，将想要替换的数放在其后，中间用逗号隔开

3. 重命名

SELECT NAME,math+english AS 总分 FROM student; //使用AS重命名 或者空格替代

4. 排序

关键字order by

SELECT * FROM student ORDER BY math; //默认按照升序排列 ASC
SELECT * FROM student ORDER BY math DESC; //降序 DESC

如果按照两个字段进行排序,其中一个值相同则按照另一个排序

SELECT * FROM 表名 ORDER BY 字段一 ASC 字段二 ASC; 
SELECT * FROM student ORDER BY math ASC english ASC; //数学相同按照英语排序

5.聚合函数

将一列数据作为一个整体，进行纵向计算

注意:聚合元素的计算，排除null值

count:计算个数

SELECT COUNT(字段名) FROM 表名; 

解决为null问题

SELECT COUNT(IFNULL(字段名,0)) FROM 表名; 
SELECT COUNT((*) FROM 表名; //*一般为主键

max :计算最大值

 SELECT MAX(字段名) FROM 表名; 

min:计算最小值

 SELECT MIN(字段名) FROM 表名; 

sum:计算和

 SELECT SUM(字段名) FROM 表名; 

avg:计算平均值

 SELECT AVG(字段名) FROM 表名; 

6.分组查询

关键字GROUP BY 语法：GROUP BY 分组字段
注意：

1. 分组之后查询的字段：分组字段、聚合函数

   SELECT 字段名 AVG(字段名) FROM 表名 GROUP BY 字段名; 
   //如：SELECT sex, AVG(math),COUNT(id) FROM student GROUP BY sex;

2. 在where和having的区别

1、where在分组之前进行限定，如果不满足条件，则不参与分组，having在分组之后  进行限定，如果不满足不显示。  
2、where后不可以跟聚合函数，having可以进行聚合函数的判断。

7.分页查询

关键字:limit
语法：limit开始的索引，每页查询的条数

• 开始的索引=（当前的页码 -1 ）*每页显示的条数

SELECT * FROM 表名 LIMIT 开始的索引,查询的条数;
SELECT * FROM student LIMIT 0,3;//查询学生表的前三行

8.约束

9.级联操作

• 级联更新
  • 在添加外键时，设置级联更新。

ON UPFATE CASADE        //在添加外键时添加至外键后

• 级联删除
  • 在添加外键时，设置级联删除。

ON DELETE CASCADE

提醒：级联删除慎用

10.多表查询

• 笛卡尔积

  例如：有两个集合A、B,取两个集合的所有组成情况。A的个数乘B的个数条数据。

  要完成多表查询，需要消除无用的数据，加限定条件。

• 查询方式

  • 内连接查询

    1. 隐式内连接

    2. 显示内连接

       select 字段列表 from 表名1 [inner] join 表名2 on 条件
       

  • 外连接

    1. 左外连接

       select  字段列表 from 表1 left [outer] join 表2 on 条件//左表所有数据以及其交集部分
       

    2. 右外连接

       select  字段列表 from 表1 left [right] join 表2 on 条件//右表所有数据以及其交集部分
       

    3. 全外连接

       select  字段列表 from 表1 full join 表2 on 条件//两表所有数据以及其交集部分
       

  • 子查询

    当一个查询是另一个查询的条件时，称之为子查询。

    子查询可以使用几个简单命令构造功能强大的复合命令。

    子查询最常用于SELECT-SQL命令的WHERE子句中。

    子查询是一个 SELECT 语句，它嵌套在一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。

11.事务

事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

事务是恢复和并发控制的基本单位。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（atomicity） 一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（consistency) 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability） 持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

操作

Mysql数据库中事务是默认自动提交的

• 自动提交

  • mysql自动提交

  • 一条DML语句自动提交一次事务

• 手动提交

  • 需开启后提交

查看默认提交方式

Select @@autocommit;//1代表自动提交 ，0代表手动提交

修改默认提交方式

set @@autocommit = 0;

• 开启事务

  START TRANSACTION;
  

• 回滚

  ROLLBACK;
  

• 提交

  COMMIT;
  

12.事务隔离级别

参考

13. DCL

• SQL分类

  1. DDL:操作数据库和表
  2. MDL:增删改数据库中的数据
  3. DQL:查询表中数据
  4. DCL:管理用户，授权

• DBA:数据库管理人员

• DCL:管理用户，授权

  • 管理用户

    1. 添加用户

       create user '用户名'@'主机名' identified by '密码';
       

    2. 删除用户

       DROP USER '用户名'@'主机名';
       

    3. 修改用户密码

       UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
       

    4. 查询用户

       SELECT * FROM USER;
       

       • %表示可以在任意主机使用用户登录数据库

  • 权限管理

    	SHOW GRANK FOR '用户名'@'主机名';
    

  • 授予权限

    GRANT 权限列表 ON 数据库名.表名 to '用户名'@'主机名';
    

运算符

1.算数运算符

MySQL 支持的算术运算符包括:

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

2.比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

模糊查询

select * from student name like"___";//名字是三个字 
select * from student name like"杨%";  //查询姓杨 
select * from student name like "_成";  //查询第二个字是成
select * from student name like "%杨%"; //查询名字中含有杨字

3.逻辑运算符

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

4.位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符号	作用
&	按位与
|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

此表摘自：菜鸟教程

数据库设计

1.多表之间关系

• 一对一

• 一对多

• 多对多
  

2.范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）

• 1、第一范式（1NF

所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项.

• 2、第二范式（2NF）
  在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

• 3、第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖

• 其他范式参考百度百科

数据库备份和还原

1.命令行

• 备份：语法：mysqldump -u用户名 -p密码  > 保存的路径

• 还原：

  • 登录

    创建数据库

    使用数据库

    执行文件source 文件路径

2.图形化工具

数据库操作

1. 查看表空间的名称及大小

    select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
    from dba_tablespaces t, dba_data_files d
    where t.tablespace_name = d.tablespace_name
    group by t.tablespace_name;

2. 查看表空间物理文件的名称及大小

    select tablespace_name, file_id, file_name,
    round(bytes/(1024*1024),0) total_space
    from dba_data_files
    order by tablespace_name;

3. 查看回滚段名称及大小

    select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,
    (next_extent/1024) NextExtent, max_extents, v.curext CurExtent
    From dba_rollback_segs r, v$rollstat v
    Where r.segment_id = v.usn(+)
    order by segment_name ;

4. 查看控制文件

    select name from v$controlfile;

5. 查看日志文件

    select member from v$logfile;

6. 查看表空间的使用情况

    select sum(bytes)/(1024*1024) as free_space,tablespace_name 
    from dba_free_space
    group by tablespace_name;

    SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
    (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
    FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

7. 查看数据库库对象

    select owner, object_type, status, count(*) count# 
    from all_objects 
    group by owner, object_type, status;

8. 查看数据库的版本

    Select version FROM Product_component_version 
    Where SUBSTR(PRODUCT,1,6)='Oracle';

9. 查看数据库的创建日期和归档方式

    Select Created, Log_Mode, Log_Mode From V$Database; 

10. 捕捉运行很久的SQL

    column username format a12 
    column opname format a16 
    column progress format a8 

    select username,sid,opname, round(sofar*100 / totalwork,0) || '%' as progress, time_remaining,sql_text 
    from v$session_longops , v$sql 
    where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value 

11. 查看数据表的参数信息

    SELECT   partition_name, high_value, high_value_length, tablespace_name,
            pct_free, pct_used, ini_trans, max_trans, initial_extent,
            next_extent, min_extent, max_extent, pct_increase, FREELISTS,
            freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
            empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
            last_analyzed
    FROM dba_tab_partitions
    --WHERE table_name = :tname AND table_owner = :towner
    ORDER BY partition_position

12. 查看还没提交的事务

    select * from v$locked_object;
    select * from v$transaction;

13. 查找object为哪些进程所用

    select 
    p.spid,
    s.sid,
    s.serial# serial_num,
    s.username user_name,
    a.type  object_type,
    s.osuser os_user_name,
    a.owner,
    a.object object_name,
    decode(sign(48 - command),
    1,
    to_char(command), 'Action Code #' || to_char(command) ) action,
    p.program oracle_process,
    s.terminal terminal,
    s.program program,
    s.status session_status   
    from v$session s, v$access a, v$process p   
    where s.paddr = p.addr and
        s.type = 'USER' and   	
        a.sid = s.sid   and
    a.object='SUBSCRIBER_ATTR'
    order by s.username, s.osuser

14. 回滚段查看

    select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents, 
    v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets Gets, 
    v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status 
    from v$rollstat, sys.dba_rollback_segs, v$rollname 
    where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and 
    v$rollstat.usn (+) = v$rollname.usn order by rownum

15. 耗资源的进程（top session）

    select s.schemaname schema_name, decode(sign(48 - command), 1, to_char(command), 
    'Action Code #' || to_char(command) ) action, status session_status, 
    s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,  
    nvl(s.username, '[Oracle process]') user_name, s.terminal terminal, s.program program,  	st.value criteria_value  
    from v$sesstat st,  	v$session s  , v$process p   
    where st.sid = s.sid and  	st.statistic# = to_number('38') and ('ALL' = 'ALL' or s.status = 'ALL') 
    and p.addr = s.paddr 
    order by st.value desc,  p.spid asc, s.username asc, s.osuser asc

16. 查看锁（lock）情况

    select /*+ RULE */ ls.osuser os_user_name,  	ls.username user_name,  	
    decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 
    'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,  	
    o.object_name object,  	decode(ls.lmode, 1, null, 2, 'Row Share', 3, 
    'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null) 
    lock_mode,   	o.owner,  	ls.sid,  	ls.serial# serial_num,  	ls.id1,  	ls.id2    
    from sys.dba_objects o, (  	select s.osuser,  		s.username,  		l.type,   		
    l.lmode,  		s.sid,  		s.serial#,  		l.id1,  		l.id2  	from v$session s,   		
    v$lock l  	where s.sid = l.sid ) ls  where o.object_id = ls.id1 and   	o.owner 
    <> 'SYS'   order by o.owner, o.object_name

17. 查看等待（wait）情况

    SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value 
    FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets', 
    'consistent gets') group by v$waitstat.class, v$waitstat.count

18. 查看sga情况

    SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC

19. 查看catched object

    SELECT owner, name, db_link, namespace,  
            type, sharable_mem, loads, executions,   
            locks, pins, kept        
    FROM v$db_object_cache

20. 查看V$SQLAREA

    SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, 
    SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, 
    USERS_OPENING, EXECUTIONS, USERS_EXECUTING, LOADS, 
    FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
    BUFFER_GETS, ROWS_PROCESSED 
    FROM V$SQLAREA

21. 查看object分类数量

    select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 , 
    'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from 
    sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 
    , 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select 
    'COLUMN' , count(*) 
    from sys.col$ union select 'DB LINK' , count(*) from 

22. 按用户查看object种类

    select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes, 
    sum(decode(o.type#, 2, 1, NULL)) tables, 	sum(decode(o.type#, 3, 1, NULL))  clusters,  	sum(decode(o.type#, 4, 1, NULL)) views, 	sum(decode(o.type#, 5, 1, NULL)) synonyms,  	sum(decode(o.type#, 6, 1, NULL)) sequences, 
    sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1)) others   
    from sys.obj$ o, sys.user$ u   
    where o.type# >= 1 and u.user# = o.owner# and u.name <> 'PUBLIC'   
    group by u.name    
    order by sys.link$ 
    union 
    select 'CONSTRAINT' , count(*) from sys.con$

23. 有关connection的相关信息

1. 查看有哪些用户连接

    select s.osuser os_user_name,   	decode(sign(48 - command), 1, to_char(command),
    'Action Code #' || to_char(command) ) action, 	  	p.program oracle_process, 	  	
    status session_status,   	s.terminal terminal,   	s.program program,   	
    s.username user_name,   	s.fixed_table_sequence activity_meter,   	'' query,   	
    0 memory,   	0 max_memory,     0 cpu_usage,   	s.sid,  	s.serial# serial_num    
    from v$session s,   	v$process p   where s.paddr=p.addr and   	s.type = 'USER'  
    order by s.username, s.osuser

2.根据v.sid查看对应连接的资源占用等情况

    select n.name, 
            v.value, 
            n.class,
            n.statistic#  
    from  v$statname n, 
            v$sesstat v 
    where v.sid = 71 and 
            v.statistic# = n.statistic# 
    order by n.class, n.statistic#

3.根据sid查看对应连接正在运行的sql

    select /*+ PUSH_SUBQ */
            command_type, 
            sql_text, 
            sharable_mem, 
            persistent_mem, 
            runtime_mem, 
            sorts, 
            version_count, 
            loaded_versions, 
            open_versions, 
            users_opening, 
            executions, 
            users_executing, 
            loads, 
            first_load_time, 
            invalidations, 
            parse_calls, 
            disk_reads, 
            buffer_gets, 
            rows_processed,
            sysdate start_time,
            sysdate finish_time,
            '>' || address sql_address,
            'N' status 
    from v$sqlarea
    where address = (select sql_address from v$session where sid = 71)

24. 查询表空间使用情况

    select a.tablespace_name "表空间名称",
        100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
        round(a.bytes_alloc/1024/1024,2) "容量(M)",
        round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
        round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
        Largest "最大扩展段(M)",
        to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间" 
    from  (select f.tablespace_name,
            sum(f.bytes) bytes_alloc,
            sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes 
        from dba_data_files f 
        group by tablespace_name) a,
        (select  f.tablespace_name,
            sum(f.bytes) bytes_free 
        from dba_free_space f 
        group by tablespace_name) b,
        (select round(max(ff.length)*16/1024,2) Largest,
            ts.name tablespace_name 
        from sys.fet$ ff, sys.file$ tf,sys.ts$ ts 
        where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts# 
        group by ts.name, tf.blocks) c 
    where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

25. 查询表空间的碎片程度

    select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name 
    having count(tablespace_name)>10; 

    alter tablespace name coalesce; 
    alter table name deallocate unused; 

    create or replace view ts_blocks_v as 
    select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space 
    union all 
    select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents; 

    select * from ts_blocks_v; 

    select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space 
    group by tablespace_name;

26. 查询有哪些数据库实例在运行

    select inst_name from v$active_instances;