一、库的创建、删除及修改:
create database [if not exists] db_anme [default] [character set=''] [collate=''];
drop database if exists db_name;
alter database db_name [default] [character set=''] [collate=''];
二、表创建:
1、第一种:
create table [if not exists] tbl_name (col_name type/constraint...)
[options如 engine=MyISAM/InnoDB,character set='',collate='',comment='注释']
如:
例1、两个字段作为主键:
create table t (name varchar(50) not null ,age int unsigned not null ,primary key (name,age));
例2、指定存储引擎和字符集:
create table t (name varchar(50) not null ,age int unsigned not null)
engine='InnoDB',character set='utf8',comment='学生表';
2、第二种(复制表数据):
create table [if not exists] tbl_name select语句。
这种方式创建的表会将select语句查询的结果数据插入到新创建的表tbl_name中,
但无表结构的属性定义(desc表即可看到)。
例:create table t_tmp select * from t;
3、第三种(复制表结构):
create table [if not exists] tbl_name like old_tbl_name;
这种方式创建的表会将表结构的属性定义完全复制到新表,即新表和老表的结构完全一致,但不复制数据。
例:create table t_tmp like t;
三、mysql数据库目录以及数据目录:
1、MyISAM表:每张表有三个文件,都位于数据库目录中:
tb_name.frm:表结构定义。
tb_name.MYD:数据文件。
tb_name.MYI:索引文件。
2、InnoDB表:有两种存储方式。
(2.1)、默认:
每表有一个独立表结构定义文件和一个多表共享的表空间文件。
tb_name.frm:表结构定义文件,位于数据库目录中。
ibdata#:共享的表空间文件(存储所有库的数据文件),默认位于数据目录中(datadir中)。
(2.2)、独立的表空间:
推荐使用这种存储方式,这样方便对某个单独的库进行备份、恢复等。
tb_name.frm:每表有一个表结构文件。
tb_name.ibd:一个独立的表空间文件。
【注】:启用独立的表空间方法,在vi /etc/my.cnf 中的[mysqld]段加InnoDB_file_pre_table=ON 即可。
对于使用InnoDB最好在 /etc/my.cnf 添加此项。
四、Mysql连接查询、子查询和联合查询:
1、交叉连接(笛卡尔积,无任何条件,效率低下很少使用,联结后的记录数=A表记录数*B表记录数)
select * from A ,B
2、内连接 (自然连接、 等值连接):只显示A.ID=B.ID相等的记录
select * from A ,B where A.ID=B.ID
3、外连接:
左外连接
右外连接
4、自连接:顾名思义,自己连接自己。
5、子查询:在查询中嵌套的查询。
6、联合查询 union:
把两个或多个语句的查询结果合并为一个结果输出
注意:前提是各个语句的查询结果的字段个数和类型要一致。
7、常用的聚合函数:
sum() 、avg() 、max()、 min()、 count()
五、视图:存储下来的select语句。
1、创建视图:create view view_name as select 语句。
例:create view stt as select id,name,age from student;
select * from stt等价于select * from (select id,name,age from student) as stt
2、删除视图:drop view view_name;
如:drop view stt;
六、用户、权限管理:
1、创建用户:create user '用户名'@'主机' [identified by '密码'];
也可以用insert sql往mysql.user中插入数据。
用create user 创建的用户权限较小,不能创建数据库、表等。
2、修改用户名:rename user '用户名1'@'主机' to '用户名2'@'主机';
也可以用update sql语句修改mysql.user表中的数据。
3、删除用户:drop user '用户名'@'主机';
也可以用delete sql语句删除mysql.user表中的数据。
4、设置密码:set password for '用户名'@'主机'=password('密码');
5、授权(创建用户并授权,此方式创建的用户拥有创建数据库、表等):
grant 权限类型 on 库.表/函数/存储过程 to '用户名'@'主机' [ identified by 'testpass'];
例1、将mydb库的students表的所有权限授权给 'testuser'@'192.168.%.%',并设置秘密为testpass:
grant all on mydb.students to 'testuser'@'192.168.%.%' identified by 'testpass';
例2、将"所有库的所有表/函数/存储过程"的所有权限授权给'testuser'@'192.168.%.%'并设置秘密为testpass:
grant all on *.* to 'testuser'@'192.168.%.%' identified by 'testpass';
例3、将mydb库的所有表的select权限授权给'readonly'@'%',即'readonly'@'%'只有查询权限。
grant select on `mydb`.* TO 'readonly'@'%'
6、权限类型:
(1)、管理类权限:create user,file,show databases,super,
reload,shutdown,processlist,replication slave,
replication client,lock tables;
(2)、库级别和表级别权限:alter,create,create view,drop,execute,
index,grant,show view
(3)、数据操作(表级别):select,insert,update,delete
(4)、字段级别:select(col1,...),insert(col1,...),update(col1,...)
7、查看某个用户的权限:
show grants for '用户名'@'主机';
8、收回权限:revoke 权限类型 on 库.表/函数/存储过程 from '用户名'@'主机';
如:将收回用户'testuser'@'localhost'所有权限。
revoke all on *.* from 'testuser'@'localhost';
9、mysql库中与用户授权相关的表:
db:库级别的权限。
tables_priv:表级别的权限。
colomns_priv:列级别的权限。
procs_priv:存储过程和存储函数相关的权限。
proxies_priv:代理用户相关的权限。
10、flush:
(1)、flush privileges:刷新授权,一般用于对某账户授权后,使其立即生效。
(2)、flush hosts:清空host,一般用于因账户信息记不太清楚当连接数据库几次一直失败,那么当 获得正确账户信息连接被拒绝时,就需要执行flush host.
七、truncate:
删除表中所有数据后,恢复自动增长字段为0的方法:truncate tb_name
八、存储引擎:
1、存储引擎:是表级别的类型。
2、查看表存储引擎的方法:
(1)、mysql> show table status in mydb where name='classes'\G;
*************************** 1. row ***************************
Name: classes 表名
Engine: InnoDB 存储引擎
Version: 10 表的当前版本(多版本并发)
Row_format: Compact 行格式
Rows: 4 表中的数据行数
Avg_row_length: 4096 平均每行包含的字节数
Data_length: 16384 表中数据的总字节数
Max_data_length: 0 表能够占用的最大空间,单位字节
Index_length: 0 索引的大小,单位字节
Data_free: 8388608
Auto_increment: 5 下一个Auto_increment值
Create_time: 2015-12-24 18:35:10 表的创建时间
Update_time: NULL 表数据最近一次的修改时间
Check_time: NULL 使用check table 或 myisamchk最近一次检测表的时间
Collation: utf8_general_ci 排序规则
Checksum: NULL
Create_options: 创建表时指定的选项。
Comment: 表的注释
(2)、mysql>use mydb;
mysql> show table status like 'classes'\G;
*************************** 1. row ***************************
Name: classes
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 8388608
Auto_increment: 5
Create_time: 2015-12-24 18:35:10
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
3、InnoDB存储引擎的特性:
(1)、支持事务:事务日志。
(2)、支持外键
(3)、MVCC多版本并发控制
(4)、支持聚簇索引也叫聚集索引,因聚簇索引的索引文件和数据文件放置到一起,因此聚簇索引只能有一个,
一般用主键做聚簇索引。那么,其他非聚簇索引通常称为辅助索引
(辅助索引的索引文件和数据文件不放置到一起),辅助索引可以有多个。
(5)、支持热备份,前提是表存储时使用独立表空间(innoDB_file_pre_table=ON )。
(6)、支持行级锁。
(7)、数据库崩溃后可以恢复数据,因有事务日志。
4、MyISAM存储引擎的特性:
(1)、不支持事务。
(2)、不支持外键。
(3)、不支持行级锁,支持表级锁。
(4)、支持延迟更新索引。
(5)、支持全文索引。
(6)、适用场景:读多写少、数据较小的表;能容忍崩溃后的修改操作和数据丢失。
5、ARCHIVE存储引擎的特性:
(1)、仅支持insert和select,支持很好的压缩功能。
(2)、适用于存储日志信息或者其它按时间序列实现的数据采集类的应用。
(3)、不支持事务和索引。
6、第三方的存储引擎:
(1)、XtraDB:增强版的InnoDB,由Percona提供;
编译安装时,下载XtraDB的源码替换为Mysql存储引擎中的InnoDB的源码,
XtraDB已作为MariaDB中的默认的存储引擎。
(2)、TokuDB:使用Fractal Trees索引,性能很高,特别适用于存储大数据的表,
已被引入到新版的MariaDB中。
(3)、PBXT:MariaDB自带此存储引擎,对SSD硬盘提供适当的支持,
支持事务、MVCC、外键约束等,性能也比较高。
九、EXPLAIN:explain显示语句的执行过程,有否用到索引。
students的表结构如下
desc students
mysql> desc students;
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| sex | enum('F','M')| YES | | NULL | |
| age | int(11) | YES | | NULL | |
| classid | int(11) | YES | | NULL | |
+---------+---------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
例1、mysql>explain select * from test where age>18;
结果如下(age上未建立索引):
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: NULL 可能用到的键
key: NULL 用到的键
key_len: NULL
ref: NULL
rows: 30
Extra: Using where
1 row in set (0.00 sec)
例2、mysql> explain select * from students where id>0 \G;
结果如下(因表太小未使用id索引):
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 6
Extra: Using where
1 row in set (0.00 sec)
本文转自lzf0530377451CTO博客,原文链接:http://blog.51cto.com/8757576/1727772 ,如需转载请自行联系原作者