MySQL
查询 select
- 查询一个表的所有数据
# 检索所有列
select * from tableName;
# 一般不要使用*检索，因为会降低检索和程序的性能
- 查询单个列和多个列
# 查询单个字段 也可使用字段的全限定名即：表名.字段名-->tableName.columnName
select columnName from tableName;
# 查询多个字段，字段之间用逗号隔开，最后一个字段不写逗号
select columnName1,columnName2,...columnNamen from tableName;
- 检索不同的行，关键字 distinct
select distinct columnName from tableName;
# distinct 关键字适用于查询语句中的所有列，而不是他的前置列
# select disticnt columnName1,columnName2 from tableName，选择columnName1,columnName2都不同的列
# count(distinct columnName);
- 限制结果查询，关键字 limit
# 检索columnName1，columnName2 从第三行开始，返回五行。
select columnName1,columnName2 from tableName limit 3,5;
# 等价于
select columnName1,columnName2 from tableName limit 5 offset 3;
# mysql 检索出来的第一行为行0,下标是0开始计算,在行数不够时，有多少行返回多少行。
-
过滤数据，关键字之一 where
常见操作符说明
= 等于 <> 和 != 不等于 < <= 小于和小于等于 (>=)和 > 大于等于和 大于 between 在指定的两个值之间
# 1.检索单一值
# 查询 columnName1 = xxx 的columnName1,columnName2字段
select columnName1,columnName2 from tableName where columnName1 = xxx;
# 2.检索多个值
# 查询 products 表中价格大于5的 prod_name,prod_price 的所有产品
select prod_name,prod_price from products where prod_price > 5;
# 3.不匹配查询
# 查询价格不等于5的所有产品
select prod_name,prod_price from products where prod_name != 5;
# 4.范围查询 ***between***
# 查询价格在 5和10 之间的所有产品
select prod_name,prod_price from products where prod_price between 5 and 10;
1. 空值查询 null
# 空值查询 null
# 查询价格为 null (is null)的产品 ！！！不是价格为 0 的产品
select prod_name,prod_price from products where prod_price is null;
# 查询cust_email为null的用户
select cust_email from customer where cust_email is null;
-
数据过滤之组合使用，关键字 and 和 or
# 1.and 对不止一个列进行过滤时 用在 where 语句中，用来检索满足所有给定条件的行 # 查询prod_id = 1且价格大于10的商品 select prod_id,prod_name,prod_price from products where prod_id = 1 and prod_price >10; # 2.or 查询匹配任一条件的行 # 查询prod_id = 1 或者prod_id = 2的产品 select prod_id from products where prod_id=1 or prod_id = 2; # 3.and和or的组合使用 and和or # 3.1 计算次序的问题：在mysql中 and的计算次序要高于or 因此需要加()进行明确的分组操作 # 查询prod_id = 1或者prod_id = 2中价格大于10的商品 select prod_id,prod_price from products where prod_id = 1 or prod_id = 2 and prod_price >10; # 错误写法sql 会执行(prod_id = 2 and prod_price >10)之后 在进行or操作 # 即查询prod_id = 2且价格大于10和prod_id = 1 价格任意的商品 # 改正 select prod_id,prod_price from products where (prod_id = 1 or prod_id = 2) and prod_price >10; -
操作符 in
用于指定条件范围，对范围中的每个条件都可以进行匹配
# 查询prod_id = 1 和prod_id = 2 的商品信息
select * from products where prod_id in(1,2);
select * from products where prod_id = 1 or prod_id = 2;
# 上述两条sql语句等价 说明：
# in 和 or操作符能完成相同的功能，in较于or有一下优点
# 1.在使用长的合法选项清单时，in更加直观
# 2.in的计算次序更容易管理
# 3.in的效率高于or
# 4.in的最大优点可以包含其他select语句，(子查询)
- 操作符 not
# 在 MySQL中，可以使用not对in，between，exists子句取反表示，
# not 否定它之后的条件
select * from products where prod_id not in(1,2,3);
- 谓词 like 和通配符
| 符号 | 含义 |
|---|---|
| % | 表示任意字符出现任意次数 |
| _ | 与%一样，但是表示出现的次数只有一次 |
# 查询users表中姓刘的username
select username from users where username like '刘%';
#查询两个字且名超的username
select username from users where username like '_超';
/*
说明：
1.不要过度使用通配符
2.通配符中严格区分大小写，'%jet'和'%JET'含义不一样
3.通配符实际是SQL的where子句中特殊含义的字符。
*/
字段
- 拼接字段，函数 concat()
select concat(username,password1) '测试连接别名' from users
- 消除空格
# 消除字段的空格 trim()左右两边的空格，rtrim(str)消除str右边的空格 ltrim();
select trim(username) from users;
# MySQL语句中 可以执行四则运算！注意运算的优先级.
函数
1.文本类函数
# upper() 将文本转为大写
select upper(username) from users;
# lower() 将文本转为小写
select lower(usernaem) from users;
# left(columnName,len);
select left(username,1) from users;
# right()
# length() 返回长度 参数可以是一个具体的字符串，也可以是columnName
SELECT LENGTH(username) from user_test;
# locate(substr,str)返回stubstr在str中的位置，locate(sub,str,pos);从指定pos开始找
select locate('s','test') from user_test;
select locate('t','test',2) from user_test;
# trim() 消除空格
# substring();返回从二开始长度为2的字符串
SELECT SUBSTRING(username FROM 2 FOR 2) from user_test;
# soundex();返回发音与所给字段类似的行
select username from users where soundex(username) = soundex('hello');
- 日期类函数
# AddDate();增加一个日期(天，周等)
# AddTime();增加一个时间(时，分等)
# CurTime();返回当前时间
# CurDate();返回当前日期
# Day(); 日期中的天数部分
# DayOfWeek();
# Hour();
# Minute();
# Month();
# Now();当前日期和时间
# Second();
# Time();
# Year();
### mysql中日期的格式一定要指定为yyyy-MM-dd
### 如果仅仅使用日期，用Date();
- 数值处理类函数
# Pi(); 返回圆周率
# Abs(); 返回一个数的绝对值
# Exp(); 返回一个数的指数值
# Mod(); 返回除操作的余数
# Sin(); 返回一个角度的正弦值
# Cos(); 返回一个角度的余弦值
# Tan(); 返回一个角度的正切值
# Rand(); 返回一个随机数
# Sqrt(); 返回一个数的平方根
- 聚集函数
# Avg(); 只能用于单个列，用来确定数值列的平均值。而且列名必须作为参数给出。会忽略值为null的行
# Count(); 确定行的数目或者符合条件的列的数目，
## count(*); 计算所有的行，不会忽略null值的行
## count(columnName); 会忽略指定列中值为null的行
# Max(columnName); 返回指定列的最大值，会忽略指定列中值为null的行，允许返回任意列，当列为文本时且按照列排序，返回最后一行
# Min(column); 返回指定列值的最小值，会忽略指定列中值为null的行，允许返回任意列，当列为文本时且按照列排序，返回最前面的一行
# Sum(columnName); 返回指定列值的和，会忽略指定列中值为null的行,
- 分组数据 group by
# 1.group by子句中可以包含多个字段。表示将多个字段相同的行归为一组
# 查询年龄相同，同一班班级的人数
select age,count(age) from classTable group by age,grade;
# 2.group by子句中可以嵌套分组，数据将在最后的规定的分组进行汇总。
# 3.group by子句中的每一个列都必须是检索列或者有效的表达式(不能是聚集函数),不能使用别名。
# 4.select查询的每个列都必须在group中出现。否则在group by中没出现的字段不会查询！！！
# 5.如果分组的列中具有null值，则将null作为一个分组返回，如果有多个nul值，则将他们作为一组返回
# 6.group by子句必须出现在where之后 order by 之前。
# 7.一般在使用group by之后要使用order by子句！！！
# 8.order by子句不能是子查询
过滤
- 过滤分组 havaing
# having和where的区别
# 1.where是对行进行过滤，having是对组进行过滤
# 2.having支持所有where操作符，大部分情况下where可以用having替换 句法相同只是关键字的区别
# 3.where是在数据分组前进行过滤，having是在数据分组后进行过滤。
# 对students表中年龄大于10且在同一个班的不同age分组。
select age,grade from students where age >10 group by age,grade;
- 分组和排序
| order by | group by |
|---|---|
| 排序产生的输出 | 分组行，但输出的不一定是分组的顺序 |
| 任意列都可以使用(包括非选择的列) | 只能使用选择列或者表达式列，而且必须使用每个选择列表达式 |
| 不一定需要 | 如果与聚集函数一起使用列，则必须使用 |
- select语句执行顺序
select columnName,sum(columnName) as col from tableName where columnName > val group by columnName
having sum(columnName)>val2 order by col limit 2;
子查询
- 子查询
# 子查询：将一个select语句查询的结果作为另一个select语句的条件
# 1.列必须匹配，在where子句中使用子查询，则应该保证select语句中具有与where子句中相同数目的列。
# 通常子查询将返回单个列并且与单个列匹配。如果需要也可以使用多个列
# 2.由于性能的限制，不能嵌套多个子查询。
select cust_name,cust_contact from customers
where cust_id in (select cust_id from orders where order_num in(
select order_num from orderiterms where prod_id = "xxx" ));
# 说明：最内层where后的prod_id 和 select 的order_num,次外层order_num 和cust_id.
##############################################################################
# 相关子查询：涉及外部查询的子查询 (select count(*) from orders where orders.id = customers.id ) as orders
# 1.任何时候只要列名可能有多义性，必须使用全限定名。orders.id = customers.id
select cust_name,cust_state
(select count(*) from orders where orders.id = customers.id ) as orders from customers
order by cust_name;
# 使用子查询时，应该由内向外逐层查询测试，有利于查找结果不正确时，分析问题所在。
- 联结查询
# 1.自联结：在单条select语句中不止一次引用相同的表
# 查询一个产品的供应商供应的所有产品
# 首先查找除产品id为xdf的供应商，然后查找供应商的所有产品。关联条件：p1.vend_id = p2.vend_id p2.vend_id决定p1.vend_id.
select p1.prod_name,p1.prod_id from products p1,products p2
where p1.vend_id = p2.vend_id and p2.prod_id = "xdf";
# 2.左连接 A left join B on 关联条件; 用A去匹配B A为基准表
# 3.有连接 A right join B on 关联条件; 用B去匹配A B为基准表
## 简记：AB两个表 right就是右边的表B为基准表 left 就是左边的表A为基准表
- 组合查询 union
# 规则：
# 1.union必须由两条或两条以上的select语句组成。语句之间用union连接
# 2.union中的每个查询必须包含相同的列，表达式或聚集函数(顺序没要求)。
# 3.列数据类型必须兼容。类型不一定完全相同，但必须是dbms可以隐含转换的类型。
# 4.union自动取消重复的行，union all 返回所有的行，包含重复的行。
# 5.在用union组合查询时，只能使用一次order by子句，且必须出现在最后的一条select语句中。
select prod_id,prod_name from products where prod_price > 5
union
select prod_id,prod_name from products where vend_id in (1,2);
- 全文本搜索
# 1.不是所有引擎都支持全文本搜索。
# 2.MyISAM和InnoDB前者支持全文本搜索，后者不支持。
# 3.一般在创建表时启用全文本搜索-->fulltext(columnName,columnName1...)。
# 4.不要在导入数据时使用fulltext();在导入数据建立新表时，先建立表，在修改表
# 5.match(columnName) against(str);使用match()指定搜索的列，against()指定要搜索的字符串
# 6.传递给match()的值必须与fulltext()中定义的一致，如果指定多个列，则必须列车他们(且顺序一致);
# 7.默认不区分大小写;除非使用binary()方式。
# 8.全文本搜索的会对搜索结果进行排序，具有较高等级的行优先返回。like搜索不会。
# 9.文本词中靠前的行具有较高的优先级
# 10.排序多个搜索项时，一行中词较多的具有较高的优先级
# 11.全文本搜索的是索引效率高于like
select note_text from productnotes where Match(note_text) Against('rabbit');
# 12.with query expansion 扩展查询
select note_text from productnotes where Match(note_text) Against('rabbit' with query expansion);
插入 insert
# 1.插入完整的行
# values()中给出所有列的值
insert into tableName values();
# 2.插入部分的行
insert into tableName(columnName1,columnName2) values(val1,val2);
# 3.插入多行
## 3.1编写多个insert 语句 一次提交
insert into tableName values();insert into tableName values();
## 3.2在insert语句中使用()赋予多个值
insert into tableName values(),(),();
# 4.插入某些查询结果 insert select
## 把oldTable中的数据迁移到newTable中。
insert into newTable (id,username,password1,...) select id,username,password1 from oldTable;
## 在迁移过程中注意主键重复的问题。
更新 update
# 组成：要更新的表+列名和新值+过滤条件
# 1.更新表中的特定行
update users set username = 'new',password1 = 'newpassword' where id = 1;
# 2.更新表中的所有行 不加过滤条件则会更新所有行
update users set username = 'new',password1 = 'newpassword';
# 3.update语句中可以使用子查询
# 4.当执行update语句更新多行时，有一行发生错误，则全部回滚到未更新状态，使用ignore会忽视的错误，继续执行
update ignore tableName...
删除 delete
# 1.删除特定的行
delete from tableName where columnName = val;
# 2.删除所有的行
delete from tabelName;
# 3.delete删除的是表中的数据，不是表
# 4.如果要删除表中所有的行，使用如下,可恢复主键
truncate table tableName;
表相关的操作
更新表
# 给表添加个列 必须确定列名
alter table tablename add columnName CHAR(20);
# 给表删除一个列
alter table tableName drop columnName;
删除表
drop table tableName;
重命名
rename table oldTableName to newTableName;
