sql学习笔记

一、数据库基本操作

1. 创建数据库
   create database <database_name>;
2. 查看所有数据库
   show database;
3. 删除数据库
   drop database <database_name>;
4. 选择数据库
   use <database_name>;

二、数据类型
数值类型
类型 大小(Bytes) 范围(有符号) 范围(无符号) 用途
tinyint 1 (-128,127) (0,255) 小整数值
smallint 2 (-32768, 32767) (0,65535) 大整数值
mediumint 3 (-8 388 608，8 388 607) (0，16777215) 大整数值
int或integer 4 (-2 147 483 648，2 147 483 647) (0，4 294 967 295 大整数值
bigint 8 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
float 4 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值
double 8 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值
decimal 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型
类型 大小(bytes) 范围 格式 用途
date 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型
类型 大小(bytes) 用途
char 0-255 定长字符串
varchar 0-65536 变长字符串
tinyblob 0-255 短文本字符串
blob 0-65535 长文本数据
MEDIUMBLOB 0-16777215 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16777215 中等长度文本数据
LONGBLOB 0-4294967295 二进制形式的极大文本数据
LONGTEXT 0-4294967295 极大文本数据

三、数据表操作

1. 创建数据表
   create table if not exists demo_base1(
   id int UNSIGNED not null auto_increment,
   title varchar(100) not null,
   author varchar(100) not null,
   submission_date date,
   primary key (id)
   )ENGINE=InnoDB default charset=utf8;
   注: 1. UNSIGNED:int类型无符号整数

2. primary key (id) ,id为主键,必须设置为not null

3. 删除数据表
   drop table <table_name>;

4. 插入数据
   insert into <table_name> (<field_name1>, <field_name2>,…,<fied_nameN>) values (,,…,);

4.查询数据

1. 查询所有数据
   select * from <table_name>;

2. 查询指定的字段数据
   select <字段1>,<字段2> from <table_name>;

3. where子句
   select * from <table_name> where <字段名> <操作符> or/and <字段名> <操作符>
   操作符 描述 实例
   = 等号，检测两个值是否相等，如果相等返回true (A = B) 返回false。
   <>, != 不等于，检测两个值是否相等，如果不相等返回true (A != B) 返回 true。

大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true (A > B) 返回false。
< 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true (A < B) 返回 true。
= 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true (A >= B) 返回false。
<= 小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true (A <= B) 返回 true。

5. 更新数据(不加where会更新所有字段)
   update <table_name> set <field1_name>=, <field2_name>= where <操作符> ;

6. 删除数据(不加where会删除所有字段)
   delete from <table_name> where <操作符> ;

7. like子句
   select * from <table_name> where like ; 此时like等价于 =
   select * from <table_name> where like %%; 模糊查询

like匹配方式:

1. %: 表示0个或者多个字符,可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示;
   pythoniand_java: (1):’%i%’
   (2):’%_%’:涉及到匹配符号的需要转义
   pythoni\\and_java:转义’’ 需要三个\

2. _: 表示任意单个字符,匹配单个任意字符,它通常来限制表达式的字符长度语句;
   小小好王: ‘__好王’:两个下划线

注: 3、4没用明白
3) []:表示括号内所列字符中的一个(类似正则表达式),指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个;

4. [^] : 表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符

8.union

1. union操作符:将多个select的结果合并,但是重复的只会列出一次
   select <列名1>,<列名2>,…,<列名n> from <table1_name> [where 子句]
   union
   select <列名1>,<列名2>,…,<列名n> from <table2_name> [where 子句];

2. union all:相比union,会将重复的列出

9. 排序(默认asc(升序))
   select <列名1>,<列名2>,…,<列名n> from <table_name> order by <列名1> [asc/desc];

10.分组

1. 普通用法
   select <列名> from <table_name> group by <列名>;
   例:select title,count(*) from demo_base group by title;
   2)使用 WITH ROLLUP:可以实现在分组统计数据基础上再进行相同的统计
   select title,sum(id) as id_count from demo_base group by title with ROLLUP;

11. 连接的使用:

1. inner join(内连接或等值连接):获取两个表中字段匹配关系的记录;
   select a.id,a.author,b.id from demo_base a inner join demo_base1 b on a.id = b.id;
   两个表根据id匹配到多少条就是多少条

2. left join(左连接): 获取左表所有记录, 即使右表没有对应匹配的记录;
   select a.id,a.author,b.id from demo_base a left join demo_base1 b on a.id = b.id;
   根据左表匹配,右表没有的为null,结果条数以左表为准

3. right join(右连接): 与left join相反,用于获取右表所有记录,即使左表没有对应匹配的记录
   select a.id,a.author,b.id from demo_base a right join demo_base1 b on a.id = b.id;
   根据右表匹配,左表没有的为null,结果条数以右表为准

12. null值处理:null值必须使用is null 和is not null 处理

1. is null:submission_date 为null
   select * from demo_base where submission_date is null;
2. is not null: submission_date不为null
   select * from demo_base where submission_date is not null;

13. 正则表达式
    regexp操作符:正则匹配;
    模式 描述
    ^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
    $ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
    . 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用像 ‘[.\n]’ 的模式。
    […] 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
    [^…] 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
    p1|p2|p3 匹配 p1 或 p2 或 p3。例如，‘z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。

• 匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。

• 匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
  {n} n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
  {n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

1)匹配author 小开头的:
select * from demo_base where author regexp ‘^小’

2)匹配author 王结尾的:
select * from demo_base where author regexp ‘王$’;

3)匹配author 中包含小王的:
select * from demo_base where author regexp ‘小王’;

4)匹配author 小开头的或者王结尾的
select * from demo_base where author regexp ‘^小|王$’;

14.事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交

事务控制语句：
BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MYSQL 事务处理主要有两种方法：

1. 用 BEGIN, ROLLBACK, COMMIT来实现
   BEGIN 开始一个事务
   ROLLBACK 事务回滚
   COMMIT 事务确认
2. 直接用 SET 来改变 MySQL 的自动提交模式:
   SET AUTOCOMMIT=0 禁止自动提交
   SET AUTOCOMMIT=1 开启自动提交
   (1) commit:
   begin;
   select * from demo_base where author regexp ‘^小|王${}^{\prime };select\ast fromdem{o}_{b}asewhereauthorregex{p}^{\prime }王$’;
   update demo_base set author=‘小李李李111’ where id=3;
   select * from demo_base where submission_date is not null;
   commit;
   END;

(2)rollback:rollback需要配合if判断是否出错使用