MySQL学习笔记

SQL

基本操作

退出数据库

mysql> exit

查看所有的数据库

mysql> show databases;

创建自定义数据库

mysql> create database test;

选择数据库进行操作

mysql> use test;

查看数据库中的表

mysql> show tables;

创建一个数据表

mysql> create table pet (name VARCHAR(20), owner VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

查看数据表的结构

mysql> describe pet;

查看数据表中的记录

mysql> select * from pet;

往数据表中添加记录

mysql> insert into pet values("liuhao", "liuhao", 'f', '1999-02-02', NULL);

在数据表中删除数据

mysql> delete from pet where name="liuhao";

在数据表中修改数据

mysql> update pet set name="haoliu" where owner="liuhao"

常用数据类型

• 数值
• 日期
• 字符串

数值

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-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 bytes	(-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	混合日期和时间值，时间戳

字符串

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

约束

主键约束

它能够唯一确定一 -张表中的一 条记录， 也就是我们通过给某个字段添加约束，就可以使得改字段
不重复且不为空。

# 单主键约束
mysql> create table user (id int primary key, name varchar(20));
# 多主键约束
mysql> create table user (id int, name varchar(name) primary key(id, name));

在表创建后添加主键约束

mysql> create table user(id int, name varchar(20));
mysql> alter table user add primary key(id);

删除主键约束

mysql>  alter table user drop primary key;

自增约束

# 通常和主键约束一起使用，采用自增
mysql> create table user(id int primary key, auto_increment, name varchar(20));

外键约束

涉及到两个表：父表和子表（主表和副表）

• 拥有外键约束的是副表
• 主表classes 中没有的数据值，在副表中，是不可以使用的
• 主表中的记录被副表引用，是不可以被删除的。

mysql> create table classes (id int primary key, name varchar(20));
mysql> create table student (id int primary key, name varchar(20), class_id int, foreign key(class_id) references classes(id));

唯一约束

修饰的字段不可以重复

# 第一种
mysql> create table user(id int, name varchar(20));
mysql> alter table user add unique(name);
# 第二种
mysql> create table user(id int, name varchar(20) unique);
# 第三种
mysql> create table user(id int, name varchar(20), unique(name));

非空约束

修饰的字段不可为空

mysql> create table user(id int, name varchar(20) not null);

默认约束

当没有插入值的时候，如果没有传值，就会使用默认值

mysql> create table user(id int, name varchar(20) default "liuhao");

数据表设计

第一范式1NF

数据表中的所有字段都是不可分割的原子值

比如一个数据为中国北京，中国是国家，北京是城市，是可拆分的，因此不满足第一范式

第一范式2NF

在满足1NF的前提下，2NF要求，除主键外的每一列都必须完全依赖于主键；如果要出现不完全依赖，只可能发生在联合主键的情况下。

第一范式3NF

必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。

查询

基础查询

# 查询表中所有的属性
mysql> select * from student;
# 查询表中某些属性
mysql> select name, id from student;
# 查询无重复的属性
mysql> select distinct name from student;
# 查询表中某属性的范围
mysql> select * from student where id between 20 and 80;
# 直接使用运算符
mysql> select * from student where id > 20 and id < 100;
# 查询某属性为32，23，21
mysql> select * from student where id in(32,23,21);
# 或条件
mysql> select * from student where id < 20 or id > 100;
# 降序
mysql> select * from student order by id desc;
# 升序
mysql> select * from student order by id asc;
# id降序，pd升序
mysql> select * from student order by id desc, pd asc;
# 计数
mysql> select count(*) from student where class="001";

多表查询

# 按照共同字段进行匹配
mysql> select sname,sno,degree from score,student where student.sno=score.sno

并集

union：拼接两个语句，取并集

连接查询

内连接

# inner join两边为两个表名
mysql> select * from person inner join card on person.cardID=card.id;

外连接

左连接：左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的显示，没有显示NULL

mysql> select * from person left join card on person.cardID=card.id;

右连接：和左外连接相反

mysql> select * from person right join card on person.cardID=card.id;

完全外连接（有的MySQL不支持）：求并集

mysql> select * from person full join card on person.cardID=card.id;

事务

定义：事务其实是一个最小的不可分割的工作单元。可以保证一个业务的完整性

实际的程序中，如果只有一条语句执行成功了，而另外一条没有执行成功?出现数据前后不一致。
多条sql语句，可能会有同时成功的要求，要么就同时失败

控制事务

mysql> select @@autocommit;
mysql> set autocommit=0;	# 为0表示关闭事务

MySQL默认开启事务

当我们执行一个sql语句，效果就会立即体现出来，且不能回滚

事务回滚：撤销前面语句

mysql> rollback;

此时仍然可以读取到，但是此时是一个虚拟数据，没有进行实际操作

手动开启事务

mysql> begin;
mysql> start transaction;

四大特征

• A：原子性，事务是最小的单位，不可分割
• C：一致性，事务要求，同一事务中的sql语句，b必须保证同时成功h或者同时失败
• I：隔离性，事务1和事务2之间具有隔离性
• D：持久性，事务一旦jji结束，就不可以返回

隔离性：脏读

mysql> read uncommited;		# 读未提交的，脏读
mysql> read commited;		# 读已提交的，不可重复读
mysql> repeatable read;		# 可以重复读，幻读
mysql> serializable;		# 串行化

查看数据的隔离级别

# 系统隔离级别
mysql> select @@global.transaction_isolation;
# 会话隔离级别
mysql> select @@transaction_isolation;

修改隔离级别

# 系统隔离级别
mysql> set global transaction isolation level xxx;
# 会话隔离级别
mysql> set transaction isolation level xxx;

mysql> begin;
...
mysql> rollback;
mysql> commit;	# 提交

脏读：如果两个不同的地方，都在进行操作，如果事务a 开启之后，他的数据可以被其他事务读取。

不可重复读：数据已经提交，不能再读取未提交之前的数据

幻读：事务a和事务b·同时操作一张表，事务a提交的数据，也不能被事务b读到，就可以造成幻读。

串行化：串行，当事务a对表进行操作时，事务b被阻塞