Hetianhan的博客
0%

Mysql

发表于 更新于 分类于

MySQL学习

查询端口号：
1
show global variables like 'port'
1、查询数据
1
select * from 表名；
2、查询指定数据
1
select 指定字段 from 表名；
3、条件查询
1
2
3
4
5
6
7
查询年龄15-17
 select * from 表名 where age<=17；      selete * from 表名 where age between 15 and 17；
查询年龄15和17的
 select * from 表名 where age=15 and age=17；
 
 select name from 表名 where(age=15 or age =17) and sex=man;
 /*这条 SELECT 语句与前一条的唯一差别是，这条语句中，前两个条件用圆括号括了起来。因为圆括号具有较 AND 或 OR 操作符高的计算次序，DBMS首先过滤圆括号内的 OR 条件*/
4、IN操作符
1
2
3
/*IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配*/
查询年龄15和17的
  select * from 表名 where age IN(15,17) ORDER BY username;
5、NOT操作符
1
2
3
/*NOT操作符用来否定后面跟条件的关键字*/
查询除了15和17的人
select *from 表名 wher age NOT IN(15,17) ORDER BY username;
6、LIKE操作符
6.1 百分号（%）通配符
1
2
/*%通配符表示任何字符出现任意次数*/
select id,prod_name from products where prod_name LIKE'jet%';
1
2
3
4
5
6
7
8
9
/*根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写， 'jet%' 与 JetPack 1000 将不匹配。

  通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符。*/
 select id,prod_name from products where prod_name LIKE'%jet%';
 
 /*尾空格可能会干扰通配符匹配。例如，在保存词anvil 时，如果它 后面有一个或多个空格，则子句 WHERE
prod_name LIKE '%anvil' 将不会匹配它们，因为在最后的 l 后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个 % 。一个更好的办法是使用函数（第11章将会介绍）去掉首尾空格*/

/*虽然似乎 % 通配符可以匹配任何东西，但有一个例外，即 NULL 。即使是 WHERE prod_name LIKE '%' 也不能匹配用值 NULL 作为产品名的行。*/
6.2 下划线（ _ ）通配符
1
2
3
/*下划线(_)通配符只能匹配单个字符*/
/*_总是匹配一个字符，不能多也不能少*/
SELECT *FROM products WHERE prod_name LIKE 'jes_s_sdw_';

7、正则表达式

正则表达式是用来匹配文本的特殊的字符串。如果你想从一个文本文件中提取电话号码，可以使用正则表达式。如果你需要查找名字中间有数字的所有文件，可以使用一个正则表达式。如果你想在一个文本块中找到所有重复的单词，可以使用一个正则表达式。如果你想替换一个页面中的所有URL为这些URL的实际HTML链接，也可以使用一个正则表达式（对于最后这个例子，或者是两个正则表达式）。regexp : 正则表达式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/*没有返回值*/
select * from user where username LIKE 'huahu';
Empty set (0.00 sec)  

/*REGEXP有返回值*/
select * from user where username REGEXP 'huahu';
+-------------+----------+----------+------+-------------+-----------+---------+
| username    | pwd      | license  | sex  | tel         | drive_age | cartype |
+-------------+----------+----------+------+-------------+-----------+---------+
| huahua      | fggg6rge | CAOG8T6  | man  | 15388342650 |         1 |       1 |
| huahuahu    | dab5htd  | CAOG8T6  | man  | 15388342650 |         3 |       2 |
| huahuahua   | ses23df  | 川ANB211 | man  | 15388342650 |         7 |       0 |
| huahuahuahe | sdwadsd  | 川UCZ002 | man  | 15388342650 |         6 |       2 |
+-------------+----------+----------+------+-------------+-----------+---------+
4 rows in set (0.37 sec)
7.1 BINARY关键字

Mysql的正则表达式匹配不区分大小。为区分大小，可使用BINARY关键字。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql>  select * from user where username REGEXP 'HUAHUA';
+-------------+----------+----------+------+-------------+-----------+---------+
| username    | pwd      | license  | sex  | tel         | drive_age | cartype |
+-------------+----------+----------+------+-------------+-----------+---------+
| huahua      | fggg6rge | CAOG8T6  | man  | 15388342650 |         1 |       1 |
| HUAHUAa     | wwqw     | 川AOTNTG | man  | 15388342650 |         2 |       2 |
| huahuahu    | dab5htd  | CAOG8T6  | man  | 15388342650 |         3 |       2 |
| huahuahua   | ses23df  | 川ANB211 | man  | 15388342650 |         7 |       0 |
| huahuahuahe | sdwadsd  | 川UCZ002 | man  | 15388342650 |         6 |       2 |
+-------------+----------+----------+------+-------------+-----------+---------+
5 rows in set (0.00 sec)

mysql>  select * from user where username REGEXP BINARY  'HUAHUA';
+----------+------+----------+------+-------------+-----------+---------+
| username | pwd  | license  | sex  | tel         | drive_age | cartype |
+----------+------+----------+------+-------------+-----------+---------+
| HUAHUAa  | wwqw | 川AOTNTG | man  | 15388342650 |         2 |       2 |
+----------+------+----------+------+-------------+-----------+---------+
1 row in set (0.00 sec)
7.2 进行OR匹配 |通配符

为搜索两个串之一，使用|，如下所示：

1
2
3
4
5
6
7
8
9
/*|通配符表示匹配huahuaa和yuhan其中之一，因此两者都可以返回*/
mysql>  select * from user where username REGEXP 'huahuaa|yuhan';
+----------+-----------+----------+------+-------------+-----------+---------+
| username | pwd       | license  | sex  | tel         | drive_age | cartype |
+----------+-----------+----------+------+-------------+-----------+---------+
| HUAHUAa  | wwqw      | 川AOTNTG | man  | 15388342650 |         2 |       2 |
| yuhan    | hjzcxvw21 | CAOG8T6  | man  | 15388342650 |         5 |       2 |
+----------+-----------+----------+------+-------------+-----------+---------+
2 rows in set (0.00 sec)
7.3 [] 匹配几个字符之一
1
2
3
4
5
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton';
/*[] 是另一种形式的 OR 语句。事实上，正则表达式 [123]Ton
为 [1|2|3]Ton 的缩写，也可以使用后者。但是，需要用 [] 来定义 OR 语句
查找什么。为更好地理解这一点，请看下面的例子：*/
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton';

1601386281283

如上所示：着相当于检测1 ,2和3 Ton。

8、子查询

8.1子查询过滤

在SELECT语句中，子查询总是从内向外处理

1
2
3
4
SELECT cust_id FROM orders WHERE order_num IN(SELECT 
                                              FROM orderitems WHERE prod_id = 'TNT2')
/*这个查询表示：首先查询表orderitems中prod_id列为TNT2的oder_num数据，然后根据oder_num在表orders中查询cust_id数据。*/
SELECT cust_name,cuse_contact FROM customers WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));/*套娃查询*/

虽然子查询一般与IN操作符结合使用，但也可以用于测试=、<>等操作符。

8.2作为计算字段使用子查询

使用子查询的另一个方法是创建计算字段，假如需要显示orders表中姓名为hth的用户的订单数目，使用SELECT COUNT（*）来查询，如：

1
SELECT count(*) from orders where username='hth';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
/*查询license以及每个用户订单的次数
  请注意这里是全限定列名，同时注意FROM user ，前面也智能去匹配user中存在的列，且需要外键去关联*/
select license ,(select count(*) from orders where orders.username=user.username)  from user;
/*结果如下：*/
+----------+------------------------------------------------------------------+
| license  | (select count(*)from orders where orders.username=user.username) |
+----------+------------------------------------------------------------------+
| CAOG8T6  |                                                                8 |
| OG8T6    |                                                              252 |
| CAOG8T6  |                                                                1 |
| 川AOTNTG |                                                               19 |
| CAOG8T6  |                                                                5 |
| 川ANB211 |                                                               15 |
| 川UCZ002 |                                                                0 |
| CAOG8T6  |                                                                0 |
| CAOG8T6  |                                                                0 |
| CAOG8T6  |                                                                0 |
| CAOG8T6  |                                                                0 |
+----------+------------------------------------------------------------------+
11 rows in set (0.02 sec)
/*如果不适用全限定列名，会导致：*/
+----------+------------------------------------------------------+
| license  | (select count(*)from orders where username=username) |
+----------+------------------------------------------------------+
| CAOG8T6  |                                                  300 |
| OG8T6    |                                                  300 |
| CAOG8T6  |                                                  300 |
| 川AOTNTG |                                                  300 |
| CAOG8T6  |                                                  300 |
| 川ANB211 |                                                  300 |
| 川UCZ002 |                                                  300 |
| CAOG8T6  |                                                  300 |
| CAOG8T6  |                                                  300 |
| CAOG8T6  |                                                  300 |
| CAOG8T6  |                                                  300 |
+----------+------------------------------------------------------+
11 rows in set (0.00 sec)
/*⭐⭐原因分析：不用全限定列名，就会导致MySQL认为你在对orders表中的username进行自身比较。eg:*/
mysql> select count(*)from orders where username=username;
+----------+
| count(*) |
+----------+
|      300 |
+----------+
1 row in set (0.00 sec)

#####

4、查询表的结构
1
desc 表名
2、插入数据
1
insert into 表名 （字段1，字段2） values （字段1的值，字段2的值）；
3、更新（修改）数据
1
update 表名 set 列名=列值 where 列名=列值；
4、删除记录
1
2
delete from 表名 where 列名=列值；  -------（删除数据可以找回）
  truncate table 表名；            ------（删除整个表再重新建立一个一样结构的表，数据不能找 回）
5、修改某字段/表名 添加列 删除列
1
2
3
4
alter table 表名 change  旧字段名  新字段名 字段类型
alter table 表名 rename to 新表名
alter table 表名 add  column 列名 类型 not null之类 after COLUMN_NAME
alter table 表名字 drop column 列名
6、设置某int类型字段为主键
1
alter table 表名 add primary key(字段名）； ---（字段名一般选择为id）
7、设置主键自增
1
alter table tableName modify id int auto_increment
8、创建表时就设置自增主键
1
2
3
4
create table user(
     id int(4) primary key not null auto_increment,
     username varchar(10) not null,
     userpwd varchar(10) not null);
9、排序
1
select id from 表名 order by id   ---(依次按照id和name排序)
1
select id,price,name from 表名 order by id,name   ---(依次按照id和name排序)
1
select id,price,name from 表名 order by id desc,name   ---(desc降序排列，只应用到直接位于其前面的列名)
1
2
---使用 ORDER BY 和 LIMIT 的组合，能够找出一个列中最高或最低的值。
select price from products order by
10、通配符
1
2
3
4
---百分号通配符 % 表示任何字符出现的任意次数
select id,price from products where id like '001%'  
--其实就是在检索所有id以001开头的产品的id、价格 
--⭐区分大小写 根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写， 'jet%' 与 JetPack 1000 将不匹配
1
2
3
--通配符也可以在中间，但极少使用
select id,price from products where id like '001%00' 
--其实就是在检索所有id以001开头，以00结束的产品的id、价格
1
2
--另一个有用的通配符是下划线（ _ ）。下划线的用途与 % 一样，但下划线只匹配单个字符而不是多个字符。
select name,price from products where name like 'hu_hu_hu_'

⭐⭐11、外键 https://www.w3school.com.cn/sql/sql_foreignkey.asp

1
--外键（foreign key）作为某个表的一列，他包含另一个表的主键值
1
2
3
4
5
6
7
8
9
--创建外键连接
 CREATE TABLE Orders
(
	Id_O int NOT NULL,
	OrderNo int NOT NULL,
	Id_P int,
	PRIMARY KEY (Id_O),
	FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
1
2
3
4
5
6
7
8
9
10
/*Cannot add foreign key constraint 错误解决办法：
产生这个错误的多数原因有一下两点： 
1，两张表里要设主键和外键的字段的数据类型或者数据长度不一样 （例如这个是int 另外一个是tinyint，或者都是int，但是设置的长度不同）
2，某个表里已经有记录了
3、两个表的引擎不一样，查看表的引擎语句：
show table status from 数据库名 where name='表名';
4、要设置外键的字段不能为主键
5、改建所参考的字段必须为主键
6、两个字段必须具有相同的数据类型和约束
来自 https://www.cnblogs.com/olddriver123/p/8335263.html
12、主键https://www.w3school.com.cn/sql/sql_primarykey.asp
13、UNIQUE KEY问题

为什么用查询创建表信息的语句多了这几句话啊，不知道什么时候弄错的。

1
alter table parking_lot_swpu.parking_info drop index parking_spot_id_2

14、事务处理

⭐⭐术语：

​ 事务（transaction）– 指一组SQL语句；

​ 回退（rollback）– 指撤销指定SQL语句的过程；

​ 提交（commit）– 指将未存储的SQL语句结果写入数据库表；

​ 保留点（savepoint）– 指事务处理中设置的临时占位符（place-hoder），您可以对它发布回退（区别于回退整个事务）。

1
2
3
4
5
6
7
8
--控制简单的事务处理
select * from ordertotals;
begin 或 start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;
--首先执行一条 SELECT 以显示该表不为空。然后开始一个事务处理，用一条 DELETE 语句删除 ordertotals 中的所有行。另一条SELECT 语句验证 ordertotals 确实为空。这时用一条 ROLLBACK 语句回退 START TRANSACTION 之后的所有语句，最后一条 SELECT 语句显示该表不为空。
1
2
3
4
5
6
7
--⭐⭐使用commit
	一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT 语句，如下所示：
start transaction 或 begin;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;
--在这个例子中，从系统中完全删除订单 20010 。因为涉及更新两个数据库表 orders 和 orderItems ，所以使用事务处理块来保证订单不被部分删除。最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交（实际上，它是被自动撤销的）。
15、创建UNIQUE约束

对name字段添加unique唯一性约束

① 创建表时添加:

1
2
3
4
5
6
7
CREATE TABLE user
(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(255),
	PRIMARY KEY (id),
	UNIQUE (name)
)

② 表已经存在时添加:

1
ALTER TABLE user ADD UNIQUE (name)

撤销unique约束

1
ALTER TABLE user DROP INDEX name;

访问数据库流程

1
驱动管理器----连接数据库/建立连接（Socket）----SQL语句----结果集   (需要JDBC-java包)

1、建立连接本质是Socket，是一个远程的连接，比较耗时！真正的开发中一般使用连接池来管理连接对象。

2、Statement会导致SQL注入

3、更新一个自己理解的误区还是什么？？ —- 我突然想远程连接自己的mysql数据库，没有连接成功正在烦，但是我为什么要远程连接字地数据库呢？我的服务器程序是直接操作的服务器本地的数据库，只要保证我的客户端软件可以和服务器端程序连接就可以了啊！！！！

4、PreparedStatement的参数索引是从开始计算的

1
2
3
4
5
6
7
8
Class.forName（“com.mysql.jdbc.Driver”）;
String url = "jdbc:mysql://localhost:3306/test";
Connection conn = DriverManager.getConnection(url,用户，密码);     Crtl+Shif+O：important 类
String sql="insert into 表名(列1,列2,列3,) values(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,);
ps.setObject(2,);
ps.serDate(3,new java.sql.Data(System.currentTimeMillis()));
1
2
3
4
ResultSet rs = ps.excuteQuery();
while(rs.next()){
     System.out.pritln(rs.getInt(1)+"---"+rs.getString(2)+"----"++rs.getString(3));
}

5、finally处一定要要关闭(Connect、PreparedStatement）

​ 遵循：resultset —> statement —> connection这样的关闭顺序！

​ 对三个操作需要执行不同的try — catch ，这样可以保证在关闭某个操作出问题时不会影响其他的关闭操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
        if(rs != null){
      try {
	rs.close();
} catch (SQLException e) {
	e.printStackTrace();
}
      }
  if(ps != null) {
  	try {
	ps.close();
} catch (SQLException e) {
	e.printStackTrace();
}
  }
  if(conn != null) {
  	try {
	conn.close();
} catch (SQLException e) {
	e.printStackTrace();
}
  }

JDBC总结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Connection;

public class JDBCUtil {
	
	public static Connection getMysqlConn(String url,String user, String pwd){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(url, user, pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void close(ResultSet rs, Statement ps ,Connection conn) {
		    if(rs != null){
		        try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		        }
		    if(ps != null) {
		    	try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		    }
		    if(conn != null) {
		    	try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		    }
	}
	
	public static void close(Statement ps ,Connection conn) {
	    if(ps != null) {
	    	try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	    }
	    if(conn != null) {
	    	try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	    }
}
	
	public static void close(Connection conn) {
	    if(conn != null) {
	    	try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	    }
}
	
}

MySQL问题总结：

1、开启binlog日志遇到的问题：

如何开启binlog：

1
2
3
4
#设置binlog日志
server_id=1918
log_bin=mysql-bin
binlog-format=Row

完整配置表如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=F:\mysql\mysql-5.7.26-winx64
# 设置mysql数据库的数据的存放目录
datadir=F:\mysql\mysql-5.7.26-winx64\data 
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

#设置binlog日志
server_id=1918
log_bin=mysql-bin
binlog-format=Row

遇到问题：先看日志：

1
2
PS F:\mysql\mysql-5.7.26-winx64\bin> cd F:\mysql\mysql-5.7.26-winx64\bin
PS F:\mysql\mysql-5.7.26-winx64\bin> mysqld --console

找寻其中的error