MySQL
MySQL
-眼里有星河-
这个作者很懒,什么都没留下…
展开
-
MySQL单一表格检索数据
WHWRE子句USE world; -- 选中数据库,选中加粗SELECT * FROM city -- 查询表中所有数据SELECT * FROM city WHERE ID = 1; -- 显示表中某行SELECT DISTINCT Population AS new FROM city; -- 显示表中某列-- 如果新名字有空格,需要用引号-- DISTINCT可以去重-- AS可以省略IN运算符USE world;原创 2022-01-13 11:28:25 · 162 阅读 · 0 评论 -
MySQL多张表格检索数据及编写复杂查询
内连接USE store;SELECT c.customer_id,first_name,last_name FROM customers AS cJOIN orders AS o ON c.customer_id = o.customer_id-- USING (customer_id)也可以之前在SELECT中给选定的列加别名主要是为了得到更有意义的列名,这里在 FROM JOIN 中给表加别名主要是为了简化。注:用了别名后其他地方只能用别名,用全名会报错。跨数据库连原创 2022-01-13 15:51:27 · 348 阅读 · 0 评论 -
MySQL创建数据库
插入INSERT INTO orders(customer_id, order_date, status)VALUES(1,'2022-01-01',2);INSERT INTO order_itemsVALUES (last_insert_id(), 1, 2, 2.5), (last_insert_id(), 2, 5, 1.5)last_insert_id():获取最新的成功的INSERT中的自增id复制数据表TRUNCATE 'orders_archived';原创 2022-01-13 19:55:04 · 158 阅读 · 0 评论 -
MySQL汇总数据及基本函数
聚合函数SELECT MAX(invoice_date) AS latest_date, MIN(invoice_total) AS lowest, AVG(invoice_total) AS average, SUM(invoice_total * 1.1) AS total, COUNT(invoice_total) AS number_of_invoices, COUNT(*) AS total_records, -- 包括null原创 2022-01-13 21:16:12 · 188 阅读 · 0 评论 -
MySQL视图、存储过程、函数
视图USE sql_invoicing;-- 创建视图CREATE VIEW sales_by_client AS SELECT client_id, name, SUM(invoice_total) AS total_sales FROM clients c JOIN invoices i USING(client_id) GROUP BY client_id;-- 删除视图DROP VIEW sales_原创 2022-01-14 17:35:09 · 196 阅读 · 0 评论 -
MySQL触发器、事件、事务
触发器添加触发器:DELIMITER $$-- 命名习惯:触发表_before/after_触发的SQL语句类型CREATE TRIGGER payments_after_insert AFTER INSERT ON payments -- BEFORE/AFTER INSERT/UPDATE/DELETE ON FOR EACH ROW -- 触发频率语句BEGIN UPDATE invoices SET payment_total =原创 2022-01-15 10:45:16 · 547 阅读 · 0 评论 -
MySQL数据类型
整数类型整数类型 占用储存 记录的数字范围 TINYINT 1B [-128,127] SMALLINT 2B [-32K,32K] MEDIUMINT 3B [-8M,8M] INT 4B [-2B,2B] BIGINT 8B [-9Z,9Z] 我们用括号表示显示位数,如INT(4)表示0001,注意这只影响MySQL如何显示数字而不影响如何保存数字定点数类型和浮点数类型定点数:DEC原创 2022-01-15 11:50:26 · 299 阅读 · 0 评论 -
MySQL索引、用户
索引CREATE INDEX idx_state ON customers (state);-- CREATE INDEX 索引名(idx_列名) ON 表名 (列名);EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';-- 解释性查询-- 查看索引ANALYZE TABLE customers;-- 有这句才能得到精确的Cardinality SHOW INDEXES IN customers;-- 删除索引原创 2022-01-15 21:03:19 · 687 阅读 · 0 评论