MYSQL MYSQL必知必会

1.主键:任意两行不会拥有相同的主键;主键的值不为空值;

2.查看命令

show databases;
use [databasename];
show [tablename];
show columns from [tablename];  = describe [tablename];
show create database [database];
show create table [table]; //查看创建

3.sql中语句中的空格将被忽略,且不区分大小写;

4.查询语句

//不打括号
select [col1],[col2] from [table];
select * from [table];
//不返回重复的值
select distinct [col1] from [table];
//返回前num行
select [col1] from [table] limit [num];
//返回从num_1开始的num_2行
select [col1] from [table] limit [num_1],[num_2];
//按顺序,可以采用非选择的列
select [col1],[col2],[col3] from [table] order by [col4],[col5];
//降序 DESC 只作用于直接位于其前面的一个列名
select [col1],[col2] from [table] order by [col3] DESC,[col4] DESC;
//限定条件 where
select [col1] from [table] where [col2] [op] [value];
select [col1] from [table] where [col2] between [value_1] and [value_2];
select [col1] from [table] where [col2] is null;

5.AND操作符的优先级高于OR优先级;

6.

select * from [table] where [col1] in ([value_1],[value_2])
select * from [table] where [col1] not in ([value_1],[value_2])
7.% -> 匹配0-多个任意字符;

   _  -> 匹配单个字符;

8.正则表达式

//binary区分大小写
select * from [table] where [col] regexp binary [re];

9.为了匹配特殊字符,需要用\\作为前导,\\.表示查找;

10.预定义了部分字符集,可以直接用于匹配 P71;

11.用于控制次数的元字符
*0或多个匹配
+1或多个匹配
?0或1个匹配
{n}指定数目n的匹配
{n,}至少n次的匹配
{n,m}匹配数目的范围

12.位置定位符

^文本的开始
$文本的结束

13.拼接字段concat ; 去掉空格trim;不影响原本的列;

select concat([col1],string,[col2]) from [table];
select RTrim([col1]) from [table] 
14.别名
// as 需要写在 from 前面
select [col1] as [name_1] from [table]

15.获取当前时间

select Now();

16.分组及过滤

//按[col1]统计Count(*)>2的
select [col1] ,Count(*) as Orders from [table1] group by [co1] having Count(*) > 2;

17.外键

外键为某个表中的一列,包含另一个表的主键值,定义了两个表之间的关系;

18.联结

select [col1_table1],[col2_table2] from [table1],[table2] where [table1.col3] = [table2.col3];
select [col1_table1] ,[col1_table2] from [table1] inner join [table2] on [table1.col2] = [table2.col2]; 

19.表别名只在查询中使用,不返回到客户机。

20.外联接:需要包含没有关联行的时候使用;

select [table1.col1],[table2.col1] from [table1] LEFT OUT JOIN [table2] ON [table1.col2] = [table2.col2];

21.UNION:给出多条SELECT语句,将其结果合成单个结果集;

    UNION ALL : 不取消重复的行;

22.全文本搜索:不区分大小写,结果按优先程度返回;不具有词分隔符(中文)不能恰当的返回搜索结果

     布尔文本搜素:不限定于FULLTEXT,返回结果不排序

select [col1] from [table1] where MATCH([col2]) AGIAINST(str); 
//返回比较列
select [col1],MATCH([col1]) AGAINST([str]) AS [name] from [table1];
select [col1] from [table1] MATCH([col1]) AGAINST('+[str1] +[str2]'IN BOOLEAN MODE);

23.INSERT语句

insert into [table1] values ([val1],...,[valn]);
insert into [table1]([col1],..[coln]) values ([val1],...,[valn]);

24.降低语句优先级

INSERT LOW_PRIORITY INTO ...

25.更新语句UPDATE

update [table1] set [col1] = [value1] where [col2] = [value2];
//遇到错误依旧继续
update ignore ...

26.删除语句DELETE

delete from [table1] where [col1] = [value1];
//删除表中所有数据
truncate [table];

27.更改表

alter table [table] add [col1] [char(20)];
alter table [table] drop column [col1];
alter table [table] add constraint [col1_table] foreign key ([col1_table2] reference [table2]([col1_table2]);

28.视图:更新一个视图将更新其基表,一般视图用于检索

29.存储过程:为以后的使用而保存的一条或多条MYSQL语句的集合 ->简单,安全,高性能

DELIMITER //
CREATE PROCEDURE [name()]
BEGIN
...
END //
DELIMITER ;
CALL [name](@[para1],@[para2]..);

30.游标

create PROCEDURE pro()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE ordernumbers CURSOR
	FOR
	SELECT id FROM people_test;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	OPEN ordernumbers;
	REPEAT
		FETCH ordernumbers INTO o;
	UNTIL done END REPEAT;
	CLOSE ordernumbers;
END;

31.支持触发器的语句:DELETE,INSERT,UPDATE;只有表支持触发器

32.事物处理:用于管理必须成批执行的Mysql操作;用于DELETE,INSERT,UPDATE

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

34.日志文件:

错误日志data/hostname.err 启动和关闭问题及任意关键错误的细节;
查询日志

data/hostname.log 记录所有MYSQL活动;

二进制日志data/hostname-bin 更新过数据的所有语句; 
缓慢查询日志data/hostname-slow.log 记录执行缓慢的所有语句;
FLUSH LOGS 刷新和重新开始所有日志文件;

35.查看当前设置:SHOW VARIABLES; SHOW STATUS;

    查看所有的活动进程:SHOW PROCESSLIST;

    查看某条语句如何执行:EXPLAIN [SENTENCE];

    






评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值