MySQL
数据库技术分享
大都督老师
爱老婆不能只停留在口头上
展开
-
解决MySQL数据库乱码
效果图:解决方案一、application.ymlspring: jpa: hibernate: ddl-auto: update show-sql: true datasource: # docker run -d --name mysql-sharding-jdbc -p 3306:3306 -v /opt/dadudu/data/mysql-sharding-jdbc:/var/lib/mysql -v /opt/dadudu/etc/mysql-s原创 2022-03-23 18:59:20 · 2313 阅读 · 0 评论 -
进入docker中安装的MySQL8.0数据库,设置navicat正确连接
启动dockersystemctl start docker启动MySQL8.0查看镜像:[root@localhost ~]# docker imagesREPOSITORY TAG IMAGE ID CREATED SIZEhello-world latest d1165f221234 12 days ago 13.3kBmysql latest c8562eaf9d81 8 weeks ago 5原创 2021-03-18 23:40:08 · 1171 阅读 · 10 评论 -
MySQL使用视图
利用试图简化复杂的联结CREATE VIEW productCustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;SELECT * FROM productCustomers;...原创 2021-01-17 10:44:03 · 70 阅读 · 0 评论 -
MySQL创建和操纵表
创建表CREATE TABLE products_a (prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name char(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL, prod_desc VARCHAR(1000) NULL);使用null值CREATE TABLE orders_o (order_num INTEGER NOT NULL, order_date dateti原创 2021-01-09 20:21:07 · 110 阅读 · 0 评论 -
MySQL更新和删除数据
更新数据UPDATE customers SET cust_email = '大都督@qq.com', cust_contact = '18232577543' WHERE cust_id = '1';删除数据DELETE FROM customers WHERE cust_id = '1';原创 2021-01-09 20:16:53 · 105 阅读 · 0 评论 -
MySQL插入数据
插入完整的行INSERT INTO customers VALUES ('1', '大都督', '123 Any Street', 'new york', 'ny', '111', 'china', NULL, NULL);INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES ('2'原创 2021-01-09 20:14:52 · 418 阅读 · 0 评论 -
MySQL组合查询
使用unionSELECT cust_name, cust_contact, cust_email FROM customers WHERE cust_state IN ('IL', 'IN', 'MI');SELECT cust_name, cust_contact, cust_email FROM customers WHERE cust_name = 'Fun4All';SELECT cust_name, cust_contact, cust_email FROM customers WHERE原创 2021-01-09 20:12:16 · 75 阅读 · 0 评论 -
MySQL创建表高级联结
使用表的别名SELECT * FROM customers C, orders O, orderitems OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';自联结SELECT * FROM customers c1, customers c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';原创 2021-01-09 19:43:30 · 119 阅读 · 0 评论 -
MySQL联结表
创建联结SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id;WHERE子句的重要性-- WHERE子句的重要性-- 笛卡儿积(检索出的行的数目是第一个表的行数乘以第二个表中的行数)SELECT * FROM vendors;SELECT * FROM products;SELECT vend_name, prod_name, prod_原创 2021-01-09 19:39:48 · 94 阅读 · 0 评论 -
MySQL使用子查询
利用子查询进行过滤SELECT * FROM orderitems WHERE prod_id = 'RGAN01';SELECT * FROM orders WHERE order_num IN (20007,20008);SELECT * FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01');SELECT * FROM customers WHERE cust_id I原创 2021-01-09 19:35:33 · 220 阅读 · 0 评论 -
MySQL分组数据
创建分组SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;过滤分组SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 4 GROUP BY原创 2021-01-09 19:32:04 · 104 阅读 · 0 评论 -
MySQL汇总数据
聚集函数AVG()函数SELECT AVG(prod_price) AS avg_price FROM products;COUNT()函数SELECT COUNT(*) AS num_cust FROM customers;-- 忽略null值SELECT COUNT(cust_email) AS num_email FROM customers;MAX()函数SELECT MAX(prod_price) AS max_price FROM products;原创 2021-01-09 19:29:08 · 184 阅读 · 0 评论 -
MySQL使用函数处理数据
文本处理函数SELECT vend_name, UPPER(vend_name) AS vend_name_upper FROM vendors ORDER BY vend_name;匹配所有发音类似的SELECT * FROM customers WHERE cust_contact = 'Michelle Green';SELECT * FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');日期和时间原创 2021-01-08 21:22:10 · 130 阅读 · 0 评论 -
MySQL创建计算字段
拼接字段SELECT CONCAT(vend_name, ' ( ', vend_country, ' ) ') FROM vendors ORDER BY vend_name;SELECT TRIM(CONCAT(vend_name, ' ( ', vend_country, ' ) ')) AS vend_title FROM vendors ORDER BY vend_name;SELECT RTRIM(CONCAT(vend_name, ' ( ', vend_country, ' )原创 2021-01-06 20:46:08 · 105 阅读 · 0 评论 -
MySQL用通配符进行过滤
LIKE操作符-- 百分号(%)通配符SELECT * FROM products WHERE prod_name LIKE 'Fish%';-- 6.1.1 百分号(%)通配符SELECT * FROM products WHERE prod_name LIKE 'Fish%';下划线(_)通配符SELECT * FROM products WHERE prod_name LIKE '__ inch%';方括号([])通配符(只有微软的access和SQL支持集合)SELECT *原创 2021-01-05 20:28:01 · 128 阅读 · 0 评论 -
MySQL高级数据过滤
组合where子句and操作符SELECT * FROM products WHERE vend_id = 'DLL01' AND prod_price <= 4;OR操作符SELECT * FROM products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';求值顺序SELECT * FROM products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price原创 2021-01-04 21:53:49 · 131 阅读 · 0 评论 -
MySQL过滤数据
使用WHERE子句-- 使用WHERE子句SELECT prod_name, prod_price FROM products WHERE prod_price = 3.49WHERE子句操作符检查单个值-- 检查单个值SELECT prod_name, prod_price FROM products WHERE prod_price < 10不匹配检查-- 不匹配检查SELECT vend_id, prod_name FROM products WHERE vend_i原创 2021-01-03 10:51:17 · 66 阅读 · 0 评论 -
MySQL检索数据
检索单个列-- 检索单个列SELECT prod_name FROM products检索多个列-- 2.检索多个列SELECT prod_id, prod_name, prod_price FROM products;3. 检索所有列-- 3.检索所有列SELECT * FROM products;4. 检索不同的值-- 4. 检索不同的值SELECT DISTINCT vend_id FROM products;5. DISTINCT后的两列不完全相同,.原创 2021-01-02 11:26:35 · 67 阅读 · 0 评论 -
MySQL创建、修改、插入语句
CREATE TABLE-- ------------------------ Create Customers table-- ----------------------CREATE TABLE Customers( cust_id char(10) NOT NULL , cust_name char(50) NOT NULL , cust_address char(50) NULL , cust_city char(50) NULL , cu原创 2021-01-01 20:20:17 · 159 阅读 · 1 评论 -
MySQL-数字格式化
####代码如下:SELECT CAST('123456789012345678.115' AS DECIMAL(20,2)) AS num -- 数字格式化####效果图:原创 2018-09-19 12:18:34 · 6077 阅读 · 0 评论 -
MySQL-日期格式化
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s') AS now_time -- 日期格式化原创 2018-09-19 12:20:09 · 2099 阅读 · 0 评论 -
1.1 MySQL判断两个字符串是否有交集
1.1.1 设置log_bin_trust_function_creatorslog_bin_trust_function_creators控制是否可以信任存储函数创建者,不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0(默认值),用户不得创建或修改存储函数,除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETER...原创 2018-12-05 16:16:08 · 3375 阅读 · 2 评论 -
应该算是比较高级的SQL了吧?
1.两个牛X的SQL1.1 update两张表中的数据1.2 insert into+select+where+group by+having2. 案例if (godIdSet != null &amp;&amp; godIdSet.size() &gt; 0) { //修改订单状态和商品的销售量 String goods_order_detail = ...原创 2018-12-15 20:09:35 · 161 阅读 · 0 评论 -
MySQL-SUBSTRING_INDEX函数
1.截取字符串 a,b,c 获取第1个逗号‘,’之前的子字符串SELECT SUBSTRING_INDEX('a,b,c', ',', 1) AS sub_str;2.效果图原创 2019-01-19 18:48:43 · 1365 阅读 · 0 评论