SQL基本语句使用总结

时间: 2023-07-11 admin 互联网

SQL基本语句使用总结

SQL基本语句使用总结

1.什么是SQL
Structured Query Language:结构化查询语言
其实就是订一块操作所有关系型数据的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”

2.SQL通用语法:
1.SQL语句可以单行或者多行书写,以分号结尾
2.可使用空格和缩进来增强语言的可读性
3.MYSQL数据库的SQL语句不区分大小写,关键字建议大写
4.有3种注释
(1)单行注释:(–空格)注释内容
(2)(#空格)注释内容(MySQL特有)
(3)多行注释:/注释内容/

3.SQL分类
1.DDL(data definition language)数据库定义语言
用来操作数据库对象:数据库,表,列等。
关键字:cerate,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:操作数据库、表
1.操作数据库:CRUD

(1)C(create):创建
1.创建数据库:
create database数据库名称;

2.创建数据库,先判断数据库是否存在,不存在再创建
create database if not exists 数据库名称;

3.创建数据库,并指定字符集
create database 数据库名称 character set 字符集名;

4.如创建db4数据库,先判断还是否已存在,并指定字符集为gbk
create database if not exists db4 character set gbk;

(2)R(retrieve)查询
1.查询所有数据库的名称
show databases;
2.查询某个数据库字符集:查询某个数据库的创建语句
show create database 数据库名称;

(3)U(update)修改
1.修改数据库字符集
Alter database 数据库名称 character set 字符集名称;

(4)D(delete)删除
1.删除数据库
drop database;

2.判断数据库是否存在,存在在删除
drop database if exists 数据库名称;

(5)使用数据库
1.查询当前正在使用的数据库名称
select database();

2.使用数据库
use 数据库名称;

2.操作表:CRUD
(1)C(create):创建

1.语法:

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

注意:最后一列,不需要加逗号
数据库类型
1.int:整数类型
2.double:小数类型
3.date:日期,只包含年月日,yyyy-MM-dd
4.datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5.timestamp:时间错类型 包含年月日时分秒yyyy-MM-dd HH:mm:ss
如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值

6.varchar:字符串
name varchar(20):姓名最大20个字符
Zhangsan 8个字符 张三 2个字符

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

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

(2)R(retrieve)查询
1.查询某个数据库中所有表的名称
show tables;
2.查询表结构
desc 表名;

(3)U(update)修改
1.修改表名
alter table 表名 rename to 新表名;

2.修改表的字符集
alter table 表名character set 字符集名称;
3.添加一列
alter table 表名add 列名 数据类型;

4.修改列名称、类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;

5.删除列
alter table 表名drop 列名;
(4)D(delete)删除
drop table 表名;
drop table if exists 表名;

##DML:增删改表中数据

1.添加数据:
语法:

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

注意:
(1)列名和值要一一对应
(2)如果表名后,不定义列名,则默认给所有列添加值
insert into表名 values(值1,值2,值3,…值n)
(3)除了数字类型,其他类型需要使用引号(单双都可以)引起来

2.删除数据
语法:
delete from 表名 where 条件
注意:
1.不加条件,则删除表中全部内容
2.如果要删除所有记录
(1)delete from 表名;不推荐使用
(2)TRUNCATE TABLE 表名;先删除表,然后再创建一张一样的表。推荐使用,效率更高

3.修改数据:
语法:
update 表名 set 列名1=值1,列名2=值2… where 条件
注意:
1.如果不加任何条件,则会将表中所有记录全部修改

##DQL:查询表中的记录

select * from 表名;
1.语法

  select字段列表from表名列表where
条件列表group by分组字段having分组之后条件order by排序limit分页限定

2.基础查询

(1)多个字段查询
select 字段名1,字段名2…from表名;
注意:如果查询所有字段,可以使用*来代替字段列表

(2)去除重复:
distinct

(3)计算列
一般可以使用四则运算计算一些列的值(一般只进行数值型计算)
ifnull(表达式1,表达式2):null参与的运算,计算结果都为null
表达式1:哪个字段需要判断是否为null
如果该字段为null后的替换值

(4)起别名:
as:as也可以省略

3.条件查询
(1)where子句后跟条件
(2)运算符

< <= >= = <>(不等于) !=(不等于)
BETWEEN…AND
IN(集合)
LIKE
IS NULL
and 或&&
or 或||
not或!

相关操作

create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp);
INSERT INTO student (id,name,age,score) VALUES (1,'张三',29,69.1);INSERT INTO student (id,name,age,score) VALUES (2,'李四',30,47.5);INSERT INTO student (id,name,age,score) VALUES (3,'王五',32,90.1);INSERT INTO student (id,name,age,score) VALUES (4,'麻溜',36,59.1);INSERT INTO student (id,name,age) VALUES (5,'麻溜',36);select * from student;DELETE FROM student WHERE id=18;TRUNCATE TABLE student;UPDATE student set age=17 WHERE id=3;
 -- 查询姓名和年龄SELECTname, -- 姓名age   -- 年龄fromstudent; --学生表-- 查询年龄大于30SELECT * from student WHERE age>30;-- 查询年龄等于30SELECT * from student WHERE age=30;-- 查询年龄不等于30SELECT * from student WHERE age<>30;SELECT * from student WHERE age!=30;-- 查询年龄大于18,小于35SELECT * from student WHERE age>=18 && age<=35;SELECT * from student WHERE age>=18 AND age<=35;SELECT * from student WHERE age BETWEEN 18 AND 35;-- 查询年龄17,29,36SELECT * FROM student WHERE age=17 or age=29 or age=36;SELECT * FROM student WHERE age IN(17,29,36);-- 查询得分为nullSELECT * FROM student WHERE score is null;-- 查询得分不为nullSELECT * FROM student WHERE score is NOT null;
CREATE table stu2(id int, -- 编号name varchar(20), -- 姓名age int,   -- 年龄sex varchar(5),  -- 性别address varchar(100),  -- 地址math int,   -- 数学english int  -- 英语);INSERT INTO stu2(id,name,age,sex,address,math,english) VALUE (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'男','深圳',98,99),(3,'马景涛',55,'男','香港',56,77),(4,'倪妮',25,'女','北京',99,68),(5,'刘德华',58,'男','香港',99,9),(6,'玛德',24,'女','香港',99,99),(7,'柳岩',38,'女','湖南',79,82),(8,'田娜娜',18,'女','南京',100,100);
select * from stu2;-- 查询所有姓马的
SELECT * FROM stu2 WHERE name LIKE '马%'-- 查询姓名第二个字是化的人
SELECT * FROM stu2 WHERE name LIKE '_化%'-- 查询姓名第三个字的人
SELECT * FROM stu2 WHERE name LIKE '___'-- 查询姓名中包含马的人
SELECT * FROM stu2 WHERE name LIKE '%马%'

#DQL:
1.查询语句
(1)排序查询
语法:order by 子句

order by 排序字段1,排序方式1, 排序字段2,排序方式2…

排序方式:
ASC:升序,默认
DESC:降序
注意:如果有多个排序条件,则当前面的田间益阳市,会判断第二个条件

– 默认升序排序方式

SELECT * from stu2 ORDER BY math;  

– 降序排序方式

 SELECT * from stu2 ORDER BY math DESC;  

– 多字段排序,先排数学,数学成绩相同按英语排序

SELECT * from stu2 ORDER BY math,english; 

(2)聚合函数:将一列数据作为一个整体,进行纵向计算

1.count:计算个数
(1)一般选择非空的列:主键
(2)count(*)
2.max:计算最大值
3.min:计算最小值
4.sum:计算和
5.avg:计算平均值

注意:聚合函数计算,排除null
解决·方案:
1.选择不包含非空的列进行计算
2.IFNULL函数

-- 统计数量
SELECT count(name) FROM stu2;-- 聚合计算是排除Null的,所以只有8个
SELECT count(english) FROM stu2;-- 最大值,最小值
SELECT max(math) FROM stu2;
SELECT min(math) FROM stu2;-- 求和
SELECT sum(math) FROM stu2;-- 平均数
SELECT avg(math) FROM stu2;

(3)分组查询
1.语法:group by 分组字段

2.注意:
1.分组之后查询的字段:分组字段、聚合函数

2.where和having的区别?
(1)where在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
(2)where后不可以跟聚合函数,having可以进行聚合函数的判断

-- 按照性别分组。分别查询男、女同学平均分
SELECT sex , avg(math) from stu2 GROUP BY sex;-- 按照性别分组。分别查询男、女同学平均分,人数
SELECT sex , avg(math),COUNT(id) from stu2 GROUP BY sex;-- 按照性别分组。分别查询男、女同学平均分,人数,要求分数大于70分,才参与分组
SELECT sex , avg(math),COUNT(id) from stu2 WHERE math>70 GROUP BY sex;-- 按照性别分组。分别查询男、女同学平均分,人数,要求分数大于70分,才参与分组,分组后,人数大于2人
SELECT sex , avg(math),COUNT(id) from stu2 WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;

(4)分页查询

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

2.公式:开始的索引=(当前的页码 - 1)*每页显示的条数

- 每页显示三条记录,(0,4)代表从0开始,4条(4,4)代表从4开始,4条
SELECT * FROM stu2 LIMIT 0,4;-- 第一页SELECT * FROM stu2 LIMIT 4,4;-- 第二页SELECT * FROM stu2 LIMIT 8,4;-- 第三(一共9条,余下的只够显示一条)
3.Limit是MySQL特有分页语言

2.约束
概念:对表中的数据进行限定,保证数据的正确性、有效性、完整性。
分类:
(1)主键约束:primary key
(2)非空约束:not null ,值不能为空
(3)唯一约束:unique,值不能重复
(4)外键约束:foreign key
非空约束:

1.创建表时添加约束create table stu3(id int,
name varchar(20) not null
);
2.创建完表之后,添加非空约束
ALTER TABLE stu3 MODIFY NAME VARCHAR(20) not null;
3.删除name非空约束
ALTER TABLE stu3 MODIFY NAME VARCHAR(20);

唯一约束
1.创建表时,给列添加唯一约束,列中某一行值是唯一的,不能有其他行和他相同他值和

create table stu4(id int,
phone_number varchar(20) unique  -- 添加唯一约束
);

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

2.删除唯一约束

  alter table stu4 drop index phone_number;

3.在创建表后,添加唯一约束

alter table stu4 modefy phone_number varchar(20) unique;

主键约束 primary key
含义:非空且唯一(例如idb标号不能为空,标号不能重复)
一张表只能有一个字段为主键
主键就是表中记录的唯一标识,例如id

在创建表时,添加主键约束(含有主键约束后,不能进行添加和删除数据的操作,主键删除后才能进行)

create table stu5(id int primary key, -- 给id添加主键约束name varchar(20)
);
删除主键
alter table stu5 drop primary key;
创建完表后,添加主键
alter table stu5 modify id int primary key;

自动增长(一般和主键在一块用)
概念:如果每一列是数值类型的,使用auto_increment 可以来完成值的自动增长
注意:自动增长只在上一条基础上进行增长,如果是id是1-2-3-9则下一条是10

1.在创建主键时,添加主键约束,并且完成主键自增长

create table stu5(id int primary key auto_increment,name varchar(20)
);-- 删除自动增长
ALTER TABLE stu5 MODIFY id int;-- 添加自动增长
alter TABLE stu MODIFY id int auto_increment;

外键约束: foreign key,让表与表产生关系,从而保证数据的正确性

1.在创建表时,可以添加外键
create table表名(...外键列constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

2.删除外键

alter table 表名 drop foreign key 外键名称

3.创建表之后,添加外键

alter table 表名 add constraint 外键名称 drop foreign ke(外键字段名称)references 主表名称(主表列名称)。

4.级联操作
1.添加级联操作

语法:alter table 表名 add constraint 外键名称

foreign key (外键字段名称) references 主表名称(主表列名称)on update cascade on delete cascade

2.分类
级联更新:on update cascade
级联删除:on delete cascade

数据库设计

1.多表之间的关系
(1)一对一:一个人只有一个身份证,一个身份证只能对应一个人
(2)一对多(多对一):一个部门有多个员工,一个员工只能对应一个部门
(3)多对多:一个学生可以选择很多门课,一个课程可以被很多学生选择
实现关系
(1)一对多(多对一):在多的一方建立外键,指向一的一方主键
(2)多对多:需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。
(3)在任意一方添加唯一外键指向另一方主键

2.设计范式
概念:设计数据库是,需要遵循的规则。要遵循后面范式要求,前面的要求要先满足

分类:
第一范式(1nf):每一列都是不可分割的原子数据项
第二范式(2nf):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF的基础上消除非主属性对主码的部分函数依赖)

几个概念:
1.函数依赖:A–>B,如果通过A属性(属性组的值),可以确定唯一B属性的值

2.完全函数依赖:A–>B,如果通过A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值

3.部分函数依赖:如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某一些值即可

4.传递函数依赖:A–>B,B–>C

5.码:如果在一张表中,一个属性或属性组被其他所有属性所完全依赖,则称这个属性(属性值)为该表的吗
例如:该表中码为(学号,所有课程)
主属性:码属性组中的所有属性
非主属性:除过码属性组的属性

第三范式(3nf):在2nf基础上,任何非主属性不依赖于其他非主属性(在2nf基础上消除传递依赖)

3.数据库的备份和还原

#多表查询

查询语法:select 列名列表from表名列表
where...

创建部门表

create TABLE dept(id INT PRIMARY KEY auto_increment,NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

创建员工表

create table emp(id INT PRIMARY KEY auto_increment,NAME VARCHAR(10),gender CHAR(1),  -- 性别salary DOUBLE,   -- 工资join_date date,  -- 入职时间dept_id INT,FOREIGN KEY (dept_id) REFERENCES dept(id)  -- 外键,关联部门表(部门表主键)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

笛卡尔积
有两个集合A,B,取这两个集合的所有组成情况
要完成多表查询,需要消除无用的数据(按表的用途判断消除那些)

多表查询分类
1.内连接查询
隐式内连接:使用where条件消除无用数据
– 查询所有员工信息的对应部门信息

SELECT * FROM emp,dept WHERE emp.dept_id=dept.id;

– 查询员工表的名称,性别。部门表的名称

SELECT emp.name,emp.gender,dept.name  from emp,dept WHERE  emp.dept_id=dept.id;
-- 可以改成这种形式查询
SELECTt1.NAME, -- 员工表姓名
t1.gender, -- 员工表性别
t2.name  -- 部门表名称
FROMemp t1,
dept t2
WHEREt1.dept_id=t2.id;

显示内连接

语法: select 字段列表 from 表名1  inner(可以省略)join 表名2  on  条件
SELECT * FROM emp inner join dept on emp.dept_id=dept.id;
SELECT * FROM emp  join dept on emp.dept_id=dept.id;

注意事项:
1.从哪些表中查询数据
2.条件是什么
3.查询哪些字段

2.外连接查询
1.左外连接
语法:select 字段列表 from 表1 left outer(可省略) join 表2 on 条件
查询的是左表所有数据以及交集的部分
2.右外连接
语法:select 字段列表 from 表1 right outer(可省略) join 表2 on 条件

3.子查询
概念:查询中嵌套查询

– 查询工资最高的员工信息

-- 1.查询最高的工资是多少
SELECT MAX(salary) FROM emp;
-- 2查询员工信息,并且工资等于9000的
SELECT * FROM emp WHERE emp.salary=9000;
-- 一条sql语句完成上述两个步骤,子查询
SELECT * FROM emp WHERE emp.salary=(SELECT  MAX(salary) FROM emp);

子查询不同情况:

1.结果是单行单列的:

  子查询结果可以作为条件使用
-- 查询工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary<(SELECT  AVG(salary) FROM emp);

2结果是多行单列的

子查询结果可以作为条件,使用运算符in来判断

-- 查询财务部和市场部所有员工信息
SELECT id FROM dept WHERE NAME='财务部',OR NAME='市场部';
SELECT * FROM emp WHERE dept_id=3 OR dept_id=2;
-- 一条语句搞定
SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME='财务部',OR NAME='市场部');

3.结果多行多列的
子查询结果可以作为一张虚拟表参与查询

-- 查询员工入职日期是2011-11-11之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date>'2011-11-11')t2 WHERE ti.id=t2.dept_id;-- 普通内连接
SELECT * FROM emp t1,dept t2  WHERE t1.dept_id=t2.id AND t1.join_date>'2011-11-11'

#事务
1.事物基本介绍
1.概念:如果一个包含多个步骤的业务操作,被事物管理,那么这些操作要么同时成功,要么同时失败
2.操作:
开启事务:start transaction
回滚:rollback
提交: commit

3.例子:

CREATE TABLE account(id INT PRIMARY KEY auto_increment,NAME VARCHAR(10),balance DOUBLE
);-- 添加数据
INSERT INTO account(NAME,balance) VALUES('张三',1000),('李四',1000);SELECT * FROM account;
UPDATE account SET balance =1000;
-- 张三给李四转账500元-- 开启实物
START TRANSACTION;
-- 张三账户减500
UPDATE account SET balance =balance-500 WHERE name='张三';-- 李四账户加500
UPDATE account SET balance =balance+500 WHERE name='李四';-- 发现执行没问题,提交事务
COMMIT;-- 发现出问题,回滚事务
ROLLBACK;

4.MySQL数据库中事务默认自动提交
修改事务的默认提交方式:
查看事务的默认提交方式:select @@autocommit; 1代表自动提交,0代表手动提交
修改默认提交方式: set @@autocommit=0;

事务四大特征:(常见面试题)
1.原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
2.持久性:当事务提交或回滚后,数据库会持久化的保存数据
3.隔离性:多个事务之间。相互独立
4.一致性:事务操作前后,数据总量不变。

事务隔离级别(了解)
概念:多个事务之间隔离是相互独立的。但如果多个事务操作统一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
存在的问题:
1.脏读:一个事务,带到另一个事务中没有提交的数据
2.不可重复读:在同一个事务中,两次读取到的数据不一样
3.幻读:一个事务(同一个read view)在前后两次查询同一范围的时候,后一次查询看到了前一次查询没有看到的行。

隔离级别:
1.read uncommitted:读未提交
产生的问题:脏读,不可重复读,幻读
2.read committed:读已提交(oracle默认级别)
产生问题:不可重复读,幻读
3.repeatable read:可重复读(MySQL默认级别)
产生的问题:幻读
3.serializable:串行化
可以解决所有问题

注意:隔离级别从小到大安全性越来越高,但是效率越来越低
数据库查询隔离级别:
select @@tx_isolation
数据库设置安全级别:
set global transaction isolation level 级别字符串

##DCL:管理用户,授权
1.管理用户
(1)添加用户
(2)删除用户
(3)修改用户密码
MySQL中忘记root用户密码
1.cmd—> net stop mysql 停止MySQL服务(需要管理员身份运行该cmd)
2.使用无验证方式启动MySQL服务:MySQL—>skip-grant-tables
3.打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
4.use mysql;
5.update user set password=password(‘你的新密码’) where user=’toot’;
6.关闭两个窗口
7.打开任务管理器,手动结束MySQL.exe进程
8.启动MySQL服务
9.使用新密码登录

(4)查询用户

- 切换到MySQL数据库
USE mysql;
-- 查询user表
select * from USER;-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED by '密码';CREATE USER 'zhangsan'@'localhost' IDENTIFIED by '123';
CREATE USER 'lisi'@'%' IDENTIFIED by '123';-- 删除用户
DROP USER '用户名'@'主机名';
DROP USER 'zhangsan'@'localhost';
DROP USER 'lisi'@'localhost';-- 修改lisi用户密码为abc
UPDATE USER SET PASSWORD=PASSWORD('新密码')WHERE USER='用户名';
UPDATE USER SET PASSWORD=PASSWORD('abc')WHERE USER='lisi';SET PASSWORD FOR'用户名'@'主机名'=PASSWORD('新密码');
SET PASSWORD FOR'root'@'localhost'=PASSWORD('123');

2.管理权限
(1)查询权限
– 查询权限
show grants for ‘用户名’@’主机名’;
show grants for ‘lisi’@’%’;
(2)授予权限
– 授予权限
grant权限列表 on 数据库名.表名 to ‘用户名’@’主机名’;
– 给张三用户授予所有权限,在任意数据库任意表上
grant all on . to ‘zhangsan’@’localhost’;
(3)撤销权限
– 撤销权限
revoke 权限列表 on 数据库名称.表名 from ‘用户名’@’主机名’;