微软手机做网站服务器,建筑公司怎么注册,比较好的网站开发团队,教程网站搭建DDL 操作数据库语句
创建删除数据库语句
创建数据库
create database 数据库名称; -- 直接创建
create database if not exists 数据库名称; -- 如果不存在#xff0c;则创建
create database 数据库名称 default charset utf8mb4; -- 创建编译类型utf8的数据类型
cre…DDL 操作数据库语句
创建删除数据库语句
创建数据库
create database 数据库名称; -- 直接创建
create database if not exists 数据库名称; -- 如果不存在则创建
create database 数据库名称 default charset utf8mb4; -- 创建编译类型utf8的数据类型
create database 数据库名称 collate 排序规则; -- 创建依托排序规则的排序规则删除数据库
drop database 数据库名称; -- 直接删除
drop database if not exists 数据库名称; -- 如果不存在则删除使用数据库
进入数据库
use 数据库名称;查询当前所在的数据库
select database();表操作-查询
查询当前数据库的所有表
show tables;查询某表的结构
DESC 表名;查询指定表的创表语句
show create table 表名;表操作-创建
创建表
create table user_table(id int comment 编号,name varchar(50) comment 姓名,age int comment 年龄,gender varchar(1) comment 性别
) comment 用户表;SQL语言中的数据类型
分类类型大小有符号(SIGNED)范围无符号(UNSIGNED)范围描述数值类型TINYINT1 byte(-128, 127)(0, 255)小整数值SMALLINT2 bytes(-32768, 32767)(0, 65535)大整数值MEDIUMINT3 bytes(-8388608, 8388607)(0, 16777215)大整数值INT或INTEGER4 bytes(-2147483648, 2147483647)(0, 4294967295)大整数值BIGINT8 bytes(-2^63, 2^63?1)(0, 2^64?1)极大整数值FLOAT4 bytes(-3.402823466E38, 3.402823466E38)0 和 (1.175494351E?38, 3.402823466E38)单精度浮点数值DOUBLE8 bytes(-1.7976931348623157E308, 1.7976931348623157E308)0 和 (2.2250738585072014E?308, 1.7976931348623157E308)双精度浮点数值DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值精确定点数 分类类型大小描述字符串类型CHAR0–255 bytes定长字符串VARCHAR0–65535 bytes变长字符串TINYBLOB0–255 bytes不超过255个字符的二进制数据TINYTEXT0–255 bytes短文本字符串BLOB0–65535 bytes二进制形式的长文本数据TEXT0–65535 bytes长文本数据MEDIUMBLOB0–16777215 bytes二进制形式的中等长度文本数据MEDIUMTEXT0–16777215 bytes中等长度文本数据LONGBLOB0–4294967295 bytes二进制形式的极大文本数据LONGTEXT0–4294967295 bytes极大文本数据 分类类型大小范围格式描述日期类型DATE31000-01-01 至 9999-12-31YYYY-MM-DD日期值TIME3-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间YEAR11901 至 2155YYYY年份值DATETIME81000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值时间戳
示例
age tinyint unsigned; -- 无符号整型
score double(4,1); -- 最大长度为4,保留小数点后一位
name char(10); -- 当输入数据长度小于10时自动补空格补足10位
name varchar(10); -- 当输入数据长度小于10时长度就为数据长度DDL-表操作-修改
添加字段
alter 表名 add 字段名 类型;修改数据类型
alter table 表名 modify 字段名 新数据类型;修改字段名字段类型
alter table 表名 change 旧字段名 新字段名 类型;删除字段
alter table 表名 drop 字段;修改表名
alter table 表名 rename to 新表名;删除表
drop table 表名;删除指定表内所有数据
truncate table 表名;DML 修改数据库语句
DML-添加数据
给指定字段添加数据
insert into 表名(字段名1,字段名2,……) values (值1值2,……);给全部字段添加数据
insert into 表名 values (值1值2,……);批量添加数据
insert into 表名(字段名1,字段名2,……) values (值1值2,……),(值1值2,……),(值1值2,……);
insert into 表名 values (值1值2,……), (值1值2,……), (值1值2,……);注意
插入数据时指定的字段顺序需要与值的顺序是一一对应的。字符串和日期型数据应该包含在引号中。插入的数据大小应该在字段的规定范围内。 DQL 数据库查询语句
DQL 语法
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数DQL-基本查询
查询多个字段
select * from 表名;
select 字段1,字段2,…… from 表名;设置别名
select 字段1[as 别名1],字段2[as 别名2],…… from 表名;去除重复记录
select distinct 字段列表 from 表名;DQL-条件查询
语法
select 字段列表 from 表名 where 条件列表;条件 比较运算符
运算符功能大于大于等于小于小于等于等于 或 !不等于BETWEEN … AND …在某个范围内含最小、最大值IN(…)在 in 之后的列表中的值多选一LIKE 占位符模糊匹配_ 匹配单个字符% 匹配任意字符IS NULL是 NULL
逻辑运算符
运算符功能AND 或 并且多个条件同时成立OR 或 ||或者多个条件任意一个成立NOT 或 !非不是
DQL-聚合函数
将一列数据视为整体进行纵向统计常见的聚合函数
函数功能count统计数量max最大值min最小值avg平均值sum求和
使用方法
select 聚合函数(字段列表) from 表名;DQL-分组查询
语法
select 字段列表 from 表名 group by 分组字段名where与having区别
执行时机不同where是分组之前进行过滤不满足where条件不参与分组而having是分组之后对结果进行过滤。判断条件不同where不能对聚合函数进行判断而having可以。
注意
抗行顺序where聚合函数having分组之后查询的字段一般为聚合函数和分组字段查询其他字段无任何意义。
DQL-排序查询
语法
select 字段列表 order by 字段1 排序方式, 字段2 排序方式;排序方式
ASC 升序(默认)DESC 降序
DQL-分页查询
语法
select 字段列表 from 表名 limit 起始索引,查询记录数;注意
起始索引从0开始,起始索引 (查询页码 - 1) * 每页显示的记录数不同的sql软件的分页查询语法不同,mysql的语法是limit如果查询的是第一页数据,起始索引可以忽略
DQL-语法执行顺序
from - where - group by - having - select - order by - limit DCL-数据控制语言
DCL-管理客户
-- 1. 查询用户
use mysql;
select * from user;-- 2. 创建用户
create user 用户名主机名 identified by 密码;-- 3. 修改用户密码
alter user 用户名主机名 identified with mysql_native_password by 新密码;-- 4. 删除用户
drop user 用户名主机名;DCL-权限控制
常用权限
权限说明ALL, ALL PRIVILEGES所有权限SELECT查询数据INSERT插入数据UPDATE修改数据DELETE删除数据ALTER修改表DROP删除数据库/表/视图CREATE创建数据库/表
-- 1. 查询权限
show grants for 用户名主机名;-- 2. 授予权限
grant 权限列表 on 数据库名.列表名 to 用户名主机名;-- 3. 撤销权限
revoke 权限列表 on 数据库名.列表名 from 用户名主机名函数
字符串函数
函数功能CONCAT(S1, S2, … Sn)字符串拼接将S1、S2、… Sn拼接成一个字符串LOWER(str)将字符串str全部转为小写UPPER(str)将字符串str全部转为大写LPAD(str, n, pad)左填充用字符串pad对str的左边进行填充达到n个字符串长度RPAD(str, n, pad)右填充用字符串pad对str的右边进行填充达到n个字符串长度TRIM(str)去掉字符串头部和尾部的空格SUBSTRING(str, start, len)返回从字符串str从start位置起的len个长度的字符串
数值函数
函数功能CEIL(x)向上取整FLOOR(x)向下取整MOD(x, y)返回 x/y 的模RAND()返回 0~1 内的随机数ROUND(x, y)求参数 x 的四舍五入的值保留 y 位小数
日期函数
函数功能CURDATE()返回当前日期CURTIME()返回当前时间NOW()返回当前日期和时间YEAR(date)获取指定 date 的年份MONTH(date)获取指定 date 的月份DAY(date)获取指定 date 的日期DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔 expr 后的时间值DATEDIFF(date1, date2)返回起始时间 date1 和 结束时间 date2 之间的天数
流程控制函数
函数功能IF(value, t, f)如果 value 为 true则返回 t否则返回 fIFNULL(value1, value2)如果 value1 不为空返回 value1否则返回 value2CASE WHEN [val1] THEN [res1] … ELSE [default] END如果 val1 为 true返回 res1… 否则返回 default 默认值CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END如果 expr 的值等于 val1返回 res1… 否则返回 default 默认值 约束
作用于表中字段上的规则,用于限制存储在表中的数据用于保证数据库中的数据的正确性有效性和完整性约束分类
约束描述关键字非空约束限制该字段的数据不能为 nullNOT NULL唯一约束保证该字段的所有数据都是唯一、不重复的UNIQUE主键约束主键是一行数据的唯一标识要求非空且唯一PRIMARY KEY默认约束保存数据时如果未指定该字段的值则采用默认值DEFAULT检查约束保证字段值满足某一个条件8.0.16 版本之后CHECK外键约束用来让两张表的数据之间建立连接保证数据的一致性和完整性FOREIGN KEY
用法
create table user(id int primary key);外键约束
外键用于使两张表的数据之间建立连接从而保证数据的一致性和完整性 建立外键约束语法
-- 添加外键
alter table 子表 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);-- 创建外键
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型,...外键字段 数据类型,CONSTRAINT 外键名称 FOREIGN KEY (外键字段)REFERENCES 被引用表(主键字段)
);-- 删除外键
alter table 表名 drop foreign key 外键名称; 删除/更新行为
行为说明NO ACTION当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则不允许删除/更新。与 RESTRICT 一致RESTRICT当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则不允许删除/更新。与 NO ACTION 一致CASCADE当在父表中删除/更新对应记录时如果有则也删除/更新外键在子表中的记录。SET NULL当在父表中删除/更新对应记录时如果有则设置子表中该外键值为 null这就要求该外键允许取 null。SET DEFAULT父表有变更时子表外键列设置成一个默认的值InnoDB 不支持
alter table 子表 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) update 行为 delete 行为;多表查询
多表关系
多对多: 含义一个表中的多条记录可以关联到另一个表中的多条记录反之亦然。建模方式使用第三张中间表来表示这种关系通常包含两个外键分别指向两张主表。 一对多: 含义一个表中的一条记录可以关联另一个表中的多条记录但反之不成立。建模方式在“多”的一方表中添加外键指向“一”的一方的主键。 一对一: 含义一个表中的一条记录 只对应 另一个表中 唯一一条记录反之亦然。建模方式在任意一方加入外键关联另外一方的主键并且设置外键为唯一的(UNIQUE)。
多表查询分类
连接查询 内连接相当于查询A、B交集部分数据外连接 左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据 自连接当前表与自身的连接查询自连接必须使用表别名 子查询:嵌套在其他 SQL 语句中的 SELECT 查询通常用于在主查询中动态提供数据。
多表连接-内连接
-- 1. 隐式内连接
select 字段列表 from 表1, 表2 where 条件;-- 2. 显式内连接
select 字段列表 from 表1 join 表2 on 连接条件;
select 字段列表 from 表1 inner join 表2 on 连接条件;多表连接-外连接
-- 1. 左外连接
select 字段列表 from 表1 left join 表2 on 连接条件;
select 字段列表 from 表1 left outer join 表2 on 连接条件;-- 2. 右外连接
select 字段列表 from 表1 right join 表2 on 连接条件;
select 字段列表 from 表1 right outer join 表2 on 连接条件;自连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件……;联合查询
对于union查询就是把多次查询的结果合并起来形成一个新的查询结果集。对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。union all会将全部的数据直接合并在一起union会对合并之后的数据去重。
-- 直接合并
select 字段列表 from 表A ……
union all
select 字段列表 from 表B ……;-- 合并并去重
select 字段列表 from 表A ……
union
select 字段列表 from 表B ……;子查询
概念SQL语句中嵌套SELECT语句称为嵌套查询又称子查询分类 标量子查询子查询结果为单个值列子查询子查询结果为下列行子查询子查询结果为一行表子查询子查询结果为多行多列
标量子查询
子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询成为标量子查询。
列子查询
子查询返回的结果是一列可以是多行这种子查询称为列子查询。常见操作符
操作符描述简述示例说明IN在指定集合中多选一SELECT * FROM emp WHERE dept_id IN (1, 2, 3);? 选出属于部门 1、2 或 3 的员工NOT IN不在指定集合中SELECT * FROM emp WHERE dept_id NOT IN (1, 2);? 排除部门 1 和 2 的员工ANY子查询返回值中任意一个满足即可salary ANY (SELECT salary FROM emp WHERE dept_id 10)? 工资高于部门 10 里某一个人的员工SOME与 ANY 相同完全等价使用不多ANY 更常见ALL子查询返回值中所有都必须满足salary ALL (SELECT salary FROM emp WHERE dept_id 10)? 工资高于部门 10 所有人的员工
练习
使用到的表格
-- 1. salgrade 薪资等级表
create table salgrade(
grade int,
losal int,
hisal int
) comment 薪资等级表;insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values(3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values(5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);-- 2. dept 部门表
CREATE TABLE dept (id INT PRIMARY KEY,name VARCHAR(50)
);INSERT INTO dept (id, name) VALUES
(1, 研发部),
(2, 市场部),
(3, 财务部),
(4, 销售部),
(5, 总经办),
(6, 人事部);-- 3. emp 职工表
CREATE TABLE emp (id INT PRIMARY KEY,name VARCHAR(50),age INT,job VARCHAR(50),salary DECIMAL(10, 2),entrydate DATE,managerid INT,dept_id INT
);INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1, 金庸, 66, 总裁, 20000, 2000-01-01, NULL, 5),
(2, 张无忌, 20, 项目经理, 12500, 2005-12-05, 1, 1),
(3, 杨逍, 33, 开发, 8400, 2000-11-03, 2, 1),
(4, 韦一笑, 48, 开发, 11000, 2002-02-05, 2, 1),
(5, 常遇春, 43, 开发, 10500, 2004-09-07, 3, 1),
(6, 小昭, 19, 程序员鼓励师, 6600, 2004-10-12, 2, 1),
(7, 灭绝, 60, 财务总监, 8500, 2002-09-12, 1, 3),
(8, 周芷若, 19, 会计, 48000, 2006-06-02, 7, 3),
(9, 丁敏君, 23, 出纳, 5250, 2009-05-13, 7, 3),
(10, 赵敏, 20, 市场部总监, 12500, 2004-10-12, 1, 2),
(11, 鹿杖客, 56, 职员, 3750, 2006-10-03, 10, 2),
(12, 鹤笔翁, 19, 职员, 3750, 2007-05-09, 10, 2),
(13, 方东白, 19, 职员, 5500, 2009-02-12, 10, 2),
(14, 张三丰, 88, 销售总监, 14000, 2004-10-12, 1, 4),
(15, 俞莲舟, 38, 销售, 4600, 2004-10-12, 14, 4),
(16, 宋远桥, 40, 销售, 4600, 2004-10-12, 14, 4),
(17, 陈友谅, 42, NULL, 2000, 2011-10-12, 1, NULL);题目
-- 1. 查询员工的姓名、年龄、职位、部门信息。
select e.name, e.age, e.job, d.name from dept d, emp e where e.dept_id d.id; -- 隐式内连接
select e.name, e.age, e.job, d.name from emp e left join dept d on e.dept_id d.id; -- 左外连接-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
select e.name, e.age, e.job, d.name from dept d, emp e where (e.dept_id d.id and age 30); -- 隐式内连接
select e.name, e.age, e.job, d.name from emp e join dept d where (e.dept_id d.id and age 30); -- 显式内连接
select e.name, e.age, e.job, d.name from emp e join dept d on e.dept_id d.id where age 30; -- 显式内连接(标准写法)-- 3. 查询拥有员工的部门ID、部门名称。
select distinct d.id, d.name from emp e, dept d where e.dept_id d.id order by d.id; -- 显式内连接(去重并顺序输出)-- 4. 查询所有年龄大于40岁的员工及其归属的部门名称如果员工没有分配部门也需要展示出来。
select e.name, d.name from emp e left join dept d on e.dept_id d.id where e.age 40; -- 左外连接-- 5. 查询所有员工的工资等级。
select e.name, s.grade from emp e join salgrade s on e.salary between losal and hisal;
select e.name, s.grade from emp e join salgrade s on (s.losal e.salary and e.salary s.hisal);-- 6. 查询研发部所有员工的信息及工资等级。
select e.*, s.grade from emp e
join dept d on e.dept_id d.id
join salgrade s on e.salary between losal and hisal
where d.name 研发部;select e.*,s.grade
from emp e, dept d, salgrade s
where e.dept_id d.id and e.salary between losal and hisal and d.name 研发部;
-- 7. 查询研发部员工的平均工资。
select avg(e.salary) from emp e
join dept d on e.dept_id d.id
where d.name 研发部;
-- 8. 查询工资比灭绝高的员工信息。
select * from emp where salary (select salary from emp where name 灭绝);-- 9. 查询比平均薪资高的员工信息。
select * from emp where salary (select avg(salary) from emp);-- 10. 查询低于本部门平均工资的员工信息。
select e1.* from emp e1 where e1.salary (select avg(salary) from emp e2 where e2.dept_id e1.dept_id); -- 11. 查询所有的部门信息并统计部门的员工人数。
select d.id, d.name, count(e.id) as emp_count
from dept d
left join emp e on d.id e.dept_id
group by d.id, d.name;select d1.*, (select count(*) from emp e where e.dept_id d1.id) as emp_count
from dept d1;
---
## 事务
### 事务简介事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作**要么同时成功要么同时失败**。默认MySQL的事务是自动提交的也就是说当执行一条DML语句MySQL会立即隐式的提交事务。### 事务操作
sql
-- 1. 查看/设置事务提交方式
select autocommit
set autocommit-- 2. 提交事务
commit;-- 3. 回滚事务
rollback;-- 4. 开启事务
start transaction;
begin;事务四大特性
原子性(Atomicity):事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性(Consistency):事务完成时必须使所有的数据都保持一致状态。隔离性(Isolation):数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性(Durability)事务一旦提交或回滚它对数据库中的数据的改变就是永久的。
并发事务问题
问题描述脏读一个事务读取到了另一个事务尚未提交的数据。如果那个事务随后回滚了那么你读取到的数据就根本不应该存在叫做“脏”数据。不可重复读一个事务两次读取同一条记录但读取的内容不同中间有其他事务修改了数据并提交称为不可重复读。幻读一个事务按照条件查询数据时第一次查没有另一个事务插入了满足条件的新记录并提交当前事务再次查询时又看到了新数据好像产生了“幻影”。
事务隔离级别
隔离级别Isolation Level脏读Dirty Read不可重复读Non-repeatable Read幻读Phantom ReadRead Uncommitted? 可能出现? 可能出现? 可能出现Read Committed? 避免了? 可能出现? 可能出现Repeatable Read默认? 避免了? 避免了? 可能出现??Serializable? 避免了? 避免了? 避免了
事务隔离级别越高数据越安全但是性能越低。
-- 1. 查看事务隔离级别
select transaction_isolation;-- 2. 设置事务隔离级别
-- session 仅针对当前客户端窗口有效
-- global 针对所有客户端窗口有效
set [session|global] transaction level 隔离级别;
