目录
2. 查看表结构 SHOW COLUMNS, DESCRIBE
2. 流程控制语句 IF, CASE, WHILE, LOOP, REPEAT, ITERATE
一. 数据库操作
1. 认识数据库
1.1 相关概念
(1)数据库是按照数据结构来组织,存储和管理数据的仓库,是存储在一起的相关数据的集合。
优点:
1)减少数据的冗余度,节省数据的存储空间。
2)具有较高的数据独立性和易扩充性。
3)实现数据资源的充分共享。
(2) 数据库系统
(3)数据库管理系统
(4)关系数据库
1.2 数据库常用对象
表,字段,索引,视图,存储过程
1.3 系统数据库
(1)information_schema数据库
(2)performance_schema数据库
(3)sakila数据库
(4)test数据库
(5)World数据库
2. 创建数据库 CREATE
(1)create database 库名;
(2)create schema 库名;
(3)指定字符集:
create database 库名 (库名后不加分号,按回车键,即换行) character set = GBK;
(4)创建前先判断是否已存在同名库:create database if not exists 库名;
3. 查看数据库 SHOW
(1)show databases;
(2)show databases like '模式';
(3)show databases like '模式' where 条件;
可用databases或者schemas
4. 选择数据库 USE
(1)use 库名;
USE语句可以实现选择一个数据库,使其成为当前数据库。只有使用USE语句指定某个数据库为当前数据库后,才能对该数据库及其存储的数据对象执行操作。
5. 修改数据库 ALTER
(1)修改数据库的相关参数,设置默认字符集和校对规则
> alter database 库名
> default character set gbk(gbk是字符集名)
> default lollate gbk_chinese_ci; (gbk_chinese_ci是校对规则)
创建一个数据库后,可以对其相关参数进行修改,但是不能使用这个命令修改数据库名。
6. 删除数据库 DROP
(1)drop database 库名;
(2)drop database if exists 库名;
可用database或schema
使用该命令删除数据库的同时,该数据库中的表以及表中的数据也将永久删除。注意!
二. 存储引擎和数据类型
1. MySQL存储引擎
1.1 MySQL存储引擎的概念
MySQL中的数据用各种不同的技术存储在文件或内存中。
这些技术中的每一种技术都是用不同的存储机制,索引技巧,锁定水平并且最终提供广泛的不同的功能和能力。
通过选择不同的技术,能够获得额外的速度或者功能,从而改善引用的整体功能。
这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。
MySQL默认配置了许多不同的存储引擎,可以预先设置或者在MySQL服务器中启用。
1.2 查询MySQL中支持了哪些存储引擎?
(1)查询支持的全部存储引擎
命令:show engines;或 show engines\g 或 show engines\G
(2)查询默认的存储引擎
命令:show variables like 'storage_engine%';
1.3 InnoDB存储引擎
MySQL上第一个提供外检约束的表引擎。
1.4 MyISAM存储引擎
1.5 MEMORY存储引擎
1.6 如何选择存储引擎
1.7 设置数据表
2. MySQL数据类型
2.1 数字类型
数字类型总体可以分为整型和浮点型
2.2 字符串类型
(1)普通的文本字符串类型(CHAR和VARCHAR)
(2)可变类型(TEXT和BLOB)
(3)特殊类型(SET和ENUM)
2.3 日期和时间类型
三. 操作数据表
1. 创建数据表 CREATE TABLE
(1)语法:
> create table 表名 (
> 列名1 属性,
> 列名2 属性...);
(2)创建一个临时表:
> create temporary table if not exists 表名 (
> 列名1 属性,
> 列名2 属性...);
2. 查看表结构 SHOW COLUMNS, DESCRIBE
(1)使用 show columns 语句查看
show columns from 表名 from 库名;
或者:
show columns from 表名.库名;
(2)使用 describe 语句查看
describe 表名;
或者
desc 表名;
查看某一列信息: desc 表名 列名;
3. 修改表结构 ALTER TABLE
(1)添加新字段及修改字段定义
语法:
> alter table 表名 add 要新增的列名 属性,
> modify 要修改属性的列名 属性;
备注:
通过alter语句修改表列,前提是必须将表中数据全部删除,然后才可以修改表列。
(2)修改字段名
语法:
> alter table 表名,
> change column 旧列名 新列名 属性;
(3)删除字段
语法:
> alter table 表名 drop 要删除的列名;
(4)修改表名
语法:
> alter table 旧表名 rename as 新表名;
4. 重命名表 RENAME
(1)语法:
> rename table 旧表名 to 新表名;
备注:
可以同时对多个数据表进行重命名,多个表之间以逗号分隔
5. 复制表 CREATE TABLE
(1)Create table的另外一个作用,就是备份某个表格。
语法:这个命令,只备份表(含字段),不包含表中的数据。
> create table if not exists 备份表名 like 原表名;
(2)这个命令,同时备份表(含字段)+ 表中的数据。
语法:
> create table 备份表名 as select * from 原表名;
6. 删除表 DROP TABLE
(1)语法:
> drop table if exists 表名;
四. MySQL基础(运算符,流程控制语句)
1. 运算符
1.1 算术运算符
1.2 比较运算符
(1)=:
用来判断数字,字符串和表达式是否相等。
判断两个字符是否相等时,数据库系统是根据字符的ASCII码进行判断的。
切记:空值null不能使用 = 来判断。
(2)!= 和 <>:
用来判断数字,字符串和表达式是否不相等。
(3)>:
判断左边操作数是否大于右边操作数。同理,>=,<,<=。
(4)IS NULL:
用来判断操作数是否为空值。
备注: <,<=,>,>=,=,!=,<>不能用来判断空值,一旦使用,结果返回null。
is null, is not null, <=>可以用来判断空值。
(5) BETWEEN AND:
用来判断是否在某个取值范围内。 row between x and y...
(6)IN:
用来判断数据是否存在某个集合中。x1 in (值1, 值2...值n)...
(7)LIKE:
用来匹配字符串。
语法:x1 like s1.
备注:如果x1与字符串s1匹配,返回1。
比如: select user, user like 'mr', user like '%k%' from 表名;
(8)REGEXP:
也用于匹配字符串,但使用的是正则表达式。
语法:x1 regexp '匹配方式'
备注:如果x1满足匹配方式,返回1。
比如:select user, user regexp 'm$', user regexp 'k' from 表名;
意思是 用来匹配user字段的值是否以m结尾,并包含k的字符串。
1.3 逻辑运算符 AND, OR, NOT, XOR
(1)与运算:如果存在一个数据为null且没有数据为0,返回null
(2)或运算:如果数中不包含非0的数字,但包含Null, 返回null
(3)非运算:null的非运算,返回null
(4)异或运算:只要其中任一数据是null, 返回null
1.4 位运算符
1.5 运算符的优先级
2. 流程控制语句 IF, CASE, WHILE, LOOP, REPEAT, ITERATE
2.1 IF语句
2.2 CASE语句
2.3 WHILE循环语句
2.4 LOOP循环语句
2.5 REPEAT循环语句
2.6 ITERATE语句
ITERATE lable:可以出现在LOOP, WHILE和REPEAT语句内,表示“再次循环”
五. 表数据的操作:增,删,改,查
1. 插入数据
1.1 insert...values语句
语法:
insert into 表名 values (值1,值2, 值3...值n);
//表示插入完整数据
insert into 表名(列名1,列名2) values(值1,值2);
//表示插入数据记录的一部分
insert into 表名(列名1,列名2) values(值1.1,值1.2),(值2.1,值2.2);
//表示插入多条记录,用逗号隔开多个记录
1.2 insert...set语句
语法:
insert into 表名 set 列名1=值1,列名2=值2...列名n=值n
1.3 insert...select语句
将查询结果插入到指定的表中。
语法:
insert into 表2名(列名1,列名2) select 列名1,列名2 from 表1名;
2. 修改数据
2.1 语法:
update 库名.表名 set 列名2=值2 where 列名1=xxx;
3. 删除数据
3.1 通过 delete 语句删除数据
语法:
delete from 库名 where 列名=xxx;
备注:通过where条件指定删除某个项。没有where条件的话删除所有记录。
3.2 通过 truncate table 语句删除数据
语法:
truncate table 库名.表名;
备注:从表中删除所有的行,并且无法恢复。
六. 数据查询
1. 基本查询语句
1.1 使用select语句查询一个数据表
语法:
select * from 表名;
1.2 查询表中的一列或多列
语法:
select 列名1,列名2 from 表名;
1.3 从一个或多个表中获取数据
语法:
select 表1.列1, 表1.列2, 表2.列1,表2.列2 from 表1,表2;
备注:
(1)如果数据中涉及中文,有可能输出时出现乱码,最后在执行查询操作之前,用set names语句设置编码格式。
(2)还可以在where子句中使用连接运算来确定表之间的联系, 比如:
select 表2.列名3 from 表1, 表2 where 表1.列名1=表2.列名1 and 表1.列名2=xxx;
2. 单表查询
2.1 查询所有字段
select * from 表名;
2.2 查询指定字段
select 字段名1,字段名2 from 表名;
2.3 查询指定数据
select * from 表名 where 字段名=某字段值;
2.4 带关键字 IN 的查询
select * from 表名 where 字段名 IN (字段值1, 字段值2...)
2.5 带关键字 BETWEEN AND 的查询
select * from 表名 where 字段名 BETWEEN 值1 AND 值2;
select * from 表名 where 字段名 not BETWEEN 值1 AND 值2;
2.6 带 LIKE 的字符匹配查询
模糊查询,有两个通配符:
(1)“%”:可以匹配一个或多个字符,可以代表任意长度的字符串,长度可以为0。
(2)“_”:只匹配一个字符,可代表任意一个字符。这点上,英文字母和中文都算一个字符。
select * from 表名 where 列名 like ’模式‘; //模式比如 %sh%
2.7 用关键字 IS NULL 查询空值
select 列名1,列名2 from 表名 where 列名2 IS NULL;
2.8 带关键字 AND 的多条件查询
联合多个查询条件,只有同时满足所有查询条件的记录才会被查询出来。
select * from 表名 where 条件1 AND 条件2;
2.9 带关键字 OR 的多条件查询
只需满足查询条件中的一个,记录就会被查询出来。
select * from 表名 where 条件1 OR 条件2;
2.10 用关键字 DISTINCT 去除结果中的重复行
可以去除查询结果中的重复记录。
select distinct 字段名 from 表名;
即: 如果表中有多个username为Sheryl的记录,name查询结果中只会出现一行 Sheryl记录.
2.11 用关键字 ORDER BY 对查询结果排序
可以对查询的结果进行升序(ASC)和降序(DESC)排列。默认情况下ORDER BY按升序输出。
select * from 表名 order by 字段名 DESC;
2.12 用关键字 GROUP BY 分组查询
可以将数据划分到不同的组中,实现对记录进行分组查询。
在查询时,所查询的列必须包含在分组的列中,目的是使查询到的数据没哟矛盾。
(1)使用关键字 group by 来分组
单独使用group by的话,查询结果只显示每组的一条记录。
select 字段1,字段2,字段3 from 表名 GROUP BY 字段3;
(2)关键字 group by 和 group_concat()函数一起使用
可以将每个组中的所有字段值都显示出来。
select 字段1,字段2,CONCAT(字段3) from 表名 GROUP BY 字段3;
(3)按多个字段进行分组
select 字段1,字段2,字段3 from 表名 GROUP BY 字段1,字段3;
2.13 用关键字 LIMIT 限制查询结果的数量
(1)可以对查询结果的记录条数进行限定,控制它输出的行数。
select * from 表名 order by 字段1 limit 数量;
(2)还可以从查询结果的中间部分取值。
定义两个参数:
参数1:开始读取的第一条记录的编号(表中记录编号从0开始);
参数2是要查询记录的个数。
select * from 表名 where 字段1 order by 字段1 asc limit 参数1,参数2;
3. 聚合函数查询
聚合函数的最大特点是根据一组数据求出一个值。
聚合函数的结果值只根据选中行的非NULL值进行计算,NULL值被忽略。
3.1 COUNT()函数
(1)没有*:返回所选择集合中非NULL值得行的数目。
(2)包含*:返回选择集合中所有行的数目,包含NULL值的行。
(3)语法:
select count(*) from 表名;
3.2 SUM()行数
(1)可以求出表中某个字段取值的总和。
(2)语法:
select sum(字段名) from 表名;
3.3 AVG()函数
(1)可以求出表中某个字段取值的平均值。
(2)语法:
select AVG(字段名) from 表名;
3.4 MAX()函数
(1)可以求出表中某个字段取值的最大值。
(2)语法:
select MAX(字段名) from 表名;
3.5 MIN()函数
(1)可以求出表中某个字段取值的最小值。
(2)语法:
select MIN(字段名) from 表名;
4. 连接查询
连接是把不同表的记录连到一起的最普通的方法。
4.1 内连接查询
(1)内连接是最普通最匀称的连接。要求构成连接的每一部分的每个表的匹配,不匹配的行将被排除。类似并集。
(2)最常见的是相等连接。即:连接后的表中的某个字段与每个表中的都相等。这种情况下,最后的结果集只包含参加连接的表中与指定字段相等的行。
(3)语法:
select 字段1,字段2 from 表名1,表名2 where 表名1.字段A=表名2.字段A;
4.2 外连接查询
(1)外连接是指使用 OUTER JOIN 关键字将两个表连接起来。外连接生成的结果集不仅包含符合连接条件的行数据,还包括左表或者右表或者两边连接表中的所有的数据行。类似合集。
外连接分为:左外连接,右外连接,全外连接。
(2)左外连接 left join:
是指将左表中的所有数据分别与右表中的每条数据进行连接组合,返回的结果除了内连接的数据外,还包括左表中不符合条件的数据,并在右表的相应列中添加NULL值。
语法:
select 字段1,字段2 from 左表 left join 右表 on 左表.字段A=右表.字段A;
(3)右外连接 right join:
是指将右表中的所有数据分别与左表中的每条数据进行连接组合,返回的结果除了内连接的数据外,还包括右表中不符合条件的数据,并在左表的相应列中添加NULL值。
语法:
select 字段1,字段2 from 右表 right join 左表 on 右表.字段A=左表.字段A;
(4)全外连接:
4.3 复合条件连接查询
(1)连接查询时,增加其他的限制条件,通过多个条件的复合查询,可以使查询结果更加准确。
(2)比如: select 字段1,字段2 from 表1,表2 where 表1.字段A=表2.字段A and 字段3>5;
5. 子查询
什么是子查询?
就是select查询时另一个查询的附属。MySQL4.1可以嵌套多个查询,在外面一层的查询中使用里面一层查询产生的结果集。这样就不是执行两个(多个)独立的查询,而是执行包含一个或者多个子查询的独立查询。
这种情况,MySQL从最内层的查询开始,然后从它开始向外向上移动到外层查询。这个过程中每个查询产生的结果集都被赋给包围它的父查询,接着这个父查询被执行,它的结果也被指定给父查询。
子查询可以使用在任何可以使用表达式的地方。
必须由父查询包围,必须包含一个字符列表(单列表),一个具有一个或多个表名字的FROM子句以及可选的WHERE, HAVING 和 GROUP BY 子句。
5.1 带关键字 IN 的子查询
(1)IN 运算符可以检测结果集中是否存在某个特定的值,如果检测成功,就执行外部的查询。
(2)语法:
select * from 表1 where 字段名1 in (select 字段名1 from 表2);
类似: NOT IN
5.2 带比较运算符的子查询
(1)子查询可以使用比较运算符,比如 <,<=,>,>=,=和!= 等。
(2)比如:
select 字段1,字段2 from 表名1 where 字段3>= (select 字段3 from 表2 where 字段4=xxx);
5.3 带关键字 EXISTX 的子查询
(1)带关键字 EXISTS 时,内层查询语句不返回查询的记录,而是返回一个真假值。如果内层查询语句查询到满足条件的记录,就返回一个真值(true),否则返回一个假值。当返回真值时,外层查询语句将进行查询。当返回假值时,外层查询语句不进行查询或者查询不出任何记录。
(2)语法:
select * from 表名1 where exists (select * from 表名2 where 字段1=xxx);
(3)当关键字 EXISTS 和其他查询条件一起使用时,需要使用 AND 或者OR 来连接表达式和 EXISTS 关键字。
(4)语法:
select * from 表名1 where 字段2>yyy and exists (select * from 表名2 where 字段1=xxx);
5.4 带关键字 ANY 的子查询
(1)ANY表示满足其中任意一个条件。使用关键字ANY时,只要满足内层查询语句返回的结果中的任意一个,就可以通过该条件来执行外层查询语句。
(2)语法:
select 字段1,字段2 from 表名1 where 字段3< ANY(select 字段3 from 表名2);
(3)备注: < ANY 表示小于所有值。
5.5 带关键字 ALL 的子查询
(1)ALL表示满足所有条件。使用关键字ALL时,只有满足内层查询语句返回的所有结果,才可以执行外层查询语句。
(2)语法:
select 字段1,字段2 from 表名1 where 字段3 >= ALL(select 字段3 from 表名2);
6. 合并查询结果
合并查询结果,是将多个select语句的查询结果合并到一起。关键字 UNION 和 UNION ALL。
(1)UNION:
将所有的查询结果合并到一起,然后去除相同记录。
语法:
> select 字段1 from 表1
> UNION
> select 字段2 from 表2;
(2)UNION ALL:
将所有的查询结果合并到一起,保留相同记录。
语法:
> select 字段1 from 表1
> UNION ALL
> select 字段2 from 表2;
7. 定义表和字段的别名
7.1 为表取别名
select * from 表1 别名 where 别名.字段1=xxx;
7.2 为字段取别名
select 字段1 as 别名1, 字段2 as 别名2 from 表名;
8. 使用正则表达式查询
(1)正则表达式,是某种模式去匹配一类字符串的一个方式。正则表达式的查询能力比普通字符的查询能力强大,更加灵活。
(2)基本语法:
字段名 regexp '匹配方式'
(3)模式字符如下:
七. 常用函数
1. MySQL函数
2. 数学函数
(1)数学函数汇总
(2)比如:
select ABS(5), ABS(-5);
select FLOOR(1.5);
select RAND(), RAND();
select ROUND(RAND()*100), FLOOR(RAND()*100), CEILING(RAND()*100);
select PI();
select truncat(1.2345, 3);
select ROUND(1.2), ROUND(1.23456,3);
select SQRT(16), SQRT(25);
.......
3. 字符串函数
(1)字符函数汇总
(2)比如:
select insert(s1,x,len,s2);//用字符串s2, 替换字符串s1中x位置开始的长度为len的字符串
select upper(s), ucase(s);//将字符串s的所有字母变成大写字母
select left(s,n);//返回字符串s的前n个字符
select concat('+', rtrim(' sh '), '+'); //去掉字符串sh结尾处的空格
select substring(s,n,len); //从字符串s的第n个位置开始,获取长度为len的字符串
select reverse(s); //将字符串s的顺序反过来
select field(s,s1,s2,...); //返回第一个字符串s匹配的字符串的位置
下面这三个函数,都表示:子字符串s1在字符串s中的开始位置
select locate(s1,s);
select position(s1 IN s);
select instr(s,s1);
4. 日期和时间函数
5. 条件判断函数
6. 系统信息函数
7. 加密函数