学习笔记-mysql基础(DDL,DML,DQL)

一.DDL

DDL,Data Definition Language,数据库定义语言,该语言包括以下内容:

• 对数据库的常用操作
• 对表结构的常用操作
• 修改表结构

1.对数据库的常用操作

-- 查看所有的数据库
show databases
-- 创建数据库
create database [if not exists] test [charset=utf8]
-- 切换 选择 数据库
use test 
-- 删除数据库
drop database [if exists] test
--修改数据库编码
alter database test character set utf8

注意:在sql server 中使用,if exists和mysql不同,sql server里面检查是否有这个表再删除，需要这样：if exists (select * from dbo.sysobjects where id = object_id(N’表名’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)

2.对表结构的常用操作

(1) 创建表

create table [if not exists]表名(
	字段名,数据类型[(宽度)],约束,[comment'字段说明']
)

数值类型

日期类型

字符串

PS:记录一些数据类型的使用与sql server不同的地方(日后补充):
1.在SQL Server中, Tinyint类型表示一个无符号整数，它的取值范围是从0到255。而MySQL中,默认取值范围是从-128到127,可以通过unsigned关键字的使用,取值范围变为0到255
2.MySQL中没有nvarchar数据类型，但可以使用varchar类型来存储Unicode字符数据。使用utf8mb4字符集和正确的排序规则可以确保正确存储和比较Unicode字符数据

(2) 其他操作

-- 查看数据库的其他表
show tables
-- 查看某个表的创建语句
show create table 表名 # 可以看到字段数据类型及长度还有约束等
-- 查看表结构
desc 表名  //这个真的很好用,比sql server方便很多!
-- 删除表
delete table 表名

3.对表结构的常用操作

(1) 列名操作

-- 添加
alter table 表名 add 列名 数据类型(长度) [约束]
-- 修改 可以适用于很多修改
alter table 表名 change 旧列名 新列名 数据类型(长度) [约束]
-- 删除
alter table 表名 drop 列名

注意: SQL Server中修改字段名和数据类型需要分开修改,如下所示:

-- SQL Server
exec sp_rename '表名.旧列名','新列名'
alter table 表名 alter column 新列名 数据类型(长度) 

(2) 修改表名

-- mysql
rename  table 表名 to 新表名
-- sql server
exec sp_rename '表名','新表名'

二. DML

DML,Data Manipulation Language,用来对数据库中表的数据记录进行更新
关键字:

• insert
• delete
• update

1. 数据插入

insert  into 表(列1,列2,...) values (值1,值2,...) //插入一行
insert  into 表(列1,列2,...) values (值1,值2,...),(值11,值22,...) //插入多行
insert into 表(列1,列2,...)  select 值1,值2,... [from 其他表]

2. 数据修改

update 表名 set 字段名=值1 [where 条件]

3. 数据删除

delete  from 表名 [where 条件]  # 与sql server不同的是,sql server中可以直接使用 delete 表名,from可以省略 
truncate table  表名 	|   truncate 表名

注意:delete和truncate原理不同,delete只删除内容,而truncate类似于drop table ,可以理解为将整表删除,然后再创建该表
truncate 效果等同于delete之后再重置自增键,alter table 表名 auto_increment=新的自增值(效果和sql server中的 dbcc checkident (表名, reseed , 新的自增值))

三. 约束

表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性.
分类:

• 主键约束(primary key) PK
• 自增长约束(auto_increment)
• 非空约束(not null)
• 唯一性约束(unique)
• 默认约束(default)
• 零填充约束(zerofill)
• 外键约束(foreign key) FK

1. 主键约束

• 每个表最多只允许有一个主键
• 主键约束相当于 唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值
• 当创建主键的约束时,系统默认会在所在的列和列组合上建立对应唯一索引

(1) 单列主键

-- 在create table时,通过primary key关键字来指定主键
create table 表名(
<字段名> <数据类型> primary key
)
-- 在定义字段之后再指定主键
create table 表名(
...
[constraint <约束名>] primary key(字段名)
);

(2) 多列主键(联合主键)

 create table 表名(
 ...
[constraint <约束名>] primary key(字段1,字段2,...)
);

注意:联合主键的每一列都不能为空

(3) 通过修改表结构来添加主键

alter table 表名 add primary key (字段)  //可当列可多列

(4) 删除主键

alter table 表名 drop primary key 

2. 自增长约束

语法: 字段名 数据类型 auto_increment
特点:

• 默认情况下,auto_increment的初始值是1,每新增一条记录,字段值自动+1
• 一个表中只能有一个字段使用auto_increment,该字段必须有唯一索引,以免序号重复(即为主键或者主键的一部分)
• auto_increment约束的字段必须具备NOT NULL属性
• auto_increment约束的字段只能是整数类型
• auto_increment约束的字段的最大值受该字段的数据类型约束,如果达到上限,就会失效
  注意:
  [42000][1075] Incorrect table definition; there can be only one auto column and it must be defined as a key |表定义错误;auto列只能有一个，并且必须定义为键
  mysql的自增键必须是主键或者主键的一部分,与mysql 不同的是,sql server中,自增键可以是主键,但主键不一定是自增键

(2) 指定字段的初始值

  -- 创建表时指定
  create table 表名(
  字段 数据类型 primary key auto_increment,
  ...
  ) auto_increment=100 
  -- 创建表之后指定
  create table 表名(
  字段 数据类型 primary key ,
  ...
  ) auto_increment=100 
  alter table 表名 auto_increment=100 
  -- 

注意:设置了自增长的字段上,在mysql中,直接插入null值或者0,系统会默认成自增长的值,但是在sql server中会直接报错

3. 非空约束

(1) 添加非空约束

# 创建表时指定
create table 表名(
字段 数据类型 not null ,
...
)
# 创建表之后指定
alter table 表名 modify 字段 数据类型 not null ;

(2) 删除非空约束

# 方法1
alter table 表名 modify 字段 数据类型 ; 
# 方法2
alter table 表名 change 字段 数据类型 ; 
# 注意:如果是主键,不能直接删除非空约束
alter table 表名 drop primary key
alter table 表名 change 字段 数据类型 ; 

4. 唯一约束

(1) 添加唯一约束

# 方法1 创建表时指定
create table 表名(
字段 数据类型 unique,  # 默认字段名就是约束名
...
)
# 方法2 创建表之后指定
alter table 表名 add constraint 约束名 unique(列名) ;

注意:添加唯一约束的时候系统会给添加一个索引 create unique index,默认名字是字段名

(2) 删除唯一约束

# 方法1
alter table 表名 drop index  约束名 ; 
# 方法2
drop index 约束名 on 表名;

5. 默认约束

(1) 添加默认约束

# 方法1 创建表时指定
create table 表名(
字段 数据类型 default 默认值 ,  
...
)
#方法2 创建表之后指定
alter table 表名 modify 字段 数据类型 default 默认值 ;

(2) 删除默认约束

# 方法1
 alter table 表名 change 字段名 字段名 数据类型;
# 方法2
alter table 表名 modify 字段 数据类型 default null ;

6. 零填充约束

• 插入数据时,当该字段的值的长度下于定义的长度时,会在该值面前补上相应的0(作用:长度固定的显示效果)
• zerofill默认为int(10)
• 当使用zerofill时,默认会自动加unsigned(无符号)属性,使用unsigned后,数值范围是原来的2倍,例如,有符号为-128到127,无符号为0都256

(1) 添加零填充约束

create table 表名(
字段 数据类型 zerofill ,  
...)

(2) 删除零填充约束

alter table 表名 modify 字段 数据类型 ;

四. DQL 基本查询

DQL:Data Query Language 数据查询语言，用来查询数据库中的记录

1. 基本语法

select [all|distinct]  字段1 [别名1],字段2 [别名2]...
from 表名 
where 条件 
group by 分组字段                                   
having 分组之后的条件 [asc|desc]
order by 排序
limit 数字或列表

详细用法见数据查询语言DQL

2. 正则表达式

(1) 介绍

mysql通过关键字regexp支持正则表达式进行字符串匹配

(2) 使用

-- ^ ：在字符串开始处进行匹配 
select 'abc' regexp '^a'   --1
--$ : 在字符串末尾开始匹配
select 'abc' regexp 'a$'   --0
select 'abc' regexp 'c$'   --1
-- . ：可以匹配除了换行符以外的任意字符
select 'abc' regexp 'a.'   --1
select 'abc' regexp '.b'   --1
select 'abc' regexp '.c'   --1
select 'abc' regexp '.d'   --0
-- [...] 匹配括号内的任意单个字符
select 'abc' regexp '[xyz]'   --0
select 'abc' regexp '[xaz]'   --1
-- [^...] 注意^符号只有在[]内才是取反的意思,在别的地方都是表示开始处匹配
select 'a' regexp '[^xyz]'   --1
select 'x' regexp '[^xyz]'   --0
select 'abc' regexp '[^a]'   --1
-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
SELECT 'stab' REGEXP '.ta*b'  --1
SELECT 'stab' REGEXP '.(ta)*b'  --1
SELECT 'stb' REGEXP '.ta*b'  --1
SELECT 'stb' REGEXP '.(ta)*b'  --0
SELECT '' REGEXP 'a*'  --1
-- a+  匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b' --1
SELECT 'stb' REGEXP '.ta+b' --0
-- a?  匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b' --1
SELECT 'stab' REGEXP '.ta?b' --1
SELECT 'staab' REGEXP '.ta?b' --0
-- a1|a2  匹配a1或者a2
SELECT 'a' REGEXP 'a|b' --1
SELECT 'b' REGEXP 'a|b' --1
SELECT 'b' REGEXP '^(a|b)' --1
SELECT 'a' REGEXP '^(a|b)' --1
SELECT 'c' REGEXP '^(a|b)' --0
-- a{m} 匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c' --1
SELECT 'auuuuc' REGEXP 'au{3}c' --0
-- a{m,n} 匹配m到n个a,包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c' --1
SELECT 'auuuuc' REGEXP 'au{4,5}c' --1
SELECT 'auuuuc' REGEXP 'au{5,10}c' --0
-- (abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y' --1
SELECT 'xababy' REGEXP 'x(ab)*y' --1
SELECT 'xababy' REGEXP 'x(ab){2,3}y' --1
SELECT 'xababy' REGEXP 'x(ab){3}y' --0

3. 多表关系

(1) 外键约束

//创建表时添加
create table 从表表名(
...,
[constraint 外键名 ] foreign key 列名 references 主键表(主键字段)
)
//创建表后添加
alter table 从表表名 add constraint 外键名 foreign key (列名) references 主键表名(主键字段)
//删除外键
alter table 表名 drop foreign key 外键名

(2) 多表联合查询

• 交叉连接查询[产生笛卡尔积,select * from a,b]
• 内连接查询[inner join]
• 外连接查询[left|right|full outer join]
• 子查询[关键字:all|any|some|in|exists]
• 表自关联

注意:mysql对full join 的支持不好,一般用union 来达到目的