乐观锁和悲欢锁

SQL概念

SQL语句分类

  1. 数据定义语言DDL(DataDefinition Language)
    a. 作用：对容器的创建、修改、删除【库、表、列】
    b. 关键字：create(创建)、alter(改建)、drop(丢弃)
  2. 数据操作语言DML(Data Manipulation Language)
    a. 作用：对数据插入、修改、删除操作
    b. 关键字：insert(插入)、update(修改)、delete(删除)、truncate(截断)
  3. 数据查询语句DQL(Data Query Language)
    a. 作用：进行数据库操作查询
    b. 关键字：select(选择)
  4. 数据控制语言DCL(Data Control Language)
    a. 作用：创建用户，用户赋权、权限收回、删除用户
    b. create、rant、revoke、drop
  5. 事务处理语言TPL(Transaction Process Language)
    a. 作用：进行数据库事务操作，优化多条DML语句安全问题
    b. start、commit、rollback···

    数据库的基本操作

    ```plsql SHOW DATABASES; —查询当下所有数据库

CREATE DATABASE <库名>; — 创建数据库

SHOW CREATE DATABASE <库名>; — 查看创建数据库信息

DROP DATABASE <库名>; — 删除数据库(不存在报错)

  1. <a name="Gda87"></a>
  2. # 数据表的基本操作
  4. - 约束
  5.    - 主键约束：要求主键列数据唯一，并且不允许为空
  6.    - 联合主键约束：不存在联合主键约束相等的数据
  7.    - 外键约束：
  8.       - 关系
  9.          - 主表(父表)
  10.          - 从表(子表)
  11.       - 外键约束模式：两个表数据之间字段建立连接
  12.          - district：严格模式，不允许操作
  13.          - cascade：级联模式，一起操作，主表变化，从表数据跟随(删除)
  14.          - set null：空值模式，父表变化(删除)，从表为null(需要从表允许为null)
  15.    - 非空约束：字段值不允许为null
  16.    - 唯一性约束：字段值要求是唯一的，允许为null，但只有一个null
  17.    - 默认约束：数据没有为该字段赋值时，会使用默认字段值
  18.    - 自增约束：整数类型(TINYINT、SMALLIN、INT、BIGINT)，唯一并自增
  19. ```plsql
  20. -- 创建数据表
  21. CREATE TABLE <表名>(
  22.   字段名 数据类型 [列级别约束条件] [默认值],
  23.   [表级别约束条件]
  24. );
  25. -- 举例说明
  26. CREATE TABLE tb_emp1(
  27.     id int(11) comment "员工编号",
  28.     name varchar(25) comment "员工名称",
  29.     depptId int(11) comment "所在部门编号",
  30.     salary float comment "工资"
  31. );
  33. -- 主键约束
  34. id int(11) primary key comment "员工编号"; -- 定于主键约束
  35. PRIMARY KEY(id); -- 指定主键约束
  36. -- 联合主键约束
  37. PRIMARY KEY(id, name);
  38. -- 外键约束
  39. CONSTRAINT KEY(id) REFERENCES <主表>(id); 
  40. -- 非空约束
  41. name varchar(25) NOT NULL;
  42. -- 唯一性约束
  43. name varchar(25) UNIQUE;
  44. -- 默认约束
  45. name varchar(25) DEFAULT "未命名";
  46. -- 自增约束
  47. id int(11) PRIMARY KEY AUTO_INCREMENT;
  48. -- 检查约束
  49. sex char(1) CHECK('男' or '女');
  51. -- 查看数据表
  52. DESCRIBE 表名; -- 查看数据表数据；简写：DESC 表名
  53. SHOW CREATE TABLE 表名\G; -- 查看表创建结构
  55. -- 修改字段表
  56. ALTER TABLE<旧表名>RENAME<新表名>; -- 修改表名
  57. ALTER TABLE<表名>MODIFY<字段名><数据类型>; -- 修改表结构
  58. ALTER TABLE<表名>CHANGE<旧列名><新列名><数据类型>; -- 修改列名和结构
  59. ALTER TABLE<表名>ADD<列名><数据类型>[FIRST|AFTER<列1>]; -- 新增字段
  60. ALTER TABLE<表名>NODIFY<字段名><数据类型>[FIRST|AFTER<列1>];
  61. -- 修改字段插入的位置
  62. -- FIRST<列1>插入列1前、AFTER<列1>插入列1后、没有列1放最前和最后
  64. -- 删除字段
  65. ALTER TABLE<表名>DROP<字段>; --删除字段
  66. ALTER TABLE<表名>DROP FOREIGN KEY<外键约束名> -- 删除外键约束
  67. DROP TABLE [IF EXISTS] 表1,表2,表n; -- 删除没有关联的一个或多个表
  69. -- 修改存储引擎
  70. ALTER TABLE <表名> ENGINE=<引擎名>; -- 外键约束不能跨引擎使用

数据操作

  1. -- 增
  2. INSERT [INTO] <表名> (字段列表) VALUES (值列表),(值列表)...;
  3. INSERT [INTO] <表名> VALUE (值列表);
  5. -- 删
  6. DELETE FROM <表名> [where];
  8. -- 改
  9. UPDATE <表名> SET key=val [where];
  11. -- 查
  12. SELECT * FROM <表名>;  -- 后面将祥写查询数据

数据类型和运算符

数据类型

常用类型

  • 整数
    • tinyint 1字节 -128~127 / 0~255
    • smallint 2字节 -32768~32767 / 0~65535
    • int(integer) 4字节 -2*10**9 ~ 2*10**9 / 0 ~ 4*10**9
    • bigint 8字节 -9*10*18~9*10*18 / 0 ~ 18*10*18
    • unsigned(无符号数值)
  • 小数
    • float 4字节
    • double 8字节
    • decimal(m,d) m+2字节
  • 时间日期
    • year YYYY 1901~2155 1字节
    • time HH:MM:SS ±838:59:59 3字节
    • date YYYY-MM-DD``1000-01-01~9999-12-3 3字节
    • datetime YYYY-MM-DD HH:MM:SS``1000-0101 00:00:00 ~ 9999-12-31 23:59:59 8字节
    • timestamp YYYY-MM-DD HH:MM:SS``1970-01-01 00:00:01 ~ 2038-01-19 03:14:074字节
  • 字符
    • char(M) 固定长度
    • varchar(M) 动态长度，性能略低

      对大型文件的存储(图片、声音、网页、文本)，虽然有相关的存储方法(blob、text)，但会减低服务器的性能 在非必要条件下可以将文件存储其他地方，用文件路径进行引导

条件运算符号

  1. 普通运算符
  2.     =        等于
  3.     <>(!=)    不等于
  4.     <= | >=    小于等于 | 大于等于
  5.     <  | >    小于    |    大于
  6.     <=>     安全等于，与等于相同，但它能判断null，两边为null时才为1
  7. 特殊比较符
  8.     IS NULL | ISNULL    判断是否为null
  9.     IS NOT NULL            判断是否不为null
  10.     BETWEEN V1 AND V2    判断值是否在区间内(含v1,v2)
  11.     IN(v1,v2)            判断是否在in列表中(左侧和列表内有null，均返回null)
  12.     NOT IN(v1,v2)        判断是否不在in列表中(左侧和列表内有null，均返回null)
  13.     LEAST(v1,v2)        返回最小值(有null时返回null)
  14.     GREATEST(v1,v2)        返回最大值(有null时返回null)
  15.     LIKE "%_"            通配符匹配
  16.         % 匹配0到多个字符
  17.         _ 匹配一个字符
  18.     REGEXP                正则表达式匹配(与其他正则语法类似)
  19.     EXISTS(select) 判断是否能查询出数据，如果有数据返回true，否则false
  21. 大小写如何判断？
  22.     在Windows的mysql中，是不区分大小写的('a'='A'结果为1)
  23.     但在字符串前加binary可以区分(BINARY'a'='A'结果为0)

条件拼接符号

  1. and &&    逻辑与
  2. or  ||  逻辑或
  3. not !   逻辑非
  4. XOR            逻辑异或

MySQL函数

数字函数

  1. abs(x)        绝对值
  2. pi()            圆周率(7位小数)
  3. sqrt(x)        平方根(负数没有平方根，返回null)
  4. mod(x,y)    求余
  5. format(x, n) 四舍五入，保留n位小数
  6. ceil(x)     向下取整
  7. floor(x)    向上取整
  8. round(x)    取整四舍五入 round(x,y)保留y位小数
  9. truncate(x,y) 截取小数
  10. rand()        0-1的随机数，rand(x)种子值,产生重复序列
  11. sign(x)        输入负、零、正数，返回-1、0、1
  12. pow(x,y)    幂运算

字符串函数

  1. -- 查
  2. char_length(str)        字符串的个数
  3. length(str)                    返回字节长度(utf8时汉字为3字节)
  4. left(s,n)                        切片字符[0, n)
  5. right(s,n)                    切片字符[n, end] 截取尾部n个字符
  6. substr(s, n)                切片字符(n, end] 第n个开始到结尾 
  7. substring(s, n, l)    切片字符[n, l] n可以是负数                                          mid(s,n,l)
  8. strcmp(s1,s2)                比较字符串大小
  10. locate(s1,s)、position(s1 in s)、instr(s, s1)  s1存在存在s的位置
  11. elt(n, s1, s2, s3)    指定字符串的位置
  12. field(s, s1, s2,s3)    s所在的位置
  13. find_in_set(s1, s2)    s1在s2的位置，用多个,隔开的字符串
  15. -- 改
  16. concat(s,s,s)                拼接多个字符(遇到null,只返回null)    
  17. concat_ws(x,s,s)        拼接多个字符(遇到null,用x代替)
  18. insert(s, i, l, s)    替换并拼接  
  19.     s原字符串, i开始位(超出原字符位返回原字符串), l替换长度, s替换后字符
  20. lower(s),lcase(s)        字母全小写
  21. upper(s),ucase(s)        字母全大写
  22. mysql没有首字母大写的函数，但可以通过复杂函数来实现首字母大写
  23. concat( upper(left(s, 1)), lower(substr(s, 2)) )
  24. reverse(s)                    翻转
  25. replace(s,s1,s2)        替换，s中的s1替换成s2
  27. trim(s1 from s)         删除两边的s1值，默认空格
  28. lpad(s1, len, s2)        s1长度不够len，左边填充s2
  29. rpad(s1, len, s2)        s1长度不够len，右边填充s2
  30. ltrim(s)、rtrim(s)、trim(s)    左侧删除空格、右侧删除空格、两边删除空格
  32. -- 生成
  33. repeat(s, n)                生成n个s进行拼接
  34. space(n)                        生成n个空格

时间日期函数

  1. curdate(); current_data(); 获取当前日期
  2.     -- curdate()+0 当前日期转换成数值型
  3. curtime(); current_time(); 获取当前时间
  4.     -- curtime()+0 当前时间转换成数值型
  5. unix_timestamp(); 获取当前时间戳
  6.     -- unix_timestamp(new()); 时间转时间戳
  7. from_unixtime(unix); 时间戳转时间格式
  9. year(date);month(date);dayofweek(date);day(date);dayofyear(date);
  10. 获取 年、月、星期、日、今年的第几天
  11. hour(date);minute(date);second(date);
  12. 获取时、分、秒
  14. date_format(date, format); 
  15. 时间转字符
  17. datediff(date1, date2); 
  18. 两个时间的隔天数 date1-date2
  20. date_add(date, interval expr type);
  21. date_sub(date, interval expr type);
  22. date时间获取隔间后的时间

条件判断函数

  1. if(expr, v1, v2); 正确返回v1， 错误返回v2
  2. ifnull(v1,v2);         v1不为null返回v1，否者v2
  3. case expr when v1 then r1 [when v2 then r2] else f end; 多分支判断

系统信息函数

  1. version(); 返回版本
  2. connection(); 用户连接数
  3. database(); 当前数据库
  4. user(); 当前用户
  6. processlist; 当前用户连接状态

加密函数

  1. md5(str);  md5加密
  2. sha(str);  sha加密
  3. sha2(str, len); 加密长度(支持长度：224、256、384、512)

其他函数

  1. conv(n, f, b); f进制转b进制

窗口函数

  1. select *, rank() over w1 as `rank` from branch
  2. window w1 as (order by brcount);
  3. 生成排名
  5. select * , (brcount) / (sum(brcount) over()) as rate from branch;
  6. 每个部门人数占总人数的百分比

事务处理

  1. show variables like "%autocommit%";

查询数据

查询语句基本结构

  1. select [distinct]{ * | <字段列表> }
  2.   [
  3.     from <表1>,<表2>...
  4.       [where <表达式>]
  5.       [group by <group by definition>]
  6.       [having <expression> [{<operator> <expression>...}] ]
  7.       [order by <order by definition>]
  8.       [limit [offset, ] <row couunt>]
  9.   ]
  • where限定查询必须满足查询条件
  • group by查询出来的数据按照指定字段分组
  • having与group by一起使用，筛选分组后的数据
  • order by查询出来的数据按照顺序进行显示（asc升序，desc降序）
  • limit查询出来的数据每次显示的区域

    单表查询

    ```plsql select { * | <字段列表> } from 表名; — 获取不需要的列数据会 减低查询和应用程序的效率

select * from 表名 where <条件判断>;
— 条件过滤

select * from 表名 where <条件判断> order by <字段>[,<字段>] [def asc | desc]; — 字段排序

select distinct <字段v> from 表名; — 字段v不重复

  1. <a name="ZBuT5"></a>
  2. ## 聚合查询
  3. ```plsql
  4. select <字段> count(*) from 表名 group by <字段>;
  6. aggregate函数
  7. avg(); count(); sum(); 
  8. 平均值; 行数; 总数
  9. first(); last(); max(); min();
  10. 第一个记录值; 最后一个记录值; 最大值; 最小值
  12. -- MAX(), MIN()可以对时间进行聚合
  14. COALESCE(expression [ ,...n ])
  15. 返回从头到为第一个不为null的值
  17. group_concat(v [order by v, SEPARATOR ","]); -> v, v
  18.   [order by排序, SEPARATOR分隔符]
  19. json_objectagg(k, v); -> {k: v, k: v}
  20. json_arrayagg(v); -> [v, v]

count(*) 、count(0)、count(1) 、 count(列名) 的区别?

  • count(0) = count(1) = count(*) 不忽略null值和空值
  • count(列名) 忽略null值
  • count(*) 会自动帮你完成优化
  • 列名为主键时效率上：count(列名) > count(1)
  • 列名不为主键时效率上：count(列名) < count(1)
  • 如果有多字段并且没有主键效率上：count(1) > count(*)
  • 如果表只有一个字段时：count(*) > count(1)

99查询法模仿99乘法表

水平查询

99查询法

连接查询

外连接

内连接

自连接

子查询

插入、更新、删除数据

索引

存储过程和函数

视图

MySQL触发器

MySQL权限与安全管理

数据备份与恢复

MySQL日志

性能优化

MSQL Replication

MySQL workbench的使用

MySQL管理利器—-MySQL Utilities

MySQL读写分离利器—-MySQL Proxy

精通MySQL存储引擎

PDO数据库抽象类库

综合项目

项目1—开发网上商城

项目2—论坛管理系统

项目3—新闻发布系统0