02_SQL语句对数据库和表的增,删,改,查,约束操作

SQL

1.什么是SQL？

Structured Query Language：结构化查询语言

其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

2.SQL通用语法

1. SQL 语句可以单行或多行书写，以分号结尾。

2. 可使用空格和缩进来增强语句的可读性。

3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

4. 3 种注释

单行注释: – 注释内容 或 # 注释内容(mysql 特有)

多行注释: /* 注释 */

3. SQL分类

1) DDL(Data Definition Language)数据定义语言

用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

2) DML(Data Manipulation Language)数据操作语言

用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

3) DQL(Data Query Language)数据查询语言

用来查询数据库中表的记录(数据)。关键字：select, where 等

4) DCL(Data Control Language)数据控制语言(了解)

管理用户,授权

用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

*******************************************************

*******************************************************

DDL:操作数据库、表

一 , 操作数据库(database)：CRUD

1. C(create): 创建数据库

1. 创建数据库，判断不存在，再创建：

create database if not exists 数据库名称;

2. 创建数据库：

create database 数据库名称;

3. 创建数据库，并指定字符集

create database 数据库名称 character set 字符集名;

4. 练习：

创建db4数据库，判断是否存在，并制定字符集为gbk

create database if not exists db4 character set gbk;

2. R(Retrieve)：查询数据库show

1. 查询所有数据库的名称:

show databases;

2. 查询某个数据库的字符集:查询某个数据库的创建语句

show create database 数据库名称;

3. U(update):修改数据库alter

修改数据库的字符集

alter database 数据库名称 character set 字符集名称;

alter database mysql1 character set utf8;

4. D(delete):删除数据库drop

1. 判断数据库存在，存在再删除

drop database if exists 数据库名称;

2. 删除数据库

drop database 数据库名称;

5. 使用数据库use

1. 查询当前正在使用的数据库名称

select database();

2. 使用数据库

use 数据库名称;

*******************************************************

二 , 操作表(table)

1. C(create):创建表

1. 创建表：create

			create table 表名(
				列名1 数据类型1,
				列名2 数据类型2,
				....
				列名n 数据类型n
			);

create table 表名( name varchar(32), );

			create table student(
				id int,
				name varchar(32),
				age int ,
				score double(4,1),
				birthday date,
				insert_time timestamp
			);

注意：最后一列，不需要加逗号（,）

2. 复制已存在的表: like

create table 新表名 like 被复制的表名;

3. 数据类型：

1. int：整数类型

age int,

2. double/float:小数类型

score double(5,2)//一共5位,小数点后2位,最大值999

3. decimal:数字型

decimal(a,b),高精度,银行账目计算

a 指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。

b 指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

4. date:日期，只包含年月日

yyyy-MM-dd

5. datetime:日期，包含年月日时分秒

yyyy-MM-dd HH:mm:ss

6. timestamp:时间戳类型 包含年月日时分秒

yyyy-MM-dd HH:mm:ss

如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值

7. varchar：字符串

name varchar(20):姓名最大20个字符

zhangsan 8个字符 张三 2个字符

8. Blob

9. Clob

10. 二进制

11. 等等

2. R(Retrieve)：查询表

1. 查询某个数据库中所有的表名称

show tables;

2. 查询表结构

desc 表名;

3. U(update):修改表alter

1. 修改 表名rename to

alter table 表名 rename to 新的表名;

2. 修改 表的字符集

alter table 表名 character set 字符集名称;

3. 添加 字段

alter table 表名 add 列名 数据类型;

4. 修改 列名称 数据类型

alter table 表名 change 列名 新列别 新数据类型;

5.修改 数据类型

alter table 表名 modify 列名 新数据类型;

6. 删除 列

alter table 表名 drop 列名;

4. D(delete):删除表

drop table if exists 表名 ;

客户端图形化工具：SQLYog

*******************************************

*******************************************

DML：增,删,改表中数据

1. 添加一行表数据：insert into

insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);

注意：

1. 列名和值要一一对应。

insert into 表名(列名) value(值);

2. 如果不定义列名，则默认给所有列添加值

insert into 表名 values(值1,值2,…值n);

3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来

2. 删除表中数据：delete form

* delete from 表名 [where 条件]

如果要删除所有记录truncate

truncate table 表名; – 推荐使用，效率更高 先删除表，然后再创建一张一样的表

1. delete from 表名; – 不推荐使用。有多少条记录就会执行多少次删除操作

3. 修改数据：update 表名

update 表名 set 列名1 = 值1, 列名2 = 值2,… [where 条件];

注意：如果不加任何条件，则会将表中所有记录全部修改。

*******************************************

*******************************************

DQL：查询表中的记录

1. 基础查询select … from

1. 多个字段 ,所有字段的查询

1. select * from 表名;

2. select 字段名1,字段名2… from 表名；

2. 去除重复distinct

select distinct 字段名 form 表名

3. 计算列

1. 算术运算符计算（一般只会进行数值型的计算）

select name,mathscore,englishscore,math+ifnull(english,0) form 表名

2. ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null

表达式1：哪个字段需要判断是否为null

如果该字段为null后的替换值。

4. 起别名：

1. 给要查询数据列设别名

as：as也可以省略

2. 给表对象设别名

select

t1.name, – 员工表的姓名

t1.gender,-- 员工表的性别

t2.name – 部门表的名称

from

emp t1, --表对象别名

dept t2 --表对象别名

where

t1.dept_id = t2.id;

2. 条件查询 where

1. where子句后跟条件,括号可加

2. 运算符

1. 关系运算符

> 、< 、<= 、>= 、= 、<>

2. 逻辑运算符

1. and 或 &&

1. select * from student where age >= 20 and age <=30;

2. select * from student where age >= 20 && age <=30;

2. where between…and… 在…和…之间

select * from student where age between 20 and 30;

3. or 或 ||

4. not 或 !

4. in( 条件集合)

– 查询年龄22岁，18岁，25岁的信息

select * from student where age in (22,18,25);

select * from student where age = 22 or age = 18 or age = 25

5. like：模糊查询

1. 单个任意占位符: _

2. 多个任意占位符:%

– 查询姓马的有哪些？ like"马%"

select * from student where name like ‘马%’;

– 查询姓名第二个字是化的人like"_马%"

select * from student where name like “_化%”;

– 查询姓名是3个字的人like"___"

select * from student where name like ‘___’;

– 查询姓名中包含德的人like"%德%"

select * from student where name like ‘%德%’;

6. 判断值是否为null(is null ,is not null)

注意:null值不能使用 =null 或!=null判断

is not null --查询英语成绩为 null

select * from student where english is null;

is not null–查询英语成绩不为null

select * from student where english is not null;

3. 排序查询order by

1. order by math asc,englishScore desc

2. 排序方式：

1. asc：升序，默认的。

2. desc：降序。

select * from Studentinfo order by age;

3. 多个排序条件,优先第一条件

select * from Studentinfo order by id desc,age desc;

4. 聚合函数：列数据计算,排除null值的个数

注意：聚合函数的计算，排除值为null的记录。

解决方案：

1. 选择不包含非空的列进行计算

2. ifnull(null字段,0)函数

1. count：计算某个字段有几条数据

1. 一般选择非空的列：主键

2. count(*): 计算表中一共有几条纪录,也就是所有字段有几条纪录

select count(name) from studentinfo; # name字段有几条数据记录

select count(ifnull(english,0)) from stuinfo

2. max：计算最大值

3. min：计算最小值

4. sum：计算和

5. avg：计算平均值

5. 分组查询:group by 分组字段

1.语法：有几种类别显示几组,分组字段首尾统一

select 分组字段A,分组查询的字段…group by 分组字段A;

1.-- 按性别分组。分别查询男、女同学的平均分

select sex , avg(math) from student group by sex;

2.-- 按班级分组。分别查询男、女同学的平均分,人数

select class, avg(math),count(id) from student group by sex;

2. 注意：面试题:where 和 having 的区别？

1. where 在分组前限定，having在分组后限定

2. havinghou后可跟聚合函数

select sex , avg(math),count(id) from student where math > 70 group by sex;

select sex , avg(math),count(id) from student where math > 70 group by sex having count(id) > 2;

6. 分页查询 limit 分页限定

1. 语法：limit 开始的索引,每页查询的条数;

2. – 每页显示3条记录

select * from student limit 0,3; – 第1页

select * from student limit 3,3; – 第2页

select * from student limit 6,3; – 第3页

3. 计算公式：开始的索引 = （当前的页码(第几页) - 1） * 每页显示的条数

4. limit关键字是一个MySQL"方言",只在mysql中用

7.约束(字段数据值约束)

概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：

1. 主键约束：primary key

2. 非空约束：not null

3. 唯一约束：unique

4. 默认值约束:default

5. 外键约束：foreign key

非空约束：值不能为null

1. 创建表 时 , 添加约束

		create table stu(
			id int,

name varchar(20) not null – name为非空

		);

2. 创建表 后，modify添加或删除约束

alter table stu modify name varchar(20) not null;

alter table stu modify name varchar(20);

唯一约束：unique，非null值不能重复

1. 创建表 时，添加唯一约束

注意: mysql中，唯一约束限定的列的值可以有多个null

		create table stu(
			id int,

number varchar(20) unique

		);

2. 在创建表 后，添加唯一约束,modify

alter table stu modify phone_number varchar(20) unique;

3. 删除唯一约束,drop index

alter table stu drop index phone_number;

默认值约束:default “值”

1. 创建表时添加默认值约束

sex char(1) default’男’

2. 删除约束

alter table studentinfo modify gender varchar(1);

主键约束：primary key(主键组)

1. 注意：

1. 含义：非空且唯一

2. 一张表只有一个主键

3. 主键就是表中记录的唯一标识

2. 在创建表时，添加主键约束

		create table stu(

id int primary key, – 给id添加主键约束

			name varchar(20)
		);

3. 创建完表后，添加主键modify

alter table stu modify id int primary key;

4. 删除主键drop primary key

– 错误 alter table stu modify id int ;

alter table stu drop primary key;

5. 自动增长：auto_increment

1. 概念:数值类型 列值的自动增长

2. 在创建表时，添加主键约束，并且完成主键自增长

		create table stu(
		-- 给id添加主键约束

id int primary key auto_increment ,

			name varchar(20)
		);

3. 删除自动增长,modify

alter table stu modify id int;

4. 添加自动增长,modify … auto_increment;

alter table stu modify id int AUTO_inCREMENT;

外键约束：foreign key

1.概念:让表于表产生关系,保证数据的正确性。

2. 在创建表时，可以添加外键

		* 语法：
			create table 表名(
				....
				外键列

constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

			);

3. 删除外键drop foreign key 外键名

alter table 表名 drop foreign key 外键名称;

4. 创建表之后，添加外键add

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

5. 父子表级联操作

1. 添加级联操作语法

alter table 表名 add constraint 外键名称

foreign key (外键字段名) references 主表名(主表列名)

on update cascade on delete cascade ;

2. 分类：

1. 级联更新：on update cascade

2. 级联删除：on delete cascade

3. 外键定制三种约束模式

1. restrict和no action:父表更新或者删除时，子表有匹配记录，则禁止父表的更新和删除。默认选项

2. cascade：父表更新或者删除时，子表有匹配记录，则父表操作成功，同时更新或删除子表匹配项

3. set null：父表更新或者删除时，子表有匹配记录，则父表操作成功，同时将子表的匹配项设置为null值(前提能设置为null)

alter table t2 add constraint c_fk foreign key t_id references t1(id) [on {delete | update} { restrict | no action | cascade | set null}];

主键外键总结

概念

1.主键是表记录的非空唯一标识,外键为了表与表建立关系,保持数据正确性,

操作

1. 外键表的外键字段,添加数据必须来自于主表主键字段的值中

2. 主键表主键字段,只能更新或删除,外键表外键字段中没有的 数据

3. 清空或删除主表前,要先删除外键表的外键

栲霓杞蛙的总结