mysql客户端工具的使用和数据库类型

2017年2月22日00:01:58 发表评论 2,806 ℃

mysql客户端工具的使用

mysql客户端工具

mysql

mysqldump

mysqladmin

mysqlcheck

mysqlimport

[client]

-u USERNAME

-h host

-p

--protocol {tcp|socket|pipe|memory}

mysql非客户端工具

myisamchk

myisampack

MyISAM：每表三个文件，无事务功能，支持表锁

.frm：表结构

.MYD：表数据

.MYI：表索引

InnoDB:支持事务，行锁

所有表共享一个表空间文件：

建议：每表一个独享的表空间文件：编辑/etc/my.cnf 添加innodb_file_per_table=1

.frm：表结构

.ibd：表空间（表数据和表索引）

mysql

--user, -u

--host, -h

--password ,-p

--port

--protocol

--database database, -d 指定登录以后默认的数据库

mysql>

交互模式

批处理模式（脚本模式）

# mysql < test.sql

< source test.sql

mysql>

命令两类：

客户端命令

服务器语句：有语句结束符，默认；

delimiter ，\d :定义语句结束符

客户端命令：

\C ,clear : 提前终止语句执行

\g ,gO：无论语句结束符是什么，直接将此语句送至服务端执行

\G ,ego：无论语句结束符是什么，直接将此语句送至服务端执行，而且结果以竖排方式显示

\! ，system : 执行shell命令

\W ，warnings :语句结束后显示警告信息

\# ，rehash : 对于新建的对象，支持补全功能

补全：

名称补全

输出格式：

#mysql -uroot -p --html

#mysql -uroot -p --xml

服务器端命令获取帮助：

help keyword ：help create table

# mysqladmin [options] command [arg] [command [arg] ]...

# mysqladmin -uroot -p password 'new_passwd'

create database

drop database

ping：查看是否在线

processlist ：查看链接数

status

--sleep N：显示状态

--count N : 显示多少个状态

extended-status：显示状态变量

veriables：显示服务器变量

flush-privileges：让mysqld重读授权表，等同于reload

flush-stastus：重置大多数的服务器状态变量

flush-logs：二进制和中继日志滚动

flush-hosts：

refresh : 相当于同时执行flush-hosts和flush-logs

shutdown : 关闭mysql服务器进程

version：服务器版本及当前状态信息

start-slave :启动复制，启动从服务器复制线程

SQL thread

IO thread

stop-slave : 关闭复制

mysqldump，mysqlimport ，mysqlcheck

show engines;

show tables status [like ...]

# mysql --help --verbos 查看mysql支持的指令

hostname.err :错误日志

无法启动mysql

1、此前服务未关闭

2、数据初始化失败

3、数据目录位置错误

4、数据目录权限问题

DBA:

开发DBA : 数据库设计、SQL语句、存储过程、存储函数、触发器

管理DBA：安装、升级、备份、恢复、用户管理、权限管理、监控、性能分析、基准测试

mysql数据库类型和sql模型

数据类型

数值型

精确数值

int

近似数值

float

double

real

字符型

定长：char, binary（区分大小写）

变长：varchar , varbinary

text , blob

enum , set

日期时间型

date , time , datetime ,timestamp

数据类型：

1、存入的值类型

2、占据的存储空间

3、定长还是变长

4、如何比较及排序

5、是否能够索引

修饰属性：

not null 不允许为空

null 允许为空

default 默认值

character set 字符集

collation 排序规则

>show character set; 显示数据库支持的所有字符集

>show collation;显示字符集的排序规则

auto_increment ：必须是整形（int）、非空（not null）、无符号（unsigned）、主键（primary key）或唯一键

create table test(id int unsigned auto_increment not null primary key,name char(20))

> SELECT LAST_INSERT_ID(); 查询上一次的id结果

ENUM

DNS

RRtype CHAR(5)

A, PTR, CNAME, AAAA, MX, NS, SOA, SRV

RRtype ENUM('A','PTR')

SQL模型

CHAR(3)

abc, abcdefg

mysql服务器变量

作用域，分为两类

全局变量

show global variables

会话变量

show [session] variables

生效时间，分为两类

动态：可即时修改，

静态：

写在配置文件中

通过参数传递给mysqld

动态调整参数的生效方式：

全局：对当前会话无效，只对新建立会话有效

会话：即时生效，但只对当前会话有效

服务器变量：@@变量名

显示：select

设定：set global|session变量名='value'

SQL语句:

数据库

表

索引

视图

DML

数据库：

create database| schema [if noet exists] db_name [character set=][collate=] 新建数据库

create schema if not exists class character set'gbk' collate 'gbk_chinese_ci';

alter {database | schema} [db_name].. 修改数据库
drop {database | schema} [if exists] db_name 删除数据库

表创建

1、直接定义一张空表

create table myclass( class_id int(2) not null auto_increment primary key,class_name varchar(40) not null,teacher varchar(16) not null);

insert into myclass(class_name,teacher)values('yuwen','lihua'),('huaxue','like'),('dili','zhanghua'),('english','anhong');

2、从其他表中查询出数据，并以之创建新表

create table test_class select * from myclass where class_id <=3;

3、以其他表为模板创建一个空表

create table test2_class like myclass;

CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination, constraint )

CREATE TABLE testtb (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL) ENGINE [=] engine_name

CREATE TABLE testtb (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL, PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))

单字段：

primary key 主键

unique key 唯一键

单或多字段：

pramary key(col,....)

unique key(col,...)

index (col,...)

键也称作约束，可用作索引，属于特殊索引(有特殊限定)：B+Tree
CREATE INDEX创建索引
SHOW INDEXES FROM tb_name：显示指定表上的索引

show table status like 'table_name'\G :显示表属性

修改表定义：

alter table

添加、删除、修改字段

alter table myclass add starttime date default'2016-09-01';

添加、删除、修改索引

alter table myclass add unique key (teacher);

改表名

alter table test_class rename to test;

rename table test to test_class;

修改表属性

alter tables testtb engine=[InnoDB|MyISAM]

删除表：

InnoDB支持外键

索引创建：

create index index_name on table_name(col_name,...)

col_name [(length)] [asc | desc]

删除索引：

DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

DML：

select

insert into

delete

update

查询语句类型：select

简单查询

多表查询
子查询

SELECT * FROM tb_name;

SELECT field1,field2 FROM tb_name; 投影

SELECT [DISTINCT] * FROM tb_name WHERE qualification; 选择

DISTINCT：相同的值只显示一次

from子句：要查询的关系表、多个表、其它select语句

where子句：布尔关系表达式

=、>、>=、<=、<

逻辑关系：and, or , not

between...and ...

like ''

%: 任意长度任意字符

_：任意单个字符

rlike 支持正则表达式

in (列表)：

is null

is not null

order by field_name {asc|desc} 排序

字段别名：as select name as test_name from class

limit子句：limit [offset(偏移量),]Count（取值）

聚合：sum(), min(), max(), avg(), count()

group by：分组，一般用于聚合场景

having ：过滤group by的结果

select conut(cid) as persons,cid from students group by cid having persons>=2

多表查询：

连接：

交叉连接：笛卡尔乘积

自然连接：

select s.name,c.cname from students as s where class as c on s.cid=c.cid

外连接：

左外链接：... left join ...on ....

select s.name,c.cname from students as s left join class as c on s.cid=c.cid

右外链接 : ..right join ...on ...

select s.name,c.cname from students as s right join class as c on s.cid=c.cid

自链接：

子查询：

比较操作中使用子查询：子查询只能返回单个值

select name from students where age >(select avg(age) from students);

in():使用子查询

select name from students where age in (select age from teacher);

在from中使用子查询

select name,age from (select name,age from students) as t where t.age >20;

联合查询：union

(select name,age from students) union (select name,age from teacher)

练习：

1、挑选出courses表中没有被students中的CID2学习的课程的课程名称；

select cname from coureses where cid not in(select distinct cid2 from students where cid2 is not null)

附加：挑选出没有教授任何课程的老师，每个老师及其所教授课程的对应关系在courses表中；

select tname from tutors where tid not in (select distinct tid from courses);

找出students表中CID1有两个或两个以上同学学习了的同一个门课程的课程名称；

select cname from courses where cid in (select cid1 from students group by cid1 having count(cid1)>=2);

2、显示每一位老师及其所教授的课程；没有教授的课程的保持为NULL；

select t.tname,c.cname from tutors as t left join courses as c on t.tid=c.tid;

3、显示每一个课程及其相关的老师，没有老师教授的课程将其老师显示为空；

select t.tname,c.cname from tutors as t right join courses as c on t.tid=c.tid;

4、显示每位同学CID1课程的课程名及其讲授了相关课程的老师的名称；

select name,cname,tname from studengts,courses,tutors where students.cid1=courses.cid and courses.tid=tutors.tid;

视图：存储下来的select语句

基于基表的查询结果

create view view_name as

drop view view_name

show create :查看创建对象时，使用的语句

mysql -e ：mysql -e "select * from class"

添加语句insert：

insert into tb_name (col1,col2,...) values （val1.val2,....）[(val1,val2,...)...]

insert into tb_name(col1,col2,col3) select col1,col2,col3 from tb_name2 where ....