mysql 语句整理和复习（一）

【DBMS，Database Management System】

以前在学校的时候，学校开设了数据库的课程，对于大多数人，因为基本语句很简单，所以大家都认为学会了，整个学期，逃课率高居榜首。不过我相信，那些将来准备编程的，一定会后悔当初的决定。一个不会sql的程序员。。。。

---

基本操作
show databse;
create databse mydb;
use mydb;
show tables;
desc mytabel;
drop databse mydb;

/**
* 数据类型：
* 数值、字符、日期、时间
* 【注意点】
* 1.char（M），varchar（M)：长度不足时，一个是右侧填充空格，一个是不进行填充。
*/

建表和修改
create table mytabel(
name varchar(20),
age int,
sex char(2)
);
create table if not exists person();
alter table mytabel add(id int comment ‘the id of students’);
alter table mytabel add height int;

alter table mytabel modify height float; 只能修改类型
alter table mytabel change height weight int; 字段名和类型都可以修改

alter table mytabel drop sex;

alter table mytabel rename to students;

drop table mytabel;

查询语句
select name,age,sex from students;
select name,age from students where age>20;
select name,age from students where age>20 and age<30;
select name,age from students where age>17 or age<10;
select name,age from students where age in (12,16,18,20);
select name,age from students where age between 12 and 30;

is null:mysql中，null不等于任何值，包括自身。要判断是否为null，需要使用is null
或者使用<=>（可以比较null值的等号）
select name,age from students where name is not null;
select name,age from students where name <=>null;

distinct 去重
当查询多个字段时，只有多个字段全部相等时，才认为记录时重复的。
部分字段相等，则不认为是重复的。
select distinct name,age from person;
limit count 取结果集中前count条记录
select name, age from person limit 3;
limit start, count取结果集中从start索引位置开始的前count条记录
start索引从0开始。
select name, age from person limit 2, 2
limit count 等价于 limit 0, count

模糊查询
% 通配符：任意0个或者多个字符
_表示任意一个字符
eg:
%x% 包含x的项目
%x 以x结尾的项目
x% 以x开头的
\% 转意%。
select name, age from person where name like ‘%点%’
select name, age from person where name like ‘张%’
select name, age from person where name like ‘%张’
select name, age from person where name like ‘张__’
select name, age from person where name like ‘%\%%’
select name, age from person where name like ‘%’

插入数据
insert into table person(id,name) values (1,’zhang’);
insert into person(1,’wang’,23);

update person set name=’ming’ where id=’1’

删除
delete from person where id=’1’;
truncate table person;
truncate与delete都可以删除表中的数据。
truncate实际上是删除表的数据结构，然后再重新创建表，
从性能角度来说，通常要好于delete。
由于truncate是删除表中所有的记录，因此不能指定where条件。

别名
select person.name as ‘姓名’, person.age as ‘年龄’ from person
引号（’）可以去掉。
select person.name as 姓名, person.age as 年龄 from person
as也可以去掉
select name 姓名, age 年龄 from person
也可以为表取别名，表起别名后，就可以别名来引用字段。
select p.name, p.age from person p

别名的好处
对于字段讲，可以解决多表查询时，多表中同名字段的冲突。
对于表讲，可以为临时表取一个名字。
不要认为起别名没有作用。因为在后来的学习中，比如Hive。
（类SQL的HQL）进行复杂的查询语句，如果不起别名，系统会自己给弄一个
自定义的别名，那个别名却不能操作。。希望早些注意这个坑。

MySQL中，true与1是同义词，false与0是同义词。
select true = 1, false = 0
if 如果第一个表达式为真（非0，非null），则返回第二个
表达式的值，否则返回第三个表达式的值。
select if(0, 20 * 2, 5 + 5);
select if(null, 100, -100);
ifnull 如果第一个表达式不为null，则返回第一个表达式的值，
否则返回第二个表达式的值。
select ifnull(5, 10);
select ifnull(null, 10);
select id, name, ifnull(age, 1) from person;
nullif 如果两个表达式值相等，则返回null，否则返回第一个
表达式的值。
select nullif(5, 5);
select nullif(5, 6);
isnull 判断参数是否为null，如果为null，返回true（1），
否则返回false（0）。
select isnull(null);
select isnull(100);

case
第一种形式（类似Java中的switch-case）
else 是可选的。
select
case age
when 10 then ‘10岁’
when 20 then ‘弱冠’
when 30 then ‘而立’
else ‘不知道什么年’
end description
from person;

第二种形式（类似于Java中的if-elseif）
else 也是可选的。
select
case
when age > 30 then ‘超过30’
when age > 20 then ‘超过20’
when age > 10 then ‘超过10’
else ‘小于等于10’
end description
from person;

数学函数
abs 返回参数的绝对值。
select abs(10), abs(-10);
ceil / ceiling
返回大于等于参数的最小整数值。（向上取整）
select ceil(3.3), ceiling(5.1);
floor 返回小于等于参数的最大整数值。（向下取整）
select floor(3.2)
mod 返回第一个参数除以第二个参数的余数
select mod(10, 3)
pow / power 返回a（第一个参数）的b（第二个参数）次方（幂）。
select pow(2, 5), power(2, 10);
rand 返回0-1.0之间的随机小数。（包括0，包括1）
select rand();
可以指定种子（seed）
select rand(200);
round 返回最接近参数值的整数值。
select round(-20.5);
round可以指定第二个参数
保留小数点后两位（第二个参数指定），根据小数点后第三位
进行四舍五入。
select round(44.44544, 2);
第二个参数也可以指定负值，当指定负值时，根据小数点前面
进行定位。例如-1，那就是根据各位对十位进行四舍五入。
select round(5888, -2);
一个参数的round也可以使用两个参数的round来表示。
select round(-20.5, 0);
sqrt 返回参数的平方根值。
select sqrt(16);
select sqrt(-16);

字符函数
length返回字符串的长度，以字节为单位。
select length(‘ab中文’);
char_length 返回字符串的长度，以字符为单位。
select char_length(‘ab中文’);
concat 返回连接后的字符串，该函数为可变参数。
select concat(‘AB’, ‘CD’);
select concat(‘AB’, ‘CD’, ‘EF’, ‘GH’);
concat_ws 使用分隔符进行字符串的连接。该函数为可变参数。
select concat_ws(”, ‘AB’, ‘CD’);
select concat_ws(”, ‘AB’, ‘CD’, ‘EF’);
insert 将第四个参数插入到第一个参数中，将会替换掉从第
二个参数起，第三个参数指定长度的字符串。
注意：MySQL中，字符函数的索引从1开始。
select insert(‘123456’, 2, 3, ‘ABCD’);
instr 返回第二个参数在第一个参数中首次出现的位置，
如果没有出现，则返回0。
select instr(‘123456’, ‘56’);
select instr(‘123456’, ‘78’);
left 返回第一个参数最左侧的n（第二个参数指定）个字符。
select left(‘123456’, 3);
right 返回第一个参数最右侧的n（第二个参数指定）个字符。
select right(‘123456’, 3);
lower / lcase 返回字符串小写的形式。
select lower(‘ABC’), lcase(‘ABC’);
upper / ucase 返回字符串大写的形式
select upper(‘abc’), ucase(‘abc’);
replace 返回将第一个参数的字符串中所有的第二个参数使用
第三个参数替换后的结果。
select replace(‘12341234’, ‘34’, ‘ABCD’)
mid / substr/ substring
取子串，从第二个参数指定的位置开始，一直到字符串的末尾。
select substr(‘123456’, 2);
select substr(‘123456’ from 2);
取子串，从第二个参数指定的位置开始，截取第三个参数指定的长度。
select substr(‘123456’, 2, 3);
select substr(‘123456’ from 2 for 3);
ltrim 去掉字符串左端的空格
select ltrim(’ dddd ‘);
rtrim 去掉字符串右端的空格
select rtrim(’ dddd ‘);
trim 去掉字符串两端的空格
select trim(’ aaaabbb ‘);
指定两端要去掉的字符。
select trim(‘X’ from ‘XXXaaaabbbbXXX’)
去掉前端指定的字符
select trim(leading ‘X’ from ‘XXXaaabbbXXX’);
去掉后端指定的字符
select trim(trailing ‘X’ from ‘XXXaaabbbXXX’);
去掉两端指定的字符（显式指定）
select trim(both ‘X’ from ‘XXXaaabbbXXX’);

日期函数
返回当前的日期与时间
select now()
返回当前的日期。
select curdate();
返回当前的时间
select curtime();
取一个日期时间的日期部分
select date(now());
去一个日期时间的时间部分
select time(now());
year month day hour minute second
select year(now()), month(now()), day(now());
select hour(now()), minute(now()), second(now());

聚合函数
select * from person
求和
select sum(age) from person;
求最大值
select max(age) from person;
求最小值
select min(age) from person;
求平均值
select avg(age) from person;
求记录的条数
select count(*) from person;
select count(age) from person;
count(*)返回结果集中记录条数，而count(字段）返回记录中指定字段不为null的数量。

分组统计，group by
使用group by进行分组后，会根据group by后指定的字段
进行分组统计，（字段值相同的记录会划分成一个组）。
select sex, max(age), min(age), avg(age) from person
group by sex
关于分组查询字段的要求
当使用group by 进行分组查询时，查询的字段要么使用聚合函数，
要么出现在group by后的分组字段中。
group by 可以指定按多个字段进行分组，多个字段使用,进行分隔。
使用多个字段分组时，只有多个字段全部相等的记录才会划分到一个
组中
having
select sex, max(age), min(age), avg(age) from person
group by sex having avg(age) > 20
关于where与having
where对记录进行过滤，而having对组进行过滤。
where中不能使用聚合函数，而having可以使用。
排序
asc 升序 desc 降序 默认为升序
select id, name ,age from person order by age asc
select id, name, age from person order by age desc
排序可以指定多个字段，使用,进行分隔。
select id, name, age from person order by age asc, id desc
顺序前后要求
where -> group by -> order by -> limit

MySQL中，cross join与inner join是等价的。
通过on指定连接条件。
select b.id bid, b.name, b.author, b.publish,
o.id oid, o.borrow_person
from book b cross join borrow o
on b.id = o.id
where length(b.name) > 5

select b.id bid, b.name, b.author, b.publish,
o.id oid, o.borrow_person
from book b inner join borrow o
on b.id = o.id
natural 使用表中所有同名的字段进行等值连接。
因为natural join已经具有隐含的连接条件，所以，
我们不能再指定连接条件。
select b.id bid, b.name, b.author, b.publish,
o.id oid, o.borrow_person
from book b natural join borrow o
可以使用using指定等值连接的字段
select b.id bid, b.name, b.author, b.publish,
o.id oid, o.borrow_person
from book b join borrow o
using(id) b.id = o.id

select * from book
select * from borrow;
外连接
outer是可选的
select b.id bid, b.name, b.author, b.publish,
o.id oid, o.borrow_person
from book b left outer join borrow o
on b.id = o.id
outer是可选的
select b.id bid, b.name, b.author, b.publish,
o.id oid, o.borrow_person
from borrow o right join book b
on b.id = o.id

内连接与外连接
对于内连接，不满足连接条件的记录不会在结果集中显示。
对于外连接，不满足连接条件的记录也可能在结果集中显示。
以左外连接为例，对于左表的记录，一定会在结果集中显示，
而不管右表是否存在满足条件的连接记录。当右表不存在
满足条件的连接记录时，对应的字段为null值。