MySQL学习笔记

基础sql操作（单表的增删改查）

查询语法：select (字段列表) from 表名;

插入语法：insert into 表名 (字段列表) values (值列表)

修改语法：update 表名 set 字段1=新值，字段2=新值…..where 条件过滤

删除行语法：delete from 表名 where 条件过滤

删除表语法：drop table 表名

字段的增删改：

增：alter table 表名 add 字段（类型）

改：alter table 表名 alter column 原字段 新字段（类型）

删：alter table 表名 drop column 字段

sql注入：select * from user where id = 2 or '1'='1';

一、列属性

列属性又称为字段属性，在MySQL中一共有6个属性：null、默认属性、列描述、主键、唯一建和自动增长

1、null

Null属性：代表字段为空，如果对应的值为yes，表示该字段可以为null

注意：设计表的时候，尽量不要让字段为空。

Mysql的记录长度为65535个字节，如果一个表中有字段允许为null，那么系统就会设计保留一个字节来存储null，最终有效存储长度为65534个字节。

2、默认值

Default：当字段被设计之后，如果允许默认条件下，用户不进行数据的插入，那么就可以使用事先准备好的数据来填充，通常填充的为null。

Default关键字的另外一层使用，显示告知字段使用默认值，在进行数据插入的时候，对字段直接使用default。

create table my_default(

name VARCHAR(10) not null,

age int DEFAULT 18 ： 表示默认值

)

desc my_default;查询表的各属性，如类，主键，默认值.....

insert into my_default (name) VALUES ('jack');

select * from my_default;

insert into my_default VALUES ('rose',DEFAULT);

3、列描述

列描述：comment 是专门用于给开发人员维护的一个注释说明

基本语法： comment 字段描述;

查看comment：必须通过查看创建语句

Show create table 表名；

create table my_comment(

username VARCHAR(10) not null COMMENT '当前是用户名，不能为空',

password VARCHAR(10) not null comment '当前是密码，不能为空'

)

select * from my_comment;

show create table my_comment;

4、主键

顾名思义：主要的键。Primary key 在一张表中，有且仅有一个字段，里面的值具有唯一性与其他字段没有任何冲突，同时不允许为空，保证数据的唯一性。

创建主键

1. 随表创建

两种方式

1.1 直接在需要当作主键的字段之后，增加 primary key，增加primary key属性确定主键，在字段后增加主键属性。

create table my_pri1(

username VARCHAR(20) PRIMARY key

)

1.2 在所有字段之后增加primary key选项，primary key（字段信息）

create table my_pri2(

username VARCHAR(20),

PRIMARY key(username)

)

2. 表后增加

基本语法；alter table 表名 add primary key(字段)

create table my_pri3(

username VARCHAR(10)

)

alter TABLE my_pri3 add PRIMARY key(username);

查看主键

\1. 通过查看表结构desc 表名，pri主键，主键描述非空且唯一。

\2. 查看表的创建语句 show create table 表名;

删除主键

基本语法：alter table drop PRIMARY key;

保留了非空条件。

复合主键

将多个字段看成一个字段，作为主键，同时满足主键约束。

案例：有一张学生选修课表，一个学生可以选择多个课程，一个课程可以由多个学生来选择，但是一个学生在一个选修课程中只有一个成绩。

主键约束：主键一旦增加，那么对对应的字段有要求

\1. 当前字段对应的数据不能为空

\2. 当前字段对应的数据不能有重复

--复合主键

create table my_score(

student_no char(10),

course_no char(10),

score int not null,

PRIMARY key(student_no,course_no)

)

select * from my_score;

insert into my_score VALUES ('001','course001',100);

insert into my_score VALUES ('002','course001',90);

insert into my_score VALUES ('001','course002',95);

insert into my_score VALUES ('002','course001',98); --报错 主键冲突

主键分类

主键分类采用的是主键对应的字段的业务意义

业务主键：主键所在的字段，具有业务意义

逻辑主键：主键所在的字段，没有业务意义

5、唯一键

唯一键：unique key ，用来保证对应的字段中的数据唯一性的。

主键也可以用来保证数据的唯一性，但是一张表中只能有一个主键，多个字段都有需求，就要用到唯一键。

唯一键在一张表中可以存在多个

唯一键允许字段为null，null可以有多个，null不参与比较。

创建唯一键与创建主键非常类似：

\1. 直接在表字段之后使用unique key(字段)

\2. 在创建完表之后也可以增加唯一键

作业：自行创建唯一键，字段id，username，给username增加唯一键，测试效果。

删除唯一键，有多个唯一键，根据唯一键名字删除

删除基本语法： alter table 表名 drop index 唯一键名字;

Index关键字:索引，唯一键是索引的一种。

6、自动增长

自动增长：auto_increment,当给定某个字段该属性之后，该列的数据在没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据。

通常应用于逻辑主键。

原理：

\1. 在系统中有维护一组数据，用来保存当前使用了自动增长属性的字段，记录当前对应数据值，再给定一个指定的步长。

\2. 当用户进行数据插入的时候，如果没有给定值，系统在原始值上加上步长变成新的数据进行插入

\3. 自动增长的触发：给定的属性字段没有提供值

\4. 自动增长只适用于数值。

基本语法：在字段之后增加一个属性：auto_increment

create table my_auto(

id int PRIMARY key auto_increment,

name VARCHAR(10) not null comment '用户名',

pass VARCHAR(10) not null comment '密码'

)

insert into my_auto VALUES (null,'jack','123');

insert into my_auto VALUES (null,'rose','123');

insert into my_auto VALUES (null,'liuneng','123');

select * from my_auto;

delete from my_auto where id=2;

二、运算符

1、算术运算符

+，-，*，/，%

基本算术运算，通常不在条件中使用，而是用于结果（select字段）

除法的运算用浮点表示，除数为0，系统返回null

拿null进行任何算术运算，结果都为null

create table ysf(

i1 int,

i2 int,

i3 int,

i4 int

)

insert into ysf VALUES (10,100,0,null);

select i1+i2,i1-i2,i1*i2,i1/i3,i2/i1,i1+i4 from ysf;

2、比较运算符

>,<,>=,<=,<>

通常用来在条件中限定结果

查询不同班级20岁以上的学生

Select class_id,count(*) from my_student where age>=20 group by class_id;

<=>比较

特殊应用：在字段结果中进行比较 select没有规定必须要有数据源

Select ‘1’ <=>1,0.02<=>0;

Mysql中数据会先转换成相同类型，再进行比较。

在mysql中没有boolean，用0，1代替，0代表false，1代表true

select class_id,count(*) from my_student where age BETWEEN 20 and 30 GROUP BY class_id;

between and 是闭区间

条件1必须小于条件2，反过来不可以。

3、逻辑运算符

And Or not

select class_id,count(*) from my_student where age>20 and age<30 GROUP BY class_id;

select class_id,count(*) from my_student where age<20 or age>30 GROUP BY class_id;

select * from my_student where not age =18;

4、in运算符

in：在sql中用来代替=，当结果不是一个值，而是一个结果集的时候。

基本语法：in (结果1，结果2……)

只要当前条件在结果集中出现，那么就成立

select * from my_student where class_id in (1,2);

5、like运算符

用来进行模糊查找

两个占位符

% ---- 代表多个字符

_ ------代表一个字符

select * from my_student where stu_name LIKE 'q%';

select * from my_student where stu_name LIKE 'q__';

三、三大范式

1. 字段是原子性的，不可再分

2. 有主键，非主键字段依赖主键，确保一个表只能说明一个事务

3. 非主键，字段不能互相依赖，每列都与主键有直接关系，不存在传递的依赖

第一范式：不符合的例子

表：字段1，字段2（字段2.1，字段2.2），字段3.。。。。

第二范式：不符合的例子

表：学号、课程号、姓名、学分

这个表说明了两个事物，学生信息、课程信息，由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖学号，所以不符合二范式。

解决：课程号变内容，或者增加课程表。或者增加逻辑主键。

第三范式：不符合第三范式的例子

表：学号、姓名、年龄、所在学院、学院地点、学院联系电话

存在依赖传递 学号==》所在学院=》学院地点、学院电话

解决方案：新增学院表

四、列类型

1、整型

Tinyint

迷你整型，系统采用一个字节来保存的整型，最大的表示范围是0-255

smallint

小整型，系统采用2个字节来保存的整型，能表示0-65535之间

Mediumint

中整型，系统采用3个字节来保存的整型。

Int

标准整型，采用4个字节来保存的数据

Bigint

大整型，采用8个字节来报从的数据。

无符号设定

无符号：表示存储的数据在当前字段中，没有负数，只有正数。

基本语法：在类型之后加上一个unsigned

显示长度

显示长度：指数据（整型）在数据显示的时候到底可以显示多少位

Tinyint(3)：表示最长可以显示3位，unsigned说明只是正数，0-255永远不会超过3位。

Tinyint(4): 表示最长可以显示4位 -127---128

显示长度只是代表了数据是否可以达到指定长度，但是不会自动满足到指定长度，如果想要显示数据的时候，保持最高位显示长度，那么还需要给字段增加一个zerofill属性才行。

Zerofill：从左侧开始填充0，从左侧不会改变数值大小，所以负数的时候就不能使用zerofill，一旦使用了zerofill就相当于确定该字段位unsigned。

如果不足3位，那么填充到3位，如果本身已经够了或者超出，那么就不再填充。

显示长度可以自己设定，超出长度（但是没有超出范围）不会影响，只会对不够长度的进行补充。

create table my_int(

int_1 TINYINT,

int_2 smallint,

int_3 MEDIUMINT,

int_4 int,

int_5 BIGINT

)

--插入数据

insert into my_int VALUES (10,1000,100000,1000000,1000000000);

select * from my_int;

--插入超出范围的值

insert into my_int VALUES (255,255,255,255,255);

错误原因：并不是说tinyint没有那么大的空间，而是因为mys