use datbase_name 转换操作的数据库
SHOW tables 展示数据库所有的数据表
SHOW columns FROM table_name 展示所选表的列
一、MySQL的数据类型
类型 大小 用途 范围 范围(无符号)
数字
tinyint 1bytes 小整数 (-128,127) (0,225)
smallint 2bytes 大整数值 (0,65535)
mediumint 3bytes 大整数值 (0,16777215)
int/integer 4bytes 大整数值 (0,4294967295)
bigint 8bytes 极大整数值 (0,18 446 744 073 709 551 615)
float 4bytes 单精度浮点数
double 8bytes 双精度浮点数
decimal decimal(M,D)
日期时间
date 3bytes 日期 YYYY-MM-DD
time 3bytes 时间、持续时间 HH:MM:SS
year 1bytes 年份 YYYY
datetime 8bytes 混合日期、时间 YYYY-MM-DD HH:MM:SS
timestamp 4bytes 时间戳 YYYYMMDD HHMMSS
字符串类型
char 0-255bytes 定长字符串
varchar 0-65535bytes 变长字符串
tinyblob 0-255bytes 短二进制字符串
tinytext 0-255bytes 短文本字符串
blob
text
mediumblob
mediumtext
longblob
longtext
二、创建数据库、数据表、删除数据库、数据表
1、创建:CREATE
语法: CREATE database [,if not exists] db_name character set 'utf8';
CREATE TABLE [,if not exists] table_name(
col_1 int [,primary key,auto_increment],
col_2 char(10) [,not null],
...
)engine=innodb default charset=utf8;
2、删除:DROP
语法: DROP database [,if exists] db_name;
DROP table [,if exists] table_name
tips:
auto_increment 自增长
primary key 设置主键,被设置为主键的列不允许出现重复
not null 不允许空值,出现时用NULL填充
engine 设置引擎
charset 设置字符集
三、增:插入数据
insert into
语法: insert into table_name(col1,col2...coln)
value
(value1,value2...valuen)
tips:
now() sql的一个函数,返回当前日期、时间
四、查询数据
SELECT
语法: SELECT column_name,columon
FROM table_name
[WHERE clause]
[LIMIT N][offset M]
tips:
1.查询语句可以使用多个表,表之间使用(,)分开
2.SELECT 可以读取一条或者多条语句
3.(*)可以代表所有字段,返回查询到的结果的所有字段
4.WHERE包含期望语句的条件
5.LIMIT返回结果的数目
6.offset指定SELECT查询时的数据偏移量,比如排在前面多少的数据不检索
五、WHERE子句
WHERE 语句可以和SELECT语句一起使用,精确查找的内容
语法:
SELECT field1,field2...fieldn
FROM table_name1 t1,table_name2 t2...
WHERE t1.condition1 [and [or]] t2.condition2...
tips:
1.多个表使用逗号分隔,每个表可以指定新的名字便于引用
2.WHERE可以指定多个条件,使用and or 来连接
3.WHERE也能于update、delect连用
4.组合使用:
GROUP BY:对SELECT查询出来的结果按照某个字段或者表达式分组,
获得一组组集合,然后从每组中取出一个指定字段或者表达式的值。
HAVING:对WHERE和group的分组进行过滤,是在结果集得到后过滤
FROM, including JOINs
执行顺序:
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
UNION
ORDER BY
LIMIT and OFFSET
操作符 描述
= 等于
<> != 不等于
>,>=,<,<= 大于(等)小于(等)
六、更新update
update set
用于更新表中的数据
语法:
update table_name set field1=value, field2=value2
[WHERE clause]
tips:
1.可以同时更新多个字段
2.能使用WHERE来指定任何条件
3.可以将字段改为默认值
4.set中可以把要更新的值当作变量操作,如:set age=age+1
5.替换某些字段:将title中的字段‘aaa’替换为‘bbb’
set title=repalce(title,'aaa','bbb')
七、删除DELETE
DELETE FROM
用于删除表的数据
语法:
DELETE FROM table_name [WHERE clause]
tips:
1.如果没有指定条件,将会删除所有记录
2.可以在表中删除一次性的记录
DELETE、DROP、truncate的区别
delect、truncate只删除数据,DROP删除表结构
truncate删除所有内容 truncate table_name
八、like子句
like用于模糊查询,
语法:
SELECT field
FROM table_name
WHERE field1 like 'xxx%'
tips:
1.like语句中,%表示任意数量的任意字符,和正则表达式的*是一样的
2.like如果不适用%,相当于=
3._下划线,表示任意一个字符的占位
写法 释义
%a a结尾的数据
a% a开头
%a% 包含a
_a_ 三个字符,中间的为a
[abc] 一个字符,可以是a,b,c # 没弄出来,暂时别信
[^abc] 不是a、b、c,的任意字符
九、union
将多个SELECT语句的结果联合到一起,SELECT语句会删除重复的数据
语法:
SELECT field1,field2...fieldn
FROM table1
[WHERE condition1]
union[all|distinct]
selsect fiald1,field2...fieldn
form table2
[WHERE clause]
tips:
1.distinct:(不同)可选,默认情况下union已经删除重复数据,因此distinct并无太大意义
2.all:返回所有的结果,包含重复的结果集
3.使用union是两个结果集上下连接
如果两个结果返回的字段不是同一类,也会拼接在一起
4.所以一般用来出两个表里面一样的列的数据,相当于合计
十、排序
order by
asc(ascend) desc(descend)
语法:
SELECT field1,filed2
FROM table_name1
[WHERE clause]
order by filed1[asc[desc]], field2[asc[desc]]...
tips:
1.可以有多个字段排序
2.排序的字段之间,使用逗号隔开
3.null总是排在前面
4.如果对汉字进行拼音排序,汉字是utf8编码的话,使用convert转化编码
:order by convert(name using gbk) asc
十一、GROUP BY
使用GROUP BY分组
在分组的列上,可以使用sum、average、count等函数
语法:
SELECT coalesce(sal,'合计') as '合计',sum(sal),count(sal)
FROM table_name
[WHERE clause]
GROUP BY sal
with rollup
tips:
1.使用sum(),average(),count()函数和GROUP BY处理
2.GROUP BY 会合并相同值的行,只显示第一条
3.使用coalesce(a,b[,c]),当a行出先none时写上b
4.关键词as 可以和coalesce一起用
5.with rollup:对分组的数据进行一次相同的统计
十二、sql的连接使用
inner jion 内连接 获取两表相同的记录
LEFT JOIN 左连接 以左边的表的优先,右边匹配
RIGHT JOIN 右连接 以右表优先,左表做匹配
语法:
SELECT t1.field,t2.filed
FROM t1 inner[|left|right] join t2
[WHERE clause]
tips:
1.INNER JOIN:连接两个表相同的内容
2.LEFT JOIN/RIGHT JOIN:当一边的表没有查询内容时,(比如表一没有name列,
但是表二有,左连接查询name列时,不指定条件)会左列表返回空
十三、正则表达式
regexp
除了like,还能使用正则表达式匹配字符
语法:
SELECT *
FROM table_name
WHERE name regexp '^S*' # 匹配以S开头的名字
tips:
模式 描述
^ 匹配输入的位置
$ 匹配结尾
. 匹配任意字符
[...] 匹配一个其中包含的任意字符
[^...] 匹配一个不包含在其中的任意字符
p1|p2|p3 匹配p1/p2/p3中的任意一个表达式
* 匹配前面的子表达式0到多次,相当于{0,}
+ 匹配前面的子表达式1到多次,相当于{1,}
{n} 匹配前面的子表达式n次
{m,n} 匹配前面的子表达式m-n次,m<=n,均为非负整数
十四、事务
一些列的sql语句构成一个事务。(描述的属一种行为)
事务专门为了处理大量的、复杂的sql操作
只有使用innoDB引擎的数据库或者数据表才能实现事务
通过 BEGIN 或 START TRANSACTION 开启一个事务
通过 commit 或 commit work 提交事务,是对数据的操作变为永久的
ROLLBACK/ROLLBACK WORK 回滚会结束用户的事务,并将未提交的修改撤销
SAVEPOINT IDENTIFIER 允许在事务中创建一个保存点,一个事务可以有多个保存点
RELEASE SAVEPOINT IDENTIFIER 删除一个事务的保存点,当不存在这个保存点的时候,会抛出异常
ROLLBACK TO IDENTIFIER 返回到指定的保存点
SET TRANSACTION 设置隔离级别,事务有四个隔离级别,包括READ UNCOMMITTED、READ COMMITED、REPEATABLE READ、SERIALIZABLE
事务的特点:原子性、一致性、隔离性、持久性
原子性:一个事务执行后,其中的操作要么全部执行,要么全部不执行,不会出现到中间就结束的情况,如果中间出错,可以使用回滚回到上一个保存点或者回到事务开始
事务的隔离性:
READ UNCOMMITTED:读未提交,
READ COMMITTED:读提交
REPAETABLE:可重复读
SERIALIZATION:串行化
事务的处理方法:
1.使用BEGIN、ROLLBACK、COMMIT(开始、回滚、提交)
2.使用SET AUTOCOMMIT=0[|1] 取消或设置自动提交
十五、ALTER选中表
ALTER选中表,对表格结构进行操作
ALTER选中后的操作
ADD 添加
DROP 删除
MODIFY 修改
1、添加新的字段
ALTER TABLE table_name add 'new_col' int|date after|first
添加表索引
ALTER TABLE table_name add index index_name(column_name)
2、修改modify\change
ALTER TABLE table_name modify col_name int|char(10) 修改字段的数据类型
ALTER TABLE table_name change col_name char(10) col_newName char(10) 修改字段的名字、数据类型
修改字段的时候,可以设置是否为null或者是否设置默认值,如下:
ALTER TABLE teble_name modify col_name int not null default 100;
修改表名
ALTER TABLE table_name rename to table_newName
修改表引擎
ALTER TABLE table_name engine=MYISAM;
3、删除
删除表字段
ALTER TABLE teble_name DROP col_name;
删除表字段的默认值,结合双ALTER、DROP来实现
ALTER TABLE table_name ALTER col_name DROP default;
删除外键约束
ALTER TABLE table_bame DROP foreign key keyname;
十六、索引
# 索引为是为了提交sql查询的效率而创造的,可以使查询的速度更快
# 不使用索引的情况下,查询的时候是遍历表中的所有内容,十分浪费效率
# 使用合理的索引,就如同使用字典目录一样,极快地提高了检索的效率
索引分为单列索引、组合索引(聚集索引、非聚集索引、联合索引)
单列索引:只包含一个列,一个表可以有多个单列索引
聚集索引(主键索引):数据库中,所有的行数按照主键排序
非聚集索引:普通字段的索引
组合索引:一个索引包含多个列
创建索引时,应该确保索引是应用于查询的条件
索引其实是一张表,这个表保存了主键以及索引字段并指向,也会占存储空间
滥用索引,会降低表的更新速度,如对表内容进行update、DELETE、insert等操作,索引也要随之改变
创建索引:
普通索引:没有限制的基本索引
CREATE INDEX index_name on table_name(col_name)
在创建表的时候,创建索引
CREATE TABLE tbale_name(
...
...
INDEX [index_name] (col_name(length))
)
联合索引:
CREATE INDEX index_name(col1,col2...)
删除索引:
DROP INDEX index_name on table_name
tips:
走索引的条件
索引遵循最左匹配的原则
KEY 'idx_age'('age')
KEY 'idx_name'('name')
查询形式 结果 结论
age like '%1' 不走索引 %在前不走索引
name like '刘%' 走索引 %在后走索引
age = 10+8 走索引
age+8=18 不走索引 用字段进行运算的不走索引
age != 20 不走索引 不等于不走索引
B+树、B-树?
B树最先是为了解决系统中,查询数据效率低的问题提出的。B树最早源于二叉树,二叉树是只有左右两个孩子结点的树,数据量大的时候,节点就越多
B树依据二叉树的形式,确定以度(当前结点的孩子结点数量)、阶(当前结点的最大的孩子结点)为度量单位的B树,
外存储器:数据存储在磁盘中,需要读取数据的时候要进行IO操作,每次读取到一个结点就要进行一次IO
内存储器:数据存储在内存中,查询快,但是容量小
B+树,解决了当数据量大的时候,使用外存储器的时候效率低的问题
1.结点不存储数据,只当索引用,所有的数据保存在子节点中
2.所有的中间结点,在子结点中要么是最大、要么是最小
3.叶子节点的数据,从小到大形成一个链表,可以支持范围查询
B+树的优势
1.单个结点可以存储更多的数据,减少了IO次数
2.查找性能稳定,因为最终都是到达叶子节点
3.叶子节点之间形成链表便于查询
B+树的查找方式
单元素查找:正常的按照结点查找,结点只存储索引,没有数据,所以就算再结点找到数据,也要向下到叶子节点才能获取数据
范围查找:查找最小值,然后查最大值,得到的叶子结点的两个数,这段数中间的数据
十七、临时表
1.创建临时表
1) CREATE TEMPORARY TABLE table_name(
...
);
2) CREATE TEMPORARY TABLe table_name1 [as](
SELECT *
FROM table_name2
[WHERE clause]
);
2.临时表在退出sql的时候,会自动删除,也能使用DROP主动删除
十八、复制表
直接使用CREATE TABLE ...SELECT 的命令,无法有效复制表结构
复制表结构
1.使用SHOW CREATE table table_name 查看创建表的时候的语句
2.复制显示的sql语句,修改数据表名,并执行
3.赋值表的内容,可以使用insert into ... SELECT实现
语法:
1) CREATE table table_name(
...) (
SELECT field1
FROM table1
[WHERE clause]
)
2) CREATE table targetTable like sourceTable
十九、MySQL的元数据
sql的信息分为以下三种
1.查询结果信息:包含SELECT、DELETE、update等语句影响的记录和记录数
2.数据库和数据表的信息:数据库以及数据表的结构信息
3.MySQL的信息:版本、当前状态等
语句 功能
SELECT databases() 返回当前使用到的数据库
SELECT version() 返回当前的MySQL的版本
SELECT user() 返回当前用户
SHOW status 返回MySQL的状态信息
SHOW variables 返回MySQL的配置
二十、序列
使用auto_increment是在创建表格时,让序列自增
在设置完每类的结构之后,可以在engine设置后是一个 auto_increment=n,设置初始值为n
二十一、重复数据的处理
通过添加主键(primary key)或者唯一(unique)索引保证数据的唯一性
1.设置方法:
1)创建表结构时设置,如 id primary key,`name` unique key
primary key (id )
unique (`name`)
2)insert ingrone 可以忽略这种限制
2.统计重复数据
firstName/lastName
select fristName,lastName,count(*) as repeatitions
from table_name
group by firstName,lastName
having repaetitions>1;
3.统计不重复的数据
select distinct id
from table_name
select firstName,lastName
from table_name
group by (firstName ,lastName)
3.删除重复数据,
将不重复的部分提取出来到新表,删除原表,然后将新表名字改为原表
create table new_table select id,unname,sex,age from old_table group by id,uname,sex,age
drop table old_table
alter table new_table rename to old_table