mySQL基础知识

MySQL数据库基本操作-DDL

2、创建表
创建表格式：

create table 表名（
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
）[表的一些设置];

创建表是构建一张空表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型。

3、对表结构的常用操作

简单操作：

show tables; //查看当前数据库的所有表名称
show create table 表名; //查看指定某个表的创建语句
desc 表名； //查看表结构
drop table 表名; //删除表

添加列：

alter table 表名 add 列名 类型（长度）[约束]

修改列名和类型：

alter table 表名 change 旧列名 新列名 类型（长度） 约束

删除列：

ALTER TABLE student DROP department;

修改表名：

rename table 表名 to 新表名；

4、DML操作

DML指数据操作语言，全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。（增、删、改）

1、数据插入

insert into 表(列名1，列名2，列名3...)values (值1，值2，值3...)；  //向表中插入某些列
insert into 表values (值1，值2，值3...)；  //向表中插入所有列
// 一条insert命令添加一行值。如果想同时添加多行值，在值后加逗号，再写上其他值（values不用重复写）

2、数据修改

update 表名 set 字段名=值，字段名=值...；
update 表名 set 字段名=值，字段名=值... where条件；

3、数据删除

delete from 表名 [where 条件]；
truncate table 表名 / truncate 表名；
//truncate与delete from 都可以删除所有数据，他们的区别为truncate更彻底，将表一起删除后再创建一个新表。

5、MySQL约束

在创建表时使用，是表中数据的限制条件，能够保证表中的记录完整性和有效性。
分类：

• 主键约束
• 自增长约束
• 非空约束
• 唯一性约束
• 默认约束
• 零填充约束
• 外键约束

1、主键约束（重要）
主键约束是一个列或者多个列的组合，其作用为使目标列不允许重复，也不允许出现空值。（相当于唯一约束与非空约束的组合）其值能唯一地标识表中的每一行，方便在RDBMS中尽快的找到某一行。

注意：

• 每个表最多只允许一个主键
• 当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

操作：

添加单列主键①

create table 表名(
    ...
    字段名 数据类型 primary key //该列不能重复且不能为空
    ...
)

添加单列主键②

create table 表名(
    ...
    [constraint <约束名>] primary key [字段名]
)
-- [constraint <约束名>]可以省略

添加联合主键①
添加联合主键时，无法使用添加单列主键①的方法，需要在最后声明。

create table 表名(
    ...
    primary key (字段1， 字段2， ...， 字段n)
)

同时，一张表只能有一个主键，联合主键也是一个主键。

添加联合主键②
也可以通过修改表结构添加主键，与上面添加列操作类似。

create table xxx(
    ...
    列1 ...
    ...
);
alter table xxx add primary key (列1,...);

删除主键约束

alter table <数据表名> drop primary key;
-- 一张表只有一个主键，因此操作简单

2、自增长约束
在MySQL中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。
语法：

字段名 数据类型 auto_increment

规则：

• 默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1
• 一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• auto_increment约束的字段必须具备NOT NULL属性
• auto_increment约束的字段只能是整数类型（TINYINT SMALLINT INT BIGINT）等
• auto_increment约束字段的最大值受该字段的数据类型约束。如果达到上限，auto_increment就会失效。

同时，可以指定自增字段的初始值，在创建表时指定或创建之后指定。

--创建时
create table x(
    ...
)auto_increment=100;
--创建之后
alter table 字段名 auto_increment = 200;

3、非空约束

指字段的值不能为空。如果用户在对使用了非空约束的字段添加数据时没有指定值，数据库系统就会报错。
添加非空约束的两种方式：

<字段名><数据类型> not null; --方式1
alter table 表名 modify 字段 类型 not null; --方式2

删除非空约束：

alter table 表名 modify 字段 类型；

4、唯一约束（unique）

指所有记录中字段的值不能重复出现。

<字段名><数据类型> unique;
alter table 表名 add constraint 约束名 unique（列）；

注意，在MySQL中NULL和任何值都不相同，甚至和自己都不相同。因此可以重复为NULL。
删除唯一约束：

alter table 表名 drop index 约束名；

5、默认约束（default）

用来指定某列的默认值。

<字段名><数据类型> default <默认值>; --方式1
alter table 表名 modify 列名 类型 default 默认值; --方式2

6、零填充约束（zerofill）

• 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
• zerofill默认为int（10）
• 当使用zerofill时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128_{+127，无符号为0}256.

create table xx(
	id int zerofill, --零填充约束
	name varchar(20)
);
-- 删除
alter table xx modify id int;

MySQL数据库基本操作-DQL-基本查询

1、mysql使用select来查询数据。

select
	[all|distinct]
	<目标列的表达式1> [别名]
	<目标列的表达式2> [别名]...
from <表名或视图名> [别名]，<表名或视图名> [别名]...
[where<条件表达式>]
[group by<列名>]
[having<条件表达式>]
[order by<列名> [asc|desc]]
[limit<数字或者列表>]

简化版语法：

select *| 列名 from 表 where 条件

一些额外操作：

--表别名
select * from product as p;
select * from product p;
select p.id,u.id from product p, user u;

--列别名
select pname as '商品名', price '商品价格' from product;

--去掉重复值
select distinct price from product;
select distinct * from product;

--查询结果是表达式（如将所有商品加价10元展示） 再起个别名
select pname, price +10 new_price from product;

2、运算符

通过运算符，获取一些表结构以外的数据。
算术运算符：

比较运算符：

like 关键字用于模糊范围搜索，后加搜索目标；
搜索目标放在单引号’ '中，%代替若干任意字符，_代替一个字符，如 ‘%鞋%’ 表示字段中有鞋的目标， ‘_给’ 表示由两个字符组成的，且后一个字符为 给 的目标。

逻辑运算符：

位运算符：

3、排序查询

可以使用 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

select
	字段名1，字段名2，....
from 表名
order by 字段名1 [asc|desc], 字段名2[asc|desc]....
--中括号要省略不写！

其中：

• asc表示升序，desc表示降序，如果不写默认为升序
• order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
• order by 子句，放在查询语句的最后面，LIMIT子句除外。
• select后再加 distinct 字段名 可实现去重排序

4、聚合查询

之前做的查询都是横向查询，根据条件一行一行进行判断，而使用聚合操作是纵向查询，对一列的值进行计算，然后返回一个单一的值。另外聚合函数会忽略空值。

count(); 统计指定列不为NULL的记录行数
sum(); 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max(); 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
min(); 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
avg(); 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

当表格中出现NULL值时，如果统计的是整张表，则NULL值不会对结果造成影响；如果统计单列，则统计结果忽略NULL值。

5、分组查询

使用group by字句对查询信息进行分组

select 字段1，字段2... from 表名 group by 分组字段 having 分组条件；
如
select category_id , count(*) from product group by category_id;

在执行分组操作后，原来的表格可看作切分成若干张表。count操作会对每张表计算行数，而只计算行数我们也不知道是哪个id对应哪些行，于是再将category_id展示出来。
同时，group by后面也可以跟多个字段，只有都满足条件的才会分到一个组。分组之后，select后边只能写分组字段和聚合函数。

having为分组之后的筛选条件，这里不能用where。where是跟在from后，作用于from的语句。甚至group by的优先级都在where之后，是对where的结果进行分组，再having进行条件筛选。

6、分页查询

   分页查询在项目开发中常见， 由于数据量很大，显示器长度有限，因此对数据需要采取分页显示的方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

格式：

显示前n条
select 字段1，字段2... from 表名 limit n;
分页显示
select 字段1，字段2... form 表名 limit m,n;

7、insert into select语句

将一张表的数据导入到领一张表中，可以使用insert into select语句

insert into Table2(field,field2,...) select value1,value2,... from Table1
或者
insert into Table2 select * from Table1

8、正则表达式

正则表达式描述了一种字符串匹配的规则，其本身就是一个字符串，使用字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。
mysql通过REGEXP关键字支持正则表达式进行字符串匹配。

用法举例：

select * from product where pname REGEXP '^海'

表示筛选出以海字开头的名字。
‘水$’ 表示以水结尾

'.b’表示判断第二个字符是否为b，.可以匹配除了换行符以外的任意字符

[xyz] 表示判断字符中是否出现x或y或z，只要出现就返回1

[ ^abc]表示判断字符中只要存在不是a、b、c的字符，就返回1
(^只有在[]内才是取反的意思)

a*表示匹配0个a或多个a，包括空字符串
a+表示匹配1个a或多个a，不包括空字符串
a？表示匹配0个a或1个a
a1|a2 表示匹配a1或者a2

a{m}表示匹配m个a
a{m,}表示匹配m个或更多个a
a{m,n}表示匹配m到n个a，包含m和n