使用mysql

--linux远程连接mysql(注意端口号的P大写)
mysql -h 132.252.34.47 -P 8922 -u summer -p hello123

--显示库rouproup
show databases;
--选择数据库
use db1;
--显示表
show tables;

--查看表结构
desc tb1;
describe tb1; --等价desc
show columns from tb1;

--查看建表语句
show create table tb1;
show create database db1;

--显示用户安全权限

show grants;

检索数据

--查询
select * from tb1; --通配符*检索效率低
select c1,c2 from tb1;

-- distinct去重
select distinct c1 from tb1;
select distinct c1,c2,c3 from tb1; --三列都去重

--limit限制
select * from tb1 limit 5; --前5行
select * from tb1 limit 2,2; --从行号2开始,取2行(行号从0开始,所以实际为第3行)

排序检索数据

-- order by 排序:默认升序(asc);不区分大小写

--单列排序
select * from tb1 order by c1;
--多列排序
select * from tb1 order by c1,c2; --先按c1排序,c1相同则再按c2排序
--降序
select * from tb1 order by c1 desc,c2; --按c1降序,再按c2升序
--limit
select * from tb1 order by c1 desc limit 1;  --order要在from后,limit要在order后

过滤数据

--过滤单个值
--等于
select * from tb1 where c1='summer'; --单引号为字符串,没有为数值
--小于
select * from tb1 where c2< 10;
--小于等于
select * from tb1 where c2<= 10;
--不等于
select * from tb1 where c2<> 10;
select * from tb1 where c2!= 10;
--between介于中间
select * from tb1 where c1 between 5 and 10;
--null空值
select * from tb1 where c1 is null;
select * from tb1 where c1 is not null;

数据过滤

-- 多个条件and
select * from tb1 where c1 = 'summer' and c2>= 10;
-- or或
select * from tb1 where c1 = 'summer' or c1='winter'

--and优先级大于or
select *from tb1 where (c1='a' or c1='b') and c2>10;
 --默认优先级相当于:c1='a' or (c1='b' and c2>10)
select *from tb1 where c1='a' or c1='b' and c2>10;

--in范围条件;in的效率高于or
select * from tb1 where c1 in('a','b','c');
select * from tb1 where c1 not in('a','b','c');

通配符过滤

-- %任意字符出现任意次数

 --匹配开头为: summer
select * from tb1 where c1 like 'summer%';
 --匹配含有summer
select * from tb1 where c1 like '%summer%';
--匹配: A开头,E结尾
select * from tb1 where c1 like 'A%E';

-- 下划线_ 匹配单个字符
select * from tb1 where c1 like 'app_e';

正则表达式

-- regexp

-- . 表示匹配任意一个字符
select * from tb1 where c1 regexp '.dog';
-- | 表示或
select * from tb1 where c1 regexp 'cat|dog|pig';

-- [] 表示括号中特定字符
select * from tb1 where c1 regexp 'dog[123]'; --匹配 dog1,dog2,dog3
select * from tb1 where c1 regexp '[Aa]pple'; --匹配Apple,apple
-- 匹配范围: 数字[0-9],字幕[a-z]
select * from tb1 where c1 regexp 'dog[1-5]';
select * from tb1 where c1 regexp '[a-f]less';

-- 转义字符: \\
--常用有 \\-  \\.  \\\
-- 一般正则表达式用单个反斜杠当转义字符;但是mysql用双反斜杠(mysql自己解释一个,正则表达式库解释另一个)
-- 元字符:  换页\\f  换行\\n  回车\\r 制表\\t  \\v纵向制表
-- 重复元字符:    * 0个或多个
--			+ 一个或多个
--			? 0个或1个
-- 定位元字符: 	^ 文本开始
--			$ 文本结尾

创建计算字段

--把c1和c2拼接起来: c1(c2)
-- trim函数可以去除左右两边空格,还有ltrim和rtrim
select concat(trim(c1),'(',trim(c2),')') as c3 from tb1 ;

测试计算

--select可以省略FROM子句以便简单地访问和处理表达式
select 3*2;
select trim('abc');
select now();

数据处理函数

--文本处理函数:
-- left() right() length()  locate() 
-- upper() lower()  trim() ltrim()  rtrim()
-- substring()  upper()
select upper(c1) as c1_upper from tb1;


--时间处理函数
--日期必须为格式yyyy-mm-dd

--获取当前时间
select now();  -- 2021-06-23 14:19:55
--获取日期部分
select date('2021-06-23 14:19:55'); -- 2021-06-23
--获取时间部分
select time('2021-06-23 14:19:55'); -- 14:19:55
--where取某个月的数据
select * from tb1 where date(c3_date) between '2021-02-01' and '2021-02-28';
select * from tb1 where year(c3_date) = 2021 and month(c3_date)=

常用日期和时间处理函数

AddDate()	 	增加一个日期（天、周等）
AddTime()	 	增加一个时间（时、分等）
CurDate() 		返回当前日期
CurTime() 		返回当前时间
Date() 		返回日期时间的日期部分
DateDiff()	 	计算两个日期之差
Date_Add()	 	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day() 		返回一个日期的天数部分
DayOfWeek() 	对于一个日期，返回对应的星期几
Hour() 		返回一个时间的小时部分
Minute() 		返回一个时间的分钟部分
Month()	 	返回一个日期的月份部分
Now() 		返回当前日期和时间
Second()	        返回一个时间的秒部分
Time() 		返回一个日期时间的时间部分
Year() 		返回一个日期的年份部分

聚合函数

aggregate function,运行在行组上，计算和返回单个值的函数

-- AVG()函数忽略列值为NULL的行
select avg(c1) as avg_c1 from tb1;

-- count(*)对表中行的数目进行计数,不管null与否
select count(*)  from tb1;
-- COUNT(column)对特定列中具有值的行进行计数，忽略NULL值
select count(c1) as count_c1 from tb1;

--数值或日期或文本都可以取最大最小值
--max 
select max(c1) as max_c1 from tb1;
--min
select min(c2) as min_c2 from tb2;
--sum
select sum(c1) as sum_c1 from tb1;
select sum(c1*c2) as sum_c1c2 from tb1;

--distinct后面需要加具体的列
select count(distinct c1) count_c1 from tb1;

AVG() 返回某列的平均值
COUNT() 返回某列的行数;count(column)忽略null;count(*)不忽略null
MAX() 返回某列的最大值;忽略null行
MIN() 返回某列的最小值;忽略null行
SUM() 返回某列值之和;忽略null行

分组数据

select的列要么出现在group by中,要么有聚合函数;

group by 子句中的列必须是检索列或者有效表达式(不能是聚合函数),如果select中使用表达式,groupby中也要用相同表达式,不能用别名;

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前;

-- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
--select的列要么出现在group by中,要么有聚合函数
select c1,count(*) as num_c1 from tb1 group by c1;

having过滤分组

where和having的功能用法都类似,唯一的差别是: where过滤行,而having过滤分组

或者这么理解:WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

--按c1分组并计数,并筛选出>=2
select c1,count(*) as num_c1 from tb1
group by c1
having count(*) >=2;

--order by排序, 因为分组输出的结果可能不是分组的顺序
select c1,count(*) as num_c1 from tb1
group by c1
having count(*) >=2
order by num_c1;

select字句顺序: select→from→where→group by→having→order by→limit

子查询

相关子查询(correlated subquery): 涉及外部查询的子查询

--子查询
select * from tb1
where c1 in (select c1 from tb1 where c2='jack');

--相关子查询
select c1
    ,c2
    ,(select c3 from tb2 where tb1.c1=tb2.c1) as c3
from tb2

连接表

完全限定列名: 表名.列名

笛卡尔积(cartesian product): 由于没有联结条件的表关系返回的结果; 返回总行数=表1行数*表2行数

内连接: 只有关联的行才保留;

inner join简写为join

外连接: right join或left join 保留基表所有行,即使没有关联用null补全

left outer join和right outer join 简写为 left join和right join

组合查询

union中每个查询必须包含相同的列,表达式或聚集函数(不过各个列不需要以相同的次序列出)

union自动去重;union all不去重;

union组合查询时,只能在最后一个select后加order by,是对整个所有结果排序;

全文本搜索

插入数据

--全列插入可以省略类名values后必须含有所有列
insert into tb1 values('v1','v2',null,'v4','v5');

--指定列名插入
insert into tb1(c1,c3,c4,c5) values('v1',null,'v4','v5');--部分字段
insert into tb1(c1,c2,c3,c4,c5) values('v1','v2',null,'v4','v5'); --全部字段
--一次多条插入:每组数据一对括号,逗号分隔
insert into tb1(c1,c2,c3,c4,c5) values('v1','v2',null,'v4','v5'),('x1','x2','x3','x4','x5');

--插入检索数据 insert select
insert into tb2 
select c1,c2,c3 
from tb1 where c1>0;

单条insert多个插入比多条insert语句块

更新删除数据

--更新单列
update tb1 set c2='jack' where c1>0;
--更新多列
update tb1 set c1='aa',c3='bb' where c2='jack';

--如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，
--则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）
--如果想发生错误也继续更新,就用ignore
update ignore

创建操纵表

create table tb1 (
    id int not null
    ,name char(10)
    ,age int not null default 0
    ,address char(128)
    ,primary key(id,name)
)engine=innodb


--添加列
alter tb1 add c1 char(32);
--删除列
alter tb1 drop cloumn c2;

--删除表
drop table tb1;

--重命名表
rename tb1 to tb2;

mysql数据库默认引擎是MyISAM;

InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索；

MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）

MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用,但外键不能跨引擎

alter table 一定谨慎,要先完整的备份（模式和数据的备份）

视图

重用sql语句;简化复杂的sql操作;

视图不包含数据

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

--创建视图
create view view_apple as
select a.c1,b.c2,a.c3 from tb1 a,tb2 b
where a.c1=b.c1 and b.c4>0;

--查看创建语句
show create view view_name;

--删除视图
drop view view_name;

--创建视图(如果存在则替换)
create or replace view 

存储过程

为以后的使用而保存的一条或多条MySQL语句的集合;可将其视为批文件

-- 创建存储过程
create procedure pro_avg_c1()
begin
	select avg(c1) as avg_c1 from tb1;
end;

-- 执行存储过程
call pro_avg_c1();

--删除
drop procedure pro_avg_c1();

--含参: in表示传入参数;out表示返回结果参数
create procedure pro_total_c1(
    in v_c1 int,
    out v_total decimal(8,2) 
)
begin 
    select Sum(c2*c3) from tb1 where c1=v_c1
    into v_total;
end;

--调用时必须含两个参数:c1列值,返回结果变量名
call pro_total_c1(25,@total_result)

--查看存储过程
show procedure status;
show procedure status like 'pro_total_c1';
--查看存储过程
show create procedure pro_total_c1;

游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询

触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语

句）:delete,insert,update

每个表最多支持6个触发器

--创建trigger
create trigger trigger_apple after insert on tb1 
for each row select 'trigger_apple add';
--这里可能会报错: ERROR 1415 (0A000): Not allowed to return a result set from a trigger
--在MySQL5以后不支持触发器返回结果集,改写成下面:
create trigger trigger_apple after insert on tb1 
for each row select 'trigger_apple add' into @trig_result;
--查看触发器结果
select @trig_result;

--删除
drop trigger trigger_apple;

事务

事务(transaction): 指一组sql

回退(rollback):撤销指定的sql语句的过程

提交(commit):将未存储的sql语句结果写入数据库表

保留点(savepoint):事务处理中设置临时占位符(place-holder),你可以对它发布回退(与回退整个事务不同)

事务管理用来管理insert,update,delete; 但不能回退create,drop

一般的sql语句都是隐含提交(implicit commit);

事务处理块必须明确加上commit

start transaction;
delete from tb1 where c1='apple';
delete from tb2 where c2='apple';
commit;

字符语言

字符集: 字母和符号的集合

编码: 某个字符集成员内部表示

校对: 为规定字符如何比较的指令

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)


--
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
| utf32_slovak_ci          | utf32    | 173 |         | Yes      |       8 |
| utf32_spanish2_ci        | utf32    | 174 |         | Yes      |       8 |
| utf32_roman_ci           | utf32    | 175 |         | Yes      |       8 |
| utf32_persian_ci         | utf32    | 176 |         | Yes      |       8 |
| utf32_esperanto_ci       | utf32    | 177 |         | Yes      |       8 |
| utf32_hungarian_ci       | utf32    | 178 |         | Yes      |       8 |
| utf32_sinhala_ci         | utf32    | 179 |         | Yes      |       8 |
| utf32_german2_ci         | utf32    | 180 |         | Yes      |       8 |
| utf32_croatian_ci        | utf32    | 181 |         | Yes      |       8 |
| utf32_unicode_520_ci     | utf32    | 182 |         | Yes      |       8 |
| utf32_vietnamese_ci      | utf32    | 183 |         | Yes      |       8 |
| binary                   | binary   |  63 | Yes     | Yes      |       1 |
| geostd8_general_ci       | geostd8  |  92 | Yes     | Yes      |       1 |
| geostd8_bin              | geostd8  |  93 |         | Yes      |       1 |
| cp932_japanese_ci        | cp932    |  95 | Yes     | Yes      |       1 |
| cp932_bin                | cp932    |  96 |         | Yes      |       1 |
| eucjpms_japanese_ci      | eucjpms  |  97 | Yes     | Yes      |       1 |
| eucjpms_bin              | eucjpms  |  98 |         | Yes      |       1 |
| gb18030_chinese_ci       | gb18030  | 248 | Yes     | Yes      |       2 |
| gb18030_bin              | gb18030  | 249 |         | Yes      |       1 |
| gb18030_unicode_520_ci   | gb18030  | 250 |         | Yes      |       8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.00 sec)

--不指定character set和collate则数据库默认
--创建表时指定字符集和校对
create table tb1(
    c1 int
    ,c2 varchar(10)
)default character set hebrew
collate hebrew_general_ci;


-- orderby指定collate(校对)
select * from tb1 order by c1,c2 collate latinl_general_cs;

数据库维护

--检查表键是否正确
analyze table tb1;

mysql> analyze table user_user_ext;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| coc_sql.user_user_ext | analyze | status   | OK       |
+-----------------------+---------+----------+----------+
1 row in set (0.03 sec)

 
mysql> check table user_user_ext,user_user_error,user_user_map;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| coc_sql.user_user_ext   | check | status   | OK       |
| coc_sql.user_user_error | check | status   | OK       |
| coc_sql.user_user_map   | check | status   | OK       |
+-------------------------+-------+----------+----------+
3 rows in set (0.00 sec)