语法"/>
小码笔记18:SQL数据库语法
小码笔记:SQL数据库笔记
- 一、MySQL数据库安装、卸载
- 1.安装
- 2.卸载
- 3.配置
- 二、SQL
- 1.SQL通用语法
- 2.SQL分类
- 三、DDL操作数据库、表
- 1.操作数据库:CRUD
- 2.操作表:CRUD
- 四、DML:增删改表中数据
- 1.添加数据:(insert)
- 2.删除数据:
- 3.修改数据:
- 五、DQL:查询表中的记录(重点!)
- 1.语法
- 2.基础查询
- 3.条件查询
- 4.排序查询
- 5.聚合函数
- 6.分组查询
- 5.分页查询
- 六、约束
- 1. 分类:
- 2.非空约束:not null
- 3.唯一约束:unique
- 4.主键约束:primary key
- 5.外键约束:foreign key
- 七、多表查询
- 1.内连接查询:
- 2. 外链接查询:
- 3.子查询:
- 八、DCL:管理用户,授权
- 1.管理用户
- 2.权限管理
- 九、数据库设计的范式
- 1.表间关系:
- 2.数据库的三大范式
- 十、事务
- 1.操作:
- 2.例子:转账
- 3.MySQL事务默认自动提交- 事务提交的两种方式:
- 4.事务的四大特征
- 5.事务的隔离级别(了解)
数据库 (DataBase),简称 : DB
- 用于存储和管理数据的仓库
- 本质上是一个文件系统,还是以文件的方式存在服务器的电脑上的。
- 所有的关系型数据库都可以使用通用的 SQL 语句进行管理 DBMS
DataBase Management System
- 常见的关系型数据库:
- MySQL:开源免费的数据库,小型的数据库,已经被 Oracle 收购了。
- Oracle:收费的大型数据库,Oracle 公司的产品
- SQL Server:MicroSoft 公司收费的中型的数据库。C#、 等语言常使用
一、MySQL数据库安装、卸载
公司收购了 MySQL,而 Sun 公司又被 Oracle 收购
1.安装
- 参见在下是小白的博客 MySQL安装(详细,适合小白)
- 想安装5.5版本的MySQL也可以私聊
2.卸载
-
去mysql的安装目录找到my.ini文件
- 复制
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
- 复制
-
卸载MySQL
-
删除
C:/ProgramData
目录下的MySQL文件夹。
3.配置
-
MySQL服务启动
1.cmd--> services.msc 打开服务的窗口2.找到【服务】-【MySQL】启动方式设置为手动3.使用管理员打开cmd net start mysql : 启动mysql的服务 net stop mysql:关闭mysql服务
-
MySQL登录
1.mysql -uroot -p密码 2.mysql -hip -uroot -p连接目标的密码 3.mysql --host=ip --user=root --password=连接目标的密码
-
MySQL退出
1.exit2.quit
-
MySQL数据库的还原
1.备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径 2.还原:1. 登录数据库2. 创建数据库3. 使用数据库4. 执行文件。source 文件路径
二、SQL
SQL( Structured Query Language
):结构化查询语言
定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”
1.SQL通用语法
-
SQL 语句可以单行或多行书写,以分号结尾。
-
可使用空格和缩进来增强语句的可读性。
-
MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写。
-
3 种注释
-
单行注释: – 注释内容 或 # 注释内容(mysql 特有)
-
多行注释: /* 注释 */
-
2.SQL分类
- DDL(
Data Definition Language
)数据定义语言:定义数据库,表,列 - DML(
Data Manipulation Language
)数据操作语言:表数据增删改 - DQL(
Data Query Language
)数据查询语言:表数据查询 - DCL(
Data Control Language
)数据控制语言:定义数据库的访问权限和安全级别
三、DDL操作数据库、表
1.操作数据库:CRUD
-
1.C(Create):创建
1.创建数据库: create database 数据库名称;2.创建数据库,判断不存在,再创建: create database if not exists 数据库名称;3.创建数据库,并指定字符集 create database 数据库名称 character set 字符集名;练习: 创建db4数据库,判断是否存在,并制定字符集为gbk create database if not exists db4 character set gbk;
-
2.R(Retrieve):查询
1.查询所有数据库的名称: show databases;2.查询某个数据库的字符集:查询某个数据库的创建语句 show create database 数据库名称;
-
3.U(Update):修改
修改数据库的字符集 alter database 数据库名称 character set 字符集名称;
-
4.D(Delete):删除
1.删除数据库 drop database 数据库名称;2.判断数据库存在,存在再删除 drop database if exists 数据库名称;
-
5.使用(进入)数据库
1.查询当前正在使用的数据库名称 select database();2.使用数据库 use 数据库名称;
2.操作表:CRUD
-
1.数据库的数据类型:
1. int:整数类型age int2. double:小数类型score double(5,2)3. date:日期,只包含年月日,yyyy-MM-dd4. datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss 如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值6.varchar:字符串name varchar(20): 姓名最大20个字符zhangsan 8个字符 张三 2个字符
-
2.C(Create):创建
1. 创建表 create table 表名(列名1 数据类型1,列名2 数据类型2,...列名n 数据类型n); 注意:最后一列,不需要加逗号(,)create table student(id int,name varchar(32),age int ,score double(4,1),birthday date,insert_time timestamp );2.复制表:create table 表名 like 被复制的表名;
-
3.R(Retrieve):查询
1.查询某个数据库中所有的表名称 show tables;2.查询表结构 desc 表名;
-
4.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 列名;
-
5.D(Delete):删除
1. drop table 表名; 2. drop table if exists 表名 ;
四、DML:增删改表中数据
1.添加数据:(insert)
- 语法
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
- 注意:
- 列名和值要一一对应。
- 如果表名后,不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
- 除了数字类型,其他类型需要使用引号(单双都可以)引起来
2.删除数据:
- 语法:
delete from 表名 [where 条件]
- 2.注意:
- 如果不加条件,则删除表中所有记录。
- 如果要删除所有记录
- delete from 表名; – 不推荐使用。有多少条记录就会执行多少次删除操作
- TRUNCATE TABLE 表名; – 推荐使用,效率更高 先删除表,然后再创建一张一样的表
3.修改数据:
-
语法:
update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
-
注意:
- 如果不加任何条件,则会将表中所有记录全部修改
五、DQL:查询表中的记录(重点!)
1.语法
1.全部查询
select * from 表名;
2.组合查询 select 字段列表from 表名列表where 条件列表group by 分组字段having 分组之后的条件order by 排序limit 分页限定;
2.基础查询
1. 多个字段的查询select 字段名1,字段名2... from 表名;注意:如果查询所有字段,则可以使用*来替代字段列表。2. 去除重复:distinct3. 计算列一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)ifnull(表达式1,表达式2):null参与的运算,计算结果都为null表达式1:哪个字段需要判断是否为null如果该字段为null后的替换值。4. 起别名:表名/列名 as 别名 as也可以省略
3.条件查询
-
where子句后跟条件
-
运算符
1.>、< 、<= 、>= 、= 、<>2.BETWEEN...AND 3.IN( 集合) 4.LIKE:模糊查询占位符:_ 单个任意字符% 多个任意字符5.IS NULL 6.and 或 &&or 或 || not 或 !
-
查询语句举例练习
1.查询年龄大于20岁 SELECT * FROM student WHERE age > 20; SELECT * FROM student WHERE age >= 20;2.查询年龄等于20岁 SELECT * FROM student WHERE age = 20;3.查询年龄不等于20岁 SELECT * FROM student WHERE age != 20; SELECT * FROM student WHERE age <> 20;4.查询年龄大于等于20 小于等于30 SELECT * FROM student WHERE age >= 20 && age <=30; SELECT * FROM student WHERE age >= 20 AND age <=30; SELECT * FROM student WHERE age BETWEEN 20 AND 30;5.查询年龄22岁,18岁,25岁的信息 SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25 SELECT * FROM student WHERE age IN (22,18,25);6.查询英语成绩为null SELECT * FROM student WHERE english = NULL; (X)不对的。null值不能使用 = (!=) 判断 SELECT * FROM student WHERE english IS NULL;(√)7.查询英语成绩不为null SELECT * FROM student WHERE english IS NOT NULL;8.查询姓马的有哪些? like SELECT * FROM student WHERE NAME LIKE '马%';9.查询姓名第二个字是化的人 SELECT * FROM student WHERE NAME LIKE "_化%";11.查询姓名中包含腾的人 SELECT * FROM student WHERE NAME LIKE '%腾%';10.查询姓名是3个字的人 SELECT * FROM student WHERE NAME LIKE '___';
4.排序查询
-
语法:
1.order by 子句 2.order by 排序字段1 排序方式1 , 排序字段2 排序方式2...
-
排序方式:
1. ASC:升序,默认的。2. DESC:降序。
-
注意:
如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
5.聚合函数
将一列数据作为一个整体,进行纵向的计算
1. count:计算个数一般选择非空的列:主键count(*)2. max:计算最大值3. min:计算最小值4. sum:计算和5. avg:计算平均值注意:聚合函数的计算,要排除null值。
解决方案:1. 选择不包含非空的列进行计算2. IFNULL函数
6.分组查询
-
1.语法:
group by 分组字段;
-
2.注意:分组之后查询的字段:分组字段、聚合函数
-
3.where 和 having 的区别?
-
where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
-
where 后不可以跟聚合函数,having可以进行聚合函数的判断
-
-
4.举例练习
1.按照性别分组。分别查询男、女同学的平均分 SELECT sex , AVG(math) FROM student GROUP BY sex;2.按照性别分组。分别查询男、女同学的平均分,人数 SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;3.按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组 SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;4.按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人 SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
5.分页查询
-
1.语法:
limit 开始的索引,每页查询的条数;
-
2.公式:
开始的索引 = (当前的页码 - 1) * 每页显示的条数 每页显示3条记录 SELECT * FROM student LIMIT 0,3; -- 第1页SELECT * FROM student LIMIT 3,3; -- 第2页SELECT * FROM student LIMIT 6,3; -- 第3页limit 是一个MySQL"方言"
六、约束
对表中的数据进行限定,保证数据的正确性、有效性和完整性
1. 分类:
-
主键约束:primary key
-
非空约束:not null
-
唯一约束:unique
-
外键约束:foreign key
2.非空约束:not null
某一列的值不能为null
1. 创建表时添加约束:name为非空CREATE TABLE stu(id INT,NAME VARCHAR(20) NOT NULL );2. 创建表完后,添加非空约束ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;3. 删除name的非空约束ALTER TABLE stu MODIFY NAME VARCHAR(20);
3.唯一约束:unique
某一列的值不能重复
- 注意:唯一约束可以有NULL值,但是只能有一条记录为null
1.在创建表时,添加唯一约束:手机号CREATE TABLE stu(id INT,phone_number VARCHAR(20) UNIQUE );2. 删除唯一约束ALTER TABLE stu DROP INDEX phone_number;3. 在表创建完后,添加唯一约束ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
4.主键约束:primary key
主键特征:非空且唯一
一张表只能有一个字段为主键;主键就是表中记录的唯一标识
1. 在创建表时,添加主键约束create table stu(id int primary key,-- 给id添加主键约束name varchar(20) );2. 删除主键ALTER TABLE stu MODIFY id int;(X)ALTER TABLE stu DROP PRIMARY KEY;(√)3. 创建完表后,添加主键ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-
自增长
如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
自增长一般都是设置给主键1.在创建表时,添加主键约束,并且完成主键自增长create table stu(id int primary key auto_increment,-- 给id添加主键约束name varchar(20) );2. 删除自动增长ALTER TABLE stu MODIFY id INT;3. 添加自动增长ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
5.外键约束:foreign key
让表于表产生关系,从而保证数据的正确性(不常用,一般使用Java逻辑控制关系,淡化SQL里的逻辑)
1.在创建表时,可以添加外键
语法:create table 表名(....外键列constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称));2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;3.创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);4.级联操作1. 添加级联操作语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;2. 分类:1. 级联更新:ON UPDATE CASCADE 2. 级联删除:ON DELETE CASCADE
七、多表查询
- 笛卡尔积:有两个集合A,B .取这两个集合的所有组成情况。
要完成多表查询,需要消除无用的数据
1.内连接查询:
-
隐式内连接:使用
where
条件消除无用数据1.查询所有员工信息和对应的部门信息 SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;2.查询员工表的名称,性别。部门表的名称SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;SELECT t1.name, -- 员工表的姓名t1.gender,-- 员工表的性别t2.name -- 部门表的名称FROMemp t1,dept t2WHERE t1.dept_id = t2.id;
-
显式内连接:
select 字段列表 from 表名1 [inner] join 表名2 on 条件
1.SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id; 2.SELECT * FROM emp JOIN dept ON emp.dept_id= dept.id;
2. 外链接查询:
-
左外连接:
select 字段列表 from 表1 left [outer] join 表2 on 条件;
查询的是左表所有数据以及其交集部分。
查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称 SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
-
右外连接:
select 字段列表 from 表1 right [outer] join 表2 on 条件;
查询的是右表所有数据以及其交集部分。SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
3.子查询:
查询中嵌套查询,称嵌套查询为子查询。
查询工资最高的员工信息
1. 查询最高的工资是多少 9000SELECT MAX(salary) FROM emp;2. 查询员工信息,并且工资等于9000的SELECT * FROM emp WHERE emp.salary = 9000;3.子查询,一句话完成SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
-
子查询不同情况:
-
子查询的结果是单行单列的:
子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= = 查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
-
子查询的结果是多行单列的:
1.子查询可以作为条件,使用运算符in来判断2.查询'财务部'和'市场部'所有的员工信息SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;3.子查询SELECT * FROM emp WHERE dept_idIN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
-
子查询的结果是多行多列的:
1.子查询可以作为一张虚拟表参与查询2.查询员工入职日期是2011-11-11日之后的员工信息和部门信息3.子查询SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2WHERE [t1.id]() = t2.dept_id;4.普通内连接SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
-
八、DCL:管理用户,授权
DBA:数据库管理员
1.管理用户
1.添加用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';2.删除用户:
DROP USER '用户名'@'主机名';3. 修改用户密码: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');4.mysql中忘记了root用户的密码?1. cmd -- > net stop mysql 停止mysql服务需要管理员运行该cmd2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功use mysql;update user set password = password('你的新密码') where user = 'root';4. 关闭两个窗口,打开任务管理器,手动结束mysqld.exe 的进程5. 启动mysql服务,使用新密码登录。5. 查询用户:1. 切换到mysql数据库USE myql;2. 查询user表SELECT * FROM USER;通配符: % 表示可以在任意主机使用用户登录数据库
2.权限管理
1. 查询权限:SHOW GRANTS FOR '用户名'@'主机名';SHOW GRANTS FOR 'lisi'@'%';2.授予权限:grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';给张三用户授予所有权限,在任意数据库任意表上GRANT ALL ON *.* TO 'zhangsan'@'localhost';3.撤销权限:revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
九、数据库设计的范式
设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
- 目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)
1.表间关系:
-
一对一:
- 实现方式:实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键
-
一对多(多对一):
- 实现方式:在多的一方建立外键,指向一的一方的主键
-
多对多:
- 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
2.数据库的三大范式
- 第一范式(1NF):
每一列都是不可分割的原子数据项 - 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
- 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
详细了解请查看凉_ting的博客数据库三大范式
十、事务
如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败
1.操作:
-
开启事务:
start transaction;
-
回滚:
rollback;
-
提交:
commit;
2.例子:转账
1.创建表格
CREATE TABLE account (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),balance DOUBLE
);2.添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;3.张三给李四转账 500 元开启事务START TRANSACTION;张三账户 -500UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';李四账户 +500出错了UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';发现执行没有问题,提交事务COMMIT;发现出问题了,回滚事务ROLLBACK;
3.MySQL事务默认自动提交- 事务提交的两种方式:
- 自动提交:
- mysql就是自动提交的
- 一条DML(增删改)语句会自动提交一次事务。
- 手动提交:
- Oracle 数据库默认是手动提交事务
- 需要先开启事务,再提交
- 修改事务的默认提交方式:
- 查看事务的默认提交方式:
SELECT @@autocommit;
1 代表自动提交 0 代表手动提交 - 修改默认提交方式:
set @@autocommit = 0;
- 查看事务的默认提交方式:
4.事务的四大特征
- 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
- 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
- 隔离性:多个事务之间。相互独立。
- 一致性:事务操作前后,数据总量不变
5.事务的隔离级别(了解)
多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
-
存在问题:
-
脏读:一个事务,读取到另一个事务中没有提交的数据
-
不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
-
幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
-
-
隔离级别:
1. read uncommitted:读未提交产生的问题:脏读、不可重复读、幻读2. read committed:读已提交 (Oracle)产生的问题:不可重复读、幻读2. repeatable read:可重复读 (MySQL默认)产生的问题:幻读3.serializable:串行化可以解决所有的问题
-
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
1.数据库查询隔离级别:select @@tx_isolation; 2.数据库设置隔离级别:set global transaction isolation level 级别字符串;3.演示:set global transaction isolation level read uncommitted;start transaction;4.转账操作update account set balance = balance - 500 where id = 1;update account set balance = balance + 500 where id = 2;
更多推荐
小码笔记18:SQL数据库语法
发布评论