事务
- 设置隔离性:set global transaction isolation level read uncommitted(read committed、repeatable read、serializable);
- 查询事务隔离性:select @@global.tx_isolation
-
mysql> select @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | SERIALIZABLE | +-----------------------+
- 关闭自动提交:start transaction
索引
- 创建索引:create index test_name_undex test(name);
- explain select * from test where name = "xxx";
-
mysql> explain select * from test where name = "huangqi"; +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | test | ref | test_name_index | test_name_index | 22 | const | 5 | Using where; Using index | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+--------------------------+
- 删除索引:drop index test_name_index on test
-
mysql> explain select * from test where name = "huangqi"; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
视图
- 视图创建:create view myview as select * from test;(删除视图数据,实际删除基表数据)
- 删除视图:drop view myview
DBA命令
- 创建用户:create user username identified by password;
- 用户授权:GRANT privileges ON dbname.tbname TO usernanme@'login ip' WITH GRANT OPTION;
- privileges = all (all privileges)表示开通所有权限(select update insert delete,可开通具体权限)
- dbname,tbname 表示数据库名和表名,*表示所有
- username 表示开通权限的用户名
- ‘login ip’表示本地,%表示任何ip
- with grant option 表示该用户可以授权给其他用户
- 如果以上命令还不能登录新用户:删除用户表中的空用户
- 查询用户:select user,password from mysql.user;
- 如果存在空用户,删除删除空用户:delete from mysql.user where user = '';
- 删除后刷新:flush privileges;
- 收回权限:revoke PRIVILEGES from USERNAME@'LOGIN IP';
- PRIVILEGES 具体回收的权限(如:所有权限all privileges,查询select,授权grant option)
- 导出数据库:mysqldump dbname>E:\text\dbname.sql -uuser -ppassword;
- 导入数据库:
- create database dbname;
- use dbname; dbname和dbname.sql的dbname可以不同
- source E:\text\dbname.sql;
三范式
- 第一范式:任何一张表都有主键,每个字段原子性不能再分
- 第二范式:建立在第一范式的基础上,所有非主键的字段完全依赖主键,不能产生部分依赖(多对多 三张表,关系表两个外键)
- 第三范式:建立在第二范式的基础上,所有非主键字段直接依赖主键,不能产生传递依赖 (一对多 两张表 多的表加外键)
附加知识
- MySQL中单引号和双引号的关系:
- 一般使用单引号,当字符串内需要单引号时,出使用转义的办法外,可以用双引号包括字符串
- 当字符串内需要双引号时,出使用转义的办法外,可以用单引号包括字符串