首页
登录 | 注册

Mysql学习历程(一)

最近在重新学习Mysql数据库,从浅至深,打算找几本书来看看,第一本就是《Mysql必知必会》。在学习的过程中,记录一些要点,以防忘记,也方便以后温习。

  1. 数据库:是一个以某种有组织的方式存储的数据集合。
  2. mysql架构:客户机-服务器架构。
  3. show databases:显示所有数据库
    show tables:显示选中数据库的所有表
    show columns from test:显示test表的所有字段属性
    show status:显示服务器状态信息
    show create database test:显示创建数据库test所用的msyql语句
    show create table test: 显示创建表test所用的mysql语句
    show grants:显示已授权信息
    show errors:显示服务器错误信息
    show warnings:显示服务器警告信息
    help show:显示show命令的帮助信息
  4. 当心逗号:在选择多个列时,一定要在列名之间加上逗号,但最后一个列名后不加。
  5. select distinct schoolname,address from school:distinct关键字是为了取唯一,不过是作用于所有列出的字段,即schoolname与address合在一起进行比较筛选。
  6. select * from school limit 3,1:第一个数为开始位置,第二个数为要检索的行数。行号从0开始,依次类推。等价语句select * from school limit 1 offset 3
  7. select * from school limit 3:表示从第一行开始,要检索3行。单个数字总是表示从第一行开始。
  8. 子句:clause,SQL语句由子句构成,一个子句通常由一个关键字和所提供的数据组成,例如order by子句。
  9. order by:升序asc,降序desc,order by子句可以使用非检索的列进行排序,例如select schoolname from school order by address desc
  10. select * from school order by schoolname desc,address asc:先按schoolname降序,再按address升序。
  11. 子句位置:在同时使用order by 和where子句时,应该让order by位于where之后。
  12. between关键字:between num1 and num2,匹配范围中所有的值,包括指定的开始值和结束值。
  13. select * from product where id = 1002 or id = 1003 and price >=10:因为and在计算次序中优先级更高,此句并不能达到原先的目的,其等价于select * from product where id = 1002 or (id = 1003 and price >= 10),为避免出错,应使用括号明确分组,正确语句为select * from product where (id = 1002 or id = 1003) and price >= 10
  14. 通配符:百分号(%),匹配任何字符出现任意次数,例如select * from school where address like ‘da%’,检索所有以‘da’开头的记录;下划线(_),只匹配单个字符。
  15. 正则表达式:关键字regexp,例如,select * from school where address regexp ‘.xue’
     (.):匹配任意一个字符;
     (|):相当于or匹配,‘100|200’匹配100或200;
     ([]):匹配其中之一, ‘[123]’匹配1或2或3,等价于 ‘[1|2|3]’, ‘[^123]’匹配除了这些字符外的任何东西,[012345]等价于[0-5],“-”表示范围;
     (\):转义,为匹配特殊字符,必须在前面加上 ‘\’, ‘\.’匹配“.”;
     {n}:匹配n个指定字符内容, ‘(da){3}’匹配 ‘dadada’,不匹配 ‘daxuedada’;
     {n,}:匹配不少于n个指定字符内容;
     {n,m}:匹配至少n个,至多m个指定字符内容;
     (*):匹配0个或多个,等价于{0,};
     (+):匹配1个或多个,等价于{1,};
     (?):匹配0个或1个,等价于{0,1};
     (^):匹配文本的开始;
     ($):匹配文本的结尾;
  16. like子句与正则表达式的区别:like匹配整个列,即使被匹配的文本在列值中出现,like也不会找到它,除非使用通配符;而regexp是在列值中进行匹配,如果被匹配的文本在列值中出现,regexp将会找到它,regexp可以配合使用定位符“^”和“$”实现like的功能。
  17. 正则表达式测试:可以在不使用数据库的情况下用select来测试正则表达式,返回0表示不匹配,返回1表示匹配。例如,select ‘hello’ regexp ‘[0-9]’返回0。
  18. 数据库自带函数:
     Concat():拼接字符串,例如,select concat(schoolname,’-’,address) from school
     Trim():去除左右两边的空格,RTrim()只去除右边的空格,LTrim()只去除左边的空格;
     Lower():将字符串转换为小写,例如,select Lower(‘ABc’)
     Upper():将字符串转换为大写,例如,select Upper(schoolname) from school
     Now():返回系统当前时间,例如,select Now()
     CurDate():返回当前日期,同理,CurTime()返回当前时间;
     Date():返回日期时间的日期部分,例如,select Date(Now())。同理,Time(),Year(),Month(),Day(),Hour(),Minute(),Second(),DayOfWeek()函数都是返回日期时间的相应部分;
     DateDiff():计算两个日期之差,例如,select DateDiff(Now(),’2015-12-10’)
     AddDate():增加一个日期,例如,select AddDate(Now(),90)
     AddTime():增加一个时间,例如,select AddTime(Now(),’8:00’)
     Date_Add():高灵活度的日期运算函数,例如,select Date_Add(Now(),Interval ‘1 1 10’day_minute),详情请看help Date_Add
     Rand():返回一个随机数,select Rand()
  19. 列别名:使用as关键字,例如,select Now() as time
  20. 日期格式:mysql首选的日期格式为yyyy-mm-dd,也尽量使用这种格式,例如,select * from order where Date(order_date) = ‘2005-09-01’
  21. 聚集函数:运行在行组上,计算和返回单个值的函数;
     AVG():返回某列的平均值,忽略列值为NULL的行;
     COUNT():返回某列的行数。使用COUNT(column)进行计数,会忽略NULL值;使用COUNT(*)进行计数,包含NULL值;
     MAX():返回某列的最大值,忽略列值为NULL的行;
     MIN():返回某列的最小值,忽略列值为NULL的行;
     SUM():返回某列值之和,忽略列值为NULL的行。
     DISTINCT:加上DISTINCT关键字,统计时会忽略多余的相同值,例如,select COUNT(DISTINCT schoolname) from school
  22. 数据分组:关键字group by ,having
     如果分组列中具有NULL值,则NULL将作为一个分组返回;
     Group by子句必须出现在where子句之后,order by子句之前;
     Having子句可以过滤分组,而where子句过滤的是行而不是分组,where并没有分组的概念,也可以理解为,where在数据分组前进行过滤,而having在数据分组后进行过滤;
     Group by子句中列出的每个列都必须是检索列或有效的表达式,但不能是聚集函数,如果在select中使用表达式,那么在group by子句中也必须指定相同的表达式,不能使用别名;
  23. Select子句顺序:
     Select:要返回的列或表达式;
     From:从中检索数据的表;
     Where:行级过滤;
     Group by:分组说明;
     Having:组级过滤;
     Order by:输出排序顺序;
     Limit:要检索的行数;
  24. 相关子查询:涉及外部查询的子查询,例如,select cust_name,cust_state,(select COUNT(*) from order where order.id = customer.id) as orders from customer,该子查询对检索出的每个客户都会执行一次;
  25. 笛卡儿积:由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数,例如,select * from student,school
  26. 内部联结:基于两个表之间的相等测试,也称等值联结。例如,select * from student inner join school on student.schoolId = school.schoolId,等价于select * from student,school where student.schoolId = school.schoolId
  27. 自然联结:标准的联结如内部联结返回所有数据,甚至相同的列多次出现,自然联结就是排除多次出现,使每个列只返回一次;
  28. 外部联结:联结包含了那些在相关表中没有关联行的行,
     左外联结:关键字left outer join on,以左边的表为准;
     右外联结:关键字right outer join on,以右边的表为准;
  29. 组合查询:关键字union,
     union中的每个查询必须包含相同的列、表达式或聚焦函数,不过各个列不需要以相同的次序列出;
     列数据类型必须兼容:类型不必完全相同,但必须是数据库可以隐含转换的类型;
     重复的行会被自动取消,想要返回所有匹配行,需使用union all;
     只能使用一条order by子句,并且必须是在最后一条select语句之后;
  30. 全文本搜索:关键字match()、against(),
     MyISAM引擎支持全文本搜索,InnoDB不支持;
     为了进行全文本搜索,必须索引被搜索的列,使用FULLTEXT索引,例如,create table course(id int not null auto_increment, courseName varchar(128), remark text, PRIMARY KEY(id), FULLTEXT(note_text))Engine=MyISAM
     使用全文本搜索查询,select * from course where match(remark) against(‘english’)
     布尔文本搜索:功能更强大的全文本搜索,不一定需要FULLTEXT索引,但查询速度缓慢。例如,select * from course where match(remark) against(‘chinese -english’ IN BOOLEAN MODE),搜索包含“chinese”但不包含“english”的remark;
  31. 插入数据:关键字insert into,values,
     插入查询的结果:需要对应列的类型相兼容,例如,insert into user(username,address) select name,address from customer
     可以使用关键字low_priority降低insert语句的执行优先级,如,insert low_priority into,同样适用于update和delete语句;
  32. 更新数据:关键字update,set
     使用update语句更新多行,如果在更新这些行中有一行或多行出现错误,那么整个update操作都将被撤消,即错误发生前更新的所有行也会被恢复到原来的值。为即使发生错误,也继续进行更新,可使用关键字ignore,如,update ignore table;
  33. 删除数据:关键字delete
     如果想删除表中所有的行,可使用truncate table语句,速度更快,truncate语句实际是删除原来的表然后重新创建一个表,而不是逐行删除表中的数据;
  34. last_insert_id():获取最后一个auto_increment值,例如,select last_insert_id()
  35. 外键不能跨引擎,即使用一个引擎的表不能引用使用另外一种引擎的表的外键。
  36. 更新表结构:关键字alter table
     新增列:alter table school add column id int
     删除列:alter table school drop column id
     新增外键:alter table student add constraint fk_1 foreign key(schooled) references school(id),外键引用的必须是主键;
     删除外键:alter table student drop foreign key fk_1
  37. 重命名表:关键字rename table,例如,rename table stu to student
  38. 视图:关键字view。视图本身不包含数据,只包含使用时动态检索数据的查询,即在添加或更改这些表中的数据时,视图将返回改变过的数据。
     简化复杂的sql操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节;
     保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限;
     更改数据格式和表示;
     视图不能索引,也不能有关联的触发器或默认值;
  39. 视图操作:
     新建视图:关键字create view as,例如,create or replace view test as select * from school
     删除视图:drop view test
  40. 存储过程:关键字 create procedure,begin end
     如果是使用mysql命令行创建procedure,首先需要修改命令结束符,默认的是“;”,使用delimiter ”;
     新建:
create procedure test()
         Begin
            Select * from school;
         End 

 删除:drop if exists procedure test,需要给出存储过程的名字,但不需要加括号;
 调用:关键字call,例如,call test();
 信息:show procedure statusshow create procedure test
41. 游标:关键字cursor,mysql游标只能用于存储过程或函数。
 创建游标:关键字declare cursor for,例如,

Create procedure test()
Begin
  Declare cur_test() cursor for
  Select * from school;
End;

 打开与关闭:使用游标前需先打开游标,例如,open cur_test;使用完后,需关闭以释放资源,例如,close cur_test
 使用游标:关键字fetch,例如,fetch cur_test into result
42. 触发器:关键字trigger,只有delete、insert、update语句支持触发器,
 只有表支持触发器,视图不支持,临时表也不支持;
 每个表每个事件每次只允许一个触发器,因此,每个表最多支持6个触发器(每条insert、update和delete的之前和之后)。单一触发器不能与多个事件或多个表关联;
 新建:关键字create trigger before after insert on for each row,例如,

create trigger school_tri after insert on school for each row 
begin
  insert into school_bak(schoolName,address) values(‘guanggong’,’wushan’);
end

 删除:delete trigger test
 显示:show triggers
43. 事务处理:关键字start transaction,rollback,commit
 Innodb引擎支持事务,MyISAM引擎不支持;
 事务处理可以管理insert,update,delete语句,但不能回退create或drop操作。事务处理块中可以使用这两条语句,但如果执行回退,它们不会被撤销;
 使用事务:start transaction; delete from student; rollback
 当commit或rollback语句执行后,事务会自动关闭,即后面的更改会隐含提交;
 部分回退:关键字savepoint,例如,savepoint delete_1; rollback to delete_1;保留点在事务处理完成(执行一条rollback或commit)后自动释放,自Mysql 5以来,也可以用release savepoint 明确了释放保留点;
 关闭自动提交:set autocommit=0
44. 字符集:关键字character set
 显示:show variables like ‘character%’show character set
 设置:表级设置,例如,create table( )default character set utf-8;列级设置,例如,studentName varchar(128) character set gbk;
45. 用户管理:用户信息保存在mysql.user表中
 新建:create user admin@192.168.1.1 identified by ‘admin’
 删除:drop user admin@192.168.1.1
 改名:rename user admin@localhost to administrator@localhost
 修改密码:密码必须使用password()函数加密。例如,修改自己的密码set password = password(‘admin’);修改某个用户的密码set password for admin = password(‘admin’)
46. 权限管理:关键字grant、revoke。
 授权:授权使用grant语句,至少要给出三方面信息:要授予的权限;被授予访问权限的数据库或表;用户名。例如,grant select on ywr.* to admin
 撤消授权:revoke select on ywr.* from admin
 查看权限:show grants for admin
47. 数据备份:为了保证所有数据被写到磁盘,可能需要在进行备份前使用flush tables语句;
48. 数据库维护:关键字analyze、check、repair、optimize
 analyze table:检查表键是否正确;
 check table:检查表的多种问题;
 repair table:修复表,只对MyISAM表有用,但不应该经常使用;
 optimize table:如果从一个表中删除了大量数据,应该使用optimize table 回收所有的空间,优化表的性能;
49. 改善性能:
 mysql是一个多用户多线程的DBMS,可使用show processlist显示所有活动进程,可使用kill id终结某个进程;
 可使用explain语句让mysql解释它将如何执行一条select语句;



2020 jeepxie.net webmaster#jeepxie.net
10 q. 0.010 s.
京ICP备10005923号