初始mysql(3w)

数据库基础

mysql清屏指令

system clear;

1.1链接服务器

cmd下输入

mysql -h 127.0.0.1 -p 3306 -u root -p

注意
如果没有写 -h 127.0.0.1 默认链接本地
如果没有写-p 3306 默认是链接3306端口号

1.2 服务器，数据库，表关系

• 所谓安装数据库服务器，只是在机器上安装了一个数据库管理系统程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
• 为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。
• 数据库服务器、数据库和表的关系如下：

1.3存储引擎

1.3.1什么数据库引擎

存储引擎是：数据库管理系统如何存储数据，如何为数据库建立索引和如何更新、查询数据等技巧。
MySQL的核心就是插件式存储引擎，支持多种存储引擎。

1.3.2如何查看存储的引擎

show engines;

库的操作

2.1创建数据库

语法

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] …]

[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

说明

• 大写的表示关键字
• [] 是可选项 [IF NOT EXISTS] 表示如果数据库不存在才创建数据库
• CHARACTER SET:指定数据库采用的字符集
• COLLATE:指定数据库字符集的校验规则

2.2 创建数据库案例

• 创建一个tmp数据库

create database tmp;

运行结果如下：

此时说明一下：当我们没有指定字符集和校验规则时，我系统默认字符集为utf8，校验规则是：utf8_general_ci

• 创建一个使用utf8字符集的tmp2数据库

create database tmp2 charset=utf8;

运行结果如下

• 创建一个使用utf字符集，并带校对规则的 db3 数据库。

create database tmp3 charset=utf8 collate utf8_general_ci;

运行结果如下

• 使用数据库

use databaseName;

示例

2.3字符集和校验规则

2.3.1查看系统默认的字符集以及校验规则

show variables like 'character_set_database';
show variables like 'collation_database';

查看系统默认字符集

查看系统校验规则

2.3.2查看数据库支持的字符集

show charset;

字符集的作用：主要控制用什么语言
举个例子utf8就可以使用中文，而有的字符集使用中文则是一团乱码

2.3.3查看数据库支持的字符集校验规则

show collation;

2.3.4校验规则对数据库的影响

• 不区分大小写
  创建一个数据库，校验规则使用utf8_general_ci (不区分大小写)

create database test1 collate utf8_general_ci;
use test1;
create table person(name varchar(20));
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');
select * from person where name='a';

最终结果如下：

创建一个数据库，校验规则为utf8_bin (区分大小写)

create database test2 collate utf8_bin;
use test2;
create table person(name varchar(20));
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');
select * from person where name='a';

最终结果如下：

2.4.2显示创建语句

show create database 数据库名

示例：

说明：

• MySQL建议我们关键字大写，但是不是必须的。
• 数据库名字反引号`,'是为了防止使用的数据库名刚好是关键字
• /*!40100 default… */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

2.4.3修改数据库

语法：

ALTER DATABASE db_name [alter_spacification [,alter_spacification]…]
alter _spacification :
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

• 对数据库的修改主要指的是修改数据库的字符集，校验规则
  举个例子：将test1数据库字符集修改成gbk
  
  自己用show create dabases test1;检测

2.4.4数据库删除

drop database [IF EXISTS] db_name;
执行后结果：

• 数据库内看不到对应的数据库
• 对应的数据库文件夹被删除，级删除，里面的数据表全被删除

2.4.5 备份和恢复

2.4.5.1备份

语法

mysqldump -p3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径(绝对路径)

2.4.5.2还原

语法

source 文件路径(绝对路径)

2.4.5.3 注意事项

• 如果备份的不是整个数据库，而是其中一张表

mysqldump -u root -p 数据库名 表名1 表名2 > 备份的路径

• 如果要备份的是多个数据库

mysqldump -u root -p -B 数据库名1 数据库名2 >备份的存放路径

3表的操作

3.1创建表

语法

CREATE TABLE table_name(
field1 datatype,
field2 datatype,
field3 datatype (切记最后一列没有逗号)
)character set 字符集 collate 校验规则 engine 存储引擎；
说明：

• file表示列名
• datatype表示列的类型
• character set 字符集 ，如果没有指定的字符集，则以所在的数据库的字符集为准
• collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准
  接下来就来创建个表

create table users(
id int,
name varchar(20) comment '用户名',
password char(32) comment '密码是32位的md5值',
birthday date comment '生日'
)character set utf8 engine MyISAM;

此处的comment相当于一个注释—方便于我们要使用时可以借助show create table users; 查看备注

3.3查看表结构

desc 表名;
拿上面的users来看看

3.4修改表

在项目的实际开发中，我们要经常修改某个表的结构，比如字段名称，字段大小，字段类型，表的字符集属性等等。

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]…);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]…);
ALTER TABLE tablename DROP (column);
案例：

• 往表中插入数据

insert into users values(1,'a','b','2000-01-01');

3.4.1添加字段

• 在users添加一个字段，用于保存图片路径
• 语法：alter table table_name add col_name column definition[first|after col_name]

alter table users add assets varchar(100) comment '图片路径';  

此语句默认把此字段放在表的最后一位

alter table users add assets varchar(100) comment '图片路径 after birthday;

把assert字段放在birthday后面

3.4.2修改字段类型

name，将其长度改为60

alter table users modify name varchar(60);

使用desc users查看表结构

• 删除password字段

注意：删除字段一定要小心，删除字段及其对应的队列数据都没了
语法：alter table table_name drop column

alter table users drop password;

此时使用desc uesers; 查看表结构可以看见表结构没有password

• 修改表名users为employee

alter table users rename to employee;    to可以省略

rename table old_name to new_name;第二种修改表名

此时可以用show tables;进行查看表

• 将employee 里name列修改为xingming
• 如果要并且修改字段大小或者字段属类型也可以重新定义
• 字段类型不可以省略不写

alter table employee change name xingming varchar(60);

此时可以进行 desc employee; 查看表结构发现字段名字或者或者字段类型

3.5删除表

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] …

示例

drop table employee;

此时用show tables; 查看表是否删除

3.6表的复制

3.6.1 表的快速复制

语法：create table table_name like old_table_name
此语句只适用于表的快速复制，其主要复制表的结构，不复制表的数据

create table tt1 like t1;

3.6.2表的数据复制

语法：create table table_name select * from old_table_name;
此语句仅使用表的数据复制，不复制表的结构等

• 区别：
  like:like方法其实是专门复制表结构的方法，但是它只复制表的结构和相关属性但并不复制数据

select：严格来讲select方法不能理解成复制表结构的方法，其实它只是执行了一个select查询语句，所以复制的结果只包含了select的字段和数据，其它表属性都有系统的配置文件决定；包括存储引擎、默认字符集等都是有系统的默认配置所决定。

4数据类型

4.1数据类型

分类	数据类型	说明
数据类型	BIT(M)	位类型。M指定位数，默认值1，范围1-64
	TINTINT	带符号的范围-128--127，无符号范围0--255，默认有符号
	BOOL	使用0和1表示真和假
	INT	带符号是-2^31次方 到2^31-1,无符号是2^32-1
	BIGINT	带符号是-2^63^次方到2^36^-1,无符号是2^64-1|
	FLOAT(M,D)	M指定显示长度，d指定小数位数，占用4字节
	DOUBLE(M,D)	表示比float精度更大的小数，占用8字节
	DECIMAL(M,D)	定点数M指定长度，D表示小数点的位数
文本、二进制类型	CAHR(SIZE)	固定长度字符串，最大255
	VARCHAR(SIZE)	可变长度字符串，最大长度65535
	BLOB	二进制数据
TEXT	大文本，不支持全文索引，不支持默认值
时间日期	DATA/DATATIME/TIMESTAMP	时间类型(yyyy-mm-dd)(yyyy-mm-dd hh:mm:ss)timesetamp时间戳
String类型	ENUM类型	ENUM是一个字符串对象，其值来自表创建时在列规定中显示枚举的一个值
	SET类型	SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各成员之间用逗号隔开。这样set成员值本身不能包含逗号

4.2数值大小

类型	字节	最大值	最小值
		(带符号的/无符号的)	(带符号的/无符号的)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

4.2.1tinyint类型

数值越界测试：

create table t1(num tinyint);
insert t1 values(-129);

运行结果如下

如图显示的：error 1264 Out of range value for column ‘num’ at row 1；指出在第一行插入的数据超出num的范围
说明：

• 在mysql中整形可以指定是无符号还是有符号的，默认是有符号的。
• 可以通过unsigned 来说明某个某个符号是无符号的
  下面来说明无符号的案例

create table t2(num tinyint unsigned);
insert into t2 values(-1);

mysql 结果如下

4.2.2bit类型

基本语法：

bit[(M)]: 位字段类型,M表示每个值的位数，范围从1到64，如果M被忽略默认为1，切记 bit 字段在显示时，是按照ASCII码对应的值显示。

举个示例：

create table if not exists t4(id,int,a bit(8));
insert into t4 values(10,10);
select * from t4;

由上图可以得知，可以看到插入数据成功了，但是a的值没显示出来
此时我们根据ASCII码表可以得知，ASCII为10的是换行
此时我们再插入一个65的数值进去

可以得知它显示得ASCII码对应的值显示。
测试下，一个bit位可以最大存多少

create table if not exists t5(num bit(1));
insert t5 values(0);
insert t5 values(1);
insert t5 values(2);

运行结果如下：

4.2.3小数类型

4.2.3.1 float

语法：

float [(m,d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4字节

案例：

• 小数：float(4,2)表示的范围是-99.99~99.99,MySQL在保存值会进行四舍五入。
• float(4,2) unsigned 表示的范围是0~99.99。
  下面来进行mysql测试下

create table t7(nums float(4,2));
insert into t7 values(99.99);
insert into t7 values(-99.99);
insert into t7 values(-100);
insert into t7 values(-99.992);
insert into t7 values(-99.995);

运行结果如下：

4.3.2 decimal

语法：

decimal (m,d) [unsigned] :定点数m指定长度，d指定小数后面的位数

• decimal(5,2)表示的范围是-999.99~999.99
• deciaml(5,2) unsigned 表示的范围0~999.99
• decimal和float很像但还是有区别的： float和decimal的精度不一样，float的最大精度为7，decimal最大精度为30，且decimal不输入m，默认m为10，不输入d，默认d为0
  下面来精测精度

create table if not exists t8(id int,salary float(10,8),salary2 decimal(10,8));
insert into t8 values(100,23.12345612,23.12345612);
select * from t8;

此时，我们可以看到salary一列的后面两位数值出现了问题
下面来进行看decimal的默认值

create table if not exists t9(nums decimal);
desc t9;

4.3字符串类型

4.3.1char

语法：

char(L):固定长度字符串，L是可以储存的长度，单位是字符(不是字节，一个汉字或一个字母为一个字符)，最大长度255.

create table if not exists t10(id int,name char(2));
insert into t10 values(100,'ab');
insert into t10 values(101,'中国');
select * from t10;

4.2.2 varchar

语法：

varchar(L):可变长度字符串，L表示字符长度，最大长度65535个字节

create table if not exists t11(id int,name varchar(6));
insert into t11 values(100,'hello');
insert into t11 values(101,'祝祖国繁荣');
select * from t11;

特此说明一下：

• varchar(L),这个L表示字符，varchar的最大长度为65535字节，表的编码不同，会有1-3个字节用于表示一个字符。
• 当我们的表的编码为utf8时，varchar(L)中L的最大值为65535/3=21844字符（因为一个字符占了3个字节），如果编码为gbk时，varchar(L)的参数最大值是65535/2=32766（一个字符占了两个字节）
  下面来验证varchar在utf-8下的最大值

create table t12(name varchar(21845))charset=utf8;
create table t12(name varchar(21844))charset=utf8;

下面用linux检测一个汉字多少字节

借助cut命令来检查

echo '中国' | cut -b 1-3
echo '中国' | cut -b 4-6

4.3.3char 和varchar的比较

实际储存	char(4)	varchar(4)	char占用字节	varchar占用字节
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	1*3=3	1*3+1=4
Abcde	X	X	数据超过长度	数据超过长度

** 如何选择定长或变长字符串 **

• 如果数据长度都一样，就用定长(char),比如：身份证，手机号，md5
• 如果数据长度有变化，就用变长(varchar),比如：名字，地址，但是你要保证最长的能存的进去
• 定长的磁盘空间比较浪费，但是效率高
• 变长的磁盘空间比较节省，但是效率低

4.4日期和时间类型

常用的时间有如下三个：

• date ：日期’yyyy-mm-dd’，占用三字节
• datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss’表示范围从1000-9999，占用8字节
• timestamp：时间戳，从1970年开始的’yyyy-mm-dd HH:ii:ss’格式和datetime完全一致，占用四字节 时间戳不写，默认当前北京时间
  案例：
  写法一：

create table if not exists t13(t1 date,t2 datetime,t3 timestamp);
insert t13 values('2000-01-01','2000-01-01 12:01:01',NULL);
select * from t13;

写法二：

insert t13(t1,t2) values('2000-01-01','2000-01-01 12:01:01');
select * from t13;

切记写法二的数据输入时要一一对应
结果自己使用mysql查询吧

4.5enum 和set

语法：

• enum ：枚举，“单选”类型
• enum(‘选项1’,‘选项2’,‘选项3’,……);
  该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。
• set:集合，“多选”类型；
• set(‘选项值1’,‘选项值2’,‘选项值3’, …);
  该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字1,2,4,8,16,32，… 最多64个。（可以根据二进制来理解）

说明一下：不太建议添加枚举值，集合值的时候采用数字的方式，因为不太利于阅读

题目：有一个调查表，需要调查人的喜好(多选)，比如性别(单选)
建表

create table votes(
username varchar(30),
sex enum('男','女'),
hobby set('登山','游泳','篮球','武术','绘画')
);

插入数据：

insert votes values
('张三','男','游泳,篮球,武术'),
('李四','男','绘画'),
('王五','男','登山'),
('雷锋','男','登山,武术,游泳'),
('小红','女','武术,游泳'),
('小蓝','女','登山,绘画');

使用查询语句：

select * from votes;

此时我们可以看到数据已经插入成功
此时我们试着用where指令查询喜好登山的人

select * from votes where hobby='登山';

此时我们发现,貌似不能查出所有爱好为爬山的人
此时我们要引入一个find_in_set函数
具体使用help find_in_set查询如下：

此时我们就用此函数来查所有爱好为登山的人

select * from votes where find_in_set('登山',hobby);

5.表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。

5.1空属性

• 两个值：null（默认的）和not null(不为空
• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。
• 设置为非空属性时，插入禁止为空，否则会报错

举个例子：

• 如果学校没有名字，那么你知道你学校在哪吗？
• 一个学校没有地址，你也知道你学校在哪吗？
• 综上所诉，我们在设计数据库，一定要限制

具体使用

create table school(
school_name varchar(30) not null,
school_address varchar(30) not null
);

使用desc命名查看school结构

5.2默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。
默认值的生效：数据在插入的时候不给该字段赋值时，就使用默认值

create table t1(
name varchar(20) not null,
age tinyint unsigned default 0,
sex enum('男','女') default '男'
)character set utf8;

使用desc查看表的结构 我们可以看到表的default一列

• 如果创建表时没有设置默认属性，想添加(修改)默认值

alter table table_name modify col_name col_defination default 默认值;

案例修改(type 一列要写全，否则自己试试看)

alter table t1 modify age tinyint(3) unsigned default 18;

• 删除默认值

alter table table_name modify col_name col_defination (后面的default 不要加下去)

5.3列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解

create table t2(
name varchar(20) not null comment '姓名',
age tinyint unsigned not null comment '性别',
sex enum('男','女') default '男'
)character set utf8;

我们使用desc 命名查看不到注释信息
要使用show create table t2;来查看信息

5.4主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

案例：

• 创建表的时候可以直接在字段上指定主键

create table if not exists t3(
id int unsigned primary key comment'学号',
name varchar(30) not null);

使用desc可以看到表的结构里id在key列多了PRI指的就是主键

• 也可以在表创建完后指定主键

create table t4(
id int unsigned not null,
name varchar(30) not null);
alter table t4 add primary key (id);

删除主键使用

alter table t4 drop primary key;

此时使用desc命名查看表结构

• 复合主键
  在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

create table t5(
id int unsigned,
course char(10) comment '课程代码',
score tinyint unsigned default 60 comment '成绩',
primary key(id, course)
);

primary key (id,course) 也可以改成alter table t5 add primary key(id,cource); 复合主键，指多个字段共同合起来充当一个主键

• 符合主键的插入–复合主键的组成部分字段中数值重复是可以的，但是不可以整体重复
  拿t5当例子–其是id和course合起来充当一个主键

insert t5 values(1000,'123',60);
insert t5 values(1000,'1234',60);
insert t5 values(1000,'123',60);

5.5自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
自增长的特点：

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长
• 自增长默认从1开始
  举例：如果创建表时，没加索引

create table t6(
id int unsigned auto_increment,
name varchar(20) not null
);

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
so：我们创建表是一定要加索引

create table t7(
id int unsigned  primary key auto_increment,
name varchar(20) not null
);

我们插入数据看看

insert into t7(name) values('李四');
insert into t7(name) values('张三');

使用select 命令查看表中数据，并且用show命令查看下表


此时我们看到AUTO_INCREMENT=3;
再插入后获取上次插入的AUTO_INCREMENT的值

select last_insert_id();

索引：
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

5.6唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。

create table student(
id char(10) unique comment '学号，不能重复，但可以为空',
name varchar(10)
);

此时插入数据

insert student values(100,'aaa');
insert student values(100,'bbb');
insert student values(NULL,'aaa');
select * from student;

5.7外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。
语法 foreign key (字段名) references 主表(列)
案例：

如果将班级表中的数据都设计在每个学生表后面，那么就会显得数据冗余，所以我们只要设计成让学生表的class_id和班级表的id形成关联的关系==>外键约束
*对上面的示意图进行设计：
对主表(班级表进行设计)

create table class(
id int primary key,
name varchar(30) not null 
);

再建从表

create table stu(
id int primary key,
name varchar(20) not null ,
class_id int,
foreign key(class_id) references class(id)
);

切记主表和从表的建立链接的字段要一样，且插入数据时，要先插入主表，从表根据主表的数据进行插入

那么我们怎么判断主表和从表呢？
主表和从表的关系1:N

• 正常插入
  先对主表进行插入

insert into class values(10,'一年级一班');
insert into class values(20,'一年级二班');

再对从表进行插入

insert into stu values(100,'王五','10');
insert into stu values(101,'李四','20');

• 现在插入一个班级号为30的学生

insert into stu values(102,'张三','30');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test.stu, CONSTRAINT stu_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (id))
原因组表没有id为30的班级

• 插入班级id为null，比如来了一个学生，目前还没有分配班级

insert into stu values(105,'关羽',null);

• 如果在从表创建中忘记加外键

alter table 从表名 add [constraint 约束名] foreign key(从表外键字段名) references 主表名(主表字段名);

*删除外键

alter table 从表名 drop foreign key 约束名
*切记删除表时，必须先删除外键。

6.表的增删改查

CRUD:Create(创建),Retrieve(读取),UPdate(更新),Delete(删除)

6.1Create

INSERT [INTO] table_name
[(column [, column] …)]
VALUES (value_list) [, (value_list)] …
value_list: value, [, value] …

案例：

• 创建一张学生表

drop table if exists student;
create table if not exists student(
id int unsigned primary key auto_increment,
sn int not null unique comment'学号',
name varchar(20) not null,
qq varchar(20)
);

6.1.1 单行数据+全列插入

** 插入记录** value_list 数量必须和定义表的数量和顺序一致
切记表中含有主键和唯一键数据不能重复

insert into student values(100,10000,'唐三藏',NULL);
insert into student values(666,10001,'孙悟空',NULL);

使用select查看表的数据,我们可以看到数据已经插进表

select * from student;

此时我们用show 命名查看表的结构可以看到表中AUTO_INCREMENT=667了

6.1.2多行数据+指定列插入

** 插入记录** value_list 数量必须和定义表的数量和顺序一致

insert into student(id,sn,name) values
(199,1,'关羽'),
(201,3,'刘北'),
(200,2,'张飞');

我们此时使用select命名可以看到

6.1.3插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败
*主键冲突

insert into student values(100,4,'达摩',null);

ERROR 1062 (23000): Duplicate entry ‘100’ for key ‘PRIMARY’

• 唯一键冲突

insert into student values(233,3,'达摩',null);

ERROR 1062 (23000): Duplicate entry ‘3’ for key ‘sn’

• 可以选择性的进行同步更新操作 语法：

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

案例：

INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';

– 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，并且数据已经被更新

6.1.4替换

– 主键 或者 唯一键 没有冲突，则直接插入；
– 主键 或者 唯一键 如果冲突，则删除后再插入

replace into student(sn,name) values(10010,'李白');

– 1 row affected: 表中没有冲突数据，数据被插入
– 2 row affected: 表中有冲突数据，删除后重新插入

6.2 Retrieve

语法：SELECT
[DISTINCT] {* | {column [, column] …}
[FROM table_name]
[WHERE …]
[ORDER BY column [ASC | DESC], …]
LIMIT …

• 创建一个成绩表(里面有编号，姓名，语文，数学，英语),并且插入一些成绩，

create table exam_result(
id int unsigned primary key auto_increment,
name varchar(20) not null comment '同学姓名',
chinese float comment '语文成绩',
math float comment '数学成绩',
english float comment '英语成绩'
);
insert exam_result(name,chinese,math,english) values
('刘备',66,70,70),
('李白',80,70,90),
('魔鬼',90,50,70),
('孙尚香',70,90,80),
('摩根',80,80,75);

6.2.1 select

6.2.1.1全列查询

语法：select * from table_name;
作用表中的数据全部展示出来

select * from exam_result;

6.2.1.2指定列查询

–指定列的顺序不需要按定义表的顺序来
语法：select col_name,col_name……from table_name;

select id,name from exam_result;

6.2.1.3查询字段为表达式

• 表达式不包含字段

select id,name,10 from exam_result;

• 表达式包含一个字段

select math+10 from exam_result;

6.2.1.4为查询的结果别名

语法:SELECT column [AS] alias_name […] FROM table_name;
案例:统计总分

select id,math,english,chinese,math+english+chinese 总分 from exam_result;

6.2.1.5查询的结果去重

语法：SELECT distinct column […] FROM table_name;

select distinct math from exam_result;

6.2.2 where 条件

6.2.2.1符号表

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如NULL=NULL的结果为NULL
<=>	等于，NUL安全，例如NULL<=>NULL的结果为TRUE(1)
!=,<>	不等于
BETWEEN a0 AND A	范围匹配，[a0,a1],如果a0<=value<=a1,返回TRUE(1)
IN(option,……)	如果是option中任意一个，返回TRUE(1)
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配.%表时任意多个字符；_表示任意一个字符

逻辑运算符

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

6.2.2.2符号表的运用

举个例子:可以观察到他的一些常规写法

selete * from exam_result where math<60;

• 写出数学成绩在一定的区间,例如60~70

select * from exam_result where math>=60 and math<=70;
或者:
select * from exam_result where math between 60 and 70;

• 查数学成绩在58，60，62，65

or的运用
select * from exam_result where math=58,math=60,math=62,math=65;
或者in的运用
select * from exam_result where math in (58,60,62,65);

• 模糊度的运用

% 匹配任意多个(包括0个)字符

select name from exam_result where name like '孙%';

_严格匹配的一个字符

select name from exam_result where name like '孙_';

• 并列条件

_与%匹配更加严格，且在孙前还是孙后都可以使用

6.2.2.3别名?

别名在select里是可以使用的

select name,math+chinese+english 总分 from exam_result where 总分<200;

个人理解：mysql实现执行where查找语句，在进行筛选。

6.2.3结果排序

语法： asc为升序(从小到大)，desc为降序 默认asc
SELECT … FROM table_name [WHERE …]ORDER BY column [ASC|DESC], […];
注意:没有order by 子句的查询，返回的顺序是未定义的，它是按照数据的先后插入排序的
运用：

• 数学成绩升序排序显示：

select name,math from exam_result order by math ;

• 数学成绩按照降序排列显示

select name,math from exam_result order by math desc;

• 查询多个字段，并且字段有的降序，有的升序

select name,english,chinese from exam_result order by math desc,english,chinese;

原理先进行数学成绩的排序，若数学成绩相同，则进行英语成绩的比较……;

• 别名的运行

select name,chinese+english+math 总分 from exam_result order by 总分;

• 表达式的运用

select name,chinese+english+math 总分 from exam_result order by chinese+english+math;

6.2.4筛选分页结果

语法：

– 起始下标为 0
– 从 0 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
– 从 s 开始，筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n;
从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;

select id,name,math,english,chinese from exam_result order by id limit 3 offset 0;

6.3update

语法：
UPDATE table_name SET column = expr [, column = expr …] [WHERE …] [ORDER BY …] [LIMIT …]
举例：

6.3.1修改表中的数据

• 修改孙尚香的数学成绩为80分

update exam_result set math=80 where name='孙尚香';

• 一次更新多列

update exam_result set math=100,chinese=80 where name='李白';

update 和order的连用
语法：

• 将总成绩倒数前三的3位同学的数学成绩加上30分
  update中offset用不了

update exam_result set math=math+30 order by chinese+english+math asc limit 3;

• 直接刷新原有数据

没有用where子句等

update exam_result set math =0;
select * from exam_result;

6.4 Delete

6.4.1删除数据

语法:delete from table_name [where……] [order by……][limit……]

6.4.1.1 删除表中某一行数据

select * from exam_result where name='孙尚香';
delete from exam_result where name='孙尚香';
select * from exam_result;

6.4.1.2删除整表的数据

delete from stu;
select *from stu;

6.4.2截断表

语法：truncate [table] table_name
注意：这个指令少用
1.只能对整表进行操作，不能像delete一样删除部分操作
2.会进行重置AUTO_INCREMENT项

感谢观看，谢谢.