mysql数据库基础总结

mysql数据库基础总结

数据库及表的用法：

数据库的创建：

create database [if not exists] 数据库名; 创建一个数据库

show create database 数据库名; 可以查看数据库的创建信息

表的创建：

create table 表名(

​ 字段1 字段类型 约束条件，

​ 字段2 字段类型 约束条件

​ …

)

表的复制：

两种方式：

​ 1.create table 新表名 like 原表； 只复制了原表的设计结构

​ 2.create table 新表名 sele * from 原表； 既复制了原表的设计结构，也复制了原表的值

但是：这两种复制表都只是复制了原表的基本结构，没有复制主键，外键等约束条件。

表的删除：

drop table 表名；

表的修改：

alter table 表名 add 列名 数据类型 列属性； 向表中添加列

alter table 表名 add constraint 约束名 约束类型（字段名）； 向列中增加约束条件

alter table 表名 change 旧列名 新列名 数据类型；修改列名称

alter table 表名 modify 列名 新数据类型 新列属性； 修改列属性

alter table 表名 drop column 列名； 删除列

alter table 表名 drop 约束 列名； 删除列中的约束

数据操作语言：

插入数据：

insert into 表名 （表字段1，表字段2，…） values（‘数据1’，‘数据2’，‘…‘）； 向表中插入一条数据

insert into 表名（表字段1，表字段2，…） values（‘数据1’，‘数据2’，‘…‘），（‘数据1’，‘数据2’，‘…‘），（‘数据1’，‘数据2’，‘…‘），（…）； 向表中插入多条数据

insert into 表名（表字段1，表字段2，…） select 表字段1，表字段2，… from 新表名 where 条件； 通过子查询语句向表中插入数据，（不必写values子句，但查询的列字段必须与表字段相同）

修改数据：

update 表名 set 列=值 where 条件；

update 表名 set 列1=值1，列2=值2，… where 条件； 修改表中的多个数据

删除数据：

delete from 表名 where 条件；

注：如果删除行中有主键作为外键被其他表所引用，则不能删除该行数据

查询数据：

select * from 表名 where 条件 [group by 属性|having 函数条件|order by 属性 （asc，desc）|limit 数值，数值]；

事务：

事务的特性：原子性，一致性，隔离性，持久性：

–原子性（Atomicity）

•事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。

–一致性（Consistency）

•一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。

–隔离性（Isolation）

•一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。

–持久性（Durability）

•也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

事务处理：

1通过begin，rollback，commit来实现

​ begin：开始一个事务

​ rollback：事务回滚

​ commit：事务提交

2直接用set改变mysql的自动提交模式

​ set autocommit = 0 禁止自动提交； 必须通过commit方式手动提交；

​ set autocommit=1 开启自动提交；

隐式提交；

​ 执行一个ddl语句：（create ，alter，drop，truncate，rename）

​ 执行一个dcl语句：（grant，revoke）

隐式回滚：

​ 客户端强制退出，客户端连接服务器中断，系统崩溃

例：

begin；

delete from test；//删除test表

rollback；//回滚，test表存在

insert into test values（’A‘）；//执行插入a

save point insert a；//一个保存点

insert into test values（’B‘）；//执行插入b

save point insert b；//一个保存点

insert into test values（’C‘）；//执行插入c

rollback to insert b；//不能回滚到b点，因为begin碰到rollback或者commit事件就会结束一个事务，回滚到test表后的每一个都是一个事务，都进行了自动commit

delete from test where test str=‘A’；//删除test中的字段为A的行

commit；//没有执行

rollback；//没有执行

简单查询：

列别名的方式：

​ 列名 列别名

​ 列名 as 列别名

消除重复行：

​ select distinct 属性 form 表名 …

显示表结构：

​ desc 表名

特殊比较运算符：

​ between…and… 判断要比较的值是否在某一个范围内

​ in（集合列表） 判断要比较的值是否和集合中的任何一个值相等

​ like 判断要比较的值是否满足部分匹配

​ is null 判断要比较的值是否为空值null

常用函数：

数学函数：

•pi（）返回圆周率；

•mod（x，y）返回x被y除的余数；

•round（x，y）返回保留小数点后面y位，四舍五入的整数；

•rand（）每次产生不同的随机数；

•pow（x，y）和power（x，y） 返回x的y次乘方的结果值；

字符串函数：

•CHAR_LENGTH(str)：返回字符串str的所包含字符个数;

•LENGTH(str):返回字符串str的长度；

•CONCAT(s1,s2,…)： 字符串连接；

•CONCAT_WS(x,s1,s2,…)：字符串连接, x是其它参数的分隔符;

•INSERT(s1,x,len,s2) ：返回字符串s1，s1中插入字符串s2；

•LOWER (str)|LCASE (str)：将字符串全部转换成小写字母；

•UPPER(str)|UCASE(str)：将字符串全部转换成大写字母；

•LEFT(s,n)：返回最左边指定长度的字符；

•RIGHT(s,n)：返回最右边指定长度的字符；

•LPAD(s1,len,s2)| RPAD(s1,len,s2) ：填充字符串函数；

•TRIM(s1 FROM s)|LTRIM(s）|RTRIM(s):删除空格函数；

•REPEAT(s,n)：重复生成字符串函数；

•SPACE(n)：返回一个由n个空格组成的字符串；

•REPLACE（s，s1，s2）：字符串替换函数；

•STRCMP(s1,s2)：比较字符串大小函数；

•SUBSTRING(s,n,len)：获取子串函数；

•LOCATE(str1,str)|POSITION(str1 IN str)|INSTR(str, str1)：匹配子串开始位置函数;

•REVERSE(s)：将字符串s反转；

•ELT(N,字符串1,字符串2,字符串3,…)：返回指定位置函数;

日期和时间函数

•CURDATE()和CURRENT_DATE() ：获取当前日期函数;

•NOW()：返回服务器的当前日期和时间；

•CURTIME(）：返回当前时间，只包含时分秒；

•UTC_DATE(）：返回世界标准时间日期函数；

•UTC_TIME()：返回世界标准时间函数；

•TIMEDIFF(expr1, expr2)：返回两个日期相减相差的时间数；

•DATEDIFF(expr1, expr2)：返回两个日期相减相差的天数；

•DATE_ADD(date,INTERVAL expr type):日期加上一个时间间隔值；

•DATE_SUB(date,INTERVAL expr type):日期减去一个时间间隔值；

•DATE(date)、TIME(date)、YEAR（date）：选取日期时间的各个部分：

•EXTRACT(unit FROM date)：从日期中抽取出某个单独的部分或组合；

•DAYOFWEEK(date) 、DAYOFMONTH(date) 、DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天

•DAYNAME、MONTHNAME：返回日期的星期和月份名称；

•DATE_FORMAT(date,format)：格式化日期；

•TIME_FORMATE(time,formate）：格式化时间；

控制流程函数：

case value when [compare-value] then [result] 判断一个值是否符合

IFNULL （e1，e2） 如果e1不为null，返回e1；否则返回e2

例：select ifnull（null，5） ：返回结果为5

NULLIF（e1，e2）如果e1等于e2，返回null，否则返回e1

例：select nullif（5，5） ：返回结果为null

其他函数：

•Database():返回使用utf8 字符集的默认( 当前) 数据库名

•Version():返回指示MySQL 服务器版本的字符串。

•User():返回当前MySQL 用户名和机主名

•Inet_aton():给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。

• Inet_ntoa():给定一个数字网络地址, 返回作为字符串的该地址的点地址表示。

• Password(str):从原文密码str 计算并返回密码字符串，当参数为NULL 时返回NULL。

• Md5(str):为字符串算出一个MD5 128 比特检查和。

多表连接查询：

两个表值之间的连接方式：

外连接：左外连接 表1 left join 表2， 右外连接 表1 right join 表2，自连接 表1 inner join 表2

内连接：表1 join 表2

交叉连接： 表1，表2

高级查询：

分组函数：

MIN 返回每组中最小值

MAX 返回每组中最大值

SUM 返回每组的和

AVG 返回每组的平均值

COUNT 返回每组的记录数

分组：

DISTINCT 用来消除重复记录在使用分组函数

GROUP BY 用来对表进行分组，分组函数要在这条语句中才能进行分组，如果没有这条语句，这表示整个表为一个组

HAVING 对分组函数进行比较操作

select语句的执行过程：

–1.通过FROM子句中找到需要查询的表；

–2.通过WHERE子句进行非分组函数筛选判断；

–3.通过GROUP BY子句完成分组操作；

–4.通过HAVING子句完成组函数筛选判断；

–5.通过SELECT子句选择显示的列或表达式及组函数；

–6.通过ORDER BY子句进行排序操作。

–7.通过LIMIT子句对数据进行分批处理

多行子查询：

​ IN

​ ANY: <ANY 表示小于最大值，>ANY表示大于最小值，=ANY表示等于查询结果中的任意一个，等同与IN

​ ALL： <ALL表示小于最小值，>ALL表示大于最大值，=ALL表示等于所有值

视图和索引：

视图：

创建视图：

​ CREATE VIEW view_name [(column_list)]

​ AS select_statement

​ [WITH [CASCADED | LOCAL] CHECK OPTION]

•WITH CHECK OPTION：一个约束条件，通过视图所插入或修改的数据行必须满足视图所定义的查询；

修改视图：

​ CREATE OR REPLACE VIEW view_name [(column_list)]

​ AS select_statement

删除视图：

​ DROP VIEW view_name

条件：

在视图上进行插入，修改，删除数据时，只要视图中没有group函数，group by子句，distinct关键字，都能对数据进行如上操作

索引：

索引分类：

​ –普通索引

​ –惟一性索引

​ –全文索引

​ –单列索引

​ –多列索引

​ –空间索引

创建索引：

​ •CREATE TABLE 表名 (

​ 属性名 数据类型 [完整性约束条件],

​ 属性名 数据类型 [完整性约束条件],

​ …

​ 属性名 数据类型

​ [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

​ [别名] (属性名1 [长度] (asc|desc) )

);

删除索引：

​ DROP INDEX 索引名 ON 表名 ;

用户和权限管理：

创建用户：

​ 方式一 create user ‘用户名’@‘localhost’ identified by ‘密码’； 创建只能在本地连接的用户

​ 方式二 create user ‘用户名’@’%’ identified by ‘密码’；创建可以在不同电脑上连接用户数据库

账户管理：

授权：

​ grant select，update on demo.demo01 to ‘用户名’@‘localhost’ identified by ‘密码’ ：对用户中demo数据库中的demo01表设置查询和更新操作

​ grant all privileges on demo.demo01 to ‘用户名’@‘%’ identified by ‘密码’ ：对用户中demo数据库中的demo01表设置所有操作

收回：

​ revoke update on demo.demo01 from ‘用户名’@‘localhost’

修改密码：

​ set password for 用户名 = password（‘新密码’）;

删除用户：

​ drop user 用户名；

查看权限：

​ show grant for ‘用户名’@‘主机名（localhost）’；

表分区管理：

分区类型：

​ –RANGE分区

​ –LIST分区

​ –COLUMNS分区

​ –HASH分区

​ –KEY分区
range分区：

例：`create table employees (

​ id int not null,

​ fname varchar(30),

​ lname varchar(30),

​ hired date not null default ‘1970-01-01’,

​ separated date not null default ‘9999-12-31’,

​ job_code int not null,

​ store_id int not null

)

partition by range (store_id) (

​ partition p0 values less than (6),

​ partition p1 values less than (11),

​ partition p2 values less than (16),

​ partition p3 values less than maxvalue

)；`

list分区：

例：`create table employees (

​ id int not null,

​ fname varchar(30),

​ lname varchar(30),

​ hired date not null default ‘1970-01-01’,

​ separated date not null default ‘9999-12-31’,

​ job_code int,

​ store_id int

)

partition by list(store_id)

​ partition pnorth values in (3,5,6,9,17),

​ partition peast values in (1,2,10,11,19,20),

​ partition pwest values in (4,12,13,14,18),

​ partition pcentral values in (7,8,15,16)

)；`

hash分区：

例：`create table employees (

​ id int not null,

​ fname varchar(30),

​ lname varchar(30),

​ hired date not null default ‘1970-01-01’,

​ separated date not null default ‘9999-12-31’,

​ job_code int,

​ store_id int

)

partition by hash(year(hired))

partitions 4；`