【MySQL】MySQL的基础知识与常用操作--开发手册

服务启动、停止

net start/stop mysql

登录

mysql -u username -p password

退出

mysql>exit / quit / \q

修改MySql提示符

1、连接客户端是通过参数指定

shell>mysql -u username -p password --prompt 提示符

2、mysql>prompt ……

提示符：\D日期 \d当前数据库名 \h服务器名 \u当前用户

常用命令

显示服务器版本

select version（）；

显示当前日期

select now（）；

显示当前用户

select user（）；

语句规范：

关键字与函数名大写

db、table、字段名小写

sql语句以分号结尾

创建数据库

create { database|schema } [ if not exists ] db_name [ default ] character set [ = ] charset_name

查看当前服务器下的数据表列表

show { database|schemas } [ like 'pattern' | where expr ]

查看警告信息

show warnings；

查看创建数据库的语句

show create database db_name；

修改数据库

alter { database|schema }[ db_name ] [ default ] character set [ = ] charset_name;

删除数据库

Drop { databse|schema } [ if exists ] db_name

数据类型

数据类型	具体类型	占用字节
整型	tinyint	1
	smallint	2
	mediumint	3
	int	4
	bigint	5
浮点型	float[(m,d)]	约7位小数
	double[(m,d)]	m数字总位数 d小数点后位数
日期时间	Year	1
	time	3
	data	3
	datetime	8
	timestamp	4
字符型	char（m）	m
	varchar（m）	L+1
	tinytext	L+1
	text	L+2
	mediumtext	L+3
	longtext	L+4
	enum	max 65535
	set	max 64

数据表

行称作记录，列成为字段

打开数据库

use db_name；

显示当前数据库

select database；

创建表

create table [ if not exists ] table_name (

     cloumn_name data_type,

     ...

);

查看表

show tables [ from db_name ] [ like 'pattern'|where expr ];

查看表结构

show columns from tb_name;

插入记录

insert [ into ] tb_name [ (col_name,...) ] values (val,...);

查看记录

select expr,... from tb_name

空值与非空

null，not null

 

自动编号 auto_increment

必须与主键组合使用

默认下起始1，增量1

主键：primary key

唯一：unique 可以存在空值，一张表可以有多个

默认：default

外键约束 foreign key，reference

要求：1、父表和字表必须使用相同的引擎，禁止使用临时表

          2、数据表存储引擎只能是InnoDB

          3、外键列和参照列必须数据类型相似（数字长度、符号位必须相同，字符长度可以不同）

          4、外键列和参照列必须创建索引，如果参照列没有索引MySQL会自动创建

在配置文件中可以编辑数据表的默认存储引擎：

default-storage-enginee = InnoDB

外键约束的参照操作：（因为引擎受限而较少使用）

cascade 从父表删除或更新且自动删除或更新字表匹配行

set null 从附表删除或更新行，并设置子表中外键列为null。这需要保证字表该列没有not null约束

restrict 拒绝对附表的删除或更新操作

no action 标准SQL，同mysql中的restrict

发生在 on delete ，on update 事件上

修改数据表

添加单列

Alter table tb_name add [ column ] colname column_definition [ first|after_column ]

默认添加于所有列的最后

添加多列——不能指定位置

alter table tb_name add [ column ] ( col_name column_defination , ...)

删除列

alter table tb_name drop [ column ] col_name

添加主键约束

alter table tb_name add [ constraint [ symbol ] ] primary key [ index_type ] ( index_col _name, ...)

添加唯一约束

alter table tb_name add [ constraint [ symbol ] ] unique [ index | key ] [ index_name ] [ index_type ] ( index_col_name )

添加外键约束

alter table tb_name add [ constraint [ symbol ] ] foreign key [ index_name] ( index_col_name ,...) reference_defination

添加/删除默认约束

alter table tb_name alter [ column ] col_name { set default literal | drop default }

删除主键约束

alter table tb_name drop primary key

删除唯一约束

alter table tb_name drop { index | key } index_name

删除外键约束

alter table tb_name drop foreign key fk_symbol

(这里需要的外键名，可以通过show create table tb_name 查看）

修改列定义

alter table tb_name modify [ column ] col_name

column_definition [ first | after col_name ]

(这里要注意，大类型转小类型时可能会导致数据丢失）

修改列名称

alter table tb_name change [ column ] old_col_name new_col_name column_definition [ first | after col_name ]

数据表更名

1、alter table tb_name rename [ to | as ] new_tb_name

2、rename table tb_name to new_tb_name [,tb_name2 to new_tb_name2]

记录

插入记录

insert [ into ] tb_name [ (col_name) ] { values | value } ( { expr | default },...),(...),...

auto_increament的字段可写成null，default，将保持原有递增，字符值可以使用default

如果该字段有default约束，可以把default赋值给记录

插入记录——子查询

insert [ into ] tb_name set col_name = { expr | default } , ...

可使用子查询（SubQuery）

一次只能插入一条记录

插入记录——select

insert [ into ] tb_name [ (col_name,...) ] select ...

可将查询结果插入

更新记录

单表更新

update [ low_priority ] [ ignore ] table_reference set , col_name = { expr1 | default }[ ,col_name2 = { expr | default } ]  [ where where_condition ]

删除记录

单表删除

delete from tb_name [ where where_condition ]

查询

select select_expr [ ,select expr ... ]

[ from table_references

  [ where where_condition ]

  [ group by { col_name | postion } [ asc | desc ] , ... ]

  [ having where_condition ]

  [ order by { col_name | expr | position } [ asc | desc ] , ... ]

  [ limit { [ offset, ] row_count | row_count offset ofsset } ]

]

tb_name.* 可表示命名表的所有列

可使用 [ as ] alias_name 设置别名，可用于group by，order by，having子句

where：可用mysql支持的函数或运算符

group by：可使用位置（字段在select中的位置 ）

having：分组条件，要么是聚合函数，要么出现在select列表中

order by：排序

limit：限制记录返回数量

select从0开始编号，“ limit 起始id，几个 ”，完全按位置

set Names charset_name；更改客户端查看的编码

子查询：

出现在其他SQL语句内的select子句

嵌套在查询内部，必须始终出现在圆括号内

可以包含多个关键字或条件（distinct，group by，order by...）

外层查询可以是select , insert , update , set , do

返回：标量，一行，一列或子查询

产生情况：

1、使用比较运算符的子查询：=   >   <   >=   <=   <>    !=   <=>

operand comparison_operator subquery

any、some、all

any和some是等价的

	any	some	all
>   >=	最小	最小	最大
<   <=	最大	最大	最小
=	任意	任意
<>  !=			任意

2、使用 [ not ] in的子查询

operand comparison_operator [ not ] in ( subquery )

=any与in等价

！=all 或 <>all 与 not in 等价

3、使用 [ not ] exists的子查询

子查询返回任何行exists返回true，否则为false

多表更新

update table_references

set col_name1 = { expr1 | default }

[ , col_name2 = { expr2 | default } ] ...

[ where where_codition ]

表连接

table_reference

{ [ inner | cross ] join } { left | right } [ outer ] join } 

table_reference

on conditional_expr

MySQL中 join，cross join ， inner join是等价的

create...sekect创建表同时将查询结果写入到数据表

create table [ if not exists ] tb_name

[ ( create_definition , ... ) ]

select_statement

数据表参照

table_name [ [ as ] alias ] | table_subquery [ as ] alias 

table_subquery 可作为子查询使用在from中

这样的子查询必须为其赋予别名

连接条件

用on来设定连接条件，可用where代替

通常用on设定连接条件，用where进行结果集记录的过滤

无限级分类表设计

create table tb_name(

type_id smallint unsigned primary key auto_increment,

type_name varchar(20) not null,

parent_id smallint unsigned not null ,default 0

)

多表删除

delete tb_name[ .* ] [ ,tb_name1. * ]...

from table_references

[ where where_condition ]

运算符和函数

字符函数 字符串编号从1开始

concat（）     字符连接

concat_ws（）     使用指定分隔符字符连接

format（）     数字格式化

lower（）/ upper（）     大小写转换

left（）/ right（）     从左、右字符截取，第二个参数指定取几位

length（）     字符串长度

ltrim（）/ rtrim（）/ trim（）     删除前、后、前后空格，可带参数（both、leading、trailing……from）

substring（）     截取（源 ，开始，几个）起始可负数，倒数

[ not ] like      '%'多个，'_'单个 （escape 'c' c后%识别为普通字符）

replace（）     替换（原，‘替换前’，‘替换后’）

数值运算符即函数

ceil（）     向上取整、进一取整 

float（）     向下取整、舍一取整

div     整数除法

mod     取余、取模，可用于浮点数

power（）     幂运算（源，指数）

round（）     四舍五入（源，精度）

truncate（）     数字截取（源，精度）精度可为负，截整数

比较

[ not ] between ... and      [不] 在范围中

[ not ] in ( )     [不]在列出范围内

is [ not ] null     [不]为空

日期时间函数

now（）     当前日期和时间

curdate（）     当前日期

curtime（）     当前时间

date_add（）     日期变化 （源，interval 数 单位），可负

date_diff（）     日期差值

date_format（）     日期格式化（源，格式） %m，%d，%Y

信息函数

connection_id（）     连接ID

database（）     当前数据库

last_insert_id（）     最后插入记录的ID号

user（）     当前用户

version（）     版本信息

聚合函数，只有一个返回值

avg（），count（），max（），min（），sum（）

加密函数

md5     信息摘要算法

password（）