使用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)