MySQL(关系型数据库)常用语句练习_用Navicat Premium基本操作_DDL(数据定义语言)、DML(数据操作语言)、约束

SQL注释

快捷键：CTRL + /
-- 单行注释，-- 后面一定要加一个空格			代码注释
# 单行注释，#后面可加可不加空格				说明性文字注释

/*
多行注释
*/

MySQL默认数据有四个：

1. information_schema
2. mysql
3. performance_schema
4. sys

MySQL基本操作_DDL——数据定义语言

Navicat Premium 放大 新建查询表 里的 文字	CTRL + 鼠标滚轮

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

-- 1、DDL操作之-数据库操作
-- 1.1查看所有数据库
show databases;
-- 1.2创建 判断 数据库MyTestdb 是否存在，若不存在，则创建，反之忽略
create database if not exists MyTestdb;
-- 1.3切换数据库
use MyTestdb;
-- 1.4删除 数据库MyTestdb 是否存在，若存在，则删除
drop database if exists MyTestdb;
-- 1.5修改数据库MyTestdb 编码 为 utf8(默认编码)
alter database MyTestdb character set utf8;

-- 2、对表结构常用操作-创建表
-- 2.1选择数据库MyTestdb
use MyTestdb;
-- 2.2创建表格式
create table if not exists student(
	sid int,
	name varchar(20),
	gender varchar(10),
	age int,
	birth date,
	address varchar(20),
	score double
);

-- 3、对表结构的常用操作-其它操作
-- 3.1查看当前数据库所有表
show tables;
-- 3.2查看指定student表创建语句
show create table student;
-- 3.3查看student表结构
desc student;
-- 3.4删除student表
drop table student;

-- 4、对表结构常用操作-修改表结构
use MyTestdb;
-- 4.1添加列：alter table 表名 add 列名 类型(长度) [约束];
	-- 为students表添加一个新的字段为：系别 dept 类型为 varchar(20)
	alter table student add dept varchar(20); 
-- 4.2修改列名和类型：alter table 表名 change 旧列名 新列名 类型(长度) [约束]
	-- 为student表的dept字段更换为department varchar(30);
	alter table student change dept department varchar(30);
-- 4.3删除列：alter table 表名 drop 列名;
	-- 删除student表中department这列
	alter table student drop department;	
-- 4.4修改表名：rename table 旧表名 to 新表名
	-- 将student表的名字改为stu
	rename table student to stu;

MySQL基本操作_DML——数据操作语言

用来对数据库中表的数据记录进行更新

关键字：

• insert 插入
• delete 删除
• update 更新

-- DML操作
-- 1.数据插入
-- 格式1：insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
	insert into student(sid,name,gender,age,birth,address,score)
			     values(1001,'张三','男',18,'2001-12-23','北京',85.5);
	 
	insert into student(sid,name,gender,age,birth,address,score)
				 values(1002,'李四','男',19,'2002-09-13','上海',78.5),
					   (1003,'王五','女',17,'2003-11-29','深圳',66.5);
										 
	insert into student(sid) values(1004);
	insert into student(sid,name) values(1005,'赵六');
		
-- 格式2：insert into 表 values (值1,值2,值3...);     //向表中插入所有列
	insert into student values(1006,'张华','女',21,'1999-01-08','广州',79);
	
	insert into student values(1006,'钱博','男',21,'2005-05-07','北京',98),
							  (1007,'李方','男',24,'1998-05-04','武汉',89);

-- 2.数据修改
-- 格式1：update 表名 set 字段名=值,字段名=值...;
-- 格式2：update 表名 set 字段名=值,字段名=值... where 条件;

-- 2.1将所有学生的地址修改为重庆 
	 update student set address='重庆';	 
-- 2.2讲id为1004的学生的地址修改为北京
	 update student set address='北京' where sid=1004;
	 
	 update student set address='上海' where sid>1004;
-- 2.3讲id为1005的学生的地址修改为北京，成绩修成绩修改为100
	 update student set address='北京' ,score=100 where sid=1005;

-- 3.删除数据
-- 格式：delete from 表名 [where 条件];
-- truncate table  表名 或者 truncate 表名
-- 注意：delete 和 truncate 原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表；

-- 3.1删除sid为1004的学生数据
delete from student where sid=1004;
-- 3.2删除表所有数据
delete from student;
-- 3.3清空表数据
truncate table student;

MySQL约束

作用：

表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

分类——七种：

主键约束(primary key) PK

1. 主键约束的列，非空且唯一，且值不能重复
2. 一个表只有一个主键(包含联合主键)
3. 主键可以唯一标识表中的一行数据，提高查询效率

-- 1.1创建单列主键约束——主键约束的列，非空且唯一
-- 方式1
/*
在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。 
在定义字段的同时指定主键，语法格式如下：
create table 表名( 
... 
<字段名> <数据类型> primary key 
... 
)
*/
use MyTestdb;
create table emp1(
eid int primary key,
name varchar(20),
deptId int,
salary double
);
-- 方式2
/*
在定义字段之后再指定主键，语法格式如下：
create table 表名(
   ...
   [constraint <约束名>] primary key [字段名]
);
*/
create table emp2(
eid int,
name varchar(20),
deptId int,
salary double,
constraint pk1 primary key(eid)  --constraint pk1可以省略
);
-- 1.2联合主键——中各列不能为空
-- 主键是由一张表中多个字段组成
-- primary key (字段1,字段2,……,字段n)
create table emp3(
name varchar(20),
deptId int,
salary double,
primary key(name,deptId)
);

-- 2.1修改表结构添加主键
/*
create table 表名(
   ...
);
alter table <表名> add primary key（字段列表);
*/
create table emp4(
eid int,
name varchar(20),
deptId int,
salary double
);
alter table emp4 add primary key(eid);
-- 2.2添加多列主键
create table emp5(
eid int,
name varchar(20),
deptId int,
salary double
);
alter table emp5 add primary key(name ,deptId);
-- 3.1删除单列主键
alter table emp1 drop primary key;
-- 3.2删除多列主键
alter tabLe emp5 drop primary key;

自增长约束(auto_increment)

1. 和主键一起使用
2. 默认从1开始增长
3. 每次自增长值加1
4. delete删除数据之后，自增长的值会被保留，从上一个断点继续增长
5. truncate是将表删除，重新创建新表，所以清空数据之后，自增长从1开始

-- 自增长正约束auto_increment
create table t_user1(
id int primary key auto_increment,
name varchar(20)
);

insert into t_user1 values(null,'张三');
insert into t_user1(name) values('李四');

delete from t_user1;	-- delete删除数据之后，自增长还是在最后一个值基础上加1

truncate t_user1;	-- truncate删除之后，自增长从1开始

-- 指定自增长的初始值
-- 方式一：创建表时指定
create table t_user2(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;

insert into t_user2 values(null,'张三');
-- 方式二：创建表之后指定
create table t_user3(
id int primary key auto_increment,
name varchar(20)
);
alter table t_user3 auto_increment=200;

insert into t_user3 values(null,'张三');

非空约束(not null)

1. 添加非空约束的列不能为空
2. 真正的空是NULL,不是’NULL’,也不是’ ’

/*MySQL非空约束(NOT NULL)指字段的值不能为空。
对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

格式：
方式1 <字段名><数据类型> not null;
方式2 alter table 表名 modify 字段 类型 not null;
*/
use MyTestdb;
-- 1.创建非空约束-方式1 创建表时指定
create table t_user6(
id int,
name varchar(20) not null,-- 指定非空约束
address varchar(20) not null-- 指定非空约束
);
insert into t_user6(id) values(1001);-- 不可以
-- 2.创建非空约束-方式2 创建表之后指定
create table t_user7(
id int,
name varchar(20),
address varchar(20)
);
alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;
desc t_user7;-- 查看表结构
-- 3.删除非空约束
-- alter table 表名 modify 字段 类型
alter table t_user7 modify name varchar(20);
alter table t_user7 modify address varchar(20);

唯一性约束(unique)

1. 添加唯一约束之后，该列值不能重复
2. 虽然不能重复，但是添加唯一约束的列可以为NULL, 因为NULL不能等于NULL

/*唯一约束(Unique Key)指所有记录中字段的值不能重复出现。
例如，为id字段加上唯一性约束后，每条记录的id值都是唯一的，不能重复出现。

注：在MySQL约束中null和任何值都不相同，甚至和自己都不同
*/
use MyTestdb;-- 指定Mytestdb数据库
-- 1.添加唯一约束-方式1-创建表时指定
-- 格式：<字段名> <数据类型> unique;
create table t_user8(
id int,
name varchar(20),
phone_number varchar(20) unique-- 指定唯一约束
);

insert into t_user8 values(1001,'张三',138);
insert into t_user8 values(1002,'张三1',138);
-- 2.添加唯一约束-方式2-创建表之后指定
-- 格式：alter table 表名 add constraint 约束名 unique(列);
create table t_user9(
id int,
name varchar(20),
phone_number varchar(20)
);
alter table t_user9 add constraint unique_pn unique(phone_number);

insert into t_user9 values(1001,'张三',138);
insert into t_user9 values(1002,'张三1',138);
-- 3.删除唯一约束  默认就是字段名
-- 格式：alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_pn;

默认约束(default)

1. 添加约束的列如果没有指定值，则默认值

-- 默认约束
use MyTestdb;
-- 方式1-创建表时指定
-- 格式：<字段名> <数据类型> default <默认值>;
create table t_user10(
id int,
name varchar(20),
address varchar(20) default '北京' -- 指定默认约束
);

insert into t_user10(id,name) values(1001,'张三');

-- 方式2-创建表之后指定
-- 格式：alter table 表名 modify 列名 类型 default 默认值;
create table t_user11(
id int,
name varchar(20),
address varchar(20)
);
alter table t_user11 modify address varchar(20) default '深圳';

insert into t_user11(id,name) values(1001,'张三');

-- 删除默认约束
-- 格式：alter table <表名> change column <字段名> <类型> default null;
alter table t_user11 modify address varchar(20) default null;

insert into t_user11(id,name) values(1002,'张三1');

零填充约束(zerofill)——前面自动补0，一共十位数

1. 当给数值类型设置该约束，则左边不够指定的整形长度时，则自动补充0
2. 默认零填充约束的数字int(10)
3. 指定零填充约束的列为unsigned(无符号)类型

-- 1.添加约束
create table t_user12(
id int zerofill,-- 零填充约束
name varchar(20)
);

insert into t_user12 values(123,'张三');

insert into t_user12 values(1,'李四');

-- 2.删除约束
alter table t_user12 modify id int;

外键约束(foreign key) FK

经常与主键约束一起使用，主键所在的表就是主表（父表），外键所在的表就是从表（子表）。