mysql的常用操作整理及事务日志

一.DDL:数据库定义语言

create,alter,drop等与数据库相关的操作。

二.DML:数据库操作语言

insert,delete,update,select操作相关的。

别名:

字段在显示的时候可以显示别名,表中字段没有修改,仅仅显示的时候变了。

col1 as stucol1,col2 as stucol2,…

MariaDB [hellodb]> select Name as stuname from students;

where子句:指明过两条件以实现“选择功能”

MariaDB [hellodb]> select StuID,Name,Age from students where Age != 22 ;

查询students表中stuid,name,age字段中age不等于22的。

过滤条件为布尔型表达式

算数比较:+,-,*,/,%

比较操作:=,!=,<>,<=>,>,<,>=,<=

group by聚合:根据指定的条件查询结果进行“分组”以用于做“聚合”运算。

avg(),max(),min(),count(),sum()

MariaDB [hellodb]> select * from students group by StuID;

MariaDB [hellodb]> select avg(Age) from students group by Gender;

having过滤:是对分组聚合运算之后结果做过滤的。

MariaDB [hellodb]> select avg(Age) from students group by Gender having avg(Age) >20;

order by:根据指定的字段对查询结果进行排序

升序:acs降序:desc

MariaDB [hellodb]> select Name,Age from students order by age desc;

limit:对查询的结果进行输出前限制

MariaDB [hellodb]> select Name,Age from students order by age desc limit 10;

MariaDB [hellodb]> select Name,Age from students order by age desc limit 10,10;

update:更改数据库信息

MariaDB [hellodb]> update students set TeacherID=1 where StuID=25;

delete:删除数据指定行操作

MariaDB [hellodb]> select * from coc;

MariaDB [hellodb]> delete from coc where ID=1;

Query OK, 1 row affected (0.00 sec)

insert into :向指定表插入数据

MariaDB [hellodb]> insert into coc (ID,ClassID,CourseID) values (3,3,3);

savepoint:保存还原点

MariaDB [hellodb]> start transaction;

MariaDB [hellodb]> savepoint s1;

MariaDB [hellodb]> rollback to T1;

三.常用数据操作命令

1.查看所有数据库:

MariaDB [mysql]> show databases;

2.使用该数据库:

MariaDB [hellodb]> use mysql;

3.查看数据库中的所有表:

MariaDB [mysql]> show tables;

4.查询表里面的数据:

MariaDB [mysql]> select * from user;

5.查看表上的索引:

MariaDB [mysql]> show index from user;

6.查询表结构:

MariaDB [mysql]> select desc user;

7.explain自行分析该语句是否使用到索引,不会执行。

MariaDB [hellodb]> explain select * from courses where CourseID=3\G

8.查看是否启用ssl

MariaDB [hellodb]> show global variables like '%ssl%';

9.创建和管理索引有两种途径:

1)通过表创建和修改来实现,在创建表时候直接创建索引

MariaDB [hellodb]> create index t1 on coc(ID);

MariaDB [hellodb]> explain select * from coc where ID\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: coc

type: ALL

possible_keys: PRIMARY,t1

key: NULL

key_len: NULL

ref: NULL

rows: 14

Extra: Using where

1 row in set (0.00 sec)

2)创建或删除索引,修改表的命令

alter创建索引,创建索引在CourseID字段上:

MariaDB [hellodb]> alter table courses add index(CourseID);

Query OK, 7 rows affected (0.18 sec)

Records: 7 Duplicates: 0 Warnings: 0

查看courses表上的索引:

MariaDB [hellodb]> show indexes from courses;

删除索引:

DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

注:索引一般不建议任意添加。

10.查询数据库存储引擎

MariaDB [mysql]> show engines;

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

10.查看查询缓存是否开启

MariaDB [hellodb]> show global variables like 'query%';

当query_cache_type 为on时才打开。当定义为demand时候,查询缓存功能按需进行。

11.查询缓存是否命中

MariaDB [hellodb]> show global status like 'Qcache%';

MariaDB [hellodb]> show global status like 'Com_se%';

12.自动提交功能

MariaDB [hellodb]> show global variables like '%auto%';

MariaDB [hellodb]> set session autocommit=0; 关闭session级别的自动提交

MariaDB [hellodb]> show variables like '%auto%'; 查看是否关闭

13.查看mysql的连接请求

MariaDB [hellodb]> show processlist;

MariaDB [hellodb]> start transaction;

14.事务日志

关于事物日志:

首先得把通过start transaction启动事务日志

结束事务可以通过1.commit(提交)2.rollback(回滚)

事件隔离级别:

read uncommitted(读未提交)

read commited(读提交)

repeatable read (可重读)默认级别

serializabile(可串行化)

可能存在问题:

脏读:

不可重复读:

幻读:

加锁读:

查看时间隔离级别:

MariaDB [hellodb]> show global variables like '%iso%';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

修改事件隔离级别:

tx_isolation为服务器变量,默认为REPEATABLE-READ,可以在session级别下修改。

MariaDB [hellodb]> set tx_isolation='read-uncommitted';

MariaDB [hellodb]> start transaction; 启动事务。

15.死锁

两个或者多个事务在同一资源相互占用,并请求锁定对方占用的资源状态。

锁分为隐式锁(存储引擎自动施加的)和显式锁(手动指定的)。

事务日志的写入类型为“追加”,因为其操作为“顺序IO”,此日志通常也被成为“预写式日志(write ahead logging)”。

若要基于LVM,要做备份恢复。事务日志和数据库文件要存放在一块。

注:一般不建议在同一个数据库中使用多个表存储引擎,即便是不同也不能支持和不支持的混用。 (#sed -I 's@engine=myisam@engine=innodb@ig' hellodb.sql)

15.日志文件类型

二进制文件,事务日志,错误日志,一般查询日志,中继日志,慢查询日志。

查看二进制日志索引文件:

MariaDB [(none)]> show binary logs;

MariaDB [(none)]> show master status;

四.常用操作整理

1)给mysql设置密码:

[root@node2 /]# mysqladmin -u root password "123pwd"

2)给数据库赋予权限:

MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '123pwd' with grant option;

Query OK, 0 rows affected (0.00 sec)

%代表所有主机,此处也可以跟网络地址

MariaDB [(none)]> grant all privileges on *.* to 'root'@'localhost' identified by '123pwd' with grant option;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges; //刷新数据库命令

Query OK, 0 rows affected (0.01 sec)

3)忘记mysql密码如何修改?

1.打开主配置文件,加入以下行

[root@node2 ~]# vim /etc/mysql/my.cnf

skip_grant_tables

2)重启mysqld

[root@node2 ~]# /etc/init.d/mysqld restart

3)登录数据库并修改密码

[root@node2 ~]# mysql

MariaDB [(none)]> use mysql;

Database changed

MariaDB [mysql]> update user set password=password('123abc') where user='root';

Query OK, 5 rows affected (0.01 sec)

Rows matched: 5 Changed: 5 Warnings: 0

MariaDB [mysql]> flush privileges;//更新数据库

Query OK, 0 rows affected (0.01 sec)

4)去掉配置文件中的skip_grant_tables,并数据新密码登录。

[root@node2 ~]# mysql -uroot -p

Enter password: new-password

mysql

 

 


发表评论

登录 后发表评论.