手机做网站对比路由器做网站,河北住房和城乡建设厅网站电话,电子商务网站建设策划报告,艺点意创设计公司MySQL数据库概述
1 SQL
SQL语句大小写不敏感。 SQL语句末尾应该使用分号结束。
1.1 SQL语句及相关操作示例
DDL#xff1a;数据定义语言#xff0c;负责数据库定义、数据库对象定义#xff0c;由CREATE、ALTER与DROP三个语法所组成DML#xff1a;数据操作语言#xff…MySQL数据库概述
1 SQL
SQL语句大小写不敏感。 SQL语句末尾应该使用分号结束。
1.1 SQL语句及相关操作示例
DDL数据定义语言负责数据库定义、数据库对象定义由CREATE、ALTER与DROP三个语法所组成DML数据操作语言负责对数据库对象的操作CRUDcreate、read、update、delete增查、改、删DCL数据控制语言负责数据库权限访问控制由GRANT和REVOKE两个指令组成TCL事务控制语言负责处理ACID事务支持commit、rollback指令
# GRANT授权、REVOKE撤销
grant all on crashsource.* to test_user% identified by cli*963.; -- 在所有主机上对test_user用户授权操作数据库crashsource中的所有表。 .*表示所有表 %通配符revoke all on *.* from test_use; -- 撤销test_user用户对所有表的配置权限。# 删除用户
drop user test_user; -- 删除用户test_user慎用。# 创建数据库
create database if not exists gogs character set utf8mb4 collate utf8mb4_general_ci; -- 创建数据库# 删除数据库
drop database if exists gogs; -- 如果数据库gogs存在则删除。# 创建表
CREATE TABLE reg (loginname varchar(48) NOT NULL,name varchar(64) NOT NULL,password varchar(255) NOT NULL,KEY ln (loginname)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 创建表反引号标注的名称被认为是非关键字。# DESC查看列信息
desc products; -- 查看列信息
desc products %name; -- 查看以name结尾的列信息 1.2 主键PRIMARY KEY *
主键PRIMARY KEY表中一列或多列一个字段或多个字段组成组成唯一的key通过这个key能唯一的标识一条记录。主键的列不能使用空值NULL。主键往往设置为整型、长整型字符串、日期型都可以但是开发中一般不这么做因为存在性能上的问题通过自增AUTO_INCREMENT来保证主键的唯一性。通常实践结果表明也不怎么使用多列组合来组成主键。
自增AUTO_INCREMENT自己管理一个数字每增加一行数字1删除一行这个数字不变。数字绝不回头。
表中也可以没有主键但不符合惯例。 1.3 索引INDEX *
索引可以看做是一本大字典的目录为了快速检索用的。空间换时间显著提高查询效率同时会带来插入、删除、修改的效率问题比如要修改某一个字段可能需要同时去更新索引。
可以对一列或多列设定索引。
**主键索引**主键会自动建立主键索引主键本身就是为了快速定位唯一记录的。 **唯一索引**表中的索引列组成的索引必须唯一但可以为空非空值必须唯一。 主键索引和唯一索引的显著差异就是主键不可以为NULL唯一键可以为NULL。 **普通索引**没有唯一性的要求就是建立了一个字典的目录而已。 1.4 约束Constraint *
unique约束唯一键约束定义了唯一键索引就定义了唯一键约束。唯一约束就是不允许重复但是可以为NULL为NULL只允许某一行的一个字段为NULL。
primary key约束定义了主键就定义了主键约束。主键约束就是不允许为NULL,而且不允许重复。
外键约束Foreign Key **外键**在表B中的列关联表A中的主键表B中的列就是外键。 1、如果在表B插入一条数据B的外键列插入了一个值这个值必须是表A中存在的主键值。 2、修改表B的外键值同样要在表A中存在。 3、如果表A要删除一条记录那么就等于删除了一个主键那么如果表B中引用了这个主键就必须先删除表B中引用这个主键的记录然后才能删除表A的记录否则删除失败。 4、修改表A的主键由于主键的唯一性修改的主键相当于插入新的主键那么表B引用过这个主键将组织表A的主键修改必须删除表B的相关记录后才能修改表A的主键。 外键约束为了保证数据完整性、一致性杜绝数据冗余、数据讹误。 1.5 视图
视图也称续表看起来像表。它是由查询语句生成的。可以通过视图进行CRUD增删改查操作。
视图的作用
简化操作将复杂的SQL查询语句定义为视图可以简化查询。数据安全视图可以只显示真实表的部分列或计算后的结果隐藏真实表的数据。
# 创建视图示例
create view ProductCustomers AS select cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_idorders.cust_id AND orderitems.order_numorders.order_num; 2 MySQL数据类型 *
下列常用的数据类型必须牢记
类型含义tinyint1字节带符号的范围是128到127。无符号的范围是0到255。bool或boolean就是tinyint0表示假非0表示真smallint2字节带符号的范围是-32768到32767。无符号的范围是0到65535int整型4字节同Integer带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295bigint长整型8字节带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615float单精度浮点数精确到大约7位小数位double双精度浮点数精确到大约15位小数位DATE日期。支持的范围为1000-01-01到9999-12-31DATETIME支持的范围是1000-01-0100:00:00到9999-12-3123:59:59TIMESTAMP时间戳。范围是1970-01-0100:00:00到2037年char(M)固定长度右边填充空格以达到长度要求。M为长度范围为0~255。M指的是字符个数varchar(M)变长字符串。M表示最大列长度。M的范围是0到65535。但不能突破行最大字节数65535text大文本。最大长度为65535(2^16-1)个字符BLOB大字节。最大长度为65535(2^16-1)字节的BLOB列
LENGTH函数返回字节数。而char和varchar定义的M是字符数限制。
char可以将字符串变成等长的空间换时间效率略高查询、存储效率 varchar变长省了空间。但是会带来效率问题。因为存的数据参差不齐带来读取的问题比如存了一百万条记录单要对前面的数据进行修改而这个数据恰恰引起了长度的变化导致整个IO开始变动上百万条整体挪动。。。
所以到底用什么要根据实际项目评估。比如数据库经常修改还不如用char呢。但是用char一个主从、多从的数据库架构那要浪费多少磁盘空间啊。。。 2.1 使用Navicat设计表示例
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uAF2gcKc-1692374636464)(设计表示例.jpg)] 保存后生成的SQL语句
CREATE TABLE reg (id int(11) unsigned NOT NULL AUTO_INCREMENT,loginname varchar(48) NOT NULL,name varchar(64) NOT NULL,password varchar(255) NOT NULL,reserved1 varchar(255) DEFAULT NULL,reserved2 varchar(255) DEFAULT NULL,PRIMARY KEY (id),UNIQUE KEY ln (loginname) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;2.2 使用Navicat创建视图示例 点击视图- 新建视图 点击视图创建工具 - 拖拽需要创建视图的表salaries、employees到窗口勾选需要查看的字段first_name、last_name、salary、emp_no - 构建 预览 点击保存然后双击打开保存的视图新建视图查询语句- 点击运行 3 关系操作
关系在关系型数据库中关系就是二维表。关系操作就是对表的操作。
选择selection又称为限制是从关系中选择出满足给定条件的元组。投影projection在关系上投影就是从选择出若干属性列组成新的关系。连接join将不同的两个关系连接成一个关系。
3.1 DML
CRUD增create、查read、改update、删delete。
3.1.1 Insert语句
INSERT INTOcustomers (cust_name, cust_address, cust_city, cust_country, cust_contact, cust_email)
VALUES (Li Ming, JIN NIU, ChengDU, China, 02811111, testemail.com); -- 基本的insert语句。主键自动递增可以不指定。INSERT INTOorders (order_date, cust_id)
VALUES (database(), 123)
ON DUPLICATE KEYUPDATE order_datedatabase(); -- 如果主键冲突、唯一键冲突就执行Upgdate后的设置。主键不在新增记录主键存在更新部分字段。INSERT IGNORE INTOvendors (vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES (Huawei, PIDU, CHENGDU, SICHUAN, 123454, CHINA); -- 如果主键冲突、唯一建冲突就忽略错误返回一个告警。3.1.2 Update语句
UPDATE IGNORE vendors SET vend_addressPi DU, vend_cityCHENG DU where vend_nameHuawei; -- 要有where语句否则就会更新所有行。ignore同上3.1.3 DELETE语句
DELETE IGNORE FROM vendors WHERE vend_nameHUAWEI; -- 删除符合条件的记录; 没有where子句将删除所有行可以通过日志恢复。delete不会删除表本身。truncate语句 作用是清空表或者说是截断表只能作用于表。truncate的语法很简单后面直接跟表名即可 truncate是DDL语句。
TRUNCATE vendors -- 不记录数据的变动可以通过日志恢复truncate与drop,delete的对比 truncate与deletedrop很相似其实这三者还是与很大的不同的下面简单对比下三者的异同。
truncate与drop是DDL语句执行后无法回滚delete是DML语句可回滚。truncate只能作用于表deletedrop可作用于表、视图等。truncate会清空表中的所有行但表结构及其约束、索引等保持不变drop会删除表的结构及其所依赖的约束、索引等。truncate会重置表的自增值delete不会。truncate不会激活与表有关的删除触发器delete可以。truncate后会使表和索引所占用的空间会恢复到初始大小delete操作不会减少表或索引所占用的空间drop语句将表所占用的空间全释放掉。
3.1.4 select语句
查询的结果称为结果集record set。
/*SELECT[DISTINCT]select_expr,...[FROM table_references[WHERE where_definition][GROUP BY {col_name | expr | position}[ASC | DESC],... [WITH ROLLUP]][HAVING where_definition][ORDER BY {col_name I expr I position][ASC I DESC],..][LIMIT ([offset,] row count I row_count OFFSET offset}][FOR UPDATE | LOCK IN SHARE MODE]]*/for update 会把进行写锁定这是排他锁。
SELECTDISTINCTvend_name, vend_address
FROM vendors
WHERE vend_country IN(USA, ENGLISH, CHINA)
GROUP BY vend_country DESC
HAVING vend_country IN(USA, ENGLISH, CHINA)
ORDER BY vend_country
LIMIT 5 OFFSET 2
FOR UPDATE;SELECT 1;
-- 最简单的查询
SELECT * FROM vendors;-- 字符串合并
SELECT vend_id, vend_name vend_address FROM vendors;
SELECT vend_id, COUNT(vend_name, , vend_address, , vend_city) FROM vendors;
-- as 定义别名
SELECT vend_id, COUNT(vend_name, , vend_address, , vend_city) as vend_info FROM vendors;-- limit子句
SELECT * FROM orders LIMIT 5; -- 提取前5行
SELECT * FROM orders LIMIT 5 OFFSET 4; -- 提取5条记录偏移4条。偏移可以用作分页读取比如每次偏移20读取20条记录每读取一次偏移增加20.
SELECT * FROM orders LIMIT 4, 5; -- 上条语句的另外一种写法。– where子句 where子句涉及的运算符
运算符描述等于不等于,,,大于、小于、大于等于、小于等于BETWEEN在某个范围之内between a andb等价于[ab]LIKE字符串模式匹配%表示任意多个字符表示一个字符IN指定针对某个列的多个可能值AND与OR或
**注意**如果很多表达式需要使用AND、OR计算逻辑表示式的值的时候由于结合律问题可以使用小括号来避免错误。
-- 条件查询
SELECT * FROM vendors WHERE vend_id1006 and vend_city in (New York, Pairs, London) and vend_name like J%;
SELECT * FROM vendors WHERE vend_id BETWEEN 1000 AND 1005 AND vend_name LIKE J%;
SELECT * FROM vendors WHERE vend_id IN (1003, 1004, 1005);-- order by子句
# 对查询结果进行排序可以升序ASC、降序DESC
-- 降序
SELECT * FROM vendors WHERE vend_id IN (1003, 1004, 1005) ORDER BY vend_id DESC;-- DISTINCT
# 不返回重复记录
SELECT DISTINCT vend_id FROM vendors;
SELECT DISTINCT vend_id, vend_city FROM vendors;聚合函数
函数描述COUNT(expr)返回记录中记录的数目如果指定列则返回非NULL值的行数COUNT(DISTINCT expr,[expr..)返回不重复的非NULL值的行数AVG(IDISTINCT]expr)返回平均值返回不同值的平均值MIN(expr), MAX(expr)最小值最大值SUM(IDISTINCT]expr)求和Distinct返回不同值求和
-- 聚合函数
SELECT COUNT(*), AVG(quantity), SUM(quantity), MIN(quantity), MAX(quantity) FROM orderitems;分组查询 使用group by子句如果有条件使用HAVING子句过滤分组、聚合过滤后的结果。
-- 聚合所有
SELECT SUM(item_price), AVG(item_price), COUNT(order_item) FROM orderitems;
-- 聚合被选择的记录
SELECT order_item, SUM(quantity), AVG(item_price), COUNT(order_item) FROM orderitems WHERE order_item1;
-- 按照不同的order_item分组分组分别聚合
SELECT order_item, SUM(quantity), AVG(item_price), COUNT(order_item) FROM orderitems WHERE item_price10 GROUP BY order_item;
-- HAVING子句对分组结果过滤
SELECT order_item, SUM(quantity), AVG(item_price), COUNT(order_item) FROM orderitems GROUP BY order_item HAVING AVG(quantity)3;
-- 使用别名
SELECT order_item, SUM(quantity), AVG(item_price) as avg_q FROM orderitems GROUP BY order_item HAVING AVG(quantity)3;
-- 最后对分组过滤后的结果进行排序
SELECT order_item, SUM(quantity), AVG(item_price) as avg_q FROM orderitems GROUP BY order_item HAVING AVG(quantity)3 ORDER BY avg_q;子查询 查询语句可以嵌套内部查询就是子查询。子查询必须在一组小括号中。子查询不能使用order by。
SELECT *
FROM orderitems
where item_price IN (SELECT item_price FROM orderitems WHERE item_price10)
ORDER BY item_price
DESC;SELECT order_item
FROM (SELECT * FROM orderitems WHERE item_price10) as ord_items
where item_price9
ORDER BY order_item
DESC;子查询存在性能问题建议少用改用join。 连接join
在联结两个表时实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件只包含那些匹配给定条件这里是联结条件的行。 没有WHERE子句第一个表中的每一行将与第二个表中的每一行配对而不管它们逻辑上是否能配在一起这种联结就是笛卡尔联结。
内联结:
没有where限定的内联结就是笛卡尔联结又称叉联结。有的说法叉联结是叉联结使用CROSS JOIN内联结是内联结使用INNER JOIN只是两种联结的语法相同。
SELECT vend_name, prod_name, prod_price FROM vendors, products;
SELECT vend_name, prod_name, prod_price FROM vendors CROSS JOIN products; -- 叉联结使用CROSS JOIN
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products; -- 内联结使用INNER JOIN因为没有where限定得出的结果也是叉联结的结果。等值联结,有where限定的内联结 在联结两个表时实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件只包含那些匹配给定条件这里是联结条件的行。 没有 WHERE子句第一个表中的每一行将与第二个表中的每一行配对而不管它们逻辑上是否能配在一起。 SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id products.vend_id;
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id products.vend_id; -- 更推荐使用inner join... on... 语法
-- 自联结特殊的等值联结
-- 找出与Mr Wang在同一个国家的所有顾客然后给他们发邮件。先使用子查询
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_country (SELECT cust_countryFROM CustomersWHERE cust_contact Mr Wang);
-- 使用自联结
SELECT a.cust_id, a.cust_name, a.cust_email
FROM customers AS a INNER JOIN customers AS b ON a.cust_country b.cust_country AND b.cust_nameMr Wang;-- 自然联结等值连接中若干个联结的表会产生重复的列。自然联结就是没有重复的列是一种特殊的等值连接。
-- 没有内置的DBMS语法支持自然联结需要用户自己实现。一般通过对一个表使用通配符(SELECT *)而对其他表的列使用明确的子集来完成
SELECT C.*, O.order_num, O.order_date,OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O,OrderItems AS OI
WHERE C.cust_id O.cust_id
AND OI.order_num O.order_num
AND prod_id RGAN01;外联结分为左外联结右外联结 许多联结将一个表中的行与另一个表中的行相关联但有时候需要包含没有关联行的那些行。 例如对每个顾客下的订单进行计数包括那些至今尚未下订单的顾客列出所有产品以及订购数量包括没有人订购的产品。 -- 使用 LEFT OUTER JOIN 从 FROM 子句左边的表(Customers 表)中选择所有行.检索包括没有订单顾客在内的所有顾客
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id orders.cust_id;
-- 使用 RIGHT OUTER JOIN 从 FROM 子句右边的表(Customers 表)中选择所有行.检索包括没有订单顾客在内的所有顾客
SELECT customers.cust_id, orders.order_num FROM orders RIGHT OUTER JOIN customers ON customers.cust_id orders.cust_id;-- 带聚集函数的联结
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id Orders.cust_id
GROUP BY Customers.cust_id; -- 先执行INNER JOIN... ON...将两张表关联然后按顾客ID分组最后通过聚集函数统计出每个顾客的订单数。-- 联结多个表
-- 使用子查询
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id RGAN01));
-- 使用联结
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id Orders.cust_id
AND OrderItems.order_num Orders.order_num
AND prod_id RGAN01;
